AP Posted Invoice Register – Oracle EBS SQL Report

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

Overview

Application: Payables Description: Payables Posted Invoice Register

This report provides equivalent functionality to the Oracle standard Payables Posted Invoice Register report.

For scheduling the report to run periodically, use the relative period from/to offset parameters. These are the relative period offsets to the current period, so when the current period changes, the included periods will also automatically be updated when the report is re-run.

Templates

Pivot: Summary by Account, Invoice Currency Equivalent to standard report in Summarize Report = Yes Mode
Pivot: Summary by Account, Batch, Invoice Currency Equivalent to standard report in Summarize=No Order By=Journal Entry Batch
Pivot: Summary by Account, Invoice Currency, Batch Equivalent to standard report in Summarize=No Order By=Entered Currency
Detail Provides detail listing of the posted invoices

Additional data fields are available. Run the report without a template to see full list available fields

Source: Payables Posted Invoice Register Short Name: APXPOINV DB package: XLA_JELINES_RPT_PKG

Also requires custom package XXEN_XLA package to be installed to initialize the hidden parameters removed from the report to simplify scheduling of the report.

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, CESS Amount, Custom Amount, Unclassified Tax Amount, Non-Recoverable GST, Taxable Value, HSN SAC Code, CGST Rate, SGST Rate, IGST Rate, CESS Rate, CGST Recoverable, CGST Non-Recoverable, SGST Recoverable, SGST Non-Recoverable, IGST Recoverable, IGST Non-Recoverable, Reverse Charge Flag, Place of Supply, Ship From State.

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, Ledger/Ledger Set, Period From, Period To, Journal Source, Include Zero Amount Lines, Account Flexfield From, Account Flexfield To, GL Batch Name, Include Manual Entries from GL, Relative Period From, Relative Period To

Oracle EBS Tables Used

ap_holds_all, xla_ae_headers

Report Categories

Enginatics, R12 only

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	Example
Blitz Report™ XML Import	AP_Posted_Invoice_Register.xml
SQL Source	AP Posted Invoice Register.sql
Full Report on Enginatics	www.enginatics.com/reports/ap-posted-invoice-register/

Case Study & Technical Analysis: AP Posted Invoice Register

1. Executive Summary

Business Problem

The “Posting” process (transferring data from Payables to the General Ledger) is the final step in the AP cycle. Finance teams need to verify that all approved invoices have been successfully transferred to the GL to ensure month-end completeness. Standard Oracle reports for this are often rigid, making it hard to reconcile specific GL batches back to the individual invoices that comprise them.

Solution Overview

The AP Posted Invoice Register is a flexible, high-performance alternative to the standard APXPOINV report. It lists all AP invoices that have been posted to the General Ledger within a specific period. Crucially, it includes the GL Batch Name and Header ID, allowing users to seamlessly cross-reference a line in a GL Journal back to the specific AP Invoice Number and Supplier.

Key Benefits

Reconciliation: The “Rosetta Stone” between AP and GL. If a GL account balance looks wrong, this report shows exactly which invoices fed into it.
Period Close: Verifies that all eligible invoices for the period have been posted (Status = ‘P’).
Drill-Down: Provides the granular detail (Invoice Line, Distribution Line) behind summarized GL journal entries.

2. Technical Analysis

Core Tables and Views

This report is heavily anchored in the Subledger Accounting (SLA) schema:

XLA_AE_HEADERS / XLA_AE_LINES: The source of truth for posted accounting. These tables store the debits and credits exactly as they were sent to GL.
AP_INVOICES_ALL: Provides the operational context (Supplier, Invoice Num).
GL_JE_BATCHES / GL_JE_HEADERS: The destination in the General Ledger.
XLA_TRANSACTION_ENTITIES: The bridge table linking the generic SLA engine to the specific AP application.

SQL Logic and Data Flow

The query starts from the “Accounted” perspective rather than the “Transactional” perspective.

Driver: XLA_AE_HEADERS where GL_TRANSFER_STATUS_CODE = 'Y' (Posted).
Join to AP: Uses SOURCE_ID_INT_1 in XLA_TRANSACTION_ENTITIES to join back to AP_INVOICES_ALL.INVOICE_ID.
Filtering: Filters by Ledger, Period, and optionally Account Flexfield to isolate specific accounts (e.g., Liability or Expense).
Relative Periods: Supports “Relative Period” parameters (e.g., “Current Period - 1”), making it ideal for automated scheduling.

Integration Points

General Ledger: This report is essentially a “Subledger Drilldown” report, mirroring the data found in GL Drilldown pages but in a flat, exportable format.

3. Functional Capabilities

Parameters & Filtering

Period From/To: Standard date range selection.
GL Batch Name: Allows users to investigate a specific suspicious batch found in the GL.
Account Flexfield: Filter for a specific natural account (e.g., “Show me all postings to the ‘Office Supplies’ account”).
Include Zero Amount Lines: Toggle to show/hide net-zero adjustments.

Performance & Optimization

Indexed Access: Querying XLA_AE_HEADERS by PERIOD_NAME and LEDGER_ID leverages the primary partitioning keys of the SLA tables, ensuring rapid retrieval even in high-volume environments.

4. FAQ

Q: Why does this report show different amounts than the “Invoice Register”? A: The Invoice Register shows entered amounts. This report shows accounted amounts. Differences can arise due to exchange rate variances, tax calculations, or SLA rules that modify the accounting derivation.

Q: Can I see unposted invoices here? A: No, this report is specifically for Posted items. Use the “Unaccounted Transactions” report for items stuck in AP.

Q: What is the “Pivot” template? A: The Pivot template summarizes the data by Account and Currency, mimicking the “Summarize Report = Yes” option in the standard Oracle report, but with the ability to drill down in Excel.

AP Posted Invoice Register | Oracle EBS SQL Report

Application: Payables Description: Payables Posted Invoice Register This report provides equivalent functionality to the Oracle standard Payables Posted…