Search Results bsc_sys_calendars_b_u1

Overview

BSC.BSC_SYS_CALENDARS_B is a foundational configuration table in the Oracle EBS Balanced Scorecard (BSC) schema. It stores the definition of fiscal and calendar systems used throughout the Balanced Scorecard and Enterprise Data Warehouse (EDW) integration layer. Each row represents a calendar header, capturing the fiscal year, start month and day, current year, and the linkage to the corresponding EDW calendar and EDW calendar type. The table resides in the APPS_TS_TX_DATA tablespace and is owned by the BSC schema, with the FND Design Data reference BSC.BSC_SYS_CALENDARS_B confirming it is a seeded, application-managed object.

The object carries a VALID status and is documented as a standalone entity. The heuristic Data Vault classification mined from the foreign key structure is standalone, suggesting this object behaves as a reference or lookup table rather than participating in a classic hub, link, or satellite pattern. In a Data Vault modeling sense, it is best treated as a reference table feeding dimensions and hubs elsewhere in the warehouse, since it does not reference any database object itself.

Key Information Stored

The most significant columns define the identity and the fiscal orientation of each calendar record:

• CALENDAR_ID (NUMBER) — The primary key and surrogate identifier for each calendar definition. It is the leading column of the unique index BSC_SYS_CALENDARS_B_U1, which also serves as the documented business-key candidate.
• EDW_CALENDAR_ID (NUMBER) — The identifier of the corresponding calendar in the Enterprise Data Warehouse, enabling cross-system alignment.
• EDW_CALENDAR_TYPE_ID (NUMBER) — Classifies the calendar type (for example, fiscal versus reporting calendar) within the EDW model.
• EDW_FLAG (NUMBER) — Indicates whether the record is sourced from or synchronized with the EDW.
• FISCAL_YEAR (NUMBER) — The fiscal year to which the calendar definition applies.
• CURRENT_YEAR (NUMBER) — The currently active calendar year for the definition.
• FISCAL_CHANGE (NUMBER) — A flag indicating that the fiscal year has changed.
• RANGE_YR_MOD (NUMBER) — A flag indicating that the range of years has been modified.
• START_MONTH and START_DAY (NUMBER) — Define the fiscal calendar start point.
• SHORT_NAME (VARCHAR2, 30) — A user-facing abbreviated name for the calendar.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Who columns providing audit lineage for every row.

The surrogate primary key is CALENDAR_ID (BSC_SYS_CALENDARS_B_PK), while BSC_SYS_CALENDARS_B_U1 on CALENDAR_ID represents the documented unique business-key constraint. The table contains 16 documented columns.

Common Use Cases and Queries

Because the Balancd Scorecard aggregates performance data by fiscal period, this table is typically joined to period and fact tables to assign the correct fiscal year, start month, and EDW calendar context. A common pattern retrieves the active fiscal calendar for a given year:

• SELECT CALENDAR_ID, FISCAL_YEAR, START_MONTH, START_DAY, SHORT_NAME FROM BSC.BSC_SYS_CALENDARS_B WHERE FISCAL_YEAR = :p_year;
• Reporting queries join CALENDAR_ID to period and EDW mapping tables to translate calendar years into fiscal periods for scorecard dashboards.
• Data warehouse reconciliation scripts compare EDW_CALENDAR_ID and EDW_CALENDAR_TYPE_ID against EDW staging tables to detect synchronization gaps.
• The flags FISCAL_CHANGE and RANGE_YR_MOD support change-detection logic in concurrent programs that rebuild calendar hierarchies.

Related Objects

The metadata states that BSC_SYS_CALENDARS_B does not reference any database object, and that it is referenced by the APPS synonym BSC_SYS_CALENDARS_B. As a standalone reference table, the most significant related objects are those that consume CALENDAR_ID as a foreign key in the BSC schema:

• APPS.BSC_SYS_CALENDARS_B — the APPS-layer synonym providing runtime access.
• Period and time-dimension tables in the BSC schema that join on CALENDAR_ID to resolve fiscal periods.
• EDW calendar mapping tables aligned via EDW_CALENDAR_ID and EDW_CALENDAR_TYPE_ID.
• Balanced Scorecard fact tables and scorecard result views that filter or group by CALENDAR_ID.
• Concurrent programs and PL/SQL packages that populate or refresh calendar definitions using the FISCAL_CHANGE and RANGE_YR_MOD flags.

Join keys should center on CALENDAR_ID, with EDW_CALENDAR_ID used for enterprise warehouse reconciliation.