Search Results from_uom_code

Overview

MSD_SR_UOM_CONVERSIONS_V is an APPS-owned database view in Oracle E-Business Suite that functions as the source view for Unit of Measure (UOM) conversion information used by the Demand Planning module (product code MSD). It is part of the source-side staging layer that extracts conversion data from an Oracle Applications instance so that it can be consumed by Demand Planning for demand aggregation, forecasting, and planning calculations. In the Demand Planning architecture, MSD_SR_* source views are designed to be deployed on the source EBS instance (originally Oracle Applications 11i, and supported in 12.1.1 and 12.2.2) to expose data in a normalized form that the planning engine expects. The view's status is VALID in the ETRM documentation set for 12.2.2.

The essential purpose of this view is to present a unified list of UOM conversions that includes both item-specific and item-independent conversion rules, expressed across UOM classes. Because Demand Planning operates on items that may cross classes (for example, converting between a weight-based class and a volume-based class), the view deliberately consolidates data from the item-level conversion table and the class-level conversion table rather than relying on a single source.

Underlying Base Objects

Per the documented metadata, the view is defined over the following referenced objects:

• MTL_UOM_CONVERSIONS (SYNONYM) — item-specific and item-independent UOM conversion definitions.
• MTL_UNITS_OF_MEASURE (SYNONYM) — the UOM master, used here to identify base UOMs via BASE_UOM_FLAG = 'Y' and to derive UOM_CLASS membership.
• MTL_UOM_CLASS_CONVERSIONS (SYNONYM) — class-to-class conversion definitions, used for item-based conversions that span different UOM classes.
• MSD_SR_UTIL (PACKAGE) — a Demand Planning utility package whose ITEM function is invoked to resolve the inventory item identifier into a plannable item reference.

The view is constructed as a three-branch UNION, each DISTINCT where required, to avoid duplicate conversion rows. The first branch selects item-specific conversions from MTL_UOM_CONVERSIONS joined to MTL_UNITS_OF_MEASURE where the master UOM is a base UOM, excluding the base code itself and excluding INVENTORY_ITEM_ID = 0, and resolves the item through MSD_SR_UTIL.ITEM with a 'N' BASE_UOM_FLAG. The second branch handles item-independent conversions (INVENTORY_ITEM_ID = 0) for the same UOM class, returning SR_ITEM_PK = 0 and a NULL item. The third branch draws from MTL_UOM_CLASS_CONVERSIONS for item-based, cross-class conversions, excluding self-referencing code and class rows, setting BASE_UOM_FLAG = 'Y' and again resolving the item through MSD_SR_UTIL.ITEM.

Key Columns

• FROM_UOM_CLASS / TO_UOM_CLASS — the UOM class of the source and target units, enabling cross-class conversion logic.
• FROM_UOM_CODE / TO_UOM_CODE — the actual unit codes being converted between.
• CONVERSION_RATE — the multiplicative rate from the FROM unit to the TO unit.
• BASE_UOM_FLAG — indicates whether the row originates from an item-level (N) or class-level (Y) conversion branch.
• SR_ITEM_PK — the inventory item primary key (INVENTORY_ITEM_ID), or 0 for item-independent conversions.
• ITEM — the plannable item reference produced by MSD_SR_UTIL.ITEM, populated for item-based rows and NULL for item-independent rows.

Common Use Cases and Queries

Typical usage involves validating the conversions that will be staged for Demand Planning, auditing cross-class conversion coverage, and troubleshooting missing or duplicated conversion rates. A representative query listing item-specific conversions is:

• SELECT from_uom_code, to_uom_code, conversion_rate, sr_item_pk, item FROM apps.msd_sr_uom_conversions_v WHERE base_uom_flag = 'N';

A query to inspect item-independent and cross-class conversions would filter on base_uom_flag = 'Y' or on sr_item_pk = 0. Because the view resolves ITEM through the MSD_SR_UTIL package, queries returning the ITEM column can be more resource-intensive than those reading only the UOM columns, so filtering by UOM class or item is advisable in diagnostic scripts.