Search Results bis_dimensions_vl




Overview
Explore in AI Chat

Overview

BIS_DIMENSIONS_VL is a seeded, valid database view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the BIS product family (Applications BIS), the schemas and objects that support Oracle's Business Intelligence and dimensional analysis functionality within EBS. The view presents a language-translated, consolidated definition of the "dimensions" maintained by the BIS layer — the named analytical axes (for example, time, organization, product, or other business qualifiers) that drive multidimensional reporting and aggregation.

The "_VL" suffix follows the standard EBS convention for a "view with language" object: it joins a base entity table with its translation table and filters the translation rows by the session's current language. As a result, the view behaves as a localization-aware interface rather than a stored table, returning a single logical row per dimension with the NAME and DESCRIPTION resolved into the user's active language.

Because it is a reporting-oriented view, BIS_DIMENSIONS_VL is primarily consumed by BIS reports, discovery-style queries, and integration logic that must present dimension metadata to end users. It centralizes joined and translated access to dimension definitions so that consumers do not need to perform the language join themselves. Note that the object is an APPS-owned synonym-backed view — the referenced base tables are exposed to APPS through synonyms — so querying it requires appropriate grants, typically through a reporting responsibility or an APPS-executed context.

Underlying Base Objects

As documented in the ETRM metadata for 12.2.2, the view is defined over two base objects, both of which are reachable from APPS through synonyms:

  • BIS_DIMENSIONS — the primary dimension definition table, holding the persistent, language-independent attributes. The view selects DIM from this object.
  • BIS_DIMENSIONS_TL — the translation table holding the language-dependent NAME and DESCRIPTION. The view selects NAME and DESCRIPTION from this object, aliased DIM_TL.

The two objects are joined on DIMENSION_ID, and the translation side is restricted by DIM_TL.LANGUAGE = USERENV('LANG'), so only rows matching the session language are returned. This is the canonical "_TL + _VL" pattern used throughout EBS for reference data that supports multiple installed languages.

Key Columns

The view text exposes the following attributes, whose documented names include:

  • DIMENSION_ID — the unique identifier of the dimension; the primary join key between BIS_DIMENSIONS and BIS_DIMENSIONS_TL.
  • DIM_GRP_ID — identifier of the dimension group to which the dimension belongs, allowing dimensions to be organized into logical groupings.
  • SHORT_NAME — the internal short code for the dimension, suitable for programmatic reference and display in constrained formats.
  • NAME — the translated display name of the dimension in the session language.
  • DESCRIPTION — the translated, longer descriptive text for the dimension.
  • HIDE_IN_DESIGN — a flag indicating whether the dimension should be concealed during design-time presentation, a UI/authoring concern.
  • APPLICATION_ID — the owning application identifier, used to associate the dimension with an EBS application.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard EBS "WHO" audit columns.

The documented metadata also lists ROW_ID among the columns. ROW_ID is the standard EBS row identifier used to support the Oracle Forms editioning/row-versioning model (typically based on the ROWID), and it is exposed for interface consistency with other EBS views.

Common Use Cases and Queries

Typical uses of BIS_DIMENSIONS_VL include: building pick lists of dimensions for a reporting or setup UI; resolving dimension IDs to human-readable names when displaying BIS report output; joining dimension definitions to fact or group metadata; and auditing which dimensions are defined and in which groups.

Enumerate the available dimensions with their translated names:

SELECT dimension_id,
       dim_grp_id,
       short_name,
       name,
       description
FROM   apps.bis_dimensions_vl
ORDER  BY short_name;

Retrieve a single dimension by its internal code:

SELECT dimension_id,
       short_name,
       name,
       description,
       hide_in_design
FROM   apps.bis_dimensions_vl
WHERE  short_name = :p_short_name;

List dimensions for a specific group, showing the owning application:

SELECT v.name,
       v.short_name,
       v.application_id
FROM   apps.bis_dimensions_vl v
WHERE  v.dim_grp_id = :p_dim_grp_id
ORDER  BY v.name;

Because presentation text is filtered by USERENV('LANG'), query results reflect the language of the connected session. The view should be treated as read-only; dimension definitions are created and maintained through BIS setup forms and their underlying base tables rather than through the view.