Search Results mtl_cc_serial_numbers_pk

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:

• CYCLE_COUNT_ENTRY_ID — Foreign key to MTL_CYCLE_COUNT_ENTRIES, linking each serial record to its parent count entry.
• SERIAL_NUMBER — The serial number of the unit counted; together with CYCLE_COUNT_ENTRY_ID, forms the composite primary key and is also the leading column of the unique index MTL_CC_SERIAL_NUMBERS_U1.
• NUMBER_OF_COUNTS — Number of times the serialized unit has been counted for this entry.
• UNIT_STATUS_CURRENT — The unit status currently assigned to the serial number at count time.
• UNIT_STATUS_PRIOR — The unit status held before the count was processed, enabling status-change auditing.
• UNIT_STATUS_FIRST — The initial unit status recorded for the serial number when the count entry was created.
• POS_ADJUSTMENT_QTY — Positive quantity adjustment applied to the serialized unit as a result of the cycle count.
• NEG_ADJUSTMENT_QTY — Negative quantity adjustment applied to the serialized unit.
• APPROVAL_CONDITION — Indicator of the approval state or condition governing the serial record.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — Concurrent program context columns that identify the process which created or last updated the row.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns.

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.