Search Results mtl_transaction_lot_numbers




Overview
Explore in AI Chat

Overview

The MTL_TRANSACTION_LOT_NUMBERS table is a core transactional data object within the Oracle E-Business Suite Inventory (INV) module. It functions as a child table to MTL_MATERIAL_TRANSACTIONS, providing a detailed, line-level record of lot-controlled items involved in inventory transactions. Its primary role is to store and track the specific lot numbers and their associated quantities for every material transaction that impacts lot-tracked inventory. This enables precise lot-level traceability, cost management, and compliance reporting throughout the supply chain, from receipt to issue. The table is essential for maintaining data integrity for lot-controlled items in both Oracle EBS 12.1.1 and 12.2.2.

Key Information Stored

The table's structure centers on linking a transaction line to a specific lot. The primary key is a composite of TRANSACTION_ID and LOT_NUMBER, ensuring a unique record for each lot per transaction. Key columns include TRANSACTION_ID, which foreign keys to MTL_MATERIAL_TRANSACTIONS to identify the parent transaction. The LOT_NUMBER column stores the identifier of the specific lot being transacted. Supporting columns, as indicated by the foreign key relationships, typically include INVENTORY_ITEM_ID and ORGANIZATION_ID to uniquely identify the item and its owning organization, and TRANSACTION_SOURCE_TYPE_ID to classify the transaction origin (e.g., Sales Order, Purchase Order). The table also commonly stores the primary transaction quantity applied to the specific lot (TRANSACTION_QUANTITY) and a reference to the lot's serial number detail table if applicable.

Common Use Cases and Queries

This table is central to lot history and traceability reports. A common use case is tracing the movement of a specific lot across the organization, or identifying all transactions that consumed a particular lot. For example, to find all material transactions for a given lot number, one would join this table to MTL_MATERIAL_TRANSACTIONS. Another critical use is in cost calculation and reconciliation, where lot-specific costs are applied. Sample SQL to retrieve transaction details for a lot would be:

  • SELECT mtn.lot_number, mmt.transaction_id, mmt.transaction_date, mmt.transaction_type_id, mmt.transaction_quantity FROM mtl_transaction_lot_numbers mtn, mtl_material_transactions mmt WHERE mtn.transaction_id = mmt.transaction_id AND mtn.lot_number = '<LOT_NUMBER>' AND mtn.inventory_item_id = <ITEM_ID>;

Data from this table is also vital for regulatory compliance reports demonstrating lot genealogy or expiration date tracking.

Related Objects

As per the provided metadata, MTL_TRANSACTION_LOT_NUMBERS has integral relationships with several key Inventory tables. Its primary parent is MTL_MATERIAL_TRANSACTIONS (via TRANSACTION_ID), which holds the header-level transaction data. It references MTL_LOT_NUMBERS (via INVENTORY_ITEM_ID, ORGANIZATION_ID, and LOT_NUMBER) to validate and pull lot attributes like expiration date. The link to MTL_TXN_SOURCE_TYPES (via TRANSACTION_SOURCE_TYPE_ID) provides context on the transaction's origin. For serialized lots, the table MTL_TRANSACTION_SERIAL_NUMBERS is a further child detail table. In practice, transactional data is typically created and managed via public APIs such as INV_TRANSACTION_FLOW_PUB and INV_LOT_API, rather than through direct table inserts.