AP_PMTS_VW1(SQL View) |
Index Back |
|---|---|
AP Payment Table Details ViewPayment source transaction detail level |
| SELECT P.BANK_SETID , P.BANK_CD , P.BANK_ACCT_KEY , P.PYMNT_ID , P.PYMNT_DT , P.CURRENCY_PYMNT , P.BANK_SETID %Concat '/' %Concat P.BANK_CD %Concat '/' %Concat P.BANK_ACCT_KEY %Concat '/' %Concat P.PYMNT_ID "payment_keys" , X.BUSINESS_UNIT %Concat ':' %Concat X.VOUCHER_ID %Concat '/' %Concat %NumToChar(X.PYMNT_CNT) "unique_id" , X.BUSINESS_UNIT , V.INVOICE_ID , X.PAID_AMT , X.LATE_CHRG_PAID , X.PAID_AMT_DSCNT , CASE WHEN P.CANCEL_ACTION <> 'N' THEN 'N' WHEN X.PAID_AMT_DSCNT_BSE = 0 THEN 'N' WHEN V.VOUCHER_STYLE IN ('PPAY' , 'TMPL') THEN 'N' WHEN X.PYMNT_SELCT_STATUS = 'P' AND V.ENTRY_STATUS = 'P' THEN CASE WHEN X.LATE_CHRG_PAID_BSE = 0 THEN CASE WHEN X.PAID_AMT_GROSS_BSE = X.PAID_AMT_BSE THEN 'L' WHEN X.PAID_AMT_GROSS_BSE - X.PAID_AMT_DSCNT_BSE = X.PAID_AMT_BSE THEN 'T' ELSE 'N' END WHEN X.PAID_AMT_GROSS_BSE + X.LATE_CHRG_PAID_BSE = X.PAID_AMT_BSE THEN 'L' WHEN X.PAID_AMT_GROSS_BSE + X.LATE_CHRG_PAID_BSE - X.PAID_AMT_DSCNT_BSE = X.PAID_AMT_BSE THEN 'T' ELSE 'N' END ELSE 'N' END "discount_xlat" , CASE WHEN V.VOUCHER_STYLE = 'PPAY' AND P.PYMNT_STATUS = 'P' THEN ( SELECT MIN(AVAILABLE_AMT) FROM PS_VCHR_PPAY_XREF WHERE BUSINESS_UNIT = X.BUSINESS_UNIT AND VOUCHER_ID = X.VOUCHER_ID ) ELSE 0.00 END "available_amt", V.TXN_CURRENCY_CD, V.VOUCHER_STYLE, V.VOUCHER_STYLE "trans_type_kib", T.DESCRSHORT, CASE WHEN P.PYMNT_STATUS = 'P' THEN %DateDiff(X.DUE_DT, P.PYMNT_DT) ELSE 0 END "days_duration" FROM PS_PAYMENT_TBL P , PS_PYMNT_VCHR_XREF X , PS_VOUCHER V LEFT OUTER JOIN PS_SET_CNTRL_GROUP S ON S.SETCNTRLVALUE = V.BUSINESS_UNIT AND S.REC_GROUP_ID = 'FS_14' LEFT OUTER JOIN PS_PYMT_TRMS_HDR T ON T.SETID = S.SETID AND T.PYMNT_TERMS_CD = V.PYMNT_TERMS_CD WHERE P.SOURCE_TXN = 'VCHR' AND X.BANK_SETID = P.BANK_SETID AND X.BANK_CD = P.BANK_CD AND X.BANK_ACCT_KEY = P.BANK_ACCT_KEY AND X.PYMNT_ID = P.PYMNT_ID AND X.PAY_CYCLE = P.PAY_CYCLE AND X.PAY_CYCLE_SEQ_NUM = P.PAY_CYCLE_SEQ_NUM AND V.BUSINESS_UNIT = X.BUSINESS_UNIT AND V.VOUCHER_ID = X.VOUCHER_ID UNION ALL SELECT P.BANK_SETID , P.BANK_CD , P.BANK_ACCT_KEY , P.PYMNT_ID , P.PYMNT_DT , P.CURRENCY_PYMNT , P.BANK_SETID %Concat '/' %Concat P.BANK_CD %Concat '/' %Concat P.BANK_ACCT_KEY %Concat '/' %Concat P.PYMNT_ID "payment_keys" , T.SOURCE_BUS_UNIT %Concat ':' %Concat T.TR_SOURCE_CD %Concat '/' %Concat T.TR_SOURCE_ID "unique_id" , T.SOURCE_BUS_UNIT , T.TR_SOURCE_ID , T.PAID_AMT , 0.00 "late_charge_paid" , 0.00 "paid_amt_dscnt" , 'N' "discount_xlat" , 0.00 "available_amt" , T.CURRENCY_CD , 'n/a' "voucher_style" , CASE WHEN T.TR_SOURCE_CD='W' THEN 'TR_W' WHEN T.TR_SOURCE_CD='X' THEN 'TR_X' ELSE 'TR_F' END "trans_type_kib" , 'n/a' "descrshort" , %DateDiff(T.BUSINESS_DATE, P.PYMNT_DT) "days_duration" FROM PS_PAYMENT_TBL P , PS_CASH_FLOW_TR T WHERE P.SOURCE_TXN IN ('TR', 'TRET') AND T.BANK_SETID = P.BANK_SETID AND T.BANK_CD = P.BANK_CD AND T.BANK_ACCT_KEY = P.BANK_ACCT_KEY AND T.PYMNT_ID = P.PYMNT_ID UNION ALL SELECT P.BANK_SETID , P.BANK_CD , P.BANK_ACCT_KEY , P.PYMNT_ID , P.PYMNT_DT , P.CURRENCY_PYMNT , P.BANK_SETID %Concat '/' %Concat P.BANK_CD %Concat '/' %Concat P.BANK_ACCT_KEY %Concat '/' %Concat P.PYMNT_ID "payment_keys" , A.BUSINESS_UNIT_GL %Concat ':' %Concat A.ADVANCE_ID %Concat '/' %Concat %NumToChar(A.LINE_NBR) %Concat '/' %Concat %NumToChar(A.SEQ_NBR) %Concat '/' %Concat A.EX_PYMNT_TYPE "unique_id" , A.BUSINESS_UNIT_GL , A.ADVANCE_ID , A.PAID_AMT , 0.00 "late_charge_paid" , 0.00 "paid_amt_dscnt" , 'N' "discount_xlat" , 0.00 "available_amt" , A.TXN_CURRENCY_CD , 'n/a' "voucher_style" , 'EX_A' "trans_type_kib" , 'n/a' "descrshort" , %DateDiff(A.DUE_DT, P.PYMNT_DT) "days_duration" FROM PS_PAYMENT_TBL P , PS_EX_ADVNCE_PYMNT A WHERE P.SOURCE_TXN = 'EXAD' AND A.BANK_SETID = P.BANK_SETID AND A.BANK_CD = P.BANK_CD AND A.BANK_ACCT_KEY = P.BANK_ACCT_KEY AND A.PYMNT_ID = P.PYMNT_ID UNION ALL SELECT P.BANK_SETID , P.BANK_CD , P.BANK_ACCT_KEY , P.PYMNT_ID , P.PYMNT_DT , P.CURRENCY_PYMNT , P.BANK_SETID %Concat '/' %Concat P.BANK_CD %Concat '/' %Concat P.BANK_ACCT_KEY %Concat '/' %Concat P.PYMNT_ID "payment_keys" , E.BUSINESS_UNIT_GL %Concat ':' %Concat E.SHEET_ID %Concat '/' %Concat %NumToChar(E.LINE_NBR) %Concat '/' %Concat %NumToChar(E.SEQ_NBR) %Concat '/' %Concat E.EX_PYMNT_TYPE "unique_id" , E.BUSINESS_UNIT_GL , E.SHEET_ID , E.PAID_AMT , 0.00 "late_charge_paid" , 0.00 "paid_amt_dscnt" , 'N' "discount_xlat" , 0.00 "available_amt" , E.TXN_CURRENCY_CD , 'n/a' "voucher_style" , 'EX_R' "trans_type_kib" , 'n/a' "descrshort" , %DateDiff(E.DUE_DT, P.PYMNT_DT) "days_duration" FROM PS_PAYMENT_TBL P , PS_EX_SUMM_PYMNT E WHERE P.SOURCE_TXN = 'EXPN' AND E.BANK_SETID = P.BANK_SETID AND E.BANK_CD = P.BANK_CD AND E.BANK_ACCT_KEY = P.BANK_ACCT_KEY AND E.PYMNT_ID = P.PYMNT_ID |
| # | 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 | PYMNT_DT | Date(10) | DATE | Payment Date |
| 6 | CURRENCY_PYMNT | Character(3) | VARCHAR2(3) NOT NULL | Payment Currency |
| 7 | PAYMENT_KEYS | Character(30) | VARCHAR2(30) NOT NULL | Key Values |
| 8 | UNIQUE_IDENTIFIER | Character(36) | VARCHAR2(36) NOT NULL | Generated unique identifier for accessing ledger scenario inquiry drill down criteria. |
| 9 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 10 | INVOICE_ID | Character(30) | VARCHAR2(30) NOT NULL | Invoice Number |
| 11 | PAID_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Paid Amount |
| 12 | LATE_CHRG_PAID | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Late Charge Paid |
| 13 | PAID_AMT_DSCNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Discount on Paid Amount |
| 14 | DISCOUNT_XLAT | Character(1) | VARCHAR2(1) NOT NULL |
Dicount Status
L=Discount Lost N=Discount Not Applicable T=Discount Taken |
| 15 | AVAILABLE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Prepayment Available Amount |
| 16 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 17 | 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 |
| 18 | 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 |
| 19 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 20 | DAYS_DURATION | Number(5,1) | DECIMAL(4,1) NOT NULL | Days Duration |