Search Results bsc_sys_calendars_tl_u1

Overview

BSC.BSC_SYS_CALENDARS_TL is a translation (TL) table in the Oracle EBS BSC schema that stores language-specific text for calendars defined in the Enterprise Resource Planning and Supply Chain Management modules. In Oracle EBS 12.1.1 and 12.2.2, the BSC schema is associated with Oracle Advanced Planning Command Center and related supply chain planning functionality. The _TL suffix indicates that this table holds translated descriptive attributes — specifically the display name and help text associated with each calendar — while the base attributes of the calendar are stored in the corresponding base table, BSC_SYS_CALENDARS_B.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its unique index BSC_SYS_CALENDARS_TL_U1 is stored in APPS_TS_TX_IDX. The primary key is defined as BSC_SYS_CALENDARS_TL_PK on (CALENDAR_ID, LANGUAGE). From a Data Vault modeling perspective, this object behaves as a satellite, since it carries descriptive, non-key attributes that are dependent on a business key and change over time; the heuristic classification provided is standalone.

Key Information Stored

The table contains ten documented columns. The two-column primary key (CALENDAR_ID, LANGUAGE) uniquely identifies each translated row, and the same columns form the unique business-key index BSC_SYS_CALENDARS_TL_U1. The most significant columns are:

• CALENDAR_ID (NUMBER) — Surrogate identifier linking the translation to the parent calendar in the base table.
• LANGUAGE (VARCHAR2) — Language code for the translation, forming the second half of the primary key.
• SOURCE_LANG (VARCHAR2) — The language mirrored by the text; if no translation exists for LANGUAGE, changes to the source-language row are reflected here.
• NAME (VARCHAR2, 150) — Calendar display name (maximum 50 characters of meaningful text).
• HELP (VARCHAR2, 240) — Help text associated with the calendar (maximum 80 characters of meaningful text).
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard Oracle "Who" audit columns tracking row creation and modification, all mandatory.

Common Use Cases and Queries

Reporting on calendar definitions is the principal scenario, particularly when localized names or help text must be surfaced to end users. A typical query joins the translation table to its base counterpart on CALENDAR_ID:

SELECT t.calendar_id, t.language, t.name, t.help
FROM   bsc.bsc_sys_calendars_tl t
WHERE  t.language = USERENV('LANG')
AND    t.source_lang = 'US';

To retrieve the complete set of documented columns, the ETRM supplies the following query text:

SELECT CALENDAR_ID, LANGUAGE, SOURCE_LANG, NAME, HELP,
       CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
       LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
FROM   BSC.BSC_SYS_CALENDARS_TL;

Other common uses include validating translation coverage (identifying rows where LANGUAGE differs from SOURCE_LANG), auditing changes through the Who columns, and extracting multilingual calendar metadata for downstream integration or BI reporting.

Related Objects

The ETRM dependency data shows that BSC_SYS_CALENDARS_TL does not reference any database object directly, but it is referenced by the APPS synonym BSC_SYS_CALENDARS_TL. The most significant related objects are:

• BSC.BSC_SYS_CALENDARS_B — The base table holding non-translated calendar attributes; joined on CALENDAR_ID.
• APPS.BSC_SYS_CALENDARS_TL — The APPS-level synonym that exposes the table to application code and reports.
• FND_LANGUAGES / FND_LANGUAGE_TL — Provides valid LANGUAGE and SOURCE_LANG values for translation joins.
• FND_TERRITORIES_TL — Frequently combined with calendar translations in locale-aware reporting.
• BSC_SYS_CALENDARS_TL_U1 — The unique index supporting business-key lookups on (CALENDAR_ID, LANGUAGE).

Because the table is classified as standalone, referential integrity is enforced at the application layer rather than through declared foreign keys, so joins to the base table should be performed explicitly in custom SQL.