AP Invoice on Hold – Oracle EBS SQL Report

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

Overview

Based on Oracle standard Invoice on Hold report Source: Invoice on Hold Report (XML) Short Name: APXINROH_XML

Report Parameters

Operating Unit, Hold Name, Trading Partner, Order By, Entered Date From, Entered Date To, Due Date From, Due Date To, Discount Date From, Discount Date To

Oracle EBS Tables Used

po_headers_all, ap_invoice_lines, po_line_locations_all, po_lines_all, ap_invoice_distributions, gl_sets_of_books, ap_holds, gl_code_combinations, ap_invoice_payments_all, ap_checks_all, hr_all_organization_units, hz_parties, ap_supplier_sites, ap_invoices, ap_batches_all, ap_payment_schedules, ap_hold_codes, ap_lookup_codes, fnd_currencies_vl, ap_suppliers

Report Categories

Enginatics, GST Reports, R12 only

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 on Hold 30-Sep-2020 221341.xlsx
Blitz Report™ XML Import	AP_Invoice_on_Hold.xml
Full SQL on Enginatics	www.enginatics.com/reports/ap-invoice-on-hold/

Case Study & Technical Analysis: AP Invoice on Hold

Executive Summary

The AP Invoice on Hold report provides a comprehensive and real-time view of all supplier invoices currently placed on hold within the Oracle Payables module. This report is a critical tool for Accounts Payable (AP) Managers and Financial Controllers to identify process bottlenecks, prevent payment delays, and maintain healthy supplier relationships. By aggregating hold data into a single operational view, it enables rapid resolution of disputes and processing errors.

Business Challenge

In many organizations, invoices get stuck in the payment process due to various reasons such as price variances, quantity discrepancies, or missing documentation.

Lack of Visibility: Standard Oracle forms require checking invoices one by one or running static PDF reports that are hard to analyze.
Operational Inefficiency: AP clerks often spend hours manually compiling lists of problem invoices to distribute to purchasing or receiving departments for resolution.
Financial Risk: Unresolved holds lead to late payment penalties, lost early payment discounts, and potential credit holds placed by suppliers, disrupting the supply chain.

The Solution

This report solves these challenges by providing a direct Operational View of all active holds.

Proactive Management: Users can instantly see which invoices are stopped and why (e.g., “Qty Rec vs Inv”, “Max Qty Rec”).
Targeted Resolution: The report allows filtering by “Hold Name” or “Trading Partner,” enabling teams to tackle high-priority issues or specific systemic problems first.
Enhanced Detail: Unlike standard summaries, this report links the hold directly to invoice details, allowing for immediate investigation without navigating through multiple Oracle screens.

Technical Architecture (High Level)

The report extracts data primarily from the Oracle Payables (AP) module, linking invoice headers to their specific hold details.

Primary Tables:
- AP_INVOICES_ALL: Contains the invoice header information (Invoice Number, Date, Amount).
- AP_HOLDS_ALL: Stores the specific hold details linked to the invoice.
- AP_SUPPLIERS / AP_SUPPLIER_SITES_ALL: Provides vendor master data.
- PO_HEADERS_ALL: (Optional join) Links to the Purchase Order if the invoice is matched.
Logical Relationships:
- The core join is between AP_INVOICES_ALL and AP_HOLDS_ALL on INVOICE_ID.
- Supplier information is retrieved by joining VENDOR_ID and VENDOR_SITE_ID.
- Lookup codes (AP_LOOKUP_CODES) are used to translate system codes into user-friendly descriptions.

Parameters & Filtering

The report includes robust parameters to help users slice the data effectively:

Operating Unit: Segregates data by business entity for multi-org environments.
Hold Name: Allows users to filter for specific types of holds (e.g., filter only for “Price Variance” to route to the Purchasing team).
Trading Partner: Enables viewing all holds for a specific critical supplier.
Date Ranges (Entered, Due, Discount): Critical for prioritizing invoices that are approaching their due date or discount expiration.

Performance & Optimization

Direct Extraction: The report uses direct SQL extraction, bypassing the overhead of XML parsing often found in standard Oracle BI Publisher reports.
Indexing: The query is optimized to utilize standard Oracle indexes on INVOICE_DATE, VENDOR_ID, and ORG_ID, ensuring fast execution even with large volumes of historical data.
Selective Joins: Joins to heavy tables like PO_HEADERS_ALL are typically handled efficiently to avoid Cartesian products or performance drags.

FAQ

Q: Does this report show holds that have already been released? A: Typically, “Invoice on Hold” reports focus on active holds that require attention. However, depending on the specific parameter selection (e.g., if a “Release Date” filter is available or ignored), it may show historical holds. The primary business use case is for active issues.

Q: Can I see who placed the hold? A: Yes, the underlying AP_HOLDS_ALL table contains HELD_BY information (User ID). This report can be configured to display the username of the person or system process that applied the hold.

Q: Why do I see multiple lines for the same invoice? A: An invoice can have multiple holds applied to it simultaneously (e.g., a Price Hold and a Qty Hold). This report will typically list each hold as a separate line item to ensure all reasons are visible and addressed.

AP Invoice on Hold | Oracle EBS SQL Report

Based on Oracle standard Invoice on Hold report Source: Invoice on Hold Report (XML) Short Name: APXINROHXML