IN_SDWC_PUT_VW2

(SQL View)
 Index Back

Putaways not Complete

Putaways not complete view to populate the context record

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 , '1' , A.BUSINESS_UNIT %Concat '~' %Concat A.RECEIVER_ID %Concat '~' %Concat %NumToChar(A.RECV_LN_NBR) %Concat '~' %Concat %NumToChar(A.RECV_SHIP_SEQ_NBR) FROM PS_RECV_LN_SHIP A , PS_RECV_LN_DISTRIB B 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.MOVE_STAT_INV = 'P' AND NOT EXISTS ( SELECT BB.RECEIVER_ID FROM PS_IN_SDWC_PUT_CTX BB , PS_EOEN_DASHBRD CC WHERE BB.BUSINESS_UNIT = A.BUSINESS_UNIT AND BB.RECEIVER_ID = A.RECEIVER_ID AND BB.RECV_LN_NBR = A.RECV_LN_NBR AND BB.RECV_SHIP_SEQ_NBR = A.RECV_SHIP_SEQ_NBR AND BB.EOEN_PRCS_NAME = CC.EOEN_PRCS_NAME AND BB.EOEN_CATEGORY = CC.EOEN_CATEGORY AND BB.SEQ_NBR_EOEN = CC.SEQ_NBR_EOEN AND CC.EOEN_STATUS = '0') UNION SELECT S1.BUSINESS_UNIT , 0 , S1.INV_LOT_ID , S1.CONTAINER_ID , S1.INV_ITEM_ID , S1.SERIAL_ID , S1.RECEIVER_ID , S1.RECV_LN_NBR , S1.SEQ_NUM , CASE WHEN S1.SOURCE_BUS_UNIT = ' ' THEN S1.BUSINESS_UNIT ELSE S1.SOURCE_BUS_UNIT END , S1.RECV_SHIP_SEQ_NBR , S1.DISTRIB_LINE_NUM , S1.DISTRIB_SEQ_NUM , '2' , S1.BUSINESS_UNIT %Concat '~' %Concat S1.INV_LOT_ID %Concat '~' %Concat S1.CONTAINER_ID %Concat '~' %Concat S1.INV_ITEM_ID %Concat '~' %Concat S1.SERIAL_ID %Concat '~' %Concat S1.RECEIVER_ID %Concat '~' %Concat %NumToChar(S1.RECV_LN_NBR) %Concat '~' %Concat %NumToChar(S1.SEQ_NUM) %Concat '~' %Concat S1.SOURCE_BUS_UNIT %Concat '~' %Concat %NumToChar(S1.RECV_SHIP_SEQ_NBR) %Concat '~' %Concat %NumToChar(S1.DISTRIB_LINE_NUM) %Concat '~' %Concat %NumToChar(S1.DISTRIB_SEQ_NUM) FROM PS_STAGED_INF_INV S1 WHERE NOT EXISTS ( SELECT BB.RECEIVER_ID FROM PS_IN_SDWC_PUT_CTX BB , PS_EOEN_DASHBRD CC WHERE BB.BUSINESS_UNIT = S1.BUSINESS_UNIT AND BB.INV_LOT_ID = S1.INV_LOT_ID AND BB.CONTAINER_ID = S1.CONTAINER_ID AND BB.INV_ITEM_ID = S1.INV_ITEM_ID AND BB.SERIAL_ID = S1.SERIAL_ID AND BB.RECEIVER_ID = S1.RECEIVER_ID AND BB.RECV_LN_NBR = S1.RECV_LN_NBR AND BB.STAGED_NUM = S1.SEQ_NUM AND BB.SOURCE_BUS_UNIT = S1.SOURCE_BUS_UNIT AND BB.RECV_SHIP_SEQ_NBR = S1.RECV_SHIP_SEQ_NBR AND BB.DISTRIB_LINE_NUM = S1.DISTRIB_LINE_NUM AND BB.DISTRIB_SEQ_NUM = S1.DISTRIB_SEQ_NUM AND BB.EOEN_PRCS_NAME = CC.EOEN_PRCS_NAME AND BB.EOEN_CATEGORY = CC.EOEN_CATEGORY AND BB.SEQ_NBR_EOEN = CC.SEQ_NBR_EOEN AND CC.EOEN_STATUS = '0') UNION SELECT S2.BUSINESS_UNIT , S2.STAGING_ID , S2.INV_LOT_ID , S2.CONTAINER_ID , S2.INV_ITEM_ID , S2.SERIAL_ID , S2.RECEIVER_ID , S2.RECV_LN_NBR , S2.STAGED_NUM , CASE WHEN S2.TRANS_SOURCE = '02' THEN S2.BUSINESS_UNIT ELSE S2.SOURCE_BUS_UNIT END , S2.RECV_SHIP_SEQ_NBR , S2.DISTRIB_LINE_NUM , S2.DISTRIB_SEQ_NUM , '3' , S2.BUSINESS_UNIT %Concat '~' %Concat %NumToChar(S2.STAGING_ID) %Concat '~'%Concat S2.INV_LOT_ID %Concat '~' %Concat S2.CONTAINER_ID %Concat '~' %Concat S2.INV_ITEM_ID %Concat '~' %Concat S2.SERIAL_ID %Concat '~' %Concat S2.RECEIVER_ID %Concat '~' %Concat %NumToChar(S2.RECV_LN_NBR) %Concat '~' %Concat %NumToChar(S2.STAGED_NUM) %Concat '~' %Concat S2.SOURCE_BUS_UNIT %Concat '~' %Concat %NumToChar(S2.RECV_SHIP_SEQ_NBR) %Concat '~' %Concat %NumToChar(S2.DISTRIB_LINE_NUM) %Concat '~' %Concat %NumToChar(S2.DISTRIB_SEQ_NUM) FROM PS_STAGED_ITEM_INV S2 WHERE NOT EXISTS ( SELECT BB.RECEIVER_ID FROM PS_IN_SDWC_PUT_CTX BB , PS_EOEN_DASHBRD CC WHERE BB.BUSINESS_UNIT = S2.BUSINESS_UNIT AND BB.INV_LOT_ID = S2.INV_LOT_ID AND BB.CONTAINER_ID = S2.CONTAINER_ID AND BB.INV_ITEM_ID = S2.INV_ITEM_ID AND BB.SERIAL_ID = S2.SERIAL_ID AND BB.RECEIVER_ID = S2.RECEIVER_ID AND BB.RECV_LN_NBR = S2.RECV_LN_NBR AND BB.STAGED_NUM = S2.STAGED_NUM AND BB.SOURCE_BUS_UNIT = S2.SOURCE_BUS_UNIT AND BB.RECV_SHIP_SEQ_NBR = S2.RECV_SHIP_SEQ_NBR AND BB.DISTRIB_LINE_NUM = S2.DISTRIB_LINE_NUM AND BB.DISTRIB_SEQ_NUM = S2.DISTRIB_SEQ_NUM AND BB.EOEN_PRCS_NAME = CC.EOEN_PRCS_NAME AND BB.EOEN_CATEGORY = CC.EOEN_CATEGORY AND BB.SEQ_NBR_EOEN = CC.SEQ_NBR_EOEN AND CC.EOEN_STATUS = '0')

# 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 PUTAWAY_STATE Character(1) VARCHAR2(1) NOT NULL Putaway State
1=Not Interfaced
2=Interfaced, Not Staged
3=Staged, Not Putaway
4=Putaway Complete
15 IN_SDWC_HASH_KEY Character(60) VARCHAR2(60) NOT NULL Hash