Customer Bank Account Details

Bank account can be associated with customer account or customer site. This query can find all bank accounts associated with a customer and it's sites

SELECT ca.account_number "Customer Account Number" , null "Site address" , piu.start_date "Bank Account Use Start Date" , piu.end_date "Bank Account Use End Date" , eba.bank_account_num "Bank Account Number" , eba.bank_account_name "Bank Account Name" , eba.start_date "Bank Account Start Date" , eba.end_date "Bank Account End Date" FROM hz_cust_accounts ca , iby_external_payers_all epa , iby_pmt_instr_uses_all piu , iby_ext_bank_accounts eba WHERE ca.cust_account_id = epa.cust_account_id AND piu.ext_pmt_party_id = epa.ext_payer_id AND piu.payment_function = 'CUSTOMER_PAYMENT' AND eba.ext_bank_account_id = piu.instrument_id AND ca.account_number = 'SKM-PERSON-01' AND epa.acct_site_use_id is null union SELECT ca.account_number , (SELECT address1 || ' ' || loc.city || ' ' || loc.state || '-' || loc.postal_code FROM hz_locations loc , hz_party_sites hps WHERE loc.location_id = hps.location_id AND hps.party_site_id = cas.party_site_id ) addr , piu.start_date , piu.end_date , eba.bank_account_num , eba.bank_account_name , eba.start_date , eba.end_date FROM hz_cust_site_uses_all csu , iby_external_payers_all epa , iby_pmt_instr_uses_all piu , iby_ext_bank_accounts eba , hz_cust_acct_sites_all cas , hz_cust_accounts ca WHERE ca.account_number = 'SKM-PERSON-01' AND cas.cust_account_id = ca.cust_account_id AND csu.cust_acct_site_id = cas.cust_acct_site_id AND epa.acct_site_use_id = csu.site_use_id AND piu.ext_pmt_party_id = epa.ext_payer_id AND piu.payment_function = 'CUSTOMER_PAYMENT' AND eba.ext_bank_account_id = piu.instrument_id