Search Results mrp_inventory_lots

Overview

The MTL_LOT_NUMBERS table is a core master data table within the Oracle E-Business Suite Inventory (INV) module. It serves as the central repository for defining and storing all lot-controlled inventory items within an organization. A lot number is a unique identifier assigned to a specific batch of material produced or received together, enabling traceability and quality control. This table is fundamental to the lot management functionality, linking a specific lot number to an inventory item and organization, and storing critical control attributes that govern the lot's lifecycle and usage throughout the supply chain, manufacturing, and order management processes.

Key Information Stored

The table's structure is defined by a primary key that uniquely identifies a lot: INVENTORY_ITEM_ID, ORGANIZATION_ID, and LOT_NUMBER. Beyond this key, the table stores vital descriptive and control attributes. Key columns include STATUS_ID, which links to MTL_MATERIAL_STATUSES_B to enforce hold or approval statuses on the lot. Other significant columns typically include fields for expiration dates, grade, origination dates, and parent lot references, although the specific column list is not detailed in the provided metadata. The table's integrity is maintained through foreign key relationships to MTL_SYSTEM_ITEMS_B (defining the item), MTL_PARAMETERS (defining the organization), and MTL_MATERIAL_STATUSES_B (defining the status).

Common Use Cases and Queries

This table is central to any operation involving lot-tracked material. Common use cases include validating lot numbers during transactions, reporting on lot inventory quantities and statuses, and enforcing lot expiration in picking rules. A fundamental query retrieves lot details for a specific item:

• SELECT lot_number, status_id, expiration_date FROM inv.mtl_lot_numbers WHERE inventory_item_id = 12345 AND organization_id = 101;

Another critical pattern involves joining to MTL_ONHAND_QUANTITIES to report on-hand balances by lot:

• SELECT moq.lot_number, moq.subinventory_code, SUM(moq.primary_transaction_quantity) FROM inv.mtl_onhand_quantities moq JOIN inv.mtl_lot_numbers mln ON moq.inventory_item_id = mln.inventory_item_id AND moq.organization_id = mln.organization_id AND moq.lot_number = mln.lot_number WHERE moq.inventory_item_id = 12345 GROUP BY moq.lot_number, moq.subinventory_code;

Lot status validation is also a frequent requirement for transaction interfaces and custom logic.

Related Objects

As indicated by the extensive foreign key metadata, MTL_LOT_NUMBERS is referenced by numerous transactional and interface tables across EBS modules. Key related objects include MTL_ONHAND_QUANTITIES (storing lot-specific stock), MTL_TRANSACTION_LOT_NUMBERS (recording lot movements), and MTL_MATERIAL_TRANSACTIONS (the core transaction history). The provided data shows foreign keys from execution systems like AHL_WORKORDER_MTL_TXNS (Service), planning systems like MRP_INVENTORY_LOTS, cycle counting tables (MTL_CYCLE_COUNT_ENTRIES), demand interfaces (MTL_DEMAND_INTERFACE), and Installed Base (CSI_ITEM_INSTANCES). This wide integration underscores the table's role as a master data source for lot information enterprise-wide.