Oracle Fusion - SQL Query for Suppliers

| 3 min read

Oracle Fusion - SQL Query for Suppliers

home/ERP

Supplier master extract: supplier number, name, procurement BU, status, type, business relationship and creator. Built for Energix on Oracle Fusion Cloud via the RunSQL.xdo BIP data model. A BU-secured variant below restricts rows to suppliers whose site BU the calling user is entitled to.

Query — base (no security) 

SELECT sup.segment1                    AS supplier_number,
       pty.party_name                  AS supplier_name,
       LISTAGG(DISTINCT bu.name, ', ')
         WITHIN GROUP (ORDER BY bu.name) AS supplier_bu,          -- procurement BU(s) from the sites
       CASE
         WHEN sup.enabled_flag = 'N'                        THEN 'Inactive'
         WHEN sup.end_date_active IS NOT NULL
              AND sup.end_date_active <= TRUNC(SYSDATE)     THEN 'Inactive'
         ELSE 'Active'
       END                             AS supplier_status,        -- derived; no STATUS column exists
       sup.vendor_type_lookup_code     AS supplier_type,
       sup.business_relationship        AS business_relationship,
       sup.created_by                  AS creator_username
FROM   poz_suppliers              sup,
       hz_parties                 pty,
       poz_supplier_sites_all_m   sit,
       hr_all_organization_units_vl bu
WHERE  pty.party_id      (+) = sup.party_id
AND    sit.vendor_id     (+) = sup.vendor_id
AND    TRUNC(SYSDATE) BETWEEN sit.effective_start_date (+) AND sit.effective_end_date (+)  -- current site row
AND    bu.organization_id (+) = sit.prc_bu_id
GROUP  BY sup.segment1,
          pty.party_name,
          sup.enabled_flag,
          sup.end_date_active,
          sup.vendor_type_lookup_code,
          sup.business_relationship,
          sup.created_by
ORDER  BY sup.segment1;

Query — BU-secured by caller email

Restricts output so a user sees a supplier only if that supplier has a site in a Business Unit the user has data access to. Same email→BU chain used across the row-level security work — see Oracle Assistant V3 - Row-Level Document Security and Orecle Fusion - Query for Data Access.

WITH me AS (                              -- resolve caller's verified email -> user_guid (fail-closed)
    SELECT pu.user_guid
    FROM   per_email_addresses pea,
           per_users           pu
    WHERE  pea.mastered_in_ldap_flag = 'Y'
    AND    SYSDATE BETWEEN NVL(pea.date_from, SYSDATE-1) AND NVL(pea.date_to, SYSDATE+1)
    AND    UPPER(pea.email_address) = UPPER(:p_user_email)     -- Make passes the Graph-verified email
    AND    pu.person_id = pea.person_id
),
my_bus AS (                               -- Business Units the caller has data access to
    SELECT DISTINCT bu.bu_id
    FROM   me,
           fun_user_role_data_asgnmnts role,
           fun_all_business_units_v    bu
    WHERE  role.user_guid = me.user_guid
    AND    role.org_id    = bu.bu_id                           -- BUSINESS UNIT data-access context
    AND    NVL(role.active_flag,'Y') = 'Y'
)
SELECT sup.segment1                    AS supplier_number,
       pty.party_name                  AS supplier_name,
       LISTAGG(DISTINCT bu.name, ', ')
         WITHIN GROUP (ORDER BY bu.name) AS supplier_bu,
       CASE
         WHEN sup.enabled_flag = 'N'                        THEN 'Inactive'
         WHEN sup.end_date_active IS NOT NULL
              AND sup.end_date_active <= TRUNC(SYSDATE)     THEN 'Inactive'
         ELSE 'Active'
       END                             AS supplier_status,
       sup.vendor_type_lookup_code     AS supplier_type,
       sup.business_relationship        AS business_relationship,
       sup.created_by                  AS creator_username
FROM   poz_suppliers              sup,
       hz_parties                 pty,
       poz_supplier_sites_all_m   sit,
       hr_all_organization_units_vl bu,
       my_bus
WHERE  pty.party_id      (+) = sup.party_id
AND    sit.vendor_id      = sup.vendor_id                     -- INNER: a visible supplier must have a site
AND    TRUNC(SYSDATE) BETWEEN sit.effective_start_date AND sit.effective_end_date
AND    bu.organization_id = sit.prc_bu_id
AND    sit.prc_bu_id      = my_bus.bu_id                      -- SECURITY GATE: site BU in caller's access
GROUP  BY sup.segment1,
          pty.party_name,
          sup.enabled_flag,
          sup.end_date_active,
          sup.vendor_type_lookup_code,
          sup.business_relationship,
          sup.created_by
ORDER  BY sup.segment1;

Field & table notes

  • Supplier number = poz_suppliers.segment1.
  • Name — not on poz_suppliers; it lives on the party (hz_parties.party_name via party_id). (The organization_name_phonetic "Alternate Name" was dropped from the final version.)
  • Supplier BU — not on the header. It comes from the procurement BU (prc_bu_id) on the supplier site (poz_supplier_sites_all_m, date-effective — the _M suffix is what's exposed; poz_supplier_sites_all is not). LISTAGG DISTINCT collapses multiple site BUs into one row per supplier.
  • Status — there is no STATUS column on poz_suppliers; it is derived from enabled_flag + end_date_active to match Active/Inactive in the Suppliers work area.
  • Type = vendor_type_lookup_code. business_relationship (Spend Authorized / Prospective) is the other "type" people often mean.
  • Creator = created_by (raw username). Resolving it to a display name via per_users / per_person_names_f failed with ORA-00942 in this data model — those views aren't exposed to RunSQL.xdo.
  • If hr_all_organization_units_vl errors (ORA-00942), swap it for fun_all_business_units_v and join bu.bu_id (+) = sit.prc_bu_id, selecting bu.bu_name.

Security model (BU-secured variant)

  • Email → user_guid: per_email_addresses (mastered_in_ldap_flag='Y', date-effective) → per_users — the same identity anchor as Oracle Assistant V3 - Row-Level Document Security. Bind the Graph-verified :p_user_email, never chat text (anti-spoof / anti-injection).
  • User → BU access: fun_user_role_data_asgnmnts.user_guid = per_users.user_guid with role.org_id = fun_all_business_units_v.bu_id (BUSINESS UNIT data-access context, from Orecle Fusion - Query for Data Access).
  • Gate: the supplier site's prc_bu_id must be one of the caller's accessible bu_ids. The site join becomes inner (a supplier with no accessible site must not leak), and supplier_bu then shows only the caller's BUs.
  • Fail-closed: an email that doesn't resolve, or a caller with no BU data access, yields zero rows.

קישורים (Zettelkasten)

📅 Weekly/2026-08-16