Search Results mtl_material_transactions_temp

Overview

The MTL_MATERIAL_TRANSACTIONS_TEMP table is a core transactional staging table within the Oracle E-Business Suite Inventory (INV) module. It serves as a temporary holding area for material transaction data before it is validated, processed, and posted to the permanent history table, MTL_MATERIAL_TRANSACTIONS. This design is fundamental to the transaction processing engine in both EBS 12.1.1 and 12.2.2, ensuring data integrity by isolating in-flight transactions. The table acts as an intermediary for various transaction sources, including inventory transfers, work-in-process (WIP) issues, receiving transactions, and move order processing, before they are finalized and accounted for.

Key Information Stored

The table's primary key is TRANSACTION_HEADER_ID, which uniquely identifies each staged transaction batch. Its structure mirrors the permanent transactions table, containing columns that define the transaction's core attributes. Key columns, as indicated by the foreign key relationships, include INVENTORY_ITEM_ID and ORGANIZATION_ID (linking to MTL_SYSTEM_ITEMS_B), TRANSFER_SUBINVENTORY and TRANSFER_ORGANIZATION (linking to MTL_SECONDARY_INVENTORIES), and DISTRIBUTION_ACCOUNT_ID (linking to GL_CODE_COMBINATIONS). Other critical fields capture source details, such as RCV_TRANSACTION_ID for receiving, MOVE_TRANSACTION_ID for WIP, MOVE_ORDER_LINE_ID for move orders, and RESERVATION_ID for inventory reservations. The ACCT_PERIOD_ID links to the accounting period for the transaction.

Common Use Cases and Queries

The primary use case is the investigation of pending or errored transactions within the transaction processing interface. Database administrators and functional consultants often query this table to diagnose why a transaction has not posted to inventory or to identify data validation failures. A common diagnostic query is to find pending transactions for a specific item or organization:

• SELECT transaction_header_id, inventory_item_id, transaction_quantity, transaction_uom, transaction_source_id, transaction_source_name FROM inv.mtl_material_transactions_temp WHERE organization_id = :org_id AND inventory_item_id = :item_id AND process_flag != 2;

Another critical pattern is joining with the permanent table to find transactions that have been processed but may have left orphaned temporary records, or to trace the lifecycle of a specific transaction batch from staging to final posting.

Related Objects

MTL_MATERIAL_TRANSACTIONS_TEMP has extensive dependencies, as shown by its foreign keys. It is directly related to the permanent history table, MTL_MATERIAL_TRANSACTIONS. Key referenced tables include MTL_SYSTEM_ITEMS_B (for items), MTL_SECONDARY_INVENTORIES (for subinventories), ORG_ACCT_PERIODS (for periods), and GL_CODE_COMBINATIONS (for accounts). It integrates with other EBS modules via links to WIP_DISCRETE_JOBS, WIP_FLOW_SCHEDULES, RCV_TRANSACTIONS, MTL_TXN_REQUEST_LINES (move orders), and MTL_RESERVATIONS. Data is typically inserted into this table by various public APIs (e.g., INV_TRANSACTION_PUB) and is processed by concurrent programs like the Transaction Manager and Transaction Worker.