# Oracle EBS Query for a Vendor Extract Report

> Source: https://netray.co/erp-help/oracle-ebs-supplier-vendor-extract-sql-ap-suppliers-sites-contacts-bank
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## Short answer

In EBS R12 the supplier header is AP_SUPPLIERS, supplier sites are AP_SUPPLIER_SITES_ALL (org-specific, so filter by ORG_ID), contacts link through AP_SUPPLIER_CONTACTS and HZ_PARTIES, and bank accounts live in the Payments tables (IBY_EXTERNAL_PAYEES_ALL, IBY_PMT_INSTR_USES_ALL, IBY_EXT_BANK_ACCOUNTS). Join them with VENDOR_ID and the party and payee keys.

## Kind

how-to

## System

oracle-ebs

## Erp

Oracle E-Business Suite

## Module

Payables / Trading Community (suppliers)

## Query

oracle ebs query for vendor extract report

## Alternate Queries

- oracle ebs supplier sql query ap_suppliers
- oracle r12 vendor sites contacts bank account query
- ap_supplier_sites_all query
- oracle ebs supplier extract

## Applies To

Oracle E-Business Suite R12.1 and 12.2 with Payables and Trading Community Architecture. Verify columns in your release before production use.

## Steps

### How to build the supplier extract

**Items:**

- Start from AP_SUPPLIERS (alias aps) and select VENDOR_ID, SEGMENT1 (supplier number), VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE, ENABLED_FLAG and END_DATE_ACTIVE.
- Join AP_SUPPLIER_SITES_ALL on VENDOR_ID for site code, address, pay group, inactive date and ORG_ID. Filter by ORG_ID for the operating unit you want.
- Add contacts by joining AP_SUPPLIER_CONTACTS to the site and to HZ_PARTIES on PER_PARTY_ID for the contact name.
- For bank accounts, join IBY_EXTERNAL_PAYEES_ALL on supplier site, then IBY_PMT_INSTR_USES_ALL on the payee, then IBY_EXT_BANK_ACCOUNTS for the account.
- Use outer joins for contacts and banks so suppliers without them still appear.
- Exclude or mask bank account numbers unless the report is approved for them.
- Run it from SQL Developer on a reporting copy, or wrap it in a concurrent program with a BI Publisher template.
- Check row counts against the Suppliers summary screen to confirm the joins did not multiply rows.

## Sections

### Core supplier and site query

**Body:**

Suppliers are global across operating units, but sites are specific to an operating unit, which is why the site table is the _ALL table with ORG_ID. Reading it through the multi-org view AP_SUPPLIER_SITES (which depends on the session policy context) gives different results from a raw SQL session, so for extracts use the _ALL table and filter ORG_ID explicitly.

Be aware that a supplier with five sites returns five rows. If the report should be one row per supplier, aggregate or reduce to the primary pay site.

**Code:**

SELECT aps.segment1 supplier_num, aps.vendor_name,
       aps.vendor_type_lookup_code, aps.enabled_flag, aps.end_date_active,
       apss.vendor_site_code, apss.org_id, apss.address_line1,
       apss.city, apss.country, apss.pay_group_lookup_code,
       apss.inactive_date
FROM   ap_suppliers aps
LEFT   JOIN ap_supplier_sites_all apss
       ON apss.vendor_id = aps.vendor_id
WHERE  apss.org_id = :org_id
ORDER  BY aps.vendor_name, apss.vendor_site_code

### Contacts and bank accounts

**Body:**

In R12 the contact record is a party relationship in the Trading Community Architecture, with AP_SUPPLIER_CONTACTS holding the supplier-specific link. Join it to HZ_PARTIES on PER_PARTY_ID for the person name, and use the contact point tables (HZ_CONTACT_POINTS) for email and phone.

Bank accounts moved to the Payments module. Payee records in IBY_EXTERNAL_PAYEES_ALL point to a supplier site, instrument assignments are in IBY_PMT_INSTR_USES_ALL, and the account details are in IBY_EXT_BANK_ACCOUNTS. Account numbers may be encrypted or masked depending on your security setup.

**Code:**

SELECT aps.vendor_name, apss.vendor_site_code,
       eba.bank_account_name, eba.currency_code
FROM   ap_suppliers aps
JOIN   ap_supplier_sites_all apss ON apss.vendor_id = aps.vendor_id
JOIN   iby_external_payees_all ep
       ON ep.supplier_site_id = apss.vendor_site_id
JOIN   iby_pmt_instr_uses_all iu
       ON iu.ext_pmt_party_id = ep.ext_payee_id
      AND iu.instrument_type = 'BANKACCOUNT'
JOIN   iby_ext_bank_accounts eba
       ON eba.ext_bank_account_id = iu.instrument_id
WHERE  apss.org_id = :org_id

### Security and data handling

**Body:**

Supplier bank details are among the most sensitive data in the ERP and a common fraud target. Limit the extract to what the recipient needs, mask account numbers, log who runs it, and avoid emailing the file. If the extract feeds a migration or an AI tool, strip or tokenise banking fields first.

For repeated use, a BI Publisher report on a data model gives access control and scheduling, in line with the BI Publisher report page on this site.

## Pitfalls

- Using the wrong site view and getting different results in SQL Developer than in the application.
- Forgetting the ORG_ID filter and mixing operating units.
- Inner joining contacts and bank accounts and dropping suppliers that have none.
- Exporting full bank account numbers into spreadsheets with no controls.
- Relying on pre-R12 column names such as the old PO_VENDORS tables for new work.

## Ai Angle

An assistant grounded in your EBS schema (ERPray with read-only access, showing the SQL it generated) can build and explain this kind of join when a buyer asks for inactive suppliers with open POs or sites missing a pay group. For EBS running on-premises, the model can be hosted on local hardware, which matters when the question touches supplier banking data. Treat generated SQL as something a DBA reviews, and keep banking columns excluded by default.

## Faq

### What table stores suppliers in Oracle EBS R12?

**Answer:** AP_SUPPLIERS holds the supplier header (it replaced PO_VENDORS in R12). Sites are in AP_SUPPLIER_SITES_ALL, and contacts reference HZ_PARTIES. The supplier is also registered as a party in the Trading Community.

### How do I get supplier bank accounts in R12?

**Answer:** Join the supplier site to IBY_EXTERNAL_PAYEES_ALL, then to IBY_PMT_INSTR_USES_ALL and IBY_EXT_BANK_ACCOUNTS. Banks and branches are held as parties. Mask the account number in any extract that is not strictly approved.

### Why does my supplier query return duplicate suppliers?

**Answer:** Because each supplier has multiple sites, contacts or bank accounts, and joining them multiplies rows. Aggregate, filter to primary sites, or produce separate extracts for each level.

### Can I use this query in Fusion Cloud?

**Answer:** No. Fusion Cloud does not give direct table access, and its supplier tables differ. Use the Suppliers REST resource or a BI Publisher report with Oracle's supplier data model.

## Search terms

oracle ebs query for vendor extract report, ap_suppliers, ap_supplier_sites_all, oracle r12 supplier query, oracle ebs vendor sql, iby_ext_bank_accounts supplier, oracle ebs supplier contacts query, oracle ebs vendor extract

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json