Results for “bom_item_revisions_view”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BOM_ITEM_REVISIONS_VIEW is a read-only database view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It resides in the Bills of Material (BOM) product family and presents item revision records together with calculated start and high (end) effectivity dates. The view consolidates revision effectivity information for engineering items into a single, queryable structure, making it suitable for reporting, concurrent program extracts, and integration interfaces that must determine which revision of an item was effective at a given point in time.

Because it is a view rather than a table, no data is stored within it; all values are derived at runtime from the underlying item revision and engineering revised item tables. This makes it a lightweight reporting object that always reflects current transactional data. The view's most distinctive feature is the derivation of a HIGH_DATE, which represents the boundary at which a revision is superseded by a subsequent revision. The window between EFFECTIVITY_DATE and HIGH_DATE defines the active life of each revision.

Underlying Base Objects

The view is defined over two documented base objects, both exposed through synonyms in the APPS schema:

• MTL_ITEM_REVISIONS_B — the base item revision table, which stores revision identifiers, effectivity dates, and implementation dates per organization and inventory item. It appears twice in the view definition (aliased REV1 and REV2) to support self-joining: REV1 supplies the row being reported, while REV2 identifies the next revision whose effectivity immediately follows.
• ENG_REVISED_ITEMS — the engineering revised item table, sourced from Oracle Engineering. It contributes the change notice, revised item identifier, and revised item status associated with each revision.

The self-join between the two MTL_ITEM_REVISIONS_B instances is an outer join on ORGANIZATION_ID and INVENTORY_ITEM_ID, constrained so that REV2.EFFECTIVITY_DATE is greater than REV1.EFFECTIVITY_DATE. The join to ENG_REVISED_ITEMS is likewise an outer join on REVISED_ITEM_SEQUENCE_ID, ensuring that revisions without a matching engineering revised item are still returned.

Key Columns

• ORGANIZATION_ID — the inventory organization to which the item revision belongs.
• INVENTORY_ITEM_ID — the internal identifier of the revised item.
• REVISION — the revision label (for example, A, B, or C).
• EFFECTIVITY_DATE — the date and time the revision becomes effective, formatted as a 25-character string.
• HIGH_DATE — the computed end date of the revision. It is derived from the earliest subsequent revision's effectivity date minus sixty seconds, using the expression (REV2.EFFECTIVITY_DATE - 60/(60*60*24)). Where no subsequent revision exists, the value resolves to the greater of SYSDATE or the current revision's effectivity date.
• IMPLEMENTATION_DATE — the date the revision was implemented, also formatted as a 25-character string.
• CHANGE_NOTICE — the engineering change notice associated with the revision.
• REVISED_ITEM_ID — the identifier of the associated engineering revised item record.
• REVISED_ITEM_STATUS — the status of the revised item, sourced from RI.STATUS_TYPE. This column is frequently searched as "revised_item_status" by users seeking the engineering status of a revision.

Common Use Cases and Queries

Typical uses include revision history reporting, as-of-date revision determination, and engineering change analysis. The HIGH_DATE column makes it straightforward to identify the revision that was active on a specific date.

To list all revisions for an item in a given organization:

SELECT revision, effectivity_date, high_date, implementation_date,
       change_notice, revised_item_status
  FROM apps.bom_item_revisions_view
 WHERE organization_id = :org_id
   AND inventory_item_id = :item_id
 ORDER BY effectivity_date;

To find the revision effective on a particular date:

SELECT revision, effectivity_date, high_date, revised_item_status
  FROM apps.bom_item_revisions_view
 WHERE organization_id = :org_id
   AND inventory_item_id = :item_id
   AND :as_of_date BETWEEN TO_DATE(effectivity_date,'YYYY/MM/DD HH24:MI:SS')
                       AND NVL(TO_DATE(high_date,'YYYY/MM/DD HH24:MI:SS'), :as_of_date);

To filter by status, constrain on the REVISED_ITEM_STATUS column, which exposes the engineering status of each revised item. Because effectivity and high dates are returned as formatted character strings, comparisons in custom queries should convert them with TO_DATE using the 'YYYY/MM/DD HH24:MI:SS' format mask to preserve correct chronological ordering.