Search Results msc_uom_conversions_view




Overview
Explore in AI Chat

Overview

MSC_UOM_CONVERSIONS_VIEW is a read-only database view owned by the APPS schema within the MSC (Advanced Supply Chain Planning) product family of Oracle E-Business Suite 12.1.1 and 12.2.2. It resolves unit-of-measure (UOM) conversion factors for inventory items as they are represented inside the MSC planning engine. Whereas the underlying transactional and setup tables in Oracle Inventory store conversions through multi-layered hierarchies of item-specific, class-level, and standard conversions, this view flattens that complexity into a single denormalised result set suitable for direct querying by planners, report developers, and integration interfaces.

The view is a planning-side construct; it operates against the MSC_% synonyms that point to the mirrored planning tables, not against the base INV tables. This distinction matters, because the row population is governed by the plan context (PLAN_ID = -1 identifies the master, non-plan-specific data set), so the view reflects the working data set that the supply chain planning engine uses during a plan run. It is primarily consumed by planning reports, custom diagnostics, and conversion-validation scripts.

Underlying Base Objects

The view is defined over four documented base objects, all exposed as synonyms in the APPS schema:

  • MSC_SYSTEM_ITEMS — supplies the inventory item, organisation, primary UOM code, and SR instance context. It is the driving table and is restricted to PLAN_ID = -1.
  • MSC_UOM_CONVERSIONS — appears three times in the view text (aliased CONV1, CONV2, and CONV1_2) to supply item-specific and seeded-class conversion rates together with their UOM codes and DISABLE_DATE columns.
  • MSC_UNITS_OF_MEASURE — referenced twice (UOM and UOM2) to obtain UOM classes and unit-of-measure codes for both the from- and to-side of each conversion.
  • MSC_UOM_CLASS_CONVERSIONS — provides class-to-class conversion rates and target UOM classes, including item-level overrides.

The joins are complex and use outer joins (marked with the (+) operator) for the conversion tables, with DECODE and LEAST/NVL logic applied so that expired conversions (DISABLE_DATE in the past) are excluded and a single effective rate is returned. A correlated subquery against MSC_UOM_CLASS_CONVERSIONS further selects the applicable class row for the item.

Key Columns

  • INVENTORY_ITEM_ID — surrogate identifier of the item whose conversion is resolved.
  • ORGANIZATION_ID — organisation context of the item.
  • UOM_CODE — the item's primary or base unit of measure.
  • SR_INSTANCE_ID — source instance identifier used to distinguish data originating from different EBS instances in a multi-instance planning configuration.
  • UOM_CLASS / CONV2.UOM_CLASS — the UOM class of the from- and to-side units; the view returns a factor of 1 where both units fall in the same class.
  • UNIT_OF_MEASURE — the target unit of measure for which the conversion factor applies.

The computed conversion factor is not given a named column alias in the view text; it is derived as the product of the class conversion rate and the class-specific rate divided by the item- or seeded-level conversion rate, with DISABLE_DATE checks ensuring only the currently active rate is used.

Common Use Cases and Queries

Typical scenarios include validating that every planned item has a resolvable conversion between its base UOM and each target UOM, diagnosing planning discrepancies caused by stale or missing conversion data, and feeding conversion factors into custom planning extracts.

  • Identify items lacking a valid conversion: SELECT inventory_item_id, uom_code, unit_of_measure FROM msc_uom_conversions_view WHERE organization_id = :org;
  • Filter by UOM class to audit class-level behaviour: SELECT inventory_item_id, uom_class, unit_of_measure FROM msc_uom_conversions_view WHERE sr_instance_id = :sr;
  • Reconcile planning conversions against INV_CONVERSIONS to surface mismatches before a plan run.

Because the underlying rate is unaliased, most custom queries should select the named columns only; where the factor is required, the view is best wrapped or replicated with an explicit alias. All queries should be executed against the APPS schema with appropriate MSC read privileges.