Search Results mtl_material_transactions_pk

Overview

MTL_MATERIAL_TRANSACTIONS is the core transaction ledger of the Oracle Inventory (INV) module, owned by the INV schema. Every material movement that affects on-hand balances, valuation, or costing within an inventory organization is recorded as a row in this table. It captures receipts, issues, subinventory transfers, miscellaneous transactions, WIP completions and component issues, cycle count and physical inventory adjustments, and inter-organization shipments. In effect, it is the single source of truth for inventory movement history in Oracle E-Business Suite 12.1.1 and 12.2.2.

From a Data Vault modeling perspective, the ETRM classification identifies this object as a hub. This reflects its role as the central anchor of transaction identity, referenced by a large constellation of costing, receiving, shipping, and manufacturing satellites. The primary key is MTL_MATERIAL_TRANSACTIONS_PK, defined on TRANSACTION_ID.

Key Information Stored

The table contains 180 documented columns in the 12.2.2 physical schema. The most operationally significant include:

• TRANSACTION_ID — surrogate primary key and unique index (MTL_MATERIAL_TRANSACTIONS_U1).
• INVENTORY_ITEM_ID — the item transacted; joins to MTL_SYSTEM_ITEMS_B by item and organization.
• ORGANIZATION_ID — the inventory organization in which the transaction occurred.
• REVISION — item revision, where revision control applies.
• SUBINVENTORY_CODE and LOCATOR_ID — the source subinventory and locator.
• TRANSACTION_TYPE_ID and TRANSACTION_ACTION_ID — classify the movement (e.g., receipt, issue, transfer).
• TRANSACTION_SOURCE_TYPE_ID and TRANSACTION_SOURCE_ID — identify the originating document (sales order, WIP job, purchase order).
• TRANSACTION_QUANTITY, PRIMARY_QUANTITY, TRANSACTION_UOM — quantities and unit of measure.
• TRANSACTION_DATE and ACCT_PERIOD_ID — posting date and accounting period linkage to ORG_ACCT_PERIODS.
• TRANSFER_ORGANIZATION_ID, TRANSFER_SUBINVENTORY, TRANSFER_LOCATOR_ID — destination details for transfers.
• TRANSACTION_COST, ACTUAL_COST, PRIOR_COST, NEW_COST — valuation attributes populated during costing.
• COSTED_FLAG — indicates whether the transaction has been picked up by the cost processor.
• RCV_TRANSACTION_ID, MOVE_ORDER_LINE_ID, PICKING_LINE_ID — upstream document references.

The second unique index (MTL_MATERIAL_TRANSACTIONS_U2) spans TRANSACTION_ACTION_ID, TRANSACTION_TYPE_ID, TRANSACTION_SOURCE_TYPE_ID, and TRANSACTION_ID, functioning as a composite business-key candidate alongside the surrogate primary key.

Common Use Cases and Queries

MMT is the primary subject of on-hand reconciliation, transaction audit, and costing analysis. Typical reporting includes transaction history for a given item and organization, unexplained on-hand variances, and uncosted transaction review.

A common query pattern retrieves recent transaction activity for an item:

• Filter MMT by INVENTORY_ITEM_ID, ORGANIZATION_ID, and TRANSACTION_DATE range.
• Join MTL_TRANSACTION_TYPES on TRANSACTION_TYPE_ID for a readable type name.
• Join MTL_SYSTEM_ITEMS_B on INVENTORY_ITEM_ID and ORGANIZATION_ID for descriptions.

Costing teams query rows where COSTED_FLAG is null or where TRANSACTION_COST is null to identify costing gaps. Receiving reconciliation joins MMT.RCV_TRANSACTION_ID to RCV_TRANSACTIONS. Inventory auditors frequently correlate MMT to MTL_ONHAND_QUANTITIES via CREATE_TRANSACTION_ID and UPDATE_TRANSACTION_ID to reconstruct the timeline of balance changes. Because of the table's size, queries should always be bounded by organization, date range, or item to avoid full scans.

Related Objects

MTL_MATERIAL_TRANSACTIONS is heavily referenced. The most significant dependent and related objects include:

• MTL_TRANSACTION_ACCOUNTS — distribution lines generated per transaction; joined by TRANSACTION_ID.
• MTL_CST_TXN_COST_DETAILS and MTL_CST_ACTUAL_COST_DETAILS — costing detail rows keyed by TRANSACTION_ID.
• MTL_ONHAND_QUANTITIES — references MMT through CREATE_TRANSACTION_ID and UPDATE_TRANSACTION_ID.
• MTL_UNIT_TRANSACTIONS — item-level transaction detail keyed by TRANSACTION_ID.
• MTL_TRANSACTION_LOT_NUMBERS — lot associations for the transaction.
• MTL_SERIAL_NUMBERS — links via LAST_TRANSACTION_ID.
• RCV_TRANSACTIONS — receiving records referencing INV_TRANSACTION_ID.
• CST_COGS_EVENTS — cost-of-goods-sold events referencing MMT_TRANSACTION_ID.
• MTL_MATERIAL_TXN_ALLOCATIONS — allocation detail for transfer transactions.

Applications interact with MMT primarily through the Open Transaction Interface and INV transaction APIs (e.g., INV_TXN_MANAGER_PUB), rather than through direct inserts.