Search Results mtl_serial_numbers_pk

Overview

MTL_SERIAL_NUMBERS is the master serial number definition table in the Oracle E-Business Suite Inventory (INV) module, owned by the INV schema. In Oracle EBS 12.1.1 and 12.2.2, this table stores one record for each unique serial number instance that an organization has ever transacted, received, manufactured, or manually defined. It is the authoritative registry for serialized item tracking across receiving, inventory, work in process, order management, shipping, and enterprise asset management (EAM) flows.

Each row represents a serialized unit within a specific organization and item combination. The table drives serial number validation, on-hand visibility, genealogy tracking, and transaction processing for all serial-controlled items. Because serial numbers are generated for a wide range of supply and demand events — receipts, WIP completions, adjustments, and pre-defined ranges — MTL_SERIAL_NUMBERS typically grows to become one of the highest-volume tables in an Inventory implementation.

From a data modeling perspective, the ETRM heuristic classifies MTL_SERIAL_NUMBERS as a hub. This reflects its role as a central entity holding the durable business key (SERIAL_NUMBER with INVENTORY_ITEM_ID and CURRENT_ORGANIZATION_ID), with numerous dependent foreign keys and downstream references attaching to it rather than the reverse. The classification is a modeling suggestion derived from its foreign key topology.

Key Information Stored

The table contains 187 documented columns in the 12.2.2 schema. The most significant columns include:

• SERIAL_NUMBER — the human-readable serial identifier; part of the composite primary key.
• INVENTORY_ITEM_ID — the item the serial belongs to; part of the composite primary key and a foreign key to MTL_SYSTEM_ITEMS_B.
• CURRENT_ORGANIZATION_ID — the organization currently owning the serial; part of the composite primary key and a foreign key to MTL_PARAMETERS.
• CURRENT_STATUS — the numeric serial status governing transaction eligibility (e.g., defined, in stock, issued, shipped, resold).
• CURRENT_SUBINVENTORY_CODE and CURRENT_LOCATOR_ID — the current physical storage position, joined to MTL_SECONDARY_INVENTORIES and MTL_ITEM_LOCATIONS.
• LOT_NUMBER — the lot associated with the serial, where lot control is also enabled.
• REVISION — the item revision in effect for the serialized unit.
• ORIGINAL_WIP_ENTITY_ID — the WIP job that originally produced the serial, referencing WIP_ENTITIES.
• ORIGINAL_UNIT_VENDOR_ID and VENDOR_SERIAL_NUMBER — supplier and supplier-assigned serial for purchased units.
• PARENT_SERIAL_NUMBER and PARENT_ITEM_ID — self-referencing genealogy linkage to a parent assembly.
• LAST_TXN_SOURCE_TYPE_ID and LAST_TRANSACTION_ID — the last transaction applied, referencing MTL_TXN_SOURCE_TYPES and MTL_MATERIAL_TRANSACTIONS.
• STATUS_ID — the material status record governing the serial, from MTL_MATERIAL_STATUSES_B.
• LPN_ID — the license plate number (LPN) container if the serial is nested in WMS handling units.
• GEN_OBJECT_ID — the generated object identifier, providing a surrogate-style unique key.

Three key structures are documented: the composite primary key MTL_SERIAL_NUMBERS_PK (SERIAL_NUMBER, INVENTORY_ITEM_ID, CURRENT_ORGANIZATION_ID); the unique index MTL_SERIAL_NUMBERS_U1 on (INVENTORY_ITEM_ID, SERIAL_NUMBER); and the unique index MTL_SERIAL_NUMBERS_U2 on GEN_OBJECT_ID. The business-key candidates are therefore the natural composite of item, organization, and serial number, with GEN_OBJECT_ID serving as a technical/surrogate identifier.

Common Use Cases and Queries

Typical reporting and operational scenarios include serial genealogy, on-hand serialized inventory, and audit trails of serial movements.

Locate a specific serial in an organization:

• SELECT serial_number, inventory_item_id, current_organization_id, current_status, current_subinventory_code, current_locator_id FROM mtl_serial_numbers WHERE inventory_item_id = :item AND current_organization_id = :org AND serial_number = :serial;

List all on-hand serials for an item in a subinventory:

• SELECT serial_number, current_locator_id, revision, lot_number FROM mtl_serial_numbers WHERE current_subinventory_code = :subinv AND inventory_item_id = :item AND current_status = 3;

Trace the manufacturing origin of a serial:

• SELECT msn.serial_number, we.wip_entity_name FROM mtl_serial_numbers msn, wip_entities we WHERE msn.original_wip_entity_id = we.wip_entity_id AND msn.serial_number = :serial;

Retrieve serial genealogy (parent-child):

• SELECT child.serial_number, child.parent_serial_number FROM mtl_serial_numbers child WHERE child.parent_serial_number = :parent_serial;

Find serials received from a specific supplier:

• SELECT msn.serial_number, msn.vendor_serial_number, sup.vendor_name FROM mtl_serial_numbers msn, ap_suppliers sup WHERE msn.original_unit_vendor_id = sup.vendor_id AND sup.vendor_name = :vendor;

Reports frequently join this table to MTL_MATERIAL_TRANSACTIONS to reconstruct transaction history by serial, and to MTL_ONHAND_QUANTITIES or MTL_UNIT_TRANSACTIONS for on-hand and unit-level detail.

Related Objects

The table participates in an extensive relationship network. The most significant related objects include:

• MTL_SYSTEM_ITEMS_B and MTL_ITEM_REVISIONS_B — item and revision definitions joined on INVENTORY_ITEM_ID, CURRENT_ORGANIZATION_ID, and REVISION.
• MTL_PARAMETERS — the organization definition, joined on CURRENT_ORGANIZATION_ID (part of the primary key).
• MTL_SECONDARY_INVENTORIES and MTL_ITEM_LOCATIONS — current storage position via CURRENT_SUBINVENTORY_CODE and CURRENT_LOCATOR_ID.
• MTL_LOT_NUMBERS — lot linkage on INVENTORY_ITEM_ID, CURRENT_ORGANIZATION_ID, and LOT_NUMBER.
• MTL_MATERIAL_TRANSACTIONS and MTL_TXN_SOURCE_TYPES — last transaction context via LAST_TRANSACTION_ID and LAST_TXN_SOURCE_TYPE_ID.
• WIP_ENTITIES — original producing job via ORIGINAL_WIP_ENTITY_ID.
• CSI_ITEM_INSTANCES — downstream install base records referencing serial numbers for service and asset tracking.
• MTL_MATERIAL_STATUSES_B — material status definitions via STATUS_ID.
• AP_SUPPLIERS — original unit vendor information via ORIGINAL_UNIT_VENDOR_ID.
• MTL_SERIAL_NUMBERS (self-reference) — parent-child genealogy via PARENT_SERIAL_NUMBER and PARENT_ITEM_ID, plus downstream references from EAM tables such as EAM_ASSET_METERS and MTL_UNIT_TRANSACTIONS.