Search Results parent_table_usage_id

Overview

The view EDW_PVT_MAP_KEY_USAGES_MD_V is a metadata-oriented database object belonging to the BIS (Business Intelligence System) product family within Oracle E-Business Suite. Its designation as an EDW (Enterprise Data Warehouse) private view, combined with the _MD_ metadata suffix, indicates that it functions as an internal mapping repository rather than a user-facing transactional view. The view resolves the relationship between foreign key usages and unique key definitions across mapping constructs, exposing the identifiers needed to trace how a given relational element participates in a mapping.

The defining characteristic of this object is its role in surfacing the PARENT_TABLE_USAGE_ID, which corresponds to the external identifier TARGETRELATIONUSAGE from the underlying foreign key usage construct. The view therefore answers a recurring metadata question: given a foreign key usage in a mapping, which parent-side relation usage does it target? This makes the view relevant to data warehousing configuration auditing, ETL mapping validation, and integration scenarios where referential integrity between mapped entities must be inspected programmatically.

Oracle's ETRM documentation explicitly states that this view is not implemented in the database, meaning the packaged object may not be created by standard installation scripts and its metadata is published primarily for reference.

Underlying Base Objects

The view text is defined entirely over public and private metadata views residing in the CMP namespace, specifically CMPWBRELATIONUSAGE_V, CMPWBFOREIGNKEYUSAGE_V, CMPFOREIGNKEY_V, and CMPUNIQUEKEY_V. These are runtime mapping metadata views rather than base tables, which is consistent with the view's _PVT_ designation. ETRM records no additional referenced base objects beyond these, and the documented owner field is blank.

The join conditions establish the lineage: FK.ELEMENTID = FKU.CMPFOREIGNKEY links a foreign key usage to its foreign key definition; PK.ELEMENTID = FK.UNIQUEKEY links that foreign key to the unique key it references; and SRC_U.ELEMENTID = FKU.OWNINGRELATIONUSAGE links the usage back to the owning relation usage. The result is a four-way equijoin producing a single denormalized row per key usage pairing.

Key Columns

• SOURCE_USAGE_ID — aliased from FKU.OWNINGRELATIONUSAGE; identifies the relation usage on the child or owning side of the mapping.
• PARENT_TABLE_USAGE_ID — aliased from FKU.TARGETRELATIONUSAGE; the column users search for, identifying the parent-side table usage referenced by the foreign key.
• FOREIGN_KEY_USAGE_ID — aliased from FKU.ELEMENTID; the surrogate identifier of the foreign key usage record itself.
• FOREIGN_KEY_ID — aliased from FKU.CMPFOREIGNKEY; links to the foreign key definition.
• UNIQUE_KEY_ID — aliased from PK.ELEMENTID; the unique key referenced by the foreign key.
• MAPPING_ID — aliased from SRC_U.CMPRELATIONMAPPING; the parent mapping to which the relation usage belongs.

Common Use Cases and Queries

Typical usage involves locating parent table usages for a child foreign key usage, or enumerating all foreign keys within a mapping. Analysts joining this view to mapping and table usage views can reconstruct referential paths for ETL validation.

• Resolving the parent usage behind a foreign key: SELECT PARENT_TABLE_USAGE_ID FROM EDW_PVT_MAP_KEY_USAGES_MD_V WHERE FOREIGN_KEY_USAGE_ID = :id;
• Enumerating foreign keys for a mapping: SELECT MAPPING_ID, FOREIGN_KEY_ID, UNIQUE_KEY_ID FROM EDW_PVT_MAP_KEY_USAGES_MD_V WHERE MAPPING_ID = :mapping_id;
• Tracing usage relationships: SELECT SOURCE_USAGE_ID, PARENT_TABLE_USAGE_ID FROM EDW_PVT_MAP_KEY_USAGES_MD_V ORDER BY MAPPING_ID;

Because ETRM states the view is not implemented in this database, deployments must verify its existence before relying on it in 12.1.1 or 12.2.2 environments.