Search Results mtl_material_transaction_temp




Overview
Explore in AI Chat

Overview

WMS_MATERIAL_TXN_TRACE is a WMS (Warehouse Management) schema table within Oracle E-Business Suite 12.1.1 and 12.2.2 that functions as the history table for the temporary staging table MTL_MATERIAL_TRANSACTION_TEMP. It preserves a record of material transaction staging activity generated during WMS-driven operations. The object is documented by Oracle as being for internal WMS use only, meaning it is not exposed as a public integration interface and is not intended for direct end-user or third-party customization. Its status is VALID and it resides in the WMS schema.

The documented physical schema at 12.2.2 spans 228 columns, making it a very wide, denormalized trace table. Its primary key constraint is named WMS_MATERIAL_TXN_T_PK, keyed on the single column TRANSACTION_HEADER_ID. Mined foreign-key metadata classifies the object as standalone, and the data-vault heuristic classification is therefore also standalone. From a modeling perspective, this suggests treating TRANSACTION_HEADER_ID as a natural hub-like surrogate identity for trace rows, with the remaining attributes behaving as descriptive satellite data rather than participating in an explicit link structure. Because the relationship model did not surface a satellite chain or dependent link, standalone is the appropriate classification suggestion.

Key Information Stored

Among the 228 documented columns, the most operationally significant include TRANSACTION_HEADER_ID, which serves as the primary key and is the single documented surrogate/business identity candidate. TRANSACTION_TEMP_ID ties the trace row back to the parent temporary transaction record in MTL_MATERIAL_TRANSACTION_TEMP. Transaction classification is captured by SOURCE_CODE, TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID, and TRANSACTION_SOURCE_NAME.

Inventory and item context is captured by INVENTORY_ITEM_ID, REVISION, ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID, TRANSACTION_QUANTITY, PRIMARY_QUANTITY, and TRANSACTION_UOM. Costing and accounting attribution is provided by TRANSACTION_COST, TRANSFER_COST, TRANSPORTATION_COST, MATERIAL_ACCOUNT, COST_GROUP_ID, COST_TYPE_ID, and ORG_COST_GROUP_ID. Workflow and processing control columns such as LOCK_FLAG, PROCESS_FLAG, POSTING_FLAG, REQUIRED_FLAG, TRANSACTION_STATUS, ERROR_CODE, and ERROR_EXPLANATION support diagnostics and reprocessing. Audit and concurrency columns include CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID. LPN and task-handling context is represented by LPN_ID, TRANSFER_LPN_ID, CONTENT_LPN_ID, ALLOCATED_LPN_ID, WMS_TASK_TYPE, and WMS_TASK_STATUS.

Common Use Cases and Queries

Typical usage centers on troubleshooting WMS staging and transaction posting failures. This includes tracing an error back to a temporary transaction, reproducing transaction metadata after the temporary row has been purged, and reconciling transaction header, cost, and LPN context for a given inventory movement.

  • Trace a specific temporary transaction: SELECT * FROM wms.wms_material_txn_trace WHERE transaction_temp_id = :temp_id;
  • Reconcile errored transactions: SELECT transaction_header_id, inventory_item_id, organization_id, error_code, error_explanation FROM wms.wms_material_txn_trace WHERE error_code IS NOT NULL;
  • Audit cost and LPN context for a movement: SELECT transaction_header_id, transaction_cost, transfer_cost, lpn_id, transaction_status FROM wms.wms_material_txn_trace WHERE organization_id = :org_id AND transaction_date >= :from_date;
  • Compare trace rows against the originating staging or interface tables to validate what was processed versus what was staged.

Related Objects

The documented foreign keys establish these significant related objects:

Because Oracle classifies this table as WMS internal use only, it should be queried for diagnostic and reconciliation purposes rather than treated as a supported public interface.