Results for “mtl_rtg_item_revisions_pk”

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

Overview

MTL_RTG_ITEM_REVISIONS is an Inventory (INV) module table that stores routing revisions associated with manufactured inventory items in Oracle E-Business Suite 12.1.1 and 12.2.2. Each row represents a specific revision level of a routing for a given inventory item within a given organization, capturing the engineering change context, effectivity dates, and implementation details under which that revision became active.

Routing revisions are fundamental to discrete manufacturing and flow manufacturing execution. When an item's manufacturing process changes — through an engineering change order, a process improvement, or a new bill of routing — a new routing revision is defined and linked to prior revisions through effectivity and change notice data. This table serves as the master reference for which routing revision is valid at any point in time for a given item/organization combination.

From a Data Vault modeling perspective, the heuristic classification of this table is satellite-leaning. The primary key (INVENTORY_ITEM_ID, ORGANIZATION_ID, PROCESS_REVISION) identifies a revision record that describes attributes of an item-organization relationship over time, rather than acting as an independent business hub. The descriptive attributes (change notice, effectivity dates, implementation data) are the mutable characteristics typical of a satellite structure.

Key Information Stored

The table contains 34 documented columns. The most significant include:

• INVENTORY_ITEM_ID — The inventory item for which this routing revision is defined. Part of the composite primary key MTL_RTG_ITEM_REVISIONS_PK.
• ORGANIZATION_ID — The manufacturing organization context. Also part of the primary key; routing revisions are organization-specific.
• PROCESS_REVISION — The alphanumeric revision identifier (e.g., 'A', 'B', 'C'). Completes the primary key and uniquely names the revision.
• CHANGE_NOTICE — References the engineering change (ECN) that authorized this revision. Foreign key to ENG_ENGINEERING_CHANGES.
• ECN_INITIATION_DATE — The date the associated engineering change was initiated.
• IMPLEMENTATION_DATE — The date on which the revision was implemented in manufacturing.
• EFFECTIVITY_DATE — The date from which this routing revision becomes effective for the item/organization.
• IMPLEMENTED_SERIAL_NUMBER — The serial number at which the revision was first implemented, relevant for serialized manufacturing.
• REVISED_ITEM_SEQUENCE_ID — Foreign key to ENG_REVISED_ITEMS, linking the routing revision to the engineering revised item record.
• Standard WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.
• Concurrent program columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, indicating whether the record was generated by a concurrent process.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield (DFF) columns for customer-specific extensions.

The unique index MTL_RTG_ITEM_REVISIONS_U1 (INVENTORY_ITEM_ID, PROCESS_REVISION, ORGANIZATION_ID) mirrors the primary key as the business-key candidate, ensuring one revision record per item per organization per revision label.

Common Use Cases and Queries

This table is typically queried to determine which routing revision is effective for an item at a point in time, to trace revisions back to their authorizing engineering change, and to reconcile work orders and schedules against valid revisions.

A representative query to list effective routing revisions for an item in an organization:

SELECT inventory_item_id, organization_id, process_revision,
       effectivity_date, change_notice, implementation_date
FROM   mtl_rtg_item_revisions
WHERE  inventory_item_id = :item_id
AND    organization_id  = :org_id
ORDER BY effectivity_date DESC;

To identify revisions implemented for a specific engineering change notice:

SELECT r.inventory_item_id, r.organization_id, r.process_revision,
       e.change_notice
FROM   mtl_rtg_item_revisions r,
       eng_engineering_changes e
WHERE  r.change_notice = e.change_notice
AND    r.implementation_date BETWEEN :start_date AND :end_date;

Reporting scenarios include: effectivity audits (which revision was active on a given date), ECN impact analysis (all items affected by a change), and manufacturing readiness checks comparing WIP_DISCRETE_JOBS routing revisions against this master reference.

Related Objects

The following objects are the most significant related tables and their join columns:

• MTL_PARAMETERS — Joined on ORGANIZATION_ID; defines the organization context for each routing revision.
• ENG_ENGINEERING_CHANGES — Joined on CHANGE_NOTICE; provides engineering change authorization details.
• ENG_REVISED_ITEMS — Joined on REVISED_ITEM_SEQUENCE_ID; links the routing revision to the revised item engineering record.
• WIP_DISCRETE_JOBS — References this table via PRIMARY_ITEM_ID, ORGANIZATION_ID, and ROUTING_REVISION; discrete jobs consume valid routing revisions.
• WIP_FLOW_SCHEDULES — References this table via PRIMARY_ITEM_ID, ORGANIZATION_ID, and ROUTING_REVISION; flow schedules depend on routing revisions for execution.
• MTL_ITEM_REVISIONS — Closely related table capturing item (not routing) revisions, providing complementary revision history for the same item.
• MTL_ROUTING_HEADERS / BOM_ROUTING_HEADERS — Store the routing definition that each revision version controls.

Together these objects form the routing revision backbone that connects engineering change management, inventory item definitions, and work order execution across discrete and flow manufacturing in Oracle EBS.