PO_ANALYTICS_VW

(SQL View)
 Index Back

Purchase Order Anlytics View


SELECT %Coalesce(A.BUSINESS_UNIT , 'B:NOB') , A.BUSINESS_UNIT , F.DESCR , A.PO_ID , B.LINE_NBR , C.SCHED_NBR , %Coalesce(A.BUSINESS_UNIT , 'B:NOB') || A.PO_ID , %Coalesce(D.BUSINESS_UNIT , 'B:NOB') , D.BUSINESS_UNIT , G.DESCR , D.RECEIVER_ID , D.RECV_LN_NBR , D.RECV_SHIP_SEQ_NBR , %Coalesce(D.BUSINESS_UNIT , 'B:NOB') || A.PO_ID , A.PO_DT , RE.RECEIPT_DT , A.BUYER_ID , H.OPRDEFNDESC , A.PYMNT_TERMS_CD , M.DESCRSHORT , M.DESCR , A.VENDOR_SETID , A.VENDOR_ID , E.NAME1 , A.VNDR_LOC , B.ITM_SETID , B.INV_ITEM_ID , B.DESCR254_MIXED , B.CATEGORY_ID , D.CATEGORY_ID , B.CNTRCT_ID , C.PRICE_PO_BSE , C.DUE_DT , D.DUE_DT , C.SHIPTO_SETID , C.SHIPTO_ID , C.QTY_PO , C.CURRENCY_CD_BASE , C.MERCH_AMT_BSE , C.FREIGHT_TERMS , C.BUSINESS_UNIT_RTV , C.RTV_ID , C.RTV_LN_NBR , D.ITM_SETID , D.INV_ITEM_ID , D.MERCH_AMT_BSE , D.MERCH_AMT_PO_BSE , D.PRICE_PO_BSE , D.QTY_SH_RECVD_SUOM , D.QTY_SH_ACCPT_SUOM , D.QTY_SH_REJCT_SUOM , D.QTY_SH_RTN_SUOM , D.QTY_SH_NETRCV_VUOM , A.LAST_DTTM_UPDATE , '' , '' , A.HOLD_STATUS , A.PO_STATUS , CASE WHEN A.HOLD_STATUS = 'Y' THEN 'Hold' ELSE I.XLATLONGNAME END , A.POA_STATUS , J.XLATLONGNAME , A.DOC_TOL_HDR_STATUS , K.XLATLONGNAME , A.BUDGET_HDR_STATUS , L.XLATLONGNAME , CASE WHEN C.DUE_DT > RE.RECEIPT_DT THEN 'Early' WHEN C.DUE_DT < RE.RECEIPT_DT THEN 'Late' WHEN C.DUE_DT = RE.RECEIPT_DT THEN 'On Time' ELSE NULL END , C.QTY_PO - %Coalesce(RECPT.RECEIVED_QTY,0) , '' ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 , '' FROM (((((( ( (PS_PO_HDR A LEFT OUTER JOIN PS_VENDOR E ON E.VENDOR_ID = A.VENDOR_ID AND E.SETID = A.VENDOR_SETID ) LEFT OUTER JOIN PS_PYMT_TR_EFF_VW M ON M.PYMNT_TERMS_CD = A.PYMNT_TERMS_CD AND M.SETID = A.VENDOR_SETID ) LEFT OUTER JOIN PS_BUS_UNIT_TBL_FS F ON F.BUSINESS_UNIT = A.BUSINESS_UNIT ) LEFT OUTER JOIN PS_OPRID_VW H ON H.OPRID = A.BUYER_ID ) LEFT OUTER JOIN PS_PO_XLAT_VW I ON I.FIELDNAME = 'PO_STATUS' AND I.FIELDVALUE = A.PO_STATUS ) LEFT OUTER JOIN PS_PO_XLAT_VW J ON J.FIELDNAME = 'POA_STATUS' AND J.FIELDVALUE = A.POA_STATUS ) LEFT OUTER JOIN PS_PO_XLAT_VW K ON K.FIELDNAME = 'DOC_TOL_HDR_STATUS' AND K.FIELDVALUE = A.DOC_TOL_HDR_STATUS) LEFT OUTER JOIN PS_PO_XLAT_VW L ON L.FIELDNAME = 'BUDGET_HDR_STATUS' AND L.FIELDVALUE = A.BUDGET_HDR_STATUS) , PS_PO_LINE B , ((((PS_PO_LINE_SHIP C LEFT OUTER JOIN PS_RECV_LN_SHIP D ON C.BUSINESS_UNIT = D.BUSINESS_UNIT_PO AND C.PO_ID = D.PO_ID AND C.LINE_NBR = D.LINE_NBR AND C.SCHED_NBR = D.SCHED_NBR AND D.RECV_SHIP_STATUS <> 'X') LEFT OUTER JOIN PS_RECV_HDR RE ON D.BUSINESS_UNIT = RE.BUSINESS_UNIT AND D.RECEIVER_ID = RE.RECEIVER_ID ) LEFT OUTER JOIN PS_BUS_UNIT_TBL_FS G ON G.BUSINESS_UNIT = D.BUSINESS_UNIT ) LEFT OUTER JOIN ( SELECT REC.BUSINESS_UNIT_PO , REC.PO_ID , REC.LINE_NBR , REC.SCHED_NBR , SUM(REC.QTY_SH_RECVD_SUOM) RECEIVED_QTY FROM PS_RECV_LN_SHIP REC WHERE REC.RECV_SHIP_STATUS <> 'X' GROUP BY REC.BUSINESS_UNIT_PO, REC.PO_ID , REC.LINE_NBR, REC.SCHED_NBR) RECPT ON C.BUSINESS_UNIT = RECPT.BUSINESS_UNIT_PO AND C.PO_ID = RECPT.PO_ID AND C.LINE_NBR = RECPT.LINE_NBR AND C.SCHED_NBR = RECPT.SCHED_NBR) 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.LINE_NBR = C.LINE_NBR AND ((A.PO_STATUS <> 'X' AND C.CANCEL_STATUS <> 'X') OR (A.PO_STATUS = 'PX')) ) UNION SELECT 'B:NOB' , NULL , NULL , NULL , NULL , NULL , NULL , F.BUSINESS_UNIT , F.BUSINESS_UNIT , H.DESCR , F.RECEIVER_ID , F.RECV_LN_NBR , F.RECV_SHIP_SEQ_NBR , %Coalesce(F.BUSINESS_UNIT , 'B:NOB') || F.RECEIVER_ID , G.RECEIPT_DT , G.RECEIPT_DT , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , F.ITM_SETID , F.INV_ITEM_ID , NULL , F.CATEGORY_ID , F.CATEGORY_ID , NULL , NULL , NULL , F.DUE_DT , G.SETID , F.SHIPTO_ID , NULL , F.CURRENCY_CD_BASE , F.MERCH_AMT_BSE , NULL , NULL , NULL , NULL , F.ITM_SETID , F.INV_ITEM_ID , F.MERCH_AMT_BSE , F.MERCH_AMT_PO_BSE , F.PRICE_PO_BSE , F.QTY_SH_RECVD_SUOM , F.QTY_SH_ACCPT_SUOM , F.QTY_SH_REJCT_SUOM , F.QTY_SH_RTN_SUOM , F.QTY_SH_NETRCV_VUOM , G.LAST_DTTM_UPDATE , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , CASE WHEN F.DUE_DT > G.RECEIPT_DT THEN 'Early' WHEN F.DUE_DT < G.RECEIPT_DT THEN 'Late' WHEN F.DUE_DT = G.RECEIPT_DT THEN 'On Time' ELSE NULL END , NULL , NULL ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 , '' FROM PS_RECV_LN_SHIP F , PS_RECV_HDR G , PS_BUS_UNIT_TBL_FS H WHERE ( F.BUSINESS_UNIT = G.BUSINESS_UNIT AND F.RECEIVER_ID = G.RECEIVER_ID AND F.BUSINESS_UNIT_PO = ' ' AND F.PO_ID = ' ' AND H.BUSINESS_UNIT = F.BUSINESS_UNIT AND F.RECV_SHIP_STATUS <> 'X')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit
2 BUSINESS_UNIT_PO2 Character(5) VARCHAR2(5) NOT NULL Purchasing Business Unit
3 BU_PO_DESCR Character(30) VARCHAR2(30) NOT NULL Purchasing Business Unit
4 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
5 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
6 SCHED_NBR Number(3,0) SMALLINT NOT NULL Schedule Number
7 UNIQUE_ID Character(20) VARCHAR2(20) NOT NULL Unique ID
8 BUSINESS_UNIT_RECV Character(5) VARCHAR2(5) NOT NULL Receiving Business Unit
9 REC_BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit for the receiving bank.
10 BU_RECV_DESCR Character(30) VARCHAR2(30) NOT NULL Receiving Business Unit
11 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
12 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
13 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
14 UNIQUE_IDENTIFIER Character(36) VARCHAR2(36) NOT NULL Generated unique identifier for accessing ledger scenario inquiry drill down criteria.
15 PO_DT Date(10) DATE Date
16 RECEIPT_DT Date(10) DATE Received Date
17 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
18 BUYER_DESCR Character(30) VARCHAR2(30) NOT NULL Buyer Desc
19 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.
20 PYMNT_TERMS_DESCR Character(30) VARCHAR2(30) NOT NULL Payment Terms Description
21 PYMT_TERMS_DESCR Character(50) VARCHAR2(50) NOT NULL Payment Terms Description
22 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
23 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
24 VENDOR_NAME1 Character(40) VARCHAR2(40) NOT NULL Vendor Name1
25 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
26 ITM_SETID_PO Character(5) VARCHAR2(5) NOT NULL Item setid PO
27 INV_ITEM_ID_PO Character(18) VARCHAR2(18) NOT NULL Item Number PO
28 DESCR254_MIXED2 Character(254) VARCHAR2(254) NOT NULL Long Description
29 CATEGORY_ID_1 Character(5) VARCHAR2(5) NOT NULL Category ID
30 CATEGORY_ID_2 Character(5) VARCHAR2(5) NOT NULL Category ID
31 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
32 PRICE_PO_BSE Signed Number(17,5) DECIMAL(15,5) NOT NULL Price Base
33 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.
34 DUE_DT_OLD 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.
35 SHIPTO_SETID Character(5) VARCHAR2(5) NOT NULL ShipTo SetID
36 SHIPTO_ID Character(10) VARCHAR2(10) NOT NULL Ship To Location
37 QTY_PO Number(16,4) DECIMAL(15,4) NOT NULL Purchase Order Quantity
38 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
39 MERCH_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Merchandise Amount
40 FREIGHT_TERMS Character(10) VARCHAR2(10) NOT NULL Freight Terms Code
41 BUSINESS_UNIT_RTV Character(5) VARCHAR2(5) NOT NULL RTV Business Unit
42 RTV_ID Character(10) VARCHAR2(10) NOT NULL Return To Vendor ID
43 RTV_LN_NBR Number(5,0) INTEGER NOT NULL Return To Vendor Line
44 ITM_SETID_RECV Character(5) VARCHAR2(5) NOT NULL Item Setid Receiver
45 INV_ITEM_ID_RECV Character(18) VARCHAR2(18) NOT NULL Item Number
46 MERCH_AMT_BSE_DIF Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Merch Amt Dif from Balanc
47 MERCH_AMT_PO_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt PO Base
48 PRICE_PO_BSE_SV Signed Number(17,5) DECIMAL(15,5) NOT NULL Price Base
49 QTY_SH_RECVD_SUOM Number(16,4) DECIMAL(15,4) NOT NULL Receipt Quantity
50 QTY_SH_ACCPT_SUOM Number(16,4) DECIMAL(15,4) NOT NULL Accepted Quantity
51 QTY_SH_REJCT_SUOM Number(16,4) DECIMAL(15,4) NOT NULL Rejected Quantity
52 QTY_SH_RTN_SUOM Number(16,4) DECIMAL(15,4) NOT NULL Qty Returned in Std UOM
53 QTY_SH_NETRCV_VUOM Number(16,4) DECIMAL(15,4) NOT NULL Net Receipt Quantity
54 LAST_DTTM_UPDATE DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
55 PO_COLLAB_URL Character(254) VARCHAR2(254) NOT NULL 02/01/01 rml CN#WV802-1.0
56 DOC_URL Character(254) VARCHAR2(254) NOT NULL Document Url
57 HOLD_STATUS Character(1) VARCHAR2(1) NOT NULL Hold From Further Processing
58 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
59 PO_STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL PO Status Description
60 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
61 POA_STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL POA Status Description
62 DOC_TOL_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL Document Heade Document Tolerance Status
E=Error
N=Not Checked
R=Reset
V=Valid
63 DOC_TOL_STS_DESCR Character(30) VARCHAR2(30) NOT NULL Document Tolerence Status
64 BUDGET_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL Budget Checking Header Status
E=Error in Budget Check
I=Document In Processing
N=Not Budget Checked
P=Provisionally Valid
V=Valid
65 BUD_STS_DESCR Character(30) VARCHAR2(30) NOT NULL Budget Status Description
66 DELIVERY_PERF Character(10) VARCHAR2(10) NOT NULL Delivery Performance
67 PENDING_QTY Signed Number(13,4) DECIMAL(11,4) NOT NULL Pending Quantity
68 PO_DRILL_URL Character(254) VARCHAR2(254) NOT NULL URL
69 MERCH_AMT_RPT Signed Number(28,3) DECIMAL(26,3) NOT NULL Report Merchandise Amount
70 MERCH_AMT_CNVT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandant Amount Converted
71 MERCH_AMT_EXTND_PO Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt Extended PO
72 RECVD_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Received Amount
73 ACCT_BALANCE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL The monetary balance for a given bank account.
74 REJECT_AMT Signed Number(17,2) DECIMAL(15,2) NOT NULL Reject Amount
75 AMT_RETURN Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Returned
76 NET_BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Netted Amount
77 CURR_CD_KIBANA Character(3) VARCHAR2(3) NOT NULL Insights Reporting Preferences