Search Results oe_hold_sources




Overview
Explore in AI Chat

Overview

APPS.OE_ORDER_HOLDS_V is a reporting view in Oracle E-Business Suite that presents the currently active order holds applied within Oracle Order Management. The view is a filtered join across the order hold transaction table, the hold source assignments, the hold definitions, and the application user table. Its defining characteristic is the predicate oh.released_flag = 'N', which restricts the result set to holds that have not yet been released. Consequently, the view is an "open holds" view rather than a historical ledger of hold activity; released holds remain in OE_ORDER_HOLDS but are excluded from this view.

This makes the view well suited to operational reporting and integration: dashboards that surface orders blocked from further processing, exceptions feeds that alert planners to held lines, and interface programs that must determine whether a given header or line is eligible to progress. Because the view is owned by APPS and defined over public synonyms, it can be queried from custom concurrent programs, BI Publisher data models, and external integration layers that connect as a reporting user.

Underlying Base Objects

The documented ETRM metadata lists four referenced base objects, all exposed as synonyms: OE_ORDER_HOLDS, OE_HOLD_SOURCES, OE_HOLD_DEFINITIONS, and FND_USER. The join topology is as follows:

  • OE_ORDER_HOLDS OH is the driving table. It stores one row per hold instance, keyed by ORDER_HOLD_ID, and carries the header/line context, the released flag, the descriptive flexfield columns, and the ORG_ID operating unit identifier.
  • OE_HOLD_SOURCES HS is joined on OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID. It identifies the specific source (for example, a user, a system validation, or an external feed) that applied the hold.
  • OE_HOLD_DEFINITIONS HD is joined on HS.HOLD_ID = HD.HOLD_ID. It supplies the hold's business name, its ITEM_TYPE classification, and the workflow release behavior flag.
  • FND_USER FU is joined on OH.CREATED_BY = FU.USER_ID, resolving the numeric creator identifier into a displayable USER_NAME.

The join traverses the hold source to reach the hold definition, meaning the view exposes the definition attributes of whichever hold is currently sourced through the recorded source. Because the released-flag filter is applied to the driving table, the joins only need to resolve currently active rows.

Key Columns

  • ORDER_HOLD_ID, HOLD_SOURCE_ID, HOLD_RELEASE_ID — the hold instance, its source, and the release record if one exists.
  • HEADER_ID, LINE_ID — the sales order header and, where applicable, the specific line against which the hold is placed.
  • NAME and ITEM_TYPE — the hold's business name and its scope indicator, drawn from OE_HOLD_DEFINITIONS.
  • PROGRESS_WF_ON_RELEASE_FLAG — indicates whether releasing the hold should advance the order's workflow.
  • RELEASED_FLAG — always 'N' in this view by definition; useful as a defensive predicate in downstream queries.
  • USER_NAME — the FND user who created the hold.
  • ORG_ID — the operating unit, enabling multi-org filtered reporting.
  • CONTEXT and ATTRIBUTE1–ATTRIBUTE15 — the descriptive flexfield segment values captured with the hold.
  • Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN), concurrent request columns (PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, REQUEST_ID), and CONTEXT-adjacent DFF columns are inherited from the driving table.

Common Use Cases and Queries

A typical operational query lists all open holds for a given order, including who applied them and under what definition:

  • SELECT header_id, line_id, name, item_type, user_name, creation_date FROM oe_order_holds_v WHERE header_id = :p_header_id;
  • Multi-org filtered extraction: SELECT * FROM oe_order_holds_v WHERE org_id = :p_org_id AND name = 'Credit Check';
  • Hold aging analysis: SELECT name, TRUNC(SYSDATE) - TRUNC(creation_date) age_days, COUNT(*) FROM oe_order_holds_v GROUP BY name, TRUNC(SYSDATE) - TRUNC(creation_date) ORDER BY 1, 2;
  • Integration gate check: SELECT COUNT(*) FROM oe_order_holds_v WHERE header_id = :p_header_id; — a non-zero result signals that the order is currently blocked and should not be passed downstream.

Because the view already filters to unreleased holds and resolves both the user name and the hold definition name, it removes the need for custom joins that would otherwise be required against OE_HOLD_SOURCES, OE_HOLD_DEFINITIONS, and FND_USER. Users searching for oe_hold_sources typically arrive here because the source table alone does not carry the created-by name or the release filter; OE_ORDER_HOLDS_V supplies both in a single queryable object.