Results for “oe_hold_definitions”

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

Overview

The OE_HOLD_DEFINITIONS table, owned by the ONT schema, is a core Order Management setup table in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the definition of every order hold that can be applied within Oracle Order Management, including the hold name, its type, the workflow item type and activity that governs its behavior, and the validity window during which the hold may be placed. Because holds are used to block sales orders, lines and returns from progressing through the order-to-cash flow (for example credit, fraud, incomplete pricing or inventory checks), this table acts as the master repository of hold metadata referenced at runtime by the Order Management engine.

The user-searched key oe_holds_pk is the documented primary key constraint of this table, defined on the HOLD_ID column. A second unique constraint, OE_HOLDS_UK1, is defined on NAME, ensuring every hold name is unique. In 12.2.2 the editioning architecture adds the surrogate and business-key columns to composite unique indexes (OE_HOLDS_U1 on HOLD_ID, ZD_EDITION_NAME and OE_HOLDS_U2 on NAME, ZD_EDITION_NAME), supporting online patching and edition-based redefinition. From a Data Vault modeling perspective, the heuristic classification of this object is hub-leaning: the table behaves primarily as a hub of hold identities, with descriptive attributes (type, validity dates, workflow metadata) resembling satellite detail attached to that hub.

Key Information Stored

Although the documented physical schema contains 33 columns, the most significant attributes are:

• HOLD_ID — the surrogate primary key (OE_HOLDS_PK) that uniquely identifies each hold definition and is propagated to dependent tables.
• NAME — the business-key hold name, enforced unique by OE_HOLDS_UK1 and OE_HOLDS_U2.
• TYPE_CODE — classifies the hold type and drives which Order Management logic applies the hold.
• DESCRIPTION — free-text explanation of the hold's purpose, displayed to order-entry users.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the validity period during which the hold may be applied.
• ITEM_TYPE — foreign key to WF_ITEM_TYPES, binding the hold to an Oracle Workflow item type used when the hold triggers workflow processing.
• ACTIVITY_NAME — the workflow activity invoked when the hold is applied or released.
• HOLD_INCLUDED_ITEMS_FLAG — indicates whether included (child) items are also placed on hold.
• APPLY_TO_ORDER_AND_LINE_FLAG — controls whether the hold applies at the order header, line, or both levels.
• PROGRESS_WF_ON_RELEASE_FLAG — determines whether workflow continues when the hold is released.
• ZD_EDITION_NAME — editioning column supporting 12.2 online patching.
• Standard audit columns such as CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY and LAST_UPDATE_LOGIN, plus the CONTEXT and ATTRIBUTE1–15 descriptive flexfield columns.

Common Use Cases and Queries

Typical scenarios include identifying active hold definitions, auditing hold setup, and diagnosing why an order was blocked. A common query retrieves the hold metadata by surrogate key or business name:

• SELECT hold_id, name, type_code, start_date_active, end_date_active FROM oe_hold_definitions WHERE hold_id = :p_hold_id;
• SELECT hold_id, name FROM oe_hold_definitions WHERE name = 'CREDIT CHECK';
• Filtering currently valid holds: WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE+1);
• Joining to workflow item types to review the item type/activity bound to each hold: ... JOIN wf_item_types w ON d.item_type = w.name;
• Reporting on where holds originate by joining OE_HOLD_SOURCES_ALL on HOLD_ID.
• Auditing which holds are authorized for which users/vendors via OE_HOLD_AUTHORIZATIONS.

Related Objects

• OE_HOLD_AUTHORIZATIONS — references OE_HOLD_DEFINITIONS.HOLD_ID; defines who is authorized to apply or release each hold.
• OE_HOLD_SOURCES_ALL — references OE_HOLD_DEFINITIONS.HOLD_ID; records the source configurations from which holds can be applied.
• WF_ITEM_TYPES — referenced by OE_HOLD_DEFINITIONS.ITEM_TYPE; links holds to Oracle Workflow definitions.
• OE_ORDER_HOLDS_ALL and OE_ORDER_HOLDS — store actual hold instances placed on orders and lines, resolving back to hold definitions.
• Order Management hold application and release APIs, which validate against this master table before applying a hold.