Search Results color_range_id

Overview

BSC.BSC_COLOR_RANGES is a transactional configuration table in the Oracle E-Business Suite Balanced Scorecard (BSC) module. As documented in the ETRM metadata for release 12.1.1 and 12.2.2, its stated purpose is "Table to store the color thresholds/ranges." It therefore functions as the persistence layer for the banding logic that drives conditional color formatting on scorecards, KPI dashboards, and strategy maps. Each row defines one threshold band: a lower bound (LOW), an upper bound (HIGH), the ordinal position of that band within its parent color range (COLOR_RANGE_SEQUENCE), and the color assigned to that band (COLOR_ID). Because it stores pure reference and configuration data rather than transacted business events, the heuristic Data Vault classification supplied with the metadata is standalone, suggesting a reference or lookup-style model rather than a hub, link, or satellite construct.

Key Information Stored

The table is defined in the BSC schema and resides in the APPS_TS_TX_DATA tablespace with PCTFREE 10. Five columns are documented:

• COLOR_RANGE_ID (NUMBER) — Identifies a color range grouping. Together with the sequence it forms the unique business key.
• COLOR_RANGE_SEQUENCE (NUMBER) — "Sequence to identify a threshold within a color range." This is the column the user searched for; it orders the individual bands inside a range so the application can evaluate them deterministically. It is the second component of the unique index.
• LOW (NUMBER) — "Lower Range Value," the inclusive or exclusive floor of the band.
• HIGH (NUMBER) — "Higher Range Value," the ceiling of the band.
• COLOR_ID (NUMBER) — "Color Id for the color range," a foreign key to the PON_COLORS table.

The single unique index, BSC_COLOR_RANGES_U1, is defined on (COLOR_RANGE_ID, COLOR_RANGE_SEQUENCE) and is stored in the APPS_TS_TX_IDX tablespace. There is no separately documented single-column surrogate primary key; the composite unique index is the effective business-key candidate. The COLOR_ID column carries the only documented foreign key, pointing to PON_COLORS.

Common Use Cases and Queries

The primary use case is rendering scorecard score values with the correct stoplight or spectrum color. A reporting query typically resolves each KPI value against the band table to determine which COLOR_ID applies:

• Retrieving all thresholds for a range: SELECT COLOR_RANGE_SEQUENCE, LOW, HIGH, COLOR_ID FROM BSC.BSC_COLOR_RANGES WHERE COLOR_RANGE_ID = :id ORDER BY COLOR_RANGE_SEQUENCE;
• Resolving the applicable band for a given metric value: SELECT COLOR_ID FROM BSC.BSC_COLOR_RANGES WHERE COLOR_RANGE_ID = :id AND :value BETWEEN LOW AND HIGH;
• Validating threshold integrity — detecting gaps or overlaps between adjacent bands by comparing each row's HIGH to the next row's LOW ordered by COLOR_RANGE_SEQUENCE.
• Joining to PON_COLORS to translate COLOR_ID into a display name or RGB value for dashboards and extracts.

Typical consumers are scorecard configuration screens, OBIEE or XML Publisher scorecard reports, and data-warehouse extracts that need to reproduce EBS native color thresholds.

Related Objects

• PON_COLORS — referenced by BSC_COLOR_RANGES.COLOR_ID; supplies the color definition applied to each band.
• APPS.BSC_COLOR_RANGES — the APPS-synonym or view layer through which the table is normally queried by report and page logic.
• BSC_COLOR_RANGES_U1 — the unique index on COLOR_RANGE_ID and COLOR_RANGE_SEQUENCE, the principal access path for threshold lookups.
• BSC scorecard/KPI definition objects — the surrounding BSC configuration entities that associate a range with a specific scorecard metric or indicator, allowing the band definitions here to be reused across indicators.

Because the table references no other BSC objects directly and is referenced only through the APPS synonym layer, its integration footprint is deliberately narrow: it is a self-contained threshold lookup consumed at runtime by the scorecard rendering and reporting logic.