Results for “bom_routing_revisions_view”

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

Overview

The APPS.BOM_ROUTING_REVISIONS_VIEW is a Bills of Material (BOM) reporting view that presents routing revisions together with their effective start and high dates. In Oracle EBS 12.1.1 and 12.2.2, routing revisions are stored in the MTL_RTG_ITEM_REVISIONS table, where each row records a single revision of an item's routing and its own EFFECTIVITY_DATE. The view's stated purpose is to expose, for every routing revision, the date from which it becomes effective and the date at which it is superseded by the next revision (HIGH_DATE). This "start date / end date" transformation is the core value of the view: it converts a vertically stored revision history into a form that can be joined to transactions, work orders, or cost records on a date range.

The view is owned by the APPS schema and is documented as VALID in the ETRM metadata for 12.2.2. It is typically consumed by custom reports, concurrent program extracts, and integration interfaces that need to answer the question "which routing revision was active for this item on this date?" Because the view derives HIGH_DATE from the next revision's effectivity date rather than storing it, it should be treated as a read-only reporting object and not as a maintained entity.

Underlying Base Objects

The documented base object is a single table referenced through a synonym: MTL_RTG_ITEM_REVISIONS. The view's defining query aliases this table twice — once as REV1 (the "current" revision) and once as REV2 (the "next" revision that bounds it). The two instances are outer-joined on ORGANIZATION_ID and INVENTORY_ITEM_ID, and the join is further restricted so that REV2.EFFECTIVITY_DATE > REV1.EFFECTIVITY_DATE. This correlated, self-referencing structure is what allows the view to compute an end date for each revision from the following revision's start date.

Note that the two aliases share the same organization and item but are not filtered on routing or process identity beyond that, so the boundaries are derived within the item's revision sequence. The outermost GROUP BY collapses the candidate "next" rows down to the earliest later effectivity date, yielding a single row per (ORGANIZATION_ID, INVENTORY_ITEM_ID, PROCESS_REVISION, EFFECTIVITY_DATE, IMPLEMENTATION_DATE) combination.

Key Columns

• ORGANIZATION_ID — the inventory organization that owns the routing revision; part of the key for date-effective lookups.
• INVENTORY_ITEM_ID — the item whose routing is being revised; joins to MTL_SYSTEM_ITEMS_B.
• PROCESS_REVISION — the routing revision identifier as stored on MTL_RTG_ITEM_REVISIONS.
• EFFECTIVITY_DATE — the start date (low date) of the revision, rendered as a character string, typically YYYY/MM/DD HH24:MI:SS, truncated to 25 characters.
• HIGH_DATE — the derived end date. When no later revision exists, the view substitutes GREATEST(SYSDATE, REV1.EFFECTIVITY_DATE), effectively making the revision open-ended to the present. When a later revision exists, the value is REV2.EFFECTIVITY_DATE minus one second (60/60/60/24 of a day) so the ranges do not overlap.
• IMPLEMENTATION_DATE — the date the revision was implemented, also formatted to the same character pattern.

Common Use Cases and Queries

The most common scenario is date-effective resolution: given an item and a date, return the routing revision in force. Because EFFECTIVITY_DATE and HIGH_DATE are exposed as formatted character strings, comparisons usually require an explicit conversion back to a date.

A typical query joining the view to item master data:

• SELECT v.organization_id, v.inventory_item_id, v.process_revision, v.effectivity_date, v.high_date FROM apps.bom_routing_revisions_view v WHERE v.organization_id = :org AND v.inventory_item_id = :item ORDER BY v.effectivity_date;
• Date-effective lookup for a specific point in time: SELECT process_revision FROM apps.bom_routing_revisions_view WHERE organization_id = :org AND inventory_item_id = :item AND TO_DATE(effectivity_date,'YYYY/MM/DD HH24:MI:SS') <= :as_of AND TO_DATE(high_date,'YYYY/MM/DD HH24:MI:SS') >= :as_of;
• Identifying the currently open revision (no later successor): filter where the derived high date equals the effectivity date or falls in the future.

Additional applications include routing revision history extracts for audit, verification that revision sequences contain no gaps or overlaps, and feeds into planning or costing interfaces that must respect the revision in effect at transaction time.