Search Results edw_levels_md_v




Overview
Explore in AI Chat

Overview

EDW_LEVELS_MD_V is an Oracle E-Business Suite (EBS) reporting view owned by the APPS schema and shipped as part of the BIS (Business Intelligence System / Applications BIS) product family. It is a metadata-oriented view that exposes the "level" definitions used within the Enterprise Data Warehouse (EDW) and Business Intelligence dimensional model. In EBS 12.1.1 and 12.2.2, this object remains a standard, valid database view rather than a physical table, and it is intended primarily for read-only consumption by EDW/BI extraction and reporting logic.

The view's practical role is to publish, in a single relational result set, the mapping between a dimensional level and the corresponding level table (the "_LTC" table). This is significant because the user searching for "level_table_name" is looking for precisely the column that this view provides: LEVEL_TABLE_NAME. The "_LTC" suffix denotes the "level table" naming convention used in the EDW star schema, where each analytic level has an associated table holding its members. By centralizing this mapping, EDW_LEVELS_MD_V supports data warehouse population routines, ETL validation, and BI administration queries that need to resolve which physical table backs a given level.

Underlying Base Objects

The view text defines EDW_LEVELS_MD_V over three underlying objects, all of which are themselves views in the APPS schema:

  • CMPLEVEL_V DIMLVL — supplies the level (dimension level) definitions, including element ID, name, prefix, long name, dimension, and description.
  • CMPWBTABLE_V TBL — supplies the level table information. The join is performed with an outer join operator (+), matching DIMLVL.NAME concatenated with '_LTC' to TBL.NAME.
  • CMPWBDIMENSION_V DIM — supplies dimension-level metadata, joined on DIMLVL.DIMENSION = DIM.ELEMENTID.

The ETRM 12.2.2 metadata documents no referenced base tables directly, reflecting that the view sits on top of the CMP* view layer (the "Common Metadata Platform" / BI metadata views), not on base tables. The outer join on TBL means a level is still returned even when no matching "_LTC" table exists, in which case LEVEL_TABLE_ID and LEVEL_TABLE_NAME resolve to NULL. The view is created WITH READ ONLY, confirming it is non-updatable and intended for query access only.

Key Columns

  • LEVEL_ID — The unique element ID of the level (DIMLVL.ELEMENTID).
  • LEVEL_NAME — The internal name of the level (DIMLVL.NAME).
  • LEVEL_PREFIX — A short prefix associated with the level.
  • LEVEL_LONG_NAME — The descriptive long name of the level.
  • LEVEL_TABLE_ID — The element ID of the associated level table (TBL.ELEMENTID); NULL when no table exists.
  • LEVEL_TABLE_NAME — The level table identifier. This is the column most relevant to the "level_table_name" search. Note the definition DECODE(TBL.ELEMENTID, NULL, NULL, DIMLVL.NAME||'_LTC'): the table name is derived by appending '_LTC' to the level name whenever a matching table row exists.
  • DIM_ID — The element ID of the owning dimension.
  • DIM_NAME — The name of the owning dimension.
  • DESCRIPTION — Free-text description of the level.

Common Use Cases and Queries

The principal scenario is resolving a level's physical table for ETL or validation. A typical query is:

SELECT level_name, level_table_name, dim_name FROM apps.edw_levels_md_v WHERE level_table_name IS NOT NULL;

Administrators may filter to a specific dimension:

SELECT level_name, level_table_name FROM apps.edw_levels_md_v WHERE dim_name = :p_dim_name ORDER BY level_name;

To find levels lacking a backing table (a data-modeling gap worth investigating), query for NULL table names:

SELECT level_name, dim_name FROM apps.edw_levels_md_v WHERE level_table_name IS NULL;

Because the object is read-only, it is safe to expose through BI Publisher data models, custom concurrent programs, and EDW loaders. Queries should reference the view through the APPS synonym, and joins to the underlying CMP* views should normally be avoided in favor of this consolidated metadata source.