Search Results mtl_item_revisions




Overview
Explore in AI Chat

Overview

APPS.BOM_BILL_NO_HOLD_REVISIONS_V is a reporting view in Oracle EBS Bills of Material (BOM) that exposes item revision records which are not placed on engineering hold. It is derived primarily from MTL_ITEM_REVISIONS, joined to ENG_REVISED_ITEMS to filter out revisions whose engineering change status indicates a hold (STATUS_TYPE = 2). The view is commonly used in engineering, manufacturing, and planning reporting where only active, non-held revisions should be considered for effectivity, implementation, and revision sequencing.

The view is identified in ETRM metadata as owned by APPS and is available in both 12.1.1 and 12.2.2. Its name — "NO_HOLD_REVISIONS" — directly reflects its filtering logic, which excludes records where the associated engineering revised item has an engineering hold status. This makes it suitable for integration and reporting scenarios requiring clean revision data without engineering change holds.

Underlying Base Objects

The view is defined over two documented base objects (as SYNONYMs in APPS schema):

  • MTL_ITEM_REVISIONS — Stores revision definitions per inventory item and organization, including revision label, effectivity date, and implementation date.
  • ENG_REVISED_ITEMS — Engineering change revised items, providing STATUS_TYPE to identify revisions under engineering hold. STATUS_TYPE = 2 indicates a hold status, and such records are excluded by the view.

The view aliases MTL_ITEM_REVISIONS as MIR/MIR2 and ENG_REVISED_ITEMS as ERI/ERI2, using self-joins to compute the next revision's effectivity window. Outer joins (+) are used on ERI and MIR2 so that items without a matching engineering revised item or subsequent revision are still included, with NVL handling for a status of 0 when no hold is recorded.

Key Columns

The view exposes the following important columns:

  • INVENTORY_ITEM_ID — Item identifier joining to MTL_SYSTEM_ITEMS_B.
  • ORGANIZATION_ID — Organization context for the revision.
  • REVISION — Revision label (e.g., 'A', 'B').
  • EFFECTIVITY_DATE — Date the revision becomes effective.
  • IMPLEMENTATION_DATE — Date the revision was implemented; drives a DECODE producing 1 (implemented) or 2 (not implemented).
  • Derived effectivity end — An NVL/MIN expression computing the end of effectivity as either the next revision's effectivity date minus one second, or GREATEST(SYSDATE, EFFECTIVITY_DATE) if no later revision exists.

Common Use Cases and Queries

Typical usage includes identifying currently effective, non-held revisions for an item or organization, and building revision history reports. A common query pattern:

SELECT inventory_item_id, organization_id, revision, effectivity_date, implementation_date FROM apps.bom_bill_no_hold_revisions_v WHERE inventory_item_id = :item_id AND organization_id = :org_id ORDER BY effectivity_date;

Because the user search referenced mtl_item_revisions, this view is frequently discovered when developers need revision data but must exclude engineering holds — an operation not possible directly from MTL_ITEM_REVISIONS without joining ENG_REVISED_ITEMS and filtering STATUS_TYPE <> 2. The view therefore serves as a convenient, pre-filtered alternative for reports, concurrent programs, and integration extracts that require only non-held revision records.