Saturday, December 31, 2016

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