Search Results current_revision




Overview
Explore in AI Chat

Overview

APPS.BOM_RTG_CURRENT_REV_VIEW is a reporting view in the Oracle E-Business Suite Applications schema (APPS) that resolves the currently effective routing revision for each organization and inventory item combination. The view is defined over the routing item revisions table and returns only a single row per organization/item pairing: the row whose effectivity date is the latest date on or before the current system date (SYSDATE), and, within that effectivity date, the highest process revision number. In this way the view answers the question "what routing revision is in force right now for this item in this organization?" without requiring callers to write the nested MAX subqueries themselves.

Because routing revision data is versioned by both effectivity date and process revision, consumers of the base table must otherwise evaluate two dimensions of currency. BOM_RTG_CURRENT_REV_VIEW encapsulates that logic, making it useful for reporting, concurrent programs, interfaces, and integrations that need a stable, single-row snapshot of the active routing revision. The view is read-only and exposes no data of its own; its behavior is entirely derived from the underlying table at query time, so results always reflect the current contents of the base table and the current database system date.

Underlying Base Objects

The view is defined exclusively over MTL_RTG_ITEM_REVISIONS, referenced in the APPS schema as a synonym. MTL_RTG_ITEM_REVISIONS stores routing revisions for items, keyed by organization, inventory item, process revision, and effectivity date. In the view text the table is aliased as MRIR and is referenced both in the outer query and in two correlated subqueries. The first subquery determines the maximum effectivity date for the same organization and item that is less than or equal to SYSDATE. The second subquery determines the maximum process revision for that organization, item, and effectivity date. A row is returned only when both conditions match. No other base objects, joins, or views participate in the definition.

Key Columns

  • ORGANIZATION_ID — The inventory organization that owns the item and routing revision. Forms part of the effective key along with the item.
  • INVENTORY_ITEM_ID — The inventory item for which the routing revision applies. Combined with ORGANIZATION_ID it identifies the item within the organization.
  • PROCESS_REVISION — The routing (process) revision identifier. The view returns the maximum revision available for the selected effectivity date.
  • EFFECTIVITY_DATE — The date on which the revision becomes effective. The view returns the latest such date not exceeding SYSDATE.

Common Use Cases and Queries

The view is typically used to report or drive processing against the active routing revision for an item, to validate that a revision exists before scheduling or costing work, or to feed downstream integrations that expect a single current revision per item.

To retrieve the current routing revision for a specific item in an organization:

  • SELECT organization_id, inventory_item_id, process_revision, effectivity_date FROM apps.bom_rtg_current_rev_view WHERE organization_id = :org_id AND inventory_item_id = :item_id;

To list current revisions across an organization:

  • SELECT inventory_item_id, process_revision, effectivity_date FROM apps.bom_rtg_current_rev_view WHERE organization_id = :org_id ORDER BY inventory_item_id;

Because the view filters on SYSDATE, revise revision records with future effectivity dates do not appear until their effectivity date arrives. Ensure the session's date and timezone context are appropriate for the reporting requirement.