Search Results ref_um

Overview

OPI_PMI_UOMS_MST_V is a view owned by the APPS schema within the Oracle E-Business Suite, delivered as part of the OPI (Operations Intelligence) product family. Its purpose is to translate Process Manufacturing (OPM) instance units of measure into the equivalent Oracle Applications instance units of measure. Once the underlying fact data has been expressed in Applications-level UOMs, it can be normalized further to EDW Warehouse UOMs through the standard EDW_UTIL package. The view therefore occupies a bridging position in the ETRM analytics stack: it mediates between the OPM source representation of units and the canonical unit definitions used by the broader EBS reporting schema.

Within the Oracle EBS 12.1.1 and 12.2.2 releases, OPI_PMI_UOMS_MST_V is commonly consulted when users search for the column alias um_code. That alias is one of the key exposed attributes and is frequently referenced in custom extracts, OBIEE repository layers, and ETL mappings that reconcile OPM data with inventory and order management facts. It is documented as VALID, indicating that the compiled definition is syntactically consistent against its referenced objects at the time of documentation.

Underlying Base Objects

The ETRM metadata does not enumerate referenced base objects, but the view text makes the dependency explicit. The definition joins two Inventory tables:

• MTL_UNITS_OF_MEASURE UOM — the master definition of each unit of measure, supplying the unit code, description, class, and audit columns.
• MTL_UOM_CONVERSIONS UOMC — the conversion repository, joined on UNIT_OF_MEASURE with the constraint that INVENTORY_ITEM_ID equals 0.

Constraining the join to INVENTORY_ITEM_ID = 0 restricts the result to the standard (non-item-specific) UOM conversion set, which is the appropriate baseline for analytics across the enterprise. Because both sources are Inventory master tables, the view inherits the EBS multi-org-neutral unit definitions rather than any OPM-specific conversion logic.

Key Columns

The view exposes sixteen columns. The most significant for the um_code search are:

• UM_CODE — a DECODE expression over the length of UNIT_OF_MEASURE. Units whose value is one to four characters long pass through unchanged; longer values are replaced by UOM_CODE. This normalization aligns short OPM unit identifiers with their Applications equivalents.
• UM_DESC — the first forty characters of the unit description.
• UM_TYPE — the UOM class, mapping from UOM_CLASS.
• STD_FACTOR — the conversion rate, sourced from CONVERSION_RATE.
• UNIT_OF_MEASURE and UOM_CODE — the raw and coded identifiers retained for traceability back to Inventory.
• DELETE_MARK — derived via DECODE(SIGN(SYSDATE - DISABLE_DATE)), yielding 1 for disabled units and 0 otherwise.
• TEXT_CODE, RND_FACTOR, TRANS_CNT — constant zero placeholders, and REF_UM — a NULL placeholder, provided to satisfy the expected OPM master-view column contract.
• Audit columns — LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, and CREATED_BY.

Common Use Cases and Queries

Typical usage scenarios include reconciling OPM fact tables to Inventory UOMs before EDW loading, validating conversion factors used in operational reports, and populating UOM lookup dimensions in a BI repository. The following query illustrates retrieval of active unit definitions:

SELECT UM_CODE, UM_DESC, UM_TYPE, STD_FACTOR FROM APPS.OPI_PMI_UOMS_MST_V WHERE DELETE_MARK = 0 ORDER BY UM_CODE;

To inspect a single unit identified by its Applications UOM code:

SELECT UM_CODE, UOM_CODE, UNIT_OF_MEASURE, STD_FACTOR FROM APPS.OPI_PMI_UOMS_MST_V WHERE UOM_CODE = :p_uom_code;

Because the view is read-only and defined over Inventory master data, extracts should apply the DELETE_MARK filter to exclude disabled units and join to EDW structures via EDW_UTIL for warehouse-level normalization.