AP Invoice Payments 11i – Oracle EBS SQL Report

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

Overview

Supplier invoice payment details. There can be multiple payments per invoice and one document/check can be used to pay different payments and invoices. To allow reconciling payment with invoices and checks, invoice and check level amounts are shown on the last payment record only and are blank for multiple/duplicate records.

Report Parameters

Ledger, Operating Unit, Payment Date From, Payment Date To, GL Date From, GL Date To, Supplier, Supplier Number, Invoice Number, Check Number

Oracle EBS Tables Used

gl_je_batches, gl_sets_of_books, hr_operating_units, ap_invoice_payments_all, ap_checks_all, gl_daily_rates, ap_invoices_all, po_vendors, ap_terms_vl, ap_bank_accounts_all, ap_bank_branches, gl_code_combinations_kfv, ap_payment_schedules_all

Report Categories

Enginatics

Related Reports

AP Invoice Payments, GL Account Distribution Analysis, GL Account Analysis (Distributions), AP Invoices and Lines 11i, GL Account Analysis

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource	Link
Excel Example Output	None
Blitz Report™ XML Import	AP_Invoice_Payments_11i.xml
Full SQL on Enginatics	www.enginatics.com/reports/ap-invoice-payments-11i/

Case Study & Technical Analysis: AP Invoice Payments Report

Executive Summary

The AP Invoice Payments Report provides a detailed operational view of supplier payments within Oracle Payables. It is designed to facilitate the reconciliation of invoices against payments and checks, ensuring financial accuracy and transparency. By listing multiple payments per invoice and handling complex scenarios where a single check pays multiple invoices, this report is an essential tool for Accounts Payable and Treasury departments.

Business Challenge

Managing supplier payments involves several challenges:

• Reconciliation Complexity: Matching individual invoices to consolidated payments (one check paying multiple invoices) can be tedious and error-prone.
• Payment Visibility: Determining the exact status of a payment (e.g., issued, cleared, voided) often requires navigating multiple screens.
• Duplicate Data: Standard reports may duplicate check amounts when listing multiple invoices per check, leading to incorrect totals if not carefully handled.
• Audit Requirements: Auditors frequently request detailed listings of payments to verify authorization and accuracy.

The Solution

The AP Invoice Payments Report addresses these challenges by providing a structured, flat-file view of the payment process.

• Consolidated View: It links invoices to their respective payments, showing the relationship clearly.
• Smart Totals: To prevent double-counting, invoice and check level amounts are displayed only on the last payment record for a given group, leaving duplicate records blank. This feature simplifies pivot table analysis and summation.
• Status Tracking: The report includes the check state, allowing users to filter for cleared, voided, or issued payments.
• Flexible Filtering: Users can search by date ranges, supplier, or specific document numbers to quickly locate transactions.

Technical Architecture (High Level)

The report is built on the core Payables payment tables, ensuring data integrity and performance.

Primary Tables

• AP_INVOICES_ALL: Contains the invoice header information.
• AP_INVOICE_PAYMENTS_ALL: The intersection table linking invoices to payments.
• AP_CHECKS_ALL: Stores the physical payment document (check/EFT) details.
• IBY_PAYMENT_PROFILES: (R12) Provides details on the payment method and profile used.
• AP_SUPPLIERS & AP_SUPPLIER_SITES_ALL: Supplier master data.
• GL_JE_BATCHES: Links payments to General Ledger journal entries for accounting reconciliation.

Logical Relationships

1. Invoice to Payment: AP_INVOICES_ALL joins to AP_INVOICE_PAYMENTS_ALL on INVOICE_ID.
2. Payment to Check: AP_INVOICE_PAYMENTS_ALL joins to AP_CHECKS_ALL on CHECK_ID.
3. Payment Method: AP_CHECKS_ALL links to IBY_PAYMENT_PROFILES to identify the payment instrument.
4. Accounting: The payment accounting events are linked to GL via GL_JE_BATCHES (likely through XLA tables in R12, though the high-level view simplifies this).

Parameters & Filtering

The report supports a wide range of parameters for targeted analysis:

• Ledger & Operating Unit: Essential for multi-org reporting.
• Date Ranges: “Payment Date” and “Accounting Date” filters allow for period-based reporting.
• Supplier Filters: “Supplier”, “Supplier Number”, and “Site” allow for vendor-specific analysis.
• Document Filters: “Invoice Number” and “Check Number” enable quick lookups of specific transactions.
• Check State: Filters by the status of the payment (e.g., Negotiable, Voided, Cleared).

Performance & Optimization

• Smart Aggregation: The logic to show amounts only on the last record is likely handled within the SQL query (using analytic functions like ROW_NUMBER() or LAST_VALUE()), avoiding post-processing overhead.
• Indexed Access: Filtering by CHECK_DATE, CHECK_ID, or VENDOR_ID utilizes standard Oracle indexes for efficient retrieval.
• Direct SQL: Bypasses XML parsing for rapid generation of large datasets.

FAQ

Q: Why are some amount fields blank? A: To avoid double-counting totals when a single check pays multiple invoices, the report logic displays the check amount only on the last record of the group.

Q: Can I see voided payments? A: Yes, use the “Check State” parameter to filter for ‘Voided’ payments.

Q: Does this report show electronic payments? A: Yes, it includes all payment methods (Check, EFT, Wire, etc.) recorded in Oracle Payables.

---

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

© 2026 Enginatics