Results for “mtl_lot_numbers”

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

Overview

MTL_LOT_NUMBERS is the Inventory (INV) master table that stores lot number definitions in Oracle E-Business Suite 12.1.1 and 12.2.2. A lot represents a specific quantity of an item that shares common characteristics — such as a manufacturing batch, a supplier shipment, or a shelf-life controlled group — and is tracked separately from the item's total on-hand balance. Because lot control enables traceability, expiration management, and quality disposition, MTL_LOT_NUMBERS functions as the authoritative registry from which transactional tables derive their valid lot references. The table is owned by the INV schema, holds 150 documented columns in the 12.2.2 physical model, and carries a status of VALID.

Based on the mined foreign key structure, the table is heuristically classified as a hub in a Data Vault model. This reflects the fact that each row identifies a unique business concept — a lot of a specific item within a specific organization — and is decorated with descriptive attributes (satellite-like data) such as expiration dates, grade codes, and supplier lot references. The hub classification should be treated as a modeling suggestion derived from the referential topology, not as a formally declared property of the Oracle schema.

Key Information Stored

The primary key is defined as MTL_LOT_NUMBER_PK, composed of INVENTORY_ITEM_ID, ORGANIZATION_ID, and LOT_NUMBER. Two unique indexes reinforce business-key candidates: MTL_LOT_NUMBERS_U1 on (INVENTORY_ITEM_ID, ORGANIZATION_ID, LOT_NUMBER) and MTL_LOT_NUMBERS_U2 on GEN_OBJECT_ID. The composite key demonstrates that a lot number is uniquely meaningful only in the context of a particular item and inventory organization.

• INVENTORY_ITEM_ID — the item to which the lot belongs; foreign key to MTL_SYSTEM_ITEMS_B.
• ORGANIZATION_ID — the inventory organization owning the lot; foreign key to MTL_PARAMETERS.
• LOT_NUMBER — the user-assigned or system-generated lot identifier.
• EXPIRATION_DATE and RETEST_DATE — shelf-life and quality retest milestones used by expiration and cycle count logic.
• DISABLE_FLAG — indicates whether the lot is disabled for further transaction entry.
• STATUS_ID — material status assigned to the lot; foreign key to MTL_MATERIAL_STATUSES_B.
• SUPPLIER_LOT_NUMBER and VENDOR_NAME — supplier and vendor lot traceability data.
• GRADE_CODE, ORIGINATION_DATE, DATE_CODE — quality and origination attributes.
• PARENT_LOT_NUMBER — hierarchical relationship to a parent lot.
• AVAILABILITY_TYPE, RESERVABLE_TYPE, INVENTORY_ATP_CODE — availability and reservability controls.
• SAMPLING_EVENT_ID — foreign key to GMD_SAMPLING_EVENT_ID for process manufacturing sampling.
• GEN_OBJECT_ID — surrogate key used by the unique index U2.
• Descriptive and extensible columns include ATTRIBUTE1–15, C_ATTRIBUTE1–30, D_ATTRIBUTE1–20, N_ATTRIBUTE1–30, DESCRIPTION, and various dimensional attributes (LENGTH, WIDTH, HEIGHT, VOLUME, COLOR).

Common Use Cases and Queries

The table supports lot inquiry, traceability, expiration reporting, and validation of lot references during transactions. A typical query retrieves active, expiring lots for a given item and organization:

• SELECT lot_number, expiration_date, status_id FROM mtl_lot_numbers WHERE inventory_item_id = :item_id AND organization_id = :org_id AND NVL(disable_flag,'N') = 'N' ORDER BY expiration_date;
• Traceability: join to MTL_TRANSACTION_LOT_NUMBERS on INVENTORY_ITEM_ID, ORGANIZATION_ID, and LOT_NUMBER to reconstruct a lot's movement history.
• Expiration aging: filter on EXPIRATION_DATE between SYSDATE and SYSDATE + 30 to produce an expiring-lot report.
• Status analysis: join to MTL_MATERIAL_STATUSES_B to confirm lots are not assigned a "hold" or "reject" status.

Because MTL_LOT_NUMBERS is the parent for many transactional tables, validation queries frequently confirm that a supplied lot exists before an interface row (such as MTL_CC_ENTRIES_INTERFACE or MTL_DEMAND_INTERFACE) is processed.

Related Objects

• MTL_SYSTEM_ITEMS_B — parent item definition, joined on INVENTORY_ITEM_ID and ORGANIZATION_ID.
• MTL_PARAMETERS — organization parameters, joined on ORGANIZATION_ID.
• MTL_MATERIAL_STATUSES_B — material status codes, joined on STATUS_ID.
• GMD_SAMPLING_EVENTS — process manufacturing sampling events, joined on SAMPLING_EVENT_ID.
• MTL_ONHAND_QUANTITIES — on-hand balances per lot, keyed by INVENTORY_ITEM_ID, ORGANIZATION_ID, and LOT_NUMBER.
• MTL_TRANSACTION_LOT_NUMBERS — transaction-level lot detail referencing this table.
• MTL_SERIAL_NUMBERS — serial number registration linked to lots.
• MTL_RESERVATIONS and MTL_DEMAND — reservation and demand records tied to specific lots.
• MTL_PHYSICAL_INVENTORY_TAGS and MTL_CYCLE_COUNT_ENTRIES — physical and cycle count entries identifying lots.
• CSI_ITEM_INSTANCES and CSP_PACKLIST_SERIAL_LOTS — installed base and shipping packlist references to lots.