Results for “periodicity_id”

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

Overview

BSC_SYS_PERIODS_TL is the translated (language-specific) child table of the Balanced Scorecard system periods definition within Oracle E-Business Suite. It stores the translatable descriptive attributes — the period name and short name — for each period defined in the BSC periodicity calendar, keyed by language. The parent table BSC_SYS_PERIODS carries the language-independent period definition, while this table supplies the per-language name values rendered in the user interface and in Balanced Scorecard reports.

The product designation is BSC — Balanced Scorecard, and the ETRM metadata classifies the module as Obsolete. This is a critical caveat: the source documentation explicitly records that the table is not implemented in this database. The table therefore exists in the documented physical schema (owner BSC, 8 columns) but is not populated in the reference environment. Any integration or reporting design that assumes live BSC period translation data in a 12.1.1 or 12.2.2 instance should verify object existence and row counts before use, because the Balanced Scorecard module has been superseded by later EPM and GRC offerings.

Applying the heuristic Data Vault classification supplied with the metadata, BSC_SYS_PERIODS_TL is hub-leaning. This reflects the fact that its primary key consists solely of business keys rather than a single surrogate identifier, and that it participates as a referenced parent in foreign key relationships. In Data Vault modeling terms, the composite key (YEAR, PERIODICITY_ID, PERIOD_ID) behaves like a hub business key, with the language-dependent NAME and SHORT_NAME attributes functioning as satellite-style descriptive payload. This classification is a modeling suggestion only, derived from the FK structure, not an Oracle-imposed design.

Key Information Stored

The table is documented with eight columns, of which the following are most significant:

• YEAR — Identifies the calendar or fiscal year to which the period belongs. Part of both the primary key and the unique business key.
• PERIODICITY_ID — Identifies the periodicity definition (for example, monthly, quarterly, or weekly cycles) under which the period is grouped. Part of the primary key.
• PERIOD_ID — Identifies the individual period within the periodicity. Completes the three-part composite identifier.
• MONTH — Holds the month value associated with the translated period record. MONTH appears in the unique index but not in the declared primary key, which indicates that a periodicity may map to more than one month entry.
• LANGUAGE — The language code for the translated row. This is the discriminator that separates one row from another for the same period, and it is part of the unique business key.
• SOURCE_LANG — The language from which the translation was derived, enabling multilingual tracking and fallback behavior.
• NAME — The translated display name of the period, presented to users in the selected session language.
• SHORT_NAME — The abbreviated translated name, used in constrained layouts such as scorecard grids and selection lists.

The surrogate-style primary key is defined by constraint BSC_SYS_PERIODS_TL_PK on (YEAR, PERIODICITY_ID, PERIOD_ID). The business-key candidate is the unique index BSC_SYS_PERIODS_TL_U1 on (YEAR, PERIODICITY_ID, PERIOD_ID, MONTH, LANGUAGE). The distinction matters: the primary key does not include LANGUAGE, whereas the unique index does, confirming that multiple language rows can exist per period while remaining unique once MONTH and LANGUAGE are considered. A foreign key exposes this table to BSC_KPI_COMMENTS through the composite columns PERIODICITY_ID, PERIOD_ID, and YEAR.

Common Use Cases and Queries

The primary use case is resolving a period identifier to a human-readable, language-appropriate label. A typical reporting query joins the translation table to its parent and filters by the caller's language, with a fallback to the source language when a translation is missing:

• Period label lookup — select NAME and SHORT_NAME from BSC_SYS_PERIODS_TL for a given YEAR, PERIODICITY_ID, and PERIOD_ID, restricted to the desired LANGUAGE.
• Multilingual comparison — group by LANGUAGE to identify which translations are present or absent for a periodicity.
• KPI comment enrichment — join BSC_KPI_COMMENTS to this table on YEAR, PERIODICITY_ID, and PERIOD_ID to render the period label alongside comment text.
• Fallback resolution — use SOURCE_LANG to substitute the base-language NAME when no row exists for the requested LANGUAGE.
• Data-quality auditing — count distinct LANGUAGE values per (YEAR, PERIODICITY_ID, PERIOD_ID) to detect incomplete translations.

All such queries should first confirm that the table is present and populated in the target instance, given the documented "not implemented" status.

Related Objects

• BSC_SYS_PERIODS — The base (non-translated) period definition table and logical parent of BSC_SYS_PERIODS_TL, joined on YEAR, PERIODICITY_ID, and PERIOD_ID.
• BSC_KPI_COMMENTS — The documented foreign-key dependent table, referencing BSC_SYS_PERIODS_TL via PERIODICITY_ID, PERIOD_ID, and YEAR.
• BSC_SYS_PERIODS_TL_PK — The primary key constraint on (YEAR, PERIODICITY_ID, PERIOD_ID).
• BSC_SYS_PERIODS_TL_U1 — The unique index on (YEAR, PERIODICITY_ID, PERIOD_ID, MONTH, LANGUAGE), the business-key candidate.
• BSC_KPI_COMMENTS — periodicity and period context consumption for KPI commentary reporting.

Because the BSC module is documented as obsolete, dependent reporting should be migrated to supported performance-management schemas in current 12.2.2 deployments.