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 EBS R12 General Ledger (GL) Module – Practical SQL Queries

Introduction Oracle E-Business Suite (EBS) R12 General Ledger (GL) is the core Financial module in Oracle EBS R12. It is used to manage journal entries, accounting periods, balances, and financial reporting. The GL module integrates with Payables (AP), Receivables (AR), Fixed Assets (FA), Purchasing (PO), and Inventory (INV). The main purpose of a general ledger system is to record financial activity of a company and to produce financial and management reports to help the organization make decisions. Below are real-world SQL queries commonly used.   1) View configured Ledgers      Purpose: Fetch Ledger Configuration Details SELECT       ledger_id,      name  AS   ledger_name ,    short_name ,   currency_code FROM     gl_ledgers ORDER BY     ledger_id ;   2) Ledger Information Query Purpose: Fetch Ledger Configuration Details SELECT     gl.name   ...
Post a Comment
Read more

Exploring AI in Oracle APEX 26.1

Artificial Intelligence (AI) is transforming the way organizations and developers work. Businesses and professionals are increasingly adopting AI tools to improve productivity, automate processes, and build smarter applications. Oracle APEX introduces a significant evolution in application development by integrating governed Generative AI capabilities directly into the low-code platform. This enables developers to build intelligent, conversational applications while maintaining enterprise-grade security and control. In this article we explore AI capabilities in Oracle APEX 26.1 What is AI Agents AI Agents can be created to reason over user requests and take actions through approved AI Tools. Each tool exposes a specific application capability the agent is allowed to invoke, such as retrieving data, running server-side PL/SQL, or executing client-side JavaScript. APEX manages the execution flow by preparing context, dispatching tool calls, executing tools, handling results, and...
Post a Comment
Read more

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