APOP_PYMTVCH_VW(SQL View) |
Index Back |
|---|---|
First Payment Date ViewView used to get the first payment date for a Voucher and the difference between the first payment and the Invoice Date/Invoice Received Date. Copy of MV - APOP_PYMTVCH_MV |
| SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , PYMNT.PYMNT_ID_REF , A.INVOICE_ID , A.VENDOR_SETID , A.VENDOR_ID , A.GROSS_AMT , A.DSCNT_AMT , A.TXN_CURRENCY_CD , A.PYMNT_TERMS_CD , XREF.PYMNT_HANDLING_CD , A.INVOICE_DT , XREF.DUE_DT , MIN(PYMNT.PYMNT_DT) , CASE WHEN (A.INVOICE_DT IS NULL OR A.INVOICE_DT >= MIN(PYMNT.PYMNT_DT)) THEN 0 ELSE %DateDiff(A.INVOICE_DT, MIN(PYMNT.PYMNT_DT)) END , CASE WHEN (A.INVOICE_RCPT_DT IS NULL OR A.INVOICE_RCPT_DT >= MIN(PYMNT.PYMNT_DT)) THEN 0 ELSE %DateDiff(A.INVOICE_RCPT_DT, MIN(PYMNT.PYMNT_DT)) END , MIN(XREF.PYMNT_CNT) , MIN(XREF.BANK_SETID) , MIN(XREF.BANK_CD) , MIN(XREF.BANK_ACCT_KEY) , MIN(XREF.PYMNT_METHOD) FROM %Table(APOP_INVOICE_VW) A , %Table(APOP_PVCHXRF_VW) XREF , %Table(APOP_BSEPYMT_VW) PYMNT WHERE XREF.PYMNT_SELCT_STATUS = 'P' AND XREF.BUSINESS_UNIT = A.BUSINESS_UNIT AND XREF.VOUCHER_ID = A.VOUCHER_ID AND PYMNT.BANK_SETID = XREF.BANK_SETID AND PYMNT.BANK_CD = XREF.BANK_CD AND PYMNT.BANK_ACCT_KEY = XREF.BANK_ACCT_KEY AND PYMNT.PYMNT_ID = XREF.PYMNT_ID AND XREF.PYMNT_CNT = ( SELECT MAX(XREF_1.PYMNT_CNT) FROM PS_APOP_PVCHXRF_VW XREF_1 WHERE XREF_1.BUSINESS_UNIT = XREF.BUSINESS_UNIT AND XREF_1.VOUCHER_ID = XREF.VOUCHER_ID AND XREF_1.PYMNT_SELCT_STATUS = 'P' ) AND PYMNT.PYMNT_DT = ( SELECT MIN(PYMNT2.PYMNT_DT) FROM %Table(APOP_BSEPYMT_VW) PYMNT2 WHERE PYMNT.BANK_SETID = PYMNT2.BANK_SETID AND PYMNT.BANK_CD = PYMNT2.BANK_CD AND PYMNT.BANK_ACCT_KEY = PYMNT2.BANK_ACCT_KEY AND PYMNT2.PYMNT_ID IN ( SELECT XREF2.PYMNT_ID FROM PS_APOP_PVCHXRF_VW XREF2 WHERE XREF2.PYMNT_SELCT_STATUS = 'P' AND XREF2.BUSINESS_UNIT = A.BUSINESS_UNIT AND XREF2.VOUCHER_ID = A.VOUCHER_ID )) GROUP BY A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_ID, A.VENDOR_SETID , A.VENDOR_ID, A.GROSS_AMT , A.DSCNT_AMT, A.TXN_CURRENCY_CD , A.PYMNT_TERMS_CD , A.INVOICE_DT, XREF.DUE_DT, INVOICE_RCPT_DT, PYMNT.PYMNT_ID_REF , XREF.PYMNT_HANDLING_CD |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | VOUCHER_ID |
Character(8) | VARCHAR2(8) NOT NULL | Voucher ID |
| 3 | PYMNT_ID_REF |
Character(20) | VARCHAR2(20) NOT NULL | Payment Reference |
| 4 | INVOICE_ID | Character(30) | VARCHAR2(30) NOT NULL | Invoice Number |
| 5 | VENDOR_SETID | Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 6 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 7 | GROSS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Invoice Amount |
| 8 | DSCNT_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Discount Amount |
| 9 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 10 | PYMNT_TERMS_CD | Character(5) | VARCHAR2(5) NOT NULL | Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers. |
| 11 | PYMNT_HANDLING_CD | Character(2) | VARCHAR2(2) NOT NULL | Payment Handling |
| 12 | INVOICE_DT | Date(10) | DATE | Invoice Date |
| 13 | DUE_DT | Date(10) | DATE | Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received. |
| 14 | PYMNT_DT | Date(10) | DATE | Payment Date |
| 15 | AP_DAYSBETWEEN | Number(12,0) | DECIMAL(12) NOT NULL | Used to keep the difference between 2 dates. |
| 16 | AP_DAYSBETWEEN2 | Number(12,0) | DECIMAL(12) NOT NULL | Used to keep the difference between 2 dates. |
| 17 | PYMNT_CNT | Number(5,0) | INTEGER NOT NULL | Payments |
| 18 | BANK_SETID | Character(5) | VARCHAR2(5) NOT NULL | The PeopleSoft tableset ID associated with a given bank/counterparty. |
| 19 | BANK_CD | Character(5) | VARCHAR2(5) NOT NULL | Bank Code |
| 20 | 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 |
| 21 | PYMNT_METHOD | Character(3) | VARCHAR2(3) NOT NULL |
Payment Method
ACH=Automated Clearing House BEF=Draft - Customer EFT BOO=Draft - Customer Initiated CHK=System Check D=Deposit DD=Direct Debit DFT=Draft - Supplier Initiated DRA=Draft EFT=Electronic Funds Transfer GE=Giro - EFT GM=Giro - Manual LC=Letter of Credit MAN=Manual Check TRW=Treasury Wire WIR=Wire Transfer |