Results for “bsc_kpis_vl”

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

Overview

BSC_KPIS_VL is a seeded, language-enabled (VL) view in the APPS schema belonging to the Oracle Balanced Scorecard (BSC) product family. It exposes the definition of Key Performance Indicators (KPIs) — the individual performance measures that populate a Balanced Scorecard — together with their translated display name and help text. The view is the primary interface through which KPI metadata, including the INDICATOR_TYPE discriminator, is queried for reporting, extension, and integration purposes.

The "_VL" suffix denotes a "view with language translation" pattern: BSC_KPIS_VL joins the base entity table to its translation table and filters the translation rows by the session language using USERENV('LANG'). This ensures each query returns exactly one row per indicator in the caller's language, hiding the multi-language storage model from consuming code.

Underlying Base Objects

The view is documented as being defined over two BSC base tables:

• BSC_KPIS_B — the base (non-translated) table holding indicator identifiers, structural attributes, configuration flags, color and trend settings, and standard WHO columns.
• BSC_KPIS_TL — the translation table holding the language-specific NAME and HELP columns, joined back to BSC_KPIS_B on the INDICATOR key.

The join predicate is B.INDICATOR = TL.INDICATOR AND TL.LANGUAGE = USERENV('LANG'), so only rows matching the current session language are returned. A correlated scalar subquery reads BSC_SYS_CALENDARS_B to derive FISCAL_YEAR for the indicator's assigned calendar.

Key Columns

• INDICATOR — primary key of the KPI; joins to KPI detail and scorecard mapping tables.
• NAME / HELP — translated display name and help text, sourced from BSC_KPIS_TL.
• INDICATOR_TYPE — the discriminator column, central to the user's search query; distinguishes the kind of KPI/indicator being defined (for example scorecard measure vs. other indicator categories).
• CONFIG_TYPE, PROTOTYPE_FLAG, SHARE_FLAG, PUBLISH_FLAG, EDW_FLAG — configuration and visibility flags. PUBLISH_FLAG is coalesced via DECODE so that a NULL value is reported as 1.
• CSF_ID, IND_GROUP_ID, BM_GROUP_ID, PERIODICITY_ID, CALENDAR_ID — foreign keys to Critical Success Factor, indicator group, business measure group, periodicity, and calendar definitions.
• DISP_ORDER, SHORT_NAME — display sequencing and abbreviated label.
• FISCAL_YEAR — derived scalar from BSC_SYS_CALENDARS_B for the row's CALENDAR_ID.
• Color/trend columns — APPLY_COLOR_FLAG, PROTOTYPE_COLOR, PROTOTYPE_COLOR_ID, COLOR_ROLLUP_TYPE, WEIGHTED_COLOR_METHOD, PROTOTYPE_TREND_ID.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical scenarios include listing all KPIs for a language, filtering by indicator type, and joining KPI metadata to scorecard or translation reporting. Sample statements:

• List KPIs by type: SELECT indicator, name, indicator_type FROM apps.bsc_kpis_vl WHERE indicator_type = :p_type ORDER BY disp_order;
• Retrieve one KPI with fiscal context: SELECT indicator, name, help, fiscal_year FROM apps.bsc_kpis_vl WHERE indicator = :p_indicator;
• Enumerate only published KPIs: SELECT short_name, name FROM apps.bsc_kpis_vl WHERE publish_flag = 1 AND edw_flag IS NOT NULL;

Because translation is resolved by USERENV('LANG'), reports and concurrent programs querying this view automatically return the language of the executing session without additional join logic.