Results for “ahl_ump_material_reqmnts”

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

Overview

AHL_UMP_MATERIAL_REQMNTS is a table in the AHL schema belonging to the Complex Maintenance Repair and Overhaul (CMRO) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. Its name reflects its role within the Unit Maintenance Plan (UMP) framework: it stores the material requirements — components, consumables, and spare parts — associated with maintenance activities modeled inside a maintenance simulation plan. The table acts as the bridge between a planned maintenance event and the inventory items required to execute it.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign-key structure indicates that this table is a standalone object. It does not itself function as a hub, link, or satellite in a strict Data Vault sense; rather, it is a transactional detail table whose records depend on a parent simulation plan. The classification should be treated as a modeling suggestion, not a definitive architectural statement.

Key Information Stored

The table contains 29 documented columns. The most significant ones are:

• MAINTENANCE_REQMNT_ID — the surrogate primary key uniquely identifying each material requirement row.
• SIMULATION_PLAN_ID — foreign key to AHL_SIMULATION_PLANS_B, linking the requirement to its parent maintenance simulation plan.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing Oracle's multi-tenant security model.
• OPERATING_ORG_ID — the inventory organization in which the material requirement is valid.
• INVENTORY_ITEM_ID — the specific item (part, component, consumable) required.
• QUANTITY — the quantity of the item demanded by the requirement.
• UOM_CODE — the unit of measure for the quantity.
• OBJECT_TYPE — a discriminator that qualifies the nature of the requirement record.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit Who columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — the flexible descriptive flexfield (DFF) columns used for customer-specific extensions.

The surrogate key is MAINTENANCE_REQMNT_ID. No documented unique business-key index is listed in the metadata, though the combination of SIMULATION_PLAN_ID, INVENTORY_ITEM_ID, and OPERATING_ORG_ID is a natural candidate for a business key.

Common Use Cases and Queries

Typical use involves extracting the bill of materials associated with a maintenance simulation plan for cost estimation or procurement planning. For example, to list all material requirements for a given plan:

SELECT mr.maintenance_reqmnt_id, mr.inventory_item_id, mr.quantity, mr.uom_code
FROM ahl.ahl_ump_material_reqmnts mr
WHERE mr.simulation_plan_id = :plan_id;

Reporting use cases include: calculating total material cost per plan by joining to inventory item cost tables, reconciling planned versus actual consumption, feeding MRP or purchasing requisitions, and auditing security-group-scoped access. Analysts frequently join this table to AHL_SIMULATION_PLANS_B to obtain plan context and to FND_SECURITY_GROUPS for security verification.

Related Objects

The following objects are most significant in relation to this table:

• AHL_SIMULATION_PLANS_B — the parent plan definition; joined on SIMULATION_PLAN_ID.
• FND_SECURITY_GROUPS — security group master; joined on SECURITY_GROUP_ID.
• MTL_SYSTEM_ITEMS_B — inventory item master; joined on INVENTORY_ITEM_ID for item descriptions and attributes.
• ORG_ORGANIZATION_DEFINITIONS — inventory organization context; joined on OPERATING_ORG_ID.
• MTL_UNITS_OF_MEASURE — unit-of-measure validation; joined on UOM_CODE.

These relationships allow complete reconstruction of the maintenance material demand context within Oracle CMRO.