Results for “bsc_users”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BSC_USERS is the master user repository for the Oracle Balanced Scorecard (BSC) module, owned by the BSC schema. In Oracle EBS 12.1.1 and 12.2.2, it stores the identity and profile information of every user provisioned within the Balanced Scorecard application. The table acts as the central entity against which a large family of dependent configuration tables resolve their ownership columns, and it therefore underpins nearly every BSC personalization artifact, including analysis options, KPI periodicities, UI colors, and shell commands.

Based on the mined foreign-key topology, the Data Vault classification for BSC_USERS is best described as hub-leaning. The table is qualified by a single-column surrogate primary key, BSC_USERS_PK on USER_ID, and it is referenced by nine child tables through their own USER_ID columns, which is the classic signature of a hub. It also carries a single outbound reference to PSB_EMPLOYEES via EMPLOYEE_ID, so a small amount of descriptive linkage is present, but the dominant role is that of an identity hub.

Key Information Stored

The documented physical schema contains twenty columns in release 12.1.1. The columns of greatest functional importance are:

• USER_ID — the surrogate primary key (BSC_USERS_PK), and also the single documented unique business-key candidate (BSC_USERS_U1).
• USER_NAME and FULL_NAME — the login identifier and display name of the user.
• USER_TYPE — distinguishes categories of BSC user records.
• ENCRYPTED_USER_PASSWORD — the stored, encrypted authentication credential.
• EMPLOYEE_ID — the foreign key to PSB_EMPLOYEES that links the BSC user to the HR employee record.
• START_DATE and END_DATE — the effective dating window for the user account.
• EMAIL_ADDRESS, EMAIL_HOSTNAME, EMAIL_PROTOCOL, SMTP_HOSTNAME, and ENCRYPTED_EMAIL_PASSWORD — the mail delivery configuration associated with the user, used for scorecard notifications.
• DESCRIPTION — free-text annotation.
• AUTOLOGON_FLAG — indicates whether single sign-on or automatic logon behavior is permitted.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — the standard EBS audit columns.

Common Use Cases and Queries

Typical reporting and administrative scenarios include listing active scorecard users, resolving user identity for a scorecard record, and reconciling BSC users with HR assignments. A representative query joining the user to the employee record is:

• SELECT u.user_id, u.user_name, u.full_name, u.email_address FROM bsc.bsc_users u WHERE u.end_date IS NULL OR u.end_date > SYSDATE; — retrieve currently effective users.
• SELECT u.user_id, u.full_name, e.employee_number FROM bsc.bsc_users u, psb.psb_employees e WHERE u.employee_id = e.employee_id; — reconcile BSC users against HR employees.
• SELECT r.user_id, COUNT(*) FROM bsc.bsc_user_responsibility r GROUP BY r.user_id; — profile responsibility assignments per user.
• SELECT user_id FROM bsc.bsc_users WHERE user_name = :p_user_name; — resolve a login name to its surrogate key for downstream joins.

Related Objects

BSC_USERS is referenced by numerous child tables, all joining on USER_ID. The most significant are:

• BSC_APPS_USERS — links BSC users to underlying EBS application users (BSC_APPS_USERS.USER_ID → BSC_USERS.USER_ID).
• BSC_USER_INFO — extended profile attributes per user.
• BSC_USER_RESPONSIBILITY — responsibility assignments held by the scorecard user.
• BSC_KPI_ANALYSIS_OPT_USER — per-user KPI analysis options.
• BSC_KPI_CALCULATIONS_USER — per-user KPI calculation settings.
• BSC_KPI_DIM_LEVELS_USER — per-user KPI dimension level preferences.
• BSC_KPI_PERIODICITIES_USER — per-user KPI periodicity definitions.
• BSC_KPI_SHELL_CMDS_USER and BSC_UI_COLOR_USER — per-user shell command and interface color customizations.

All of these relationships resolve through the same USER_ID column, making BSC_USERS the single point of control for user-scoped Balanced Scorecard configuration.