BCH_EFTDTL_VW7

(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 , 0 , 0 , ' ' , H.BCH_TRNCOST_CD FROM PS_PAYMENT_TBL A , PS_VNDR_BANK_ACCT B , PS_PYMNT_VCHR_XREF C , 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 ( NOT EXISTS ( SELECT 'X' FROM PS_BCH_BANK_CHRG CHRG1 , PS_BCH_CHRG_HDR HDR1 WHERE CHRG1.SETID = A.BANK_SETID AND CHRG1.BANK_CD = A.BANK_CD AND CHRG1.EFT_LAYOUT_CD = 'GENX' AND CHRG1.BCH_INTER_INTRA = '1' AND HDR1.SETID = A.BANK_SETID AND HDR1.BCH_CHARGE_CD = CHRG1.BCH_CHARGE_CD AND HDR1.EFFDT = ( SELECT MAX(HDR12.EFFDT) FROM PS_BCH_CHRG_HDR HDR12 WHERE HDR1.SETID = HDR12.SETID AND HDR1.BCH_CHARGE_CD = HDR12.BCH_CHARGE_CD AND HDR12.EFFDT <= A.PYMNT_DT ) AND HDR1.EFF_STATUS = 'A' AND HDR1.CURRENCY_CD = A.CURRENCY_PYMNT ) OR NOT EXISTS ( SELECT 'X' FROM PS_BCH_BANK_CHRG CHRG2 , PS_BCH_CHRG_HDR HDR2 WHERE CHRG2.SETID = A.BANK_SETID AND CHRG2.BANK_CD = A.BANK_CD AND CHRG2.EFT_LAYOUT_CD = 'GENX' AND CHRG2.BCH_INTER_INTRA = '2' AND HDR2.SETID = A.BANK_SETID AND HDR2.BCH_CHARGE_CD = CHRG2.BCH_CHARGE_CD AND HDR2.EFFDT = ( SELECT MAX(HDR22.EFFDT) FROM PS_BCH_CHRG_HDR HDR22 WHERE HDR2.SETID = HDR22.SETID AND HDR2.BCH_CHARGE_CD = HDR22.BCH_CHARGE_CD AND HDR22.EFFDT <= A.PYMNT_DT ) AND HDR2.EFF_STATUS = 'A' AND HDR2.CURRENCY_CD = A.CURRENCY_PYMNT ) OR NOT EXISTS ( SELECT 'X' FROM PS_BCH_BANK_CHRG CHRG3 , PS_BCH_CHRG_HDR HDR3 WHERE CHRG3.SETID = A.BANK_SETID AND CHRG3.BANK_CD = A.BANK_CD AND CHRG3.EFT_LAYOUT_CD = 'GENX' AND CHRG3.BCH_INTER_INTRA = '2' AND HDR3.SETID = A.BANK_SETID AND HDR3.BCH_CHARGE_CD = CHRG3.BCH_CHARGE_CD AND HDR3.EFFDT = ( SELECT MAX(HDR32.EFFDT) FROM PS_BCH_CHRG_HDR HDR32 WHERE HDR3.SETID = HDR32.SETID AND HDR3.BCH_CHARGE_CD = HDR32.BCH_CHARGE_CD AND HDR32.EFFDT <= A.PYMNT_DT ) AND HDR3.EFF_STATUS = 'A' AND HDR3.CURRENCY_CD = A.CURRENCY_PYMNT ))

# 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