Search Results mrp_inventory_lots




Overview
Explore in AI Chat

Overview

The MRP_INVENTORY_LOTS table is a core transactional table within the Oracle E-Business Suite (EBS) Master Scheduling/MRP module. It functions as a detailed repository for lot-controlled inventory information specifically utilized during the Material Requirements Planning (MRP) or Master Production Schedule (MPS) compilation process. The table's primary role is to store a snapshot of lot-level inventory data, including lot numbers and their associated subinventories, which is critical for the planning engine's calculations of net available supply. This data is segregated by compile designator, allowing for distinct planning scenarios to operate on different sets of inventory lot information.

Key Information Stored

The table's structure is defined by its composite primary key and its relationships to foundational inventory tables. The key columns that uniquely identify a record are INVENTORY_ITEM_ID, ORGANIZATION_ID, COMPILE_DESIGNATOR, and INVENTORY_LOT_NUMBER. This design ensures that for a specific item, organization, and planning scenario (compile designator), each lot number is recorded uniquely. Other significant columns include LOT_SUB_INVENTORY_CODE, which specifies the subinventory where the lot is physically located for the purposes of the planning run. The table does not store quantity on-hand; it serves as a reference linking lot numbers to items and subinventories within the planning context.

Common Use Cases and Queries

The primary use case for MRP_INVENTORY_LOTS is supporting MRP/MPS runs that must consider lot-controlled inventory. Data is typically populated into this table during the planning snapshot or collection process from source tables like MTL_LOT_NUMBERS. Common queries involve analyzing the lot inventory data available to a specific planning scenario. For instance, to list all lot numbers considered for a given item and compile designator, a query would join to MRP_SYSTEM_ITEMS for item details:

  • SELECT mil.inventory_lot_number, mil.lot_sub_inventory_code, msi.segment1 item_code FROM mrp_inventory_lots mil, mrp_system_items msi WHERE mil.organization_id = msi.organization_id AND mil.inventory_item_id = msi.inventory_item_id AND mil.compile_designator = msi.compile_designator AND mil.compile_designator = '&plan_name' AND msi.segment1 = '&item';

This data is essential for generating planned orders that respect lot attributes and for reports detailing the lot-specific supply considered during planning.

Related Objects

MRP_INVENTORY_LOTS is centrally connected to several key MRP and Inventory tables, as defined by its foreign key constraints. Its primary relationship is with MTL_LOT_NUMBERS (INV), which is the master source for lot number definitions. It is also linked to MRP_SYSTEM_ITEMS, which provides the planning item snapshot, and to MRP_SUB_INVENTORIES, which holds the snapshot of subinventory data for the planning run. These relationships ensure referential integrity, guaranteeing that the lot information used in planning corresponds to valid items, organizations, lot numbers, and subinventories within the EBS instance. The table is a critical component in the data flow between Inventory (INV) and the MRP planning engine.