Search Results get_sorted_select_string




Overview
Explore in AI Chat

Overview

The APPS.BIS_PMF_GET_DIMLEVELS_PUB package body is a public PL/SQL API within the Oracle E-Business Suite (EBS) covering releases 12.1.1 and 12.2.2. It belongs to the Business Intelligence System (BIS) schema family, specifically the Performance Management Framework (PMF) component, as indicated by the BIS_PMF_ naming prefix. The package provides a public interface for dynamically constructing SQL SELECT statements that resolve dimension level hierarchies used by the Performance Management Framework's analytical and reporting infrastructure. In the EBS architecture, dimension levels describe the hierarchical structure of business dimensions (such as Time, Organization, Product, and Customer) that underpin balanced scorecard, KPI, and business intelligence reporting functionality. The package is classified as a PUB API, meaning it is intended to be called by external consumers rather than being restricted to internal use.

Key Procedures and Functions

The ETRM documentation records two documented procedures/functions within this package body:

  • GET_DIMLEVEL_SELECT_STRING — Builds and returns a SQL SELECT statement string that resolves the dimension level information required by the calling process. This member is the primary entry point for consumers needing a complete dimension-level query construct.
  • GET_SORTED_SELECT_STRING — Returns a variant of the SELECT statement string that incorporates ordering logic, allowing callers to obtain dimension level data in a deterministic, sorted sequence. This is typically used where the presentation layer requires a specific display order of dimension levels.

Both procedures are documented without a published parameter list in the ETRM metadata. The package delegates core implementation logic to the private companion package BIS_PMF_GET_DIMLEVELS_PVT, which is listed as a dependency. The _PUB / _PVT split is a standard Oracle EBS design pattern: the public wrapper enforces interface stability and message handling, while the private package contains the internal logic and SQL construction.

Tables Accessed

The ETRM metadata for this package body does not list any base tables referenced directly. This is consistent with the pattern described above: because the public package delegates SQL construction to BIS_PMF_GET_DIMLEVELS_PVT, any table access against the PMF dimension and level definition tables is mediated through the private package. The public package body itself declares dependencies only on other PL/SQL units — BIS_PMF_GET_DIMLEVELS_PVT, FND_MSG_PUB (the standard Oracle Application Object Library message-handling package used for error reporting), and the SYS STANDARD package. Developers diagnosing data access should therefore inspect the dependent private package rather than this public body.

Usage Notes

This package is typically invoked by the Performance Management Framework's internal engine and its dependent packages. The ETRM metadata records that APPS.BIS_PMF_GET_DIMLEVELS_PUB is referenced by 7 other packages, confirming it is a shared utility within the PMF / BIS stack. While the documented metadata notes that the object "is not referenced by any database object" in the reverse-dependency listing shown, the referenced-by count of seven packages reflects its active use by PL/SQL consumers.

Typical invocation scenarios include:

  • PMF scorecard and KPI definition processing, where dimension level metadata must be resolved at runtime.
  • Custom reporting or extensions that require programmatically generated SELECT statements over dimension levels.
  • Internal PMF administration and setup routines executed from concurrent programs or PL/SQL batch jobs.

As a _PUB API, it should be called rather than modified. Because it relies on FND_MSG_PUB, callers should follow standard EBS error-handling conventions, checking the message stack after execution. Direct form-level invocation is not documented; the package is primarily consumed from PL/SQL code paths and concurrent processing within the Business Intelligence/PMF module. In 12.1.1 and 12.2.2 the object status is VALID, indicating it is a supported, compiled component of the APPS schema.