Oracle Fusion Procurement SQL Guide: 18 Queries for POs, Requisitions, Receipts, and Spend Analysis

Oracle Fusion Procurement reporting often requires SQL across multiple modules - Purchasing, Receiving, Payables, and General Ledger. Even relatively simple reports usually involve joining four or five related tables.

This guide provides 18 practical Oracle Fusion Procurement SQL queries covering:

Purchase order tracking and lifecycle reporting
Open commitments and encumbrance by GL account
Supplier spend analysis
Receipt monitoring and exception tracking
Invoice matching and three-way match validation
Purchase requisition tracking
Requisition-to-PO tracing
Approval workflow visibility
Aging and overdue analysis

All queries are designed for Oracle Fusion reporting workflows and can be used as BI Publisher data models, OTBI validation queries, ad-hoc SQL analysis, and spend analysis foundations.

Procurement Reporting Architecture

Oracle Fusion Procurement is organized around Business Unit boundaries. Understanding how Procurement BUs relate to legal entities and ledgers is essential for writing SQL that returns meaningful, correctly scoped results.

Concept	Purpose	SQL Impact
Procurement BU	The business unit that owns and manages purchase orders	Filter by `PO_HEADERS_ALL.prc_bu_id`
Requisitioning BU	The BU that raises purchase requisitions - may differ from the Procurement BU	Filter by `POR_REQUISITION_HEADERS_ALL.requisitioning_bu_id`
Business Unit	Core security boundary for procurement data - controls which POs and requisitions are visible	Omitting BU filter returns data across all BUs in the system
Legal Entity	Statutory and accounting boundary - used in Payables processing	Joins through `AP_INVOICES_ALL.legal_entity_id`
Ledger	Accounting ledger - used for commitment and encumbrance reporting	Linked via `PO_DISTRIBUTIONS_ALL → GL_CODE_COMBINATIONS`

Key rule: Always filter by prc_bu_id when querying PO_HEADERS_ALL. Without this filter, results span all Procurement Business Units and produce inflated, misleading spend and commitment totals.

Core Oracle Fusion Procurement Tables

Oracle Fusion Procurement includes hundreds of tables across Purchasing, Receiving, Requisitions, Supplier Management, Payables, and Budgetary Control. The tables below represent the most frequently queried Oracle Fusion Procurement tables used for PO reporting, supplier analysis, receipt tracking, commitment reporting, and procure-to-pay analytics.

Table	Area	Purpose
PO_HEADERS_ALL	Purchasing	PO header information
PO_LINES_ALL	Purchasing	Purchase order lines
PO_LINE_LOCATIONS_ALL	Purchasing	Shipment schedules and receiving quantities
PO_DISTRIBUTIONS_ALL	Purchasing	Accounting distributions
AP_SUPPLIERS	Suppliers	Supplier master records
RCV_SHIPMENT_HEADERS	Receiving	Receipt batch headers
RCV_TRANSACTIONS	Receiving	Receipt transactions
AP_INVOICES_ALL	Payables	Supplier invoices
AP_INVOICE_LINES_ALL	Payables	Invoice lines
GL_CODE_COMBINATIONS	Finance	Account segment combinations
POR_REQUISITION_HEADERS_ALL	Requisitions	Purchase requisition headers
POR_REQUISITION_LINES_ALL	Requisitions	Purchase requisition lines and distributions

AP_SUPPLIERS vs POZ_SUPPLIERS: In some Oracle Fusion environments the supplier master is accessible via POZ_SUPPLIERS rather than AP_SUPPLIERS. Both views expose vendor_id and vendor_name with the same join pattern - if AP_SUPPLIERS is unavailable or returns no rows, try POZ_SUPPLIERS.

Understanding the Procurement Flow

Oracle Fusion Procurement data usually flows through this chain:

POR_REQUISITION_HEADERS_ALL
    ↓
POR_REQUISITION_LINES_ALL
    ↓
PO_HEADERS_ALL
    ↓
PO_LINES_ALL
    ↓
PO_LINE_LOCATIONS_ALL
    ↓
RCV_TRANSACTIONS
    ↓
AP_INVOICE_LINES_ALL
    ↓
AP_INVOICES_ALL

Understanding these relationships is essential for:

PO lifecycle reporting
Receipt tracking
Spend analysis
Three-way match validation
Open commitment reporting

Purchase Order Queries

Query 1 - Approved Purchase Orders with Line Details

Returns approved purchase orders with supplier and line-level pricing information.

SELECT
    ph.segment1                     AS po_number,
    ph.creation_date,
    ph.authorization_status,
    ph.currency_code,
    s.vendor_name,
    s.segment1                      AS supplier_number,
    pl.line_num,
    pl.item_description,
    pl.quantity,
    pl.unit_price,
    (pl.quantity * pl.unit_price)   AS line_total
FROM
    po_headers_all ph
    JOIN po_lines_all pl
        ON pl.po_header_id = ph.po_header_id
    JOIN ap_suppliers s
        ON s.vendor_id = ph.vendor_id
WHERE
    ph.authorization_status = 'APPROVED'
    AND ph.cancel_flag = 'N'
    AND ph.type_lookup_code = 'STANDARD'
ORDER BY
    ph.creation_date DESC,
    pl.line_num

Query 2 - Open Commitments

Find PO lines that are not fully received.

SELECT
    ph.segment1 AS po_number,
    pl.line_num,
    pl.item_description,
    pll.need_by_date,
    pll.quantity_ordered,
    NVL(pll.quantity_received, 0) AS quantity_received,
    (pll.quantity_ordered - NVL(pll.quantity_received, 0)) AS quantity_open,
    pl.unit_price,
    ((pll.quantity_ordered - NVL(pll.quantity_received, 0))
        * pl.unit_price) AS open_commitment_amount
FROM
    po_headers_all ph
    JOIN po_lines_all pl
        ON pl.po_header_id = ph.po_header_id
    JOIN po_line_locations_all pll
        ON pll.po_line_id = pl.po_line_id
WHERE
    ph.authorization_status = 'APPROVED'
    AND ph.cancel_flag = 'N'
    AND pll.cancel_flag = 'N'
    AND pll.quantity_ordered > NVL(pll.quantity_received, 0)
ORDER BY
    pll.need_by_date

Query 3 - POs Pending Approval

Monitor purchase orders still in approval workflow.

SELECT
    ph.segment1            AS po_number,
    ph.creation_date,
    ph.authorization_status,
    ph.total_amount,
    ph.currency_code,
    s.vendor_name,
    ph.agent_id            AS buyer_id
FROM
    po_headers_all ph
    JOIN ap_suppliers s
        ON s.vendor_id = ph.vendor_id
WHERE
    ph.authorization_status IN (
        'IN PROCESS',
        'PRE-APPROVED',
        'REQUIRES REAPPROVAL'
    )
    AND ph.cancel_flag = 'N'
ORDER BY
    ph.creation_date DESC

Query 4 - Cancelled Purchase Orders

Useful for procurement audit reporting.

SELECT
    ph.segment1            AS po_number,
    ph.creation_date,
    ph.cancel_flag,
    ph.closed_code,
    s.vendor_name,
    ph.comments
FROM
    po_headers_all ph
    JOIN ap_suppliers s
        ON s.vendor_id = ph.vendor_id
WHERE
    ph.cancel_flag = 'Y'
ORDER BY
    ph.creation_date DESC

Query 5 - PO Distribution Accounts

Shows accounting distributions tied to PO lines.

SELECT
    ph.segment1                    AS po_number,
    pl.line_num,
    pd.distribution_num,
    gcc.concatenated_segments      AS charge_account,
    pd.quantity_ordered,
    pd.quantity_delivered,
    pd.quantity_billed
FROM
    po_headers_all ph
    JOIN po_lines_all pl
        ON pl.po_header_id = ph.po_header_id
    JOIN po_distributions_all pd
        ON pd.po_line_id = pl.po_line_id
    JOIN gl_code_combinations gcc
        ON gcc.code_combination_id = pd.code_combination_id
WHERE
    ph.authorization_status = 'APPROVED'
ORDER BY
    ph.segment1,
    pl.line_num

Receipt and Receiving Queries

Query 6 - Recent Receipt Transactions

Track recent goods receipts.

SELECT
    rsh.shipment_num,
    rsh.receipt_date,
    rt.transaction_date,
    rt.transaction_type,
    rt.quantity,
    rt.unit_of_measure,
    ph.segment1             AS po_number,
    pl.item_description,
    s.vendor_name
FROM
    rcv_shipment_headers rsh
    JOIN rcv_transactions rt
        ON rt.shipment_header_id = rsh.shipment_header_id
    JOIN po_headers_all ph
        ON ph.po_header_id = rt.po_header_id
    JOIN po_lines_all pl
        ON pl.po_line_id = rt.po_line_id
    JOIN ap_suppliers s
        ON s.vendor_id = ph.vendor_id
WHERE
    rt.transaction_type = 'RECEIVE'
    AND rsh.receipt_date >= ADD_MONTHS(TRUNC(SYSDATE), -3)
ORDER BY
    rsh.receipt_date DESC

Query 7 - Overdue PO Lines

Find overdue purchase order lines not fully received.

SELECT
    ph.segment1 AS po_number,
    pl.line_num,
    pl.item_description,
    pll.need_by_date,
    TRUNC(SYSDATE) - pll.need_by_date AS days_overdue,
    pll.quantity_ordered,
    NVL(pll.quantity_received, 0) AS quantity_received,
    (pll.quantity_ordered - NVL(pll.quantity_received, 0))
        AS quantity_outstanding,
    s.vendor_name
FROM
    po_headers_all ph
    JOIN po_lines_all pl
        ON pl.po_header_id = ph.po_header_id
    JOIN po_line_locations_all pll
        ON pll.po_line_id = pl.po_line_id
    JOIN ap_suppliers s
        ON s.vendor_id = ph.vendor_id
WHERE
    ph.authorization_status = 'APPROVED'
    AND pll.need_by_date < TRUNC(SYSDATE)
    AND pll.quantity_ordered > NVL(pll.quantity_received, 0)
ORDER BY
    days_overdue DESC

Query 8 - Receipt Exceptions

Identify receipts with returns or corrections.

SELECT
    rsh.shipment_num,
    rt.transaction_type,
    rt.transaction_date,
    rt.quantity,
    ph.segment1 AS po_number,
    s.vendor_name
FROM
    rcv_transactions rt
    JOIN rcv_shipment_headers rsh
        ON rsh.shipment_header_id = rt.shipment_header_id
    JOIN po_headers_all ph
        ON ph.po_header_id = rt.po_header_id
    JOIN ap_suppliers s
        ON s.vendor_id = ph.vendor_id
WHERE
    rt.transaction_type IN (
        'RETURN TO VENDOR',
        'CORRECT'
    )
ORDER BY
    rt.transaction_date DESC

Supplier and Spend Analysis Queries

Query 9 - Procurement Spend by Supplier

Analyze supplier spend over the last 12 months.

SELECT
    s.vendor_name,
    s.segment1                    AS supplier_number,
    COUNT(DISTINCT ph.po_header_id) AS po_count,
    COUNT(pl.po_line_id)          AS line_count,
    SUM(pl.quantity * pl.unit_price) AS total_spend
FROM
    po_headers_all ph
    JOIN po_lines_all pl
        ON pl.po_header_id = ph.po_header_id
    JOIN ap_suppliers s
        ON s.vendor_id = ph.vendor_id
WHERE
    ph.authorization_status = 'APPROVED'
    AND ph.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -12)
GROUP BY
    s.vendor_name,
    s.segment1
ORDER BY
    total_spend DESC

Query 10 - Top 20 Suppliers by Spend

SELECT *
FROM (
    SELECT
        s.vendor_name,
        SUM(pl.quantity * pl.unit_price) AS total_spend
    FROM
        po_headers_all ph
        JOIN po_lines_all pl
            ON pl.po_header_id = ph.po_header_id
        JOIN ap_suppliers s
            ON s.vendor_id = ph.vendor_id
    WHERE
        ph.authorization_status = 'APPROVED'
    GROUP BY
        s.vendor_name
    ORDER BY
        total_spend DESC
)
WHERE ROWNUM <= 20

Query 11 - Supplier Spend by Business Unit

SELECT
    ph.prc_bu_id,
    s.vendor_name,
    SUM(pl.quantity * pl.unit_price) AS total_spend
FROM
    po_headers_all ph
    JOIN po_lines_all pl
        ON pl.po_header_id = ph.po_header_id
    JOIN ap_suppliers s
        ON s.vendor_id = ph.vendor_id
GROUP BY
    ph.prc_bu_id,
    s.vendor_name
ORDER BY
    total_spend DESC

Invoice Matching Queries

Query 12 - PO-Matched AP Invoices

Returns supplier invoices linked to purchase orders.

SELECT
    ph.segment1               AS po_number,
    ai.invoice_num,
    ai.invoice_date,
    ai.invoice_amount,
    ai.amount_paid,
    (ai.invoice_amount - NVL(ai.amount_paid, 0))
        AS outstanding_amount,
    ai.payment_status_flag,
    s.vendor_name
FROM
    ap_invoices_all ai
    JOIN ap_invoice_lines_all ail
        ON ail.invoice_id = ai.invoice_id
    JOIN po_headers_all ph
        ON ph.po_header_id = ail.po_header_id
    JOIN ap_suppliers s
        ON s.vendor_id = ai.vendor_id
WHERE
    ai.payment_status_flag IN ('N', 'P')
    AND ai.cancelled_date IS NULL
ORDER BY
    ai.invoice_date DESC

Query 13 - Invoices Without Matching Receipts

Identifies invoices where no receipt transaction exists at the PO header level. Note that the NOT EXISTS check operates at the po_header_id level - a partial receipt on any line excludes the invoice from results. For production three-way matching reports, validate receipt matching at the PO line or shipment schedule level.

SELECT
    ai.invoice_num,
    ai.invoice_date,
    ai.invoice_amount,
    s.vendor_name,
    ph.segment1 AS po_number
FROM
    ap_invoices_all ai
    JOIN ap_invoice_lines_all ail
        ON ail.invoice_id = ai.invoice_id
    JOIN po_headers_all ph
        ON ph.po_header_id = ail.po_header_id
    JOIN ap_suppliers s
        ON s.vendor_id = ai.vendor_id
WHERE
    NOT EXISTS (
        SELECT 1
        FROM rcv_transactions rt
        WHERE rt.po_header_id = ph.po_header_id
    )
ORDER BY
    ai.invoice_date DESC

Advanced Procurement Analysis Queries

Query 14 - Buyers with Highest PO Volume

Returns buyer names and PO volume by joining PER_PERSON_NAMES_F on agent_id to resolve the numeric buyer ID to a readable name.

SELECT
    pn.display_name          AS buyer_name,
    COUNT(*)                 AS po_count,
    SUM(ph.total_amount)     AS total_po_amount
FROM
    po_headers_all ph
    JOIN per_person_names_f pn
        ON  pn.person_id   = ph.agent_id
        AND pn.name_type   = 'GLOBAL'
        AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date
                               AND pn.effective_end_date
WHERE
    ph.authorization_status = 'APPROVED'
GROUP BY
    pn.display_name
ORDER BY
    total_po_amount DESC

Note: name_type = 'GLOBAL' returns the global display name in most Oracle Fusion environments. In some implementations name_type may use a localized value - verify against your PER_PERSON_NAMES_F data if no rows return.

Query 15 - Monthly Procurement Spend Trend

SELECT
    TO_CHAR(ph.creation_date, 'YYYY-MM') AS spend_month,
    SUM(pl.quantity * pl.unit_price)     AS monthly_spend
FROM
    po_headers_all ph
    JOIN po_lines_all pl
        ON pl.po_header_id = ph.po_header_id
WHERE
    ph.authorization_status = 'APPROVED'
GROUP BY
    TO_CHAR(ph.creation_date, 'YYYY-MM')
ORDER BY
    spend_month

Requisition Queries

Purchase requisitions live in the POR_ schema and represent demand prior to approved purchase orders. These tables are often overlooked but are essential for spend governance and procure-to-pay cycle reporting.

Query 16 - Open Requisitions

Returns approved requisition lines not yet sourced to a purchase order.

SELECT
    rh.requisition_number,
    rh.creation_date,
    rh.authorization_status,
    rh.requisitioning_bu_id,
    rl.line_num,
    rl.item_description,
    rl.quantity,
    rl.unit_price,
    (rl.quantity * rl.unit_price)   AS line_amount,
    rl.need_by_date
FROM
    por_requisition_headers_all rh
    JOIN por_requisition_lines_all rl
        ON rl.requisition_header_id = rh.requisition_header_id
WHERE
    rh.authorization_status = 'APPROVED'
    AND rl.line_location_id IS NULL   -- not yet sourced to a PO
ORDER BY
    rh.creation_date DESC

Note: rl.line_location_id IS NULL is a commonly used heuristic for unsourced lines. Validate this logic against your Oracle Fusion version, as sourcing behaviour may vary.

Query 17 - Requisition-to-PO Trace

Traces approved requisitions through to their corresponding purchase order lines - essential for procure-to-pay cycle analysis and spend governance reporting.

SELECT
    rh.requisition_number,
    rl.line_num              AS req_line_num,
    rl.item_description,
    rl.quantity              AS req_quantity,
    rl.unit_price            AS req_unit_price,
    ph.segment1              AS po_number,
    pl.line_num              AS po_line_num,
    pll.quantity_ordered     AS po_qty_ordered,
    pll.quantity_received    AS po_qty_received
FROM
    por_requisition_headers_all rh
    JOIN por_requisition_lines_all rl
        ON rl.requisition_header_id = rh.requisition_header_id
    JOIN por_requisition_distributions_all rd
        ON rd.requisition_line_id = rl.requisition_line_id
    JOIN po_distributions_all pd
        ON pd.req_distribution_id = rd.distribution_id
    JOIN po_line_locations_all pll
        ON pll.line_location_id = pd.line_location_id
    JOIN po_lines_all pl
        ON pl.po_line_id = pll.po_line_id
    JOIN po_headers_all ph
        ON ph.po_header_id = pl.po_header_id
WHERE
    rh.authorization_status = 'APPROVED'
ORDER BY
    rh.requisition_number,
    rl.line_num

Commitment Accounting

Commitment accounting tracks budget obligations created by approved purchase orders before the corresponding supplier invoice arrives. This is distinct from actual spend - it represents funds that are encumbered but not yet invoiced or paid.

Query 18 - Open Commitment by GL Account

Summarizes uncommitted PO spend by charge account - useful for budget controllers reconciling encumbrances against GL balances.

SELECT
    gcc.concatenated_segments           AS charge_account,
    SUM(pd.amount_ordered)              AS total_ordered,
    SUM(NVL(pd.amount_billed, 0))       AS total_billed,
    SUM(pd.amount_ordered
        - NVL(pd.amount_billed, 0))     AS open_commitment
FROM
    po_distributions_all pd
    JOIN po_headers_all ph
        ON ph.po_header_id = pd.po_header_id
    JOIN gl_code_combinations gcc
        ON gcc.code_combination_id = pd.code_combination_id
WHERE
    ph.authorization_status = 'APPROVED'
    AND ph.cancel_flag       = 'N'
    AND pd.cancel_flag       = 'N'
GROUP BY
    gcc.concatenated_segments
HAVING
    SUM(pd.amount_ordered
        - NVL(pd.amount_billed, 0)) > 0
ORDER BY
    open_commitment DESC

Note: This query reflects the PO distribution-level commitment view. For full encumbrance accounting including requisition-level commitments and funds reservation events, see GL_BC_PACKETS. Available columns depend on whether Budgetary Control is enabled in your Oracle Fusion environment.

Common Oracle Fusion Procurement SQL Mistakes

Missing Shipment Schedule Joins

Many receiving quantities exist at the shipment schedule level (PO_LINE_LOCATIONS_ALL), not only at the PO line level. Skipping shipment schedules often produces incorrect commitment calculations.

Ignoring Cancelled or Closed Records

Always filter when analyzing active commitments:

cancel_flag = 'N'
closed_code != 'FINALLY CLOSED'

Duplicate Rows from Invoice Matching

AP_INVOICE_LINES_ALL may contain multiple invoice lines per PO. Use proper grouping or aggregation to avoid row multiplication.

Confusing PO Header vs PO Line Amounts

Choose the correct granularity for the report:

PO_HEADERS_ALL.total_amount           -- PO total
PO_LINES_ALL.quantity * unit_price    -- line-level amount

Ignoring Business Unit Filters

PO_HEADERS_ALL and related procurement tables hold data from all Procurement Business Units. Omitting a prc_bu_id filter returns data across every BU in the system, producing inflated and misleading spend and commitment totals.

Accelerate Procurement SQL Development with FusionLens SQL

🔍

Procurement Schema Navigator

Browse Purchasing, Receiving, and Payables tables with column descriptions and data types - no documentation hunting.

📋

BI Publisher Ready

Queries plug directly into BI Publisher data models for purchase order reports, supplier analysis, and spend outputs.

🕐

SQL History

Save and revisit procurement SQL across sessions - useful for recurring spend reconciliation and ad-hoc supplier analysis.

Try FusionLens SQL Free

Final Thoughts

Oracle Fusion Procurement reporting requires understanding how Purchasing, Receiving, Payables, and General Ledger tables connect together.

Once these relationships are understood, SQL becomes one of the most powerful tools for:

Spend analysis and supplier performance reporting
Open commitment and encumbrance tracking by GL account
Requisition-to-PO tracing across procure-to-pay cycles
Three-way match validation
Receipt monitoring and overdue line detection
Procurement operations monitoring across Business Units

FusionLens SQL connects directly to Oracle Fusion Cloud. Explore Procurement schemas, trace relationships from requisitions through to invoices, and test SQL before moving it into BI Publisher.