Search Results bsc_rpt_keys

Overview

BSC_RPT_KEYS is a standalone table within the BSC (Balanced Scorecard) product schema of Oracle E-Business Suite, documented as valid in both release 12.1.1 and 12.2.2. It resides in the BSC schema and carries the standard Oracle proprietary confidentiality designation. Functionally, the table stores report key definitions — the periodicity, calendar, and date-context identifiers that Balanced Scorecard reporting components use to resolve which time slice of data a given report, scorecard, or measure should be evaluated against. Because the BSC module aggregates performance indicators across configurable time dimensions, a centralized repository of report keys allows multiple reporting surfaces to share a consistent time context rather than deriving period logic independently.

The supplied metadata classifies BSC_RPT_KEYS heuristically as standalone under the Data Vault convention. As a modeling suggestion, this implies the table behaves as neither a pure hub nor a link, but rather as an independent reference structure whose only declared foreign key relationship points outward to BSC_SYS_PERIODICITIES. The documented physical schema contains 13 columns, several of which are denormalized date and period attributes rather than key columns, reinforcing the reference-data interpretation.

Key Information Stored

The table's identifying and business columns, as documented, include the following:

• SESSION_ID — a session-scoped identifier that groups a set of report keys generated or consumed within a single reporting run; it functions as a surrogate-style handle for the key set.
• PERIODICITY_ID — a foreign key to BSC_SYS_PERIODICITIES, defining the frequency granularity (for example, day, week, month, quarter, or year) to which the key applies.
• PERIOD_TYPE_ID — a reference to the period type classification, allowing the same calendar boundary to be interpreted under different period conventions.
• REPORT_DATE — the effective date stamped on the report key, serving as the primary date-context business attribute.
• PERIOD, YEAR, XTD_PERIOD, XTD_YEAR — explicit period and year values, including the "XTD" (extended or cross-year) variants used when a reporting window spans fiscal or calendar boundaries.
• XTD_PERIODICITY — the periodicity applicable to the extended period context, permitting a different granularity for the XTD portion of the window.
• PERIOD_FLAG and ROLLING_FLAG — indicator columns distinguishing fixed period keys from rolling or cumulative keys.
• DAY_COUNT — a numeric count of days encompassed by the key, frequently used in rolling or year-to-date computations.
• LAST_UPDATE_DATE — the standard Oracle audit column recording the most recent modification timestamp.

The metadata does not document a declared unique index or primary key constraint, so primary-key status should be confirmed against the live dictionary (for example, via DBA_CONSTRAINTS) before being assumed in join logic.

Common Use Cases and Queries

Typical applications include resolving the correct time dimension for scorecard reporting, reconciling period-to-date versus year-to-date figures, and validating that a report run used the intended periodicity. A common reporting pattern joins the periodicity master to obtain descriptive names:

• SELECT k.SESSION_ID, k.REPORT_DATE, k.PERIOD, k.YEAR, p.PERIODICITY_NAME FROM bsc.bsc_rpt_keys k, bsc.bsc_sys_periodicities p WHERE k.PERIODICITY_ID = p.PERIODICITY_ID AND k.SESSION_ID = :session_id;
• Filtering by PERIOD_FLAG or ROLLING_FLAG to isolate rolling windows from fixed periods.
• Aggregating DAY_COUNT to confirm the span of a year-to-date or extended-period key.
• Detecting stale key sets by inspecting LAST_UPDATE_DATE within a bounded range.

Because report keys are session-scoped, reconciliation queries often group by SESSION_ID to compare the set of keys produced across successive runs.

Related Objects

The following objects are the most significant dependencies, grounded in the documented relationship data:

• BSC_SYS_PERIODICITIES — the sole documented foreign key target, joined on PERIODICITY_ID; supplies periodicity definitions such as day, week, month, quarter, and year.
• BSC_SYS_PERIODS — the BSC period calendar, conventionally the source of PERIOD, YEAR, and PERIOD_TYPE_ID values referenced here.
• BSC_SYS_PERIOD_TYPES — period type definitions aligned to PERIOD_TYPE_ID.
• BSC_SCORECARD_* family — scorecard definition and instance tables that consume report keys to determine their time context.
• BSC_KPI_* family — KPI and measure tables that inherit periodicity and date context from the report key set.
• BSC_REPORT / BSC_REPORTS — report definition and output tables that reference session identifiers produced during key generation.

Relationship verification should always be performed against the live ETRM or database metadata, as the supplied excerpt documents only one explicit foreign key and no enumerated primary key.