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 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