Search Results mtl_serial_numbers_n12

Overview

INV.MTL_SERIAL_NUMBERS is the master table that stores the definition, current whereabouts, and lifecycle status of every serial number tracked in Oracle Inventory. In Oracle EBS 12.1.1 and 12.2.2, serial numbers are not confined to Inventory alone; the same records are referenced throughout Oracle Manufacturing, Oracle Order Management, Oracle Purchasing, Oracle Assets, Enterprise Asset Management (EAM), and Warehouse Management, making this table a foundational inventory-control object. Each row represents one serial instance of an inventory item, whether it is defined but unused, residing in stores, issued out of stores, or sitting in intransit. Rows are populated primarily by the serial number generation program, which accepts a count, a starting number, and an optional prefix.

From a Data Vault modeling perspective, the metadata's heuristic classification identifies this table as a hub. That classification is appropriate: MTL_SERIAL_NUMBERS holds the durable business key of a serialized item (serial number plus item plus organization) and receives foreign-key references from a wide array of downstream operational tables. Practitioners designing a warehouse layer should therefore treat it as a hub-like entity with surrounding satellites capturing status, location, and transaction context.

Key Information Stored

The table carries 187 documented columns. The most operationally significant are the composite key columns and the current-state attributes:

• INVENTORY_ITEM_ID, SERIAL_NUMBER, CURRENT_ORGANIZATION_ID — the composite primary key defined by MTL_SERIAL_NUMBERS_PK. Every serialized item is uniquely identified by the combination of the item, the serial string, and the owning organization.
• GEN_OBJECT_ID — flagged as a unique-index business-key candidate via MTL_SERIAL_NUMBERS_U2; it links the row to the generating transaction or object.
• CURRENT_STATUS — the operational state of the serial instance (defined, in stores, issued, intransit, and related indicators).
• CURRENT_SUBINVENTORY_CODE, CURRENT_LOCATOR_ID, CURRENT_ORGANIZATION_ID — the physical storage location of the serial instance.
• REVISION, LOT_NUMBER, PARENT_SERIAL_NUMBER, END_ITEM_UNIT_NUMBER — revision control, lot association, parent/child (component) relationships, and project end-item linkage.
• LAST_TRANSACTION_ID, LAST_TXN_SOURCE_TYPE_ID, LAST_RECEIPT_ISSUE_TYPE — pointers to the most recent material transaction and its source.
• STATUS_ID, PREVIOUS_STATUS, INSPECTION_STATUS — material status controls governing availability and quality holds.
• ORIGINAL_UNIT_VENDOR_ID, VENDOR_SERIAL_NUMBER, VENDOR_LOT_NUMBER — supplier-side identifiers for purchased serialized goods.
• LPN_ID, RESERVATION_ID, FA_ASSET_ID, WIP_ENTITY_ID — license plate, reservation, fixed-asset, and work-order associations.

Note that MTL_SERIAL_NUMBERS_U1 (INVENTORY_ITEM_ID, SERIAL_NUMBER) and MTL_SERIAL_NUMBERS_U2 (GEN_OBJECT_ID) are the two documented unique indexes.

Common Use Cases and Queries

Typical reporting and operational queries revolve around locating a serial instance and tracing its movement history.

• Locate a serial number's current position: SELECT serial_number, current_organization_id, current_subinventory_code, current_locator_id, current_status FROM mtl_serial_numbers WHERE serial_number = :serial;
• List all serials in an organization/subinventory: filtered on CURRENT_ORGANIZATION_ID and CURRENT_SUBINVENTORY_CODE, supported by MTL_SERIAL_NUMBERS_N1 and _N6.
• Trace the last transaction: join LAST_TRANSACTION_ID to MTL_MATERIAL_TRANSACTIONS to obtain transaction date, type, and quantity context.
• Vendor serial reconciliation: query on ORIGINAL_UNIT_VENDOR_ID and VENDOR_SERIAL_NUMBER (indexed by MTL_SERIAL_NUMBERS_N3) to reconcile supplier shipments.
• Case-insensitive serial search: the function-based index MTL_SERIAL_NUMBERS_N12 on UPPER(SERIAL_NUMBER) supports WHERE UPPER(serial_number) = UPPER(:serial) lookups.
• LPN and reservation audit: join on LPN_ID (MTL_SERIAL_NUMBERS_N7) or RESERVATION_ID (MTL_SERIAL_NUMBERS_N13) to reconcile warehouse license plates and reservations.

Related Objects

Given its hub classification, MTL_SERIAL_NUMBERS participates in an extensive relationship network. The most significant dependents and references include:

• MTL_MATERIAL_TRANSACTIONS — joined via LAST_TRANSACTION_ID; the transaction history for each serial instance.
• MTL_UNIT_TRANSACTIONS — references MTL_SERIAL_NUMBERS.SERIAL_NUMBER, capturing discrete unit-level movements.
• MTL_RESERVATIONS — joined via RESERVATION_ID to record reservations against serialized stock.
• MTL_SYSTEM_ITEMS_B — joined via INVENTORY_ITEM_ID for item description and attribute resolution.
• WMS_LICENSE_PLATE_NUMBERS — joined via LPN_ID; also references this table's SERIAL_NUMBER.
• CSI_ITEM_INSTANCES — the installed-base record referencing SERIAL_NUMBER, central to Service and EAM semantics.
• WIP_ENTITIES — joined via ORIGINAL_WIP_ENTITY_ID for the originating work order.
• AP_SUPPLIERS — joined via ORIGINAL_UNIT_VENDOR_ID for the source supplier.
• MTL_MATERIAL_STATUSES_B — joined via STATUS_ID to resolve material status codes.
• EAM_ASSET_METERS, EAM_ASSET_STATUS_HISTORY, MTL_EAM_ASSET_ACTIVITIES — EAM tables referencing ASSET_NUMBER or SERIAL_NUMBER for asset tracking.

Collectively, these relationships confirm that MTL_SERIAL_NUMBERS functions as a central serialization hub across Inventory, Manufacturing, Purchasing, Service, and Warehouse Management in Oracle EBS.