Search Results cmpdatatype_v




Overview
Explore in AI Chat

Overview

EDW_PVT_COLUMNS_MD_V is a metadata view owned by the APPS schema within the BIS (Business Intelligence System) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes descriptive information about the columns that make up the objects managed by the Enterprise Data Warehouse / Common Metadata Persistence (CMP) framework, presenting each column together with its datatype, length, logical name, and parent-object association. Because the view resolves CMP metadata definitions into a flat, query-friendly projection, it functions as a data dictionary for the CMP schema rather than as a transactional reporting source.

The view is primarily consumed by ETL and integration components, metadata browsers, and custom diagnostic queries. Users searching for the related object cmpdatatype_v will find that it is one of the two underlying views referenced here, supplying the datatype descriptions joined against the column-level metadata. EDW_PVT_COLUMNS_MD_V therefore bridges the column definitions in CMPITEM_V with the datatype master in CMPDATATYPE_V.

Underlying Base Objects

According to the ETRM documentation, the view is defined over two CMP views rather than directly over base tables:

  • CMPITEM_V ITEM — supplies the column-level records, contributing ELEMENTID, NAME, OWNINGRELATION, LENGTH, LONGNAME, DESCRIPTION, and DOMAIN.
  • CMPDATATYPE_V DATA — supplies the datatype master, contributing NAME (the datatype label) and ELEMENTID.

The join is performed on DATA.ELEMENTID = ITEM.DOMAIN, meaning each column row is linked to its declared datatype by matching the column's domain identifier to the datatype element. No base tables are documented as directly referenced; the view inherits its lineage through CMPITEM_V and CMPDATATYPE_V. Because these are CMP-layer views, the underlying physical tables remain abstracted and are subject to the CMP metadata model. The absence of documented base objects in the 12.2.2 metadata reflects that the ETRM entry records the view text only, and downstream dependencies resolve through the CMP family.

Key Columns

  • COLUMN_ID — maps from ITEM.ELEMENTID; the unique identifier of the column element within the CMP metadata repository.
  • COLUMN_NAME — maps from ITEM.NAME; the physical or logical column name.
  • PARENT_OBJECT_ID — maps from ITEM.OWNINGRELATION; identifies the object to which the column belongs.
  • DATA_TYPE — maps from DATA.NAME via the DOMAIN join; the datatype name resolved from CMPDATATYPE_V.
  • LENGTH — the declared length of the column as recorded in CMPITEM_V.
  • LOGICAL_NAME — maps from ITEM.LONGNAME; the business-friendly label for the column.
  • DESCRIPTION — the descriptive text attached to the column definition.

Common Use Cases and Queries

Typical uses include auditing the column inventory for a CMP-managed object, verifying datatype assignments, and generating metadata extracts for downstream documentation or ETL mapping.

To list all columns for a specific parent object:

  • SELECT COLUMN_ID, COLUMN_NAME, DATA_TYPE, LENGTH, LOGICAL_NAME FROM APPS.EDW_PVT_COLUMNS_MD_V WHERE PARENT_OBJECT_ID = :object_id ORDER BY COLUMN_NAME;

To audit datatype distribution across the repository:

  • SELECT DATA_TYPE, COUNT(*) FROM APPS.EDW_PVT_COLUMNS_MD_V GROUP BY DATA_TYPE ORDER BY 2 DESC;

To search by logical name for documentation purposes:

  • SELECT COLUMN_NAME, LOGICAL_NAME, DESCRIPTION FROM APPS.EDW_PVT_COLUMNS_MD_V WHERE UPPER(LOGICAL_NAME) LIKE UPPER('%&term%');

Because the view is read-only metadata and owned by APPS, queries should be run with appropriate responsibilities and, where possible, against a reporting clone to avoid contention on production CMP metadata.