Search Results bsc_sys_periodicities

Overview

The BSC_SYS_PERIODICITIES table is a core reference table within the Oracle E-Business Suite Balanced Scorecard (BSC) application. It serves as the master repository for defining the time frequencies or intervals used for measuring and reporting on Key Performance Indicators (KPIs), benchmarks, and data sources. This table is fundamental to the BSC module's functionality, as it standardizes the periodicity definitions—such as Daily, Weekly, Monthly, Quarterly, or Yearly—that govern how performance data is aggregated, analyzed, and compared over time. Its role is to provide a centralized, controlled list of valid periodicities that ensure consistency across all scorecard components.

Key Information Stored

While the provided metadata does not list specific columns, the table's primary key and foreign key relationships define its critical structure. The central column is PERIODICITY_ID, a unique numeric identifier for each periodicity type, which serves as the table's primary key. Based on standard EBS design patterns, the table likely contains additional descriptive columns, such as a NAME or CODE (e.g., 'MONTHLY'), a DESCRIPTION, and potentially flags controlling data roll-up behavior and calendar alignment. The integrity of the PERIODICITY_ID is enforced by the primary key constraint BSC_SYS_PERIODICITIES_PK, making it the anchor for all related data.

Common Use Cases and Queries

The primary use case is to retrieve the list of available periodicities for configuration or reporting purposes. Administrators may query this table when setting up a new KPI to determine valid measurement frequencies. A common reporting need is to join this table to KPI data to translate the periodicity ID into a human-readable label. A typical query pattern would be:

• SELECT periodicity_id, name FROM bsc_sys_periodicities WHERE enabled_flag = 'Y' ORDER BY sequence_num;
• SELECT kpi.name, p.name AS periodicity FROM bsc_kpis_b kpi, bsc_sys_periodicities p WHERE kpi.periodicity_id = p.periodicity_id;

This table is also crucial for data validation, ensuring that any KPI or benchmark record references a periodicity that is officially defined within the system.

Related Objects

The BSC_SYS_PERIODICITIES table is referenced as a parent table by several key BSC module tables via foreign key relationships, as documented in the metadata. These relationships are:

• BSC_DB_TABLES.PERIODICITY_ID: Links to data source tables defined for scorecarding.
• BSC_KPIS_B.PERIODICITY_ID: Associates a periodicity with the base definition of a Key Performance Indicator.
• BSC_KPI_PERIODICITIES.PERIODICITY_ID: Likely used to define multiple allowed periodicities for a single KPI or for periodicity-specific thresholds.
• BSC_SYS_BENCHMARKS_B.PERIODICITY_ID: Assigns a measurement frequency to benchmark definitions.

These foreign key constraints ensure referential integrity, meaning a periodicity cannot be deleted if it is in use by any KPI, benchmark, or data table.