PO Buyer Listing Report (SQL Script)

Supply Chain & Manufacturing (SCM) Purchase Order (PO)
1

This PO Buyer Listing Report query displays purchasing category, ship-to location, and effective date assignments for each buyer across purchase orders, helping procurement teams maintain accurate and up-to-date buyer responsibility records.

#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;