Results for “oe_order_holds_all”

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

Overview

OE_ORDER_HOLDS_ALL is the transactional hold repository for Oracle Order Management (ONT) in Oracle E-Business Suite 12.1.1 and 12.2.2. It records every hold placed against an order header or an order line, preserving the association to the hold definition that caused the hold and, where applicable, to the release record that cleared it. The table is the authoritative source for determining why an order cannot progress to pick, ship, or invoice, and it is queried by order entry forms, the Order Management workflow, credit checking, and scheduling processes.

The object resides in the ONT schema and is documented as VALID in ETRM for release 12.2.2. The heuristic Data Vault classification mined from the foreign key structure is link. This is a reasonable modeling suggestion: the table does not describe an independent business entity but resolves the many-to-many relationships between orders/lines and hold sources, and between holds and their releases. It carries descriptive state (notably RELEASED_FLAG) and standard audit columns rather than a full attribute history.

Key Information Stored

The surrogate primary key is ORDER_HOLD_ID, enforced by OE_ORDER_HOLDS_ALL_PK and reinforced by the unique index OE_ORDER_HOLDS_ALL_U1. The table's business significance derives from four foreign keys that identify what is on hold and why:

• ORDER_HOLD_ID — surrogate primary key; unique identifier for each hold instance.
• HEADER_ID — order header on which the hold applies; joins to OE_ORDER_HEADERS_ALL.
• LINE_ID — order line on which the hold applies, where the hold is line-level rather than header-level; joins to OE_ORDER_LINES_ALL.
• HOLD_SOURCE_ID — the hold definition that generated the hold; joins to OE_HOLD_SOURCES_ALL.
• HOLD_RELEASE_ID — the release record used to lift the hold; joins to OE_HOLD_RELEASES.
• RELEASED_FLAG — indicates whether the hold instance has been released.
• ORG_ID — operating unit context, supporting multi-org reporting and security.
• CREDIT_PROFILE_LEVEL — credit-related context for holds raised by credit checking.
• INST_ID — installation identifier used in multi-instance and upgrade scenarios.
• CONTEXT and ATTRIBUTE1–ATTRIBUTE15 — the standard EBS descriptive flexfield columns, available for customer-specific hold attributes.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID — audit and concurrent program tracking columns.

Twelve of the thirty-four documented columns are the flexfield and who-columns above; the operational payload is concentrated in the four foreign keys plus RELEASED_FLAG and ORG_ID.

Common Use Cases and Queries

The most frequent requirement is identifying all unreleased holds blocking an order. A representative query joins the hold table to the header and to the hold source to return a business-readable reason:

• Order blocking report: SELECT h.ORDER_HOLD_ID, h.HEADER_ID, h.LINE_ID, s.NAME, h.RELEASED_FLAG FROM OE_ORDER_HOLDS_ALL h, OE_HOLD_SOURCES_ALL s WHERE h.HOLD_SOURCE_ID = s.HOLD_SOURCE_ID AND h.RELEASED_FLAG = 'N'.
• Hold history for an order: filter on HEADER_ID and order by CREATION_DATE to reconstruct the sequence of holds and releases.
• Release audit: left-join to OE_HOLD_RELEASES on HOLD_RELEASE_ID to distinguish holds still open from those cleared, and to attribute the release action.
• Line-level backlog: filter on LINE_ID IS NOT NULL to report only line holds, which typically affect scheduling and shipping rather than the entire order.
• Operating unit dashboards: group by ORG_ID and HOLD_SOURCE_ID to trend hold volumes and the sources generating the most exceptions.

Because standard Order Management holds are raised and released through workflow and concurrent programs, these queries are almost always read-only; direct DML on the table is not supported and will bypass the logic that synchronizes hold state with the order's workflow status.

Related Objects

The FK metadata identifies the principal related tables, each joined on a documented column:

• OE_HOLD_SOURCES_ALL — hold definitions; joined via OE_ORDER_HOLDS_ALL.HOLD_SOURCE_ID. Supplies hold name and behavior.
• OE_HOLD_RELEASES — release records; joined via OE_ORDER_HOLDS_ALL.HOLD_RELEASE_ID.
• OE_ORDER_HEADERS_ALL — order headers; joined via OE_ORDER_HOLDS_ALL.HEADER_ID.
• OE_ORDER_LINES_ALL — order lines; joined via OE_ORDER_HOLDS_ALL.LINE_ID.
• OE_HOLD_SOURCES and OE_HOLD_RELEASES views, and the public Order Management APIs such as OE_ORDER_PUB, which apply and release holds indirectly rather than writing this table directly.