Search Results target_column_usage_id

Overview

APPS.EDW_PVT_MAP_COLUMNS_MD_V is a read-only metadata view used in the Oracle E-Business Suite Enterprise Data Warehouse (EDW) and ETRM (Enterprise Transaction Repository Model) extraction layer. It presents the column-level mapping relationships that exist between source and target objects in the TCA/Common Mapping model, resolving relational "mapping" definitions into explicit source/target pairs of columns, column usages, usages, and functional parameter usages. The view is defined with WITH READ ONLY, confirming it is an analytical/reporting structure rather than a transactional one.

The view is documented at the 12.2.2 level and is compatible with the 12.1.1 architecture. Its name (prefixed EDW_PVT) indicates it is a private, internal EDW object typically consumed by ETL programs, extract views, or downstream data-warehouse mapping logic rather than by end users via the standard EBS forms. Because it resolves a mapping into source/target element IDs across multiple CMP* objects, it plays a central role in traceability—allowing implementers to determine exactly which source columns feed which target columns within a mapping definition.

Underlying Base Objects

The view is constructed entirely from other CMP* views (themselves exposing the Common Mapping base tables). Eight such objects are joined in the definition:

• CMPWBITEMUSAGE_V — aliased twice as SRC_IU and TGT_IU (source and target item usage).
• CMPITEM_V — aliased twice as SRC_IT and TGT_IT (source and target items/columns). This is the object the search term cmpitem_v corresponds to; it supplies the actual column identifiers.
• CMPWBRELATIONUSAGE_V — aliased twice as SRC_RU and TGT_RU (source and target relation usage, carrying CMPRELATIONMAPPING and DMLTYPE).
• CMPWBACTUALPARAMETER_V — aliased twice as SRC_AP and TGT_AP (actual parameters linking column usage to functional usage).

The join path is driven by the relation-mapping key: tgt_ru.cmprelationmapping = src_ru.cmprelationmapping, with src_ru.source = 1 fixing the source side. Item usages attach to relation usages via cmpwbrelationusage, items attach to item usages via cmpitem, and actual parameters attach to item usages via cmpwbitemusage. Source and target parameter rows are matched by the shared cmpwbfunctionusage. ETRM metadata records no separately documented base tables, so the view should be treated as depending on these CMP* constructs.

Key Columns

• MAPPING_ID (src_ru.cmprelationmapping) — the mapping definition that links the source and target relationship usages.
• TARGET_USAGE_ID (tgt_ru.elementid) — target relation-usage element.
• TARGET_COLUMN_USAGE_ID (tgt_iu.elementid) — target item-usage element.
• TARGET_COLUMN_ID (tgt_it.elementid) — the target column (item) element.
• SOURCE_USAGE_ID (src_ru.elementid) — source relation-usage element.
• SOURCE_COLUMN_USAGE_ID (src_iu.elementid) — source item-usage element.
• SOURCE_COLUMN_ID (src_it.elementid) — the source column (item) element.
• FUNC_USAGE_ID (src_ap.cmpwbfunctionusage) — the functional usage that binds the source and target actual parameters.

Collectively these columns provide a directional, column-to-column map for each mapping, filtered to rows where a target DMLTYPE is defined.

Common Use Cases and Queries

Typical uses include impact analysis (which target columns receive a given source column), lineage extraction for EDW metadata repositories, and validation of mapping completeness. A representative query lists all source-to-target column pairs for a mapping:

SELECT mapping_id,
       source_column_id,
       target_column_id,
       func_usage_id
FROM   apps.edw_pvt_map_columns_md_v
WHERE  mapping_id = :p_mapping_id;

To trace all mappings that consume a particular source column:

SELECT mapping_id,
       target_column_id
FROM   apps.edw_pvt_map_columns_md_v
WHERE  source_column_id = :p_source_column_id;

Because the view is read-only and joins several CMP* metadata views, queries are best restricted with equality predicates on mapping_id, source_column_id, or target_column_id to limit the join fan-out. The view is not documented as exposing additional filterable base tables, so effective use depends on the element identifiers returned by the CMP* sources.