Results for “aog_flag”

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

Overview

AHL_SCHEDULE_MATERIALS is a table within the AHL schema, belonging to the Complex Maintenance Repair and Overhaul (AHL) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores information about materials scheduled in MRP (Material Requirements Planning) for maintenance execution. In the MRO lifecycle, when a maintenance visit task or a work order operation requires parts, the scheduling engine records the resulting planned material demand in this table, linking the requirement back to the visit, the visit task, the work order operation, and the inventory item involved.

From a Data Vault modeling perspective, the metadata's FK structure suggests a satellite-leaning classification. The table's primary key is a single surrogate column, and virtually all of its meaningful columns are descriptive attributes or foreign references to parent entities (visits, tasks, operations, items). It therefore behaves more like a descriptive satellite attached to those business keys than an independent hub or a pure link. This classification is a heuristic modeling suggestion, not an Oracle-declared characteristic.

Key Information Stored

The table contains 58 documented columns. The most operationally significant are:

• SCHEDULED_MATERIAL_ID — surrogate primary key, enforced by AHL_SCHEDULED_MATERIALS_PK. A unique index (AHL_SCHEDULE_MATERIALS_UK1) also exists on this column, making it the documented business-key candidate.
• INVENTORY_ITEM_ID — the inventory item being scheduled for the maintenance requirement.
• VISIT_ID and VISIT_TASK_ID — the maintenance visit and specific task that drive the demand.
• WORKORDER_OPERATION_ID — the work order operation consuming the material.
• ORGANIZATION_ID — the inventory organization supplying the item.
• SCHEDULED_QUANTITY, REQUESTED_QUANTITY, and COMPLETED_QUANTITY — the planned, requested, and fulfilled quantities.
• SCHEDULED_DATE and REQUESTED_DATE — timing of the requirement.
• UOM — unit of measure for the quantities.
• PROCESS_STATUS and STATUS — workflow/processing state of the scheduled material.
• MATERIAL_REQUEST_TYPE — classification of the material request.
• TRANSACTION_ID — reference to the resulting inventory transaction, if posted.
• SECURITY_GROUP_ID — operating unit / security context used by AHL.

Common Use Cases and Queries

Typical reporting includes material availability by visit, open scheduled demand not yet transacted, and comparisons of requested versus completed quantities. A common pattern joins to the visit task and inventory item:

SELECT sm.scheduled_material_id, sm.inventory_item_id,
       sm.scheduled_quantity, sm.requested_quantity,
       sm.completed_quantity, sm.status
FROM   ahl_schedule_materials sm
WHERE  sm.visit_task_id = :visit_task_id
AND    sm.process_status = 'PENDING';

Another frequent query joins to AHL_WO_OPERATIONS_TXNS to reconcile scheduled versus transacted materials for a work order operation, using SCHEDULE_MATERIAL_ID as the join key in the reverse direction.

Related Objects

• AHL_WO_OPERATIONS_TXNS — references this table via SCHEDULE_MATERIAL_ID, recording actual operations transactions.
• AHL_VISIT_TASKS_B — parent of VISIT_TASK_ID.
• AHL_VISITS_B — parent of VISIT_ID.
• AHL_WORKORDER_OPERATIONS — parent of WORKORDER_OPERATION_ID.
• AHL_RT_OPER_MATERIALS — parent of RT_OPER_MATERIAL_ID (routing/operation material definition).
• AHL_ITEM_GROUPS_B, AHL_UNIT_EFFECTIVITIES_B, AHL_MR_ROUTES — referenced via ITEM_GROUP_ID, UNIT_EFFECTIVITY_ID, and MR_ROUTE_ID respectively.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID for the operating unit security context.