Search Results first_value




Overview
Explore in AI Chat

Overview

APPS.BOM_ITEM_CURRENT_REV_VIEW is a reporting and integration view in Oracle E-Business Suite that returns the current, effective revision of an inventory item for a given organization. Its purpose is to isolate the single active engineering revision that applies to an item as of the current system date, rather than exposing the full historical list of revisions that exists in the underlying revision table. The view is defined with a two-stage filter: first, it identifies the most recent EFFECTIVITY_DATE not later than SYSDATE for which a revision has actually been implemented; second, at that effectivity date it identifies the highest REVISION value present. The result is one row per organization and item, keyed by organization, item, revision, effectivity date, revision label, and revision ID.

From an EBS perspective, the view forms part of the Bills of Material (BOM) functional area. It is commonly consumed by custom reports, interfaces, and integrations that need to resolve "what revision is in effect right now" without re-implementing the effectivity and implementation logic against the base revision table.

Underlying Base Objects

The view is defined over a single documented base object, MTL_ITEM_REVISIONS_B (exposed to the APPS schema through a synonym). MTL_ITEM_REVISIONS_B is the base table that stores item revision definitions, including the organization, the item, the revision name, its effectivity date, its implementation date, and the internal revision identifier.

The view does not add any joins to other tables; instead it applies correlated subqueries against the same base table three times (aliased MIR, IR2, and IR3). The outer query aliased MIR supplies the candidate rows. The IR2 subquery derives the latest qualifying effectivity date, and the IR3 subquery derives the highest qualifying revision at that effectivity date. Because the definition relies on these correlated subqueries rather than standard joins, the view is effectively a per-item aggregation that filters the base revision table down to the current revision.

Key Columns

  • ORGANIZATION_ID — The inventory organization that owns the item revision. Revision definitions are organization-specific, so this column participates in the correlation in both subqueries.
  • INVENTORY_ITEM_ID — The internal identifier of the inventory item whose current revision is being reported.
  • REVISION — The revision value (for example, "A", "B", "C1") of the item. The view selects the highest revision present at the current effectivity date.
  • EFFECTIVITY_DATE — The date on which the revision takes effect. The view restricts this to the most recent date that is not later than SYSDATE.
  • REVISION_LABEL — The descriptive label associated with the revision record.
  • REVISION_ID — The internal primary key identifier of the revision row in MTL_ITEM_REVISIONS_B.

Two filters are applied to the underlying data that are not exposed as columns: IMPLEMENTATION_DATE IS NOT NULL ensures only implemented revisions are considered, and IR2.EFFECTIVITY_DATE <= SYSDATE ensures only revisions whose effectivity has been reached are returned.

Common Use Cases and Queries

The view is most often used when a report or interface must display or transfer the revision currently in effect for an item in a specific organization. Typical scenarios include engineering change reporting, work order and BOM validation, and outbound integrations to manufacturing systems.

To retrieve the current revision for a specific item and organization:

  • SELECT organization_id, inventory_item_id, revision, effectivity_date, revision_label, revision_id FROM apps.bom_item_current_rev_view WHERE inventory_item_id = :item_id AND organization_id = :org_id;

To list current revisions for all items in an organization:

  • SELECT inventory_item_id, revision, effectivity_date, revision_label FROM apps.bom_item_current_rev_view WHERE organization_id = :org_id ORDER BY inventory_item_id;

To join the view to an item master query for descriptive output:

  • SELECT msib.segment1 item, msib.description, v.revision, v.effectivity_date FROM apps.mtl_system_items_b msib, apps.bom_item_current_rev_view v WHERE msib.inventory_item_id = v.inventory_item_id AND msib.organization_id = v.organization_id AND msib.organization_id = :org_id;

Because the view compares effectivity to SYSDATE, results change over time and are not suitable for historical or as-of-date reporting; for those requirements the base table MTL_ITEM_REVISIONS_B should be queried directly with an explicit date predicate.