Search Results cmpwbforeignkeyusage_v

Overview

APPS.EDW_PVT_MAP_KEY_USAGES_MD_V is a metadata extraction view within the Oracle E-Business Suite warehouse and Enterprise Data Warehouse (EDW) extraction layer. It belongs to the family of EDW_PVT_* private views used by the ETRM (E-Business Suite Technical Reference Model) and EDW loading programs to publish relational metadata about the Oracle EBS data model. Specifically, this view exposes the mapping between foreign key usages and their referenced unique keys, expressed in the vocabulary of the CWM (Common Warehouse Metamodel) objects that Oracle uses to describe EBS tables, columns, and relationships.

The view presents one row per foreign key usage, joining a foreign key usage to its owning relation usage (the source table usage), its target relation usage (the parent table referenced), the corresponding CMPFOREIGNKEY_V foreign key definition, and the referenced CMPUNIQUEKEY_V unique key. This makes it an authoritative source for reconstructing referential integrity metadata in reporting, data lineage, and reverse-engineering tools.

Underlying Base Objects

The documented view text references four objects. None are catalogued as base tables in the ETRM metadata; all are themselves views over the CWM repository:

• CMPWBRELATIONUSAGE_V (aliased SRC_U) — relation usage metadata, including the CMPRELATIONMAPPING mapping identifier.
• CMPWBFOREIGNKEYUSAGE_V (aliased FKU) — foreign key usage metadata, linking to the owning and target relation usages.
• CMPFOREIGNKEY_V (aliased FK) — foreign key definitions, including the referenced unique key.
• CMPUNIQUEKEY_V (aliased PK) — unique key definitions.

The joins are equality joins on FK.ELEMENTID = FKU.CMPFOREIGNKEY, PK.ELEMENTID = FK.UNIQUEKEY, and SRC_U.ELEMENTID = FKU.OWNINGRELATIONUSAGE. No base tables are documented directly; the dependencies are entirely view-to-view, which is typical of the CWM metadata layer shipped with ETRM.

Key Columns

• SOURCE_USAGE_ID (FKU.OWNINGRELATIONUSAGE) — identifies the relation usage representing the child/source table that owns the foreign key.
• PARENT_TABLE_USAGE_ID (FKU.TARGETRELATIONUSAGE) — identifies the relation usage for the parent table referenced by the foreign key.
• FOREIGN_KEY_USAGE_ID (FKU.ELEMENTID) — the foreign key usage element identifier.
• FOREIGN_KEY_ID (FKU.CMPFOREIGNKEY) — the underlying foreign key definition identifier.
• UNIQUE_KEY_ID (PK.ELEMENTID) — the unique key that the foreign key references.
• MAPPING_ID (SRC_U.CMPRELATIONMAPPING) — the relation mapping to which the source usage belongs, allowing scoping of foreign keys within a schema mapping.

Common Use Cases and Queries

Because the view answers the question "which foreign key points to which unique key across which relation usages," it is commonly used in metadata browsing, EDW configuration validation, and the construction of join paths for data extraction. A typical query lists all parent/child relationships for a mapping:

• SELECT mapping_id, source_usage_id, parent_table_usage_id, foreign_key_id, unique_key_id FROM apps.edw_pvt_map_key_usages_md_v WHERE mapping_id = :p_mapping_id;
• SELECT * FROM apps.edw_pvt_map_key_usages_md_v WHERE foreign_key_usage_id = :p_fk_usage_id;
• Joining the view back to CMPWBRELATIONUSAGE_V on source_usage_id to resolve physical EBS table and column names for data lineage reports.

The view is read-only and is intended for metadata processing rather than transactional access. Reviewers should treat all identifiers as CWM element handles rather than EBS TABLE_NAME values, and resolve them through the corresponding relation usage views.