Search Results oe_order_holds_all




The OE_ORDER_HOLDS_ALL table in Oracle E-Business Suite (EBS) 12.1.1 or 12.2.2 is a critical repository for managing order holds within the Order Management (OM) module. It stores comprehensive information about holds applied to sales orders, including their types, reasons, statuses, and release conditions. This table plays a pivotal role in ensuring order compliance, fraud prevention, and business rule enforcement before order processing.

Table Structure and Key Columns

The OE_ORDER_HOLDS_ALL table consists of several key columns that define the hold mechanism:
  • HOLD_ID: Primary key identifier for each hold record.
  • HEADER_ID: References OE_ORDER_HEADERS_ALL.HEADER_ID to associate holds with specific orders.
  • HOLD_TYPE_ID: Links to OE_HOLD_DEFINITIONS to identify the hold type (e.g., credit check, pricing approval).
  • HOLD_RELEASE_ID: References OE_HOLD_RELEASES_ALL when holds are released.
  • STATUS: Indicates current hold status (ACTIVE, RELEASED, or PENDING).
  • APPLIED_BY: User ID who applied the hold.
  • CREATION_DATE/LAST_UPDATE_DATE: Audit columns tracking record lifecycle.

Functional Integration

The table integrates with multiple EBS components:
  1. Order Entry: Holds prevent order booking/shipping until resolved.
  2. Workflow: Triggers approval workflows based on hold types.
  3. Inventory: Blocks inventory allocation for held orders.
  4. AR: Interfaces with Accounts Receivable for credit-related holds.

Hold Types and Processing

Common hold types managed through this table include:
  • Credit Check Holds
  • Price Authorization Holds
  • Contract Compliance Holds
  • Fraud Verification Holds
The table supports both system-generated holds (via automated rules) and manual holds applied by users. When conditions are met, holds transition to RELEASED status through either automated processes or manual intervention.

Technical Considerations

Key technical aspects include:
  • Multi-Org Architecture: The _ALL suffix indicates multi-org support via ORG_ID column.
  • Indexing: Critical indexes exist on HEADER_ID, HOLD_TYPE_ID, and STATUS for performance.
  • API Access: Oracle recommends using OM public APIs rather than direct DML.
  • Purge Considerations: Historical hold data should be archived via standard OM purge processes.

Customization Points

Implementation teams often extend functionality by:
  1. Creating custom hold types via OE_HOLD_DEFINITIONS
  2. Developing automated hold release programs
  3. Building custom hold notification workflows

Performance Implications

In high-volume environments, proper maintenance is essential:
  • Monitor growth as each hold creates a separate record
  • Partition strategies may be needed for implementations with millions of orders
  • Concurrent programs like "Order Hold Cleanup" help manage data volume
The OE_ORDER_HOLDS_ALL table embodies Oracle's comprehensive approach to order control, providing auditability while enabling flexible business process enforcement. Its proper configuration and maintenance are crucial for balancing order integrity with operational efficiency in EBS implementations.