Search Results uom_conv_pk

Overview

OPI_EDW_UOM_CONV_FCV is a source view belonging to the Operations Intelligence (OPI) product family, a data warehousing and analytics module that predates and largely overlaps with the later Enterprise Data Warehouse (EDW) offerings in Oracle E-Business Suite. In the 12.1.1 and 12.2.2 releases this object is classified as obsolete, and the supplied ETRM metadata explicitly records that it is Not implemented in this database. The view is therefore best understood as a legacy extraction artifact: it was designed to feed the Unit of Measure Conversion Fact table within the OPI star schema, flattening and conforming UOM conversion data from the transactional tables so that the ETL layer could load it into a dimensional fact table without further transformation.

The view presents one row per unit-of-measure conversion relationship, enriched with an instance discriminator drawn from EDW_LOCAL_INSTANCE, which allows identical source data from multiple operating units or instances to be distinguished inside the warehouse. Its output includes a surrogate-style concatenated key built from the inventory item and the UOM codes, the conversion rate, the base UOM, item concatenated segments, and auditing timestamps.

Underlying Base Objects

Although the ETRM metadata for 12.2.2 lists no documented referenced base objects, the view text identifies the following tables in its two-branch UNION ALL definition:

• MTL_UOM_CONVERSIONS — item-specific UOM conversions, joined to MTL_UNITS_OF_MEASURE on both UOM_CODE and UOM_CLASS.
• MTL_UNITS_OF_MEASURE — used twice: once as the conversion UOM (MUOM) and once as the base UOM (BUOM, filtered by BASE_UOM_FLAG = 'Y').
• MTL_UOM_CLASS_CONVERSIONS — class-level conversions used in the second UNION branch, where FROM_UOM_CODE and TO_UOM_CODE differ.
• MTL_SYSTEM_ITEMS_KFV — the key flexfield view supplying INVENTORY_ITEM_ID and CONCATENATED_SEGMENTS; outer-joined in the first branch and inner-joined in the second.
• EDW_LOCAL_INSTANCE — supplies INSTANCE_CODE for the concatenated key and instance-column output.

Note the asymmetry between branches: item-level conversions carry a base UOM flag of 'N', while class-level conversions are marked 'Y'. The class-level branch also aggregates with MAX(MSIK.CONCATENATED_SEGMENTS||'-'||MSIK.ORGANIZATION_ID) and groups by the concatenated key, which collapses multiple item-organization rows into a single representative value.

Key Columns

The first expression in each branch yields the surrogate key referenced by the search term uom_conv_pk. It is formed as inventory_item_id (or the literal 'STANDARD' when the item id is 0) concatenated with UOM_CODE and INSTANCE_CODE for item-level rows, and with FROM_UOM_CODE, TO_UOM_CODE and INSTANCE_CODE for class-level rows. Additional significant columns include INVENTORY_ITEM_ID, UOM_CODE/FROM_UOM_CODE and TO_UOM_CODE, the resolved BASE_UOM_CODE, CONVERSION_RATE, INSTANCE_CODE, CONCATENATED_SEGMENTS, LAST_UPDATE_DATE and CREATION_DATE, and the trailing flag literal distinguishing item-level from class-level conversions. Numerous placeholder TO_NUMBER(NULL) and NULL expressions reserve positions for fact-table measures that were never populated in this release.

Common Use Cases and Queries

Because the object is obsolete and not implemented, queries against it will fail on a current 12.1.1 or 12.2.2 database; the patterns below illustrate historical reconciliation use and should instead be run against MTL_UOM_CONVERSIONS and MTL_UOM_CLASS_CONVERSIONS directly.

• Auditing conversion coverage: count rows per UOM class to detect items lacking a base conversion.
• Reconciling item-level versus class-level rates by comparing CONVERSION_RATE across the two UNION branches for the same item.
• Rebuilding the surrogate key logic in a custom view when migrating historical OPI fact data forward.

Example: SELECT uom_conv_pk, inventory_item_id, uom_code, conversion_rate FROM opi_edw_uom_conv_fcv WHERE inventory_item_id = :item_id;