Search Results bsc_sys_colors_tl

Overview

BSC.BSC_SYS_COLORS_TL is the translation table for colors within the Oracle Balanced Scorecard (BSC) module. In Oracle EBS 12.1.1 and 12.2.2, it stores the language-specific, translatable attributes — primarily the display name and description — for color definitions used throughout the Balanced Scorecard application. The base (language-independent) definition of each color resides in a companion table, while this _TL table supplies the localized text that end users see in their session language. This separation follows the standard Oracle EBS multi-language design pattern, where a base table holds the primary key and non-translatable attributes, and the corresponding _TL table holds one row per installed language per record.

The ETRM metadata classifies this object heuristically as standalone under Data Vault modeling (mined from its foreign key structure). As a modeling suggestion, it is better understood as a satellite-style translation artifact: it depends on a parent color definition and carries descriptive, context-dependent attributes (name, description, language) rather than representing an independent hub or a relationship link between two business entities.

Key Information Stored

The table contains 11 documented columns. The most important are described below.

• COLOR_ID — The surrogate identifier that links each translation row back to the underlying color definition. It is also the foreign key pointing to PON_COLORS, establishing the relationship between the BSC color translation and the shared Oracle color repository.
• LANGUAGE — The language code identifying which locale this translation row represents (for example, US for American English). Together with COLOR_ID, it forms the business-key candidate.
• NAME — The translated, user-facing name of the color, displayed in the scorecard user interface for the corresponding language.
• DESCRIPTION — The translated descriptive text for the color, providing additional context shown to end users.
• SOURCE_LANG — Indicates the source language from which the translation was derived or the language of the original definition.
• PROTOTYPE_LABEL — A label used in the application's prototyping or template framework, typically associated with the UI rendering of the color element.
• CREATION_DATE, CREATED_BY — Standard Oracle EBS audit columns recording when and by whom the translation row was created.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Audit columns capturing the most recent modification and the login context of the updating user session.

The unique index BSC_SYS_COLORS_TL_U1 (COLOR_ID, LANGUAGE) is the documented business-key candidate. It guarantees that each color has at most one translation row per language, and it distinguishes the composite business key from the surrogate key. No single-column surrogate primary key is documented separately, but COLOR_ID serves as the parent reference.

Common Use Cases and Queries

The primary use case is localized reporting and user-interface rendering of color names and descriptions for the Balanced Scorecard. When a scorecard renders color-coded status indicators, the application resolves the localized label through this table based on the user's session language. Typical queries join BSC_SYS_COLORS_TL to its base color table on COLOR_ID and filter by LANGUAGE.

SELECT t.color_id, t.name, t.description
FROM   bsc_sys_colors_tl t
WHERE  t.language = USERENV('LANG')
ORDER BY t.name;

For translation completeness audits, a common pattern identifies colors that lack a translation for a given language:

SELECT b.color_id
FROM   bsc_sys_colors b
WHERE  NOT EXISTS (
         SELECT 1 FROM bsc_sys_colors_tl t
         WHERE  t.color_id = b.color_id
         AND    t.language = 'US');

Reporting scenarios include generating multilingual dictionaries of color definitions, validating that translations exist for all supported languages, and joining to PON_COLORS to reconcile BSC color usage against the shared Oracle color master.

Related Objects

The following objects are most significant in relation to BSC_SYS_COLORS_TL, based on the documented foreign key relationships and standard BSC/EBS translation design.

• PON_COLORS — The parent color repository referenced by BSC_SYS_COLORS_TL.COLOR_ID. This is the documented foreign key target and the authoritative source of color definitions.
• BSC_SYS_COLORS — The base (non-translatable) color table for the Balanced Scorecard, joined on COLOR_ID to supply language-independent attributes.
• BSC_SYS_COLORS_TL_U1 — The unique index enforcing the COLOR_ID and LANGUAGE business key, critical for query performance and for preventing duplicate translations.
• FND_LANGUAGES — The EBS language reference table used to validate and describe the LANGUAGE codes stored in this table.
• FND_USER — Referenced through CREATED_BY and LAST_UPDATED_BY to resolve the identity of users who created or modified translation rows for audit reporting.

Because the metadata classifies this table as standalone, its dependency footprint is narrow: it is driven by the BSC color base table and the shared PON_COLORS repository, and it is consumed primarily by Balanced Scorecard UI and reporting components that require localized color labels.