Results for “bsc_sys_dim_levels_vl”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BSC_SYS_DIM_LEVELS_VL is a documented, valid view owned by the APPS schema within the Oracle E-Business Suite environment (12.1.1 and 12.2.2). It belongs to the BSC product family, commonly referred to as Balanced Scorecard, a module that supports performance management, dimensional modeling, and analytic reporting. The view is a "VL" (translated value) view, meaning it exposes a single-language, user-facing record set by joining a base table that stores language-independent data with a translation table that stores language-dependent attributes. Its documented purpose is to present a view of BSC_SYS_DIM_LEVELS_B and BSC_SYS_DIM_LEVELS_TL.

The view is used primarily as a read-only metadata source describing dimension levels defined within the Balanced Scorecard framework. Because it surfaces level configuration alongside localized display strings, it supports both internal processing logic and end-user presentation in forms, reports, and integration extracts.

Underlying Base Objects

The view text confirms a join between two base tables:

• BSC_SYS_DIM_LEVELS_B — the "_B" table, which holds language-independent or base-level data for each dimension level, keyed by DIM_LEVEL_ID.
• BSC_SYS_DIM_LEVELS_TL — the "_TL" table, which holds translation or language-dependent attributes, also keyed by DIM_LEVEL_ID and filtered by LANGUAGE.

The join condition is B.DIM_LEVEL_ID = TL.DIM_LEVEL_ID, and the translation side is restricted by TL.LANGUAGE = USERENV('LANG'). This ensures that only the record in the session's current language is returned, producing one logical row per dimension level. Note that the ETRM 12.2.2 metadata lists no referenced base objects as "documented," although the view text explicitly names the two underlying tables; the view exists solely as an access layer over them.

Key Columns

The view exposes identifiers, display attributes, and structural metadata. Notable columns include:

• DIM_LEVEL_ID — the primary identifier joining the base and translation rows.
• NAME, HELP, TOTAL_DISP_NAME, COMP_DISP_NAME — localized labels and descriptive text sourced from the TL table.
• LEVEL_TABLE_NAME, TABLE_TYPE, LEVEL_PK_COL — physical table details and primary key information for the level.
• LEVEL_VIEW_NAME, SHORT_NAME, ABBREVIATION — alternative naming attributes for the level.
• VALUE_ORDER_BY, COMP_ORDER_BY — ordering attributes for values and components.
• CUSTOM_GROUP — grouping attribute for custom dimension handling.
• USER_KEY_SIZE, DISP_KEY_SIZE — key length sizing metadata.
• EDW_FLAG, EDW_DIM_ID, EDW_DIM_LEVEL_ID — enterprise data warehouse integration flags and identifiers, with NVL(B.EDW_FLAG, 0) ensuring a default of zero.
• SOURCE — origin indicator for the level record.

Common Use Cases and Queries

Typical scenarios include reporting on dimension level configuration, validating level metadata prior to analytic loads, and resolving localized display names for forms or BI objects. A basic query is:

• SELECT DIM_LEVEL_ID, NAME, LEVEL_TABLE_NAME, TABLE_TYPE, EDW_FLAG FROM BSC_SYS_DIM_LEVELS_VL ORDER BY DIM_LEVEL_ID;
• SELECT DIM_LEVEL_ID, SHORT_NAME, LEVEL_VIEW_NAME FROM BSC_SYS_DIM_LEVELS_VL WHERE EDW_FLAG = 1;
• SELECT DIM_LEVEL_ID, TOTAL_DISP_NAME, COMP_DISP_NAME FROM BSC_SYS_DIM_LEVELS_VL WHERE LEVEL_TABLE_NAME IS NOT NULL;

Because the view already filters by USERENV('LANG'), queries automatically return the correct localized strings for the connected session, making it suitable for embedded reports without additional language predicates.