Search Results bsc_kpi_analysis_options_vl




Overview
Explore in AI Chat

Overview

The view BSC_KPI_ANALYSIS_OPTIONS_VL belongs to the Balanced Scorecard (BSC) module of Oracle E-Business Suite, identified in ETRM 12.2.2 metadata as an obsolete, no-longer-implemented component. It is a "VL" (translated view) that unifies a base table holding language-independent data with a translation table holding language-dependent data, exposing a single, localized rowset to reporting and integration consumers. In classic Oracle EBS design, this pattern allows client sessions to retrieve descriptive text (names, help) in the session language while keeping structural attributes language-neutral. The view is part of the BSC KPI analysis-option configuration layer, which defined selectable analysis options, their hierarchy, and dimension-set bindings for KPI-driven analytic display. Because the object is documented as "Not implemented in this database," it will not be present in a standard 12.1.1 or 12.2.2 installation; it is catalogued here for historical reference and for environments that migrated from earlier BSC-enabled releases. The USER_LEVEL0 search term corresponds directly to a column exposed by this view, indicating a user's interest in how top-level user-assignment attributes are surfaced for KPI analysis options.

Underlying Base Objects

The view is explicitly defined over two base tables, joined on the full composite primary key plus the session language:

  • BSC_KPI_ANALYSIS_OPTIONS_B — the base (non-translated) table holding structural and numerical attributes, including the user-level assignment columns and the dimension-set reference.
  • BSC_KPI_ANALYSIS_OPTIONS_TL — the translation table holding language-dependent text, specifically NAME and HELP.

The join is performed on INDICATOR, ANALYSIS_GROUP_ID, OPTION_ID, PARENT_OPTION_ID, and GRANDPARENT_OPTION_ID, with the added predicate LANGUAGE = USERENV('LANG'). This last condition ensures each query returns text in the language of the current session, defaulting to the site's base language when no matching translation exists. ETRM metadata records no additional referenced base objects beyond these two tables.

Key Columns

  • INDICATOR — identifier of the KPI indicator to which the analysis option applies.
  • ANALYSIS_GROUP_ID — the analysis group that scopes the option.
  • OPTION_ID — unique identifier of the analysis option.
  • PARENT_OPTION_ID, GRANDPARENT_OPTION_ID — self-referencing keys that establish the option hierarchy.
  • NAME, HELP — the translated display name and help text for the option.
  • DIM_SET_ID — the dimension set associated with the option, governing the analytic dimensions used.
  • USER_LEVEL0 — the top-level user-assignment attribute; subsequent columns USER_LEVEL1, USER_LEVEL1_DEFAULT, USER_LEVEL2, and USER_LEVEL2_DEFAULT extend this with lower-level user references and their default flags, controlling how options are personalized per user.

Common Use Cases and Queries

Typical use is retrieving a localized, hierarchical catalogue of KPI analysis options, including user-personalization metadata:

SELECT OPTION_ID, NAME, DIM_SET_ID, USER_LEVEL0,
       USER_LEVEL1, USER_LEVEL1_DEFAULT
FROM   BSC_KPI_ANALYSIS_OPTIONS_VL
WHERE  INDICATOR = :indicator
AND    ANALYSIS_GROUP_ID = :group_id
ORDER  BY GRANDPARENT_OPTION_ID, PARENT_OPTION_ID, OPTION_ID;

A second scenario filters on personalization, for example isolating options where a top-level user assignment exists:

SELECT OPTION_ID, NAME, USER_LEVEL0
FROM   BSC_KPI_ANALYSIS_OPTIONS_VL
WHERE  USER_LEVEL0 IS NOT NULL;

Because the object is obsolete and unimplemented in 12.1.1/12.2.2, these queries serve migration auditing and legacy reconciliation rather than active configuration.