OracleFree generatorPDF, Excel, Word exportOracle Fusion Cloud SCM

Suppliers Table Oracle Fusion: Supplier Report SQL Template for BI Publisher

This template writes a BI Publisher SQL query against the Oracle Fusion suppliers table, POZ_SUPPLIERS, joined to supplier names and sites. Fill the fields and copy the SQL into a data model.

Fill in

Exact name from FUN_ALL_BUSINESS_UNITS_V.

Use uppercase. Leave as a single percent sign for all suppliers.

Generated code
Download:

Active suppliers with sites

SELECT sup.vendor_id,
       sup.segment1 AS supplier_number,
       hp.party_name AS supplier_name,
       sup.vendor_type_lookup_code AS supplier_type,
       sup.enabled_flag,
       sup.start_date_active,
       sup.end_date_active,
       bu.bu_name AS procurement_bu,
       site.vendor_site_code AS site_code,
       site.inactive_date AS site_inactive_date
FROM   poz_suppliers sup
JOIN   hz_parties hp
       ON hp.party_id = sup.party_id
LEFT JOIN poz_supplier_sites_all_m site
       ON site.vendor_id = sup.vendor_id
LEFT JOIN fun_all_business_units_v bu
       ON bu.bu_id = site.prc_bu_id
WHERE  sup.enabled_flag = 'Y'
AND    TRUNC(sup.creation_date) >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
AND    UPPER(hp.party_name) LIKE '%STEEL%'
AND    bu.bu_name = 'US1 Business Unit'
ORDER BY hp.party_name, site.vendor_site_code

Starting point for a BI Publisher data model. Table and column names can differ by Fusion update, so confirm them in the Tables and Views guide and test with a narrow date range. Supplier import uses the FBDI supplier template, a spreadsheet downloaded from the File Based Data Import guide for your release; load via the Import Suppliers process and check POZ_SUPPLIER_INT_REJECTIONS for failures. Remove the business unit filter to include suppliers without sites.

Ask supplier questions without writing SQL

Optional: see a private AI layer answer supplier questions over Fusion data.

Optional. The tool and every export work without this. No spam.

How to use the supplier report template

Enter the procurement business unit, a creation date filter, an optional name filter and the enabled flag. The generated query returns one row per supplier site with supplier number, name, type, active dates and site code. Paste it into a BI Publisher data model on the Fusion data source, add parameters in place of the literal values, and build a layout in Excel or the layout editor. Run it first with a narrow date range to confirm that joins return the rows you expect.

What the query joins and why

POZ_SUPPLIERS holds the supplier header, including the supplier number in SEGMENT1. The name is not on the header, so the query joins HZ_PARTIES on PARTY_ID. Sites are stored in POZ_SUPPLIER_SITES_ALL_M, which is why a supplier with three sites shows three rows, and PRC_BU_ID points at the procurement business unit, resolved through FUN_ALL_BUSINESS_UNITS_V. Filtering on the enabled flag and active dates keeps inactive or end-dated suppliers out of operational lists.

  • Supplier number is SEGMENT1
  • A supplier can have many sites across business units
  • Use LEFT JOIN so suppliers with no site still appear

Supplier report options without SQL

Not every supplier report needs custom SQL. Oracle Fusion offers supplier subject areas in OTBI for analysis, delivered supplier reports in the BI catalog, and the Manage Suppliers task for searching single records. Use SQL in BI Publisher when you need a specific layout, scheduled delivery or joins across modules that a subject area does not cover. For migrations, remember the supplier import template is a separate FBDI spreadsheet, not a report, and its errors are read from the import rejection report.

Common mistakes

Developers often forget that names live in HZ_PARTIES, use an inner join to sites and silently drop suppliers without sites, or compare dates without TRUNC. Another frequent error is hard-coding the business unit name instead of a parameter, which breaks when the report moves to another environment. Finally, do not copy an Oracle E-Business Suite query using PO_VENDORS, since that table does not exist in Fusion Cloud.

Frequently Asked Questions

What is the suppliers table in Oracle Fusion?

POZ_SUPPLIERS holds supplier headers, with names in HZ_PARTIES and sites in POZ_SUPPLIER_SITES_ALL_M. Verify exact columns for your Fusion update in the Tables and Views documentation.

Is PO_VENDORS available in Oracle Fusion?

No. PO_VENDORS is from Oracle E-Business Suite. In Fusion Cloud use POZ_SUPPLIERS and related POZ tables, so EBS supplier queries must be rewritten.

Where is the supplier import template in Oracle Fusion?

It is the FBDI supplier import spreadsheet, downloaded from the File Based Data Import documentation for your release. You load it through Scheduled Processes and check rejections after.

How do I schedule a supplier report?

Create the BI Publisher report from your data model, then schedule it with a delivery destination such as email. Parameterize date and business unit so each run is flexible.

Netray can give procurement a private natural-language layer over Fusion supplier data so common supplier questions do not need a new report each time.

Talk to an ExpertExplore More Free Tools

Related Tools

Oracle

Oracle Fusion Supplier and Organization Tables Reference

A reference for the Oracle Fusion supplier table, organization tables, and the FBDI interface and error tables used when supplier loads fail.

Oracle

Oracle Fusion Table Names Reference for SCM and Financials

A searchable reference of widely used Oracle Fusion SCM and Financials table names with what each holds and the keys to join on.

Oracle

Oracle Fusion SCM BI Publisher SQL Query Generator

Fill in organization, item and BU filters and get starter SQL for item, on-hand and open PO data models.

Oracle

Oracle Fusion BI Publisher Report Specification Template

Document a BI Publisher report before you build it: purpose, data model, parameters, layout, schedule and security in one template.

More Oracle tools

See all
Back to All Tools

Keep exploring

Free tools

ERP fixes and how-tos

Runs with your ERP

AI on your ERP

Integrations

Comparisons

Software