Search Results uom_conv_pk_key

Overview

EDW_MTL_UOM_CONV_F_IV is a reporting and integration view within the Oracle E-Business Suite Inventory (INV) module, exposed through the Enterprise Data Warehouse (EDW) layer. The "_IV" suffix denotes an interface view — a purpose-built abstraction designed to flatten and rename operational columns into the conventions expected by downstream ETL processes, data marts, and cross-module extracts. In this case, the view presents unit of measure (UOM) conversion data, the relationships that define how a quantity expressed in one unit translates into another for a given inventory item.

The view carries no additional filtering or transformation logic beyond column projection; it selects all rows directly from its parent object and appends a literal OPERATION_CODE column populated with a single space. In Oracle EBS 12.1.1 and 12.2.2, this pattern is typical of EDW interface views, which serve primarily as stable, contract-bound surfaces for external consumers while the underlying transactional tables evolve. Notably, the ETRM metadata records the object as "Not implemented in this database," indicating the view is defined at the ETRM/documentation level but may not be deployed in every environment. Its documented owner and referenced base objects are therefore limited.

Underlying Base Objects

The view is defined as a straightforward SELECT over EDW_MTL_UOM_CONV_F, a foundation table (denoted by the "_F" suffix) in the EDW naming convention. EDW_MTL_UOM_CONV_F is the physical or derived store over which the interface view is layered. The view text references only this single object; the ETRM metadata documents no other referenced base objects.

EDW_MTL_UOM_CONV_F itself is understood to consolidate data from the Inventory UOM conversion tables (MTL_UOM_CONVERSIONS and related item-level conversion structures), but the documented metadata does not list those OLTP dependencies. From a consumer's perspective, the interface view is the authoritative surface: it presents the conversion records with EDW surrogate and foreign keys already populated, insulating the caller from the operational schema.

Key Columns

The view exposes a mix of surrogate keys, business identifiers, descriptive attributes, and audit fields.

• ROW_ID — The ROWID of the underlying EDW_MTL_UOM_CONV_F row, preserved for direct addressing.
• UOM_CONV_PK / UOM_CONV_PK_KEY — The source primary key of the conversion record and its EDW surrogate equivalent.
• INVENTORY_ITEM_ID — The item for which the conversion applies; joins to the item master.
• UOM / BASE_UOM — The conversion's from-unit and the base unit of measure.
• CONVERSION_RATE — The numeric multiplier translating UOM into BASE_UOM.
• INSTANCE_FK_KEY — The surrogate foreign key identifying the source EBS instance (environment) from which the row originated. This is the column the user was searching for.
• UOM_FK_KEY / BASE_UOM_FK_KEY — Surrogate foreign keys resolving to the UOM dimension for the from-unit and base unit respectively.
• USER_ATTRIBUTE1–15 and USER_MEASURE1–5 — Pertaining to standard EBS descriptive flexfield and attribute columns carried through for completeness.
• LAST_UPDATE_DATE / CREATION_DATE — Audit timestamps used for incremental ETL watermarking.
• OPERATION_CODE — A literal space, provided as a placeholder for change-data-capture staging.

Common Use Cases and Queries

This view is principally consumed by ETL routines and analytical queries that require item-level UOM conversion factors, particularly where surrogate keys and multi-instance provenance are needed. Typical scenarios include populating a UOM dimension in a warehouse, reconciling conversions across instances, and supporting quantity normalization in inventory and order reporting.

To isolate records by source instance:

SELECT INVENTORY_ITEM_ID, UOM, BASE_UOM, CONVERSION_RATE
FROM   EDW_MTL_UOM_CONV_F_IV
WHERE  INSTANCE_FK_KEY = :instance_key;

To pull only recently changed conversions for incremental loads:

SELECT UOM_CONV_PK, INVENTORY_ITEM_ID, UOM, BASE_UOM,
       CONVERSION_RATE, LAST_UPDATE_DATE, INSTANCE_FK_KEY
FROM   EDW_MTL_UOM_CONV_F_IV
WHERE  LAST_UPDATE_DATE >= :last_extract_date;

Because the view performs no filtering, restrictive predicates such as INSTANCE_FK_KEY and LAST_UPDATE_DATE should always be applied by the consumer to avoid full scans of EDW_MTL_UOM_CONV_F.