Search Results bom_bill_released_revisions_v




Overview
Explore in AI Chat

Overview

BOM_BILL_RELEASED_REVISIONS_V is a read-only Oracle EBS view owned by the APPS schema within the Bills of Material (BOM) product family. It presents engineering change order (ECO) released item revisions, exposing the revision records for inventory items together with their effectivity and implementation information. Its principal reporting role is to answer the question "which revision is currently effective, and when does it remain effective until?" for items whose revisions have been released through the engineering change order process.

The seventh column, HIGH_DATE, is the calculated end-of-effectivity value that users frequently search for by name. It is not stored on a base table; it is derived at query time from the intersection of the released revision and any subsequent revision rows recorded against the same item and organization. Because HIGH_DATE is generated by aggregation and DECODE logic, it cannot be filtered efficiently with an indexed predicate and should be treated as a presentation value.

Underlying Base Objects

The view is constructed from two documented base objects, exposed to APPS through synonyms:

MTL_ITEM_REVISIONS_B is referenced twice: once as MIR for the primary revision row and once as MIR2 for the following revision of the same item and organization, joined on MIR2.EFFECTIVITY_DATE(+) > MIR.EFFECTIVITY_DATE. ENG_REVISED_ITEMS is likewise outer-joined as ERI and ERI2. The outer joins ensure that released revisions are returned even when no subsequent revision or release record exists.

Key Columns

  • INVENTORY_ITEM_ID / ORGANIZATION_ID — the item and inventory organization identifying the revision.
  • REVISION — the revision label (for example, A, B, or a numeric revision).
  • EFFECTIVITY_DATE — the date on which the revision becomes effective.
  • IMPLEMENTATION_DATE — the date the revision was implemented. A NULL value drives the IMPLEMENTED_FLAG logic.
  • HIGH_DATE — the derived end date of the revision. It is computed as NVL(MIN(...), GREATEST(SYSDATE, MIR.EFFECTIVITY_DATE)). The inner expression returns the day before the next revision's effectivity date when that next revision exists and is released; TO_DATE(NULL) is returned when the next revision has status 1 or 2. When no subsequent revision is found, the fallback is the greater of the current system date and the revision's own effectivity date — meaning an open-ended revision reports a HIGH_DATE that advances with SYSDATE until it is superseded.
  • IMPLEMENTED_FLAG — DECODE(MIR.IMPLEMENTATION_DATE, NULL, 2, 1). A value of 1 means the revision has an implementation date; 2 means it does not.

Common Use Cases and Queries

Typical uses include ECO release reporting, revision effectivity windows for manufacturing and planning, and integration extracts that must supply a valid date range per item revision. The following query returns the active revision for a specific item and organization:

  • SELECT inventory_item_id, organization_id, revision, effectivity_date, implementation_date, high_date, implemented_flag FROM apps.bom_bill_released_revisions_v WHERE inventory_item_id = :item_id AND organization_id = :org_id AND SYSDATE BETWEEN effectivity_date AND high_date;
  • SELECT revision, high_date, implemented_flag FROM apps.bom_bill_released_revisions_v WHERE inventory_item_id = :item_id AND organization_id = :org_id ORDER BY effectivity_date;
  • SELECT inventory_item_id, organization_id, COUNT(*) FROM apps.bom_bill_released_revisions_v WHERE implemented_flag = 2 GROUP BY inventory_item_id, organization_id;

Because HIGH_DATE depends on SYSDATE, result sets are not deterministic across executions and should not be cached or compared between runs without accounting for that dependency.