PA Invoice Review – Oracle EBS SQL Report

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

Overview

Project draft invoices with line details showing invoice amounts, approval and release status, customer, GL date, and expenditure type breakdown.

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, Project, Invoice Date From, Invoice Date To, Released

Oracle EBS Tables Used

pa_draft_invoice_items, pa_tasks, pa_draft_invoices_all, pa_projects_all, hz_cust_accounts, hz_parties, hr_all_organization_units_vl, mo_glob_org_access_tmp, dual

Report Categories

Enginatics

ONT Orders and Lines 11i, ONT Orders and Lines, PA Project Revenue and Cost by Period, PA Project Upload, GL Account Analysis, PA Project Billing Status, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Distribution Analysis, QP Customer Pricing Engine Request

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 PA Invoice Review 23-Mar-2026 194132.xlsx
Blitz Report™ XML Import PA_Invoice_Review.xml
SQL Source PA Invoice Review.sql
Full Report on Enginatics www.enginatics.com/reports/pa-invoice-review/

Case Study & Technical Analysis: PA Invoice Review

Executive Summary

The PA Invoice Review report lists Oracle Projects draft invoices with their line details. For each draft invoice it shows the invoice amount, the approval, release and transfer status, the bill-to customer, the GL date and the project period, and for each line the amount, description, task and event. Project billing and revenue teams use it to review draft invoices before they are released to receivables, to check the approval state of each invoice, and to confirm which lines make up a billing cycle. The report runs in Excel via Blitz Report.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. PA_DRAFT_INVOICES_ALL is joined to PA_DRAFT_INVOICE_ITEMS on project and draft invoice number.
  2. PA_DRAFT_INVOICES_ALL is joined to PA_PROJECTS_ALL on project identifier, and the project’s organization is joined to HR_ALL_ORGANIZATION_UNITS_VL.
  3. PA_DRAFT_INVOICES_ALL is outer joined to HZ_CUST_ACCOUNTS on the bill-to customer identifier, and to HZ_PARTIES on party identifier.
  4. Each invoice line is outer joined to PA_TASKS on task identifier to return the task number.
  5. The invoice amount is a scalar subquery that sums the line amounts of the same project and invoice.

Parameters & Filtering

The SQL text does not reference these parameters through bind variables. The rows are determined by the operating unit access list and the joins described above.

Performance & Optimization

FAQ

Q: How do I see whether a draft invoice has been released to receivables? A: The Released column shows Yes when a released date exists, with the released date beside it.

Q: Why does the invoice amount repeat on every line? A: The invoice amount is the total of all lines for the same project and draft invoice number, so it is repeated on each line of that invoice.

Q: Which customer is shown for a draft invoice? A: It is the bill-to customer on the draft invoice header, shown by its party name and account number.

© Enginatics GmbH