Search Results user_level0




Overview
Explore in AI Chat

Overview

BSC.BSC_KPI_CALCULATIONS is a transactional configuration table in the Oracle E-Business Suite Balanced Scorecard (BSC) module. It records which calculation methods apply to each key performance indicator (KPI) and, critically, governs the visibility of those calculations across the three design levels supported by the BSC design tool. The table therefore acts as the bridge between a KPI definition and the reusable calculation logic that produces its values, while simultaneously carrying per-level display and default-selection flags.

The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, and its unique index BSC_KPI_CALCULATIONS_U1 is created in APPS_TS_TX_IDX. In Oracle EBS 12.1.1 and 12.2.2 the object is owned by the BSC schema and is exposed to the APPS layer through the APPS synonym BSC_KPI_CALCULATIONS. The ETRM metadata records a physical schema of eight columns.

Under the heuristic Data Vault classification mined from the foreign-key structure, this object is satellite-leaning. This suggests modeling it as a satellite attached to the KPI hub, since it carries descriptive attributes (visibility flags, default flags) keyed by the business identifiers of its parent entities rather than acting as an independent hub or a pure many-to-many link.

Key Information Stored

The table's substantive content is organized around a composite key and a set of control flags rather than free-form business data.

  • INDICATOR (NUMBER) — the indicator code. Part of the primary key and of the unique index BSC_KPI_CALCULATIONS_U1, and a foreign key to BSC_KPIS_B.
  • CALCULATION_ID (NUMBER) — the calculation identifier. Part of the primary key and the unique index, and a foreign key to BSC_SYS_CALCULATIONS.
  • USER_LEVEL0 (NUMBER) — visibility status of the associated calculation for design level 0.
  • USER_LEVEL1 (NUMBER) — visibility status for design level 1.
  • USER_LEVEL1_DEFAULT (NUMBER) — visibility default status for design level 1, controlling whether the calculation is presented by default at that level.
  • USER_LEVEL2 (NUMBER) — visibility status for design level 2.
  • USER_LEVEL2_DEFAULT (NUMBER) — visibility default status for design level 2.
  • DEFAULT_VALUE (NUMBER) — indicates whether the calculation is pre-selected by default.

Surrogate primary key: BSC_KPI_CALCULATIONS_PK on (CALCULATION_ID, INDICATOR). Business-key candidate: the unique index BSC_KPI_CALCULATIONS_U1 on (INDICATOR, CALCULATION_ID). Note that the two column orderings are reversed between the PK and the unique index; both enforce uniqueness over the same pair, so queries should not assume a single canonical ordering.

Common Use Cases and Queries

Typical reporting and diagnostic scenarios include determining which calculations are visible at a given design level, identifying default-selected calculations for an indicator, and reconciling indicator-to-calculation relationships before or after a scorecard design change.

To retrieve all calculations configured for one indicator:

SELECT INDICATOR, CALCULATION_ID, DEFAULT_VALUE,
       USER_LEVEL0, USER_LEVEL1, USER_LEVEL2
FROM   BSC.BSC_KPI_CALCULATIONS
WHERE  INDICATOR = :p_indicator;

To find calculations that are visible and defaulted at design level 1:

SELECT INDICATOR, CALCULATION_ID
FROM   BSC.BSC_KPI_CALCULATIONS
WHERE  USER_LEVEL1 = 1
AND    USER_LEVEL1_DEFAULT = 1;

To join to the calculation master and KPI masters for a full descriptive report:

SELECT k.INDICATOR, c.CALCULATION_ID, c.NAME,
       k.DEFAULT_VALUE, k.USER_LEVEL0, k.USER_LEVEL1
FROM   BSC.BSC_KPI_CALCULATIONS k,
       BSC.BSC_SYS_CALCULATIONS c,
       BSC.BSC_KPIS_B b
WHERE  k.CALCULATION_ID = c.CALCULATION_ID
AND    k.INDICATOR = b.INDICATOR;

Because the table is a satellite, it should normally be queried in the context of its parent hub keys rather than scanned standalone. Also note that user-specific overrides are held separately in BSC_KPI_CALCULATIONS_USER, so effective visibility for a named user may require unioning the two sources.

Related Objects

The following objects are the most significant relationship participants for this table.

  • BSC.BSC_KPIS_B — referenced by BSC_KPI_CALCULATIONS.INDICATOR. The KPI master; provides the indicator definition behind each calculation assignment.
  • BSC.BSC_SYS_CALCULATIONS — referenced by BSC_KPI_CALCULATIONS.CALCULATION_ID. The calculation master defining available calculation methods.
  • BSC.BSC_KPI_CALCULATIONS_USER — references this table via INDICATOR; stores per-user visibility overrides that layer over the base USER_LEVEL settings.
  • APPS.BSC_KPI_CALCULATIONS — the APPS-layer synonym through which the table is normally accessed in PL/SQL and SQL within EBS.
  • BSC_KPI_CALCULATIONS_PK / BSC_KPI_CALCULATIONS_U1 — the primary key and unique index that enforce uniqueness and drive typical index lookups on INDICATOR and CALCULATION_ID.

The table references no other database objects beyond the two foreign keys listed above, and it is referenced only by the APPS synonym and by BSC_KPI_CALCULATIONS_USER.