AP_PMTS_VW2(SQL View) |
Index Back |
|---|---|
AP Payments Revaluation ViewSource view for EP_AP_PAYMENTS_KIB |
| SELECT P.BANK_SETID , P.BANK_CD , P.BANK_ACCT_KEY , P.PYMNT_ID , P.BANK_SETID %Concat '/' %Concat P.BANK_CD %Concat '/' %Concat P.BANK_ACCT_KEY BANK_ACCT_KEY_FLD , P.PAYMENT_KEYS , P.UNIQUE_IDENTIFIER , P.BUSINESS_UNIT , P.INVOICE_ID , P.DISCOUNT_XLAT , P.VOUCHER_STYLE , P.TRANS_TYPE_KIB , P.DESCRSHORT , CASE WHEN P.DAYS_DURATION > 60 THEN '61+ Days' WHEN P.DAYS_DURATION > 45 THEN '46 to 60 Days' WHEN P.DAYS_DURATION > 30 THEN '31 to 45 Days' WHEN P.DAYS_DURATION > 15 THEN '16 to 30 Days' WHEN P.DAYS_DURATION > 10 THEN '11 to 15 Days' WHEN P.DAYS_DURATION > 00 THEN '01 to 10 Days' ELSE 'n/a' END AP_TPERIOD_NAME , V.RPTG_CURRENCY_CD , V.RPTG_CURRENCY_DESC , %Round(%DecMult(%DecDiv(P.PAID_AMT,R.RATE_DIV),R.RATE_MULT),V.DECIMAL_POSITIONS) PAID_AMT , %Round(%DecMult(%DecDiv(P.LATE_CHRG_PAID,R.RATE_DIV),R.RATE_MULT),V.DECIMAL_POSITIONS) LATE_CHRG_PAID , CASE WHEN P.DISCOUNT_XLAT = 'N' THEN 0 ELSE %Round(%DecMult(%DecDiv(P.PAID_AMT_DSCNT,R.RATE_DIV),R.RATE_MULT),V.DECIMAL_POSITIONS) END PAID_AMT_DSCNT , CASE WHEN P.VOUCHER_STYLE='PPAY' THEN ( SELECT %Round(%DecMult(%DecDiv(P.AVAILABLE_AMT,RR.RATE_DIV),RR.RATE_MULT),V.DECIMAL_POSITIONS) FROM PS_RT_RATE_TBL RR WHERE RR.RT_RATE_INDEX = V.RT_RATE_INDEX AND RR.TERM = V.TERM AND RR.FROM_CUR = P.TXN_CURRENCY_CD AND RR.TO_CUR = V.RPTG_CURRENCY_CD AND RR.RT_TYPE = V.RT_TYPE AND RR.EFFDT = ( SELECT MAX(EFFDT) FROM PS_RT_RATE_TBL WHERE EFFDT <= P.PYMNT_DT AND RT_RATE_INDEX = RR.RT_RATE_INDEX AND TERM = RR.TERM AND FROM_CUR = RR.FROM_CUR AND TO_CUR = RR.TO_CUR AND RT_TYPE = RR.RT_TYPE ) ) ELSE 0.00 END AVAILABLE_AMT FROM PS_AP_PMTS_VW1 P , PS_AP_PMTS_CURR_VW V , PS_RT_RATE_TBL R WHERE R.RT_RATE_INDEX = V.RT_RATE_INDEX AND R.TERM = V.TERM AND R.FROM_CUR = P.CURRENCY_PYMNT AND R.TO_CUR = V.RPTG_CURRENCY_CD AND R.RT_TYPE = V.RT_TYPE AND R.EFFDT = ( SELECT MAX(EFFDT) FROM PS_RT_RATE_TBL WHERE EFFDT <= P.PYMNT_DT AND RT_RATE_INDEX = R.RT_RATE_INDEX AND TERM = R.TERM AND FROM_CUR = R.FROM_CUR AND TO_CUR = R.TO_CUR AND RT_TYPE = R.RT_TYPE ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BANK_SETID | Character(5) | VARCHAR2(5) NOT NULL | The PeopleSoft tableset ID associated with a given bank/counterparty. |
| 2 | BANK_CD | Character(5) | VARCHAR2(5) NOT NULL | Bank Code |
| 3 | BANK_ACCT_KEY | Character(4) | VARCHAR2(4) NOT NULL | A user defined unique identifier that facilitates the identification of a given account with a given bank |
| 4 | PYMNT_ID | Character(10) | VARCHAR2(10) NOT NULL | Payment Number |
| 5 | BANK_ACCOUNT_KEYS | Character(16) | VARCHAR2(16) NOT NULL | Bank Account |
| 6 | PAYMENT_KEYS | Character(30) | VARCHAR2(30) NOT NULL | Key Values |
| 7 | UNIQUE_IDENTIFIER | Character(36) | VARCHAR2(36) NOT NULL | Generated unique identifier for accessing ledger scenario inquiry drill down criteria. |
| 8 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 9 | INVOICE_ID | Character(30) | VARCHAR2(30) NOT NULL | Invoice Number |
| 10 | DISCOUNT_XLAT | Character(1) | VARCHAR2(1) NOT NULL |
Dicount Status
L=Discount Lost N=Discount Not Applicable T=Discount Taken |
| 11 | VOUCHER_STYLE | Character(4) | VARCHAR2(4) NOT NULL |
Voucher Style
ADJ=Adjustments AMR=Amortization Voucher CLBK=Claim Voucher CORR=Reversal Voucher JRNL=Journal Voucher PPAY=Prepaid Voucher REG=Regular Voucher RGTR=Register Voucher SGLP=Single Payment Voucher THRD=Third Party Voucher TMPL=Template Voucher |
| 12 | TRANS_TYPE_KIB | Character(4) | VARCHAR2(4) NOT NULL |
Voucher Style
ADJ=Adjustment Voucher AMR=Amortization Voucher CLBK=Claim Voucher CORR=Reversal Voucher EX_A=EX - Cash Advance EX_R=EX - Expense Report JRNL=Journal Voucher PPAY=Prepaid Voucher REG=Regular Voucher RGTR=Register Voucher SGLP=Single Payment Voucher THRD=Third Party Voucher TMPL=Template Voucher TR_F=TR - Fee TR_W=TR - EFT Request TR_X=TR - Bank Transfer |
| 13 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 14 | AP_TPERIOD_NAME | Character(15) | VARCHAR2(15) NOT NULL | Period Name |
| 15 | RPTG_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Reporting Currency |
| 16 | RPTG_CURRENCY_DESC | Character(30) | VARCHAR2(30) NOT NULL | Reporting Currency Description |
| 17 | PAID_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Paid Amount |
| 18 | LATE_CHRG_PAID | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Late Charge Paid |
| 19 | PAID_AMT_DSCNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Discount on Paid Amount |
| 20 | AVAILABLE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Prepayment Available Amount |