Search Results msc_units_of_measure_u2

Overview

MSC.MSC_UNITS_OF_MEASURE is the units-of-measure (UOM) definition table within the MSC schema, the schema owned by Oracle Supply Chain Planning (Advanced Supply Chain Planning and related planning products). The table serves as the master reference for both the 25-character descriptive UOM name and the abbreviated 3-character UOM code used throughout planning data. It holds a single defining row per unit of measure, making it the canonical lookup for UOM identity, class assignment, base-unit designation, and the date beyond which a unit may no longer be used to define conversions.

The table resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10, and its indexes are held in APPS_TS_TX_IDX, consistent with standard Oracle Applications transactional data placement. All 33 documented columns carry the standard Who, concurrent program, and DFF descriptor columns typical of an EBS transactional entity.

From a dimensional modeling perspective, the metadata's heuristic Data Vault classification is hub-leaning. This reflects the role of the table as a durable, source-keyed list of UOM business keys. In a Data Vault design, UNIT_OF_MEASURE or UOM_CODE would naturally be modeled as the hub key, with the surrounding descriptive and audit attributes loaded as satellite content. The classification is a modeling suggestion derived from the foreign key topology, not a physical property of the delivered table.

Key Information Stored

The primary key is MSC_UNITS_OF_MEASURE_PK on UNIT_OF_MEASURE, and the table is therefore unique by unit-of-measure name. Two additional unique indexes act as business-key candidates:

• UNIT_OF_MEASURE (VARCHAR2(25)) — the human-readable unit-of-measure name and the primary key column; unique via MSC_UNITS_OF_MEASURE_U1.
• UOM_CODE — the abbreviated unit-of-measure code; unique via MSC_UNITS_OF_MEASURE_U2, the index name matching the user's search term.
• UOM_CLASS (VARCHAR2(10)) — the class to which the unit belongs, used to scope conversion rules.
• BASE_UOM_FLAG — indicates whether the row represents a base unit; also the leading column of non-unique index MSC_UNITS_OF_MEASURE_N1.
• DISABLE_DATE — the date after which the unit can no longer be used to define conversions, providing soft-deactivation semantics.
• DESCRIPTION (VARCHAR2(50)) — descriptive text for the unit.
• SR_INSTANCE_ID — source application instance identifier, supporting multi-instance and source-system lineage.
• REFRESH_NUMBER — planning refresh sequence identifier.
• Standard Who columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.
• Concurrent Who columns: REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.
• ATTRIBUTE_CATEGORY (VARCHAR2(30)) and ATTRIBUTE1 through ATTRIBUTE15 (VARCHAR2(150)) — descriptive flexfield structure and segments.

Common Use Cases and Queries

This table is most commonly joined during planning extracts and conversion validation. A typical lookup resolves a UOM code to its descriptive name and class:

• UOM validation and enrichment — join planning facts to this table on UNIT_OF_MEASURE to obtain UOM_CODE, UOM_CLASS, and DESCRIPTION for reporting.
• Active-unit filtering — filter on DISABLE_DATE IS NULL or DISABLE_DATE > SYSDATE to restrict to currently usable units.
• Base-unit reporting — use the MSC_UNITS_OF_MEASURE_N1 index by constraining BASE_UOM_FLAG to identify base units within a UOM class.
• Conversion integrity checks — verify that every UNIT_OF_MEASURE referenced in MSC_UOM_CONVERSIONS exists in this definition table.
• Instance reconciliation — group by SR_INSTANCE_ID and REFRESH_NUMBER to audit what was loaded per planning refresh cycle.

A representative pattern:

• SELECT m.unit_of_measure, m.uom_code, m.uom_class, m.description, m.base_uom_flag FROM msc.msc_units_of_measure m WHERE m.disable_date IS NULL ORDER BY m.uom_class, m.unit_of_measure;

Related Objects

The documented foreign keys establish the strongest dependencies:

• MSC_UOM_CONVERSIONS — references MSC_UNITS_OF_MEASURE.UNIT_OF_MEASURE, defining per-unit conversion factors.
• MSC_UOM_CLASS_CONVERSIONS — references this table twice, via FROM_UNIT_OF_MEASURE and TO_UNIT_OF_MEASURE, defining class-level conversion pairs.

Because these conversions depend on the definition rows, disabling a unit via DISABLE_DATE must be coordinated with outstanding conversion records. Reporting and integration layers should treat MSC_UNITS_OF_MEASURE as the authoritative UOM hub against which all conversion and planning references are validated.