Search Results oe_order_holds_v




Overview
Explore in AI Chat

Overview

OE_ORDER_HOLDS_V is a PL/SQL-based reporting view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the ONT (Order Management) product family and is documented under ETRM as a "View for releasing orders from hold." The view presents a denormalized, business-friendly projection of active order holds — specifically those records in OE_ORDER_HOLDS whose RELEASED_FLAG equals 'N' — enriched with the human-readable hold name and the user name of the person who applied the hold. Because the view resolves internal foreign keys (HOLD_SOURCE_ID, CREATED_BY) into descriptive values by calling OE_HOLDS_PVT.HOLD_NAME and OE_HOLDS_PVT.USER_NAME, it is particularly suited to operational reporting, hold-release workflows, and integration extracts where users need to identify holds by name rather than by numeric identifier. Its status is VALID, meaning it compiles cleanly against the EBS 12.1.1 and 12.2.2 data model.

Underlying Base Objects

Per documented view metadata, OE_ORDER_HOLDS_V is defined over the following referenced objects: OE_ORDER_HOLDS, OE_HOLD_SOURCES, OE_HOLD_DEFINITIONS, and FND_USER, all accessed through APPS synonyms. The view text joins OE_ORDER_HOLDS (alias OH) to OE_HOLD_SOURCES (alias HS) on HOLD_SOURCE_ID, restricted by OH.RELEASED_FLAG = 'N'. OE_HOLD_DEFINITIONS and FND_USER are not joined directly in the outer query; instead they are resolved internally by the OE_HOLDS_PVT package routines invoked in the SELECT list. OE_HOLD_SOURCES supplies the HOLD_ID value exposed by the view, while OE_HOLD_DEFINITIONS underlies the hold-name lookup performed by OE_HOLDS_PVT.HOLD_NAME. FND_USER supplies the user-name resolution behind the HELD_BY column. Because it is a view rather than a table, it is read-only; hold release must be performed through the supported Order Management APIs or concurrent programs that operate on OE_ORDER_HOLDS.

Key Columns

The columns exposed by the view fall into several functional groups:

  • ORDER_HOLD_ID — Primary key of the underlying OE_ORDER_HOLDS record.
  • HOLD_NAME — Descriptive hold name derived via OE_HOLDS_PVT.HOLD_NAME(OH.HOLD_SOURCE_ID). This is the column most frequently searched by users, since "hold_name" is the natural business identifier for a hold.
  • HELD_BY — User name of the individual who applied the hold, resolved through OE_HOLDS_PVT.USER_NAME(OH.CREATED_BY).
  • HOLD_ID, HOLD_SOURCE_ID, HOLD_RELEASE_ID — Identifiers linking the hold to its source definition, source record, and any release record.
  • HEADER_ID, LINE_ID — Reference the affected order header and line; LINE_ID is populated for line-level holds.
  • RELEASED_FLAG — Always 'N' in this view, restricting output to currently active holds.
  • ORG_ID — Multi-org operating unit identifier, essential for org-secured queries.
  • Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and REQUEST_ID.
  • CONTEXT and ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield segments for customer-specific hold data.

Common Use Cases and Queries

Typical uses include identifying which orders are currently held by a specific hold name, reporting hold volume by operating unit, and feeding external systems with active hold information. A representative query retrieving active holds by name is:

SELECT order_hold_id, header_id, line_id, hold_name, held_by, creation_date, org_id FROM oe_order_holds_v WHERE hold_name = :p_hold_name AND org_id = :p_org_id;

To count active holds per hold name for an operating unit:

SELECT hold_name, COUNT(*) hold_count FROM oe_order_holds_v WHERE org_id = :p_org_id GROUP BY hold_name ORDER BY hold_count DESC;

Because the view already filters RELEASED_FLAG = 'N' and performs the HOLD_NAME and HELD_BY lookups, it simplifies user-facing reports that would otherwise require joins to OE_HOLD_SOURCES, OE_HOLD_DEFINITIONS, and FND_USER. When hold release is required, the view identifies the qualifying ORDER_HOLD_ID values that should be passed to the supported Order Management release APIs rather than updated directly.