Search Results bsc_source




Overview
Explore in AI Chat

Overview

APPS.BSC_OAF_DIM_LEVELS_V is a reporting and integration view within the Oracle E-Business Suite Balanced Scorecard (BSC) module. It is defined as a filtered projection of the underlying dimension-level view BSC_BIS_DIM_LEVELS_V, restricted to rows where BSC_SOURCE equals 'PMF'. In Oracle EBS 12.1.1 and 12.2.2, this view exposes dimension-level metadata used by the Oracle Application Framework (OAF) layer to render and resolve dimension hierarchies in Balanced Scorecard and related analytical components.

The naming convention (BSC_OAF_...) indicates that the view serves as a bridge between the stored dimension-level definitions and the OAF-based user interface, allowing the application to enumerate dimension levels, resolve their display names, and derive parent relationships at runtime. Its role is primarily read-only: it surfaces dimension-level information for reporting, integration, and UI-driven queries rather than storing transactional data.

Underlying Base Objects

The view is defined directly over BSC_BIS_DIM_LEVELS_V, a Balanced Scorecard dimension-level view. The documented definition is:

Two dependencies are evident from this text:

  • BSC_BIS_DIM_LEVELS_V — the source view that supplies the base dimension-level rows. The outer view applies a filter on BSC_SOURCE to limit output to the 'PMF' source.
  • BSC_OAF_VIEWS_PVT.GET_LEVEL_PARENT_NAMES — a PL/SQL package function invoked per row to compute the PARENTS column, resolving the parent names for a given DIM_LEVEL_ID.

The ETRM metadata documents no additional base tables beyond these referenced objects. The underlying physical tables are encapsulated within BSC_BIS_DIM_LEVELS_V and are not exposed directly by this view.

Key Columns

  • DIM_LEVEL_ID — the primary identifier for a dimension level. This is the column most commonly searched and used to join to related dimension and hierarchy views.
  • SHORT_NAME — a concise code or abbreviation for the dimension level, suitable for compact display or programmatic reference.
  • NAME — the full descriptive name of the dimension level as presented to users.
  • LEVEL_VIEW_NAME — the name of the associated database view representing the level, used when constructing dynamic queries against level data.
  • PARENTS — derived by GET_LEVEL_PARENT_NAMES(DIM_LEVEL_ID); returns the parent level names for the dimension level, supporting hierarchy traversal and display.
  • SOURCE — the alias for BSC_SOURCE; always 'PMF' in this view because of the WHERE clause.
  • IMPORTED_FLAG — indicates whether the dimension level was imported from an external source rather than defined natively.

Common Use Cases and Queries

Typical uses include validating dimension levels for OAF pages, building hierarchy-aware reports, and integrating BSC dimension metadata with external systems. Because the view is pre-filtered to the 'PMF' source, queries do not need to add that predicate.

Retrieve a specific dimension level by identifier:

  • SELECT DIM_LEVEL_ID, SHORT_NAME, NAME, LEVEL_VIEW_NAME, PARENTS, IMPORTED_FLAG FROM APPS.BSC_OAF_DIM_LEVELS_V WHERE DIM_LEVEL_ID = :p_dim_level_id;

List all levels with their parent hierarchy for reporting:

  • SELECT NAME, SHORT_NAME, PARENTS FROM APPS.BSC_OAF_DIM_LEVELS_V ORDER BY NAME;

Identify imported levels:

  • SELECT DIM_LEVEL_ID, NAME FROM APPS.BSC_OAF_DIM_LEVELS_V WHERE IMPORTED_FLAG = 'Y';

Because GET_LEVEL_PARENT_NAMES executes per row, queries returning many rows may incur additional cost; restricting by DIM_LEVEL_ID or NAME is advisable where possible.