IN_SSQTYBAL1_VW

(SQL View)
 Index Back

Inv Balance inquiry

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 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 ,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 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.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 ,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 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