SCP_ACKPOLST_FL

(SQL View)
 Index Back

PO List without Vouchers

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' , 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 ,'' ,'' ,%DateTimeNull ,%DateNull ,MIN(C.DUE_DT) FROM PS_PO_DISPATCHED A , PS_PO_HDR B ,PS_PO_LINE_SHIP C ,PS_VENDOR VNDR WHERE A.PO_ID = B.PO_ID AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PO_ID = C.PO_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT 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 X WHERE B.BUSINESS_UNIT=X.BUSINESS_UNIT AND B.PO_ID=X.PO_ID AND X.CANCEL_STATUS <> 'X') AND A.DATETIME_DISP = ( SELECT MAX(Z.DATETIME_DISP) FROM PS_PO_DISPATCHED Z WHERE Z.PO_ID = A.PO_ID AND Z.BUSINESS_UNIT = A.BUSINESS_UNIT ) 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 , 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 UNION ALL SELECT A.BUSINESS_UNIT , A.PO_ID , 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 , MIN(C.DUE_DT) FROM PS_PO_HDR_EC A , PS_PO_HDR B ,PS_VENDOR VNDR ,PS_PO_LINE_SHIP C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PO_ID = B.PO_ID AND A.PO_ID = C.PO_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT 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 A.PROCESS_FLG <> 'Y' AND A.PO_ACK_SOURCE IN('SUP','BUY','EDI') AND EXISTS ( SELECT 'X' FROM PS_PO_LINE X WHERE B.BUSINESS_UNIT=X.BUSINESS_UNIT AND B.PO_ID=X.PO_ID AND X.CANCEL_STATUS <> 'X') AND A.PO_ID NOT IN ( SELECT X.PO_ID FROM PS_PO_DISPATCHED X WHERE A.BUSINESS_UNIT = X.BUSINESS_UNIT AND X.DATETIME_DISP > A.DATETIME_CREATE) AND A.DATETIME_CREATE = ( SELECT MAX(Y.DATETIME_CREATE) FROM PS_PO_HDR_EC Y WHERE Y.PO_ID = A.PO_ID AND Y.BUSINESS_UNIT = A.BUSINESS_UNIT ) GROUP BY A.BUSINESS_UNIT , A.PO_ID , 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

# 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 DATETIME_DISP DateTime(26) TIMESTAMP Date and Time Dispatched
5 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
6 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
7 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
8 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
9 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
10 CHNG_ORD_BATCH Number(3,0) SMALLINT NOT NULL Change Order Number
11 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
13 PO_DT Date(10) DATE Date
14 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.
15 BILL_LOCATION Character(10) VARCHAR2(10) NOT NULL Billing Location
16 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
17 SETID Character(5) VARCHAR2(5) NOT NULL SetID
18 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
19 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.
20 EIP_CTL_ID Character(25) VARCHAR2(25) NOT NULL Enterprise Components EIP (enterprise integration point) control field
21 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
SUP=Online (Supplier)
22 DATETIME_CREATE DateTime(26) TIMESTAMP 02/2001 cel WV-802-1.0.
23 ACKNOWLEGE_DT Date(10) DATE Acknowledge Date
24 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.