Search Results bsc_edw_calendar_type_tl

Overview

BSC_EDW_CALENDAR_TYPE_TL is a translation (TL) table in the BSC schema, the database schema that supports Oracle E-Business Suite's Balanced Scorecard module. It stores translated, language-specific descriptive information for EDW calendar types. The base entity records reside in the non-translated companion table, while this TL table holds the human-readable, language-dependent attributes — specifically the calendar type name — for each supported language. In Oracle EBS, TL tables follow a standard pattern in which each row is uniquely identified by a combination of the entity's primary key identifier and a LANGUAGE code, enabling multilingual globalization across the application.

The heuristic Data Vault classification mined from the foreign-key structure is standalone. From a dimensional or Data Vault modeling perspective, this is best interpreted as a descriptive satellite-like object (or a standalone reference table) that augments the base calendar type entity with translated text attributes. Because no FK relationships were detected, the table is not modeled as a link or hub in the relationship graph; it functions as a dependent translation store keyed to its parent entity.

Key Information Stored

The documented physical schema for release 12.1.1 contains nine columns. The most significant are summarized below.

• EDW_CALENDAR_TYPE_ID — The surrogate primary key identifier for the parent EDW calendar type. In the composite primary key it identifies the calendar type being translated.
• LANGUAGE — The language code for the translated row. Together with EDW_CALENDAR_TYPE_ID it forms the composite primary key and the unique business-key candidate.
• NAME — The translated display name of the calendar type in the given language; the principal business attribute carried by this TL table.
• SOURCE_LANG — The source language of the row, used in the standard EBS MLD (MultiLingual Data) pattern to track the base language from which translations derive.
• CREATED_BY — The user identifier who created the translation row.
• CREATION_DATE — The date and time the row was created.
• LAST_UPDATED_BY — The user identifier who last modified the row.
• LAST_UPDATE_DATE — The date and time of the most recent update.
• LAST_UPDATE_LOGIN — The login identifier associated with the last update, used for audit and concurrency tracking.

The surrogate/primary key is BSC_EDW_CALENDAR_TYPE_TL_PK (EDW_CALENDAR_TYPE_ID, LANGUAGE). A separate unique index, BSC_EDW_CALENDAR_TYPE_TL_U1 (EDW_CALENDAR_TYPE_ID, LANGUAGE), is documented as a business-key candidate and mirrors the primary key composition. The last four audit columns (LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and their creation counterparts) are the standard WHO columns present on virtually all EBS transactional and reference tables.

Common Use Cases and Queries

This table is typically joined to its base (non-TL) parent on EDW_CALENDAR_TYPE_ID and filtered by LANGUAGE to obtain localized calendar type names for reporting, user interfaces, and extracts.

• Localized lookups: Retrieve the display name for a given calendar type in the session's or user's language.
• Multilingual reporting: Produce reports that show calendar type names in multiple languages, useful for global deployments.
• Data warehouse / EDW extracts: Because the table carries an EDW_ prefix, it feeds downstream reporting and analytics pipelines that require translated calendar type descriptions.
• Incremental synchronization: LAST_UPDATE_DATE supports delta extraction into external marts or integrations.

SELECT t.edw_calendar_type_id,
       t.language,
       t.name
  FROM bsc.bsc_edw_calendar_type_tl t
 WHERE t.language = USERENV('LANG')
   AND t.edw_calendar_type_id = :p_calendar_type_id;

For multilingual output, aggregate over the LANGUAGE column filtering on the required codes, joining back to the base table to confirm the calendar type is active.

Related Objects

• BSC_EDW_CALENDAR_TYPE_B (or equivalent base table) — the non-translated parent entity, joined on EDW_CALENDAR_TYPE_ID.
• BSC_EDW_CALENDAR_TYPE_TL_PK — the primary key constraint/index governing uniqueness of (EDW_CALENDAR_TYPE_ID, LANGUAGE).
• BSC_EDW_CALENDAR_TYPE_TL_U1 — the unique index backing the business-key candidate.
• Other BSC_EDW_*_TL tables — sibling translated reference tables following the same MLD pattern.
• FND_LANGUAGES — the EBS language reference table, conceptually joined on LANGUAGE to resolve language descriptions.
• FND_USER — referenced implicitly by CREATED_BY and LAST_UPDATED_BY for audit resolution.

Because the documented FK structure identifies this object as standalone, no formal foreign-key dependencies are recorded; joins are logical, driven by the shared EDW_CALENDAR_TYPE_ID and LANGUAGE keys.