Search Results bsc_sys_colors_tl_u1

Overview

BSC.BSC_SYS_COLORS_TL is the translation (language-specific) table for system-level colors within the Oracle E-Business Suite 12.1.1 and 12.2.2 data model. It is owned by the BSC schema and is registered in FND Design Data as BSC.BSC_SYS_COLORS_TL. The table stores the multilingual name, description, and prototype-mode display label for each color definition, keyed by language. Because EBS is a multi-language application, translation tables such as this one isolate end-user-facing text from the base definition, allowing each installed language to present localized terminology without duplicating the underlying color entity.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10 and is indexed in APPS_TS_TX_IDX. It is a standalone object: it references no database object directly and is referenced only by its own synonyms and translations views. The dependency metadata records the foreign-key relationship COLOR_ID → PON_COLORS, indicating that the color master data originates in the PON (Purchasing) schema. From a Data Vault modeling perspective, this object is best described as a satellite — it carries descriptive, language-dependent attributes (NAME, DESCRIPTION, PROTOTYPE_LABEL) attached to the COLOR_ID business key, with LANGUAGE acting as an additional qualifier of that key.

Key Information Stored

The table contains 11 documented columns. The scalar grid below identifies the most significant attributes as defined in ETRM metadata:

• COLOR_ID (NUMBER) — Surrogate identifier for the color. It joins to PON_COLORS and, together with LANGUAGE, forms the unique business key BSC_SYS_COLORS_TL_U1.
• LANGUAGE (VARCHAR2) — The language of the translated row; the second component of the unique index and the discriminator that makes this a translation table.
• SOURCE_LANG (VARCHAR2) — The language from which the row was originally derived, supporting the standard EBS translation refresh process.
• NAME (VARCHAR2, 120) — System-level color name, exposed through the non-unique index BSC_SYS_COLORS_TL_N1 on (NAME, LANGUAGE) for name-based lookups.
• DESCRIPTION (VARCHAR2, 240) — System-level color description for the given language.
• PROTOTYPE_LABEL (VARCHAR2, 120) — Label used to render the color in prototype mode; this is the attribute most often targeted by developers searching for prototype-specific display text.
• CREATION_DATE, CREATED_BY — Standard audit columns capturing who created the row and when.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS "Who" columns recording the last modification audit trail.

Common Use Cases and Queries

Typical usage involves resolving color text for a specific language, generating translation audit reports, and inspecting prototype display labels. Because PROTOTYPE_LABEL is a translated attribute, any query must filter by LANGUAGE to return the correct row per installed language.

• Retrieve the display label for a color: SELECT NAME, DESCRIPTION, PROTOTYPE_LABEL FROM BSC.BSC_SYS_COLORS_TL WHERE COLOR_ID = :color_id AND LANGUAGE = USERENV('LANG');
• Detect missing translations: compare row counts per LANGUAGE against the primary language to find colors lacking translated NAME or DESCRIPTION values.
• Prototype-mode reporting: query WHERE PROTOTYPE_LABEL IS NOT NULL to list all colors that carry a dedicated prototype label.
• Name search: the BSC_SYS_COLORS_TL_N1 index supports lookups by NAME and LANGUAGE, useful for searching colors by their localized name.
• Join to master: combine with the PON_COLORS source using COLOR_ID to report the untranslated base definition alongside localized text.

Related Objects

The following objects are the most significant to consider alongside BSC_SYS_COLORS_TL:

• PON_COLORS — Parent color master; joined on BSC_SYS_COLORS_TL.COLOR_ID = PON_COLORS.COLOR_ID.
• BSC_SYS_COLORS_TL_U1 — Unique index on (COLOR_ID, LANGUAGE) enforcing the translation key.
• BSC_SYS_COLORS_TL_N1 — Non-unique index on (NAME, LANGUAGE) supporting name searches.
• FND_LANGUAGES — Language reference that validates the LANGUAGE and SOURCE_LANG values present in each row.
• APPS.BSC_SYS_COLORS_TL — The APPS-layer synonym through which EBS application code and reports normally access the table.