Query to get Customer Bank Account Details

1SELECT cust.party_name                   customer_name,
2       cust_acct.account_number,
3       cust_uses.site_use_code,
4       cust_loc.address1,
5       cust_loc.address2,
6       cust_loc.address3,
7       cust_loc.address4,
8       cust_loc.city,
9       cust_loc.postal_code,
10       bank.party_name                   bank_name,
11       bank_prof.home_country,
12       branch.party_name                 branch_name,
13       branch_prof.bank_or_branch_number branch_number,
14       account.bank_account_num,
15       account.bank_account_name,
16       account.IBAN,
17       ixbv.EFT_SWIFT_CODE,
18       ixpm.PAYMENT_METHOD_CODE,
19       hou.name
20  FROM hz_parties               bank,
21       hz_relationships         rel,
22       hz_parties               branch,
23       hz_organization_profiles bank_prof,
24       hz_organization_profiles branch_prof,
25       iby_ext_bank_accounts    account,
26       iby_account_owners       acc_owner,
27       iby_external_payers_all  ext_payer,
28       iby_pmt_instr_uses_all   acc_instr,
29       hz_parties               cust,
30       hz_cust_accounts         cust_acct,
31       hz_cust_acct_sites_all   cust_site,
32       hz_cust_site_uses_all    cust_uses,
33       hz_locations             cust_loc,
34       hz_party_sites           hps,
35       iby_ext_party_pmt_mthds  ixpm,
36       IBY_EXT_BANK_BRANCHES_V  ixbv,
37       hr_operating_units       hou
38 WHERE     1 = 1
39       AND bank.party_id = rel.object_id
40       AND hps.party_id = cust.PARTY_ID
41       AND hps.PARTY_SITE_ID = cust_site.PARTY_SITE_ID
42       AND hps.location_id = cust_loc.location_id
43       AND bank.party_type = rel.object_type
44       AND rel.object_table_name = 'HZ_PARTIES'
45       AND rel.relationship_code = 'BRANCH_OF'
46       AND rel.subject_id = branch.party_id
47       AND rel.subject_type = branch.party_type
48       AND rel.subject_table_name = 'HZ_PARTIES'
49       AND bank.party_id = bank_prof.party_id
50       AND branch.party_id = branch_prof.party_id
51       AND bank.party_id = account.bank_id
52       AND branch.party_id = account.branch_id
53       AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
54       AND acc_owner.account_owner_party_id = cust.party_id
55       AND account.ext_bank_account_id = acc_instr.instrument_id
56       AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
57       AND ext_payer.cust_account_id = cust_acct.cust_account_id
58       AND cust_acct.cust_account_id = cust_site.cust_account_id
59       AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
60       AND cust_uses.site_use_id = ext_payer.acct_site_use_id
61       AND cust.party_id = cust_acct.party_id
62       AND SYSDATE BETWEEN (bank_prof.EFFECTIVE_START_DATE)
63                       AND NVL (bank_prof.EFFECTIVE_END_DATE, SYSDATE + 1)
64       AND SYSDATE BETWEEN (branch_prof.EFFECTIVE_START_DATE)
65                       AND NVL (branch_prof.EFFECTIVE_END_DATE, SYSDATE + 1)
66       AND ixpm.EXT_PMT_PARTY_ID = ext_payer.EXT_PAYER_ID
67       AND ixpm.PRIMARY_FLAG = 'Y'
68       AND ixpm.INACTIVE_DATE IS NULL
69       AND cust_site.org_id = ORGANIZATION_ID
70       AND hou.name LIKE '<Operating Unit Name>'
71       AND ixbv.branch_party_id = branch.party_id
72       AND ixbv.BANK_PARTY_ID = bank.party_id
73       AND ixpm.PAYMENT_METHOD_CODE = 'EFT'