Search Results dim_tl

Overview

APPS.BIS_DIMENSIONS_VL is a translated (VL, "view with language") database view in the Oracle E-Business Suite Business Intelligence System (BIS) schema. It is the user-facing, language-aware representation of the dimensional metadata that underpins the Oracle Daily Business Intelligence (DBI) and Enterprise Performance Management reporting products. Where the underlying BIS_DIMENSIONS table stores language-independent attributes of a business dimension, this view joins in the translated name and description from BIS_DIMENSIONS_TL and resolves them for the session's current language. As a result, reports, dashboards, and integration routines that query BIS_DIMENSIONS_VL automatically surface dimension labels in whatever language the logged-in user has selected, without the developer having to hand-code a language join.

The object is owned by APPS and, in the common EBS deployment pattern, is queried through synonyms exposed to reporting and custom schemas. It is a read-only projection: no DML is performed against the view itself.

Underlying Base Objects

The ETRM documentation for 12.2.2 identifies the view as being defined over two referenced base objects, both reached through synonyms:

• BIS_DIMENSIONS (SYNONYM) — the base table holding the language-independent dimension definition.
• BIS_DIMENSIONS_TL (SYNONYM) — the translation table holding the language-specific NAME and DESCRIPTION, keyed by DIMENSION_ID and LANGUAGE.

The join condition is DIM.DIMENSION_ID = DIM_TL.DIMENSION_ID. The join is therefore one-to-one for any given language, and the translation side is filtered by DIM_TL.LANGUAGE = USERENV('LANG'). USERENV('LANG') returns the language code of the current session, so the view always returns exactly one translated row per dimension, in the session language. Note that this is a strict inner join: if no BIS_DIMENSIONS_TL row exists for the session language, that dimension will not appear in the result set.

Key Columns

The view exposes a mixture of administrative columns from BIS_DIMENSIONS and translated content from BIS_DIMENSIONS_TL, plus a synthetic ROWID:

• ROW_ID — the ROWID of the BIS_DIMENSIONS row, exposed as a surrogate identifier.
• DIMENSION_ID — the primary key of the dimension and the join key to the translation table.
• DIM_GRP_ID — identifies the dimension group to which the dimension belongs.
• SHORT_NAME — the internal, language-independent short identifier for the dimension, typically the value used in code and column naming.
• NAME — the translated, user-facing display name of the dimension (from BIS_DIMENSIONS_TL).
• DESCRIPTION — the translated long description (from BIS_DIMENSIONS_TL).
• APPLICATION_ID — the owning application, enabling filtering by EBS application.
• HIDE_IN_DESIGN — a flag indicating whether the dimension should be hidden from the design-time UI.
• CREATION_DATE / CREATED_BY and LAST_UPDATE_DATE / LAST_UPDATED_BY / LAST_UPDATE_LOGIN — standard EBS WHO columns from the base table.

Common Use Cases and Queries

The view is most commonly used to build pick lists or to label dynamic reports where the underlying fact or level tables reference a dimension only by ID. Because the translation is resolved automatically, the same query returns locale-appropriate labels for every user without modification.

Listing available dimensions for an application:

• SELECT dimension_id, short_name, name, description FROM apps.bis_dimensions_vl WHERE application_id = :app_id AND NVL(hide_in_design,'N') = 'N' ORDER BY name;

Resolving a single dimension label for display given an ID:

• SELECT name, description FROM apps.bis_dimensions_vl WHERE dimension_id = :dim_id;

Because the view filters on USERENV('LANG'), a report run by a user whose session language lacks a translation will silently omit the dimension. When completeness matters, query BIS_DIMENSIONS directly or use an outer join to BIS_DIMENSIONS_TL rather than relying on the VL view.