Search Results mtl_child_lot_numbers




Overview
Explore in AI Chat

Overview

MTL_CHILD_LOT_NUMBERS is an Oracle Inventory (INV) table that stores child lot number sequences. In Oracle EBS 12.1.1 and 12.2.2, child lot functionality allows a parent lot to be subdivided into one or more child lots, each tracking its own quantity and, in some cases, gradable attributes. This table records the sequence counters used to generate the next child lot number for a given parent lot. It is the bookkeeping anchor that ensures child lot numbers are issued sequentially and uniquely per parent lot per item per organization. The table resides in the INV schema and is registered with a status of VALID.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure is standalone, meaning the object does not participate as a hub, link, or satellite in the documented relationship graph. Its role is functional and operational rather than transactional; it maintains generation state rather than a history of lot movement.

Key Information Stored

The documented physical schema contains nine columns. The most significant are:

The surrogate primary key is MTL_CHILD_LOT_NUMBERS_PK, composed of ORGANIZATION_ID, INVENTORY_ITEM_ID, and PARENT_LOT_NUMBER. Because the same three columns form the only documented unique index, they also serve as the business-key candidate; the parent lot number combined with item and organization uniquely defines a sequence counter.

Common Use Cases and Queries

Typical scenarios include diagnosing why child lot numbering appears to skip, duplicate, or restart, and reconciling the last issued child lot number against actual child lot entries. A common query retrieves the sequence state for a specific parent lot:

  • SELECT organization_id, inventory_item_id, parent_lot_number, last_child_lot_number_seq FROM mtl_child_lot_numbers WHERE parent_lot_number = :parent_lot;
  • SELECT parent_lot_number, last_child_lot_number_seq FROM mtl_child_lot_numbers WHERE organization_id = :org AND inventory_item_id = :item ORDER BY parent_lot_number;
  • Reporting use case: joining this table to MTL_LOT_NUMBERS to compare the sequence counter with the count of child lots actually recorded.

These queries support inventory analysts and technical consultants investigating child lot generation issues and validating sequence integrity.

Related Objects

The most significant objects that reference or depend on this table through the shared columns include:

  • MTL_LOT_NUMBERS — joins on ORGANIZATION_ID, INVENTORY_ITEM_ID, and LOT_NUMBER / PARENT_LOT_NUMBER to relate stored lots to the parent sequence.
  • MTL_CHILD_LOT_NUMBERS_PK — the primary key index enforcing uniqueness.
  • MTL_SYSTEM_ITEMS_B — supplies item context via INVENTORY_ITEM_ID.
  • ORG_ORGANIZATION_DEFINITIONS — supplies organization context via ORGANIZATION_ID.
  • MTL_MATERIAL_TRANSACTIONS — child lot transactions that consume the generated numbers.
  • INV_LOT_API / INV_LOT_NUMBER_API — APIs used to generate and validate lot numbers that read this sequence.