PO_CNTRCTRV_VW3(SQL View) |
Index Back |
|---|---|
Off Contract Spend View |
| SELECT CH.VENDOR_SETID , CH.VENDOR_ID , R.TO_CUR , CH.SETID , CH.CNTRCT_ID , CH.VERSION_NBR , CH.CNTRCT_PROC_OPT , CH.CNTRCT_BEGIN_DT , CH.CNTRCT_EXPIRE_DT , CH.CURRENCY_CD , %Coalesce(CL.AMT_LINE_RELEASED , 0) , %Round(%DecMult(%DecDiv((%COALESCE(CL.AMT_LINE_RELEASED , 0)), R.RATE_DIV), R.RATE_MULT), C.DECIMAL_POSITIONS) , CH.AMT_CNTRCT_MAX - %Coalesce(CL.AMT_LINE_RELEASED , 0) , 0 , CH.BUYER_ID , CH.MSTR_CNTRCT_ID , CH.RENEWAL_DATE , CH.VNDR_CNTRCT_REF FROM PS_CNTRCT_HDR CH LEFT OUTER JOIN PS_CS_CNT_SUMLN_VW CL ON CL.SETID = CH.SETID AND CL.CNTRCT_ID = CH.CNTRCT_ID , PS_RT_RATE_TBL R , PS_CURRENCY_CD_TBL C WHERE CH.VERSION_STATUS = 'C' AND CH.CNTRCT_PROC_OPT IN ('AP', 'RPOV') AND R.RT_RATE_INDEX = ( SELECT RT_RATE_INDEX FROM PS_RT_INDEX_TBL WHERE DEFAULT_INDEX = 'Y') AND R.TERM = 0 AND R.FROM_CUR = CH.CURRENCY_CD AND R.RT_TYPE = CH.RT_TYPE AND R.EFFDT = ( SELECT MAX(R_ED.EFFDT) FROM PS_RT_RATE_TBL R_ED WHERE R.RT_RATE_INDEX = R_ED.RT_RATE_INDEX AND R.TERM = R_ED.TERM AND R.FROM_CUR = R_ED.FROM_CUR AND R.TO_CUR = R_ED.TO_CUR AND R.RT_TYPE = R_ED.RT_TYPE AND R_ED.EFFDT <= %CurrentDateIn) AND C.CURRENCY_CD = R.TO_CUR AND C.EFFDT = ( SELECT MAX(C2.EFFDT) FROM PS_CURRENCY_CD_TBL C2 WHERE C2.CURRENCY_CD = C.CURRENCY_CD AND C2.EFFDT <= %CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | VENDOR_ID |
Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 3 | CURRENCY_CD |
Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 4 | CNTRCT_SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 5 | CNTRCT_ID |
Character(25) | VARCHAR2(25) NOT NULL | Buying Agreement ID |
| 6 | VERSION_NBR | Number(5,0) | INTEGER NOT NULL | Contract Version number |
| 7 | 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 |
| 8 | CNTRCT_BEGIN_DT | Date(10) | DATE | Contract Begin Date |
| 9 | CNTRCT_EXPIRE_DT | Date(10) | DATE | Expire Date |
| 10 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 11 | MERCHANDISE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label |
| 12 | CONV_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 13 | REMAINING_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Remaining Amount |
| 14 | TOT_AMT_RELEASED | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Amount Released |
| 15 | BUYER_ID | Character(30) | VARCHAR2(30) NOT NULL | Buyer |
| 16 | MSTR_CNTRCT_ID | Character(10) | VARCHAR2(10) NOT NULL | Master Contract ID |
| 17 | RENEWAL_DATE | Date(10) | DATE | Renewal Date |
| 18 | VNDR_CNTRCT_REF | Character(30) | VARCHAR2(30) NOT NULL | Supplier Contract Ref |