Search Results uom_dp

Overview

EDW_MTL_UOMD_UOM_LCV is a view owned by the APPS schema in Oracle EBS 12.1.1 and 12.2.2. It belongs to the OPI (Operations Intelligence) product family and serves as the source view for the Unit of Measure (UOM) level of the UOM dimension within the Oracle Business Intelligence / Daily Business Intelligence data warehouse layer. In dimensional modeling terms, this view supplies the leaf-level rows that populate the UOM dimension table used by Operations Intelligence reporting and analytics.

The view presents a denormalized, reporting-ready projection of unit of measure definitions as stored in the transactional MTL_UNITS_OF_MEASURE table, joined against the EDW_LOCAL_INSTANCE table to stamp each row with the local instance code. Its principal purpose is to expose UOM data in a consistent, surrogate-keyed format suitable for ETL consumption into the EDW staging and dimension tables, rather than for direct transactional querying.

Underlying Base Objects

Per the documented view text, the view is defined over two base objects:

• MTL_UNITS_OF_MEASURE (alias UOM) — the master table holding all unit of measure definitions, including UOM class, code, name, base flag, description, and disable date.
• EDW_LOCAL_INSTANCE (alias INST) — the Operations Intelligence instance table, providing INSTANCE_CODE to identify the source application instance.

The view text performs a cross join (no join condition) between these two objects, meaning every UOM row is combined with the instance row(s) present in EDW_LOCAL_INSTANCE. In ETRM 12.2.2 metadata, no base objects are separately documented against the view record, but the embedded view text confirms the two tables above. The view does not itself persist data; it is a virtual, derived object dependent on MTL_UNITS_OF_MEASURE and EDW_LOCAL_INSTANCE for all its content.

Key Columns

The view exposes the following columns, several of which carry the BASE_UNIT_FLAG the user searched for:

• UOM_PK — a composite key formed by concatenating UOM_CODE and INSTANCE_CODE (UOM.UOM_CODE||'-'||INST.INSTANCE_CODE), uniquely identifying a UOM within an instance.
• INSTANCE_CODE — the source instance identifier from EDW_LOCAL_INSTANCE.
• UOM_CLASS — the UOM class to which the unit belongs (e.g., Quantity, Weight, Volume).
• UNIT_MEASURE_NAME / UOM_CODE — the unit of measure name and its code.
• BASE_UNIT_FLAG — indicates whether the UOM is the base unit for its class (mapped from MTL_UNITS_OF_MEASURE.BASE_UOM_FLAG). This is the attribute the user searched for; a value of 'Y' identifies the base unit, 'N' otherwise.
• DESCRIPTION / END_EFFECT_DATE — free-text description and the disable date (from DISABLE_DATE).
• UOM_DP / NAME — literal constants ('UNIT_OF_MEASURE') used to designate the dimension and level name in the EDW model.
• CREATION_DATE / LAST_UPDATE_DATE — audit columns inherited from MTL_UNITS_OF_MEASURE.
• USER_ATTRIBUTE1 ... USER_ATTRIBUTE5 — placeholder attribute columns, returned as NULL in this view's definition, reserved for descriptive flexfield or extensibility use.

Common Use Cases and Queries

This view is typically consumed by Operations Intelligence ETL processes to load the UOM dimension. Reporting queries often filter on BASE_UNIT_FLAG to isolate base units or to build conversion-aware aggregations. A representative query identifies base units for a given instance:

• SELECT UOM_PK, UOM_CODE, UNIT_MEASURE_NAME, UOM_CLASS, BASE_UNIT_FLAG FROM APPS.EDW_MTL_UOMD_UOM_LCV WHERE BASE_UNIT_FLAG = 'Y' AND INSTANCE_CODE = :p_instance;
• SELECT UOM_CLASS, COUNT(*) FROM APPS.EDW_MTL_UOMD_UOM_LCV GROUP BY UOM_CLASS;
• SELECT UOM_PK, DESCRIPTION, END_EFFECT_DATE FROM APPS.EDW_MTL_UOMD_UOM_LCV WHERE END_EFFECT_DATE IS NULL;

These queries support dimension validation, base-unit verification, and active UOM filtering. Because the view is an EDW construct, it should be queried in read-only reporting contexts rather than transactional ones.