Skip to main content

Oracle ERP R12 Purchasing Module – Useful SQL Queries Every Developer Should Know

Oracle Purchasing (PO Module) in Oracle ERP R12 has a significant role by managing requisitions and purchase orders . It integrates with key modules such as Inventory, Payables, and General Ledger to ensure smooth business operations.

Below are real-world SQL queries commonly used.

Note: Replace :ORG_ID with your Operating Unit ID (e.g., 89)  and :PO with the required PO number (e.g., 'PO-10045')

 1) Purchase Order Header Details

Purpose: Get basic PO information for specific Org

SELECT
    pha.segment1 AS po_number,
    pha.type_lookup_code AS po_type,
    pha.authorization_status,
    pha.creation_date,
    pv.vendor_name,
    pv.vendor_id
FROM
    po_headers_all pha,
    po_vendors pv
WHERE
    pha.vendor_id = pv.vendor_id
    AND pha.org_id = :ORG_ID;

 

 2) Purchase Order Line Details

Purpose: View items and quantities in PO

SELECT
    pha.segment1 AS po_number,
    pla.line_num,
    msi.segment1 AS item_code,
    pla.quantity,
    pla.unit_price
FROM
    po_headers_all pha,
    po_lines_all pla,
    mtl_system_items_b msi
WHERE
    pha.po_header_id = pla.po_header_id
    AND pla.item_id = msi.inventory_item_id
       AND pha.segment1 = :PO;

 3) PO Shipment Details

Purpose: Track PO delivery schedules and receipt status

SELECT
    pha.segment1 AS po_number,
    pla.line_num,
    plla.shipment_num,
    plla.quantity,
    plla.quantity_received,
    plla.need_by_date
FROM
    po_headers_all pha,
    po_lines_all pla,
    po_line_locations_all plla
WHERE
    pha.po_header_id = pla.po_header_id
    AND pla.po_line_id = plla.po_line_id
    AND pha.segment1 = :PO;

 

  4) PO Receiving Details

Purpose: Track goods receipts against purchase orders

SELECT
    rsh.receipt_num,
    pha.segment1 AS po_number,
    rsl.quantity_received,
    rsl.destination_type_code
FROM
    rcv_shipment_headers rsh,
    rcv_shipment_lines rsl,
    po_headers_all pha
WHERE
    rsh.shipment_header_id = rsl.shipment_header_id
    AND rsl.po_header_id = pha.po_header_id
    AND pha.segment1 = :PO;

 

 5) Supplier & Site Details

Purpose: Get vendor and site info

SELECT
    pv.vendor_name,
    pvs.vendor_site_code,
    pvs.city,
    pvs.country
FROM
    po_vendors pv,
    po_vendor_sites_all pvs
WHERE
    pv.vendor_id = pvs.vendor_id;

 

 6) Pending Purchase Orders

Purpose: Identify pending POs

SELECT
    pha.segment1 AS po_number,
    pha.authorization_status,
    plla.quantity,
    plla.quantity_received,
    (plla.quantity - plla.quantity_received) AS pending_qty
FROM
    po_headers_all pha,
    po_lines_all pla,
    po_line_locations_all plla
WHERE
    pha.po_header_id = pla.po_header_id
    AND pla.po_line_id = plla.po_line_id
    AND pha.authorization_status = 'APPROVED'
    AND (plla.quantity - plla.quantity_received) > 0
    AND pha.org_id = :ORG_ID;

 

 7) Cancelled Purchase Orders

Purpose: Track cancelled POs

SELECT
    segment1 AS po_number,
    authorization_status,
    cancel_flag
FROM
    po_headers_all
WHERE
    cancel_flag = 'Y'
    AND org_id = :ORG_ID;

 

 8) PO Approval History

Purpose: Track approval workflow

SELECT
    pha.segment1 AS po_number,
    pah.action_code,
    pah.action_date,
    pah.employee_id
FROM
    po_headers_all pha,
    po_action_history pah
WHERE
    pha.po_header_id = pah.object_id
   AND pha.segment1 = :PO;

 

  9) PO Wise Invoice Detail

Purpose: PO Wise Invoice Summary

SELECT
    pha.segment1 AS po_number,
    aia.invoice_num,
    aia.invoice_amount
FROM
    po_headers_all pha,
    ap_invoices_all aia,
    ap_invoice_lines_all aila
WHERE
    pha.po_header_id = aila.po_header_id
    AND aia.invoice_id = aila.invoice_id
    AND pha.segment1 = :PO;

 

Conclusion

These SQL queries cover the Purchase Order lifecycle in Oracle ERP R12.

Key Points:

  • All queries are based on standard Oracle Apps base tables
  • Parameters :ORG_ID and :PO make queries flexible and reusable
  • Covers both operational and analytical reporting needs
  • Suitable for use in  BI Publisher, or direct SQL execution


 

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