Search Results edw_all_columns_md_v

Overview

EDW_ALL_COLUMNS_MD_V is a read-only view owned by the APPS schema in Oracle E-Business Suite, registered under the BIS (Business Intelligence System / Applications BIS) product family. Its name indicates its purpose: it exposes metadata about all columns, in the sense of the Enterprise Data Warehouse (EDW) dictionary. Rather than holding transactional business data, the view publishes structural metadata about the tables and entities defined in the Oracle EBS Common Metadata (CMP) repository, which underpins the Oracle Business Intelligence and data warehouse generation utilities.

In EBS 12.1.1 and 12.2.2 the BIS schema hosts the metadata infrastructure used to describe source tables and their columns so that ETL, extracts, and reporting layers can be generated or interpreted programmatically. EDW_ALL_COLUMNS_MD_V is a presentation view over that infrastructure, giving report writers and integration developers a flattened, denormalised catalogue: one row per column, joined to its owning relation and data type. It is defined WITH READ ONLY, confirming it is a pure query and discovery interface rather than a maintenance object.

Underlying Base Objects

The ETRM metadata documents no separate base tables; the view is defined directly over three CMP views, which are themselves the exposed faces of the Common Metadata repository tables:

• CMPITEM_V ITEM — the item (column) definition, carrying ELEMENTID, NAME, LONGNAME, DESCRIPTION, POSITION, LENGTH, the owning relation, and the domain (data type) reference.
• CMPRELATION_V REL — the relation (table/entity) to which each item belongs, supplying ELEMENTID, CLASSNAME, and NAME.
• CMPDATATYPE_V DATA — the data type or domain referenced by the item's DOMAIN attribute, supplying the type name.

The join logic is: ITEM.OWNINGRELATION = REL.ELEMENTID and ITEM.DOMAIN = DATA.ELEMENTID. Consequently every row of the view represents one column belonging to one relation, annotated with its declared data type — effectively the column-level half of the EBS metadata dictionary.

Key Columns

• ENTITY_ID — the ELEMENTID of the owning relation (from CMPRELATION_V); the identifier of the table/entity that owns the column.
• ENTITY_TYPE — the CLASSNAME of the owning relation, indicating the kind of metadata object (for example table or view classification).
• ENTITY_NAME — the NAME of the owning relation; the logical entity/table name.
• COLUMN_ID — the ELEMENTID of the item; the unique identifier of the column definition.
• COLUMN_NAME — the physical item NAME (the database column name).
• BUSINESS_NAME — the LONGNAME of the item, typically the user-facing or business-friendly label.
• DESCRIPTION — the item description text.
• POSITION — the ordinal position of the column within its owning relation.
• DATA_TYPE — the NAME of the referenced data type/domain from CMPDATATYPE_V.
• LENGTH — the declared item LENGTH (size) of the column.

Common Use Cases and Queries

Typical uses include discovering the structure of metadata-described entities, validating that column definitions exist before invoking BI/EDW generation utilities, populating data dictionaries for reporting, and reconciling the metadata layer against the physical database catalogue. A simple listing of all columns for one entity:

• SELECT column_name, business_name, data_type, length, position FROM apps.edw_all_columns_md_v WHERE entity_name = :entity ORDER BY position;
• SELECT entity_name, COUNT(*) FROM apps.edw_all_columns_md_v GROUP BY entity_name ORDER BY 2 DESC;
• SELECT DISTINCT data_type FROM apps.edw_all_columns_md_v ORDER BY 1;

Because the view is READ ONLY and fully qualified under APPS, it may be queried directly by BI Publisher data models, custom concurrent programs, or ad-hoc SQL, subject to standard APPS schema grants and MOAC/security considerations applicable to metadata views.