Search Results mtl_consumption_txn_temp

Overview

MTL_CONSUMPTION_TEMP_TXN_TEMP — more precisely INV.MTL_CONSUMPTION_TXN_TEMP — is an Inventory (INV) module staging table that holds consumption transaction records prior to validation and permanent posting to the transaction tables. It serves as a transient work area in which the Inventory transaction manager (or a consuming process such as Work in Process, Purchasing, or an external feeder) assembles material consumption, tax, and cost detail before the records are committed to the permanent transaction schema. In Oracle EBS 12.1.1 and 12.2.2 the object resides in the INV schema and is reported as VALID, with a documented physical schema of 31 columns.

From a Data Vault modeling perspective, the metadata's heuristic classification places this object as a standalone structure rather than a hub, link, or satellite. This classification is best read as a modeling suggestion: the table is an integration or staging construct whose natural relationships are resolved elsewhere in the permanent transaction model, and its single documented foreign key (to PO_APPROVED_SUPPLIER_LIST) is the only referential anchor identified by the mining process.

Key Information Stored

The table is organized around the identification of the material being consumed and the quantity and cost attributes of that consumption. The columns most relevant to functional and reporting use are:

• INVENTORY_ITEM_ID — inventory item consumed; a primary business-key candidate alongside organization and subinventory.
• ORGANIZATION_ID — inventory organization in which the consumption occurs.
• SUBINVENTORY_CODE and LOCATOR_ID — the subinventory and locator from which material is issued.
• BATCH_ID — lot or batch reference for lot-controlled items.
• TRANSACTION_QUANTITY and NET_QTY — the gross and net consumed quantities; SECONDARY_TRANSACTION_QUANTITY and SECONDARY_NET_QTY carry the corresponding secondary-unit-of-measure values.
• TRANSACTION_COST — the cost associated with the consumption.
• TRANSACTION_DATE — effective date of the transaction.
• TRANSACTION_ID and TRANSACTION_SOURCE_ID — the unique transaction identifier and the source that originated it.
• OWNING_ORGANIZATION_ID and PO_LINE_ID — owning organization and the purchasing document line, supporting receipt-to-consumption traceability.
• ASL_ID — the approved supplier list reference; this is the documented foreign key pointing to PO_APPROVED_SUPPLIER_LIST.
• Tax and accounting columns including TAX_CODE_ID, TAX_RATE, RECOVERABLE_TAX, NON_RECOVERABLE_TAX, CHARGE_ACCOUNT_ID, VARIANCE_ACCOUNT_ID, and ACCRUAL_ACCOUNT_ID, which carry the tax treatment and account derivation for the pending transaction.

The metadata does not document a single-column surrogate primary key distinctly from the business keys; in practice the transaction identity is carried by TRANSACTION_ID in combination with the item and organization columns.

Common Use Cases and Queries

The primary use case is diagnostic: when consumption transactions fail validation or fail to post, this table is queried to inspect the pending rows and the specific conditions that caused the failure. Typical reporting includes reconciliation of consumption quantities against the permanent transaction table, review of tax and cost derivation, and auditing of supplier-linked consumption.

A representative query pattern joins the temporary rows to inventory master data:

SELECT t.transaction_id,
       t.inventory_item_id,
       t.organization_id,
       t.subinventory_code,
       t.transaction_quantity,
       t.net_qty,
       t.transaction_cost,
       t.transaction_date
FROM   mtl_consumption_txn_temp t
WHERE  t.organization_id = :org_id
AND    t.transaction_date >= :from_date;

To trace supplier-linked consumption, join ASL_ID to PO_APPROVED_SUPPLIER_LIST. Aggregate queries are commonly used to compare summed NET_QTY in the temporary table against MTL_MATERIAL_TRANSACTIONS for the same period, providing a variance check before purge.

Related Objects

• PO_APPROVED_SUPPLIER_LIST — referenced through the documented foreign key ASL_ID.
• MTL_MATERIAL_TRANSACTIONS — the permanent transaction table into which validated consumption rows are ultimately posted; joined on TRANSACTION_ID.
• MTL_TRANSACTION_TEMP — the parallel staging table for the more general transaction interface, sharing quantity and source columns.
• MTL_SYSTEM_ITEMS_B — joined on INVENTORY_ITEM_ID and ORGANIZATION_ID for item description and unit of measure.
• MTL_ITEM_LOCATIONS — joined on LOCATOR_ID for locator detail.
• MTL_LOT_NUMBERS — joined on BATCH_ID for lot-controlled items.
• ORG_ORGANIZATION_DEFINITIONS — for organization name resolution on ORGANIZATION_ID.
• PO_LINES_ALL — joined on PO_LINE_ID for purchase order line context.

Because the object is a staging table, its rows are typically short-lived and are cleared by the consuming process; integrations should read it rather than write to it directly.