Search Results bom_bill_no_hold_revisions_v




Overview
Explore in AI Chat

Overview

BOM_BILL_NO_HOLD_REVISIONS_V is a seeded Oracle E-Business Suite view owned by the APPS schema and classified under the Bills of Material (BOM) product family. Its documented purpose is to expose item revisions that are not on hold. In engineering and manufacturing environments, a revision placed on hold (for example, pending an engineering change or a quality investigation) must be excluded from valid configuration and planning decisions. This view provides a filtered, date-aware projection of revision records so that downstream reports, inquiries, and integrations can consume only the revisions that are currently permissible.

The view is built over revision data that spans both engineering and manufacturing sources, reconciling the revised item status held in ENG_REVISED_ITEMS with the revision date ranges recorded in MTL_ITEM_REVISIONS. Because it resolves each revision's active window, it is suited to point-in-time reporting rather than simple list-of-values retrieval. It is most frequently joined to item and organization master data to present revision information by item and inventory organization.

Underlying Base Objects

The view is defined over two underlying objects, both referenced through APPS synonyms:

  • ENG_REVISED_ITEMS — the engineering revisions table, which carries the STATUS_TYPE controlling whether a revision is on hold. STATUS_TYPE = 2 denotes a hold, and such rows are excluded by the view's predicate.
  • MTL_ITEM_REVISIONS — the item revisions table, holding INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION, EFFECTIVITY_DATE, and IMPLEMENTATION_DATE. This is the same table surfaced to users searching on the term mtl_item_revisions.

ENG_REVISED_ITEMS is joined to MTL_ITEM_REVISIONS through REVISED_ITEM_SEQUENCE_ID using an outer join, so revisions without a matching engineering row are still considered. A self-join of MTL_ITEM_REVISIONS (aliased MIR2) and a second reference to ENG_REVISED_ITEMS (ERI2) are used to find the next subsequent revision, allowing the view to compute an end date for each revision's effective period.

Key Columns

  • INVENTORY_ITEM_ID — the item identifier for the revision.
  • ORGANIZATION_ID — the inventory organization in which the revision applies.
  • REVISION — the revision label.
  • EFFECTIVITY_DATE — the date the revision becomes effective.
  • IMPLEMENTATION_DATE — the date the revision was implemented; a NULL value indicates it has not yet been implemented.
  • HIGH_DATE — the computed end of the revision's active window. It is derived from the effectivity date of the next revision (less one second), or defaults to GREATEST(SYSDATE, EFFECTIVITY_DATE) when no later revision exists, yielding an open-ended window.
  • IMPLEMENTED_FLAG — a derived indicator set to 1 when IMPLEMENTATION_DATE is populated and 2 when it is NULL.

Common Use Cases and Queries

Typical scenarios include revision history reports, current-revision lookups for a given date, and integration extracts that must suppress held revisions.

  • List all active, non-held revisions for an item in an organization:
    SELECT inventory_item_id, organization_id, revision, effectivity_date, high_date
    FROM apps.bom_bill_no_hold_revisions_v
    WHERE inventory_item_id = :item_id AND organization_id = :org_id
    ORDER BY effectivity_date;
  • Retrieve the revision effective on a specific date:
    SELECT revision, effectivity_date, high_date
    FROM apps.bom_bill_no_hold_revisions_v
    WHERE inventory_item_id = :item_id AND organization_id = :org_id
    AND :as_of_date BETWEEN effectivity_date AND high_date;
  • Identify unimplemented revisions using IMPLEMENTED_FLAG = 2.

Because the view applies date aggregation and hold filtering internally, callers should treat it as a reporting convenience rather than a substitute for direct revision maintenance against MTL_ITEM_REVISIONS.