Search Results edw_foreign_key_columns_md_v

Overview

The view EDW_FOREIGN_KEY_COLUMNS_MD_V is a metadata reporting object delivered within the Oracle E-Business Suite environment under the BIS – Applications BIS product family. It exposes the column-level definition of foreign key constraints as modeled by the Oracle E-Business Suite Common Modeling Platform (CMP) repository, which is the underlying metadata layer used by ETRM (Enterprise Technical Reference Model) and related enterprise data warehouse reporting. The view presents one row for each column participating in a foreign key relationship, capturing the owning entity, the foreign key definition, the referenced primary or unique key, and the individual column mappings that implement the relationship. It is intended for metadata analysis, impact assessment, data lineage, and schema documentation rather than for transactional or operational reporting.

The view name is commonly sought under the alias cmpforeignkey_v, reflecting its dependency on the CMP component views that describe foreign keys, relations, items, and unique keys. In the documented ETRM metadata the object is noted as “Not implemented in this database,” indicating that in the inspected instance the view definition exists in the repository but the underlying CMP objects are not populated or the view is not deployed in that specific environment. Practitioners should therefore verify availability on a per-instance basis before relying on it in custom reports.

Underlying Base Objects

The view is defined over a set of CMP metadata component views rather than physical base tables. The documented FROM clause references CMPRELATION_V (aliased REL for the owning relation and P_REL for the relation owning the primary key), CMPFOREIGNKEY_V (FK), CMPWBITEMSETUSAGE_V (FKISU), CMPITEM_V (FK_ITEM), and CMPUNIQUEKEY_V (PK). The joins link the foreign key to its owning relation through FK.OWNINGRELATION = REL.ELEMENTID, to the item set usage through FKISU.ITEMSET = FK.ELEMENTID, to the attribute item through FK_ITEM.ELEMENTID = FKISU.ATTRIBUTE, and to the referenced unique key through PK.ELEMENTID = FK.UNIQUEKEY, with the primary key’s owning relation resolved via P_REL.ELEMENTID = PK.OWNINGRELATION. The view text is created WITH READ ONLY, confirming its reporting-only role. No physical base tables are documented; all inputs are CMP metadata views.

Key Columns

• ENTITY_ID, ENTITY_TYPE, ENTITY_NAME – Identify the owning entity (table or relation) that carries the foreign key.
• FK_NAME, FK_ID, FK_LOGICAL_NAME, FK_DESCRIPTION – Identify and describe the foreign key constraint itself.
• PK_ID, PK_NAME – Identify the referenced primary or unique key on the parent entity.
• FK_COLUMN_ID, FK_COLUMN_NAME, FK_POSITION – Describe each individual column of the foreign key, including its ordering within the composite key.

These columns allow the consumer to reconstruct the full foreign key topology: parent entity, child entity, referenced key, and the ordered column mapping that joins them.

Common Use Cases and Queries

Typical scenarios include schema documentation, impact analysis before column changes, ETL/data lineage validation, and generating ER-style metadata extracts. A basic query listing foreign keys with their columns:

SELECT ENTITY_NAME, FK_NAME, PK_NAME, FK_COLUMN_NAME, FK_POSITION
FROM   EDW_FOREIGN_KEY_COLUMNS_MD_V
ORDER BY ENTITY_NAME, FK_NAME, FK_POSITION;

Impact analysis for a specific parent key:

SELECT ENTITY_NAME, FK_NAME, FK_COLUMN_NAME
FROM   EDW_FOREIGN_KEY_COLUMNS_MD_V
WHERE  PK_NAME = :pk_name;

Because the view is reported as not implemented in some databases, verify its existence in ALL_VIEWS and confirm the CMP metadata is populated before integrating it into reporting or integration pipelines.