BCH_1000_VW3

(SQL View)
 Index Back

Bank Charge Vw3


SELECT A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.PYMNT_ID , A.PYMNT_ID_REF , A.PAY_CYCLE , A.PAY_CYCLE_SEQ_NUM , A.BNK_ID_NBR , A.BRANCH_ID , A.BANK_ACCOUNT_NUM , A.REMIT_SETID , A.REMIT_VENDOR , A.VNDR_LOC , A.BANK_ACCT_SEQ_NBR , A.PYMNT_DT , A.PYMNT_AMT , A.CURRENCY_PYMNT , A.AMOUNT_1 , A.AMOUNT_2 , A.CURRENCY_CD , A.BCH_TRNCOST_CD , B.DESCR , B.DESCRSHORT , C.DESCR , C.DESCRSHORT , D.NAME1 , E.BNK_ID_NBR , E.BRANCH_ID , E.BANK_ACCOUNT_NUM , E.BENEFICIARY_BNK_AC , E.BENEF_BRANCH_AC FROM PS_BCH_EFTDTL_VW3 A , PS_BANK_CD_VW2 B , PS_BANK_ACCT_VW C , PS_VENDOR D , PS_VNDR_BANK_ACCT E , PS_VENDOR_LOC Z WHERE A.BANK_SETID = B.BANK_SETID AND A.BANK_CD = B.BANK_CD AND A.BANK_SETID = C.BANK_SETID AND A.BANK_CD = C.BANK_CD AND A.BANK_ACCT_KEY = C.BANK_ACCT_KEY AND D.SETID = A.REMIT_SETID AND D.VENDOR_ID = A.REMIT_VENDOR AND E.SETID = A.REMIT_SETID AND E.VENDOR_ID = A.REMIT_VENDOR AND E.VNDR_LOC = A.VNDR_LOC AND E.BANK_ACCT_SEQ_NBR = A.BANK_ACCT_SEQ_NBR AND Z.SETID = A.REMIT_SETID AND Z.VENDOR_ID = A.REMIT_VENDOR AND Z.VNDR_LOC = A.VNDR_LOC AND Z.EFF_STATUS = 'A' AND E.EFFDT = ( SELECT MAX(EFFDT) FROM PS_VENDOR_LOC X WHERE Z.SETID = X.SETID AND Z.VENDOR_ID = X.VENDOR_ID AND Z.VNDR_LOC = X.VNDR_LOC AND X.EFFDT <= A.PYMNT_DT AND X.EFF_STATUS = 'A')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.
2 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
3 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
4 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number
5 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference
6 PAY_CYCLE Character(6) VARCHAR2(6) NOT NULL Pay Cycle
7 PAY_CYCLE_SEQ_NUM Number(6,0) INTEGER NOT NULL Pay Cycle Sequence Number
8 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
9 BRANCH_ID Character(10) VARCHAR2(10) NOT NULL The local branch office identifier associated with a given bank/counterparty.
10 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.
11 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID
12 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
13 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
14 BANK_ACCT_SEQ_NBR Number(3,0) SMALLINT NOT NULL Bank Account Sequence Number
15 PYMNT_DT Date(10) DATE Payment Date
16 PYMNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Amount
17 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency
18 AMOUNT_1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Work Field
19 AMOUNT_2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Work Field
20 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
21 BCH_TRNCOST_CD Character(1) VARCHAR2(1) NOT NULL Transfer Cost Code
1=Payer
2=Payee
22 DESCR Character(30) VARCHAR2(30) NOT NULL Description
23 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
24 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
25 DESCRSHORT2 Character(10) VARCHAR2(10) NOT NULL Short Description
26 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
27 BNK_ID_NBR1 Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given internal counterparty.
28 BRANCH_ID_CUST Character(10) VARCHAR2(10) NOT NULL The local branch office identifier associated with a given bank/counterparty.
29 BANK_ACCOUNT_NUM1 Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given internal partner bank/counterparty acco
30 BENEFICIARY_BANK Character(30) VARCHAR2(30) NOT NULL The bank/counterparty associated with a given beneficiary.
31 BENEFICIARY_BNK_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character Set field for BENEFICIARY_BANK