IN_SUBITEM_RPT

(SQL View)
 Index Back

Inv item substitution report

Inventory item substitution report

SELECT TA.BUSINESS_UNIT , TA.REQUEST_DATE , TC.FISCAL_YEAR , %NumToChar(TC.FISCAL_YEAR) %Concat '/' %Concat CASE WHEN (TC.ACCOUNTING_PERIOD >= 10) THEN %NumToChar(TC.ACCOUNTING_PERIOD) ELSE '0' %Concat %NumToChar(TC.ACCOUNTING_PERIOD ) END , TA.ORDER_NO , TA.ORIG_INV_ITEM_ID , TA.INV_ITEM_ID , TE.UNIT_MEASURE_STD , TG.VENDOR_ID , TA.QTY_REQUESTED_SUB , TB.TOTAL_QTY , %Round(TA.QTY_REQUESTED_SUB / TB.TOTAL_QTY, 4) * 100 FROM ( SELECT T.DEMAND_SOURCE , T.SOURCE_BUS_UNIT , T.BUSINESS_UNIT , T.REQUEST_DATE , T.ORDER_NO , T.ORIG_INV_ITEM_ID , T.INV_ITEM_ID , T1.CONVERSION_RATE , SUM(( QTY_REQUESTED_BASE - QTY_BACKORDER_BASE )/ %Coalesce(T1.CONVERSION_RATE, 1)) QTY_REQUESTED_SUB FROM PS_IN_DEMAND T , PS_BU_ITEM_SUB T1 WHERE T.IN_FULFILL_STATE <> '90' AND T.DEMAND_SOURCE ='IN' AND T.ORIG_INV_ITEM_ID <> T.INV_ITEM_ID AND T.ORIG_INV_ITEM_ID <> ' ' AND T.BUSINESS_UNIT = T1.BUSINESS_UNIT AND T.ORIG_INV_ITEM_ID = T1.INV_ITEM_ID AND T.INV_ITEM_ID = T1.SUB_ITM_ID AND T.DEMAND_SOURCE ='IN' GROUP BY T.DEMAND_SOURCE, T.SOURCE_BUS_UNIT, T.BUSINESS_UNIT, T.REQUEST_DATE, T.ORDER_NO, T.ORIG_INV_ITEM_ID, T.INV_ITEM_ID, T1.CONVERSION_RATE) TA, ( SELECT T.DEMAND_SOURCE , T.SOURCE_BUS_UNIT , T.BUSINESS_UNIT , T.ORDER_NO , %Coalesce( %TrimSubstr(T.ORIG_INV_ITEM_ID,1,18),T.INV_ITEM_ID ) ORIG_INV_ITEM_ID , SUM (CASE WHEN T.ORIG_INV_ITEM_ID = T.INV_ITEM_ID OR T.ORIG_INV_ITEM_ID = ' ' THEN T.QTY_REQUESTED_BASE - T.QTY_BACKORDER_BASE ELSE (T.QTY_REQUESTED_BASE - T.QTY_BACKORDER_BASE) / %Coalesce(T1.CONVERSION_RATE, 1) END ) TOTAL_QTY FROM PS_IN_DEMAND T LEFT OUTER JOIN PS_BU_ITEM_SUB T1 ON T.BUSINESS_UNIT = T1.BUSINESS_UNIT AND T.ORIG_INV_ITEM_ID = T1.INV_ITEM_ID AND T.INV_ITEM_ID = T1.SUB_ITM_ID WHERE T.IN_FULFILL_STATE <> '90' AND T.DEMAND_SOURCE='IN' GROUP BY T.DEMAND_SOURCE, T.SOURCE_BUS_UNIT, T.BUSINESS_UNIT, T.ORDER_NO, %Coalesce( %TrimSubstr(T.ORIG_INV_ITEM_ID,1,18),T.INV_ITEM_ID )) TB, ( SELECT DISTINCT C.BUSINESS_UNIT , E.FISCAL_YEAR , E.ACCOUNTING_PERIOD , E.BEGIN_DT , E.END_DT FROM PS_SET_CNTRL_REC A , PS_FIN_OPEN_PERIOD B , PS_FIN_BU_MAPPING C , PS_BU_LED_GRP_TBL D , PS_CAL_DETP_TBL E WHERE A.RECNAME = 'CAL_DEFN_TBL' AND A.SETCNTRLVALUE = B.BUSINESS_UNIT AND B.PSFT_PRODUCT = 'IN' AND B.TRANSACTION_TYPE = 'DEF' AND C.PSFT_PRODUCT = B.PSFT_PRODUCT AND C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.BUSINESS_UNIT_GL = D.BUSINESS_UNIT AND D.DEFAULT_LEDGER_GRP = 'Y' AND B.LEDGER_GROUP = D.LEDGER_GROUP AND E.SETID = A.SETID AND E.CALENDAR_ID = B.CALENDAR_ID) TC, PS_SET_CNTRL_REC TF, PS_MASTER_ITEM_TBL TE LEFT OUTER JOIN ( SELECT A.SETID , A.VENDOR_SETID , A.INV_ITEM_ID , A.VENDOR_ID FROM PS_ITM_VENDOR A WHERE A.ITM_VNDR_PRIORITY = ( SELECT MIN(ITM_VNDR_PRIORITY) FROM PS_ITM_VENDOR B WHERE B.SETID = A.SETID AND B.INV_ITEM_ID = A.INV_ITEM_ID AND B.VENDOR_SETID = A.VENDOR_SETID) ) TG ON TE.SETID = TG.SETID AND TE.SETID = TG.VENDOR_SETID AND TE.INV_ITEM_ID = TG.INV_ITEM_ID WHERE TA.DEMAND_SOURCE = TB.DEMAND_SOURCE AND TA.SOURCE_BUS_UNIT = TB.SOURCE_BUS_UNIT AND TA.BUSINESS_UNIT = TB.BUSINESS_UNIT AND TA.ORDER_NO = TB.ORDER_NO AND TA.ORIG_INV_ITEM_ID = TB.ORIG_INV_ITEM_ID AND TA.BUSINESS_UNIT = TC.BUSINESS_UNIT AND TA.REQUEST_DATE BETWEEN TC.BEGIN_DT AND TC.END_DT AND TF.RECNAME = 'MASTER_ITEM_TBL' AND TA.BUSINESS_UNIT = TF.SETCNTRLVALUE AND TE.SETID = TF.SETID AND TE.INV_ITEM_ID = TA.ORIG_INV_ITEM_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 REQUEST_DATE Date(10) DATE Requested
3 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
4 YEAR_PERIOD Character(10) VARCHAR2(10) NOT NULL Combination Field of ChartField year and period
5 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
6 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
7 SUB_ITEM_ID Character(18) VARCHAR2(18) NOT NULL 11/02/05 EGS: Created
8 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
9 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
10 QTY_SUB_ITEM Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Substituted
11 TOTAL_QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Total Quantity
12 RATE_SUB Signed Number(11,2) DECIMAL(9,2) NOT NULL Substituted Rate (100%)