Results for “mrp_inventory_lots”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MRP_INVENTORY_LOTS is a Master Scheduling/MRP (MRP) product table owned by the MRP schema that stores inventory lot information used by the Oracle EBS planning engine. It is a planning-side staging and compilation table rather than a transactional inventory table; lot data originating in Oracle Inventory (MTL_LOT_NUMBERS) is copied or derived into MRP_INVENTORY_LOTS so that the MRP, MPS, and DRP planning processes can evaluate lot-level supply, expiration, and subinventory placement without repeatedly querying online transaction tables. The COMPILE_DESIGNATOR column distinguishes planning compilations, allowing the table to be repopulated per plan run.

From a dimensional modeling perspective, the mined Data Vault classification is a link. This reflects the table's role as an associative structure binding item, organization, lot number, subinventory, and project/task context. Modelers should treat it as a connection between hub-like business entities (item, organization, lot) rather than as a standalone descriptive satellite.

Key Information Stored

The physical schema documents 22 columns. The most significant are:

• INVENTORY_ITEM_ID – identifies the planned item; part of the primary key and a foreign key to MTL_LOT_NUMBERS and MRP_SYSTEM_ITEMS.
• ORGANIZATION_ID – the inventory organization owning the lot; part of the primary key.
• COMPILE_DESIGNATOR – the planning compilation identifier segregating data by plan run; part of the primary key.
• INVENTORY_LOT_NUMBER – the lot control number; part of the primary key.
• LOT_QUANTITY – on-hand or planned quantity held for the lot.
• LOT_EXPIRATION_DATE – date the lot expires, used for shelf-life and expiry netting.
• EXPIRED_LOT_QUANTITY and EXPIRED_LOT_ID – quantities and reference for lots already past expiration.
• LOT_SUB_INVENTORY_CODE – subinventory in which the lot resides; foreign key to MRP_SUB_INVENTORIES.
• PROJECT_ID and TASK_ID – project and task attribution for project-aware planning.
• PLANNING_GROUP and END_ITEM_UNIT_NUMBER – planning grouping and end-item unit context.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE – concurrent program audit columns identifying the process that populated the row.

The surrogate primary key is MRP_INVENTORY_LOTS_PK, defined on INVENTORY_ITEM_ID, ORGANIZATION_ID, COMPILE_DESIGNATOR, and INVENTORY_LOT_NUMBER. A separate unique index, MRP_INVENTORY_LOTS_U1, is a strong business-key candidate covering PROJECT_ID, TASK_ID, ORGANIZATION_ID, COMPILE_DESIGNATOR, INVENTORY_ITEM_ID, LOT_SUB_INVENTORY_CODE, and INVENTORY_LOT_NUMBER.

Common Use Cases and Queries

Typical uses include investigating lot-level supply available to a plan, validating expiration-driven demand, and reconciling planning output against on-hand lots. A representative query joining to the item master follows:

SELECT mil.organization_id,
       mil.inventory_item_id,
       mil.inventory_lot_number,
       mil.lot_quantity,
       mil.lot_expiration_date,
       mil.lot_sub_inventory_code
FROM   mrp.mrp_inventory_lots mil
WHERE  mil.compile_designator = :compile_designator
AND    mil.organization_id    = :org_id;

Planners also aggregate expired quantity by item to identify scrap candidates, and join to MRP_SUB_INVENTORIES to report lot distribution across subinventories.

Related Objects

• MTL_LOT_NUMBERS – joined on INVENTORY_ITEM_ID, ORGANIZATION_ID, and INVENTORY_LOT_NUMBER; the source of lot definitions.
• MRP_SYSTEM_ITEMS – joined on INVENTORY_ITEM_ID, ORGANIZATION_ID, and COMPILE_DESIGNATOR; provides planning item details.
• MRP_SUB_INVENTORIES – joined on LOT_SUB_INVENTORY_CODE, ORGANIZATION_ID, and COMPILE_DESIGNATOR; supplies subinventory context.
• MTL_ONHAND_QUANTITIES / MTL_MATERIAL_TRANSACTIONS – referenced to reconcile planned lot quantities against actual on-hand.
• MRP_SYSTEM_ITEMS / MRP_ITEM_SOURCING – planning companions used with lot data for supply/demand netting.