R12 Supplier Bank Accounts

In Release 12, Payables Supplier Bank Information is stored somewhere else instead of PO_VENDORS table as in 11i. The supplier (or External) bank account information are stored in the table called IBY_EXT_BANK_ACCOUNTS. The bank and bank branches information are stored in the table HZ_PARTIES. 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. Bank sites and Location information are stored in tables: HZ_PARTY_SITES and HZ_LOCATIONS. The bank_id and branch_id fields of IBY_EXT_BANK_ACCOUNTS table link the Bank Account to the relevant Bank and Branch Parties in the HZ_PARTIES table (IBY_EXT_BANK_ACCOUNTS.BANK_id = hz_paties.party_id).

Supplier (or External) bank accounts are created in Payables, in the Supplier Entry forms. Navigate to Suppliers -> Entry. Query or create your supplier. Click on Banking Details and then choose Create. After you have created the bank account, you can assign the bank account to the supplier site.

When the Bank is assigned to Vendors then it will be updated in a table called HZ_CODE_ASSIGNMENTS. Payment related details of supplier are also inserted in iby_external_payees_all as well as iby_ext_party_pmt_mthds.

The bank branch number can be found in the table: HZ_ORGANIZATION_PROFILES .The HZ_ORGANIZATION_PROFILES table stores a variety of information about a party. This table gets populated when a party of the Organization type is created. IBY_EXTERNAL_PAYEES_ALL stores payment-related attributes for the funds disbursement payment process for external party payees. 

Queries:

SELECT aps.vendor_name "VERDOR NAME", apss.vendor_site_code "VENDOR SITE CODE", ieb.bank_name "BANK NAME", iebb.bank_branch_name "BANK BRANCH NAME", iebb.branch_number "BRANCH NUMBER", ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER", ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME" FROM ap.ap_suppliers aps, ap.ap_supplier_sites_all apss, apps.iby_ext_bank_accounts ieba, apps.iby_account_owners iao, apps.iby_ext_banks_v ieb, apps.iby_ext_bank_branches_v iebb WHERE aps.vendor_id = apss.vendor_id and iao.account_owner_party_id = aps.party_id and ieba.ext_bank_account_id = iao.ext_bank_account_id and ieb.bank_party_id = iebb.bank_party_id and ieba.branch_id = iebb.branch_party_id and ieba.bank_id = ieb.bank_party_id;

SELECT  aps.vendor_name "VERDOR NAME",         apss.vendor_site_code "VENDOR SITE CODE",         ieb.bank_name "BANK NAME",         iebb.bank_branch_name "BANK BRANCH NAME",         iebb.branch_number "BRANCH NUMBER",         ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",         ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME" FROM    ap.ap_suppliers aps,         ap.ap_supplier_sites_all apss,         apps.iby_ext_bank_accounts ieba,         apps.iby_account_owners iao,         apps.iby_ext_banks_v ieb,         apps.iby_ext_bank_branches_v iebb WHERE   aps.vendor_id = apss.vendor_id         and iao.account_owner_party_id = aps.party_id         and ieba.ext_bank_account_id = iao.ext_bank_account_id         and ieb.bank_party_id = iebb.bank_party_id         and ieba.branch_id = iebb.branch_party_id         and ieba.bank_id = ieb.bank_party_id;

Oracle PL/SQL

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;

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;