Skip to main content

Oracle EBS R12: Procure-to-Pay (P2P) SQL Queries

 Introduction

P2P Procure-to-Pay in Oracle E-Business Suite (EBS) is a fully integrated business process followed by any organization that handles from requesting goods or services to paying the supplier and accounting for the transaction.

In this article i discuss commonly used P2P queries for reporting and analysis.

1. Purchase Order Details

Purpose: Fetch PO header and line details along with the linked supplier

 SELECT

    pha.segment1            AS po_number,

    pha.type_lookup_code    AS po_type,

    pha.authorization_status,

    pha.creation_date,

    aps.vendor_name,

    aps.segment1            AS supplier_number,

    pla.line_num,

    pla.item_description,

    pla.quantity,

    pla.unit_price,

    (pla.quantity * pla.unit_price) AS line_amount

FROM

    po_headers_all pha

JOIN

    po_lines_all pla

    ON pha.po_header_id = pla.po_header_id

JOIN

    ap_suppliers aps

    ON pha.vendor_id = aps.vendor_id

 ORDER BY

    pha.segment1, pla.line_num;

 

 2. PO with Receipt Details

Purpose:  Fetches the quantity received and accepted against each PO line.

SELECT

    pha.segment1 AS po_number,

    pla.line_num,

    rsh.receipt_num,

    rsh.creation_date AS receipt_date,

     SUM(

        CASE

            WHEN rt.transaction_type = 'RECEIVE' THEN rt.quantity

            WHEN rt.transaction_type = 'RETURN TO VENDOR' THEN -rt.quantity

            ELSE 0

        END

    ) AS quantity_received,

     SUM(

        CASE

            WHEN rt.transaction_type = 'ACCEPT' THEN rt.quantity

            ELSE 0

        END

    ) AS quantity_accepted

 FROM

    po_headers_all pha

 JOIN

    po_lines_all pla

    ON pha.po_header_id = pla.po_header_id

 JOIN

    po_line_locations_all pll

    ON pla.po_line_id = pll.po_line_id

 JOIN

    rcv_transactions rt

    ON pll.line_location_id = rt.po_line_location_id

 JOIN

    rcv_shipment_headers rsh

    ON rt.shipment_header_id = rsh.shipment_header_id

 WHERE

    rt.source_document_code = 'PO'

    AND pha.authorization_status = 'APPROVED'

    AND NVL(pha.cancel_flag, 'N') = 'N'

    AND NVL(pla.cancel_flag, 'N') = 'N'

    AND NVL(pll.cancel_flag, 'N') = 'N'

 GROUP BY

    pha.segment1,

    pla.line_num,

    rsh.receipt_num,

    rsh.creation_date

 ORDER BY

    pha.segment1,

    pla.line_num,

    rsh.creation_date;   

3. Open Purchase Orders (Not Fully Received)

Purpose: Identifies PO lines where the full ordered quantity has not yet been received.

SELECT

    pha.segment1 AS po_number,

    pha.creation_date,

    pha.authorization_status,

     pla.line_num,

    pla.item_description,

    pla.quantity AS ordered_qty,

     NVL(r.received_qty, 0) AS received_qty,

     (pla.quantity - NVL(r.received_qty, 0)) AS pending_qty

 FROM

    po_headers_all pha

 JOIN

    po_lines_all pla

    ON pha.po_header_id = pla.po_header_id

 JOIN

    po_line_locations_all pll

    ON pla.po_line_id = pll.po_line_id

LEFT JOIN

    (

        SELECT

            rt.po_line_location_id,

            SUM(

                CASE

                    WHEN rt.transaction_type = 'RECEIVE' THEN rt.quantity

                    WHEN rt.transaction_type = 'RETURN TO VENDOR' THEN -rt.quantity

                    ELSE 0

                END

            ) AS received_qty

        FROM

            rcv_transactions rt

        WHERE

            rt.source_document_code = 'PO'

        GROUP BY

            rt.po_line_location_id

    ) r

    ON pll.line_location_id = r.po_line_location_id

 WHERE

    (pll.quantity - NVL(r.received_qty, 0)) > 0

     AND pha.authorization_status = 'APPROVED'

    AND NVL(pha.cancel_flag, 'N') = 'N'

    AND NVL(pla.cancel_flag, 'N') = 'N'

    AND NVL(pll.cancel_flag, 'N') = 'N'

ORDER BY

    pha.creation_date DESC,

    pha.segment1,

    pla.line_num;

 

  4. Invoice Payment Details with Supplier

Purpose: Lists invoices with supplier names, amounts, and payment status.

SELECT

    aia.invoice_id,

    aia.invoice_num,

    aia.invoice_date,

    aia.invoice_amount,

    aps.vendor_name,

    CASE

        WHEN NVL(aia.amount_paid, 0) = aia.invoice_amount THEN 'Paid'

        WHEN NVL(aia.amount_paid, 0) = 0 THEN 'Unpaid'

        WHEN NVL(aia.amount_paid, 0) > 0

             AND NVL(aia.amount_paid, 0) < aia.invoice_amount THEN 'Partially Paid'

        ELSE 'Unknown'

    END AS payment_status,

      NVL(aia.amount_paid, 0) AS amount_paid,

     (aia.invoice_amount - NVL(aia.amount_paid, 0)) AS remaining_amount

 FROM

    ap_invoices_all aia

 JOIN

    ap_suppliers aps

    ON aia.vendor_id = aps.vendor_id

 WHERE

    aia.cancelled_date IS NULL

    AND aia.invoice_type_lookup_code = 'STANDARD'

ORDER BY

    aia.invoice_date DESC;


5. Outstanding Invoices

Purpose: Returns invoices with an outstanding balance.

 SELECT

    aia.invoice_id,

    aia.invoice_num,

    aia.invoice_amount,

    apsup.vendor_name,

     SUM(aps.amount_remaining) AS total_remaining,

    MIN(aps.due_date) AS next_due_date

 FROM

    ap_invoices_all aia

 JOIN

    ap_payment_schedules_all aps

    ON aia.invoice_id = aps.invoice_id

 JOIN

    ap_suppliers apsup

    ON aia.vendor_id = apsup.vendor_id

 WHERE

    aps.amount_remaining > 0

    AND aia.cancelled_date IS NULL

 GROUP BY

    aia.invoice_id,

    aia.invoice_num,

    aia.invoice_amount,

    apsup.vendor_name

 ORDER BY

    next_due_date;

 

6. Supplier Wise Spend Analysis

Purpose: Analyzes total invoice spend, number of invoices, and payments made for each supplier.

SELECT

    aps.vendor_id,

    aps.vendor_name,

     COUNT(DISTINCT aia.invoice_id) AS total_invoices,

     SUM(aia.invoice_amount) AS total_spend,

     SUM(NVL(aia.amount_paid, 0)) AS total_paid

 FROM

    ap_invoices_all aia

 JOIN

    ap_suppliers aps

    ON aia.vendor_id = aps.vendor_id

 WHERE

    aia.cancelled_date IS NULL

    AND aia.invoice_type_lookup_code = 'STANDARD'

    AND aia.payment_status_flag = 'Y'   

 GROUP BY

    aps.vendor_id,

    aps.vendor_name

 ORDER BY

    total_spend DESC;

7. Cancelled Purchase Orders

Purpose: Retrieves all POs that have been cancelled.

SELECT

    segment1 AS po_number,

    authorization_status,

    cancel_flag

FROM po_headers_all

WHERE

(
NVL(cancel_flag, 'N') = 'Y'
OR authorization_status = 'CANCELLED'
);

 

 8. PO Receiving Transactions (Receipt & Shipment Level)

Purpose: This query retrieves all receiving transactions recorded against a Purchase Order at the shipment level

SELECT

    pha.segment1                AS po_number,

    pla.line_num                AS po_line_number,

    pla.item_description        AS item_description,

    pll.shipment_num            AS shipment_number,

    rsh.receipt_num             AS receipt_number,

    rt.transaction_type         AS transaction_type,

    rt.quantity                 AS transaction_quantity,

    rt.unit_of_measure          AS uom,

    rt.transaction_date         AS transaction_date

FROM po_headers_all         pha

JOIN po_lines_all            pla 

    ON pha.po_header_id = pla.po_header_id

JOIN po_line_locations_all   pll 

    ON pla.po_line_id = pll.po_line_id

JOIN rcv_transactions        rt  

    ON pll.line_location_id = rt.po_line_location_id

JOIN rcv_shipment_headers    rsh 

    ON rt.shipment_header_id = rsh.shipment_header_id

 WHERE

       rt.transaction_date >= :p_from_date ------ e.g rt.transaction_date >= '01-Jan-2026'

      AND rt.transaction_date < :p_to_date ------- e.g rt.transaction_date < '15-Apr-2026'

     AND pha.authorization_status = 'APPROVED'

    AND NVL(pha.cancel_flag,'N') = 'N'

    AND NVL(pla.cancel_flag,'N') = 'N'

    AND NVL(pll.cancel_flag,'N') = 'N'

    AND rt.source_document_code = 'PO'

    AND rt.transaction_type IN ('RECEIVE','RETURN TO VENDOR')

ORDER BY

    pha.segment1,

    pla.line_num,

    pll.shipment_num,

    rt.transaction_date;

  

9. PO Approval Status Report

Purpose: Reports PO approval status with creation and approval dates

SELECT

    pha.segment1 AS po_number,

    pha.authorization_status,

    pha.creation_date,

    MAX(pah.action_date) AS approved_date

FROM po_headers_all pha

LEFT JOIN po_action_history pah

    ON pha.po_header_id = pah.object_id

   AND pah.object_type_code = 'PO'

   AND pah.action_code = 'APPROVE'

WHERE

   pha.creation_date >= :P_FROM_DATE  ----pha.creation_date >= '01-Apr-2026'

AND pha.creation_date < :P_TO_DATE---- pha.creation_date < '14-Apr-2026'

  AND pha.authorization_status = 'APPROVED'

GROUP BY

    pha.segment1,

    pha.authorization_status,

    pha.creation_date

ORDER BY

    pha.creation_date DESC;

 
Conclusion

These queries cover the most commonly needed P2P scenarios in Oracle EBS R12,

Including Purchase Orders, Receiving, Invoicing, Payments, and Supplier Analysis

Comments

Post a Comment

Popular posts from this blog

Oracle R12 Inventory Configuration: Key points to Avoid Mistakes

Most Oracle ERP R12 inventory problems are not system failures they are process and configuration failures. The same categories of mistakes surface repeatedly across implementations: Focusing on master data control, disciplined transactions, proper OU setup, and strong internal controls prevents operational and financial discrepancies.   This article walks through the four most critical areas where Oracle R12 inventory implementations go wrong, explains why each mistake is costly, and provides recommendations to prevent them. Each section includes the relevant navigation path and configuration screenshots for hands-on reference   1.       Improper Item Master Configuration in Oracle R12   Oracle E‑Business Suite R12 , the Item Master Configuration serves as the foundation of the Inventory module. Two critical components under this configuration umbrella are: Define Master Item Setup Define Item Status Both setups control how items ...
Post a Comment
Read more

Monitoring and Troubleshooting Oracle APEX Performance with Real-World SQL Queries

    Introduction Oracle Application Express (APEX) is one of the most widely adopted low-code development platforms in the Oracle ecosystem. As organizations scale their APEX deployments, the need for robust monitoring and troubleshooting capabilities becomes critical. This article provides SQL queries that DBAs and developers can use to monitor, diagnose, and optimize their APEX environments.   What makes monitoring APEX unique is the three-tier architecture it operates on: the Oracle Database at the data layer, APEX at the application layer, and Oracle REST Data Services (ORDS) as the middleware. Understanding how sessions flow through these three layers is the key to effective monitoring.     Architecture Overview   Layer Component Version Role Database Oracle DB 19.28 Stores data, executes SQL, manages sessions Application Oracle AP...
Post a Comment
Read more
SQL Query Performance Tuning: A Practical Approach for Oracle DBAs Inefficient queries in Oracle Database can significantly impact system performance, resulting in higher response times and decreased user productivity. By closely monitoring memory usage and CPU consumption, database administrators can identify performance bottlenecks and determine the root causes of inefficient query execution. Oracle Query Performance Tuning Checklist 1.          Monitor Memory Usage:                Purpose: Ensure optimal memory allocation to prevent excessive disk I/O and performance degradation.             Actions: * Track memory consumption using Oracle Enterprise Manager *Automatic Workload Repository (AWR) reports to review SGA and PGA utilization, and identify abnormal memory usage patterns. Analyze AWR reports for: a.   ...
Post a Comment
Read more