Bank Accounts- Supplier and Customer in R12

- 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;