Results for “bsc_oaf_dim_levels_v”

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

Overview

BSC_OAF_DIM_LEVELS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the BSC (Balanced Scorecard) product family. It exposes the dimension levels associated with the Oracle Financial Services / Profitability Management (PMF) source, presenting a filtered projection of the underlying dimension level definitions for use by IBUILDER, the Oracle EBS integration and reporting framework. In release 12.1.1 and 12.2.2 the object retains a VALID status and is documented within the ETRM repository.

The view is not a physical table; it is a runtime query that joins column-level metadata with a PL/SQL function call to resolve hierarchical parent names. Because it filters on BSC_SOURCE = 'PMF', it isolates only those dimension levels originating from the Profitability Management source, shielding consumers from levels belonging to other scorecard sources. This makes it a stable, source-specific interface for tools and reports that require PMF dimension structure without needing to be aware of the broader BSC_BIS_DIM_LEVELS_V universe.

Underlying Base Objects

According to the documented view text, BSC_OAF_DIM_LEVELS_V is defined over a single referenced object: the view BSC_BIS_DIM_LEVELS_V. No other base tables are documented in the ETRM metadata. The dependency is therefore:

• BSC_BIS_DIM_LEVELS_V — the immediate source object supplying DIM_LEVEL_ID, SHORT_NAME, NAME, LEVEL_VIEW_NAME, BSC_SOURCE, and IMPORTED_FLAG. The outer view projects a subset of these columns and applies the BSC_SOURCE = 'PMF' predicate.
• BSC_OAF_VIEWS_PVT.GET_LEVEL_PARENT_NAMES — a PL/SQL package function invoked in the SELECT list to return the PARENTS value for each dimension level, given its DIM_LEVEL_ID.

Because the metadata records no documented underlying base tables beyond the intermediate view, the true table lineage is inherited through BSC_BIS_DIM_LEVELS_V. Any DBA impact analysis or reengineering exercise should trace through that intermediate view to reach the physical dimension level tables.

Key Columns

• DIM_LEVEL_ID — Surrogate identifier for the dimension level; the primary linkage key used by the parent-name function and by downstream IBUILDER metadata joins.
• SHORT_NAME — Abbreviated label for the level, commonly used in compact report layouts and integration keys.
• NAME — Full descriptive name of the dimension level.
• LEVEL_VIEW_NAME — Name of the database object or view that materializes the level, enabling dynamic query construction.
• PARENTS — Computed at runtime via GET_LEVEL_PARENT_NAMES(DIM_LEVEL_ID); supplies the parent hierarchy path for the level.
• SOURCE — Derived from BSC_SOURCE and constrained to 'PMF', identifying the originating source system.
• IMPORTED_FLAG — Indicates whether the level was imported rather than natively defined.

Common Use Cases and Queries

Typical scenarios include IBUILDER metadata enumeration, hierarchy browsing for PMF dimensions, and validation of level-to-view mappings before report execution.

List all PMF dimension levels with parents:

SELECT dim_level_id, short_name, name, level_view_name, parents
FROM   apps.bsc_oaf_dim_levels_v
ORDER  BY dim_level_id;

Locate the materialization view for a named level:

SELECT level_view_name
FROM   apps.bsc_oaf_dim_levels_v
WHERE  name = :level_name;

Identify imported levels:

SELECT dim_level_id, short_name, imported_flag
FROM   apps.bsc_oaf_dim_levels_v
WHERE  imported_flag = 'Y';

Because the view is defined in APPS and sourced exclusively from BSC_BIS_DIM_LEVELS_V with a fixed PMF filter, queries are safe for read-only integration and reporting, and no DML should be attempted against it.