IN_SDWCPORCV_VW

(SQL View)
 Index Back

PO's Received

Real-time view of PO's Received

SELECT B.BUSINESS_UNIT_IN , 0 , ' ' , ' ' , A.INV_ITEM_ID , ' ' , A.RECEIVER_ID , A.RECV_LN_NBR , 0 , B.BUSINESS_UNIT , A.RECV_SHIP_SEQ_NBR , B.DISTRIB_LINE_NUM , 0 , '01' , B.BUSINESS_UNIT_IN , ' ' , ' ' , B.PO_ID , ' ' , A.RMA_ID , A.QTY_SH_ACCPT_SUOM , A.MOVE_STAT_INV , '1' , %DatePart(A.RECEIPT_DTTM ) , P.MERCH_AMT_BSE , P.CURRENCY_CD_BASE , P.MERCHANDISE_AMT , P.CURRENCY_CD , PP.VENDOR_ID , A.UNIT_MEASURE_STD FROM PS_RECV_LN_SHIP A , PS_RECV_LN_DISTRIB B , PS_PO_LINE_SHIP P , PS_PO_HDR PP WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.RECEIVER_ID = B.RECEIVER_ID AND A.RECV_LN_NBR = B.RECV_LN_NBR AND A.RECV_SHIP_SEQ_NBR = B.RECV_SHIP_SEQ_NBR AND A.BUSINESS_UNIT = P.BUSINESS_UNIT AND A.PO_ID = P.PO_ID AND A.LINE_NBR = P.LINE_NBR AND A.SCHED_NBR = P.SCHED_NBR AND A.BUSINESS_UNIT = PP.BUSINESS_UNIT AND A.PO_ID = PP.PO_ID AND A.MOVE_STAT_INV = 'P' UNION SELECT C.BUSINESS_UNIT , 0 , C.INV_LOT_ID , C.CONTAINER_ID , C.INV_ITEM_ID , C.SERIAL_ID , C.RECEIVER_ID , C.RECV_LN_NBR , C.SEQ_NUM , CASE WHEN C.SOURCE_BUS_UNIT = ' ' THEN C.BUSINESS_UNIT ELSE C.SOURCE_BUS_UNIT END , C.RECV_SHIP_SEQ_NBR , C.DISTRIB_LINE_NUM , C.DISTRIB_SEQ_NUM , C.TRANS_SOURCE , C.BUSINESS_UNIT , C.STAGED_STATUS , ' ' , C.PO_ID , C.PRODUCTION_ID , C.RMA_ID , R.QTY_SH_ACCPT_SUOM , ' ' , '2' , %DatePart(R.RECEIPT_DTTM) , P.MERCH_AMT_BSE , P.CURRENCY_CD_BASE , P.MERCHANDISE_AMT , P.CURRENCY_CD , PP.VENDOR_ID , R.UNIT_MEASURE_STD FROM PS_STAGED_INF_INV C , PS_RECV_LN_SHIP R , PS_PO_LINE_SHIP P , PS_PO_HDR PP WHERE C.BUSINESS_UNIT = R.BUSINESS_UNIT AND C.RECEIVER_ID = R.RECEIVER_ID AND C.RECV_LN_NBR = R.RECV_LN_NBR AND C.RECV_SHIP_SEQ_NBR = R.RECV_SHIP_SEQ_NBR AND R.BUSINESS_UNIT = P.BUSINESS_UNIT AND R.PO_ID = P.PO_ID AND R.LINE_NBR = P.LINE_NBR AND R.SCHED_NBR = P.SCHED_NBR AND P.BUSINESS_UNIT = PP.BUSINESS_UNIT AND P.PO_ID = PP.PO_ID UNION SELECT D.BUSINESS_UNIT , D.STAGING_ID , D.INV_LOT_ID , D.CONTAINER_ID , D.INV_ITEM_ID , D.SERIAL_ID , D.RECEIVER_ID , D.RECV_LN_NBR , D.STAGED_NUM , CASE WHEN D.TRANS_SOURCE = '02' THEN D.BUSINESS_UNIT ELSE D.SOURCE_BUS_UNIT END , D.RECV_SHIP_SEQ_NBR , D.DISTRIB_LINE_NUM , D.DISTRIB_SEQ_NUM , D.TRANS_SOURCE , D.BUSINESS_UNIT , ' ' , D.PUTAWAY_STATUS , D.PO_ID , D.PRODUCTION_ID , D.RMA_ID , R.QTY_SH_ACCPT_SUOM , ' ' , '3' , %DatePart(R.RECEIPT_DTTM) , P.MERCH_AMT_BSE , P.CURRENCY_CD_BASE , P.MERCHANDISE_AMT , P.CURRENCY_CD , PP.VENDOR_ID , R.UNIT_MEASURE_STD FROM PS_STAGED_ITEM_INV D , PS_RECV_LN_SHIP R , PS_PO_LINE_SHIP P , PS_PO_HDR PP WHERE D.BUSINESS_UNIT = R.BUSINESS_UNIT AND D.RECEIVER_ID = R.RECEIVER_ID AND D.RECV_LN_NBR = R.RECV_LN_NBR AND D.RECV_SHIP_SEQ_NBR = R.RECV_SHIP_SEQ_NBR AND R.BUSINESS_UNIT = P.BUSINESS_UNIT AND R.PO_ID = P.PO_ID AND R.LINE_NBR = P.LINE_NBR AND R.SCHED_NBR = P.SCHED_NBR AND P.BUSINESS_UNIT = PP.BUSINESS_UNIT AND P.PO_ID = PP.PO_ID UNION SELECT T.SOURCE_BUS_UNIT , 0 , T.INV_LOT_ID , T.CONTAINER_ID , T.INV_ITEM_ID , T.SERIAL_ID , T.RECEIVER_ID , T.RECV_LN_NBR , 0 , T.SOURCE_BUS_UNIT , T.RECV_SHIP_SEQ_NBR , T.DISTRIB_LINE_NUM , T.DISTRIB_SEQ_NUM , 'PO' , T.BUSINESS_UNIT , ' ' , '2' , T.PO_ID , T.PRODUCTION_ID , T.RMA_ID , T.QTY_BASE , 'M' , '4' , %DatePart(T.DT_TIMESTAMP) , %DecMult(T.PRICE_PO , QTY_BASE) , I.BASE_CURRENCY , 0 , ' ' , PP.VENDOR_ID , T.UNIT_MEASURE_STD FROM PS_TRANSACTION_INV T , PS_BUS_UNIT_TBL_IN I , PS_PO_HDR PP WHERE T.BUSINESS_UNIT = I.BUSINESS_UNIT AND T.TRANSACTION_GROUP = '020' AND T.PO_ID <> ' ' AND T.SOURCE_BUS_UNIT = PP.BUSINESS_UNIT AND T.PO_ID = PP.PO_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BUIN_NONVW

2 STAGING_ID Number(15,0) DECIMAL(15) NOT NULL Putaway Staging ID
3 INV_LOT_ID Character(15) VARCHAR2(15) NOT NULL Lot ID

Default Value: INSTALLATION_IN.DFLT_INV_LOT_ID

4 CONTAINER_ID Character(10) VARCHAR2(10) NOT NULL Container ID

Prompt Table: CNTR_ID_INV

5 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

Prompt Table: BU_ITM_INV_VW12

6 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID

Default Value: INSTALLATION_IN.DFLT_SERIAL_ID

7 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
8 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
9 STAGED_NUM Number(3,0) SMALLINT NOT NULL Staged Number

Default Value: 1

10 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
11 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
12 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
13 DISTRIB_SEQ_NUM Number(5,0) INTEGER NOT NULL Distribution Sequence
14 TRANS_SOURCE Character(3) VARCHAR2(3) NOT NULL Transaction Source
01=Purchasing
02=WIP Completion
03=Inventory
04=WIP Component
05=Adjustment
06=Component Kit Returns
15 BUSINESS_UNIT_IN Character(5) VARCHAR2(5) NOT NULL Inventory Unit
16 STAGED_STATUS Character(3) VARCHAR2(3) NOT NULL Staged Status
01=InterUnit
02=Receipt
17 PUTAWAY_STATUS Character(1) VARCHAR2(1) NOT NULL Item Putaway Status
1=Not Assigned
2=Putaway Complete
3=In Error
18 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
19 PRODUCTION_ID Character(10) VARCHAR2(10) NOT NULL Production ID
20 RMA_ID Character(10) VARCHAR2(10) NOT NULL RMA Number
21 QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity
22 MOVE_STAT_INV Character(1) VARCHAR2(1) NOT NULL MOVE_STAT_INV
M=Moved
N=N/A
P=Pending
23 PUTAWAY_STATE Character(1) VARCHAR2(1) NOT NULL Putaway State
1=Not Interfaced
2=Interfaced, Not Staged
3=Staged, Not Putaway
4=Putaway Complete
24 RECEIPT_DT Date(10) DATE Received Date
25 MERCH_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Merchandise Amount
26 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
27 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
28 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
29 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
30 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure