Results for “bisfv_dimensions”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BISFV_DIMENSIONS is a read-only database view belonging to the BIS (Business Intelligence System / Applications BIS) product family in Oracle E-Business Suite. It exposes the master list of dimensional definitions used by the BIS/ETRM analytical and reporting framework, presenting each dimension together with its translated display name and description. In the context of Oracle EBS 12.1.1 and 12.2.2, the view functions as the reporting-facing access point to dimension metadata, shielding consumers from the underlying language-specific storage model while honoring the session's language setting.

The view is defined with a WITH READ ONLY clause, confirming that it is intended purely for query and reporting purposes. DML against it is not permitted; dimension maintenance must be performed through the base tables or through the corresponding Oracle EBS application forms and concurrent programs. This design is consistent with the ETRM metadata, which documents the object as a view with no implementation artifacts in the current database.

Underlying Base Objects

The view is defined over two BIS base tables joined on the DIMENSION_ID key:

• BIS_DIMENSIONS — the primary master table holding the internal dimension identifier, its short name, and the standard Oracle EBS WHO audit columns.
• BIS_DIMENSIONS_TL — the translation ("TL") table supplying the language-dependent NAME and DESCRIPTION attributes.

The join condition BIS_DIMENSIONS.DIMENSION_ID = BIS_DIMENSIONS_TL.DIMENSION_ID links each dimension to its translated row, while the predicate BIS_DIMENSIONS_TL.LANGUAGE = USERENV('LANG') restricts the result set to the language of the current database session. This is the standard EBS multi-language seed pattern: the base table stores the non-translatable attributes once, and the TL table stores one row per installed language.

The ETRM metadata records no other referenced base objects, so the view should be treated as a straightforward two-table projection rather than a layered or nested construct.

Key Columns

• DIMENSION_ID — the surrogate primary key that uniquely identifies each dimension; joins to the TL table and to other BIS dimension-related objects.
• DIMENSION_SHORT_NAME — the internal short name carried from BIS_DIMENSIONS.SHORT_NAME, used as a stable, language-independent identifier in code and reference data.
• DIMENSION_NAME — the user-facing, language-specific name of the dimension, sourced from BIS_DIMENSIONS_TL.NAME.
• DESCRIPTION — the translated descriptive text for the dimension, sourced from BIS_DIMENSIONS_TL.DESCRIPTION.
• CREATION_DATE, CREATED_BY — the standard WHO audit columns recording when and by whom the dimension row was created.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns recording the most recent modification, the responsible user, and the login session associated with that change.

Note that the view presents the short name under the alias DIMENSION_SHORT_NAME, whereas the underlying base table column is SHORT_NAME.

Common Use Cases and Queries

Typical uses include populating dimension selection lists in custom reports, joining dimension metadata to fact or summary data, and validating that a dimension is defined before loading data into BIS/ETRM reporting structures.

Listing all dimensions in the session language:

• SELECT dimension_id, dimension_short_name, dimension_name, description FROM bisfv_dimensions ORDER BY dimension_short_name;

Retrieving a single dimension by its internal name:

• SELECT dimension_name, description FROM bisfv_dimensions WHERE dimension_short_name = :p_short_name;

Auditing recently changed dimension definitions:

• SELECT dimension_short_name, dimension_name, last_updated_by, last_update_date FROM bisfv_dimensions WHERE last_update_date >= SYSDATE - 30;

Because the view filters on USERENV('LANG'), results are automatically presented in the language of the querying session, making it suitable for multi-language deployments without requiring additional application-level filtering.