EX_VNDR_PMT_VW(SQL View) |
Index Back |
|---|---|
Supplier Payments |
| SELECT A.VENDOR_SETID , A.VENDOR_ID , A.VNDR_LOC , A.NAME1 , A.VENDOR_NAME_SHORT , A.REMIT_SETID , A.REMIT_VENDOR , A.ADDRESS_SEQ_NUM , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.PYMNT_DT , A.PYMNT_ID , A.EMPLID , B.NAME , A.INVOICE_ID , ' ' , A.CURRENCY_PYMNT , SUM(A.PAID_AMT) FROM PS_EX_SHEET_PYMNT A , PS_PERSONAL_DATA B WHERE A.EMPLID = B.EMPLID AND A.EX_PYMNT_TYPE = 'V' AND A.PYMNT_SELCT_STATUS = 'P' AND A.PYMNT_HOLD = 'N' AND A.POST_STATUS_AP = 'P' AND A.CANCEL_ACTION = 'N' AND A.EX_PYMNT_DEST = 'A' AND A.SEQ_NBR = ( SELECT MAX(C.SEQ_NBR) FROM PS_EX_SHEET_PYMNT C WHERE A.SHEET_ID = C.SHEET_ID AND A.LINE_NBR = C.LINE_NBR AND A.EX_PYMNT_TYPE = C.EX_PYMNT_TYPE) GROUP BY A.VENDOR_SETID , A.VENDOR_ID , A.VNDR_LOC, A.NAME1 , A.VENDOR_NAME_SHORT , A.REMIT_SETID , A.REMIT_VENDOR , A.ADDRESS_SEQ_NUM , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY, A.PYMNT_DT , A.PYMNT_ID , A.EMPLID , B.NAME , A.INVOICE_ID , A.ACCOUNTING_DT , A.CURRENCY_PYMNT |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | VENDOR_SETID | Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 2 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 3 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
| 4 | NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Name 1 |
| 5 | VENDOR_NAME_SHORT | Character(14) | VARCHAR2(14) NOT NULL | Short Vendor Name |
| 6 | REMIT_SETID | Character(5) | VARCHAR2(5) NOT NULL | Remit SetID |
| 7 | REMIT_VENDOR | Character(10) | VARCHAR2(10) NOT NULL | Remit Vendor |
| 8 | ADDRESS_SEQ_NUM | Number(5,0) | INTEGER NOT NULL | Address Sequence Number |
| 9 | BANK_SETID | Character(5) | VARCHAR2(5) NOT NULL | The PeopleSoft tableset ID associated with a given bank/counterparty. |
| 10 | BANK_CD | Character(5) | VARCHAR2(5) NOT NULL | Bank Code |
| 11 | 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 |
| 12 | PYMNT_DT | Date(10) | DATE | Payment Date |
| 13 | PYMNT_ID | Character(10) | VARCHAR2(10) NOT NULL | Payment Number |
| 14 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 15 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 16 | INVOICE_ID | Character(30) | VARCHAR2(30) NOT NULL | Invoice Number |
| 17 | EX_VENDOR_ALL | Character(1) | VARCHAR2(1) NOT NULL |
All Suppliers
A=All Suppliers S=Specify |
| 18 | CURRENCY_PYMNT | Character(3) | VARCHAR2(3) NOT NULL | Payment Currency |
| 19 | PAID_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Paid Amount |