Results for “measure_source”

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

Overview

BSC_KPI_DEFAULTS_VL is a view owned by the APPS schema in Oracle E-Business Suite, registered under the FND — Application Object Library product. It is part of the Oracle Balanced Scorecard (BSC) subsystem, which stores the definitions that drive KPI-based dashboards, scorecards, and analytic reporting. The view exposes KPI default definitions together with a decoded, human-readable period name. Its principal purpose is to translate internally stored period identifiers — encoded as a periodicity identifier concatenated with a descriptive token — into presentation-ready labels that are meaningful to end users and downstream reports.

The "_VL" suffix indicates that the view supplies a "value list" style, translation-friendly result set, joining the base definition table to its translation layer. This pattern is common in EBS where seeded data must be surfaced in the session language of the user. The view is documented as VALID in both 12.1.1 and 12.2.2. Notably, the requested search term "periodicity_type" appears directly within the view definition, where it is used as a discriminator to control how each period label is rendered.

Underlying Base Objects

Although the documented metadata lists no referenced base objects, the view text reveals its dependencies explicitly. The primary source is referenced by the alias B (the BSC_KPI_DEFAULTS_B definition table), which supplies TAB_ID and INDICATOR. The translation source is aliased TL (the BSC_KPI_DEFAULTS_TL table), supplying ANALYSIS_OPTION0_NAME, ANALYSIS_OPTION1_NAME, ANALYSIS_OPTION2_NAME, PERIOD_NAME, and SERIE. Two calendar-related tables are read in scalar subqueries: BSC_SYS_CALENDARS_B (alias C) and BSC_SYS_PERIODICITIES (alias P).

The join logic is significant. The numeric prefix of TL.PERIOD_NAME — everything before the first hyphen — is converted with TO_NUMBER and matched against P.PERIODICITY_ID. When the associated calendar is an EDW calendar type (EDW_CALENDAR_TYPE_ID = 1), the view returns the raw period suffix. Otherwise it retrieves PERIODICITY_TYPE from BSC_SYS_PERIODICITIES for that same PERIODICITY_ID and applies a DECODE that formats the label differently for each periodicity type value (1 through 9).

Key Columns

• TAB_ID — Identifier linking the KPI default to its scorecard tab or container.
• INDICATOR — The indicator (KPI) identifier to which the default applies.
• ANALYSIS_OPTION0_NAME, ANALYSIS_OPTION1_NAME, ANALYSIS_OPTION2_NAME — Translated labels for the three configurable analysis dimensions of the KPI.
• PERIOD_NAME — The decoded, presentation-formatted period label. The DECODE on periodicity_type determines whether this is a raw suffix, a month name, a month range, a month-with-year string, or another composite form.
• SERIE — The series identifier associated with the default, used to group or trend KPI data over time.

Common Use Cases and Queries

The view is typically queried when reporting KPI defaults by indicator, verifying how a stored period will appear to users, or reconciling calendar/periodicity configuration with displayed labels.

Basic lookup by indicator:

• SELECT tab_id, indicator, analysis_option0_name, period_name, serie FROM apps.bsc_kpi_defaults_vl WHERE indicator = :indicator;

Inspecting periodicity handling, which directly relates to the "periodicity_type" discriminator embedded in the definition:

• SELECT indicator, period_name FROM apps.bsc_kpi_defaults_vl WHERE period_name LIKE '%' ORDER BY indicator;

Joining back to the periodicity table to audit how each label was derived:

• SELECT v.indicator, v.period_name, p.periodicity_type FROM apps.bsc_kpi_defaults_vl v, apps.bsc_sys_periodicities p WHERE p.periodicity_id = TO_NUMBER(SUBSTR(v.period_name, 1, INSTR(v.period_name, '-')-1));

Because the view performs scalar subqueries and string parsing per row, large-scale extracts should be filtered by TAB_ID or INDICATOR where possible to limit full-scan cost on the underlying BSC tables.