Search Results bsc_sys_periodicities_vl




Overview
Explore in AI Chat

Overview

BSC_SYS_PERIODICITIES_VL is a language-enabled (VL) view within the Oracle E-Business Suite Balanced Scorecard (BSC) module, which is documented as obsolete in ETRM 12.1.1 and 12.2.2. The view presents periodicity definitions — the calendar granularities such as day, week, month, quarter, or year — used by the Balanced Scorecard and Enterprise Data Warehouse (EDW) integration layers to align fact data with time dimensions. As a "_VL" object, it merges the base descriptive columns of the periodicity entity with translated name values, resolving the display name according to the session language.

In EBS reporting and integration contexts, this view acts as a single read-only access point over the periodicity definition table and its translation table, so that concurrent programs, BSC dashboards, and downstream EDW extracts can retrieve a periodicity identifier, its structural attributes, and a localized label without issuing separate joins to the translation table.

Underlying Base Objects

The ETRM documentation identifies two base tables referenced by the view definition:

  • BSC_SYS_PERIODICITIES — the primary definition table holding periodicity attributes such as period counts, column names, flags, and calendar linkage.
  • BSC_SYS_PERIODICITIES_TL — the translation table providing the language-specific NAME value.
  • BSC_LOOKUPS — referenced in the first branch of the UNION to supply the MEANING value where the periodicity type corresponds to a lookup code with lookup type 'BSC_PERIODICITY'.

Because the view is defined as a UNION of two queries, its rows are populated under two distinct conditions: rows whose PERIODICITY_TYPE matches a 'BSC_PERIODICITY' lookup entry, and rows that are flagged as EDW-derived or custom (that is, EDW_FLAG <> 0 OR CUSTOM_CODE <> 0) and therefore supply their name from the TL table for the session language. The ETRM metadata notes "Not implemented in this database," meaning the view may be absent from installations where the obsolete BSC schema was never deployed or was removed during upgrade.

Key Columns

  • PERIODICITY_ID — primary identifier for the periodicity definition.
  • NUM_OF_PERIODS and NUM_OF_SUBPERIODS — the count of periods and subperiods represented by the periodicity.
  • PERIOD_COL_NAME and SUBPERIOD_COL_NAME — logical column names used when mapping period values into EDW or fact structures.
  • YEARLY_FLAG and EDW_FLAG — indicators distinguishing yearly periodicities and those derived from the Enterprise Data Warehouse.
  • CALENDAR_ID and EDW_PERIODICITY_ID — foreign keys linking the periodicity to a calendar and to its EDW counterpart.
  • CUSTOM_CODE — the column the user searched for; a flag or code identifying user-defined (custom) periodicities rather than seeded ones. Its presence in the WHERE clause of the translation branch means custom periodicities always derive their display name from BSC_SYS_PERIODICITIES_TL.
  • DB_COLUMN_NAME — the physical database column associated with the periodicity.
  • PERIODICITY_TYPE — the classification value used to join against BSC_LOOKUPS.
  • NAME — the localized display name, sourced either from BSC_LOOKUPS.MEANING or from BSC_SYS_PERIODICITIES_TL.NAME.

Common Use Cases and Queries

Typical uses include listing available periodicities for a calendar, isolating custom or EDW-sourced periodicities for migration review, and resolving localized names for reporting.

  • List all periodicities with their localized names:
SELECT periodicity_id, name, periodicity_type
FROM   bsc_sys_periodicities_vl
ORDER BY name;
  • Identify custom periodicities using the searched CUSTOM_CODE column:
SELECT periodicity_id, name, custom_code, db_column_name
FROM   bsc_sys_periodicities_vl
WHERE  custom_code <> 0;
  • Retrieve periodicities tied to a specific calendar for EDW mapping:
SELECT p.periodicity_id, p.name, p.num_of_periods, p.edw_periodicity_id
FROM   bsc_sys_periodicities_vl p
WHERE  p.calendar_id = :calendar_id
AND    p.edw_flag <> 0;

All queries should be validated against the target instance, since the view is documented as obsolete and may not exist in every EBS 12.1.1 or 12.2.2 environment.