IN_SSQTYBAL3_VW

(SQL View)
 Index Back

Inventory Balance Inq

This 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 Yes. (and filtering on supplier item id.)

SELECT A.BUSINESS_UNIT ,C.VENDOR_ID ,C.VNDR_LOC , C.VENDOR_SETID ,A.INV_ITEM_ID ,H.ITM_ID_VNDR ,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 ,SUM(E.QTY_BASE) FROM PS_BU_ITEMS_INV A ,PS_MASTER_ITEM_TBL B ,PS_ITM_VND_BU_ATTR C ,PS_ITM_CAT_TBL D ,PS_PHYSICAL_INV E ,PS_STOR_LOC_INV F ,PS_BUS_UNIT_TBL_IN G , PS_ITM_VENDOR H 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 C.VENDOR_ID = H.VENDOR_ID AND C.VENDOR_SETID = H.VENDOR_SETID AND C.INV_ITEM_ID = H.INV_ITEM_ID AND C.SETID = H.SETID 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.BUSINESS_UNIT = G.BUSINESS_UNIT AND A.BUSINESS_UNIT = E.BUSINESS_UNIT AND A.BUSINESS_UNIT = F.BUSINESS_UNIT AND A.INV_ITEM_ID = E.INV_ITEM_ID AND E.STORAGE_AREA = F.STORAGE_AREA AND E.STOR_LEVEL_1 = F.STOR_LEVEL_1 AND E.STOR_LEVEL_2 = F.STOR_LEVEL_2 AND E.STOR_LEVEL_3 = F.STOR_LEVEL_3 AND E.STOR_LEVEL_4 = F.STOR_LEVEL_4 AND F.WIP_LOC_FLG = 'Y' AND F.NETTABLE_FLG = 'Y' AND E.AVAIL_STATUS <> '4' AND (E.AVAIL_STATUS = '1' OR G.INCL_QUAR_AVAIL = 'Y') AND A.INCL_WIP_QTY_FLG = 'Y' GROUP BY A.BUSINESS_UNIT ,C.VENDOR_ID ,C.VNDR_LOC, C.VENDOR_SETID, A.INV_ITEM_ID ,H.ITM_ID_VNDR ,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

# 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 ITM_ID_VNDR Character(50) VARCHAR2(50) NOT NULL Vendor Item ID
7 VMI_PRIORITY Character(2) VARCHAR2(2) NOT NULL VMI Priority
10=High
20=Medium
30=Low
8 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
9 CATEGORY_TYPE Character(3) VARCHAR2(3) NOT NULL Category Type
PSF=User Def
UNS=UN/SPSC
10 INCL_WIP_QTY_FLG Character(1) VARCHAR2(1) NOT NULL Include WIP Quantity
11 QTY_AVAILABLE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Available
12 QTY_ONHAND Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity On Hand
13 REPLENISH_CLASS Character(4) VARCHAR2(4) NOT NULL Replenish Class
14 REPL_CALC_PERIOD Number(3,0) SMALLINT NOT NULL Replenish Calc Period (Days)
15 REPLENISH_LEAD Signed Number(16,2) DECIMAL(14,2) NOT NULL Replenish Lead (Days)
16 REORDER_POINT Signed Number(17,4) DECIMAL(15,4) NOT NULL Reorder Point
17 REORDER_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Reorder Qty
18 QTY_MAXIMUM Signed Number(17,4) DECIMAL(15,4) NOT NULL Maximum Qty
19 SAFETY_STOCK Signed Number(17,4) DECIMAL(15,4) NOT NULL Safety Stock
20 LAST_ANNUAL_DEMAND Signed Number(17,4) DECIMAL(15,4) NOT NULL Last Ann. Demand
21 STOCKOUT_RATE Number(4,1) DECIMAL(3,1) NOT NULL Desired Stock-In Probability
22 EOQ Signed Number(17,4) DECIMAL(15,4) NOT NULL Economic Order Quantity
23 QTY_OWNED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Owned
24 QTY_ALLOCATED Signed Number(17,4) DECIMAL(15,4) NOT NULL Allocated quantity