CC_POLSUM_VW

(SQL View)
 Index Back

ProCard PO Line Sum


SELECT A.BUSINESS_UNIT , A.PO_ID , B.LINE_NBR , A.PO_DT , MIN(A.PO_STATUS) , MIN(B.CANCEL_STATUS) , MIN(C.CURRENCY_CD) , SUM(C.MERCHANDISE_AMT) , MIN(C.CURRENCY_CD_BASE) , SUM(C.MERCH_AMT_BSE) FROM PS_PO_HDR A , PS_PO_LINE B , PS_PO_LINE_SHIP C WHERE A.PO_STATUS IN ('A', 'D') AND A.MID_ROLL_STATUS <> 'Y' AND A.MATCH_STATUS_PO <> 'M' AND B.CANCEL_STATUS = 'A' AND C.CANCEL_STATUS = 'A' AND A.CC_SECURITY_ID = 0 AND %Join(COMMON_KEYS, PO_HDR A, PO_LINE B) AND %Join(COMMON_KEYS, PO_LINE B, PO_LINE_SHIP C) GROUP BY A.BUSINESS_UNIT, A.PO_ID, B.LINE_NBR, A.PO_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
3 PO_LINE_NBR Number(5,0) INTEGER NOT NULL Purchase Order Line Number
4 PO_DT Date(10) DATE Date
5 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
6 CANCEL_STATUS Character(1) VARCHAR2(1) NOT NULL Cancel Status
A=Active
C=Closed
D=Denied
H=Hold
O=Open
P=Pending Approval
X=Canceled
7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
8 MERCHANDISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label
9 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
10 MERCH_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Merchandise Amount