Search Results numeric_equivalent

Overview

BSC.BSC_SYS_COLORS_B is a transactional configuration table within the Oracle E-Business Suite Balanced Scorecard (BSC) module. It stores the catalog of colors and their associated properties, and its principal functional purpose is to supply the color definitions that drive Performance Bands in scorecard and dashboard rendering. Each row represents a color that can be assigned to a performance band, a forecast indicator, or a weighted-average calculation, with both a system-level value and a user-customized override.

The table is registered as an FND Design Data object owned by the BSC schema and resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. From a dimensional modeling perspective, the metadata classifies this object heuristically as standalone, meaning it participates in no dependent relationships within the Data Vault. Where a modeling suggestion is required, it is best treated as a small reference or lookup dimension: it carries a stable, low-cardinality key (COLOR_ID), descriptive attributes, and no transaction grain of its own.

The table is identical in structure across EBS 12.1.1 and 12.2.2. The primary key column COLOR_ID and the two secondary unique columns SHORT_NAME and PERF_SEQUENCE are each backed by a unique index in the APPS_TS_TX_IDX tablespace, as documented below.

Key Information Stored

The fifteen documented columns divide into identification, color value, numeric weighting, image, and audit groups. The most operationally significant are:

• COLOR_ID (NUMBER) — Surrogate primary key and the target of unique index BSC_SYS_COLORS_B_U1. It is also the column referenced by the foreign key relationship to PON_COLORS.
• SHORT_NAME (VARCHAR2(30)) — Business-key candidate enforced by unique index BSC_SYS_COLORS_B_U2; the human-readable identifier for the color.
• PERF_SEQUENCE (NUMBER) — Business-key candidate enforced by unique index BSC_SYS_COLORS_B_U3; denotes the significance of the color with respect to color calculation ordering.
• COLOR (NUMBER(10)) — The system-level color value used in rendering.
• USER_COLOR (NUMBER(10)) — The user-customized override of the system color, allowing site- or user-specific band coloring without altering the seeded definition.
• FORECAST_COLOR (NUMBER(10)) and USER_FORECAST_COLOR (NUMBER(10)) — Equivalent system and user values applied specifically to forecast color rendering.
• NUMERIC_EQUIVALENT (NUMBER) and USER_NUMERIC_EQUIVALENT (NUMBER) — Numeric values used in Weighted Average calculations, with the user variant overriding the seeded value.
• USER_IMAGE_ID (NUMBER(15)) — Identifier for the image icon associated with the color.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns recording insert and update ownership and timestamps.

The coexistence of system-level and USER_-prefixed columns is the defining characteristic of the table: seeded rows carry the delivered values, while user customizations are layered on top rather than replacing the base definition.

Common Use Cases and Queries

Typical reporting scenarios include determining the effective color for a performance band, auditing which colors have been customized by users, and validating the ordering of colors used in weighted-average score calculations.

• Retrieve the full color catalog in performance order:

  SELECT COLOR_ID, SHORT_NAME, PERF_SEQUENCE, COLOR, USER_COLOR
  FROM   BSC.BSC_SYS_COLORS_B
  ORDER  BY PERF_SEQUENCE;

• Identify user-customized colors where the override differs from the system value:

  SELECT SHORT_NAME, COLOR, USER_COLOR
  FROM   BSC.BSC_SYS_COLORS_B
  WHERE  USER_COLOR IS NOT NULL;

• Resolve the effective color, preferring the user value when present:

  SELECT COLOR_ID, SHORT_NAME,
         NVL(USER_COLOR, COLOR) AS EFFECTIVE_COLOR
  FROM   BSC.BSC_SYS_COLORS_B;

• Compute the effective numeric weighting for weighted-average scoring using NVL(USER_NUMERIC_EQUIVALENT, NUMERIC_EQUIVALENT).

Because SHORT_NAME and PERF_SEQUENCE are uniquely indexed, either may be used as an alternative lookup key in integration or migration scripts.

Related Objects

The documented relationship data for BSC_SYS_COLORS_B is limited, indicating a largely standalone reference table. The principal documented association is:

• PON_COLORS — Referenced through COLOR_ID, linking BSC color definitions to the common color catalog. Joins on COLOR_ID = PON_COLORS.COLOR_ID.
• APPS.BSC_SYS_COLORS_B — The APPS-layer synonym or view exposing the table to application code and the BSC scorecard runtime, which reads band colors, forecast colors, and weighted-average equivalents.

No other database objects are documented as referencing this table, and it does not itself reference any object other than through the PON_COLORS relationship. Consumers should therefore treat it as a self-contained lookup whose values are consumed indirectly by BSC scorecard rendering and Performance Band evaluation logic rather than by explicit foreign-key dependencies.