AP Invoices and Lines – 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

Detail Invoice Aging report with line item details and amounts

Parameter explanation:

Hold column explanation:

When India Localization is installed, the following GST columns are also included: GST Supplier Registration No, GST PAN No, GST TAN No, Supplier GSTIN (Invoice Date), CGST Amount, SGST Amount, IGST Amount, CESS Amount, 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, Custom Amount, Unclassified Tax Amount, Unclassified Tax Rate Codes, Receipt Tax Not On Invoice, Taxable Value, HSN SAC Code.

The Approval Status column and parameter show and filter the status of the invoice in the Invoice Approval Workflow.

On withholding tax distributions, the withholding columns show the withholding tax and rate, the tax authority supplier and site of the tax, the gross amount subject to withholding, the payment that created the withholding when it was calculated at payment, and the invoice raised to the tax authority.

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

Ledger, Operating Unit, Aging Bucket Name, Display Level, Supplier, Supplier Site, Invoice Number, Invoice Date Period, Invoice Date From, Invoice Date To, Validation Date From, Validation Date To, Invoice GL Date From, Invoice GL Date To, Accounting Period, Accounting Date From, Accounting Date To, Distribution Type, Payment Period, Payment Date From, Payment Date To, Open only, Days Overdue, Invoice Type, Invoice Status, Approval Status, Payment Status, Expense Account From, Expense Account To, Exclude Cancelled, Invoice PO Matched, Invoice on Hold, Hold Name, Has Attachment, Creation Date From, Creation Date To, DFF Display, Show Attachments, Show SLA Accounting

Oracle EBS Tables Used

dual, per_all_people_f, xla_transaction_entities, xla_events, fnd_attached_documents, ap_invoice_lines_all, fnd_doc_sequence_categories, gl_daily_conversion_types, ap_invoice_payments_all, ap_checks_all, ce_banks_v, ce_bank_branches_v, mtl_system_items_vl, po_line_locations_all, mtl_parameters, rcv_transactions, fa_book_controls, fa_categories_b_kfv, po_headers_all, po_distributions_all, rcv_shipment_lines, rcv_shipment_headers, ap_tax_codes_all, ap_awt_tax_rates_all, ap_suppliers, ap_supplier_sites_all, ap_invoices_all, gl_ledgers, hr_all_organization_units_vl, ap_batches_all, ap_payment_schedules_all, iby_ext_bank_accounts, hz_parties, hz_party_sites, hz_locations, hr_locations, ap_invoice_distributions_all, gl_code_combinations, ap_recurring_payments_all, ap_terms, pa_projects_all, pa_tasks, gl_code_combinations_kfv, ap_holds_all, ap_lookup_codes, po_lines_all, po_releases_all, fnd_documents_short_text, fnd_lobs, fnd_documents, fnd_documents_tl, fnd_document_datatypes, fnd_document_categories_tl

Report Categories

Enginatics

GL Account Analysis, GL Account Analysis 11g, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Distribution Analysis, GL Account Analysis (Drilldown), PO Headers and Lines, AP Accounted Invoice Aging

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 AP Invoices and Lines 29-Jul-2020 201730.xlsx
Blitz Report™ XML Import AP_Invoices_and_Lines.xml
SQL Source AP Invoices and Lines.sql
Full Report on Enginatics www.enginatics.com/reports/ap-invoices-and-lines/

Case Study & Technical Analysis: AP Invoices and Lines

1. Executive Summary

Business Problem

Managing Accounts Payable requires deep visibility into invoice details to ensure timely payments, accurate accounting, and effective cash flow management. Standard Oracle reports often provide either a high-level summary (Aging) or specific transactional details (Register), but rarely combine both effectively. Finance teams struggle with:

Solution Overview

The AP Invoices and Lines report is a comprehensive operational tool that bridges the gap between high-level aging and detailed transaction analysis. It provides a flattened view of AP data, combining Invoice Headers, Lines, Payment Schedules, and Hold details into a single, pivot-friendly dataset. This allows AP Managers and Accountants to drill down from a supplier’s total outstanding balance to the specific line items and hold reasons causing delays.

Key Benefits

2. Technical Analysis

Core Tables and Views

The report queries the core AP transactional tables:

SQL Logic and Data Flow

The SQL is designed to handle the one-to-many relationships inherent in AP data (One Invoice -> Many Lines -> Many Holds).

Integration Points

3. Functional Capabilities

Reporting Dimensions

Key Parameters

4. Implementation Considerations

Performance

Best Practices

© Enginatics GmbH