Results for “bsc_sys_periodicities”

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

Overview

BSC_SYS_PERIODICITIES is a configuration and reference table in the Oracle E-Business Suite Balanced Scorecard (BSC) module. It stores periodicity definitions — the time-based cycles (monthly, quarterly, yearly, and similar) against which KPIs, benchmarks, scorecards, and reporting calendars are measured. In Oracle EBS 12.1.1 and 12.2.2 the table resides in the BSC schema and is documented as VALID, with a documented physical schema of 17 columns. It functions as the central registry that standardizes how time periods are named, counted, and mapped across the scorecard subsystem and the enterprise data warehouse (EDW).

From a dimensional modeling perspective, the mined relationship data classifies this table as hub-leaning within a Data Vault heuristic. That is a modeling suggestion only: the object behaves as a business-concept hub, holding a stable, uniquely identified set of periodicity definitions that many dependent tables reference by foreign key. It is not a transactional fact or a descriptive satellite; it is the anchor around which periodicity-dependent entities are linked.

Key Information Stored

The table is uniquely identified by the primary key constraint BSC_SYS_PERIODICITIES_PK, defined on the PERIODICITY_ID column. A separate unique index, BSC_SYS_PERIODICITIES_U1, is also documented on PERIODICITY_ID, which is the business-key candidate for this object. Because the surrogate and business keys coincide on the same column, PERIODICITY_ID serves as both the technical row identifier and the natural reference value propagated to child tables. The most significant columns include:

• PERIODICITY_ID — Primary key and business-key candidate; the value referenced by all foreign keys.
• SHORT_NAME — Abbreviated label used to identify the periodicity in lists and reports.
• NUM_OF_PERIODS — Count of periods within the cycle (for example, 12 for monthly, 4 for quarterly).
• NUM_OF_SUBPERIODS — Number of subdivisions within each period.
• PERIODICITY_TYPE and PERIOD_TYPE_ID — Classification attributes distinguishing the periodicity category.
• YEARLY_FLAG — Indicates whether the definition represents a yearly cycle.
• EDW_FLAG and EDW_PERIODICITY_ID — Flags and mapping values for enterprise data warehouse integration.
• CALENDAR_ID — Links the periodicity to an associated reporting calendar.
• PERIOD_COL_NAME, SUBPERIOD_COL_NAME, and DB_COLUMN_NAME — Physical column-name mappings used when resolving period labels in source data.
• CUSTOM_CODE, RECORD_TYPE_ID, and XTD_PATTERN — Extension and record-classification attributes supporting customization.

Common Use Cases and Queries

The principal use case is resolving periodicity definitions when building KPI or scorecard reports. A typical query joins the periodicity hub to dependent KPI data:

• SELECT p.PERIODICITY_ID, p.SHORT_NAME, p.NUM_OF_PERIODS, k.KPI_NAME FROM BSC_SYS_PERIODICITIES p, BSC_KPIS_B k WHERE p.PERIODICITY_ID = k.PERIODICITY_ID;
• Reporting calendars can be validated by joining BSC_REPORTING_CALENDAR to BSC_SYS_PERIODICITIES on PERIODICITY_ID to confirm that calendar entries use defined periodicities.
• Data warehouse reconciliation queries use EDW_FLAG and EDW_PERIODICITY_ID to confirm that local periodicities are mapped to warehouse equivalents before extraction.
• Administrators list all yearly periodicities with WHERE YEARLY_FLAG = 'Y' to audit scorecard cycle configuration.

Related Objects

BSC_SYS_PERIODICITIES is referenced by several significant BSC objects through the PERIODICITY_ID foreign key, making it a hub in the module's dependency graph:

• BSC_KPIS_B — Base KPI definitions reference PERIODICITY_ID to establish the measurement cycle.
• BSC_KPI_PERIODICITIES — Associates KPIs with allowed periodicities.
• BSC_SYS_BENCHMARKS_B — Benchmark definitions reference PERIODICITY_ID for comparative cycles.
• BSC_DB_TABLES — Maps database source tables to periodicity definitions.
• BSC_REPORTING_CALENDAR — Reporting calendar entries reference PERIODICITY_ID.
• BSC_RPT_KEYS — Report key definitions reference PERIODICITY_ID.

Each relationship above is anchored on the PERIODICITY_ID column, confirming the table's role as the shared periodicity reference for scorecard, benchmark, calendar, and reporting functionality.