Search Results get_kpi_measure_threshold

Overview

BSC_COLOR_CALC_UTIL is a PL/SQL utility package in the APPS schema that supports the Oracle Balanced Scorecard (BSC) module of Oracle E-Business Suite. Its primary business function is to evaluate Key Performance Indicator (KPI) measurements against defined color ranges and derive the appropriate color status for scorecard objects, KPIs, and objectives. In Balanced Scorecard terminology, "color" represents the visual performance indicator — typically green, yellow, or red — that signals whether a measured value falls within acceptable thresholds. This package centralizes the logic for translating numeric KPI results and weighting schemes into those statuses, ensuring consistent color assignment across the scorecard application.

The package carries an API classification of UTIL, indicating it is a helper/utility library rather than a public application programming interface. It is validated in both Oracle EBS 12.1.1 and 12.2.2 and remains a core dependency for numerous higher-level BSC packages.

Key Procedures and Functions

The ETRM metadata documents six procedures and functions within this package:

• CALC_OBJ_COLOR_BY_DEFAULT_KPI — Calculates an object's color using the default KPI associated with that object, applying the standard color range logic.
• CALC_OBJ_COLOR_BY_SINGLE_KPI — Derives an object's color from the measurement of a single specified KPI, rather than a default or aggregated set.
• CALC_OBJ_COLOR_BY_WEIGHTS — Computes an object's color by aggregating multiple KPI measurements according to their configured weighting factors, producing a composite status.
• GET_KPI_MEASURE_THRESHOLD — Retrieves the threshold boundaries used to determine into which color range a given KPI measurement falls.
• GET_OBJ_COLOR_ROLLUP_TYPE — Returns the rollup method configured for an object, which governs how child KPI colors are consolidated into a parent object's color.
• GET_DEFAULT_KPI_MEASURE_ID — Returns the identifier of the default KPI measure associated with an object, used as input to the default-KPI color calculation.

These routines collectively cover both atomic color determination and composite rollup scenarios.

Tables Accessed

The package reads and writes several BSC configuration and results tables through APPS synonyms:

• BSC_COLOR_RANGES — Stores the color range definitions (threshold boundaries mapped to colors) used to classify measurements.
• BSC_KPIS_B — The base KPI definition table, supplying KPI metadata.
• BSC_COLOR_TYPE_PROPS and BSC_KPI_COLOR_PROPERTIES_PUB-related configuration tables such as BSC_KPI_PROPERTIES — Define color-related properties for objects and KPIs.
• BSC_KPI_ANALYSIS_MEASURES_B and BSC_KPI_MEASURE_PROPS — Hold KPI measure definitions and their properties.
• BSC_KPI_MEASURE_WEIGHTS — Stores weighting factors used by the weighted aggregation logic.
• BSC_SYS_COLORS_B, BSC_SYS_KPI_COLORS, and BSC_SYS_OBJECTIVE_COLORS — System-level color configuration and objective color associations.
• PLITBLM — A standard Oracle-supplied PL/SQL table type used internally.

Usage Notes

BSC_COLOR_CALC_UTIL is an internal utility invoked programmatically rather than directly by end users. It is referenced by at least nine other BSC packages, including BSC_CALCULATIONS_PUB, BSC_COLOR_PUB, BSC_KPI_PVT, BSC_KPI_SERIES_PUB, BSC_COLOR_UPGRADE, and BSC_KPI_COLOR_PROPERTIES_PUB. These callers trigger color calculation during KPI measurement processing, scorecard refresh, and scorecard display. It may also be called from custom code that needs to reproduce Balanced Scorecard color logic outside the standard flows. Because it depends on BSC_UPDATE_COLOR and is itself a dependency of widely used public packages, changes to this utility can cascade through the BSC color subsystem, so it should be treated as a stable internal component.