SCP_POLSTBUY_FL

(SQL View)
 Index Back

POA Buyer Listing View

Primary view for POA Buyer landing page 10/14/22 KAJ 34703130/34525278 We are now filtering away any PO that has all its PO_LINEs in cancelled status.

SELECT A.BUSINESS_UNIT , A.PO_ID , 'NEW' , ( SELECT H1.XLATLONGNAME FROM PSXLATITEM H1 WHERE H1.FIELDNAME = 'PO_POA_STATUS' AND H1.FIELDVALUE = 'NEW' AND H1.EFFDT = ( SELECT MAX(H2.EFFDT) FROM PSXLATITEM H2 WHERE H1.FIELDNAME = H2.FIELDNAME AND H1.FIELDVALUE = H2.FIELDVALUE AND H1.FIELDVALUE = 'NEW' AND H2.EFFDT <= %CurrentDateIn)) AS POADESCR , 'NEW' , ( SELECT E1.XLATLONGNAME FROM PSXLATITEM E1 WHERE E1.FIELDNAME = 'POA_STATUS' AND E1.FIELDVALUE = 'WR' AND E1.EFFDT = ( SELECT MAX(E2.EFFDT) FROM PSXLATITEM E2 WHERE E1.FIELDNAME = E2.FIELDNAME AND E1.FIELDVALUE = E2.FIELDVALUE AND E1.FIELDVALUE = 'WR' AND E2.EFFDT <= %CurrentDateIn)) AS POASTATXT , A.DATETIME_DISP , B.VENDOR_ID , B.VENDOR_SETID , VNDR.NAME1 , B.VNDR_LOC , B.PO_STATUS , ( SELECT G1.XLATSHORTNAME FROM PSXLATITEM G1 WHERE G1.FIELDNAME='PO_STATUS' AND G1.FIELDVALUE=B.PO_STATUS AND G1.EFFDT=( SELECT MAX(G2.EFFDT) FROM PSXLATITEM G2 WHERE G1.FIELDNAME=G2.FIELDNAME AND G1.FIELDVALUE=G2.FIELDVALUE AND G1.FIELDVALUE=B.PO_STATUS AND G2.EFFDT <= %CurrentDateIn)) AS DESCR3 , B.CHNG_ORD_BATCH , (CASE WHEN B.CHNG_ORD_BATCH > 0 THEN ( SELECT MCT.MESSAGE_TEXT FROM PSMSGCATDEFN MCT WHERE MCT.MESSAGE_SET_NBR = 10258 AND MCT.MESSAGE_NBR = 9) %Concat %NumToChar(B.CHNG_ORD_BATCH) ELSE ( SELECT F1.XLATLONGNAME FROM PSXLATITEM F1 WHERE F1.FIELDNAME = 'PO_STATUS' AND F1.FIELDVALUE = B.PO_STATUS AND F1.EFFDT = ( SELECT MAX(F2.EFFDT) FROM PSXLATITEM F2 WHERE F1.FIELDNAME = F2.FIELDNAME AND F1.FIELDVALUE = F2.FIELDVALUE AND F1.FIELDVALUE = B.PO_STATUS AND F2.EFFDT <= %CurrentDateIn)) END) AS POSTATXT , B.BUYER_ID , ( SELECT OD.OPRDEFNDESC FROM PS_OPRID_VW OD WHERE B.BUYER_ID = OD.OPRID) AS BUYER_NAME , B.CURRENCY_CD , B.PO_DT , B.PYMNT_TERMS_CD , B.BILL_LOCATION , B.RECV_STATUS , B.VENDOR_SETID , B.POA_STATUS , CASE WHEN B.POA_STATUS='WR' THEN 'Y' ELSE 'N' END , B.PO_REF , 'NEXT' EIP_CTL_ID , 'NA' , ( SELECT G1.XLATSHORTNAME FROM PSXLATITEM G1 WHERE G1.FIELDNAME = 'SCP_PO_ACK_SOURCE' AND G1.FIELDVALUE = 'NA' AND G1.EFFDT = ( SELECT MAX(G2.EFFDT) FROM PSXLATITEM G2 WHERE G1.FIELDNAME = G2.FIELDNAME AND G1.FIELDVALUE = G2.FIELDVALUE AND G1.FIELDVALUE = 'NA' AND G2.EFFDT <= %CurrentDateIn)) AS POADESCR4 , NULL , NULL , MIN(C.DUE_DT) , %DatePart(A.DATETIME_DISP) , 'NEXT&Action=A' , OP.OPRID , 'A' FROM PS_PO_DISPATCHED A , PS_PO_HDR B , PS_PO_LINE_SHIP C , PS_VENDOR VNDR , PSOPRDEFN OP , PS_OPR_DEF_TBL_PM PM WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PO_ID = B.PO_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PO_ID = C.PO_ID AND B.PO_STATUS = 'D' AND B.RECV_STATUS IN ('N', 'O', 'P', 'H', 'R') AND B.VENDOR_SETID =VNDR.SETID AND B.VENDOR_ID =VNDR.VENDOR_ID AND EXISTS ( SELECT 'X' FROM PS_PO_LINE PL2 WHERE C.BUSINESS_UNIT = PL2.BUSINESS_UNIT AND C.PO_ID = PL2.PO_ID AND C.LINE_NBR = PL2.LINE_NBR AND PL2.CANCEL_STATUS <> 'X') AND (OP.OPRID =PM.OPRID AND PM.ALL_BUYERS_FLG = 'Y' ) AND A.DATETIME_DISP = ( SELECT MAX(Z.DATETIME_DISP) FROM PS_PO_DISPATCHED Z WHERE Z.BUSINESS_UNIT = A.BUSINESS_UNIT AND Z.PO_ID = A.PO_ID ) AND NOT EXISTS ( SELECT 'X' FROM PS_PO_POA_HDR_HIST X WHERE X.BUSINESS_UNIT = A.BUSINESS_UNIT AND X.PO_ID = A.PO_ID AND X.CHNG_ORD_BATCH = B.CHNG_ORD_BATCH AND X.DATETIME_DISP = A.DATETIME_DISP ) AND NOT EXISTS ( SELECT 'X' FROM PS_PO_HDR_EC Y WHERE Y.BUSINESS_UNIT = A.BUSINESS_UNIT AND Y.PO_ID = A.PO_ID AND Y.CHNG_ORD_BATCH = B.CHNG_ORD_BATCH AND Y.DATETIME_DISP = A.DATETIME_DISP ) GROUP BY A.BUSINESS_UNIT , A.PO_ID , 'NEW', 'NEW', A.DATETIME_DISP , B.VENDOR_ID , B.VENDOR_SETID , VNDR.NAME1 , B.VNDR_LOC , B.PO_STATUS , B.CHNG_ORD_BATCH, B.BUYER_ID , B.CURRENCY_CD , B.PO_DT , B.PYMNT_TERMS_CD , B.BILL_LOCATION , B.RECV_STATUS , B.VENDOR_SETID , B.POA_STATUS , B.PO_REF ,%DatePart(A.DATETIME_DISP), 'N' , OP.OPRID UNION SELECT A.BUSINESS_UNIT , A.PO_ID , 'NEW' , ( SELECT H1.XLATLONGNAME FROM PSXLATITEM H1 WHERE H1.FIELDNAME = 'PO_POA_STATUS' AND H1.FIELDVALUE = 'NEW' AND H1.EFFDT = ( SELECT MAX(H2.EFFDT) FROM PSXLATITEM H2 WHERE H1.FIELDNAME = H2.FIELDNAME AND H1.FIELDVALUE = H2.FIELDVALUE AND H1.FIELDVALUE = 'NEW' AND H2.EFFDT <= %CurrentDateIn)) AS POADESCR , 'NEW' , ( SELECT E1.XLATLONGNAME FROM PSXLATITEM E1 WHERE E1.FIELDNAME = 'POA_STATUS' AND E1.FIELDVALUE = 'WR' AND E1.EFFDT = ( SELECT MAX(E2.EFFDT) FROM PSXLATITEM E2 WHERE E1.FIELDNAME = E2.FIELDNAME AND E1.FIELDVALUE = E2.FIELDVALUE AND E1.FIELDVALUE = 'WR' AND E2.EFFDT <= %CurrentDateIn)) AS POASTATXT , A.DATETIME_DISP , B.VENDOR_ID , B.VENDOR_SETID , VNDR.NAME1 , B.VNDR_LOC , B.PO_STATUS , ( SELECT G1.XLATSHORTNAME FROM PSXLATITEM G1 WHERE G1.FIELDNAME='PO_STATUS' AND G1.FIELDVALUE=B.PO_STATUS AND G1.EFFDT=( SELECT MAX(G2.EFFDT) FROM PSXLATITEM G2 WHERE G1.FIELDNAME=G2.FIELDNAME AND G1.FIELDVALUE=G2.FIELDVALUE AND G1.FIELDVALUE=B.PO_STATUS AND G2.EFFDT <= %CurrentDateIn)) AS DESCR3 , B.CHNG_ORD_BATCH , (CASE WHEN B.CHNG_ORD_BATCH > 0 THEN ( SELECT MCT.MESSAGE_TEXT FROM PSMSGCATDEFN MCT WHERE MCT.MESSAGE_SET_NBR = 10258 AND MCT.MESSAGE_NBR = 9) %Concat %NumToChar(B.CHNG_ORD_BATCH) ELSE ( SELECT F1.XLATLONGNAME FROM PSXLATITEM F1 WHERE F1.FIELDNAME = 'PO_STATUS' AND F1.FIELDVALUE = B.PO_STATUS AND F1.EFFDT = ( SELECT MAX(F2.EFFDT) FROM PSXLATITEM F2 WHERE F1.FIELDNAME = F2.FIELDNAME AND F1.FIELDVALUE = F2.FIELDVALUE AND F1.FIELDVALUE = B.PO_STATUS AND F2.EFFDT <= %CurrentDateIn)) END) AS POSTATXT , B.BUYER_ID , ( SELECT OD.OPRDEFNDESC FROM PS_OPRID_VW OD WHERE B.BUYER_ID = OD.OPRID) AS BUYER_NAME , B.CURRENCY_CD , B.PO_DT , B.PYMNT_TERMS_CD , B.BILL_LOCATION , B.RECV_STATUS , B.VENDOR_SETID , B.POA_STATUS , CASE WHEN B.POA_STATUS='WR' THEN 'Y' ELSE 'N' END , B.PO_REF , 'NEXT' EIP_CTL_ID , 'NA' , ( SELECT G1.XLATSHORTNAME FROM PSXLATITEM G1 WHERE G1.FIELDNAME = 'SCP_PO_ACK_SOURCE' AND G1.FIELDVALUE = 'NA' AND G1.EFFDT = ( SELECT MAX(G2.EFFDT) FROM PSXLATITEM G2 WHERE G1.FIELDNAME = G2.FIELDNAME AND G1.FIELDVALUE = G2.FIELDVALUE AND G1.FIELDVALUE = 'NA' AND G2.EFFDT <= %CurrentDateIn)) AS POADESCR4 , NULL , NULL , MIN(C.DUE_DT) , %DatePart(A.DATETIME_DISP) , 'NEXT&Action=A' , OP.OPRID , 'A' FROM PS_PO_DISPATCHED A , PS_PO_HDR B , PS_PO_LINE_SHIP C , PS_VENDOR VNDR , PSOPRDEFN OP , PS_OPR_PO_BUYER OPR WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PO_ID = B.PO_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PO_ID = C.PO_ID AND B.PO_STATUS = 'D' AND B.RECV_STATUS IN ('N', 'O', 'P', 'H', 'R') AND B.VENDOR_SETID =VNDR.SETID AND B.VENDOR_ID =VNDR.VENDOR_ID AND EXISTS ( SELECT 'X' FROM PS_PO_LINE PL2 WHERE C.BUSINESS_UNIT = PL2.BUSINESS_UNIT AND C.PO_ID = PL2.PO_ID AND C.LINE_NBR = PL2.LINE_NBR AND PL2.CANCEL_STATUS <> 'X') AND( OP.OPRID =OPR.OPRID AND B.BUYER_ID=OPR.BUYER_ID AND UPDATE_AUTH ='Y' ) AND A.DATETIME_DISP = ( SELECT MAX(Z.DATETIME_DISP) FROM PS_PO_DISPATCHED Z WHERE Z.BUSINESS_UNIT = A.BUSINESS_UNIT AND Z.PO_ID = A.PO_ID ) AND NOT EXISTS ( SELECT 'X' FROM PS_PO_POA_HDR_HIST X WHERE X.BUSINESS_UNIT = A.BUSINESS_UNIT AND X.PO_ID = A.PO_ID AND X.CHNG_ORD_BATCH = B.CHNG_ORD_BATCH AND X.DATETIME_DISP = A.DATETIME_DISP ) AND NOT EXISTS ( SELECT 'X' FROM PS_PO_HDR_EC Y WHERE Y.BUSINESS_UNIT = A.BUSINESS_UNIT AND Y.PO_ID = A.PO_ID AND Y.CHNG_ORD_BATCH = B.CHNG_ORD_BATCH AND Y.DATETIME_DISP = A.DATETIME_DISP ) GROUP BY A.BUSINESS_UNIT , A.PO_ID , 'NEW', 'NEW', A.DATETIME_DISP , B.VENDOR_ID , B.VENDOR_SETID , VNDR.NAME1 , B.VNDR_LOC , B.PO_STATUS , B.CHNG_ORD_BATCH, B.BUYER_ID , B.CURRENCY_CD , B.PO_DT , B.PYMNT_TERMS_CD , B.BILL_LOCATION , B.RECV_STATUS , B.VENDOR_SETID , B.POA_STATUS , B.PO_REF ,%DatePart(A.DATETIME_DISP), 'N' , OP.OPRID UNION ALL SELECT A.BUSINESS_UNIT , A.PO_ID , A.PO_POA_STATUS , ( SELECT H1.XLATLONGNAME FROM PSXLATITEM H1 WHERE H1.FIELDNAME = 'PO_POA_STATUS' AND H1.FIELDVALUE = A.PO_POA_STATUS AND H1.EFFDT = ( SELECT MAX(H2.EFFDT) FROM PSXLATITEM H2 WHERE H1.FIELDNAME = H2.FIELDNAME AND H1.FIELDVALUE = H2.FIELDVALUE AND H1.FIELDVALUE = A.PO_POA_STATUS AND H2.EFFDT <= %CurrentDateIn)) AS POADESCR , A.PO_POA_STATUS , ( SELECT E11.XLATLONGNAME FROM PSXLATITEM E11 WHERE E11.FIELDNAME = 'PO_POA_STATUS' AND E11.FIELDVALUE = A.PO_POA_STATUS AND E11.EFFDT = ( SELECT MAX(E12.EFFDT) FROM PSXLATITEM E12 WHERE E11.FIELDNAME = E12.FIELDNAME AND E11.FIELDVALUE = E12.FIELDVALUE AND E11.FIELDVALUE = A.PO_POA_STATUS AND E12.EFFDT <= %CurrentDateIn)) AS POASTATXT , A.DATETIME_DISP , B.VENDOR_ID , B.VENDOR_SETID , VNDR.NAME1 , B.VNDR_LOC , B.PO_STATUS , ( SELECT G1.XLATSHORTNAME FROM PSXLATITEM G1 WHERE G1.FIELDNAME='PO_STATUS' AND G1.FIELDVALUE=B.PO_STATUS AND G1.EFFDT=( SELECT MAX(G2.EFFDT) FROM PSXLATITEM G2 WHERE G1.FIELDNAME=G2.FIELDNAME AND G1.FIELDVALUE=G2.FIELDVALUE AND G1.FIELDVALUE=B.PO_STATUS AND G2.EFFDT <= %CurrentDateIn)) AS DESCR3 , B.CHNG_ORD_BATCH , (CASE WHEN B.CHNG_ORD_BATCH > 0 THEN ( SELECT MCT.MESSAGE_TEXT FROM PSMSGCATDEFN MCT WHERE MCT.MESSAGE_SET_NBR = 10258 AND MCT.MESSAGE_NBR = 9) %Concat %NumToChar(B.CHNG_ORD_BATCH) ELSE ( SELECT F1.XLATLONGNAME FROM PSXLATITEM F1 WHERE F1.FIELDNAME = 'PO_STATUS' AND F1.FIELDVALUE = B.PO_STATUS AND F1.EFFDT = ( SELECT MAX(F2.EFFDT) FROM PSXLATITEM F2 WHERE F1.FIELDNAME = F2.FIELDNAME AND F1.FIELDVALUE = F2.FIELDVALUE AND F1.FIELDVALUE = B.PO_STATUS AND F2.EFFDT <= %CurrentDateIn)) END) AS POSTATXT , B.BUYER_ID , ( SELECT OD.OPRDEFNDESC FROM PS_OPRID_VW OD WHERE B.BUYER_ID = OD.OPRID) AS BUYER_NAME , B.CURRENCY_CD , B.PO_DT , B.PYMNT_TERMS_CD , B.BILL_LOCATION , B.RECV_STATUS , B.VENDOR_SETID , B.POA_STATUS , CASE WHEN B.POA_STATUS='WR' THEN 'Y' ELSE 'N' END , B.PO_REF , A.EIP_CTL_ID , A.PO_ACK_SOURCE , ( SELECT G1.XLATSHORTNAME FROM PSXLATITEM G1 WHERE G1.FIELDNAME = 'PO_ACK_SOURCE' AND G1.FIELDVALUE = A.PO_ACK_SOURCE AND G1.EFFDT = ( SELECT MAX(G2.EFFDT) FROM PSXLATITEM G2 WHERE G1.FIELDNAME = G2.FIELDNAME AND G1.FIELDVALUE = G2.FIELDVALUE AND G1.FIELDVALUE = A.PO_ACK_SOURCE AND G2.EFFDT <= %CurrentDateIn)) AS POADESCR4 , A.DATETIME_CREATE , A.ACKNOWLEGE_DT , MIN(C.DUE_DT) , %DatePart(A.DATETIME_DISP) , CASE WHEN A.PO_ACK_SOURCE = 'EDI' THEN A.EIP_CTL_ID %Concat '&Action=A' ELSE A.EIP_CTL_ID %Concat '&Action=U' END , OP.OPRID , CASE WHEN A.PO_ACK_SOURCE = 'EDI' THEN 'A' ELSE 'U' END FROM PS_PO_HDR_EC A , PS_PO_HDR B , PS_VENDOR VNDR , PS_PO_LINE_SHIP C , PSOPRDEFN OP , PS_OPR_DEF_TBL_PM PM WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PO_ID = B.PO_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PO_ID = C.PO_ID AND B.PO_STATUS = 'D' AND B.RECV_STATUS IN ('N', 'O', 'P', 'H', 'R') AND B.VENDOR_SETID =VNDR.SETID AND B.VENDOR_ID =VNDR.VENDOR_ID AND EXISTS ( SELECT 'X' FROM PS_PO_LINE PL2 WHERE C.BUSINESS_UNIT = PL2.BUSINESS_UNIT AND C.PO_ID = PL2.PO_ID AND C.LINE_NBR = PL2.LINE_NBR AND PL2.CANCEL_STATUS <> 'X') AND A.PROCESS_FLG IN ( ' ', 'N') AND A.PO_ACK_SOURCE IN('SUP','BUY','EDI') AND (OP.OPRID =PM.OPRID AND PM.ALL_BUYERS_FLG = 'Y' ) AND A.PO_ID NOT IN ( SELECT X.PO_ID FROM PS_PO_DISPATCHED X WHERE A.BUSINESS_UNIT = X.BUSINESS_UNIT AND A.PO_ID = X.PO_ID AND X.DATETIME_DISP > A.DATETIME_CREATE) GROUP BY A.BUSINESS_UNIT , A.PO_ID , A.PO_POA_STATUS , A.PO_POA_STATUS , A.DATETIME_DISP , B.VENDOR_ID , B.VENDOR_SETID , VNDR.NAME1 , B.VNDR_LOC , B.PO_STATUS , B.CHNG_ORD_BATCH , B.BUYER_ID ,B.CURRENCY_CD , B.PO_DT, B.PYMNT_TERMS_CD , B.BILL_LOCATION , B.RECV_STATUS , B.VENDOR_SETID , B.POA_STATUS , B.PO_REF , A.EIP_CTL_ID, A.PO_ACK_SOURCE, A.DATETIME_CREATE, A.ACKNOWLEGE_DT, %DatePart(A.DATETIME_DISP), A.PROCESS_FLG, OP.OPRID UNION SELECT A.BUSINESS_UNIT , A.PO_ID , A.PO_POA_STATUS , ( SELECT H1.XLATLONGNAME FROM PSXLATITEM H1 WHERE H1.FIELDNAME = 'PO_POA_STATUS' AND H1.FIELDVALUE = A.PO_POA_STATUS AND H1.EFFDT = ( SELECT MAX(H2.EFFDT) FROM PSXLATITEM H2 WHERE H1.FIELDNAME = H2.FIELDNAME AND H1.FIELDVALUE = H2.FIELDVALUE AND H1.FIELDVALUE = A.PO_POA_STATUS AND H2.EFFDT <= %CurrentDateIn)) AS POADESCR , A.PO_POA_STATUS , ( SELECT E11.XLATLONGNAME FROM PSXLATITEM E11 WHERE E11.FIELDNAME = 'PO_POA_STATUS' AND E11.FIELDVALUE = A.PO_POA_STATUS AND E11.EFFDT = ( SELECT MAX(E12.EFFDT) FROM PSXLATITEM E12 WHERE E11.FIELDNAME = E12.FIELDNAME AND E11.FIELDVALUE = E12.FIELDVALUE AND E11.FIELDVALUE = A.PO_POA_STATUS AND E12.EFFDT <= %CurrentDateIn)) AS POASTATXT , A.DATETIME_DISP , B.VENDOR_ID , B.VENDOR_SETID , VNDR.NAME1 , B.VNDR_LOC , B.PO_STATUS , ( SELECT G1.XLATSHORTNAME FROM PSXLATITEM G1 WHERE G1.FIELDNAME='PO_STATUS' AND G1.FIELDVALUE=B.PO_STATUS AND G1.EFFDT=( SELECT MAX(G2.EFFDT) FROM PSXLATITEM G2 WHERE G1.FIELDNAME=G2.FIELDNAME AND G1.FIELDVALUE=G2.FIELDVALUE AND G1.FIELDVALUE=B.PO_STATUS AND G2.EFFDT <= %CurrentDateIn)) AS DESCR3 , B.CHNG_ORD_BATCH , (CASE WHEN B.CHNG_ORD_BATCH > 0 THEN ( SELECT MCT.MESSAGE_TEXT FROM PSMSGCATDEFN MCT WHERE MCT.MESSAGE_SET_NBR = 10258 AND MCT.MESSAGE_NBR = 9) %Concat %NumToChar(B.CHNG_ORD_BATCH) ELSE ( SELECT F1.XLATLONGNAME FROM PSXLATITEM F1 WHERE F1.FIELDNAME = 'PO_STATUS' AND F1.FIELDVALUE = B.PO_STATUS AND F1.EFFDT = ( SELECT MAX(F2.EFFDT) FROM PSXLATITEM F2 WHERE F1.FIELDNAME = F2.FIELDNAME AND F1.FIELDVALUE = F2.FIELDVALUE AND F1.FIELDVALUE = B.PO_STATUS AND F2.EFFDT <= %CurrentDateIn)) END) AS POSTATXT , B.BUYER_ID , ( SELECT OD.OPRDEFNDESC FROM PS_OPRID_VW OD WHERE B.BUYER_ID = OD.OPRID) AS BUYER_NAME , B.CURRENCY_CD , B.PO_DT , B.PYMNT_TERMS_CD , B.BILL_LOCATION , B.RECV_STATUS , B.VENDOR_SETID , B.POA_STATUS , CASE WHEN B.POA_STATUS='WR' THEN 'Y' ELSE 'N' END , B.PO_REF , A.EIP_CTL_ID , A.PO_ACK_SOURCE , ( SELECT G1.XLATS HORTNAME FROM PSXLATITEM G1 WHERE G1.FIELDNAME = 'PO_ACK_SOURCE' AND G1.FIELDVALUE = A.PO_ACK_SOURCE AND G1.EFFDT = ( SELECT MAX(G2.EFFDT) FROM PSXLATITEM G2 WHERE G1.FIELDNAME = G2.FIELDNAME AND G1.FIELDVALUE = G2.FIELDVALUE AND G1.FIELDVALUE = A.PO_ACK_SOURCE AND G2.EFFDT <= %CurrentDateIn)) AS POADESCR4 , A.DATETIME_CREATE , A.ACKNOWLEGE_DT , MIN(C.DUE_DT) , %DatePart(A.DATETIME_DISP) , CASE WHEN A.PO_ACK_SOURCE = 'EDI' THEN A.EIP_CTL_ID %Concat '&Action=A' ELSE A.EIP_CTL_ID %Concat '&Action=U' END , OP.OPRID , CASE WHEN A.PO_ACK_SOURCE = 'EDI' THEN 'A' ELSE 'U' END FROM PS_PO_HDR_EC A , PS_PO_HDR B , PS_VENDOR VNDR , PS_PO_LINE_SHIP C , PSOPRDEFN OP , PS_OPR_PO_BUYER OPR WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PO_ID = B.PO_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PO_ID = C.PO_ID AND B.PO_STATUS = 'D' AND B.RECV_STATUS IN ('N', 'O', 'P', 'H', 'R') AND B.VENDOR_SETID =VNDR.SETID AND B.VENDOR_ID =VNDR.VENDOR_ID AND EXISTS ( SELECT 'X' FROM PS_PO_LINE PL2 WHERE C.BUSINESS_UNIT = PL2.BUSINESS_UNIT AND C.PO_ID = PL2.PO_ID AND C.LINE_NBR = PL2.LINE_NBR AND PL2.CANCEL_STATUS <> 'X') AND A.PROCESS_FLG IN ( ' ', 'N') AND A.PO_ACK_SOURCE IN('SUP','BUY','EDI') AND( OP.OPRID =OPR.OPRID AND B.BUYER_ID=OPR.BUYER_ID AND UPDATE_AUTH ='Y') AND A.PO_ID NOT IN ( SELECT X.PO_ID FROM PS_PO_DISPATCHED X WHERE A.BUSINESS_UNIT = X.BUSINESS_UNIT AND A.PO_ID = X.PO_ID AND X.DATETIME_DISP > A.DATETIME_CREATE) GROUP BY A.BUSINESS_UNIT , A.PO_ID , A.PO_POA_STATUS , A.PO_POA_STATUS , A.DATETIME_DISP , B.VENDOR_ID , B.VENDOR_SETID , VNDR.NAME1 , B.VNDR_LOC , B.PO_STATUS , B.CHNG_ORD_BATCH , B.BUYER_ID ,B.CURRENCY_CD , B.PO_DT, B.PYMNT_TERMS_CD , B.BILL_LOCATION , B.RECV_STATUS , B.VENDOR_SETID , B.POA_STATUS , B.PO_REF , A.EIP_CTL_ID, A.PO_ACK_SOURCE, A.DATETIME_CREATE, A.ACKNOWLEGE_DT, %DatePart(A.DATETIME_DISP), A.PROCESS_FLG, OP.OPRID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit
2 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
3 PO_POA_STATUS Character(3) VARCHAR2(3) NOT NULL 08/07/01 RML Created for Supplier Portal Acknowledgment
BAC=Buyer Accepted
CMP=EDX compare needed
NEW=New
SRP=Supplier Responded
SRV=Supplier Review
4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
5 SCP_PO_POA_STATUS Character(3) VARCHAR2(3) NOT NULL 08/07/01 RML Created for Supplier Portal Acknowledgment
BAC=Buyer Accepted
CMP=EDX compare needed
NEW=Awaiting Acknowledgement
SRP=Supplier Responded
SRV=Supplier Review
6 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
7 DATETIME_DISP DateTime(26) TIMESTAMP Date and Time Dispatched
8 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
9 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
10 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
11 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
12 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
13 DESCR3 Character(30) VARCHAR2(30) NOT NULL Description
14 CHNG_ORD_BATCH Number(3,0) SMALLINT NOT NULL Change Order Number
15 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
16 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
17 OPRDEFNDESC Character(30) VARCHAR2(30) NOT NULL Description field for a user
18 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
19 PO_DT Date(10) DATE Date
20 PYMNT_TERMS_CD Character(5) VARCHAR2(5) NOT NULL Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.
21 BILL_LOCATION Character(10) VARCHAR2(10) NOT NULL Billing Location
22 RECV_STATUS Character(1) VARCHAR2(1) NOT NULL Receipt Status
C=Closed Receipt
H=Hold Receipt
M=Moved to Destination
N=PO Not Received
O=Open
P=PO Partially Received
R=Fully Received
X=Canceled
23 SETID Character(5) VARCHAR2(5) NOT NULL SetID
24 POA_STATUS Character(2) VARCHAR2(2) NOT NULL po ack status field that will be on po_hdr.
AC=Acknowledged with changes
AK=Acknowledged
BA=Approved, Changes Pending
IN=Initial Dispatch Acknowledged
NR=Not Required
SR=Responded, Awaits Review
WD=Awaiting PO Dispatch
WR=Awaiting Acknowledgement
25 SCP_POA_REQUIRED Character(1) VARCHAR2(1) NOT NULL POA Required
N=No
Y=Yes

Default Value: Y

26 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.
27 EIP_CTL_ID Character(25) VARCHAR2(25) NOT NULL Enterprise Components EIP (enterprise integration point) control field
28 SCP_PO_ACK_SOURCE Character(3) VARCHAR2(3) NOT NULL Created on 08/09/01 CN#WV840-4.0 - Purchase Order Acknowledgment source used in PO_HDR_EC
BUY=Online (Buyer)
EDI=EDX
NA=NA
SUP=Online (Supplier)
29 DESCR4 Character(30) VARCHAR2(30) NOT NULL Description
30 DATETIME_CREATE DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0.
31 ACKNOWLEGE_DT Date(10) DATE Acknowledge Date
32 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.
33 SCP_DISPATCHED_DT Date(10) DATE Dispatched Date
34 URL_STRING Character(250) VARCHAR2(250) NOT NULL URL String
35 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
36 FLAG Character(1) VARCHAR2(1) NOT NULL Flag
N=No
Y=Yes