CNTRCT_PVG_VW2

(SQL View)
 Index Back

Contract Spend Pivot Grid View

Uses unions and LOJs to get all amounts on a contract plus category code, plus the buyer name.

SELECT A.SETID , A.CNTRCT_ID , A.VERSION_NBR , 0 , 0 , A.VENDOR_SETID , A.VENDOR_ID , A.CNTRCT_STATUS , A.BUYER_ID , CASE WHEN O.OPRDEFNDESC <> ' ' THEN O.OPRDEFNDESC ELSE ' ' END , A.DESCR , A.CNTRCT_BEGIN_DT , A.CNTRCT_EXPIRE_DT , H.AMT_RELSD_OPEN_ITM , 0 , 0 , H.AMT_RELSD_OPEN_ITM , A.CURRENCY_CD , A.RT_TYPE , ' ' ,' ' , ' ' , ' ' , ' ' , '1' , A.MSTR_CNTRCT_ID , A.RENEWAL_DATE , A.DRAFT_DUE_DATE , A.VNDR_CNTRCT_REF , A.AMT_CNTRCT_MAX , A.CNTRCT_PROC_OPT FROM PS_CNTRCT_HDR A LEFT OUTER JOIN PSOPRDEFN O ON O.OPRID = A.BUYER_ID , PS_CNTRCT_HDR_RLS H WHERE ( A.SETID = H.SETID AND A.CNTRCT_ID = H.CNTRCT_ID AND A.CNTRCT_STATUS IN ('A','H','O','C') AND A.VERSION_STATUS = 'C') UNION ALL SELECT A.SETID , A.CNTRCT_ID , A.VERSION_NBR , B.CNTRCT_LINE_NBR , 0 , A.VENDOR_SETID , A.VENDOR_ID , A.CNTRCT_STATUS , A.BUYER_ID , CASE WHEN O.OPRDEFNDESC <> ' ' THEN O.OPRDEFNDESC ELSE ' ' END , A.DESCR , A.CNTRCT_BEGIN_DT , A.CNTRCT_EXPIRE_DT , 0 , B.AMT_LINE_RELEASED , 0 , B.AMT_LINE_RELEASED , A.CURRENCY_CD , A.RT_TYPE , L.ITM_SETID , CASE WHEN L.INV_ITEM_ID =' ' THEN M.MESSAGE_TEXT ELSE L.INV_ITEM_ID END , L.DESCR254_MIXED , C.CATEGORY_CD , ' ' , '2' , A.MSTR_CNTRCT_ID , A.RENEWAL_DATE , A.DRAFT_DUE_DATE , A.VNDR_CNTRCT_REF , A.AMT_CNTRCT_MAX , A.CNTRCT_PROC_OPT FROM PS_CNTRCT_HDR A LEFT OUTER JOIN PSOPRDEFN O ON O.OPRID = A.BUYER_ID JOIN ( PS_CNTRCT_LIN_RLS B JOIN PS_CNTRCT_LINE L ON L.SETID = B.SETID AND L.CNTRCT_ID = B.CNTRCT_ID AND L.CNTRCT_LINE_NBR = B.CNTRCT_LINE_NBR JOIN PS_CNTRCT_HDR H ON H.VERSION_STATUS = 'C' AND L.SETID = H.SETID AND L.CNTRCT_ID = H.CNTRCT_ID AND L.VERSION_NBR = H.VERSION_NBR JOIN PS_ITM_CAT_TBL C ON L.ITM_SETID = C.SETID AND L.CATEGORY_ID = C.CATEGORY_ID AND %EffdtCheck(ITM_CAT_TBL E, C, H.CNTRCT_BEGIN_DT) JOIN PS_INSTALLATION_PO D ON C.CATEGORY_TYPE = D.CATEGORY_TYPE ) ON B.SETID = A.SETID AND B.CNTRCT_ID = A.CNTRCT_ID JOIN PSMSGCATDEFN M ON M.MESSAGE_SET_NBR=10220 AND M.MESSAGE_NBR=70 WHERE A.CNTRCT_STATUS IN ('A','C','H','O') AND A.VERSION_STATUS = 'C' AND B.AMT_LINE_RELEASED <> 0 UNION ALL SELECT A.SETID , A.CNTRCT_ID , A.VERSION_NBR , 0 , B.CAT_LINE_NBR , A.VENDOR_SETID , A.VENDOR_ID , A.CNTRCT_STATUS , A.BUYER_ID , CASE WHEN O.OPRDEFNDESC <> ' ' THEN O.OPRDEFNDESC ELSE ' ' END , A.DESCR , A.CNTRCT_BEGIN_DT , A.CNTRCT_EXPIRE_DT , 0 , 0 , B.AMT_LINE_RELEASED , B.AMT_LINE_RELEASED , A.CURRENCY_CD , A.RT_TYPE , ' ' , ' ' , ' ' ,' ' , C.CATEGORY_CD , '3' , A.MSTR_CNTRCT_ID , A.RENEWAL_DATE , A.DRAFT_DUE_DATE , A.VNDR_CNTRCT_REF , A.AMT_CNTRCT_MAX , A.CNTRCT_PROC_OPT FROM PS_CNTRCT_HDR A LEFT OUTER JOIN PSOPRDEFN O ON O.OPRID = A.BUYER_ID JOIN ( PS_CNTRCT_CAT_RLS B JOIN PS_CNTRCT_CATEGORY R ON R.SETID = B.SETID AND R.CNTRCT_ID = B.CNTRCT_ID AND R.CAT_LINE_NBR = B.CAT_LINE_NBR JOIN PS_CNTRCT_HDR H ON H.VERSION_STATUS = 'C' AND %Join(COMMON_KEYS, CNTRCT_CATEGORY R,CNTRCT_HDR H) JOIN PS_ITM_CAT_TBL C ON R.CATEGORY_SETID = C.SETID AND R.CATEGORY_ID = C.CATEGORY_ID AND %EffdtCheck(ITM_CAT_TBL E, C, H.CNTRCT_BEGIN_DT) JOIN PS_INSTALLATION_PO D ON C.CATEGORY_TYPE = D.CATEGORY_TYPE ) ON A.SETID = B.SETID AND A.CNTRCT_ID = B.CNTRCT_ID WHERE ( A.CNTRCT_STATUS IN ('A','C','H','O') AND A.VERSION_STATUS = 'C' AND B.AMT_LINE_RELEASED <> 0)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
3 VERSION_NBR Number(5,0) INTEGER NOT NULL Contract Version number
4 CNTRCT_LINE_NBR Number(5,0) INTEGER NOT NULL Buying Agreement Line Nbr
5 CAT_LINE_NBR Number(5,0) INTEGER NOT NULL Contract Category Line Number
6 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
7 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
8 CNTRCT_STATUS Character(1) VARCHAR2(1) NOT NULL Contract Status
A=Approved
C=Closed
H=On-Hold
O=Open
P=Pre-Approved
X=Canceled
9 BUYER_ID Character(30) VARCHAR2(30) NOT NULL Buyer
10 BUYER_DESCR Character(30) VARCHAR2(30) NOT NULL Buyer Desc
11 DESCR Character(30) VARCHAR2(30) NOT NULL Description
12 CNTRCT_BEGIN_DT Date(10) DATE Contract Begin Date
13 CNTRCT_EXPIRE_DT Date(10) DATE Expire Date
14 AMT_RELSD_OPEN_ITM Signed Number(28,3) DECIMAL(26,3) NOT NULL Open Item Released Amount
15 AMT_LINE_RELEASED Signed Number(28,3) DECIMAL(26,3) NOT NULL Line Released Amount
16 AMT_CAT_RELEASED Signed Number(28,3) DECIMAL(26,3) NOT NULL Category Released Amount
17 TOTAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount
18 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
19 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
20 ITM_SETID Character(5) VARCHAR2(5) NOT NULL Item SetID
21 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
22 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
23 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
24 CATEGORY_CODE Character(18) VARCHAR2(18) NOT NULL 01/29/01 rml CN#WV802-1.0 Created to use as search field in supplier's workbench
25 CNTRCT_AMT_TP Character(1) VARCHAR2(1) NOT NULL Contract Amount Type
1=Open Item
2=Line Item
3=Category Line
4=Total Amount
26 MSTR_CNTRCT_ID Character(10) VARCHAR2(10) NOT NULL Master Contract
27 RENEWAL_DATE Date(10) DATE Renewal Date
28 DRAFT_DUE_DATE Date(10) DATE Draft Due Date
29 VNDR_CNTRCT_REF Character(30) VARCHAR2(30) NOT NULL Supplier Contract Ref
30 AMT_CNTRCT_MAX Number(27,3) DECIMAL(26,3) NOT NULL Maximum Amount of Contract.
31 CNTRCT_PROC_OPT Character(4) VARCHAR2(4) NOT NULL Contract Process Option
AP=Recurring Voucher
BPO=Release to Single PO Only
DST=Distributor
GN=General Contract
GRPM=Group Multi Supplier
GRPS=Group Single Supplier
MFG=Manufacturer
PADV=Prepaid Voucher w/ Advance PO
PO=Purchase Order
PPAY=Prepaid Voucher
RPOV=Recurring PO Voucher
SPP=Special Purpose