Results for “edw_foreign_keys_md_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

EDW_FOREIGN_KEYS_MD_V is a metadata extraction view owned by the APPS schema and delivered as part of the BIS (Business Intelligence System) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. The view exposes foreign key metadata captured in the Oracle EBS data dictionary abstraction layer, presenting each foreign key relationship together with its owning relation (parent table) and the corresponding unique key (referenced key) that it targets. Its role is to supply BI and data-warehouse tooling with a normalized, read-only catalogue of referential constraints so that downstream ETL, lineage analysis, and metadata reporting processes can traverse the EBS entity model without querying the underlying XML-based repository tables directly.

The object is registered as VALID in the ETRM repository, and its structure is documented as a fixed projection over three metadata views rather than over physical application tables. This positions EDW_FOREIGN_KEYS_MD_V as a stable interface intended for external consumption.

Underlying Base Objects

The documented view text defines EDW_FOREIGN_KEYS_MD_V as a join across three underlying metadata views:

• CMPFOREIGNKEY_V FK — the driving source of foreign key definitions, supplying the owning relation, class name, foreign key name, element identifier, long name, and description, plus the referenced unique key identifier.
• CMPRELATION_V TBL — resolved by matching FK.OWNINGRELATION to TBL.ELEMENTID; it provides the name of the table (relation) that owns the foreign key.
• CMPUNIQUEKEY_V UK — resolved by matching FK.UNIQUEKEY to UK.ELEMENTID; it supplies the element identifier and name of the referenced unique key.

The join therefore links each foreign key to both its parent entity and its target key constraint. The view is defined WITH READ ONLY, and no base application tables are documented beyond these three metadata views. Access is governed by the standard APPS schema grants in the EBS environment.

Key Columns

The documented column list maps to the select list as follows:

• ENTITY_ID — identifier of the relation that owns the foreign key (derived from FK.OWNINGRELATION).
• ENTITY_TYPE — the class name of the owning relation (FK.CLASSNAME).
• ENTITY_NAME — the name of the owning table (TBL.NAME).
• FOREIGN_KEY_NAME — the name of the foreign key constraint (FK.NAME).
• FOREIGN_KEY_ID — the element identifier of the foreign key (FK.ELEMENTID).
• BUSINESS_NAME — the descriptive long name of the foreign key (FK.LONGNAME).
• DESCRIPTION — free-text description of the constraint (FK.DESCRIPTION).
• KEY_ID — the element identifier of the referenced unique key (UK.ELEMENTID).
• KEY_NAME — the name of the referenced unique key (UK.NAME).

Common Use Cases and Queries

The view is typically used to inventory referential relationships, drive join generation in BI metadata layers, and support impact analysis when entities are modified. A representative query lists the foreign keys owned by a specific table and the keys they reference:

SELECT entity_name, foreign_key_name, business_name, key_name, description
FROM apps.edw_foreign_keys_md_v
WHERE entity_name = :table_name
ORDER BY foreign_key_name;

A second pattern counts foreign keys per owning table to identify highly connected entities:

SELECT entity_type, entity_name, COUNT(*) fk_count
FROM apps.edw_foreign_keys_md_v
GROUP BY entity_type, entity_name
ORDER BY fk_count DESC;

Because the view is read-only and metadata-driven, these queries are safe for reporting accounts and are commonly embedded in ETL controllers and lineage utilities supporting the Oracle EBS data warehouse.