Search Results oe_order_holds_all

Overview

The OE_ORDER_HOLDS_ALL table is a central repository within the Oracle E-Business Suite (EBS) Order Management (ONT) module, versions 12.1.1 and 12.2.2. It functions as the definitive transactional ledger for all active and historical hold placements on sales orders and their constituent lines. Its primary role is to maintain the relationship between an order or line, the specific business rule or condition that triggered the hold (the hold source), and the subsequent release action that removed it. This table is critical for enforcing order validation, managing order fulfillment workflows, and providing a complete audit trail of all hold-related activities, enabling operational reporting and process analysis.

Key Information Stored

The table's structure is designed to link the hold instance to its associated entities. The primary key, ORDER_HOLD_ID, uniquely identifies each hold record. The fundamental links are established through foreign key columns: HEADER_ID references OE_ORDER_HEADERS_ALL for order-level holds, and LINE_ID references OE_ORDER_LINES_ALL for line-level holds. The business logic for the hold is defined by HOLD_SOURCE_ID, which points to OE_HOLD_SOURCES_ALL, identifying the specific hold definition (e.g., credit check, price approval). The resolution of a hold is recorded via HOLD_RELEASE_ID, linking to OE_HOLD_RELEASES_ALL, which stores details about who released the hold and when. Additional columns typically track creation and last update dates, the applying and releasing user IDs, and the current status of the hold instance.

Common Use Cases and Queries

This table is essential for operational reporting and troubleshooting. Common use cases include generating a report of all currently applied holds to prioritize release actions, analyzing hold patterns by source to identify process bottlenecks, and auditing the hold history for a specific order. A fundamental query retrieves active holds for an order:

• SELECT oh.header_id, oh.line_id, hs.name hold_source FROM oe_order_holds_all oh, oe_hold_sources_all hs WHERE oh.hold_source_id = hs.hold_source_id AND oh.header_id = :p_header_id AND oh.released_flag = 'N';

Another critical pattern is analyzing hold duration and release efficiency by joining the holds table to order headers and hold releases, calculating the time difference between the hold application and release dates.

Related Objects

OE_ORDER_HOLDS_ALL is a nexus table with key dependencies, as confirmed by the provided metadata. Its primary relationships are:

• OE_HOLD_SOURCES_ALL: Provides the definition and name of the business rule causing the hold via HOLD_SOURCE_ID.
• OE_HOLD_RELEASES_ALL: Stores the release transaction details linked via HOLD_RELEASE_ID.
• OE_ORDER_HEADERS_ALL: The parent order for order-level holds, linked by HEADER_ID.
• OE_ORDER_LINES_ALL: The specific order line for line-level holds, linked by LINE_ID.

This table is also commonly referenced in custom reports, inventory reservation interfaces, and shipping execution workflows to check for blocking holds before proceeding with fulfillment.