R12 Bank Accounts related tables for Suppliers and Customers

- Banks and their Branches are now each stored as Parties (HZ_PARTIES) in their own right. They are linked together through Relationships(HZ_RELATIONSHIP). There is a separate link for both Bank to Branch and also from Branch to Bank.

- The Bank Accounts themselves are now stored in the new Oracle Payments Application

- Below are the Key tables where the Bank Account information is stored

IBY_EXTERNAL_PAYEES_ALL
IBY_EXTERNAL_PAYERS_ALL
IBY_EXT_BANK_ACCOUNTS
IBY_PMT_INSTR_USES_ALL

- Below are the Key tables where the Bank Data of R12 TCA is stored

HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_ORGANIZATION_PROFILES
HZ_CONTACT_POINTS
HZ_ORG_CONTACT
HZ_ORG_CONTACT_ROLES

• The following query gives you the links required for matching a Bank Account to its Supplier Site Record

SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
, ass.vendor_site_code;

• The following query gives you the links required for matching a Bank Account to its Customer Site Record:

SELECT cust.party_name customer_name
, cust_acct.account_number
, cust_uses.site_use_code
, cust_loc.address1
, cust_loc.address2
, cust_loc.address3
, cust_loc.address4
, cust_loc.city
, cust_loc.postal_code
, bank.party_name bank_name
, bank_prof.home_country
, branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
, account.bank_account_num
, account.bank_account_name
FROM hz_parties bank
, hz_relationships rel
, hz_parties branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
, iby_ext_bank_accounts account
, iby_account_owners acc_owner
, iby_external_payers_all ext_payer
, iby_pmt_instr_uses_all acc_instr
, hz_parties cust
, hz_cust_accounts cust_acct
, hz_cust_acct_sites_all cust_site
, hz_cust_site_uses_all cust_uses
, hz_locations cust_loc
WHERE 1=1
AND bank.party_id = rel.object_id
and bank.party_type = rel.object_type
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.relationship_code = 'BRANCH_OF'
AND rel.subject_id = branch.party_id
AND rel.subject_type = branch.party_type
AND rel.subject_table_name = 'HZ_PARTIES'
AND bank.party_id = bank_prof.party_id
AND branch.party_id = branch_prof.party_id
AND bank.party_id = account.bank_id
AND branch.party_id = account.branch_id
AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
AND acc_owner.account_owner_party_id = cust.party_id
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
AND ext_payer.cust_account_id = cust_acct.cust_account_id
AND cust_acct.cust_account_id = cust_site.cust_account_id
AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND cust_uses.site_use_id = ext_payer.acct_site_use_id
AND cust_uses.location = cust_loc.location_id
AND cust.party_id = cust_acct.party_id;