EM_SP_LST10_PAY(SQL View) |
Index Back |
|---|---|
Last 10 PaymentsEBPP-Created 2000/10/22- This view selects the last 10 payments |
| SELECT DISTINCT D.OPRID , RMT.SETID , RMT.VENDOR_ID , RMT.VNDR_LOC , RMT.DESCR , RMT.BENEFICIARY_BANK , RMT.BNK_ID_NBR , RMT.BRANCH_ID , RMT.BENEF_BRANCH , RMT.BANK_ACCOUNT_NUM , RMT.CHECK_DIGIT , C.PYMNT_ID , C.PYMNT_ID_REF , C.PYMNT_DT , C.CURRENCY_PYMNT , C.PYMNT_STATUS , C.PYMNT_AMT , C.BANK_SETID , C.BANK_CD , C.BANK_ACCT_KEY FROM PS_PYMNT_VCHR_XREF A , PS_VOUCHER B , PS_PAYMENT_TBL C , PS_VENDOR_USER D , PS_VNDR_BANK_ACCT RMT WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND A.DUE_DT IS NOT NULL AND C.BANK_SETID = A.BANK_SETID AND C.BANK_CD = A.BANK_CD AND C.BANK_ACCT_KEY = A.BANK_ACCT_KEY AND C.PYMNT_ID = A.PYMNT_ID AND D.SETID = B.VENDOR_SETID AND D.VENDOR_ID = B.VENDOR_ID AND C.PYMNT_STATUS IN ('P','S','V') AND RMT.SETID = A.REMIT_SETID AND RMT.VENDOR_ID = A.REMIT_VENDOR AND RMT.VNDR_LOC = A.VNDR_LOC AND RMT.BANK_ACCT_SEQ_NBR = A.BANK_ACCT_SEQ_NBR AND RMT.EFFDT = ( SELECT MAX(EFFDT) FROM PS_VNDR_BANK_ACCT RMT1 WHERE RMT1.SETID = RMT.SETID AND RMT1.VENDOR_ID = RMT.VENDOR_ID AND RMT1.VNDR_LOC = RMT.VNDR_LOC AND RMT1.BANK_ACCT_SEQ_NBR = RMT.BANK_ACCT_SEQ_NBR AND RMT1.EFFDT <= B.ENTERED_DT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 3 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 4 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
| 5 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 6 | BENEFICIARY_BANK | Character(30) | VARCHAR2(30) NOT NULL | The bank/counterparty associated with a given beneficiary. |
| 7 | BNK_ID_NBR | Character(20) | VARCHAR2(20) NOT NULL | A literal alphanumeric attribute that uniquely identifies a given bank or counterparty. |
| 8 | BRANCH_ID | Character(10) | VARCHAR2(10) NOT NULL | The local branch office identifier associated with a given bank/counterparty. |
| 9 | BENEF_BRANCH | Character(30) | VARCHAR2(30) NOT NULL | Beneficiary Branch field |
| 10 | BANK_ACCOUNT_NUM | Character(35) | VARCHAR2(35) NOT NULL | The literal unique identifier associated with a given bank/counterparty account. |
| 11 | CHECK_DIGIT | Character(2) | VARCHAR2(2) NOT NULL | Check Digit |
| 12 | PYMNT_ID | Character(10) | VARCHAR2(10) NOT NULL | Payment Number |
| 13 | PYMNT_ID_REF | Character(20) | VARCHAR2(20) NOT NULL | Payment Reference |
| 14 | PYMNT_DT | Date(10) | DATE | Payment Date |
| 15 | CURRENCY_PYMNT | Character(3) | VARCHAR2(3) NOT NULL | Payment Currency |
| 16 | PYMNT_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Payment Status
A=Alignment E=ACH/EFT Prenote L=Stale Dated Payment N=Reprinted with same ref number O=Overflow P=Paid R=Replaced S=Stopped T=Undo Escheatment U=Undo Cancel V=Void W=Escheated Check X=Deleted |
| 17 | PYMNT_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Payment Amount |
| 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 |