Search Results mtl_material_transactions




Overview
Explore in AI Chat

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:

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:

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.