Search Results bsc_sys_calendars_b_pk

Overview

BSC_SYS_CALENDARS_B is a foundational configuration table in the Oracle EBS Balanced Scorecard (BSC) module, owned by the BSC schema. It stores calendar information that governs how scorecard periods, fiscal boundaries, and time-based roll-ups are defined and evaluated throughout the application. Because the Balanced Scorecard aggregates performance metrics across user-defined time horizons, the calendar definitions held here drive period mapping, fiscal-year alignment, and the assignment of scorecard data to the correct reporting interval.

In Oracle EBS 12.1.1 the table is documented with sixteen physical columns and is present in the ETRM repository as a VALID object. The same structure carries forward into 12.2.2, where the BSC module retains its core data model. Access is normally read-mostly: calendars are seeded or configured once and then referenced repeatedly by the scoring, aggregation, and reporting engines.

From a Data Vault modeling perspective, the heuristic classification supplied for this object is standalone. No foreign-key relationships to parent tables were mined, which suggests the table functions as an independent reference or hub-like structure keyed on its natural business identifier, rather than as a link or satellite dependent on another entity. This classification should be treated as a modeling suggestion, not a definitive constraint, since BSC integration points to EDW objects are implemented through flag and identifier columns rather than enforced referential constraints.

Key Information Stored

The table is anchored by a surrogate primary key and a unique business-key index, with additional columns capturing fiscal and range attributes:

• CALENDAR_ID — the surrogate primary key defined by BSC_SYS_CALENDARS_B_PK, and also the column behind the unique index BSC_SYS_CALENDARS_B_U1. It uniquely identifies each calendar definition.
• SHORT_NAME — the user-facing label or code by which a calendar is recognized in configuration screens and reports.
• FISCAL_YEAR, FISCAL_CHANGE, CURRENT_YEAR — attributes describing the fiscal-year basis, whether fiscal periods diverge from the calendar year, and the currently active year.
• START_MONTH, START_DAY, RANGE_YR_MOD — parameters controlling when the fiscal or measurement period begins and how year ranges are modulo-aligned.
• EDW_FLAG, EDW_CALENDAR_ID, EDW_CALENDAR_TYPE_ID — integration columns indicating whether the calendar is published to the Enterprise Data Warehouse and providing the corresponding EDW identifiers and type classification.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns recording who created and last modified the record and when.

The distinction between surrogate and business key is important: CALENDAR_ID serves both roles here, but the pairing of the PK constraint and the U1 unique index confirms that it is the sole business-key candidate. No other column or column combination is documented as unique.

Common Use Cases and Queries

Typical uses center on resolving a calendar identifier to its fiscal attributes before applying date logic in scorecard queries. A common lookup pattern is:

• Selecting the active calendar and its fiscal parameters: SELECT calendar_id, short_name, fiscal_year, start_month, start_day FROM bsc.bsc_sys_calendars_b WHERE current_year = 'Y';
• Identifying calendars flagged for warehouse extraction: SELECT calendar_id, edw_calendar_id, edw_calendar_type_id FROM bsc.bsc_sys_calendars_b WHERE edw_flag = 'Y';
• Joining calendar attributes to scorecard period data to map a measure to the correct fiscal interval, using CALENDAR_ID as the join key.
• Audit and configuration review, filtering on LAST_UPDATE_DATE to detect recently changed calendar definitions.

Report developers frequently embed the calendar lookup as a scalar subquery when translating a stored calendar reference into a human-readable short name for scorecard dashboards.

Related Objects

The mined relationship data classifies this table as standalone, with no enforced foreign keys, so related objects are predominantly logical rather than constraint-backed. Significant dependencies include:

• BSC_SYS_CALENDARS_TL — the translation table holding language-specific calendar names and descriptions, joined on CALENDAR_ID.
• BSC scorecard period and measure tables that reference calendars through CALENDAR_ID to assign time context.
• Enterprise Data Warehouse staging objects referenced via EDW_CALENDAR_ID and EDW_CALENDAR_TYPE_ID.
• BSC administrative setup and concurrent programs that read calendar definitions when generating scorecard periods.

Because referential integrity is not enforced at the database level, join correctness depends on application logic, and developers should validate CALENDAR_ID values against BSC_SYS_CALENDARS_B before relying on downstream calendar references.