AP Invoice Payments – 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, Accounting Date From, Accounting Date To, Supplier, Supplier Number, Supplier Site, Invoice Number, Check Number, Check State

Oracle EBS Tables Used

iby_payment_profiles, gl_je_batches

Report Categories

Enginatics, R12 only

Related Reports

CE Bank Statement and Reconciliation, CE Cleared Transactions, AR Miscellaneous Receipts, FND Attached Documents, AR Transactions and Lines, IBY Payment Process Request Details, AR Customers and Sites

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	AP Invoice Payments 22-Aug-2020 224831.xlsx
Blitz Report™ XML Import	AP_Invoice_Payments.xml
Full SQL on Enginatics	www.enginatics.com/reports/ap-invoice-payments/

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