# Oracle EBS AP Invoices on Hold SQL Generator

> Source: https://netray.co/tools/oracle-ebs-ap-invoices-on-hold-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in operating unit, lookback days and optional hold code to get ready-to-run SQL for unreleased holds in Oracle Payables.

## Category

oracle

## Erp

Oracle E-Business Suite

## Audience

- EBS DBA
- AP analyst
- EBS technical consultant
- Oracle developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Operating unit (ORG_ID)

**Key:** org_id

**Type:** number

**Default:** 204

**Help:** Use the ORG_ID from HR_OPERATING_UNITS.

#### Holds placed in the last N days

**Key:** days_back

**Type:** number

**Default:** 90

#### Hold code to filter (optional)

**Key:** hold_code

**Type:** text

**Default:** AMOUNT REC

**Help:** Matches ap_holds_all.hold_lookup_code. Used in the second query.

#### Minimum invoice amount

**Key:** min_amount

**Type:** number

**Default:** 0

**Blocks:**

#### 1. Invoices with unreleased holds

**Body:** 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 = {{org_id}}
   AND i.invoice_amount >= {{min_amount}}
   AND h.hold_date >= TRUNC(SYSDATE) - {{days_back}}
 ORDER BY days_on_hold DESC, i.invoice_amount DESC;

#### 2. Same query for one hold code

**Body:** 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 = '{{hold_code}}'
   AND i.org_id = {{org_id}}
 ORDER BY h.hold_date;

#### 3. Summary by hold code

**Body:** 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 = {{org_id}}
 GROUP BY h.hold_lookup_code
 ORDER BY invoices_on_hold DESC;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use it

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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.

**Bullets:**

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

**Cta:** 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.

**Faq:**

#### How do I know a hold is still active?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

oracle payables invoices on hold sql, ap_holds_all query, oracle ebs ap invoice hold report, ap_invoices_all ap_holds_all join, release lookup code null ap_holds_all, oracle ap invoices on hold query, payables hold reason sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json