Results for “ahl_rt_oper_materials_h”

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

Overview

AHL_RT_OPER_MATERIALS_H is the history (audit) table for AHL_RT_OPER_MATERIALS in the Oracle E-Business Suite AHL module — Complex Maintenance Repair and Overhaul (CMRO). The AHL product supports the maintenance, repair, and overhaul of complex assets, especially in aerospace and defense environments, where routings and operations define the work performed against repairable units. The RT_OPER_MATERIALS entity captures the material requirements attached to those operations — the inventory items consumed, their quantities, units of measure, and duration characteristics. The _H table preserves prior versions of those rows whenever a record is changed, providing the temporal backbone required for configuration management, auditability, and regulatory traceability in MRO settings.

Under the heuristic Data Vault classification mined from the foreign-key structure, this table is treated as a link. This modeling suggestion reflects the fact that the table is dominated by relationships — it ties an operation or routing object to a material master record and to a parent operational materials row — and its primary key is a surrogate identifier rather than a natural business key composed of attributes.

Key Information Stored

The documented physical schema for release 12.2.2 lists 35 columns, the most operationally significant of which include:

• RT_OPER_MATERIALS_H_ID — the surrogate primary key, enforced by AHL_RT_OPER_MATERIALS_H_PK and by the unique index AHL_RT_OPER_MATERIALS_H_U1.
• RT_OPER_MATERIAL_ID — foreign key to AHL_RT_OPER_MATERIALS; identifies which source row's history this record represents.
• OBJECT_ID — foreign key participating in the relationship to AHL_ROUTES_B and AHL_OPERATIONS_B; identifies the routing or operation the material is attached to.
• INVENTORY_ITEM_ID and INVENTORY_ORG_ID — foreign key pair referencing MTL_SYSTEM_ITEMS_B; together they identify the material item in a specific inventory organization.
• UOM_CODE — the unit of measure in which the quantity is expressed.
• REVISION_NUMBER — captures the item revision relevant to the material requirement.
• QUANTITY, DURATION, and TIME_SPAN — the planned consumption quantity and its associated timing attributes.
• REC_TYPE_CODE and ASSOCIATION_TYPE_CODE — control codes describing the record category and how the material associates with the parent object.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, supporting multi-org and data-security partitioning.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the Oracle Applications Framework.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN — the WHO audit columns that, in a history table, document the version's origin rather than the original creation.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — the standard Oracle DFF (descriptive flexfield) columns for user-extensible attributes.

The distinction between the surrogate key and the business-key candidate matters here: because the RT_OPER_MATERIALS_H_ID is generated and unique, it identifies a version; the business identity of the requirement is carried by RT_OPER_MATERIAL_ID together with the item, revision, and effective-dating semantics.

Common Use Cases and Queries

Typical uses fall into three categories:

• As-of and point-in-time reporting. Reconstruct the material list associated with an operation or routing as it stood on a given date, which is essential when an engineering change alters quantities or item revisions mid-program.
• Change audit and compliance. Trace who changed quantity, UOM, item revision, or association type, and when — frequently required for airworthiness or contractual traceability.
• Data migration and reconciliation. Compare current AHL_RT_OPER_MATERIALS rows to their history to detect orphaned or superseded versions.

A representative pattern retrieves the latest historical version for a given source material row:

SELECT h.* FROM ahl.ahl_rt_oper_materials_h h WHERE h.rt_oper_material_id = :p_id AND h.last_update_date = (SELECT MAX(h2.last_update_date) FROM ahl.ahl_rt_oper_materials_h h2 WHERE h2.rt_oper_material_id = h.rt_oper_material_id)

Joins to MTL_SYSTEM_ITEMS_B for item description, and to AHL_OPERATIONS_B or AHL_ROUTES_B via OBJECT_ID, are the most frequent reporting combinations.

Related Objects

• AHL_RT_OPER_MATERIALS — the current-row parent; joined on RT_OPER_MATERIAL_ID, the primary lineage path.
• MTL_SYSTEM_ITEMS_B — item master; joined on INVENTORY_ITEM_ID and INVENTORY_ORG_ID.
• AHL_ROUTES_B — routing header; joined on OBJECT_ID.
• AHL_OPERATIONS_B — operation definition; joined on OBJECT_ID.
• FND_SECURITY_GROUPS — security group definition; joined on SECURITY_GROUP_ID.

Together these relationships position AHL_RT_OPER_MATERIALS_H as the historical link between CMRO work definitions and the inventory items they consume.