Search Results bsc_user_kpi_access_u1

Overview

BSC.BSC_USER_KPI_ACCESS is a transactional table in the Oracle E-Business Suite Balanced Scorecard (BSC) module. It stores information about indicator (KPI) permissions granted to specific responsibilities, functioning as the authorization bridge that determines which Key Performance Indicators a given responsibility is permitted to view or manage. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its status is VALID in the documented ETRM 12.1.1 schema.

From a heuristic Data Vault modeling perspective, BSC_USER_KPI_ACCESS classifies as a standalone object rather than a strict hub, link, or satellite. Its composite primary key, BSC_USER_KPI_ACCESS_PK, is defined over (INDICATOR, RESPONSIBILITY_ID), which effectively makes it an associative structure binding a responsibility to an indicator with a validity window supplied by START_DATE and END_DATE. This resembles a link table with embedded satellite attributes, though the mined FK structure shows no enforced foreign keys to parent hubs. The table is referenced internally by the APPS synonym BSC_USER_KPI_ACCESS.

Key Information Stored

The table contains nine documented columns. The two business-key columns form the composite primary key and the unique index BSC_USER_KPI_ACCESS_U1, which is a NORMAL, UNIQUE index built on (RESPONSIBILITY_ID, INDICATOR) in the APPS_TS_TX_IDX tablespace.

• RESPONSIBILITY_ID (NUMBER, 15) — Responsibility identifier; the granting party in the permission model.
• INDICATOR (NUMBER) — Indicator (KPI) code that the responsibility is permitted to access.
• START_DATE (DATE) — Effective start of the permission grant.
• END_DATE (DATE) — Effective end of the permission grant; a null or future value indicates an active grant.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Who audit columns capturing insert and update provenance.

Note that RESPONSIBILITY_ID appears twice in the documented index metadata (once as the leading column); the unique business key is the pair (RESPONSIBILITY_ID, INDICATOR). No database-generated surrogate key column is documented, so the composite primary key itself serves as the row identity.

Common Use Cases and Queries

Typical uses include auditing KPI authorization, provisioning responsibility access, and reporting on indicator-to-responsibility mappings. Because the table has no enforced foreign keys, joins to responsibility and indicator masters must be performed by convention.

• List active KPI grants for a responsibility.
• Determine which responsibilities can view a given indicator.
• Identify grants expiring within a date window for recertification.

A representative query:

• SELECT RESPONSIBILITY_ID, INDICATOR, START_DATE, END_DATE FROM BSC.BSC_USER_KPI_ACCESS WHERE RESPONSIBILITY_ID = :resp_id AND (END_DATE IS NULL OR END_DATE > SYSDATE);
• SELECT INDICATOR, COUNT(*) FROM BSC.BSC_USER_KPI_ACCESS WHERE SYSDATE BETWEEN START_DATE AND NVL(END_DATE, SYSDATE + 1) GROUP BY INDICATOR;

Related Objects

The documented dependency metadata states that BSC_USER_KPI_ACCESS does not reference any database object via foreign keys, and it is referenced only by the APPS synonym BSC_USER_KPI_ACCESS. Consequently, join relationships are logical rather than enforced. Significant objects commonly joined in practice include:

• FND_RESPONSIBILITY — joined on FND_RESPONSIBILITY.RESPONSIBILITY_ID = BSC_USER_KPI_ACCESS.RESPONSIBILITY_ID to resolve responsibility names.
• BSC_INDICATORS / BSC_KPI definitions — joined on INDICATOR to resolve KPI metadata.
• FND_USER — through responsibility assignment to reach the effective end user.
• BSC_USER_KPI_ACCESS_U1 — the unique index supporting the primary access path.
• BSC_USER_KPI_ACCESS_PK — the composite primary key constraint.

Because the object is standalone, reconciliation and integrity checks should be performed against the responsibility and indicator masters rather than relying on declarative constraints within the BSC schema.