Oracle EBS Query for a Vendor Extract Report

How to build the supplier extract

1. 1Start from AP_SUPPLIERS (alias aps) and select VENDOR_ID, SEGMENT1 (supplier number), VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE, ENABLED_FLAG and END_DATE_ACTIVE.
2. 2Join 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.
3. 3Add contacts by joining AP_SUPPLIER_CONTACTS to the site and to HZ_PARTIES on PER_PARTY_ID for the contact name.
4. 4For 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.
5. 5Use outer joins for contacts and banks so suppliers without them still appear.
6. 6Exclude or mask bank account numbers unless the report is approved for them.
7. 7Run it from SQL Developer on a reporting copy, or wrap it in a concurrent program with a BI Publisher template.
8. 8Check row counts against the Suppliers summary screen to confirm the joins did not multiply rows.

Contacts and bank accounts

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

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