Search Results get_edw_select_string




Overview
Explore in AI Chat

Overview

BIS_PMF_GET_DIMLEVELS_PVT is a private PL/SQL package owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Oracle Business Intelligence System (BIS) / Enterprise Data Warehouse (EDW) product family. Its header identifies it as the "Private API for getting the Select String for DimensionLevelValues." The package resolves the SQL SELECT string that materialises dimension level values, sourcing that string from either the EDW or BIS repository depending on the setting of the BIS_SOURCE profile option. The "_PVT" suffix signals that the package is internal infrastructure rather than a public integration point; it is not intended for direct customer invocation. The source header records a history spanning December 2000 through March 2007, evidencing continuous maintenance for bug fixes (2617369, 2721710, 3388371, 3426427, 5920996, 5932973) and enhancements (2819971, 4456833, 5386112), including added support for BSC dimension levels and Simulation Tree (SM tree) filters and key items.

Key Procedures and Functions

  • GET_DIMLEVEL_SELECT_STRING — Returns the SELECT string used to retrieve dimension level values. Its declared record type, dim_level_select_rec_Type, was introduced to hold dimension level information returned from this API.
  • ISACCOUNTING_FLEXFIELD — Determines whether a given dimension is backed by an Accounting Flexfield, allowing the caller to apply key flexfield-specific handling.
  • GET_EDW_SELECT_STRING — Builds the dimension level select string from the EDW source, selected when the BIS_SOURCE profile option points to EDW.
  • GET_BIS_SELECT_STRING — Builds the equivalent select string from the BIS source, used when BIS_SOURCE points to BIS. Support for BSC dimension levels was added under bug 3388371.
  • GET_DIMLEVEL_VALUES_DATA — The principal data retrieval routine and the object matching the search term "get_dimlevel_values_data". It obtains dimension level value data, incorporating a common API adopted under bugs 4625598 and 4626579.
  • GET_DIMLEVEL_SELECT_WRAP — A wrapper routine around the select string logic, added under enhancement 4456833 together with output parameter x_is_relation_recursive, which indicates whether the parent-child relationship between dimension levels is recursive.
  • GET_DIM_LEVEL_SOURCE — Resolves which source (EDW or BIS) supplies dimension level information, effectively encapsulating the BIS_SOURCE profile evaluation.

Tables Accessed

  • AK_REGIONS — Attribute/region metadata used when resolving flexfield-backed dimension content.
  • BIS_LEVELS and BIS_DIMENSIONS — Core BIS dimension and level definitions that drive select string generation.
  • BSC_KPIS_B — Balanced Scorecard KPI definitions, referenced to support BSC dimension levels.
  • BSC_SYS_DIM_LEVELS_B and BSC_SYS_DIM_LEVEL_RELS — Scorecard system dimension level definitions and their parent-child relationships; the latter supports the recursive-relationship determination.
  • PLITBLM — The PL/SQL integer table type used to pass collections of identifiers between the package and its callers.

Usage Notes

Because this is a PVT package, it is invoked indirectly. The ETRM metadata records that eight other packages reference it, and its routines are called by higher-level BIS/EDW reporting APIs that need the correct dimension level select string before executing queries. Runtime behaviour is governed by the BIS_SOURCE profile option, which the package evaluates through GET_DIM_LEVEL_SOURCE; administrators must therefore ensure that profile is set consistently with the deployed EDW or BIS schema. Custom code should call the supported public BIS APIs rather than APPS.BIS_PMF_GET_DIMLEVELS_PVT directly, since private APIs may change between releases. In 12.1.1 and 12.2.2 the file remains BISVGDLS.pls at version 120.5.