BCH_EFTDTL_VW4

(SQL View)
 Index Back

AP EFT Detail View


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 , B.EFFDT , B.BANK_ACCT_SEQ_NBR , A.PYMNT_DT , A.PYMNT_AMT , A.CURRENCY_PYMNT , F.AMOUNT , 0 , E.CURRENCY_CD , H.BCH_TRNCOST_CD FROM PS_PAYMENT_TBL A , PS_VNDR_BANK_ACCT B , PS_PYMNT_VCHR_XREF C , PS_BCH_BANK_CHRG D , PS_BCH_CHRG_HDR E , PS_BCH_CHRG_DTL F , PS_VENDOR_PAY H WHERE A.SOURCE_TXN = 'VCHR' AND A.REMIT_SETID = B.SETID AND A.REMIT_VENDOR = B.VENDOR_ID AND A.VNDR_LOC = B.VNDR_LOC AND A.BANK_SETID = C.BANK_SETID AND A.BANK_CD = C.BANK_CD AND A.BANK_ACCT_KEY = C.BANK_ACCT_KEY AND A.PYMNT_METHOD = 'EFT' AND A.PYMNT_METHOD = C.PYMNT_METHOD AND A.PYMNT_ID = C.PYMNT_ID AND A.CANCEL_ACTION = 'N' AND B.BANK_ACCT_SEQ_NBR = C.BANK_ACCT_SEQ_NBR AND H.SETID = B.SETID AND H.VENDOR_ID = B.VENDOR_ID AND H.VNDR_LOC = B.VNDR_LOC AND H.EFFDT = ( SELECT MAX(H2.EFFDT) FROM PS_VENDOR_PAY H2 WHERE H2.SETID = H.SETID AND H2.VENDOR_ID = H.VENDOR_ID AND H2.VNDR_LOC = H.VNDR_LOC AND H2.EFF_STATUS = 'A' ) AND H.EFFDT = B.EFFDT AND H.BCH_TRNCOST_CD = '1' AND A.BANK_SETID = D.SETID AND A.BANK_CD = D.BANK_CD AND A.BNK_ID_NBR = B.BNK_ID_NBR AND A.BRANCH_ID = B.BRANCH_ID AND D.EFT_LAYOUT_CD = 'GENX' AND D.BCH_INTER_INTRA = '1' AND D.SETID = E.SETID AND D.BCH_CHARGE_CD = E.BCH_CHARGE_CD AND E.EFFDT = ( SELECT MAX(E2.EFFDT) FROM PS_BCH_CHRG_HDR E2 WHERE E2.SETID = E.SETID AND E2.BCH_CHARGE_CD = E.BCH_CHARGE_CD AND E2.EFF_STATUS = 'A' AND E2.EFFDT <= A.PYMNT_DT ) AND D.SETID = F.SETID AND D.BCH_CHARGE_CD = F.BCH_CHARGE_CD AND F.EFFDT = E.EFFDT AND F.FROM_AMOUNT <= A.PYMNT_AMT AND F.TO_AMOUNT >= A.PYMNT_AMT

# 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 AS_OF_DATE Date(10) DATE As of Date
15 BANK_ACCT_SEQ_NBR Number(3,0) SMALLINT NOT NULL Bank Account Sequence Number
16 PYMNT_DT Date(10) DATE Payment Date
17 PYMNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Amount
18 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency
19 AMOUNT_1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Work Field
20 AMOUNT_2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount Work Field
21 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
22 BCH_TRNCOST_CD Character(1) VARCHAR2(1) NOT NULL Transfer Cost Code
1=Payer
2=Payee