Search Results edw_tables_md_v




Overview
Explore in AI Chat

Overview

EDW_TABLES_MD_V is a metadata view shipped within the Oracle E-Business Suite Business Intelligence System (BIS) product family. Its role is to expose a filtered catalog of table-type metadata objects defined in the Oracle EBS data dictionary, specifically those registered as workbook tables (WB tables) in the common component metadata layer. The view name follows the enterprise data warehouse naming convention (EDW) and the "_MD_V" suffix that denotes metadata, indicating that its purpose is to support reporting and integration processes requiring a programmatic listing of tables available to the BIS/EDW analytical framework.

In EBS 12.1.1 and 12.2.2, this view is documented as a view object with no implementation in the current database for certain instances, meaning availability can vary depending on whether the BIS/EDW components are installed and whether the underlying common-component views are present. Where it is deployed, it provides a stable, presentation-layer interface for retrieving table identifiers and display names without requiring direct queries against the more complex common-component relation structures.

Underlying Base Objects

The view is defined over a single source object: CMPRELATION_V, referenced with the alias TBL. The definition applies a filter on the CLASSNAME column:

  • TBL.CLASSNAME = 'CMPWBTABLE'

This predicate restricts the result set to metadata records classified as workbook table objects. CMPRELATION_V is the common-component relation view that materializes relationship records between metadata elements, storing both the element identity and its class membership. EDW_TABLES_MD_V therefore acts as a class-specific projection of that broader relation view, surfacing only the subset relevant to workbook table definitions. No additional base tables are documented; the entire lineage is the single CMPRELATION_V source plus the constant class filter.

Key Columns

The view exposes three columns, inherited from the CMPRELATION_V projection:

  • ELEMENTID — The unique numeric identifier of the metadata element. This is the primary join key used to link the workbook table entry to other metadata views or element registries.
  • NAME — The short internal name of the table element, typically the registered object name used by the metadata framework.
  • LONG_NAME — The descriptive or user-facing name of the table. Note that the documentation lists the column header as LONG_NAME while the SELECT clause aliases the source property as LONGNAME; consumers should verify the actual exposed column name in their target instance, as the view text and the documented column list diverge on this point.

Common Use Cases and Queries

Typical scenarios include building inventory reports of workbook-eligible tables, driving metadata reconciliation between the EDW layer and source dictionary objects, and populating lookup lists in custom BIS extensions. A representative query listing all workbook table metadata is:

  • SELECT elementid, name, longname FROM edw_tables_md_v ORDER BY name;
  • Join to a metadata element view on ELEMENTID to retrieve additional properties: SELECT t.name, m.description FROM edw_tables_md_v t, cmp_element_v m WHERE t.elementid = m.elementid;
  • Filter by naming pattern: SELECT * FROM edw_tables_md_v WHERE name LIKE 'EDW%';

Because the view is documented as not implemented in some databases, developers should verify existence via ALL_VIEWS or query with a defensive PL/SQL wrapper before embedding it in production code. Where present, it offers a lightweight and semantically scoped entry point to workbook table metadata suitable for reporting and integration.