PO Headers and Lines – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Purchasing · all Purchasing reports

Overview

PO headers, lines, receiving transactions and corresponding AP invoices

When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, GST TAN No, CGST Amount, SGST Amount, IGST Amount, CGST Rate, SGST Rate, IGST Rate, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Operating Unit, Receiving Organization, PO Number, Release, Buyer, Supplier, Supplier Site, Item, Item From, Item To, Item Type, Category Set 1, Category Set 2, Category Set 3, WIP Job, Project, Has Open Quantity, Promised, Overdue, Document Type, Document Closure Status, Document Creation Date From, Document Creation Date To, Document Created By, Exclude Cancelled, Open Lines/Shipments only, Promised Date From, Promised Date To, Need By Date From, Need By Date To, Receipt Date From, Receipt Date To, PO Unit Price From, PO Unit Price To, Show Distributions, DFF Display, Show Attachment Details

Oracle EBS Tables Used

po_line_locations_all, po_distributions_all, gl_daily_conversion_types, mrp_item_purchase_orders, mrp_recommendations, mrp_full_pegging, mrp_gross_requirements, wip_entities, po_line_locations_archive_all, po_action_history, po_headers_all, financials_system_params_all, po_lines_all, hr_operating_units, gl_sets_of_books, po_releases_all, pa_projects_all, pa_tasks, ap_suppliers, ap_supplier_sites_all, fnd_territories_vl, po_vendor_contacts, po_document_types_all_vl, po_line_types_v, hr_locations_all_tl, per_people_x, rcv_shipment_lines, rcv_shipment_headers, rcv_transactions, hr_locations_all, mtl_parameters, mtl_system_items_vl, mtl_units_of_measure_tl, mtl_categories_kfv, cst_item_costs, ap_invoice_lines_all, ap_invoices_all, fnd_attached_documents, fnd_documents, fnd_documents_tl, fnd_document_datatypes, fnd_document_categories_vl, fnd_lobs, fnd_documents_short_text, fnd_documents_long_text

Report Categories

Enginatics

Related Reports

MRP Pegging, CRP Resource Plan, MRP End Assembly Pegging, GL Account Distribution Analysis, INV Item Upload 11i, WIP Discrete Job Shortage, INV Items, INV Item Upload, CAC Purchase Price Variance

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource	Link
Excel Example Output	PO Headers and Lines 01-May-2021 005057.xlsx
Blitz Report™ XML Import	PO_Headers_and_Lines.xml
SQL Source	PO Headers and Lines.sql
Full Report on Enginatics	www.enginatics.com/reports/po-headers-and-lines/

Case Study: End-to-End Procurement Lifecycle Visibility

Executive Summary

The PO Headers and Lines report provides a comprehensive, “cradle-to-grave” view of the procurement process within Oracle E-Business Suite. By consolidating data from purchase order headers, lines, shipments, and distributions—and linking them to receiving transactions and Accounts Payable invoices—this report enables organizations to track the complete lifecycle of every purchasing document. It is an essential tool for procurement managers, buyers, and financial analysts to monitor spend, ensure compliance, and streamline the procure-to-pay cycle.

Business Challenge

Managing procurement in a complex ERP environment often involves navigating fragmented data. Key challenges include:

• Fragmented Visibility: Critical purchasing data is scattered across multiple tables (Headers, Lines, Shipments, Distributions), making it difficult to get a single, unified view of a PO.
• Process Gaps: Tracking a PO from creation to receipt and final invoicing often requires running multiple disparate reports, leading to inefficiencies and data reconciliation errors.
• Spend Management: Without a consolidated view of what was ordered versus what was billed, organizations struggle to analyze spend patterns and enforce contract compliance.
• Accrual Reconciliation: Discrepancies between ordered amounts, received quantities, and invoiced amounts can lead to inaccurate financial reporting and accrual write-offs.

The Solution

This report solves these challenges by creating a unified data model that flattens the complex hierarchy of Oracle Purchasing tables.

• Full Lifecycle Tracking: Links POs directly to receipts and invoices, allowing users to see exactly what was ordered, what has been received, and what has been paid.
• Granular Detail: Provides analysis down to the distribution level, ensuring that project associations, charge accounts, and budget centers are clearly visible.
• Status Monitoring: Includes fields for “Document Closure Status,” “Promised Date,” and “Overdue” flags, enabling proactive management of open orders and supplier performance.
• Flexible Filtering: Users can filter by Buyer, Supplier, Item, Project, and Date ranges to focus on specific areas of interest.

Technical Architecture

The solution leverages a robust SQL architecture to join core Purchasing tables with Inventory, Receiving, and Payables modules.

• Core Tables: PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, PO_DISTRIBUTIONS_ALL.
• Integration Points:
  • Receiving: Joins with RCV_TRANSACTIONS to bring in receipt dates and quantities.
  • Payables: Links to AP_INVOICES_ALL and AP_INVOICE_LINES_ALL to show billed amounts and invoice status.
  • General Ledger: Resolves account code combinations via GL_CODE_COMBINATIONS for accurate financial reporting.
  • Projects: Integrates with PA_PROJECTS_ALL for project-centric procurement tracking.

Parameters & Filtering

The report offers extensive parameters for targeted analysis:

• Organizational Context: Operating Unit, Receiving Organization.
• Document Attributes: PO Number, Release, Document Type, Document Closure Status.
• Parties: Buyer, Supplier, Supplier Site.
• Item Details: Item, Item Type, Category Sets.
• Dates: Document Creation Date, Promised Date, Need By Date, Receipt Date.
• Status Flags: Has Open Quantity, Overdue, Exclude Cancelled.

Performance & Optimization

• Materialized Views: For high-volume environments, consider using materialized views for the heavy joins between PO and AP tables to improve query response times.
• Indexing: Ensure standard Oracle indexes on PO_HEADER_ID, PO_LINE_ID, and CREATION_DATE are active and analyzed.
• Date Ranges: Always encourage users to run the report with specific date ranges (e.g., “Creation Date” or “Need By Date”) to limit the dataset and enhance performance.

FAQ

Q: Does this report include Cancelled POs? A: By default, you can choose to include or exclude cancelled lines using the “Exclude Cancelled” parameter.

Q: Can I see the GL Charge Account for each line? A: Yes, the report includes distribution-level details, showing the full GL Charge Account segments.

Q: How does it handle Blanket Purchase Agreements (BPAs)? A: The report supports Releases against BPAs. You can filter by “Release” number or view the release details associated with the blanket header.

---

Useful Links

• Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
• Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
• Oracle EBS Reporting Toolkits by Blitz Report™
• Blitz Report™ FAQ & Community Q&A
• Supply Chain Hub by Blitz Report™
• Blitz Report™ Customer Case Studies
• Oracle EBS Reporting Blog
• Oracle EBS Reporting Resource Centre

© Enginatics GmbH