Search Results mtl_material_transactions_u1

Overview

INV.MTL_MATERIAL_TRANSACTIONS is the central transactional fact table in Oracle E-Business Suite Inventory, storing a record of every material transaction or cost update performed in Inventory. Records are inserted either through the transaction processor or by the standard cost update program. The table holds approximately 180 columns, reflecting its role as the widest and most heavily referenced transaction ledger in the INV schema, and it is stored in the APPS_TS_TX_DATA tablespace with a PCT Free of 10.

Each row captures a single material movement — receipt, issue, transfer, subinventory transfer, WIP completion, physical adjustment, cycle count adjustment, or cost update — along with the item, organization, quantities, costs, and source references that describe it. Because virtually every inventory-driven business process (Order Management, Purchasing, WIP, Cost Management, and shipping) ultimately writes here, the table functions as a hub in a Data Vault modeling sense: it is the anchor against which numerous satellite and link tables attach via TRANSACTION_ID. This classification is a heuristic modeling suggestion derived from the foreign-key topology, not a physical constraint imposed by EBS.

Key Information Stored

The surrogate primary key is TRANSACTION_ID, enforced by MTL_MATERIAL_TRANSACTIONS_PK and by the unique index MTL_MATERIAL_TRANSACTIONS_U1, which the user searched for. A second unique index, MTL_MATERIAL_TRANSACTIONS_U2, covers the composite (TRANSACTION_ACTION_ID, TRANSACTION_TYPE_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_ID), providing an alternate business-key access path. The most operationally significant columns include:

• TRANSACTION_ID — surrogate primary key; the join key to nearly all dependent tables.
• INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION — identify the item, its inventory organization, and revision (revision controlled via REVISION, locator via LOCATOR_ID).
• TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID, TRANSACTION_SOURCE_NAME — together describe what the transaction is and against which entity it was performed.
• SUBINVENTORY_CODE, LOCATOR_ID — the physical storage location within the organization.
• TRANSACTION_QUANTITY, PRIMARY_QUANTITY, TRANSACTION_UOM, SECONDARY_TRANSACTION_QUANTITY — the movement quantity in primary and secondary UOMs.
• TRANSACTION_DATE, ACCT_PERIOD_ID — the effective date and accounting period.
• TRANSFER_TRANSACTION_ID, TRANSFER_ORGANIZATION_ID, TRANSFER_SUBINVENTORY, TRANSFER_LOCATOR_ID — for transfers, which are stored as two single records related through TRANSFER_TRANSACTION_ID.
• ACTUAL_COST, TRANSACTION_COST, PRIOR_COST, NEW_COST — cost figures associated with the movement or cost update.
• COSTED_FLAG, COST_UPDATE_ID, TRANSACTION_GROUP_ID — cost processing state and grouping.
• RCV_TRANSACTION_ID, MOVE_ORDER_LINE_ID, PICKING_LINE_ID, RESERVATION_ID — links back to the originating receiving, move-order, pick, and reservation activity.

Common Use Cases and Queries

The table supports transaction inquiry, on-hand reconciliation, cost accounting, and period-end reporting. A typical transaction-detail query joins the descriptive context tables:

• Item-organization transactional history: filter on INVENTORY_ITEM_ID, ORGANIZATION_ID, and a TRANSACTION_DATE range, backed by MTL_MATERIAL_TRANSACTIONS_N1 (INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSACTION_DATE).
• Transaction type analysis: join MTL_TRANSACTION_TYPES on TRANSACTION_TYPE_ID and MTL_TXN_SOURCE_TYPES on TRANSACTION_SOURCE_TYPE_ID to resolve codes to names.
• Costing reconciliation: join MTL_TRANSACTION_ACCOUNTS on TRANSACTION_ID to retrieve the accounting journal entries generated for each movement.
• Lot and serial traceability: join MTL_TRANSACTION_LOT_NUMBERS and MTL_UNIT_TRANSACTIONS on TRANSACTION_ID for lot- and serial-controlled items.
• Transfer pairing: self-join on TRANSFER_TRANSACTION_ID to view both legs of a transfer as a single logical event.
• Cost update auditing: filter on COST_UPDATE_ID or COSTED_FLAG to isolate rows inserted by the standard cost update program.

Related Objects

The following dependent objects are among the most significant consumers of the transaction record, all joined on TRANSACTION_ID unless noted:

• MTL_TRANSACTION_ACCOUNTS — accounting journal entries for each transaction.
• MTL_TRANSACTION_LOT_NUMBERS — lot details for lot-controlled items.
• MTL_UNIT_TRANSACTIONS — serial details for serial-controlled items.
• MTL_CST_TXN_COST_DETAILS and MTL_CST_ACTUAL_COST_DETAILS — cost breakdowns tied to the transaction.
• MTL_ONHAND_QUANTITIES — references the creating and updating transactions via CREATE_TRANSACTION_ID and UPDATE_TRANSACTION_ID.
• RCV_TRANSACTIONS — links receipts through RCV_TRANSACTION_ID / INV_TRANSACTION_ID.
• MTL_MATERIAL_TXN_ALLOCATIONS — allocation records referencing TRANSACTION_ID.
• CST_COGS_EVENTS — cost-of-goods-sold events via MMT_TRANSACTION_ID.

Additional referencing objects include WIP_MOVE_TRANSACTIONS (via MOVE_TRANSACTION_ID), MTL_MOVEMENT_STATISTICS (via MOVEMENT_ID), and MTL_RESERVATIONS (via RESERVATION_ID), confirming the table's position as the hub of the Inventory transaction model.