Search Results bsc_sys_calendars_tl_pk

Overview

BSC_SYS_CALENDARS_TL is the translated (language-dependent) child table of the Oracle Balanced Scorecard calendar definition. It resides in the BSC schema and stores the human-readable, session-language-specific text for calendars that drive scorecard time hierarchies, period-level rollups, and KPI trending. In Oracle EBS 12.1.1 and 12.2.2, this table follows the standard EBS multi-language (ML) pattern: a base table, BSC_SYS_CALENDARS_B, holds the language-independent attributes, while BSC_SYS_CALENDARS_TL holds one row per calendar per installed language, keyed by the LANGUAGE column. This separation allows the same calendar definition to present localized names and help text without duplicating structural data.

From a Data Vault modeling perspective, the heuristic classification supplied in the metadata is standalone, meaning ETRM's FK analysis identified no inbound or outbound referential dependencies at the database level. Interpreted through Data Vault conventions, BSC_SYS_CALENDARS_TL is best modeled as a satellite attached to the BSC_SYS_CALENDARS_B hub, since it carries descriptive, language-qualified attributes (NAME, HELP, SOURCE_LANG) that describe a calendar business key rather than defining new relationships.

Key Information Stored

The primary key BSC_SYS_CALENDARS_TL_PK is a composite of CALENDAR_ID and LANGUAGE. CALENDAR_ID is the surrogate identifier inherited from the base calendar entity, and LANGUAGE identifies the installed Oracle language for the translated row. The unique index BSC_SYS_CALENDARS_TL_U1 covers the same two columns, confirming that one translation row exists per calendar per language.

• CALENDAR_ID — surrogate key linking the translation back to the base calendar record.
• LANGUAGE — the Oracle language code (for example, US or DE) qualifying this translated row.
• SOURCE_LANG — the language in which the source text was originally authored, used by the ML framework to detect the base-language row.
• NAME — the translated display name of the calendar shown to end users.
• HELP — translated help or descriptive text presented alongside the calendar.
• CREATED_BY, CREATION_DATE — standard EBS audit columns recording who created the translation row and when.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns capturing the most recent modification and the login session that applied it.

The audit columns follow the WHO column convention used throughout the EBS schema and should always be populated by the framework rather than directly.

Common Use Cases and Queries

Typical usage centers on presenting localized calendar names in Balanced Scorecard configuration and reporting. A standard ML join retrieves the correct translation for the current session language while falling back to the base language:

• Join BSC_SYS_CALENDARS_TL to BSC_SYS_CALENDARS_B on CALENDAR_ID, filtering on the LANGUAGE value returned by the EBS language context.
• Filter on SOURCE_LANG to isolate source-language rows for translation maintenance and XLIFF export.
• Report on translation completeness by comparing the count of translation rows per CALENDAR_ID across languages.
• Audit recent changes using LAST_UPDATED_BY and LAST_UPDATE_DATE to track translation edits over a period.

A representative reporting query pattern selects CALENDAR_ID, LANGUAGE, NAME, and HELP, applying a WHERE clause on LANGUAGE to present the localized name column in downstream BI or scorecard dashboards.

Related Objects

Given the standalone heuristic classification, foreign-key relationships are not enforced at the database level; associations are maintained through application logic and shared column names. The most significant related objects are:

• BSC_SYS_CALENDARS_B — the base calendar table; join on CALENDAR_ID to combine structural and translated attributes.
• BSC_SYS_CALENDARS_VL — the standard EBS ML view that unions the base and translation tables for convenient querying.
• BSC_SYS_CALENDAR_LINES — calendar period or line definitions that consume the parent calendar.
• BSC_SYS_PERIODS — period records aligned to calendar definitions.
• BSC_SCORECARD_* — scorecard definition tables that reference calendars by CALENDAR_ID for time-based measures.
• FND_LANGUAGES — provides the valid LANGUAGE codes and descriptions used for localization joins.

Because referential integrity is application-managed, joins should always be validated against the BSC base tables and FND_LANGUAGES during development and testing.