Results for “level_source”
17 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
BSC_KPI_DIM_LEVELS_VL is a Multi-Lingual (ML) validation view in the Oracle E-Business Suite Balanced Scorecard (BSC) module, owned by the APPS schema. It presents the definition of level structures used by Key Performance Indicator (KPI) dimensions, combining non-translatable structural attributes with language-specific display text. In ETRM releases 12.1.1 and 12.2.2, the view is the standard reporting and integration surface for dimension level metadata, exposing the logical hierarchy that governs how KPI dimension members are rolled up, filtered, and displayed in Balanced Scorecard dashboards, scorecards, and analytical workspace pages.
Underlying Base Objects
The view is defined over three documented base objects: BSC_KPI_DIM_LEVELS_B, BSC_KPI_DIM_LEVELS_TL, and BSC_SYS_DIM_LEVELS_B. The _B table stores the non-translatable attributes of each KPI dimension level — indicator, dimension set, level index, table and view names, filter and ordering logic, and parent relationships. The _TL table stores language-dependent columns (NAME, HELP, TOTAL_DISP_NAME, COMP_DISP_NAME) keyed by the same three identifier columns plus LANGUAGE. The _VL suffix reflects the standard EBS convention of an ML view joining _B and _TL and restricting rows to the runtime session language via USERENV('LANG'). BSC_SYS_DIM_LEVELS_B is joined with an outer (+) operator on LEVEL_TABLE_NAME to supply system-level defaults such as DISP_KEY_SIZE, SHORT_NAME (exposed as LEVEL_SHORTNAME), and SOURCE (exposed as LEVEL_SOURCE). The join on the two KPI tables is an equi-join across INDICATOR, DIM_SET_ID, and DIM_LEVEL_INDEX, so the language restriction is enforced inside the view definition rather than at query time.
Key Columns
- INDICATOR / DIM_SET_ID / DIM_LEVEL_INDEX — Composite key identifying the level definition and its position within a dimension set.
- NAME / HELP / TOTAL_DISP_NAME / COMP_DISP_NAME — Translatable labels, help text, and display names for the total and comparison variants of the level.
- LEVEL_TABLE_NAME / LEVEL_VIEW_NAME — Physical table and view backing the level, used when the view is consumed by reporting logic that must query the level source directly.
- FILTER_COLUMN / FILTER_VALUE — Predicate used to restrict the rows considered part of the level.
- DEFAULT_VALUE / DEFAULT_TYPE / DEFAULT_KEY_VALUE — Default member selection for the level.
- VALUE_ORDER_BY / COMP_ORDER_BY — Ordering expressions for the level's value and comparison lists.
- LEVEL_PK_COL / PARENT_LEVEL_INDEX / PARENT_LEVEL_REL / PARENT_LEVEL_INDEX2 / PARENT_LEVEL_REL2 — Primary key column and up to two parent link definitions, describing the hierarchy.
- TABLE_RELATION — The join relationship between the level table and its parent, central to constructing the dimensional hierarchy in generated SQL.
- STATUS / PARENT_IN_TOTAL / POSITION / TOTAL0 — Flags and ordinal values controlling visibility, inclusion in totals, and ordering.
- LEVEL_DISPLAY / NO_ITEMS / USER_LEVEL0–USER_LEVEL2 / USER_LEVEL*_DEFAULT — Display mode, item count, and per-user level overrides with their defaults.
- TARGET_LEVEL / LEVEL_SHORTNAME / LEVEL_SOURCE / DISP_KEY_SIZE — Target indicator for the level and system-level attributes sourced from BSC_SYS_DIM_LEVELS_B.
Common Use Cases and Queries
Typical scenarios include inspecting the level ladder for a KPI dimension, resolving TABLE_RELATION values to understand generated hierarchy SQL, and retrieving translated labels for a given session language. A representative query lists the levels of a dimension set with their display names:
SELECT dim_level_index, name, total_disp_name, level_table_name,
parent_level_index, table_relation, status
FROM apps.bsc_kpi_dim_levels_vl
WHERE indicator = :indicator
AND dim_set_id = :dim_set_id
ORDER BY position;
Because the session language predicate is embedded, callers need not join BSC_KPI_DIM_LEVELS_TL separately; translating labels for another language requires querying the _TL table directly or setting the language context. The outer join to BSC_SYS_DIM_LEVELS_B means LEVEL_SHORTNAME, LEVEL_SOURCE, and DISP_KEY_SIZE may be NULL for custom levels without a system entry, so comparisons and lookups on those columns should tolerate nulls.
-
View: BSC_KPI_DIM_LEVELS_VL 12.1.1
View of BSC_KPI_DIM_LEVELS_B and BSC_KPI_DIM_LEVELS_TL
APPS.BSC_KPI_DIM_LEVELS_VL·↳ BSC_KPI_DIM_LEVELS_B·↳ BSC_KPI_DIM_LEVELS_TL·↳ BSC_SYS_DIM_LEVELS_B·Explore BSC module →
-
View: BSC_KPI_DIM_LEVELS_VL 12.2.2
View of BSC_KPI_DIM_LEVELS_B and BSC_KPI_DIM_LEVELS_TL
Not implemented in this database·Explore BSC module →
-
eTRM - BSC Tables and Views 12.1.1
Tab permissions