BUSINESS_UNITVENDOR_IDOPRIDINV_ITEM_IDWV_PE_QTYBL_2VW(SQL View) |
Index Back |
|---|---|
Inv Balance InquiryThis view is used within the Inventory Balance Inquiry for supplier portal. It is used to select all the rows that have the include_wip_qty flag set to No.. (and are not filtering on supplier item id.) |
| SELECT A.BUSINESS_UNIT ,D.SETID ,A.VENDOR_ID ,D.OPRID ,B.SETID ,A.INV_ITEM_ID ,A.CONSIGNED_FLAG ,A.INCL_WIP_QTY_FLG ,A.QTY_AVAILABLE ,A.QTY_ONHAND ,A.REPLENISH_CLASS ,A.REPL_CALC_PERIOD ,A.REPLENISH_LEAD ,A.REORDER_POINT ,A.REORDER_QTY ,A.QTY_MAXIMUM ,A.SAFETY_STOCK ,A.LAST_ANNUAL_DEMAND ,A.STOCKOUT_RATE ,A.EOQ ,A.QTY_OWNED ,0 FROM PS_BU_ITEMS_INV A ,PS_MASTER_ITEM_TBL B ,PS_SET_CNTRL_REC C ,PS_VENDOR_USER D WHERE A.CONSIGNED_FLAG = 'Y' AND A.BUSINESS_UNIT = C.SETCNTRLVALUE AND C.RECNAME = 'MASTER_ITEM_TBL' AND B.SETID = C.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.VENDOR_ID <> ' ' AND B.DIST_CFG_FLAG = 'N' AND B.PRDN_CFG_FLAG = 'N' AND A.ITM_STATUS_CURRENT <> '5' AND A.ITM_STATUS_CURRENT <> '6' AND A.ITM_STATUS_CURRENT <> '7' AND A.INCL_WIP_QTY_FLG <> 'Y' AND D.VENDOR_ID = A.VENDOR_ID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | VENDOR_SETID | Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 3 | VENDOR_ID |
Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 4 | OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 5 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 6 | INV_ITEM_ID |
Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 7 | CONSIGNED_FLAG | Character(1) | VARCHAR2(1) NOT NULL | Consigned Flag |
| 8 | INCL_WIP_QTY_FLG | Character(1) | VARCHAR2(1) NOT NULL | Include WIP Quantity |
| 9 | QTY_AVAILABLE | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Qty Available |
| 10 | QTY_ONHAND | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Quantity On Hand |
| 11 | REPLENISH_CLASS | Character(4) | VARCHAR2(4) NOT NULL | Replenish Class |
| 12 | REPL_CALC_PERIOD | Number(3,0) | SMALLINT NOT NULL | Replenish Calc Period (Days) |
| 13 | REPLENISH_LEAD | Signed Number(16,2) | DECIMAL(14,2) NOT NULL | Replenish Lead (Days) |
| 14 | REORDER_POINT | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Reorder Point |
| 15 | REORDER_QTY | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Reorder Qty |
| 16 | QTY_MAXIMUM | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Maximum Qty |
| 17 | SAFETY_STOCK | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Safety Stock |
| 18 | LAST_ANNUAL_DEMAND | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Last Ann. Demand |
| 19 | STOCKOUT_RATE | Number(4,1) | DECIMAL(3,1) NOT NULL | Desired Stock-In Probability |
| 20 | EOQ | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Economic Order Quantity |
| 21 | QTY_OWNED | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Qty Owned |
| 22 | QTY_ALLOCATED | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Allocated quantity |