Search Results bom_rtg_current_rev_view

Overview

BOM_RTG_CURRENT_REV_VIEW is a read-only database view owned by the APPS schema in Oracle E-Business Suite. It belongs to the Bills of Material (BOM) product family and is documented with the description "Routing current revisions." Its purpose is to expose, for each organization and inventory item combination, the currently effective routing (process) revision as of the system date. Rather than requiring report authors and integrators to construct correlated subqueries against the routing revision history, the view encapsulates that logic and returns a single, de-duplicated row per organization/item pairing.

Because routing revisions in Oracle Manufacturing are date-effectivity driven, determining the "current" revision at any given moment requires comparing effectivity dates and, where multiple revisions share the same date, comparing revision identifiers. The view resolves both conditions internally, making it a convenient reporting and integration access point into manufacturing routing master data.

Underlying Base Objects

The view is defined over a single referenced base object: MTL_RTG_ITEM_REVISIONS. In the documented 12.2.2 metadata this object is listed as a SYNONYM, resolving to the underlying routing item revision table in the APPS schema. MTL_RTG_ITEM_REVISIONS stores the historical and future-dated routing revision records for each inventory item within an organization, keyed by ORGANIZATION_ID and INVENTORY_ITEM_ID, with EFFECTIVITY_DATE and PROCESS_REVISION carrying the versioning information.

The view text applies two correlated subqueries against that same table. The first restricts EFFECTIVITY_DATE to the maximum effectivity date that is less than or equal to SYSDATE for the given organization and item, ensuring that future-dated revisions are excluded. The second then restricts PROCESS_REVISION to the maximum revision identifier among records sharing that same effectivity date, resolving ties where more than one revision becomes effective on the same day.

Key Columns

• ORGANIZATION_ID — The organization (inventory/plant) identifier that owns the routing revision. Part of the composite logical key of the view.
• INVENTORY_ITEM_ID — The inventory item for which the routing revision applies. Together with ORGANIZATION_ID, this uniquely identifies a returned row.
• CURRENT_REVISION — The revision identifier of the currently effective routing. Note that the view text selects PROCESS_REVISION from MTL_RTG_ITEM_REVISIONS, which is exposed under the column name CURRENT_REVISION in the view definition.
• EFFECTIVITY_DATE — The effective date of the returned current revision. Because of the filter logic, this value is always less than or equal to SYSDATE at query time.

Common Use Cases and Queries

The view is typically used to retrieve the active routing revision for reporting, interfaces, and validation routines without re-implementing effectivity logic. A common pattern is to join it back to routing or item master tables to enrich a report with the current process revision.

To return all current routing revisions for a specific organization:

• SELECT organization_id, inventory_item_id, current_revision, effectivity_date FROM bom_rtg_current_rev_view WHERE organization_id = :org_id;

To look up the current revision for a single item:

• SELECT current_revision FROM bom_rtg_current_rev_view WHERE organization_id = :org_id AND inventory_item_id = :item_id;

To join the view to the item master for descriptive reporting:

• SELECT v.organization_id, m.segment1, v.current_revision, v.effectivity_date FROM bom_rtg_current_rev_view v, mtl_system_items_b m WHERE v.inventory_item_id = m.inventory_item_id AND v.organization_id = m.organization_id AND m.organization_id = :org_id;

Because the view resolves the current revision using SYSDATE, results change over time as new revisions become effective. Consumers should therefore treat the output as a point-in-time snapshot rather than a stable identifier. For historical analysis, querying MTL_RTG_ITEM_REVISIONS directly is preferable.