Search Results bis_levels_tl_u1




Overview
Explore in AI Chat

Overview

BIS.BIS_LEVELS_TL is the translation (TL) table for the BIS_LEVELS dimension level definition in the Oracle E-Business Suite Business Intelligence System (BIS) schema. It stores the language-sensitive attributes of a dimension level — specifically the long name and description — for every row in the base BIS_LEVELS table, across each installed and supported language. The base table holds language-independent attributes, while this table carries the translated text rows; the pairing follows the standard Oracle EBS MLS (Multi-Language Support) pattern in which the "_TL" suffix denotes translated columns and a "SOURCE_LANG" column tracks the language a row currently mirrors before translation is complete.

The table is stored in the APPS_TS_TX_DATA tablespace with PCTFREE 10 and is maintained through the BIS design data layer (FND Design Data: BIS.BIS_LEVELS_TL). It is present and valid in both Oracle EBS 12.1.1 and 12.2.2. The object metadata classifies this table as standalone under the heuristic Data Vault classification. In Data Vault modelling terms, this could be represented as a satellite attached to the BIS_LEVELS hub, keyed by LEVEL_ID and driven by the LANGUAGE and audit (who/date) columns; the classification here is a modeling suggestion only, as no explicit Data Vault constructs are declared in EBS.

Key Information Stored

The table contains eleven documented columns. The most significant are:

  • LEVEL_ID (NUMBER, mandatory) — the dimension level identifier. It is the foreign key linking each translated row back to the base BIS_LEVELS/level definition, referencing MSD_LEVELS.LEVEL_ID.
  • LANGUAGE (VARCHAR2(30), mandatory) — the language code of the translated text, per Oracle MLS conventions (for example US, DE, FR).
  • NAME (VARCHAR2(255)) — the long name of the dimension level in the given language.
  • DESCRIPTION (VARCHAR2(1024)) — the descriptive text for the dimension level in the given language.
  • SOURCE_LANG (VARCHAR2) — the language whose text this row mirrors; if the text has not yet been translated into LANGUAGE, edits to the source-language row are reflected here.
  • TRANSLATED (VARCHAR2) — the flag indicating whether the row's text has actually been translated or is still mirroring the source language.

The remaining columns are the standard Oracle "Who" audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN. Concerning keys, the surrogate identifier for a translated row is the combination of LEVEL_ID and LANGUAGE, enforced by the unique index BIS_LEVELS_TL_U1 on (LEVEL_ID, LANGUAGE). The business-key candidate surfaced by the user's search, BIS_LEVELS_TL_U2, is a unique index on (LANGUAGE, NAME, LEVEL_ID) — it guarantees that within any single language the level NAME is unique, effectively making LANGUAGE + NAME the natural business key alongside the parent level identifier. Both indexes reside in the APPS_TS_TX_IDX tablespace.

Common Use Cases and Queries

The principal use case is retrieving the display name and description of a dimension level in the session's language, which is central to BI reporting, Discoverer worksheets, and any dimension-browsing UI. A typical query joins the base table to the translation table on LEVEL_ID and filters by LANGUAGE:

  • Fetch translated level text: SELECT l.level_id, t.name, t.description FROM bis.bis_levels_tl t WHERE t.level_id = :p_level_id AND t.language = USERENV('LANG'); — or filtered on an explicit code such as 'US'.
  • Detect untranslated rows for a localization pass: SELECT * FROM bis.bis_levels_tl WHERE translated = 'N' AND language = 'DE'; — the TRANSLATED and SOURCE_LANG columns drive translation-status reporting.
  • Resolve a level by its business name: query on the U2 business-key columns, ... WHERE language = 'US' AND name = :p_name, to map a human-readable level name back to its LEVEL_ID.
  • Audit current content: the SELECT provided in the object documentation lists all eleven columns and can be used as a reporting basis or joined to FND_USER via CREATED_BY and LAST_UPDATED_BY for change tracking.

Related Objects

  • BIS.BIS_LEVELS — the base (language-independent) level definition table; joined to BIS_LEVELS_TL on LEVEL_ID, forming the standard base/translation pair.
  • MSD_LEVELS — referenced by the foreign key on BIS_LEVELS_TL.LEVEL_ID; the authoritative source of the level dimension hierarchy.
  • BIS.BIS_LEVELS_TL# — the internal handling object that references BIS_LEVELS_TL, part of the BIS design metadata infrastructure.
  • FND_USER — referenced implicitly via CREATED_BY and LAST_UPDATED_BY for the standard Who columns.
  • FND_LOGINS — referenced implicitly via LAST_UPDATE_LOGIN for the initiating operating-system login.

Because the table is classified as standalone with no downstream dependents beyond BIS_LEVELS_TL#, its role in the BIS schema is as a leaf-level translation satellite of the level definition rather than a widely referenced parent. Consequently, all maintenance is driven from the base level and translation setup, and no direct transactional writes should be expected.