Search Results level_table_id




Overview
Explore in AI Chat

Overview

APPS.EDW_LEVEL_TABLE_ATTS_MD_V is a reporting and metadata view within the Oracle E-Business Suite (EBS) Enterprise Data Warehouse (EDW) / ETRM (Enterprise Taxonomy and Reporting Model) layer. It exposes attribute metadata that describes how dimensional level tables are structured and qualified for analytical reporting. In EBS 12.1.1 and 12.2.2, this view is used primarily by the EDW/ETRM reporting stack and by downstream BI integrations that must resolve level-table identifiers to their column, key, and level definitions before generating SQL against the warehouse.

The view is defined in the APPS schema and, as documented in the ETRM 12.2.2 metadata, contains no separately documented referenced base objects other than the projection underlying its definition. Its role is to centralize level-table attribute metadata so that report builders, extract programs, and metadata-driven query generators can join dimension, level, and key attributes in a single, stable interface.

Underlying Base Objects

The documented view definition is a straightforward projection over EDW_LEVEL_TABLE_ATTS_MD, as shown in the ETRM view text:

No additional base tables or joins are documented in the ETRM metadata excerpt, so the view inherits the filtering, grants, and row-level behavior of the underlying EDW_LEVEL_TABLE_ATTS_MD table. Because it is a simple view, it introduces no independent storage and reflects any change committed to the base metadata table immediately. In practice, enabling or disabling rows in EDW_LEVEL_TABLE_ATTS_MD through the ETRM/EDW metadata setup directly affects query results from this view. The view is owned by the APPS schema and is intended to be queried by applications and reporting users with the appropriate APPS grants, consistent with standard EBS 12.1.1 and 12.2.2 security conventions.

Key Columns

The view exposes fourteen columns that together describe dimension, level-table, key, and level attributes. The column most relevant to the user's search, LEVEL_TABLE_ID, is the primary linkage identifier. Its significance is clarified by the surrounding columns:

  • DIM_ID / DIM_NAME — Identifier and display name of the dimension to which the level table belongs.
  • LEVEL_TABLE_ID / LEVEL_TABLE_NAME — Identifier and name of the level table; LEVEL_TABLE_ID is the key join to related EDW/ETRM metadata views.
  • LEVEL_PREFIX — Naming prefix applied to level-table columns, used when auto-generating report SQL.
  • LEVEL_TABLE_COL_ID / LEVEL_TABLE_COL_NAME / LEVEL_TABLE_COL_LONG_NAME — Identifier, physical name, and long/display name of the column within the level table.
  • KEY_TYPE / KEY_ID / KEY_NAME — Classification, identifier, and name of the key associated with the level-table attribute.
  • LEVEL_ID / LEVEL_NAME / LEVEL_LONG_NAME — Identifier, name, and long/display name of the level represented by the row.

Common Use Cases and Queries

The view is typically queried to resolve LEVEL_TABLE_ID metadata before building or validating ETRM report SQL, and to enumerate levels, keys, and columns for a given dimension. Typical scenarios include report-template generation, metadata validation, and integration mapping in EBS 12.1.1 and 12.2.2.

Sample query by level table identifier:

  • SELECT dim_name, level_table_name, level_table_col_name, key_type, key_name, level_name FROM apps.edw_level_table_atts_md_v WHERE level_table_id = :p_level_table_id;

Sample query listing attributes for a dimension:

  • SELECT level_table_id, level_table_name, level_prefix, level_table_col_long_name, level_long_name FROM apps.edw_level_table_atts_md_v WHERE dim_name = '<DIMENSION_NAME>' ORDER BY level_table_name, level_table_col_name;

Sample join to key and level metadata:

  • SELECT v.level_table_id, v.key_id, v.key_name, v.level_id, v.level_name FROM apps.edw_level_table_atts_md_v v WHERE v.key_type IS NOT NULL AND v.level_id IS NOT NULL;

Because the view is unaggregated metadata, queries should filter on a dimension or level-table identifier to avoid returning the full metadata set, and callers should treat LEVEL_TABLE_ID as the stable join key across ETRM metadata views.