Search Results ahl_workorder_mtl_txns




Overview
Explore in AI Chat

Overview

The AHL_WORKORDER_MTL_TXNS table is a core transactional entity within the Oracle E-Business Suite Complex Maintenance, Repair, and Overhaul (CMRO) application. Its primary function is to serve as a detailed audit and reference log for all material consumption and issuance activities directly associated with maintenance work orders. When parts are issued from inventory to a specific job or operation, this table records a link between the core inventory transaction (in MTL_MATERIAL_TRANSACTIONS) and the CMRO work order structure. This linkage is critical for tracking job costing, material usage history, and ensuring accurate inventory reconciliation within a maintenance and service environment.

Key Information Stored

The table's structure centers on foreign key relationships that tie together the work order, the inventory item, and the transaction details. The primary key is WORKORDER_MTL_TXN_ID, a unique system-generated identifier for each record. Essential columns include WORKORDER_OPERATION_ID, linking the transaction to a specific operation step, and NON_ROUTINE_WORKORDER_ID, linking it to the parent work order. The critical MATERIAL_TRANSACTION_ID column provides the direct join to the foundational inventory transaction in MTL_MATERIAL_TRANSACTIONS. Other key columns store item specifics (INVENTORY_ITEM_ID, ORGANIZATION_ID), location details (SUBINVENTORY_CODE, LOCATOR_ID), lot/serial control data (LOT_NUMBER, SERIAL_NUMBER), and the transaction classification (TRANSACTION_TYPE_ID).

Common Use Cases and Queries

This table is central to reporting and analysis on material consumption for maintenance work. Common use cases include generating detailed cost reports for a work order by summing transaction values, tracing the installation history of a specific serialized component, and auditing part usage against planned bills of material. A typical query pattern joins AHL_WORKORDER_MTL_TXNS to MTL_MATERIAL_TRANSACTIONS for cost and quantity details, and to AHL_WORKORDERS for work order attributes.

  • Sample Query (Material Issues for a Work Order):
    SELECT awmt.*, mmt.transaction_quantity, mmt.transaction_uom
    FROM AHL_WORKORDER_MTL_TXNS awmt,
    MTL_MATERIAL_TRANSACTIONS mmt,
    AHL_WORKORDERS awo
    WHERE awmt.MATERIAL_TRANSACTION_ID = mmt.TRANSACTION_ID
    AND awmt.NON_ROUTINE_WORKORDER_ID = awo.WORKORDER_ID
    AND awo.WORKORDER_NUMBER = '<Workorder_Number>';

Related Objects

The table maintains defined foreign key relationships with several fundamental EBS tables, as documented in the ETRM. These relationships are essential for data integrity and query joins.

  • AHL_WORKORDER_OPERATIONS: Via WORKORDER_OPERATION_ID. Links the material issue to a specific operation within the work order routing.
  • AHL_WORKORDERS: Via NON_ROUTINE_WORKORDER_ID. Links to the parent work order header.
  • MTL_MATERIAL_TRANSACTIONS: Via MATERIAL_TRANSACTION_ID. The pivotal link to the core Inventory transaction record containing quantities, dates, and costs.
  • MTL_SYSTEM_ITEMS_B: Via INVENTORY_ITEM_ID and ORGANIZATION_ID. Provides item master details like description and part number.
  • MTL_SECONDARY_INVENTORIES / MTL_ITEM_LOCATIONS: Via SUBINVENTORY_CODE, ORGANIZATION_ID, and LOCATOR_ID. Identifies the inventory source from which the material was issued.
  • MTL_TRANSACTION_TYPES: Via TRANSACTION_TYPE_ID. Classifies the transaction (e.g., Work Order Issue).
  • MTL_LOT_NUMBERS / MTL_SERIAL_NUMBERS: Via INVENTORY_ITEM_ID, ORGANIZATION_ID, and LOT_NUMBER or SERIAL_NUMBER. For tracking specific lot or serial-controlled items.
  • AHL_PART_CHANGES: This table references AHL_WORKORDER_MTL_TXNS via WORKORDER_MTL_TXN_ID, likely for managing part replacement and component history tracking.