BUSINESS_UNITVENDOR_IDINV_ITEM_ID10=High
20=Medium
30=Low
CATEGORY_CDCATEGORY_TYPEPSF=User Def
UNS=UN/SPSC
IN_SSQTYBAL2_VW(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 ,C.VENDOR_ID , C.VNDR_LOC , C.VENDOR_SETID ,A.INV_ITEM_ID ,C.VMI_PRIORITY ,D.CATEGORY_CD ,D.CATEGORY_TYPE ,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_ITM_VND_BU_ATTR C ,PS_ITM_CAT_TBL D WHERE C.VMI_FLAG = 'Y' AND A.BUSINESS_UNIT = C.BUSINESS_UNIT_IN AND A.INV_ITEM_ID = C.INV_ITEM_ID AND C.SETID = B.SETID AND C.INV_ITEM_ID = B.INV_ITEM_ID AND B.SETID = D.SETID AND B.CATEGORY_ID = D.CATEGORY_ID AND D.EFF_STATUS = 'A' AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_ITM_CAT_TBL X WHERE X.SETID = B.SETID AND X.CATEGORY_TYPE = D.CATEGORY_TYPE AND X.CATEGORY_CD = D.CATEGORY_CD AND X.EFF_STATUS = 'A') 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' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | VENDOR_ID |
Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 3 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
| 4 | VENDOR_SETID | Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 5 | INV_ITEM_ID |
Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 6 | VMI_PRIORITY | Character(2) | VARCHAR2(2) NOT NULL |
VMI Priority
10=High 20=Medium 30=Low |
| 7 | CATEGORY_CD |
Character(18) | VARCHAR2(18) NOT NULL | Category Code |
| 8 | CATEGORY_TYPE |
Character(3) | VARCHAR2(3) NOT NULL |
Category Type
PSF=User Def UNS=UN/SPSC |
| 9 | INCL_WIP_QTY_FLG | Character(1) | VARCHAR2(1) NOT NULL | Include WIP Quantity |
| 10 | QTY_AVAILABLE | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Qty Available |
| 11 | QTY_ONHAND | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Quantity On Hand |
| 12 | REPLENISH_CLASS | Character(4) | VARCHAR2(4) NOT NULL | Replenish Class |
| 13 | REPL_CALC_PERIOD | Number(3,0) | SMALLINT NOT NULL | Replenish Calc Period (Days) |
| 14 | REPLENISH_LEAD | Signed Number(16,2) | DECIMAL(14,2) NOT NULL | Replenish Lead (Days) |
| 15 | REORDER_POINT | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Reorder Point |
| 16 | REORDER_QTY | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Reorder Qty |
| 17 | QTY_MAXIMUM | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Maximum Qty |
| 18 | SAFETY_STOCK | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Safety Stock |
| 19 | LAST_ANNUAL_DEMAND | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Last Ann. Demand |
| 20 | STOCKOUT_RATE | Number(4,1) | DECIMAL(3,1) NOT NULL | Desired Stock-In Probability |
| 21 | EOQ | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Economic Order Quantity |
| 22 | QTY_OWNED | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Qty Owned |
| 23 | QTY_ALLOCATED | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Allocated quantity |