Introduction
In Oracle Applications R12, supplier bank account details are not stored in a single, easily queryable table. They’re spread across the Oracle Payments (IBY) schema and Accounts Payable (AP) tables — separated by design because Oracle decoupled banking/payment instruments from the supplier module starting in R12. This creates a recurring pain point for AP, Finance, and IT support teams: when someone needs a consolidated view of supplier bank details (bank name, branch, IBAN, SWIFT code, account number) for audits, vendor verification, or payment troubleshooting, there is no out-of-the-box report that pulls it all together cleanly.
Why We Need to Do This / Cause of the Issue
The root cause is architectural. R12 introduced the Oracle iPayment / IBY (Internet Bank Account) model, which manages bank accounts, payment instruments, and payees as party-based entities shared across modules (AP, AR, Payroll). As a result:
- Bank account data lives in iby_ext_bank_accounts and iby_pmt_instr_uses_all, not in the AP supplier tables.
- The link between a supplier/site and its bank account is indirect — routed through iby_account_owners and iby_external_payees_all, which tie back to the party model (account_owner_party_id, payee_party_id).
- Bank and branch metadata (bank name, branch number, SWIFT code) is normalized separately in ce_bank_branches_v.
The impact: without a pre-built query, teams either rely on manually clicking through the Suppliers UI (Bank Accounts tab) one vendor at a time — slow and error-prone for bulk audits — or they build ad-hoc queries that miss joins (like the primary bank account flag or operating unit context) and pull duplicate or incomplete rows.
How We Solve It
The solution is a single consolidated SQL query that joins the IBY and AP tables correctly, filtered to the primary bank account per vendor, and enriched with the operating unit name and branch details.
Key join logic:
- iby_account_owners (filtered on primary_flag = ‘Y’) identifies which bank account is the primary one for a given party — this avoids pulling stale or secondary accounts.
- iby_pmt_instr_uses_all links that bank account (as a payment instrument) to the payee record.
- iby_external_payees_all connects the payee back to the AP supplier site (vendor_site_id), which is the bridge into the standard AP tables.
- ap_supplier_sites_all and ap_suppliers give the standard vendor number, name, and site code.
- ce_bank_branches_v is left-joined to pull bank/branch descriptive details, since not every account will have branch metadata populated.
- A correlated subquery against hr_operating_units resolves the operating unit name from asa.org_id, useful when the same vendor has sites across multiple operating units.
SELECT owners.account_owner_party_id,
asp.segment1 vendor_num,
asp.vendor_name,
(SELECT NAME
FROM apps.hr_operating_units hou
WHERE hou.organization_id = asa.org_id) ou_name,
asa.vendor_site_code,
ieb.country_code,
cbbv.bank_name,
cbbv.bank_number,
cbbv.bank_branch_name,
cbbv.branch_number,
cbbv.bank_branch_type,
cbbv.eft_swift_code,
ieb.bank_account_num,
ieb.currency_code,
ieb.iban,
ieb.foreign_payment_use_flag,
ieb.bank_account_name_alt
FROM apps.iby_pmt_instr_uses_all instrument,
apps.iby_account_owners owners,
apps.iby_external_payees_all payees,
apps.iby_ext_bank_accounts ieb,
apps.ap_supplier_sites_all asa,
apps.ap_suppliers asp,
apps.ce_bank_branches_v cbbv
WHERE owners.primary_flag = ‘Y’
AND owners.ext_bank_account_id = ieb.ext_bank_account_id
AND owners.ext_bank_account_id = instrument.instrument_id
AND payees.ext_payee_id = instrument.ext_pmt_party_id
AND payees.payee_party_id = owners.account_owner_party_id
AND payees.supplier_site_id = asa.vendor_site_id
AND asa.vendor_id = asp.vendor_id
AND cbbv.branch_party_id(+) = ieb.branch_id
ORDER BY asa.vendor_site_code, owners.account_owner_party_id, asp.vendor_name;
Real-Time Scenario
During a periodic vendor master data audit, the AP team needed to verify that every active supplier site had a valid, non-duplicate primary bank account with a correctly populated IBAN and SWIFT code before a payment run. Running this query across all operating units surfaced a handful of sites where the bank_branch_type or eft_swift_code was null — flagged for correction before payments were released, preventing failed wire transfers.
Tip: If you need to include non-primary accounts too (e.g., to check for duplicates), drop the owners.primary_flag = ‘Y’ filter and add the flag as a selected column instead of filtering on it.
Conclusion
By mapping out the correct join path through the IBY payment-instrument tables into the standard AP supplier tables, this query gives a single, audit-ready view of supplier bank details — something Oracle doesn’t provide as a standard seeded report in R12. It eliminates manual UI lookups, ensures only the primary bank account is reported (avoiding duplicate/stale data), and can be easily extended (e.g., adding a vendor name filter, restricting to a specific operating unit, or scheduling it as a Concurrent Program) to fit recurring audit or reconciliation needs.
