IN_SDA_PO_VW3

(SQL View)
 Index Back

Sup/Dmd Dbrd: Staged Inf Inv

With or without PO, pegged or not, in staged_inf_inv. (Not used for PAR analytic)

SELECT S.BUSINESS_UNIT , S.BUSINESS_UNIT , S.SOURCE_BUS_UNIT , S.INV_ITEM_ID , S.INV_ITEM_ID , L.DUE_DT , L.RECEIPT_DTTM , S.PO_ID , L.LINE_NBR , S.VENDOR_ID , V.NAME1 , L.MERCH_AMT_BSE , L.MERCHANDISE_AMT , L.CURRENCY_CD , L.CURRENCY_CD_BASE , L.QTY_SH_ACCPT_VUOM , L.QTY_SH_ACCPT_SUOM , S.QTY , S.UNIT_OF_MEASURE , S.CONVERSION_RATE , S.RECEIVER_ID , S.RECV_LN_NBR , S.RECV_SHIP_SEQ_NBR , L.RECV_SHIP_STATUS , M.INV_ITEM_GROUP , M.INV_PROD_FAM_CD , M.UNIT_MEASURE_STD , CASE WHEN P.PEG_STATUS IS NULL THEN '40' ELSE P.PEG_STATUS END , '30' , 'R' FROM PS_STAGED_INF_INV S JOIN PS_RECV_LN_SHIP L ON L.BUSINESS_UNIT = S.SOURCE_BUS_UNIT AND L.RECEIVER_ID = S.RECEIVER_ID AND L.RECV_LN_NBR = S.RECV_LN_NBR AND L.RECV_SHIP_SEQ_NBR = S.RECV_SHIP_SEQ_NBR JOIN PS_RECV_HDR H ON H.BUSINESS_UNIT = L.BUSINESS_UNIT AND H.RECEIVER_ID = L.RECEIVER_ID JOIN PS_VENDOR V ON V.SETID = H.VENDOR_SETID AND V.VENDOR_ID = H.VENDOR_ID JOIN PS_SET_CNTRL_REC C ON C.SETCNTRLVALUE = S.BUSINESS_UNIT AND C.RECNAME = 'MASTER_ITEM_TBL' JOIN PS_MASTER_ITEM_TBL M ON M.SETID = C.SETID AND M.INV_ITEM_ID = S.INV_ITEM_ID LEFT OUTER JOIN PS_IN_PEGGING P ON P.SUPPLY_HASH = S.SUPPLY_HASH WHERE S.TRANS_SOURCE = '01'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
3 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit
4 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
5 INV_ITEM_ID_PO Character(18) VARCHAR2(18) NOT NULL Item Number PO
6 DUE_DT Date(10) DATE Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.
7 RECEIPT_DTTM DateTime(26) TIMESTAMP Receipt Datetime
8 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
9 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
10 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
11 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
12 MERCH_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Merchandise Amount
13 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
14 CURRENCY_CD2 Character(3) VARCHAR2(3) NOT NULL Currency Code
15 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
16 QTY_SH_ACCPT_VUOM Number(16,4) DECIMAL(15,4) NOT NULL Accept QTY in Supplier UOM
17 QTY_RECV_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Received
18 QTY_REQUESTED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Requested
19 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
20 CONVERSION_RATE Signed Number(17,8) DECIMAL(15,8) NOT NULL Conversion Rate
21 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
22 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
23 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
24 RECV_SHIP_STATUS Character(1) VARCHAR2(1) NOT NULL Receipt Schedule Status
C=Closed
H=Hold
O=Open
R=Received
X=Canceled

Default Value: O

25 INV_ITEM_GROUP2 Character(15) VARCHAR2(15) NOT NULL Item Group
26 INV_PROD_FAM_CD2 Character(10) VARCHAR2(10) NOT NULL Item Family
27 UNIT_MEASURE_STD2 Character(3) VARCHAR2(3) NOT NULL Standard UOM
28 PEG_STATUS Character(2) VARCHAR2(2) NOT NULL 07/21/04 EGS: Created
10=Open
20=Completed
30=Canceled
40=Not Pegged
29 SUPPLY_SOURCE Character(2) VARCHAR2(2) NOT NULL Supply Source
10=PO, Not Received
20=No PO, Recvd, Not Staged
30=Staged in Staged_inf_inv
40=Staged in Staged_item_inv
IN=Inventory - MSRs
PO=Purchase Orders
PR=Requisitions
30 PO_STATUS Character(2) VARCHAR2(2) NOT NULL PO Status
A=Approved
C=Complete
D=Dispatched
DA=Denied
I=Initial
LD=Line Denied
O=Open
PA=Pending Approval/Approved
PX=Pending Cancel
X=Canceled