Search Results MTL_MATERIAL_TRANSACTIONS

Overview

The MTL_MATERIAL_TRANSACTIONS table is the central transactional ledger for all inventory movement and valuation within Oracle E-Business Suite (EBS) Inventory Management (INV). It serves as the foundational data store for every material transaction processed by the system, recording the movement of items between subinventories, organizations, and various transaction sources. Its primary role is to maintain a complete, auditable history of inventory quantity changes and to provide the detailed source data necessary for inventory costing, period close processes, and supply chain reporting. The integrity of this table is critical for financial reconciliation and inventory accuracy.

Key Information Stored

The table's primary key is TRANSACTION_ID, a unique system-generated identifier for each transaction row. Key columns can be categorized into several groups. Transaction identification and type columns include TRANSACTION_TYPE_ID (linked to MTL_TRANSACTION_TYPES), TRANSACTION_SOURCE_ID, and TRANSACTION_SOURCE_NAME, which identify the originating document (e.g., a sales order, work order, or transfer). Item and location details are captured via INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE, and LOCATOR_ID. Quantity and date information is stored in TRANSACTION_QUANTITY, TRANSACTION_UOM, TRANSACTION_DATE, and ACCT_PERIOD_ID. For costing and accounting, critical columns include TRANSACTION_COST, COSTED_FLAG, DISTRIBUTION_ACCOUNT_ID, and references to cost groups (COST_GROUP_ID, TRANSFER_COST_GROUP_ID). The table also holds revision control (REVISION), lot/serial details (LOT_NUMBER, SERIAL_NUMBER), and references to specific adjustment or counting documents (PHYSICAL_ADJUSTMENT_ID, CYCLE_COUNT_ID).

Common Use Cases and Queries

This table is essential for transactional inquiry, inventory valuation reports, and reconciliation activities. A common use case is tracing the complete history of an item or lot. A typical query retrieves transaction details for analysis:

• SELECT mmt.transaction_id, mmt.transaction_date, mtt.transaction_type_name, mmt.transaction_quantity, mmt.subinventory_code, mmt.transaction_source_name FROM inv.mtl_material_transactions mmt, inv.mtl_transaction_types mtt WHERE mmt.inventory_item_id = :item_id AND mmt.organization_id = :org_id AND mmt.transaction_type_id = mtt.transaction_type_id ORDER BY mmt.transaction_date DESC;

Another critical use case is identifying uncosted transactions prior to a period close, using a query on the COSTED_FLAG. The table is also central to generating on-hand snapshot reports by summing transaction quantities and to reconciling inventory value with the general ledger by examining distribution accounts. Performance tuning often involves indexing on columns like TRANSACTION_DATE, INVENTORY_ITEM_ID, and ORGANIZATION_ID.

Related Objects

As indicated by its extensive foreign key relationships, MTL_MATERIAL_TRANSACTIONS is a core hub integrated with numerous EBS modules. Key related objects include MTL_SYSTEM_ITEMS_B for item master data, MTL_TRANSACTION_TYPES for transaction classification, and MTL_SECONDARY_INVENTORIES for subinventory details. For costing, it relates to CST_COST_GROUPS. Its integration with work in process is shown through links to WIP_DISCRETE_JOBS, WIP_REPETITIVE_ITEMS, and WIP_FLOW_SCHEDULES via TRANSACTION_SOURCE_ID. For receiving, it references RCV_TRANSACTIONS. Period control is managed through ORG_ACCT_PERIODS, and financial account validation is provided via GL_CODE_COMBINATIONS. The primary transactional interface is the INV_TRANSACTION_MANAGER_PUB public API, which is used to create and process transactions that ultimately populate this table.