Results for “mtl_material_trans_temp_u1”
14 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
MTL_MATERIAL_TRANSACTIONS_TEMP (commonly abbreviated MMTT) is the staging and gateway table for all material transactions in Oracle Inventory. Every Inventory transaction form — subinventory transfers, miscellaneous issues and receipts, sales order pick and ship transactions, WIP issues and completions, receiving, and move order transactions — writes directly into this table. The transaction processor subsequently consumes MMTT records and posts them into the permanent Inventory transaction tables (MTL_MATERIAL_TRANSACTIONS and its related accounting and cost distributions). External or custom applications are expected to populate MTL_TRANSACTIONS_INTERFACE instead, which the Transaction Worker concurrent program then seeds into MMTT before processing. The table is owned by the INV schema, resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, and holds a documented 243 columns in the 12.2.2 physical schema, reflecting its role as a wide, denormalized work area.
The ETRM metadata's heuristic Data Vault classification for this object is satellite-leaning. That is a modeling suggestion rather than an Oracle-imposed designation: MMTT behaves like a transient descriptive satellite carrying the full transactional context around a small number of identifying keys, and it references a substantial number of hub-style parent entities (items, organizations, locators, subinventories, jobs) through foreign keys.
Key Information Stored
Two identifiers dominate the table. TRANSACTION_TEMP_ID is enforced by the unique index MTL_MATERIAL_TRANS_TEMP_U1 and functions as the true business-key candidate — it is the column to which lot and serial staging rows (MTL_TRANSACTION_LOTS_TEMP and MTL_SERIAL_NUMBERS_TEMP) join. TRANSACTION_HEADER_ID is the designated primary key (MTL_MATERIAL_TRANSACTIONS_T_PK) and links related lines together. Among the most consequential columns:
- TRANSACTION_TEMP_ID — unique surrogate key per staging record; join point to lot and serial temp tables.
- TRANSACTION_HEADER_ID — primary key grouping multi-line transaction headers.
- TRANSACTION_TYPE_ID / TRANSACTION_ACTION_ID — identifies the transaction type (e.g., issue, receipt, transfer) and its accounting action.
- SOURCE_CODE / SOURCE_LINE_ID — indicates the originating application and originating line reference.
- INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION — the item, inventory organization, and revision being transacted.
- SUBINVENTORY_CODE, LOCATOR_ID — the source subinventory and locator.
- TRANSFER_SUBINVENTORY, TRANSFER_TO_LOCATION, TRANSFER_ORGANIZATION — destination values for transfer transactions.
- TRANSACTION_QUANTITY, PRIMARY_QUANTITY, TRANSACTION_UOM — quantity entered versus quantity in the item's primary UOM.
- TRANSACTION_DATE, ACCT_PERIOD_ID — effective date and the open accounting period into which the transaction posts.
- TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID — the source document (work order, move order line, RMA, etc.).
- PROCESS_FLAG, TRANSACTION_STATUS, ERROR_CODE / ERROR_EXPLANATION — transaction processor state and failure diagnostics.
- TRANSACTION_COST, LOT_NUMBER, SERIAL_NUMBER, MOVEMENT_ID — costing and lot/serial context captured at entry time.
Common Use Cases and Queries
The dominant operational use case is diagnosing why a transaction has not posted. Querying MMTT for failed or stalled rows is a standard Inventory support activity:
SELECT transaction_temp_id, transaction_header_id, inventory_item_id,
organization_id, transaction_quantity, transaction_uom,
transaction_date, process_flag, error_code, error_explanation
FROM mtl_material_transactions_temp
WHERE process_flag = 1
AND organization_id = :org_id;
Reconciling lot- and serial-controlled transactions requires joining the two staging children, observing the documented join rules:
SELECT t.transaction_temp_id, t.inventory_item_id, t.transaction_quantity,
l.lot_number, s.serial_number
FROM mtl_material_transactions_temp t,
mtl_transaction_lots_temp l,
mtl_serial_numbers_temp s
WHERE t.transaction_temp_id = l.transaction_temp_id
AND l.serial_transaction_temp_id = s.transaction_temp_id
AND t.organization_id = :org_id;
Reporting use cases include auditing unposted transaction backlogs by source or subinventory (supported by indexes MTL_MATERIAL_TRANS_TEMP_N1 on TRANSACTION_HEADER_ID, N10 on ORGANIZATION_ID/SUBINVENTORY_CODE/LOCATOR_ID, and N13 on PICK_SLIP_NUMBER), tracing a receiving or WIP transaction back to its origin via RCV_TRANSACTION_ID or MOVE_TRANSACTION_ID, and verifying transfer transactions, which are stored as a single MMTT record before the transaction processor splits them into paired outbound and inbound entries.
Related Objects
The following objects are the most significant dependents or references based on the documented foreign-key relationships:
- MTL_TRANSACTION_LOTS_TEMP — joins on TRANSACTION_TEMP_ID; holds lot data for lot-controlled items.
- MTL_SERIAL_NUMBERS_TEMP — joins on TRANSACTION_TEMP_ID (serial-only items) or via MTLT.SERIAL_TRANSACTION_TEMP_ID (lot-and-serial items).
- MTL_SYSTEM_ITEMS_B — referenced through INVENTORY_ITEM_ID; supplies item validation attributes.
- MTL_SECONDARY_INVENTORIES — referenced through SUBINVENTORY_CODE and TRANSFER_SUBINVENTORY.
- MTL_ITEM_LOCATIONS — referenced through LOCATOR_ID and TRANSFER_TO_LOCATION.
- RCV_TRANSACTIONS — referenced through RCV_TRANSACTION_ID for receiving-originated transactions.
- WIP_MOVE_TRANSACTIONS / WIP_DISCRETE_JOBS — referenced through MOVE_TRANSACTION_ID and TRANSACTION_SOURCE_ID for WIP activity.
- MTL_TXN_REQUEST_LINES — referenced through MOVE_ORDER_LINE_ID for move order transactions.
- MTL_RESERVATIONS — referenced through RESERVATION_ID.
- WMS_EXCEPTIONS and WMS_SKIP_TASK_EXCEPTIONS — child tables that reference MMTT via TRANSACTION_HEADER_ID.
The downstream counterpart to MMTT for successfully processed transactions is the permanent MTL_MATERIAL_TRANSACTIONS table, populated by the transaction processor; MTL_TRANSACTIONS_INTERFACE remains the documented entry point for third-party integrations that must not write to MMTT directly.
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
Temporary table for processing material transactions
-
Temporary table for processing material transactions
-
eTRM - INV Tables and Views 12.1.1
-
eTRM - INV Tables and Views 12.2.2