Search Results get_dimlevel_select_string




Overview
Explore in AI Chat

Overview

APPS.BIS_PMF_GET_DIMLEVELS_PVT is a private PL/SQL package in Oracle E-Business Suite that provides the internal API layer for resolving dimension level values used by Oracle Business Intelligence System (BIS) and Daily Business Intelligence reporting. Its central responsibility is to build and return a SQL SELECT string that retrieves the members (values) belonging to a particular dimension level, such as a cost center, product category, or accounting segment hierarchy. The package abstracts the physical source of that data: depending on the profile option BIS_SOURCE, the select string is generated against either the Enterprise Data Warehouse (EDW) tables or the BIS summary tables, allowing the calling code to remain independent of the underlying data model.

The source header (BISVGDLS.pls, version 120.5, last updated 2007/04/02) describes the object as a "Private API for getting the Select String for DimensionLevelValues." The PVT designation confirms it is not a public interface; it is intended for use by other BIS packages rather than by external integrations. ETRM records that the package is referenced by eight other packages, reflecting its role as a shared low-level utility within the BIS dimension framework. Successive enhancements added support for BSC dimension levels, simulation tree (SM tree) filters and key items, accounting flexfield handling, and recursive relationship detection.

Key Procedures and Functions

  • GET_DIMLEVEL_SELECT_STRING — The primary entry point that returns the SELECT statement for a given dimension level. This is the routine associated with the search term "get_dimlevel_select_string." It accepts parameters governing the level, optional distinct handling, and returns information including whether the level relationship is recursive.
  • GET_DIMLEVEL_SELECT_WRAP — A wrapper around the select-string logic, introduced to expose the additional recursive-relationship indicator and to standardize invocation across consumers.
  • GET_EDW_SELECT_STRING — Builds the select string against the EDW source tables when the BIS_SOURCE profile indicates the warehouse as the data origin.
  • GET_BIS_SELECT_STRING — Builds the equivalent select string against the BIS tables when the BIS source is selected.
  • GET_DIMLEVEL_VALUES_DATA — Retrieves the actual dimension level value data, applying the common API adopted to unify dimension level value retrieval.
  • GET_DIM_LEVEL_SOURCE — Determines which physical source (EDW or BIS) supplies the dimension level, resolving the BIS_SOURCE profile option.
  • ISACCOUNTING_FLEXFIELD — Indicates whether the dimension level is backed by an accounting flexfield, so callers can apply flexfield-specific logic.

Several record and table types are declared to carry dimension level information, including a record type for dimension level select results (added under Bug 2721710) and record/table types for child and parent dimension level information (enhancement 2819971).

Tables Accessed

  • BIS_LEVELS — Stores the defined levels within each BIS dimension; the package reads level identifiers and attributes when constructing select strings.
  • BIS_DIMENSIONS — Provides dimension definitions that qualify the levels being resolved.
  • BSC_SYS_DIM_LEVELS_B and BSC_SYS_DIM_LEVEL_RELS — Balance Scorecard system dimension level and relationship tables, used to support BSC dimension levels and parent/child relationships.
  • BSC_KPIS_B — Supplies KPI metadata associated with BSC dimensions where dimension levels feed KPI reporting.
  • AK_REGIONS — Referenced for region definitions linked to dimension level presentation in BIS/EDW pages.
  • PLITBLM — The PL/SQL integer table type, used internally for bulk collections of identifiers.

Usage Notes

Because this is a PVT package, it is not called directly from forms or concurrent programs. It is invoked by other BIS packages that need a ready-made SELECT string, typically during rendering of BIS/EDW dashboard pages, dimension value lists, or simulation tree filters. Callers pass the target dimension level and receive both the SQL text and metadata such as the recursive-relationship flag. Custom code should avoid depending on this package's signatures, as they are internal and have changed repeatedly across patches. The BIS_SOURCE profile option is the key configuration switch: setting it determines whether generated queries target the EDW or BIS tables, which affects both performance and the freshness of the dimension values returned.