Search Results mtl_cc_serial_numbers




Overview
Explore in AI Chat

Overview

MTL_CC_SERIAL_NUMBERS is an Inventory (INV) module table that stores the serial numbers associated with cycle count entries in Oracle E-Business Suite 12.1.1 and 12.2.2. During physical inventory or cycle counting, when a counted item is serial-controlled, each serialized unit must be individually identified and matched to the cycle count entry that recorded it. This table captures that relationship, recording which serial numbers were observed, counted, or adjusted during the count, along with the unit status information and adjustment quantities applied to each serialized unit.

From a heuristic Data Vault modeling perspective, the table is satellite-leaning. Its grain is defined by the combination of SERIAL_NUMBER and CYCLE_COUNT_ENTRY_ID, which together form the primary key MTL_CC_SERIAL_NUMBERS_PK. This structure positions the table as a descriptive child of the cycle count entry transaction, carrying the count-time attributes and status transitions rather than acting as an independent hub or a pure link between two business entities.

Key Information Stored

The table contains 18 documented columns. The most significant are:

The surrogate primary key is MTL_CC_SERIAL_NUMBERS_PK over (SERIAL_NUMBER, CYCLE_COUNT_ENTRY_ID). The unique index MTL_CC_SERIAL_NUMBERS_U1 over (CYCLE_COUNT_ENTRY_ID, SERIAL_NUMBER) is functionally equivalent and serves as the primary business-key candidate, guaranteeing that a serial number appears only once per cycle count entry.

Common Use Cases and Queries

Typical reporting scenarios include reconciling counted serial numbers against expected on-hand serials, auditing adjustment quantities generated by a count, and tracing unit status changes across the count lifecycle.

Retrieving all serials for a given cycle count entry:

SELECT serial_number, number_of_counts,
       unit_status_current, pos_adjustment_qty, neg_adjustment_qty
FROM   mtl_cc_serial_numbers
WHERE  cycle_count_entry_id = :entry_id;

Finding serials where adjustments were generated by a count:

SELECT e.cycle_count_entry_id, s.serial_number,
       s.pos_adjustment_qty, s.neg_adjustment_qty
FROM   mtl_cc_serial_numbers s,
       mtl_cycle_count_entries e
WHERE  s.cycle_count_entry_id = e.cycle_count_entry_id
AND   (s.pos_adjustment_qty > 0 OR s.neg_adjustment_qty > 0);

Comparing prior and current unit status to identify reclassified serials:

SELECT serial_number, unit_status_prior, unit_status_current
FROM   mtl_cc_serial_numbers
WHERE  unit_status_prior <> unit_status_current;

Related Objects

  • MTL_CYCLE_COUNT_ENTRIES — Parent table referenced via MTL_CC_SERIAL_NUMBERS.CYCLE_COUNT_ENTRY_ID. This is the primary join for nearly all queries.
  • MTL_CYCLE_COUNT_HEADERS — Header-level count definition, joined through the entries table for schedule-level reporting.
  • MTL_SERIAL_NUMBERS — Master serial number table, joinable on SERIAL_NUMBER for item and lot context.
  • MTL_ONHAND_QUANTITIES_DETAIL — Current on-hand supply for reconciling counted serials.
  • MTL_UNIT_STATUSES / MTL_UNIT_STATUS_TYPES — Lookups resolving UNIT_STATUS_CURRENT, UNIT_STATUS_PRIOR, and UNIT_STATUS_FIRST codes.
  • INV_CYCLE_COUNT_ENTRY_PUB / INV_CYCLE_COUNT_PUB — Public APIs that drive cycle count entry and adjustment processing, indirectly writing these serial records.
  • MTL_MATERIAL_TRANSACTIONS — Transaction records generated after count approval, useful for tracing the adjustment resulting from serial count entries.