Search Results oe_holds_pk

Overview

The OE_HOLD_DEFINITIONS table is a core master data table within the Oracle E-Business Suite (EBS) Order Management (ONT) module. It serves as the central repository for defining and administering order holds. A hold is a business rule that prevents an order line from progressing through its workflow, such as shipping or invoicing, until specific conditions are met. This table stores the fundamental metadata for each hold type, including its unique identifier, name, and the period during which the hold definition is active and applicable to orders. Its role is critical for enforcing business controls, credit policies, and fulfillment requirements across the order-to-cash cycle.

Key Information Stored

The table's primary columns, as indicated by its key structure, are HOLD_ID and NAME. HOLD_ID is the unique numeric primary key (OE_HOLDS_PK) for each hold definition. NAME is a unique descriptive identifier (OE_HOLDS_UK1) for the hold, such as 'CREDIT_HOLD' or 'PRICE_HOLD'. While the provided metadata explicitly notes the table stores validity period information, typical implementations include columns like START_DATE_ACTIVE and END_DATE_ACTIVE to control the timeframe a hold definition is effective. The table also maintains a relationship with the workflow system via the ITEM_TYPE column, which is a foreign key to WF_ITEM_TYPES, linking the hold to a specific order management workflow process.

Common Use Cases and Queries

This table is primarily referenced for administrative setup, operational reporting, and troubleshooting. Common scenarios include generating a list of all active holds for audit purposes, identifying which holds are applied to orders (by joining to OE_HOLD_SOURCES_ALL), and validating hold setup before order entry. A typical query retrieves active hold definitions:

• SELECT hold_id, name, start_date_active, end_date_active FROM oe_hold_definitions WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);

For diagnostic purposes, one might join to OE_HOLD_SOURCES_ALL to see all order lines currently under a specific hold, or to OE_HOLD_AUTHORIZATIONS to review who is authorized to release it.

Related Objects

OE_HOLD_DEFINITIONS has defined foreign key relationships with several key Order Management tables, forming the backbone of the hold management system. The documented relationships are:

• WF_ITEM_TYPES: Joined via OE_HOLD_DEFINITIONS.ITEM_TYPE. This links the hold to its associated workflow process.
• OE_HOLD_AUTHORIZATIONS: Joined via OE_HOLD_AUTHORIZATIONS.HOLD_ID = OE_HOLD_DEFINITIONS.HOLD_ID. This table stores the list of users or responsibilities authorized to release a specific hold.
• OE_HOLD_SOURCES_ALL: Joined via OE_HOLD_SOURCES_ALL.HOLD_ID = OE_HOLD_DEFINITIONS.HOLD_ID. This is the transactional table that records which holds are actually applied to specific order lines.