R12 Supplier Bank Accounts

R12 Supplier Bank Accounts Details

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