AP Supplier Listing (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Payables (AP-Fusion-SQL's)
1

#oracle fusion sql

AP Supplier Listing — Oracle Fusion SQL Query

This post provides a ready-to-use SQL query (or set of queries) to list supplier master data from your Oracle Fusion Payables / Procurement setup. It helps you extract essential supplier information — including supplier number, name, type, addresses, sites, and key metadata — to build supplier directories, do due-diligence, or export supplier lists for reporting or integration.

What the query returns

Depending on your joins and schema configuration, the output can include:

  • Supplier number (vendor ID / segment1), supplier name, alternate name (if available), supplier type / classification.
  • Supplier status (active / inactive), creation date, last update date — useful to filter only valid or active suppliers.
  • Supplier site / location details: site code(s), address (street, city, state/province, country, ZIP/postal), contact-site metadata (if recorded).
  • Payment / invoice / supplier-site flags or attributes (e.g. whether the site is active for payables, purchasing, or other use) — helpful to know which supplier site is used for invoicing/payment.
  • (Optional) Banking/payment-method related data — if your setup stores payment instruments, bank-account or payment preference details linked to suppliers.

Why this report is useful

  • To build a master supplier directory (supplier name, number, addresses, sites) for procurement, payables, compliance or vendor-management teams.
  • To audit supplier data — check which suppliers are active, which have incomplete addresses or missing site information; identify duplicate suppliers or inactive ones.
  • To support vendor on-boarding or vendor-clean-up initiatives — exporting all supplier master data to spreadsheets, updating missing fields, or migrating supplier records.
  • To provide a data extract for integration with other systems (ERP-to-BI, vendor portals, payment systems, etc.).
  • To help in supplier-site wise reporting or segmentation (by geographic region, supplier site address, payment site, etc.).

How to use & customize the query

  • Run the SQL against your Fusion Procurement / Payables schema (tables like AP_SUPPLIERS and site-tables such as POZ_SUPPLIER_SITES_ALL_M or related supplier-site tables / views)
  • Include joins to address/party tables (if your setup uses separate address book or party-site tables) to fetch complete address, contact-site or location details.
  • Filter by supplier status, site-status, or supplier type (e.g. “active suppliers only”) to exclude outdated/inactive or one-time vendors.
  • Optionally, join with payment-method or bank-account tables if you need supplier payment-method/banking details — helpful for AP/payment processing reports.
  • Export the result to CSV or Excel if needed — for vendor-master audits, vendor upload templates, or vendor-onboarding workflows.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project) 
  --This query retrieves detailed information about the supplier (vendor) master data
  HAOU.NAME ORGANIZATION_NAME,
  PSV.VENDOR_NAME SUPPLIER_NAME,
  PSV.SEGMENT1 SUPPLIER_NUMBER,
  PSV.CUSTOMER_NUM CUSTOMER_NUMBER,
  PSV.TYPE_1099 INCOME_TAX_TYPE,
  (
    SELECT
      ZPTP.REP_REGISTRATION_NUMBER
    FROM
      ZX_PARTY_TAX_PROFILE ZPTP
    WHERE
      ZPTP.PARTY_TYPE_CODE = 'THIRD_PARTY'
      AND ZPTP.PARTY_ID = PSV.PARTY_ID
  ) AS VAT_REGISTRATION_NUMBER,
  DECODE (
    PSV.ENABLED_FLAG,
    'Y',
    'ACTIVE',
    'N',
    'INACTIVE',
    PSV.ENABLED_FLAG
  ) SUPPLIER_STATUS,
  PSV.ONE_TIME_FLAG,
  PSV.FEDERAL_REPORTABLE_FLAG,
  PSV.STATE_REPORTABLE_FLAG ,
  PSV.CREATION_DATE SUPPLIER_CREATION_DATE,
  PU.USERNAME SUPPLIER_CREATED_BY,
  PSV.LAST_UPDATE_DATE  SUPPLIER_UPDATE_DATE,
  PU2.USERNAME  SUPPLIER_LAST_UPDATED_BY,
  FLV.MEANING SUPPLIER_TYPE,
  PSV.VENDOR_ID SUPPLIER_ID,
  HAOU.ORGANIZATION_ID
 FROM
  POZ_SUPPLIERS_V PSV,
  POZ_SUPPLIER_SITES_V PSSV,
  FND_LOOKUP_VALUES FLV,
  PER_USERS PU,
  PER_USERS PU2,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND PSV.VENDOR_ID = PSSV.VENDOR_ID
  AND PSV.VENDOR_TYPE_LOOKUP_CODE = FLV.LOOKUP_CODE(+)  
  AND FLV.LANGUAGE(+) = USERENV ('LANG')
  AND FLV.VIEW_APPLICATION_ID(+) = 201  
  AND FLV.LOOKUP_TYPE(+) = 'VENDOR TYPE'
  AND PSV.CREATED_BY = PU.USERNAME(+)
  AND PSV.LAST_UPDATED_BY = PU2.USERNAME(+)
  AND HAOU.ORGANIZATION_ID = PSSV.PRC_BU_ID