Results for “ap_holds_all”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AP_HOLDS_ALL is the foundational Payables table that stores invoice hold information within Oracle E-Business Suite 12.1.1 and 12.2.2. Every time a hold is applied to an invoice — whether through manual entry, a matching validation, a system-generated hold during import, or an automated approval workflow — a corresponding row is created in this table. The table captures the complete lifecycle of a hold, from the moment it is placed through its release, preserving the reason, the responsible user, and the associated audit trail.

The "_ALL" suffix signals that the table resides in the AP schema and is enabled for multi-organization access, evidenced by the presence of the ORG_ID column. Because holds are transactional in nature — frequently inserted, updated, and released — the table functions as a high-volume operational entity rather than a configuration table. From a dimensional modeling perspective, the mined foreign-key structure suggests a link classification: AP_HOLDS_ALL connects an invoice (AP_INVOICES_ALL) to the hold code definitions in AP_HOLD_CODES, and optionally to purchasing and receiving entities. This hub-and-link heuristic reflects its role as an associative intersection capturing event-based relationships between business entities.

Key Information Stored

The table contains 37 documented columns, with the most operationally significant described below. HOLD_ID serves as the unique surrogate identifier for each hold record, while INVOICE_ID links the hold to its parent invoice and LINE_LOCATION_ID optionally ties the hold to a specific purchase order shipment line.

• HOLD_LOOKUP_CODE and RELEASE_LOOKUP_CODE — the coded reason the hold was applied and the reason it was released; both reference AP_HOLD_CODES.
• HOLD_REASON and RELEASE_REASON — free-text explanations supplementing the coded values.
• HELD_BY and HOLD_DATE — the FND_USER who applied the hold and the timestamp of application.
• STATUS_FLAG — indicates whether the hold is currently active or has been released.
• RCV_TRANSACTION_ID — links the hold to a receiving transaction when the hold originates from receipt-based matching.
• LINE_NUMBER and HOLD_DETAILS — identify the affected invoice line and any structured hold detail.
• WF_STATUS — tracks workflow processing status for the hold record.
• ORG_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and RESPONSIBILITY_ID — standard audit and multi-organization columns.
• Fifteen ATTRIBUTE columns (ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15) provide extensibility for custom descriptive flexfield data.

The primary key is HOLD_ID. Business-key candidates include the combination of INVOICE_ID plus HOLD_LOOKUP_CODE plus HOLD_DATE, which uniquely identifies an active hold instance for reporting and de-duplication purposes.

Common Use Cases and Queries

Payables analysts and developers query AP_HOLDS_ALL to identify invoices blocked from payment, to audit hold placement history, and to drive automated release processes. A typical reporting query joins AP_INVOICES_ALL to surface held invoices by vendor and amount:

• Listing all currently active holds: SELECT ha.invoice_id, ha.hold_lookup_code, ha.hold_date, ha.held_by FROM ap_holds_all ha WHERE ha.status_flag = 'A';
• Joining hold codes for meaningful descriptions: SELECT ha.hold_lookup_code, hc.description FROM ap_holds_all ha, ap_hold_codes hc WHERE ha.hold_lookup_code = hc.hold_lookup_code;
• Reporting held invoices by vendor for a specific operating unit using ORG_ID and the join to AP_INVOICES_ALL on INVOICE_ID.
• Auditing releases by filtering on RELEASE_LOOKUP_CODE and RELEASE_REASON to trace who released a hold and why.
• Reconciling receipt-driven holds by joining to RCV_TRANSACTIONS on RCV_TRANSACTION_ID.

The Oracle Payables Invoice Hold API (AP_HOLDS_PKG) is the supported programmatic interface for inserting, applying, and releasing holds rather than direct DML against the table.

Related Objects

AP_HOLDS_ALL sits at the center of several documented foreign-key relationships:

• AP_INVOICES_ALL — joined on INVOICE_ID; the parent invoice record.
• AP_HOLD_CODES — referenced twice, via HOLD_LOOKUP_CODE and RELEASE_LOOKUP_CODE, supplying valid hold and release reason codes.
• PO_LINE_LOCATIONS_ALL — joined on LINE_LOCATION_ID when the hold targets a specific PO shipment line.
• FND_USER — joined on HELD_BY to identify the user who applied the hold.
• RCV_TRANSACTIONS — joined on RCV_TRANSACTION_ID for receiving-originated holds.
• AP_INVOICE_LINES_ALL and the AP_HOLDS_PKG PL/SQL package are commonly referenced alongside these keys for line-level and programmatic handling.