R12 Supplier Bank Accounts

So far we have seen creation of Bank, Bank Branch and Bank Account. Lets now focus on technical information.

In R12 Banks and their Branches are stored as Parties in HZ_PARTIES. Both are linked together through relationships in HZ_RELATIONSHIP table. The Bank accounts are stored in the Oracle Payments Application. All tables in this application are prefixed with IBY. The bank accounts are stored in the IBY_EXT_BANK_ACCOUNTS table. The bank_id and branch_id fields link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table.

The table IBY_ACCOUNT_OWNERS is used to identify the Supplier Party using the ext_bank_account_id to link the 2 IBY tables that the Bank Account belongs to. Finding the Bank account attached to supplier site can be done through linking the tables IBY_EXTERNAL_PAYEES_ALL and IBY_PMT_INSTR_USES_ALL. When a Bank account is assigned a record is created in the IBY_PMT_INSTR_USES_ALL table (Payment Instruments table) and is linked to the bank account by matching the ext_bank_account_id to the instrument_id. Each instrument record is linked to an external payee record in IBY_EXTERNAL_PAYEES_ALL using the ext_pmt_party_id column. The external payee record links us to supplier party ID (payee_party_id), supplier party site ID (party_site_id) and supplier site ID (supplier_site_id). The IBY_EXTERNAL_PAYEES_ALL table stores every supplier site defined and the supplier itself. The IBY_PMT_INSTR_USES_ALL is the main table which lets us know the specific Site/Supplier that the Bank Account has been assigned to.

The query below gives you the links required to get a matching Bank account assigned to supplier site.

SELECT hp.party_name supplier_name
, as.segment1 supplier_number
, assa.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 hp
, ap_suppliers as
, hz_party_sites hps
, ap_supplier_sites_all assa
, 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 hp.party_id = as.party_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = assa.party_site_id
AND assa.vendor_id = as.vendor_id
AND iep.payee_party_id = hp.party_id
AND iep.party_site_id = hps.party_site_id
AND iep.supplier_site_id = assa.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 1,3