Search Results dim_pk_date




Overview
Explore in AI Chat

Overview

EDW_CURRENCY_MPV is a source-side data validity view historically shipped within the Oracle Financial Intelligence (FII) product family, now classified as obsolete in Oracle E-Business Suite 12.1.1 and 12.2.2. The object belongs to the Enterprise Data Warehouse (EDW) layer of the FII/ETRM integration stack, where the suffix "MPV" conventionally denotes a materialized or validity-checked "multi-part view" used to feed dimensional loads and currency-related reference data into the warehouse. Its stated purpose within the ETRM documentation is narrowly defined: it presents records for data validity confirmation on the source side, meaning that downstream ETL processes could interrogate the view to verify currency codes and their last update timestamps before promoting records into the target warehouse.

The view is documented as "Not implemented in this database" and has no documented owner or referenced base objects in the 12.2.2 metadata. As such, it should be treated as a legacy or template-style definition rather than an actively deployed database object. In environments where FII modules remain installed, this view would be superseded by newer currency dimensions delivered through the standard FII/ETRM extraction views.

Underlying Base Objects

The only base object identified in the documented view text is FND_CURRENCIES, the Oracle Application Object Library (AOL) table that stores all enabled currency codes and associated attributes for an EBS instance. The view definition projects two columns directly from this table:

  • CURRENCY_CODE — the ISO-style currency code stored in FND_CURRENCIES.
  • LAST_UPDATE_DATE — the standard AOL audit column, used by warehouse loads for incremental (delta) processing.

No joins to additional base objects are documented. Because the metadata records "Referenced base objects: none documented" for the 12.2.2 deployment, the FND_CURRENCIES dependency likely persists only in the legacy ETRM 12.1.1 template definition. The physical view, where it existed, would have been created under an APPS or FII schema with synonym and grant conventions consistent with the rest of the EDW interface layer.

Key Columns

The ETRM column listing exposes two columns, which by naming convention correspond to the projected source columns:

  • DIM_PK — the dimensional primary key, a surrogate or natural key assigned to the currency dimension record. This is the value the warehouse loader would persist as the dimension member identifier.
  • DIM_PK_DATE — the dimensional primary key date, typically the effective or last-modified date associated with the dimension record, derived from LAST_UPDATE_DATE. This column drives effective-dated (Type 2) dimension processing and delta extraction windows.

The pairing of DIM_PK with DIM_PK_DATE reflects a standard EDW pattern in which each dimension row carries a unique key and a corresponding date stamp used for change detection and point-in-time reconstruction.

Common Use Cases and Queries

The principal scenario for EDW_CURRENCY_MPV is validation of currency reference data prior to loading a currency or exchange-rate dimension. A typical validity query checks whether the source currency codes are consistent and recently refreshed:

  • Confirming that all codes required by a fact extract exist in the source currency view.
  • Deriving the incremental extract window from DIM_PK_DATE.
  • Detecting duplicate or conflicting DIM_PK assignments in the dimensional mapping.

A representative query follows:

SELECT DIM_PK, DIM_PK_DATE FROM EDW_CURRENCY_MPV WHERE DIM_PK_DATE >= :p_last_run_date ORDER BY DIM_PK_DATE;

Since the object is obsolete and not implemented in current 12.1.1/12.2.2 databases, any reimplementation should be based on the documented FND_CURRENCIES projection, with the two EDW columns aliased from FND_CURRENCIES.CURRENCY_CODE and FND_CURRENCIES.LAST_UPDATE_DATE. Verification against the live FII data dictionary is recommended before dependency is assumed.