OracleFree generatorPDF, Excel, Word exportOracle E-Business Suite

Oracle EBS AP Invoices on Hold SQL Generator

Invoices stuck on hold are the most common reason an AP team misses early payment discounts and due dates. This generator produces SQL against the standard Payables base tables so you can see every unreleased hold and how old it is.

Fill in

Operating unit (ORG_ID)

Use the ORG_ID from HR_OPERATING_UNITS.

Holds placed in the last N days

Hold code to filter (optional)

Matches ap_holds_all.hold_lookup_code. Used in the second query.

Minimum invoice amount

Generated code

Download:

1. Invoices with unreleased holds

SELECT i.invoice_num,
       s.vendor_name,
       i.invoice_date,
       i.invoice_amount,
       i.invoice_currency_code,
       h.hold_lookup_code,
       h.hold_date,
       h.hold_reason,
       TRUNC(SYSDATE) - TRUNC(h.hold_date) AS days_on_hold
  FROM ap_invoices_all i
  JOIN ap_holds_all h
    ON h.invoice_id = i.invoice_id
  LEFT JOIN ap_suppliers s
    ON s.vendor_id = i.vendor_id
 WHERE h.release_lookup_code IS NULL
   AND i.org_id = 204
   AND i.invoice_amount >= 0
   AND h.hold_date >= TRUNC(SYSDATE) - 90
 ORDER BY days_on_hold DESC, i.invoice_amount DESC;

2. Same query for one hold code

SELECT i.invoice_num,
       s.vendor_name,
       i.invoice_amount,
       h.hold_date,
       h.hold_reason
  FROM ap_invoices_all i
  JOIN ap_holds_all h
    ON h.invoice_id = i.invoice_id
  LEFT JOIN ap_suppliers s
    ON s.vendor_id = i.vendor_id
 WHERE h.release_lookup_code IS NULL
   AND h.hold_lookup_code = 'AMOUNT REC'
   AND i.org_id = 204
 ORDER BY h.hold_date;

3. Summary by hold code

SELECT h.hold_lookup_code,
       COUNT(DISTINCT h.invoice_id) AS invoices_on_hold,
       SUM(i.invoice_amount) AS total_amount,
       MIN(h.hold_date) AS oldest_hold
  FROM ap_invoices_all i
  JOIN ap_holds_all h
    ON h.invoice_id = i.invoice_id
 WHERE h.release_lookup_code IS NULL
   AND i.org_id = 204
 GROUP BY h.hold_lookup_code
 ORDER BY invoices_on_hold DESC;

Run on a read-only copy or during low load, from the APPS or a read-only schema with access to the base tables. Verify column names against your release (R12.1 and R12.2 are the targets). An invoice with several holds appears once per hold, and the total_amount in the summary sums invoice amounts per hold code, so an invoice with two different codes is counted under each.

Ask Payables in plain language

Optionally see how holds and discounts can be queried in plain language over your own data.

Optional. The tool and every export work without this. No spam.

How to use it

Enter your operating unit's ORG_ID, how far back to look, a hold code for the focused query, and a minimum invoice amount. Copy the generated SQL into SQL Developer or your preferred client. The first query lists every invoice with an unreleased hold, oldest first. The second narrows to a single hold code, and the third summarizes counts and amounts by hold code so you can see which type of hold is causing the most backlog.

Run it by operating unit, since the _ALL tables hold every org.
Use read-only access and avoid peak batch hours.
Export the result to hand to AP supervisors.

What the tables mean

AP_INVOICES_ALL holds invoice headers with the vendor, amounts and org. AP_HOLDS_ALL has one row per hold placed on an invoice, with the hold lookup code, the date placed and a reason. A hold is active while the release lookup code is null; once released, the release code and date are set. The supplier name is in AP_SUPPLIERS, joined on vendor_id. Hold codes such as quantity or price variances come from matching, while others come from validation, so the fix differs by code.

Cautions and tips

Do not update these tables directly. Release holds through the Invoice Workbench or the supported APIs so that accounting and audit trails stay correct. Use the report to prioritize: oldest holds with the largest amounts, or holds on suppliers that offer early payment discounts. Be aware that matching holds can be cleared automatically by re-running Payables Invoice Validation, so also check whether the validation job is scheduled and running.

Never delete rows from AP_HOLDS_ALL.
Check validation status as well as holds.
Add a flexfield or custom hold reason only through configuration.

Frequently Asked Questions

How do I know a hold is still active?

In AP_HOLDS_ALL an active hold has a null release_lookup_code. When a hold is released the release code, release date and reason are filled in. The query filters on that column.

Why does the same invoice show twice?

An invoice can have several holds at the same time, and the join returns one row per hold. Use the summary query, which counts distinct invoice IDs, if you need counts per invoice.

Does this work on R12.2?

The tables and columns used are standard in R12.1 and R12.2. Verify in your environment, particularly if you use a custom schema or access through multi-org views such as AP_INVOICES instead of the _ALL tables.

Can I release holds with SQL?

No. Releasing holds should be done through the application or supported APIs, because updates to the base tables bypass validation, accounting and audit logic and are not supported.

If you want to ask questions like which holds are costing us discounts this month in plain language, with the SQL shown and read-only access, we can set that up over your EBS database on-prem.

Talk to an Expert Explore More Free Tools

Related Tools

Oracle

More Oracle tools

See all

Back to All Tools