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

Oracle Fusion Procurement reporting crosses several data domains: requisitions, purchasing documents, shipment schedules, receiving, Payables invoice matching, supplier master data, and accounting distributions. The difficult part is rarely the SELECT itself. It is choosing the correct grain and the correct bridge between those domains.

This guide provides 18 practical Oracle Fusion Procurement SQL patterns for purchase orders, receipts, supplier analysis, invoice matching, requisition-to-PO tracing, and open PO exposure. The examples use current Fusion table and column names and deliberately distinguish ordered PO value, received value, invoiced spend, and Budgetary Control encumbrance instead of treating them as the same measure.

Core Oracle Fusion Procurement Objects

The Procurement Join Path

The requisition-to-pay lifecycle is not one straight foreign-key chain. The important bridges are distributions and shipment schedules:

POR_REQUISITION_HEADERS_ALL
  -> POR_REQUISITION_LINES_ALL
  -> POR_REQ_DISTRIBUTIONS_ALL
  -> PO_DISTRIBUTIONS_ALL
  -> PO_LINE_LOCATIONS_ALL
  -> PO_LINES_ALL
  -> PO_HEADERS_ALL

Receiving:
PO_LINE_LOCATION_ID / PO_DISTRIBUTION_ID
  -> RCV_TRANSACTIONS

Payables matching:
PO / receipt references
  -> AP_INVOICE_LINES_ALL
  -> AP_INVOICES_ALL

That distinction matters. A header-level receipt check can hide an unreceived schedule because another line on the same PO was received, while joining invoice headers to multiple matched lines can duplicate invoice-level amounts.

Purchase Order Queries

Query 1 - Approved Standard PO Schedules with Supplier and Value

Uses the shipment schedule as the reporting grain so quantity-based and amount-based ordering can be handled without pretending every PO line is quantity × unit price.

SELECT
    ph.segment1                         AS po_number,
    ph.approved_date,
    ph.document_status,
    ph.prc_bu_id,
    ph.currency_code,
    hp.party_name                      AS supplier_name,
    ps.segment1                        AS supplier_number,
    pl.line_num,
    pl.item_description,
    pll.shipment_num,
    pll.need_by_date,
    pll.quantity                       AS schedule_quantity,
    pll.price_override,
    pll.amount                         AS schedule_amount,
    CASE
      WHEN pll.value_basis = 'QUANTITY'
        THEN NVL(pll.quantity,0) * NVL(pll.price_override, pl.unit_price)
      ELSE NVL(pll.amount,0)
    END                                AS ordered_value
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 poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ph.type_lookup_code = 'STANDARD'
  AND ph.approved_flag = 'Y'
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND NVL(pll.cancel_flag,'N') = 'N'
  AND ph.prc_bu_id = :p_prc_bu_id
ORDER BY ph.approved_date DESC, ph.segment1, pl.line_num, pll.shipment_num;

Query 2 - Open-to-Receive PO Schedules

This is an operational open-to-receive measure, not an accounting encumbrance.

SELECT
    ph.segment1 AS po_number,
    pl.line_num,
    pl.item_description,
    ph.currency_code,
    pll.need_by_date,
    pll.quantity,
    NVL(pll.quantity_received,0)  AS quantity_received,
    NVL(pll.quantity_cancelled,0) AS quantity_cancelled,
    CASE
      WHEN pll.value_basis = 'QUANTITY' THEN
        GREATEST(NVL(pll.quantity,0)
               - NVL(pll.quantity_received,0)
               - NVL(pll.quantity_cancelled,0), 0)
        * NVL(pll.price_override, pl.unit_price)
      ELSE
        GREATEST(NVL(pll.amount,0)
               - NVL(pll.amount_received,0)
               - NVL(pll.amount_cancelled,0), 0)
    END AS open_to_receive_value
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.approved_flag = 'Y'
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND NVL(pll.cancel_flag,'N') = 'N'
  AND ph.prc_bu_id = :p_prc_bu_id
  AND NVL(pll.schedule_status,'OPEN') NOT IN ('FINALLY CLOSED','INCOMPLETE','WITHDRAWN','REJECTED')
ORDER BY pll.need_by_date;

Query 3 - Purchase Orders Pending Approval

WITH po_value AS (
  SELECT pl.po_header_id,
         SUM(CASE
               WHEN pll.value_basis = 'QUANTITY'
                 THEN NVL(pll.quantity,0) * NVL(pll.price_override,pl.unit_price)
               ELSE NVL(pll.amount,0)
             END) AS po_value
  FROM po_lines_all pl
  JOIN po_line_locations_all pll
    ON pll.po_line_id = pl.po_line_id
  WHERE NVL(pll.cancel_flag,'N') = 'N'
  GROUP BY pl.po_header_id
)
SELECT ph.segment1 AS po_number,
       ph.creation_date,
       ph.document_status,
       ph.currency_code,
       pv.po_value,
       hp.party_name AS supplier_name,
       ph.agent_id AS buyer_person_id
FROM po_headers_all ph
LEFT JOIN po_value pv
  ON pv.po_header_id = ph.po_header_id
LEFT JOIN poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
LEFT JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ph.document_status = 'PENDING APPROVAL'
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND ph.prc_bu_id = :p_prc_bu_id
ORDER BY ph.creation_date DESC;

Query 4 - Canceled Purchase Orders

SELECT ph.segment1 AS po_number,
       ph.creation_date,
       ph.approved_date,
       ph.document_status,
       ph.cancel_flag,
       ph.closed_date,
       hp.party_name AS supplier_name,
       ph.comments
FROM po_headers_all ph
LEFT JOIN poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
LEFT JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE (NVL(ph.cancel_flag,'N') = 'Y' OR ph.document_status = 'CANCELED')
  AND ph.prc_bu_id = :p_prc_bu_id
ORDER BY ph.creation_date DESC;

Query 5 - PO Distribution Charge Accounts

SELECT ph.segment1 AS po_number,
       pl.line_num,
       pll.shipment_num,
       pd.distribution_num,
       gcc.concatenated_segments AS charge_account,
       pd.quantity_ordered,
       pd.quantity_delivered,
       pd.quantity_billed,
       pd.quantity_cancelled,
       pd.amount_ordered,
       pd.amount_delivered,
       pd.amount_billed,
       pd.amount_cancelled,
       pd.funds_status
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 po_distributions_all pd
  ON pd.line_location_id = pll.line_location_id
JOIN gl_code_combinations gcc
  ON gcc.code_combination_id = pd.code_combination_id
WHERE ph.approved_flag = 'Y'
  AND ph.prc_bu_id = :p_prc_bu_id
ORDER BY ph.segment1, pl.line_num, pll.shipment_num, pd.distribution_num;

Receipt and Receiving Queries

Query 6 - Recent Receipt Transactions

SELECT rsh.receipt_num,
       rsh.shipment_num AS supplier_shipment_num,
       rt.transaction_date,
       rt.transaction_type,
       rt.quantity,
       rt.uom_code,
       ph.segment1 AS po_number,
       pl.item_description,
       hp.party_name AS supplier_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 po_lines_all pl
  ON pl.po_line_id = rt.po_line_id
LEFT JOIN poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
LEFT JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE rt.transaction_type = 'RECEIVE'
  AND rt.transaction_date >= ADD_MONTHS(TRUNC(SYSDATE),-3)
  AND ph.prc_bu_id = :p_prc_bu_id
ORDER BY rt.transaction_date DESC;

Query 7 - Overdue PO Schedules Not Fully Received

SELECT ph.segment1 AS po_number,
       pl.line_num,
       pl.item_description,
       pll.shipment_num,
       pll.need_by_date,
       TRUNC(SYSDATE) - TRUNC(pll.need_by_date) AS days_overdue,
       hp.party_name AS supplier_name,
       ph.currency_code,
       CASE
         WHEN pll.value_basis = 'QUANTITY' THEN
           GREATEST(NVL(pll.quantity,0)
                  - NVL(pll.quantity_received,0)
                  - NVL(pll.quantity_cancelled,0),0)
           * NVL(pll.price_override,pl.unit_price)
         ELSE
           GREATEST(NVL(pll.amount,0)
                  - NVL(pll.amount_received,0)
                  - NVL(pll.amount_cancelled,0),0)
       END AS overdue_open_value
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
LEFT JOIN poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
LEFT JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ph.approved_flag = 'Y'
  AND ph.prc_bu_id = :p_prc_bu_id
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND NVL(pll.cancel_flag,'N') = 'N'
  AND pll.need_by_date < TRUNC(SYSDATE)
  AND (
       (pll.value_basis = 'QUANTITY' AND
        NVL(pll.quantity,0) - NVL(pll.quantity_received,0) - NVL(pll.quantity_cancelled,0) > 0)
       OR
       (NVL(pll.value_basis,'AMOUNT') <> 'QUANTITY' AND
        NVL(pll.amount,0) - NVL(pll.amount_received,0) - NVL(pll.amount_cancelled,0) > 0)
      )
ORDER BY days_overdue DESC;

Query 8 - Receipt Returns and Corrections

SELECT rsh.receipt_num,
       rt.transaction_id,
       rt.parent_transaction_id,
       rt.transaction_type,
       rt.transaction_date,
       rt.quantity,
       rt.uom_code,
       ph.segment1 AS po_number,
       hp.party_name AS supplier_name
FROM rcv_transactions rt
JOIN rcv_shipment_headers rsh
  ON rsh.shipment_header_id = rt.shipment_header_id
LEFT JOIN po_headers_all ph
  ON ph.po_header_id = rt.po_header_id
LEFT JOIN poz_suppliers ps
  ON ps.vendor_id = ph.vendor_id
LEFT JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE rt.transaction_type IN ('RETURN TO VENDOR','CORRECT')
  AND (:p_prc_bu_id IS NULL OR ph.prc_bu_id = :p_prc_bu_id)
ORDER BY rt.transaction_date DESC;

Supplier and Spend Analysis Queries

A purchase order is an authorization or commitment to buy; it is not the same thing as actual supplier spend. The next three queries therefore use PO-matched AP invoice lines as the spend measure. If your business definition of spend is cash paid, use invoice-payment/payment tables instead.

Query 9 - PO-Matched Invoiced Spend by Supplier (12 Months)

SELECT hp.party_name AS supplier_name,
       ps.segment1 AS supplier_number,
       ai.invoice_currency_code,
       COUNT(DISTINCT ai.invoice_id) AS invoice_count,
       COUNT(*) AS matched_invoice_line_count,
       SUM(ail.amount) AS po_matched_invoiced_spend
FROM ap_invoices_all ai
JOIN ap_invoice_lines_all ail
  ON ail.invoice_id = ai.invoice_id
JOIN poz_suppliers ps
  ON ps.vendor_id = ai.vendor_id
JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ail.po_header_id IS NOT NULL
  AND NVL(ail.discarded_flag,'N') = 'N'
  AND ai.cancelled_date IS NULL
  AND ai.invoice_date >= ADD_MONTHS(TRUNC(SYSDATE),-12)
  AND ai.org_id = :p_bu_id
GROUP BY hp.party_name, ps.segment1, ai.invoice_currency_code
ORDER BY po_matched_invoiced_spend DESC;

Query 10 - Top 20 Suppliers by PO-Matched Invoiced Spend

SELECT *
FROM (
  SELECT hp.party_name AS supplier_name,
         ps.segment1 AS supplier_number,
         SUM(ail.amount) AS po_matched_invoiced_spend
  FROM ap_invoices_all ai
  JOIN ap_invoice_lines_all ail
    ON ail.invoice_id = ai.invoice_id
  JOIN poz_suppliers ps
    ON ps.vendor_id = ai.vendor_id
  JOIN hz_parties hp
    ON hp.party_id = ps.party_id
  WHERE ail.po_header_id IS NOT NULL
    AND ai.cancelled_date IS NULL
    AND ai.org_id = :p_bu_id
    AND ai.invoice_currency_code = :p_currency_code
    AND ai.invoice_date >= ADD_MONTHS(TRUNC(SYSDATE),-12)
  GROUP BY hp.party_name, ps.segment1
  ORDER BY po_matched_invoiced_spend DESC
)
WHERE ROWNUM <= 20;

Query 11 - PO-Matched Invoiced Spend by Payables BU

SELECT ai.org_id AS payables_bu_id,
       hp.party_name AS supplier_name,
       ai.invoice_currency_code,
       COUNT(DISTINCT ai.invoice_id) AS invoice_count,
       SUM(ail.amount) AS po_matched_invoiced_spend
FROM ap_invoices_all ai
JOIN ap_invoice_lines_all ail
  ON ail.invoice_id = ai.invoice_id
JOIN poz_suppliers ps
  ON ps.vendor_id = ai.vendor_id
JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ail.po_header_id IS NOT NULL
  AND NVL(ail.discarded_flag,'N') = 'N'
  AND ai.cancelled_date IS NULL
  AND ai.invoice_date >= ADD_MONTHS(TRUNC(SYSDATE),-12)
GROUP BY ai.org_id, hp.party_name, ai.invoice_currency_code
ORDER BY ai.org_id, po_matched_invoiced_spend DESC;

Invoice Matching Queries

Query 12 - PO-Matched AP Invoice Lines

The query stays at invoice-line grain so a multi-line invoice does not repeat the header's full INVOICE_AMOUNT on every matched line.

SELECT ph.segment1 AS po_number,
       ai.invoice_num,
       ai.invoice_date,
       ai.invoice_currency_code,
       ail.line_number AS invoice_line_number,
       ail.line_type_lookup_code,
       ail.amount AS invoice_line_amount,
       ail.po_line_location_id,
       ail.po_distribution_id,
       ail.rcv_transaction_id,
       hp.party_name AS supplier_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 poz_suppliers ps
  ON ps.vendor_id = ai.vendor_id
JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ail.po_header_id IS NOT NULL
  AND NVL(ail.discarded_flag,'N') = 'N'
  AND ai.cancelled_date IS NULL
  AND ai.org_id = :p_bu_id
ORDER BY ai.invoice_date DESC, ai.invoice_num, ail.line_number;

Query 13 - Receipt-Required Invoice Lines with No Receipt on the Same PO Schedule

This is a diagnostic query, not a definitive “three-way match failure” report. It first limits the population to PO schedules where receipt is required, then checks receipt activity at that schedule grain.

SELECT ai.invoice_num,
       ai.invoice_date,
       ail.line_number AS invoice_line_number,
       ail.amount AS invoice_line_amount,
       ph.segment1 AS po_number,
       pl.line_num AS po_line_num,
       pll.shipment_num,
       hp.party_name AS supplier_name
FROM ap_invoices_all ai
JOIN ap_invoice_lines_all ail
  ON ail.invoice_id = ai.invoice_id
JOIN po_line_locations_all pll
  ON pll.line_location_id = ail.po_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
JOIN poz_suppliers ps
  ON ps.vendor_id = ai.vendor_id
JOIN hz_parties hp
  ON hp.party_id = ps.party_id
WHERE ai.cancelled_date IS NULL
  AND pll.receipt_required_flag = 'Y'
  AND ail.rcv_transaction_id IS NULL
  AND NOT EXISTS (
      SELECT 1
      FROM rcv_transactions rt
      WHERE rt.po_line_location_id = pll.line_location_id
        AND rt.transaction_type = 'RECEIVE'
  )
  AND ai.org_id = :p_bu_id
ORDER BY ai.invoice_date DESC;

Advanced Procurement Analysis Queries

Query 14 - Buyers with Highest Approved PO Value

WITH po_value AS (
  SELECT ph.po_header_id,
         ph.agent_id,
         ph.currency_code,
         SUM(CASE
               WHEN pll.value_basis = 'QUANTITY'
                 THEN NVL(pll.quantity,0) * NVL(pll.price_override,pl.unit_price)
               ELSE NVL(pll.amount,0)
             END) AS po_value
  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.approved_flag = 'Y'
    AND NVL(ph.cancel_flag,'N') = 'N'
    AND NVL(pll.cancel_flag,'N') = 'N'
    AND ph.prc_bu_id = :p_prc_bu_id
  GROUP BY ph.po_header_id, ph.agent_id, ph.currency_code
)
SELECT pn.display_name AS buyer_name,
       pv.currency_code,
       COUNT(DISTINCT pv.po_header_id) AS po_count,
       SUM(pv.po_value) AS approved_po_value
FROM po_value pv
JOIN per_person_names_f pn
  ON pn.person_id = pv.agent_id
 AND pn.name_type = 'GLOBAL'
 AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date AND pn.effective_end_date
GROUP BY pn.display_name, pv.currency_code
ORDER BY approved_po_value DESC;

Query 15 - Monthly Approved PO Value Trend

This is deliberately called PO value rather than spend. The approval date is used because it better represents when the document became an approved purchasing commitment than the original creation date.

SELECT TO_CHAR(ph.approved_date,'YYYY-MM') AS approval_month,
       ph.currency_code,
       SUM(CASE
             WHEN pll.value_basis = 'QUANTITY'
               THEN NVL(pll.quantity,0) * NVL(pll.price_override,pl.unit_price)
             ELSE NVL(pll.amount,0)
           END) AS approved_po_value
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.approved_flag = 'Y'
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND NVL(pll.cancel_flag,'N') = 'N'
  AND ph.prc_bu_id = :p_prc_bu_id
  AND ph.approved_date >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-12)
GROUP BY TO_CHAR(ph.approved_date,'YYYY-MM'), ph.currency_code
ORDER BY approval_month, ph.currency_code;

Requisition Queries

Query 16 - Approved Requisition Lines Not Yet Backed by a PO Distribution

SELECT rh.requisition_number,
       rh.creation_date,
       rh.document_status,
       rh.req_bu_id,
       rl.line_number,
       rl.item_description,
       rl.quantity,
       rl.unit_price,
       rl.need_by_date,
       rl.line_status
FROM por_requisition_headers_all rh
JOIN por_requisition_lines_all rl
  ON rl.requisition_header_id = rh.requisition_header_id
WHERE rh.document_status = 'APPROVED'
  AND rh.req_bu_id = :p_req_bu_id
  AND NVL(rl.cancel_flag,'N') = 'N'
  AND NVL(rl.line_status,'APPROVED') = 'APPROVED'
  AND NOT EXISTS (
      SELECT 1
      FROM por_req_distributions_all rd
      JOIN po_distributions_all pd
        ON pd.req_distribution_id = rd.distribution_id
      WHERE rd.requisition_line_id = rl.requisition_line_id
  )
ORDER BY rh.creation_date DESC, rh.requisition_number, rl.line_number;

Query 17 - Requisition-to-PO Trace

SELECT rh.requisition_number,
       rh.req_bu_id,
       rl.line_number AS req_line_number,
       rl.item_description,
       rd.distribution_number AS req_distribution_number,
       ph.segment1 AS po_number,
       ph.prc_bu_id,
       pl.line_num AS po_line_number,
       pll.shipment_num,
       pd.distribution_num AS po_distribution_number,
       pll.quantity AS po_schedule_quantity,
       pll.quantity_received,
       ph.document_status AS po_status
FROM por_requisition_headers_all rh
JOIN por_requisition_lines_all rl
  ON rl.requisition_header_id = rh.requisition_header_id
JOIN por_req_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.req_bu_id = :p_req_bu_id
ORDER BY rh.requisition_number, rl.line_number, rd.distribution_number;

Open PO Exposure and Budgetary Control

There are several different “open” amounts in Procurement. Open-to-receive measures what has not been received. Open-to-bill measures what has not yet been invoiced. Budgetary Control encumbrance is an accounting/funds-reservation concept and should not be reconstructed casually from PO header totals.

Query 18 - Unbilled PO Exposure by GL Charge Account

The calculation follows Oracle's quantity-versus-amount matching model at the PO distribution grain. It is an operational unbilled exposure report, not a Budgetary Control balance.

SELECT gcc.concatenated_segments AS charge_account,
       ph.currency_code,
       SUM(
         CASE
           WHEN NVL(pll.matching_basis,'QUANTITY') = 'AMOUNT' THEN
             GREATEST(NVL(pd.amount_ordered,0)
                    - NVL(pd.amount_cancelled,0)
                    - NVL(pd.amount_billed,0), 0)
           ELSE
             GREATEST(NVL(pd.quantity_ordered,0)
                    - NVL(pd.quantity_cancelled,0)
                    - NVL(pd.quantity_billed,0), 0)
             * NVL(pll.price_override, pl.unit_price)
         END
       ) AS unbilled_po_exposure
FROM po_distributions_all pd
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
JOIN gl_code_combinations gcc
  ON gcc.code_combination_id = pd.code_combination_id
WHERE ph.approved_flag = 'Y'
  AND NVL(ph.cancel_flag,'N') = 'N'
  AND NVL(pll.cancel_flag,'N') = 'N'
  AND pd.prc_bu_id = :p_prc_bu_id
GROUP BY gcc.concatenated_segments, ph.currency_code
HAVING SUM(
         CASE
           WHEN NVL(pll.matching_basis,'QUANTITY') = 'AMOUNT' THEN
             GREATEST(NVL(pd.amount_ordered,0)
                    - NVL(pd.amount_cancelled,0)
                    - NVL(pd.amount_billed,0), 0)
           ELSE
             GREATEST(NVL(pd.quantity_ordered,0)
                    - NVL(pd.quantity_cancelled,0)
                    - NVL(pd.quantity_billed,0), 0)
             * NVL(pll.price_override, pl.unit_price)
         END
       ) > 0
ORDER BY unbilled_po_exposure DESC;

Common Oracle Fusion Procurement SQL Mistakes

Final Thoughts

Reliable Oracle Fusion Procurement SQL comes from respecting transaction grain. Requisitions connect to purchase orders through distributions; purchase orders receive at schedule/distribution grain; Payables matches invoices at line level; and supplier names belong to the Fusion supplier/TCA model.

The 18 patterns above are intended as production-quality starting points, but Business Unit, currency, document type, receipt behavior, matching rules, tax treatment and Budgetary Control configuration still need to match your own reporting requirement.