R12 Supplier Bank Accounts-More Technical by LT Solutions

The data model for storing Banks and Bank Account information has changed for this release of the Oracle Applications Suite. Banks and their Branches are now each stored as Parties (in HZ_PARTIES) in their own right. They are linked together through Relationships (in 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. All tables are prefixed with the Application Short Name, IBY. The bank accounts themselves 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.

Now, linking the Bank Account to the relevant Supplier is a bit more involved. The table IBY_ACCOUNT_OWNERS can be used to identify the Supplier Party (using the ext_bank_account_id to link the 2 IBY tables) that the Bank Account belongs to. Identifying the Supplier Site that the Bank Account is actually attached to is a little bit trickier! This is done through linking together the following tables IBY_EXTERNAL_PAYEES_ALL and IBY_PMT_INSTR_USES_ALL. A record is created in the Payment Instrument Uses table IBY_PMT_INSTR_USES_ALL for each assignment of a Bank Account. This record is linked to the bank account by matching the ext_bank_account_id to the instrument_id. Now, each Instrument Record links to an External Payee Record held in IBY_EXTERNAL_PAYEES_ALL using the ext_pmt_party_id. It is the External Payee Record that links us to a Supplier Party ID (payee_party_id), Supplier Party Site ID (party_site_id) and Supplier Site ID (supplier_site_id). There is a record stored in the IBY_EXTERNAL_PAYEES_ALL table for every Supplier Site defined and for the supplier itself (Bank Accounts can be defined at supplier level too). The IBY_PMT_INSTR_USES_ALL is a pointer to the specific Site/Supplier that the Bank Account has been assigned to.

As an added complexity in R12, links to Suppliers are now created in the TCA. Suppliers have a Party Record and Supplier Sites have Party Site Records. As part of this functionality shift, Suppliers and
their Sites have now moved to AP_SUPPLIERS and AP_SUPPLIER_SITES_ALL (although the unique keys are still called VENDOR_ID and VENDOR_SITE_ID respectively!!). The old PO tables
used in 11i and before are now created as views which link the Supplier Records to their related TCA records (i.e. PO_VENDORS links AP_SUPPLIERS with HZ_PARTIES and PO_VENDOR_SITES_ALL links AP_SUPPLIER_SITES_ALL with HZ_PARTY_SITES).

The following query gives you the links required for matching a Bank Account to its Supplier Site Record (This will have to be updated if the Bank Accounts is assigned directly to the Supplier rather
than the supplier site):

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