Search Results fk_physical_name




Overview
Explore in AI Chat

Overview

The view APPS.EDW_FACT_FLEX_FK_MAPS_V is a dictionary-style object owned by the APPS schema and registered under the BIS (Applications BIS / Business Intelligence System) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes metadata that maps key flexfield foreign-key assignments to the fact tables used by the Oracle E-Business Suite data warehouse and ETRM (Enterprise Territory and Resource Management) reporting layers. In practical terms, the view answers the question: "for a given fact, which physical foreign-key column carries the key flexfield value for a given dimension, and is that mapping currently enabled?"

Its role is configuration discovery. The Oracle Daily Business Intelligence and related BIS/ETRM warehouse components resolve segment-level aggregation by consulting mapping metadata at runtime. Analysts, data modelers, and integration developers query this view to confirm that a fact's foreign-key column aligns with the intended dimension before writing extraction, drill-down, or ETL logic.

Underlying Base Objects

The documented ETRM metadata for this object records no referenced base objects; the view text itself is a straightforward projection:

  • The view is defined as a simple SELECT over a single underlying mapping table (not documented in the ETRM excerpt) exposing nine columns verbatim.
  • It carries no joins, filters, or aggregation in its documented definition — every column is passed through unchanged.
  • Because it is a pass-through view, the effective base object is the table holding fact-to-dimension flexfield foreign-key mappings, and row-level security or filtering (if any) is applied by that table, not the view.
  • The view is VALID in the APPS schema, confirming the base object exists and compiles in both 12.1.1 and 12.2.2 environments.

Key Columns

  • FACT_SHORT_NAME — The short identifier of the fact to which the mapping applies (for example, a fact identifier such as AP_INVOICES or GL_BALANCES).
  • FK_PHYSICAL_NAME — The physical column name in the fact table that stores the foreign-key value resolving to the dimension.
  • DIMENSION_SHORT_NAME — The short name of the dimension being referenced. This is the column most commonly searched, since it lets users enumerate every fact that references a given dimension.
  • ENABLED_FLAG — Indicates whether the mapping is active (Y) or inactive (N). Disabled mappings are retained for historical reference and are typically excluded by consumers.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — Standard Oracle WHO columns recording audit and concurrent-manager context for each mapping row.

Common Use Cases and Queries

Typical use cases include verifying a fact-to-dimension join path before constructing a custom report, identifying all facts that reference a specific dimension, and auditing which mappings have been disabled during upgrades from 12.1.1 to 12.2.2.

List all enabled mappings for a dimension of interest:

SELECT fact_short_name,
       fk_physical_name,
       dimension_short_name
FROM   apps.edw_fact_flex_fk_maps_v
WHERE  enabled_flag = 'Y'
AND    dimension_short_name = '&dimension_short_name'
ORDER  BY fact_short_name;

Find every fact referencing a given fact's foreign keys:

SELECT fk_physical_name,
       dimension_short_name,
       enabled_flag
FROM   apps.edw_fact_flex_fk_maps_v
WHERE  fact_short_name = '&fact_short_name';

Audit recently modified mappings following an upgrade:

SELECT fact_short_name,
       fk_physical_name,
       dimension_short_name,
       last_update_date,
       last_updated_by
FROM   apps.edw_fact_flex_fk_maps_v
WHERE  last_update_date >= SYSDATE - 30
ORDER  BY last_update_date DESC;

Because the view performs no filtering, queries against it should always constrain on ENABLED_FLAG where active mappings are required, and should be executed against the APPS schema or a synonym granted to the reporting user.