PO_SSPYMLST_VW1(SQL View) |
Index Back |
---|
SELECT DISTINCT b.remit_vendor , b.remit_setid , b.BANK_SETID , b.BANK_CD , b.BANK_ACCT_KEY , b.PYMNT_ID , b.PYMNT_ID_REF , b.PYMNT_DT , b.PYMNT_AMT , b.CURRENCY_PYMNT , b.PYMNT_METHOD , b.NAME1 , b.NAME2 , %subrec(address_SBR, b) , b.BANK_ACCOUNT_NUM , b.PYMNT_STATUS , b.RECON_STATUS , c.invoice_id , c.invoice_dt FROM ps_pymnt_vchr_xref a , ps_payment_tbl b , PS_VOUCHER c WHERE a.bank_setid = b.bank_setid AND a.bank_cd= b.bank_cd AND a.bank_acct_key = b.bank_acct_key AND a.pymnt_id = b.pymnt_id AND a.business_unit = c.business_unit AND a.voucher_id = c.voucher_id |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
2 | SETID | Character(5) | VARCHAR2(5) NOT NULL |
SetID
Default Value: OPR_DEF_TBL_FS.SETID |
3 | BANK_SETID | Character(5) | VARCHAR2(5) NOT NULL | The PeopleSoft tableset ID associated with a given bank/counterparty. |
4 | BANK_CD | Character(5) | VARCHAR2(5) NOT NULL | Bank Code |
5 | 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 |
6 | PYMNT_ID | Character(10) | VARCHAR2(10) NOT NULL | Payment Number |
7 | PYMNT_ID_REF | Character(20) | VARCHAR2(20) NOT NULL | Payment Reference |
8 | PYMNT_DT | Date(10) | DATE | Payment Date |
9 | PYMNT_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Payment Amount |
10 | CURRENCY_PYMNT | Character(3) | VARCHAR2(3) NOT NULL | Payment Currency |
11 | 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 |
12 | NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Name 1 |
13 | NAME2 | Character(40) | VARCHAR2(40) NOT NULL | Name 2 |
14 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL |
Country
Prompt Table: COUNTRY_TBL |
15 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
16 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
17 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
18 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
19 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
20 | NUM1 | Character(6) | VARCHAR2(6) NOT NULL | Number 1 |
21 | NUM2 | Character(6) | VARCHAR2(6) NOT NULL | Number 2 |
22 | HOUSE_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
House Type
AB=House Boat WW=Trailer |
23 | ADDR_FIELD1 | Character(2) | VARCHAR2(2) NOT NULL | Address Field 1 |
24 | ADDR_FIELD2 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 2 |
25 | ADDR_FIELD3 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 3 |
26 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
27 | STATE | Character(6) | VARCHAR2(6) NOT NULL |
State
Prompt Table: %EDIT_STATE |
28 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
29 | GEO_CODE | Character(11) | VARCHAR2(11) NOT NULL | Geo Code |
30 | IN_CITY_LIMIT | Character(1) | VARCHAR2(1) NOT NULL |
In City Limit
Y/N Table Edit |
31 | BANK_ACCOUNT_NUM | Character(35) | VARCHAR2(35) NOT NULL | The literal unique identifier associated with a given bank/counterparty account. |
32 | 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 |
33 | RECON_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Reconciliation Status
ALG=Alignment Check AMB=Ambiguous Match AMT=Amounts Not Equal CUR=Currency not Defined DAT=Tran Date <> Value Date DPI=Duplicate Input Transactions DUP=Duplicate Transaction MSC=Miscellaneous Transaction NAM=Payee Names Not Equal NTB=Not Found in Statement NTF=Not Found In System NTP=Not Found in POS Data NUL=Null Transaction ID Ref OVR=Overflow PND=Pending Reconciliation REC=Reconciled RPR=Reprinted Check RR=Requires Review - BSAC STP=Stop Payment TYP=Wrong Transaction Type UNR=Unreconciled VOI=Voided Check |
34 | INVOICE_ID | Character(30) | VARCHAR2(30) NOT NULL | Invoice Number |
35 | INVOICE_DT | Date(10) | DATE | Invoice Date |