AP Invoice on Hold – 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

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

When India Localization is installed, the following GST columns are also included: GST Registration No, Supplier GSTIN (Invoice Date), GST PAN No, GST TAN No, CGST Amount, SGST Amount, IGST Amount, CESS Amount, CGST Rate, SGST Rate, IGST Rate, CESS Rate, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code, Days Since Invoice, Amount Settled, Amount Unpaid, ITC Reversal Due, ITC Reversal Exposure.

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, Hold Name, Trading Partner, Order By, Entered Date From, Entered Date To, Due Date From, Due Date To, Discount Date From, Discount Date To, ITC Reversal Days

Oracle EBS Tables Used

ap_lookup_codes, po_line_locations_all, po_headers_all, po_lines_all, ap_invoice_lines, 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, fnd_currencies_vl, ap_suppliers

Report Categories

Enginatics

Related Reports

AP Invoices and Lines, AP Invoices and Lines 11i, CST AP and PO Accrual Reconciliation, CST Miscellaneous Accrual Reconciliation, PO Procure to Pay Tracker

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 on Hold 30-Sep-2020 221341.xlsx
Blitz Report™ XML Import	AP_Invoice_on_Hold.xml
SQL Source	AP Invoice on Hold.sql
Full Report 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.

---

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

© Enginatics GmbH