PO Buyer Listing Details (Oracle Fusion SQL)

Supply Chain & Manufacturing (SCM) Purchase Order (PO-Fusion-SQL's)
1

#oracle fusion sql

PO Buyer Listing Details — Oracle Fusion SQL Query

This post contains a ready-to-use SQL query for Oracle Fusion Purchasing — designed to list buyers, their assigned purchasing categories, ship-to locations, and effective date ranges across purchase orders (POs). It’s useful to help procurement teams track and maintain accurate “buyer responsibility” assignments.

What this query returns

  • Buyer ID and Buyer name
  • Purchase Order number and PO line number
  • Purchasing Category and Category name
  • Ship-to (location) code
  • Effective start and end date of buyer assignment (as per assignment tables)
  • List price at line level (optional, if lines are relevant)

Why you might use this report

  • To audit and verify buyer assignments for purchase orders — ensuring that correct buyer responsibilities are maintained per category / location
  • To track which buyers are responsible for POs by location, category or time period — useful for governance or compliance
  • To analyse PO distribution across buyers (by category or location) for workload balancing or procurement performance review
  • To support vendor management, procurement audits, or role-based reporting in your sourcing process

How to use & customize the query

  • Run the SQL against your Fusion Purchasing schema. Ensure your table aliases (e.g. PO_HEADERS_ALL, PO_LINES_ALL, category and location tables) match your instance.
  • If your setup includes additional attributes (e.g. vendor site, department, cost centre), you can extend the query by joining relevant tables.
  • To narrow down results, add filters — e.g. by PO date range, category ID/name, buyer ID, or location.
  • You can also group by buyer, category or location to get summary counts or spend distribution per buyer or category.

#sqlquery

SELECT 
  --SQL4Fusion (An Orbit Analytics Project)
  --Displays buyers with categories, locations, and effective dates for assignment tracking.
    ECT.CATEGORY_ID,
    ECT.CATEGORY_NAME              CATEGORY,
    -- Purchase Order Details
    PHA.PO_HEADER_ID, 
    PHA.SEGMENT1                   PURCHASE_ORDER_NUMBER,
    -- Purchase Order Line Info
    PLA.PO_LINE_ID,
    PLA.LINE_NUM                   LINE_NUMBER,
    PLA.LIST_PRICE,
    -- Buyer Information
    PHA.AGENT_ID                   BUYER_ID,
    PPNF.FULL_NAME                 BUYER_NAME,
    -- Location Information
    HLA.LOCATION_CODE              LOCATION,
    -- Effective Dates
    PAPF.EFFECTIVE_START_DATE      START_DATE,
    PAPF.EFFECTIVE_END_DATE        END_DATE
FROM 
    PO_HEADERS_ALL         PHA,
    PO_LINES_ALL           PLA,
    EGP_CATEGORIES_TL      ECT,
    HR_LOCATIONS_ALL       HLA,
    PER_ALL_PEOPLE_F       PAPF,
    PER_PERSON_NAMES_F     PPNF
WHERE 
        PHA.PO_HEADER_ID         = PLA.PO_HEADER_ID
    AND PLA.CATEGORY_ID          = ECT.CATEGORY_ID
    AND PHA.SHIP_TO_LOCATION_ID  = HLA.LOCATION_ID
    AND PHA.AGENT_ID             = PAPF.PERSON_ID
    AND PAPF.PERSON_ID           = PPNF.PERSON_ID(+)
    AND PPNF.NAME_TYPE(+)        = 'GLOBAL'
    AND ECT.LANGUAGE             = 'US'
    AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
    AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE;