Search Results mtl_cc_serial_numbers_u1

Overview

INV.MTL_CC_SERIAL_NUMBERS is a transactional table in the Oracle E-Business Suite Inventory (INV) schema that stores the serial numbers enumerated against a physical cycle count entry for a serialized item. Its role is highly specific: it is populated only when the user selects the serial control option Count: Multiple Per Request on the corresponding cycle count header. When that control is not selected, serial-level detail is not captured here.

Each row associates one serial number with one CYCLE_COUNT_ENTRY_ID, and tracks how that serial number behaved across the count — whether it was observed at the count location, missed, newly found, or already counted, and whether an adjustment was approved. This makes the table the transactional record of serial-level reconciliation for a count, rather than a master definition. Records carry the standard Oracle WHO audit columns alongside count- and status-specific attributes.

Under a heuristic Data Vault classification, the table is satellite-leaning: its natural key (CYCLE_COUNT_ENTRY_ID plus SERIAL_NUMBER) points to a parent transactional entity, and the bulk of its columns are descriptive, count- and status-specific attributes that describe the state of that parent-child relationship rather than the relationship alone.

Key Information Stored

The table's business identity is the pair CYCLE_COUNT_ENTRY_ID and SERIAL_NUMBER. The unique index MTL_CC_SERIAL_NUMBERS_U1 enforces this uniqueness across CYCLE_COUNT_ENTRY_ID and SERIAL_NUMBER, making both columns the business-key candidates. The physical primary key MTL_CC_SERIAL_NUMBERS_PK is defined on the same two columns. There is no separate single-column surrogate; the surrogate role is effectively served by the composite key plus the inherited CYCLE_COUNT_ENTRY_ID foreign key.

The most significant columns are:

• CYCLE_COUNT_ENTRY_ID — foreign key to MTL_CYCLE_COUNT_ENTRIES; identifies the count entry the serial belongs to.
• SERIAL_NUMBER — the serial number being counted (VARCHAR2(30)).
• NUMBER_OF_COUNTS — how many times this serial has been counted for the current count.
• UNIT_STATUS_CURRENT — present (1) or absent (2) at the count location now.
• UNIT_STATUS_PRIOR — status at the time of the previous count.
• UNIT_STATUS_FIRST — status at the time of the first count.
• POS_ADJUSTMENT_QTY — adjustment quantity, set to 1 when a new serial number is found at the count location.
• NEG_ADJUSTMENT_QTY — negative adjustment quantity, set to 1 when a serial number is not found.
• APPROVAL_CONDITION — condition of approval for the count record.
• Standard WHO and concurrent request columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

The three status columns allow a count history to be preserved: first observation, prior observation, and current observation are all retained on a single row.

Common Use Cases and Queries

Because the table is keyed on CYCLE_COUNT_ENTRY_ID, the dominant query pattern joins it to MTL_CYCLE_COUNT_ENTRIES to report serial-level count results and adjustments for a given cycle count. Typical reporting scenarios include identifying serial numbers expected but not found (NEG_ADJUSTMENT_QTY = 1 or UNIT_STATUS_CURRENT = 2), confirming newly discovered serials (POS_ADJUSTMENT_QTY = 1), and measuring count accuracy by comparing UNIT_STATUS_FIRST, UNIT_STATUS_PRIOR, and UNIT_STATUS_CURRENT.

A representative query retrieves all serials for a specific count entry:

• SELECT serial_number, number_of_counts, unit_status_current, unit_status_prior, unit_status_first, pos_adjustment_qty, neg_adjustment_qty, approval_condition FROM inv.mtl_cc_serial_numbers WHERE cycle_count_entry_id = :entry_id ORDER BY serial_number;

Adjustment analysis uses aggregate filtering:

• SELECT cycle_count_entry_id, serial_number, pos_adjustment_qty, neg_adjustment_qty FROM inv.mtl_cc_serial_numbers WHERE pos_adjustment_qty = 1 OR neg_adjustment_qty = 1;

Applications confirm what MTL_CC_SERIAL_NUMBERS_U1 guarantees: a serial can appear only once per count entry, so repeated counts register through NUMBER_OF_COUNTS rather than additional rows.

Related Objects

The documented foreign key ties CYCLE_COUNT_ENTRY_ID to MTL_CYCLE_COUNT_ENTRIES. That parent table is the primary object this table depends on. Beyond that relationship, the following objects are the most significant for context:

• INV.MTL_CYCLE_COUNT_ENTRIES — parent entry, joined on CYCLE_COUNT_ENTRY_ID.
• INV.MTL_CYCLE_COUNT_HEADERS — cycle count header defining the Count: Multiple Per Request serial control option.
• INV.MTL_SERIAL_NUMBERS — master serial number definitions referenced by SERIAL_NUMBER.
• INV.MTL_SYSTEM_ITEMS — serialized item definitions behind each counted entry.
• INV.MTL_CYCLE_COUNT_ADJUSTMENTS — adjustment records generated from count discrepancies.
• Inventory cycle count concurrent programs that populate REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID.

These relationships permit serial-level count results to be traced from header and entry through to item and serial masters.