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:

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:

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.