Search Results bsc_sys_colors_b




Overview
Explore in AI Chat

Overview

BSC_SYS_COLORS_B is a configuration table in the Oracle E-Business Suite Balanced Scorecard (BSC) module, a component that is documented as obsolete in ETRM releases 12.1.1 and 12.2.2. The table stores the master list of color definitions and their associated rendering and scoring properties, and it is primarily consumed by the Performance Bands feature of Balanced Scorecard. Each row describes a single color that can be applied to a performance band so that scorecard results are displayed against a defined visual range.

The object is owned by the BSC schema and contains 15 documented columns. The ETRM metadata notes that the object is "Not implemented in this database," indicating that no physical deployment or seeded rows exist in the reference environment. The heuristic Data Vault classification mined from the foreign key structure is standalone. In Data Vault terms, this suggests the table behaves as a reference or lookup hub, where COLOR_ID functions as the durable business key and the surrounding descriptive attributes can be modeled as satellite context. Because it carries a single outbound foreign key to PON_COLORS and no inbound dependents, it does not exhibit link-table characteristics.

Key Information Stored

The surrogate and business-key structure is well defined by three unique indexes. BSC_SYS_COLORS_B_U1 enforces uniqueness on COLOR_ID, which is the primary identifier and also the column that references PON_COLORS. BSC_SYS_COLORS_B_U2 enforces uniqueness on SHORT_NAME, the human-readable label for the color. BSC_SYS_COLORS_B_U3 enforces uniqueness on PERF_SEQUENCE, which governs the ordering of colors when performance bands are evaluated or displayed.

  • COLOR_ID — surrogate primary key and foreign key to PON_COLORS; the durable identifier for the color record.
  • SHORT_NAME — business-key candidate (unique) holding the abbreviated display name of the color.
  • PERF_SEQUENCE — business-key candidate (unique) defining the ordinal position of the color within the performance band sequence.
  • COLOR — the base system color value applied to performance bands.
  • USER_COLOR — user-specific override of the base color.
  • FORECAST_COLOR — the color applied when displaying forecast performance.
  • USER_FORECAST_COLOR — user-specific override of the forecast color.
  • NUMERIC_EQUIVALENT — the numeric score associated with the color, used to translate band values into quantitative thresholds.
  • USER_NUMERIC_EQUIVALENT — the user-specific numeric score override.
  • USER_IMAGE_ID — identifier of an image associated with the color for graphical rendering.
  • Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN capture row lifecycle and accountability.

Common Use Cases and Queries

The dominant use case is resolving performance band colors for scorecard rendering, both in standard and forecast views, while respecting user-level overrides. Reporting queries typically join this table to PON_COLORS to retrieve the authoritative color definition and order results by PERF_SEQUENCE to reconstruct the band progression.

A representative pattern is:

  • SELECT sc.color_id, sc.short_name, sc.perf_sequence, sc.color, sc.user_color, sc.forecast_color, sc.numeric_equivalent FROM bsc_sys_colors_b sc ORDER BY sc.perf_sequence;
  • SELECT sc.short_name, sc.color, pc.<color_attribute> FROM bsc_sys_colors_b sc, pon_colors pc WHERE sc.color_id = pc.color_id;
  • SELECT short_name, numeric_equivalent FROM bsc_sys_colors_b WHERE user_numeric_equivalent IS NOT NULL;

Practical scenarios include auditing which users have overridden the default band colors, verifying that no gaps or duplicates exist in PERF_SEQUENCE, and validating that the numeric equivalents align with the threshold ranges used elsewhere in Balanced Scorecard scoring logic. Because the module is obsolete, most environments will find the table empty or absent, and queries should be guarded against ORA-00942 errors.

Related Objects

Relationship data identifies one foreign key relationship and several implicit dependencies through shared keys and column semantics:

  • PON_COLORS — referenced by BSC_SYS_COLORS_B.COLOR_ID; the upstream source of the base color definition.
  • BSC_SYS_COLORS_TL — the likely translation companion table in a _B/_TL pair, keyed by COLOR_ID.
  • Performance band configuration tables in the BSC schema — consumers of COLOR_ID and PERF_SEQUENCE.
  • BSC scorecard and dashboard presentation objects — read color and numeric attributes at render time.
  • BSC reporting views that join color metadata to performance results for banded visualization.

Given the obsolete status and the single documented foreign key, dependency analysis should focus on PON_COLORS and the corresponding _TL table; broader BSC object dependencies may not be materialized in current 12.1.1 and 12.2.2 installations.