AP Invoice Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Creates Payables invoices through Payables Open Interface Import. Rows with the same Operating Unit, Supplier, Supplier Site and Invoice Number form one invoice, and an error on any of its rows keeps the whole invoice out of the import.

To split a line across several distributions, enter it on several rows with the same Line Number and line details, each with its own Distribution Line Number and Distribution Account, and either a Distribution Percent on every row, adding up to 100, or a Distribution Amount on every row, adding up to the Line Amount. A rounding difference goes to the largest distribution, as with distribution sets. The distributions are created after the import and before invoice validation, as the Invoices window enters them.

A line matched to a purchase order or receipt, using a Distribution Set or prorated cannot be split, and a project line only while profile PA: Allow Override of PA Distributions in AP/PO is Yes.

The result shows one row per distribution, including the distributions Payables creates for a line from a Distribution Set or a purchase order.

To add lines to an existing invoice, use the template Add Lines to Existing Invoice, which sets Add to Existing Invoice to Yes on each row; Yes is accepted only when the invoice exists. The lines are added as in the Invoices window, and Invoice Amount on these rows is the amount added to the invoice, including the tax of the added lines, the same on every row of the invoice; the result shows the invoice’s new Invoice Amount. The other invoice columns identify the invoice and are not changed. The invoice must be unpaid, and its Invoice Amount changeable in the Invoices window. Item, Freight and Miscellaneous lines can be added, without a purchase order or receipt match, proration or India Localization taxes, and a Line Number already on the invoice is rejected.

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, Invoice Source, Batch Name, GL Date, Submit Invoice Validation, Default Line Amount?

Oracle EBS Tables Used

dual, fnd_doc_sequence_categories, ap_invoices_all, po_headers_all, gl_daily_conversion_types, ap_terms, gl_code_combinations_kfv, iby_payment_methods_vl, iby_delivery_channels_vl, iby_payment_reasons_vl, fnd_lookup_values_vl, ap_awt_groups, fnd_shorthand_flex_aliases, ap_distribution_sets_all, hr_employees_current_v, po_releases_all, po_lines_all, po_line_locations_all, po_distributions_all, rcv_shipment_headers, rcv_shipment_lines, mtl_system_items_vl, fa_book_controls, fa_categories_b_kfv, pa_projects_all, pa_tasks, hr_all_organization_units_vl, fnd_territories_vl, zx_input_classifications_v, zx_fc_intended_use_v, hr_locations_all_tl, zx_fc_product_fiscal_v, zx_fc_user_defined_v, zx_fc_business_categories_v, zx_product_types_v, zx_fc_product_categories_v, ap_payment_schedules_all, ap_invoice_lines_all, ap_invoice_distributions_all, financials_system_parameters, ap_suppliers, ap_supplier_sites_all, ap_lookup_codes

Report Categories

Enginatics, Upload

AP Invoices and Lines, IBY Payment Process Request Details, AP Supplier Upload, AP Accounted Invoice Aging, AP Invoice Payments, AP Invoice Payments 11g, AR Customer Upload, GL Account Analysis

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 Invoice Upload - Default 23-Mar-2026 103610 (1).xlsm
Blitz Report™ XML Import AP_Invoice_Upload.xml
SQL Source AP Invoice Upload.sql
Full Report on Enginatics www.enginatics.com/reports/ap-invoice-upload/

Case Study & Technical Analysis: AP Invoice Upload

1. Executive Summary

Business Problem

Manual entry of Accounts Payable (AP) invoices in Oracle E-Business Suite is a time-consuming and error-prone process, especially for organizations with high transaction volumes. Accounts Payable departments often receive invoices in various formats (Excel, PDF, EDI) that require manual keying into the Oracle Invoice Workbench. This manual process leads to:

Solution Overview

The AP Invoice Upload report provides a robust, Excel-based interface for mass creating and importing AP invoices directly into Oracle EBS. Leveraging the Blitz Report™ upload framework, this tool allows users to prepare invoice data in a familiar Excel environment, validate it against Oracle master data (Suppliers, POs, GL Accounts), and upload it to the Oracle AP Interface tables (AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE). The solution supports the standard Oracle Payables Open Interface Import program to validate and create the final invoices.

Key Benefits

2. Technical Analysis

Core Tables and Views

The solution interacts with the following key Oracle EBS tables and views:

SQL Logic and Data Flow

The SQL query provided serves two main purposes:

  1. Data Extraction (Download): It retrieves existing invoice data to serve as a template or for mass updates. It joins header and line information to present a flattened view suitable for Excel.
  2. Interface Mapping: The column aliases in the SQL correspond to the columns in the interface tables, ensuring seamless mapping during the upload process.

Key logic includes:

Integration Points

3. Functional Capabilities

Supported Operations

User Parameters

The report typically accepts parameters to filter the download or control the upload context:

4. Implementation Considerations

Prerequisites

Best Practices

© Enginatics GmbH