R12 Supplier Bank Account details Oracle Apps

 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).
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.




SELECT party_supp.party_name supplier_name

,      aps.segment1          supplier_number

,      ass.vendor_site_code  supplier_site

,      branch_prof.bank_or_branch_number bank_number

,      party_branch.party_name branch_name

,      branch_prof.bank_or_branch_number branch_number

,      ieb.bank_account_num

,      ieb.bank_account_name

,      party_bank.party_name bank_name

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