Results for “ahl_workorder_mtl_txns”

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

Overview

The AHL_WORKORDER_MTL_TXNS table is a core transactional object within the Oracle E-Business Suite (EBS) Complex Maintenance Repair and Overhaul (AHL) module, owned by the AHL schema. Its documented purpose is to store the material transactions that were issued against a job. In the context of EBS 12.1.1 and 12.2.2, this table acts as the bridge between the maintenance execution side of AHL (work orders and work order operations) and the Oracle Inventory transaction model, capturing each item issue against a job with full context including organization, subinventory, locator, lot, and serial references.

The table carries a documented primary key, AHL_WORKORDER_MTL_TXNS_PK, defined on the surrogate column WORKORDER_MTL_TXN_ID, and an additional unique index AHL_WORKORDER_MTL_TXNS_U1 on the same column. From a modeling perspective, the heuristic Data Vault classification mined from the foreign key structure is satellite-leaning: the table is a child that records descriptive measures and attributes attached to a parent work order and to an Inventory material transaction, rather than acting as a standalone hub. The documented schema contains 44 columns, including the standard EBS audit columns and a set of 15 descriptive flexfield attribute columns.

Key Information Stored

The table's identity and relationships are governed by a small set of critical columns:

• WORKORDER_MTL_TXN_ID — the surrogate primary key and the single documented unique index, uniquely identifying each material transaction line issued against a job.
• WORKORDER_OPERATION_ID — the foreign key to AHL_WORKORDER_OPERATIONS, tying the material issue to the specific operation on the work order where the item was consumed.
• NON_ROUTINE_WORKORDER_ID — the foreign key to AHL_WORKORDERS, identifying the job against which the material was issued.
• MATERIAL_TRANSACTION_ID — the foreign key to MTL_MATERIAL_TRANSACTIONS, linking the AHL record to the underlying Oracle Inventory transaction that actually moved stock.
• ORGANIZATION_ID and INVENTORY_ITEM_ID — combined, these reference MTL_SYSTEM_ITEMS_B, and they also participate in the lot and serial number foreign keys, defining which item in which inventory organization was transacted.
• REVISION, LOT_NUMBER, and SERIAL_NUMBER — item revision and the lot/serial identifiers, with lot and serial both validated against MTL_LOT_NUMBERS and MTL_SERIAL_NUMBERS.
• SUBINVENTORY_CODE and LOCATOR_ID — the source subinventory (FK to MTL_SECONDARY_INVENTORIES) and stock locator (FK to MTL_ITEM_LOCATIONS).
• TRANSACTION_TYPE_ID — the foreign key to MTL_TRANSACTION_TYPES, indicating the nature of the issue.
• QUANTITY, PRIMARY_UOM_QTY, and UOM — the transaction quantity, its primary unit of measure equivalent, and the entered UOM.
• TRANSACTION_DATE — the effective date of the material issue, used for period and cost reporting.
• COLLECTION_ID, STATUS_ID, RECEPIENT_ID, and CS_INCIDENT_ID — optional context linking the transaction to a collection, a status, a recipient, or a service incident.
• SECURITY_GROUP_ID — the FK to FND_SECURITY_GROUPS, ensuring multi-org and security-group isolation.

Common Use Cases and Queries

The table is primarily queried for material consumption reporting on maintenance jobs, cost roll-ups, and reconciliation between AHL maintenance activity and Inventory transaction history. A typical reporting query joins the work order and item details:

• Listing all materials issued to a job: SELECT wmt.workorder_mtl_txn_id, wmt.quantity, wmt.uom, wmt.transaction_date FROM ahl_workorder_mtl_txns wmt WHERE wmt.non_routine_workorder_id = :p_workorder_id ORDER BY wmt.transaction_date;
• Reconciling to Inventory: joining MATERIAL_TRANSACTION_ID to MTL_MATERIAL_TRANSACTIONS to confirm the stock movement and transaction source.
• Item consumption by operation: grouping by WORKORDER_OPERATION_ID to attribute material cost to specific work order operations.
• Lot/serial traceability: filtering on LOT_NUMBER or SERIAL_NUMBER with INVENTORY_ITEM_ID and ORGANIZATION_ID to trace where a specific lot or serial was issued.
• Period and subinventory analysis: aggregating PRIMARY_UOM_QTY or QUANTITY by SUBINVENTORY_CODE and TRANSACTION_DATE for stock consumption review.

Related Objects

The following related objects are the most significant for joins and dependency analysis, based on the documented foreign key metadata:

• AHL_WORKORDERS — joined on NON_ROUTINE_WORKORDER_ID; the parent job header.
• AHL_WORKORDER_OPERATIONS — joined on WORKORDER_OPERATION_ID; the operation consuming the material.
• MTL_MATERIAL_TRANSACTIONS — joined on MATERIAL_TRANSACTION_ID; the authoritative Inventory transaction record.
• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID and ORGANIZATION_ID; item master definition.
• MTL_SECONDARY_INVENTORIES — joined on SUBINVENTORY_CODE and ORGANIZATION_ID; source subinventory.
• MTL_ITEM_LOCATIONS — joined on LOCATOR_ID and ORGANIZATION_ID; physical stock locator.
• MTL_TRANSACTION_TYPES — joined on TRANSACTION_TYPE_ID; transaction type classification.
• MTL_LOT_NUMBERS and MTL_SERIAL_NUMBERS — joined on item, organization, and the lot/serial columns for traceability.
• AHL_PART_CHANGES — a child table that references this table via WORKORDER_MTL_TXN_ID, capturing part change events linked to a material transaction.
• FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; governs multi-org security isolation.