PYMNT_UFFEC_STL(SQL View) |
Index Back |
|---|---|
EDI Manager Pymt Payment Rcrd1Payments view for payment record derived from Treasury settlement instructions used for outbound payment file created through EDI manager "PAYMENT" map ID. |
| SELECT A.PAY_CYCLE , A.PAY_CYCLE_SEQ_NUM , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.PYMNT_METHOD , A.DFI_ID_QUAL , A.DFI_ID_NUM , A.BANK_ACCOUNT_NUM , A.PYMNT_ID_REF , A.PYMNT_AMT , A.CURRENCY_PYMNT , A.EFT_PYMNT_FMT_CD , A.EFT_TRANS_HANDLING , A.PYMNT_DT , A.NAME1 , A.ADDRESS1 , A.ADDRESS2 , A.ADDRESS3 , A.ADDRESS4 , A.CITY , A.STATE , A.POSTAL , C.COUNTRY_2CHAR , A.IN_CITY_LIMIT , A.COUNTY , A.GEO_CODE , A.NUM1 , A.NUM2 , A.HOUSE_TYPE , B.NAME1_AC , E.BNK_ID_NBR , E.BRANCH_ID , E.CHECK_DIGIT , E.BANK_ACCT_TYPE , E.BENEF_BRANCH , E.BENEF_BRANCH_AC , E.ADDRESS1 , E.ADDRESS2 , E.ADDRESS3 , E.ADDRESS4 , E.CITY , E.STATE , E.POSTAL , J.COUNTRY_2CHAR , E.IN_CITY_LIMIT , E.COUNTY , E.GEO_CODE , A.STTLMNT_DT_EST , B.EFT_DOM_COSTS_CD , B.EFT_CORR_COSTS_CD , B.EFT_CROSSED_CHECK , B.EFT_CHECK_FWRD_CD , B.EFT_CHECK_DRAW_CD , B.EFT_PAY_INST_CD1 , B.EFT_PAY_INST_CD2 , B.EFT_PAY_INST_CD3 , B.EFT_PAY_INST_CD4 , B.EFT_PAY_INST_REF1 , B.EFT_PAY_INST_REF2 , B.PREFERRED_LANGUAGE , B.VNDR_SERVICE_TYPE FROM PS_PAYMENT_TBL A , PS_PYCYCL_DATA B , PS_COUNTRY_TBL C , PS_STL_INSTRUCTION E , PS_COUNTRY_TBL J WHERE A.PAY_CYCLE = B.PAY_CYCLE AND A.PAY_CYCLE_SEQ_NUM = B.PAY_CYCLE_SEQ_NUM AND 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.COUNTRY = C.COUNTRY AND E.COUNTRY = J.COUNTRY AND B.REMIT_BANK_SETID = E.SETID AND B.REMIT_BANK_CD = E.SETTLEMENT_ID AND A.CURRENCY_PYMNT = E.CURRENCY_CD |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | PAY_CYCLE | Character(6) | VARCHAR2(6) NOT NULL | Pay Cycle |
| 2 | PAY_CYCLE_SEQ_NUM | Number(6,0) | INTEGER NOT NULL | Pay Cycle Sequence Number |
| 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_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 |
| 7 | DFI_ID_QUAL | Character(2) | VARCHAR2(2) NOT NULL |
A banking industry convention identifier utilized to drive data validation for a given bank/counterparty.
01=Transit Number 02=Swift ID/BIC 03=CHIPS Participant ID 04=Canadian Bank Branch/Institute 05=CHIPS Universal ID ZZ=Mutually Defined |
| 8 | DFI_ID_NUM | Character(12) | VARCHAR2(12) NOT NULL | A banking industry convention identifier utilized to drive electronic settlements processing for a given bank/coun |
| 9 | BANK_ACCOUNT_NUM | Character(35) | VARCHAR2(35) NOT NULL | The literal unique identifier associated with a given bank/counterparty account. |
| 10 | PYMNT_ID_REF | Character(20) | VARCHAR2(20) NOT NULL | Payment Reference |
| 11 | PYMNT_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Payment Amount |
| 12 | CURRENCY_PYMNT | Character(3) | VARCHAR2(3) NOT NULL | Payment Currency |
| 13 | EFT_PYMNT_FMT_CD | Character(3) | VARCHAR2(3) NOT NULL |
Payment Format
00=Standard SWIFT transfer 10=Urgent SWIFT 15=Euro-Transfer 21=Check, w/ receipt confirmation 22=Check, Urgent 23=Check, urgent w/ receipt 40=Postal Order CTX=Corp Trade Exchange PBC=Check, standard PPD=Prearranged Pay & Deposit WRT=Wire Transfer |
| 14 | EFT_TRANS_HANDLING | Character(1) | VARCHAR2(1) NOT NULL |
Transaction Handling
D=Payment Only P=Prenotification X=Payment and Advice |
| 15 | PYMNT_DT | Date(10) | DATE | Payment Date |
| 16 | NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Name 1 |
| 17 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 18 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 19 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 20 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 21 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 22 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 23 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 24 | COUNTRY_TPRT_2CHAR | Character(2) | VARCHAR2(2) NOT NULL | Trading Partner 2-Char Country |
| 25 | IN_CITY_LIMIT | Character(1) | VARCHAR2(1) NOT NULL | In City Limit |
| 26 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
| 27 | GEO_CODE | Character(11) | VARCHAR2(11) NOT NULL | Geo Code |
| 28 | NUM1 | Character(6) | VARCHAR2(6) NOT NULL | Number 1 |
| 29 | NUM2 | Character(6) | VARCHAR2(6) NOT NULL | Number 2 |
| 30 | HOUSE_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
House Type
AB=House Boat WW=Trailer |
| 31 | NAME1_AC | Character(40) | VARCHAR2(40) NOT NULL | Name 1 Alternate Char Set |
| 32 | BNK_ID_NBR | Character(20) | VARCHAR2(20) NOT NULL | A literal alphanumeric attribute that uniquely identifies a given bank or counterparty. |
| 33 | BRANCH_ID | Character(10) | VARCHAR2(10) NOT NULL | The local branch office identifier associated with a given bank/counterparty. |
| 34 | CHECK_DIGIT | Character(2) | VARCHAR2(2) NOT NULL | Check Digit |
| 35 | BANK_ACCT_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
An account attribute that describes the ostensible purposes of a given bank/counterparty account.
01=Time Deposit 03=Checking Account 05=Stock 06=Bond 07=Life Insurance Value 08=Retirement Account 10=Business Account 11=Trust Fund Account 12=Stock & Bond Account 13=Life Insurance Acct CA=Current Account CH=Charges Account DA=Demand Deposit LN=Loan RD=Return Items on DDA RS=Return Items-Savings SA=Settlement Account SV=Savings Account Z=Mutually Defined |
| 36 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 37 | DESCR_AC | Character(30) | VARCHAR2(30) NOT NULL | Alternate Description |
| 38 | ADDRESS1_OTHER | Character(55) | VARCHAR2(55) NOT NULL | Address 1 Other |
| 39 | ADDRESS2_OTHER | Character(55) | VARCHAR2(55) NOT NULL | Address 2 Other |
| 40 | ADDRESS3_OTHER | Character(55) | VARCHAR2(55) NOT NULL | Address 3 Other |
| 41 | ADDRESS4_OTHER | Character(55) | VARCHAR2(55) NOT NULL | Address 4 Other |
| 42 | CITY_OTHER | Character(30) | VARCHAR2(30) NOT NULL | City Other |
| 43 | STATE_OTHER | Character(6) | VARCHAR2(6) NOT NULL | State Other |
| 44 | POSTAL_OTHER | Character(12) | VARCHAR2(12) NOT NULL | Postal Other |
| 45 | COUNTRY_2CHAR | Character(2) | VARCHAR2(2) NOT NULL | 2-Char Country Code |
| 46 | IN_CITY_LMT_OTHER | Character(1) | VARCHAR2(1) NOT NULL | In City Limit Other |
| 47 | COUNTY_OTHER | Character(30) | VARCHAR2(30) NOT NULL | County Other |
| 48 | GEO_CODE_OTHER | Character(11) | VARCHAR2(11) NOT NULL | Geo Code Other |
| 49 | STTLMNT_DT_EST | Date(10) | DATE | Date in which a deal transaction is estimated to be settled with a counterparty. |
| 50 | EFT_DOM_COSTS_CD | Character(1) | VARCHAR2(1) NOT NULL |
Domestic Costs
1=Payer's Domestic Account 2=Payer's Foreign Account 3=Payee's Account 4=Trans Exp to Pyr, other to Pye |
| 51 | EFT_CORR_COSTS_CD | Character(1) | VARCHAR2(1) NOT NULL |
Correspondent's Costs
1=Payer's Domestic Account 2=Payer's Foreign Account 3=Payee's Account |
| 52 | EFT_CROSSED_CHECK | Character(1) | VARCHAR2(1) NOT NULL | Crossed Check |
| 53 | EFT_CHECK_FWRD_CD | Character(1) | VARCHAR2(1) NOT NULL |
Check Forwarding
1=Payer's Bank 2=Payer 3=Payee 4=Payee's Bank |
| 54 | EFT_CHECK_DRAW_CD | Character(1) | VARCHAR2(1) NOT NULL |
Bank Check Drawn On
1=Payer's Bank 2=Payee's Bank |
| 55 | EFT_PAY_INST_CD1 | Character(3) | VARCHAR2(3) NOT NULL |
Payment Instruction 1
ACC=/ACC/ BEN=/BENONLY/ CHK=/CHECK/ COR=/CORPTRAD/ HLD=/HOLD/ INT=/INT/ IRR=PAYMENT IRREVOCABLE PHB=/PHONEBEN/ PHI=/PHONIBK/ PHN=/PHON/ RC1=ADV BEN W/CHARGES RC2=ADV BEN W/O CHARGES RCB=/RCB/ REC=/REC/ REJ=/REJT/ RET=BEN UNKNOWN RETURN TEB=/TELEBEN/ TEI=/TELEIBK/ TEL=/TELE/ XFR=INTERCOMPANY TRANSFER |
| 56 | EFT_PAY_INST_CD2 | Character(3) | VARCHAR2(3) NOT NULL | Payment Instruction 2 |
| 57 | EFT_PAY_INST_CD3 | Character(3) | VARCHAR2(3) NOT NULL | Payment Instruction 3 |
| 58 | EFT_PAY_INST_CD4 | Character(3) | VARCHAR2(3) NOT NULL | Payment Instruction 4 |
| 59 | EFT_PAY_INST_REF1 | Character(40) | VARCHAR2(40) NOT NULL | Payment Instruction Ref 1 |
| 60 | EFT_PAY_INST_REF2 | Character(40) | VARCHAR2(40) NOT NULL | Payment Instruction Ref 2 |
| 61 | PREFERRED_LANGUAGE | Character(3) | VARCHAR2(3) NOT NULL |
Preferred Language
ARA=Arabic CFR=Canadian French CZE=Czech DAN=Danish DUT=Dutch E=English ENG=English ESP=Spanish F=French FIN=Finnish FRA=French GER=German HUN=Hungarian INE=International English ITA=Italian JPN=Japanese KOR=Korean NOR=Norwegian POL=Polish POR=Portuguese RUS=Russian SVE=Swedish THA=Thai UKE=UK English ZHS=Simplified Chinese ZHT=Traditional Chinese |
| 62 | VNDR_SERVICE_TYPE | Character(3) | VARCHAR2(3) NOT NULL | Service Type |