Search Results bom_item_current_rev_view




Overview
Explore in AI Chat

Overview

BOM_ITEM_CURRENT_REV_VIEW is a read-only database view owned by the APPS schema in Oracle E-Business Suite, documented in ETRM for releases 12.1.1 and 12.2.2. It belongs to the Bills of Material (BOM) product family and is described as "Item current revisions." Its purpose is to expose, for each organization and inventory item combination, the single revision that is currently effective as of the system date. Rather than requiring callers to derive currency logic themselves against the full revision history, the view returns the effective revision directly, making it convenient for reporting, integrations, and custom code that must resolve "what revision is in effect right now" for an item.

The view is read-only by definition. It carries no validation or write semantics of its own; all constraints, effectivity logic, and implementation state are governed by the underlying revision table. This makes it a stable, low-risk object to query from concurrent programs, BI Publisher reports, OAF extensions, and outbound interfaces, particularly when the requirement is to snapshot current revision state without touching transactional or revision-history tables directly.

Underlying Base Objects

The view is defined over a single documented base object: MTL_ITEM_REVISIONS_B, accessed in the APPS schema (ALIAS MIR in the view SQL). MTL_ITEM_REVISIONS_B stores revision definitions for inventory items by organization, including effectivity date, implementation date, revision label, and revision identifier.

Currency is determined through correlated subqueries and Oracle analytic functions rather than through a join to a separate status table. Specifically, the view selects revisions whose effectivity date equals the latest effectivity date not exceeding SYSDATE, restricted to rows where IMPLEMENTATION_DATE is not null, and whose revision label equals the highest revision label at that effectivity date. The result is a single effective revision row per item-organization pair. Because the view references only the base table and synonyms, it inherits the organization-level and security profile behaviors of the underlying partitioning (organization_id) but does not itself enforce inventory or operating unit security.

Key Columns

  • ORGANIZATION_ID — Inventory organization identifier; part of the item-organization key.
  • INVENTORY_ITEM_ID — Inventory item identifier; combined with organization it identifies the item.
  • CURRENT_REVISION — (documented column name) Revision value for the currently effective revision.
  • EFFECTIVITY_DATE — Effective date of the returned revision.
  • REVISION_LABEL — Descriptive label for the revision.
  • REVISION_ID — Primary/internal key to the underlying revision row.

Note: ETRM column listings sometimes differ slightly in name from the view text aliases; consumers should verify actual column names against the deployed view definition in the target instance.

Common Use Cases and Queries

Typical uses include current-revision reporting, engineering change validation, BOM explosion enrichment, and feeder queries for interfaces that must attach the effective revision to an item transaction. The following pattern returns the current revision for a given item and organization:

  • SELECT organization_id, inventory_item_id, current_revision, effectivity_date, revision_id FROM apps.bom_item_current_rev_view WHERE organization_id = :org_id AND inventory_item_id = :item_id;
  • Joining to MTL_SYSTEM_ITEMS_B to list current revisions across an organization: SELECT msi.segment1, b.current_revision FROM apps.mtl_system_items_b msi, apps.bom_item_current_rev_view b WHERE msi.organization_id = b.organization_id AND msi.inventory_item_id = b.inventory_item_id AND msi.organization_id = :org_id;
  • Bulk interface extraction restricted by effectivity window: SELECT * FROM apps.bom_item_current_rev_view WHERE organization_id = :org_id AND effectivity_date >= :from_date;

Because currency depends on SYSDATE, results change over time as new revisions are implemented; queries intended for point-in-time comparison should therefore be scheduled or materialized accordingly.