Results for “uom_code”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BIL_DIMV_UOM is a dimensional view owned by the APPS schema in Oracle E-Business Suite, classified under the BIL – Sales Intelligence product family. It exposes a consolidated list of units of measure (UOM) used as a dimension within the Sales Intelligence (BIL) data mart. The view is documented as VALID in ETRM for release 12.1.1 and 12.2.2. Rather than presenting transactional quantities, it supplies the descriptive and classification attributes—code, class, description, disable date, and base-UOM indicator—that reporting and ETL processes attach to fact records. Its principal role is to standardize UOM metadata for dimensional joins in Sales Intelligence reporting, and it is equally suitable for integration and lookup purposes wherever an application requires the union of inventory UOM definitions and the internal BIL value-type lookup entries.

Underlying Base Objects

The view text is a UNION of two sources. The first branch selects from MTL_UNITS_OF_MEASURE_TL, the translation table for Oracle Inventory units of measure, filtered by LANGUAGE = USERENV('LANG') so that the returned description reflects the session language. The second branch selects from FND_LOOKUPS, restricted to LOOKUP_TYPE = 'BIL_VALUE_TYPE' and LOOKUP_CODE = '-999', which injects a synthetic value-type row into the dimension. ETRM documentation records no additional referenced base objects beyond these two tables. The INVENTORY UOM records supply the physical, classifiable units, while the FND_LOOKUPS branch supplies a BIL-specific pseudo-UOM entry. Both branches are combined with UNION (not UNION ALL), and the view is defined WITH READ ONLY, guaranteeing that consumers cannot perform DML through it.

Key Columns

• UNIT_OF_MEASURE — Display name of the unit of measure. In the MTL branch this is taken directly from the translation table; in the FND_LOOKUPS branch it is derived as SUBSTR(FL.MEANING, 1, 25).
• UOM_CODE — The code identifying the unit; sourced from MTL_UNITS_OF_MEASURE_TL.UOM_CODE and FND_LOOKUPS.LOOKUP_CODE respectively. This is the natural key for joining to fact tables.
• UOM_CLASS — Classification of the unit (for example, quantity, weight, or volume). The lookup branch truncates the meaning to ten characters via SUBSTR(FL.MEANING, 1, 10).
• UOM_DISABLE_DATE — Date on which the unit became inactive; sourced from DISABLE_DATE in the translation table and END_DATE_ACTIVE in FND_LOOKUPS.
• UOM_DESCRIPTION — Descriptive text for the unit, from MTL_UNITS_OF_MEASURE_TL.DESCRIPTION or FND_LOOKUPS.DESCRIPTION.
• BASE_UOM_FLAG — Indicates whether the unit is the base unit of measure. For MTL-sourced rows it is passed through from BASE_UOM_FLAG; the FND_LOOKUPS branch hard-codes the literal 'N'.
• ID — Identifier column, populated in the lookup branch by LOOKUP_CODE.
• VALUE — Value column, populated in the lookup branch by SUBSTR(FL.MEANING, 1, 25).

Common Use Cases and Queries

Typical uses include populating a UOM list of values, resolving base-unit flags, and enriching Sales Intelligence facts with readable UOM descriptions. A representative query lists active units and their base-UOM status:

• SELECT uom_code, unit_of_measure, uom_class, base_uom_flag FROM apps.bil_dimv_uom WHERE uom_disable_date IS NULL OR uom_disable_date > SYSDATE ORDER BY unit_of_measure;
• SELECT uom_code, unit_of_measure FROM apps.bil_dimv_uom WHERE base_uom_flag = 'Y'; — returns only units flagged as base units of measure, honoring the search term base_uom_flag.
• SELECT uom_code, uom_class, COUNT(*) FROM apps.bil_dimv_uom GROUP BY uom_code, uom_class; — validates uniqueness and class distribution.

Because the view is READ ONLY and joins translation and lookup data, it is safe for direct use in reports, concurrent program queries, and BI Publisher data models without risk of mutating the underlying Inventory or Application Object Library tables.