Results for “day_count”

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

Overview

The BSC.BSC_REPORTING_CALENDAR table is a repository object within the Balanced Scorecard module of Oracle E-Business Suite, present in both release 12.1.1 and 12.2.2. It stores the reporting calendar definitions that drive period-based scorecard aggregation, enabling the Balanced Scorecard engine to align fact data to defined reporting windows such as monthly, quarterly, or yearly boundaries. The table is classified as VALID and is owned by the BSC schema.

The object carries the standard Oracle internal-use restriction: Oracle Corporation does not support access to Oracle applications data using this object, except from standard Oracle Applications programs. Any direct querying should therefore be treated as a read-only, diagnostic activity rather than a supported integration path.

Under the heuristic Data Vault classification mined from its foreign-key structure, the table resolves as standalone. It does not participate in a parent-child FK relationship as a dependent child of a composite business key, nor does it anchor a substantial downstream FK fan-out. From a modeling perspective, it is best treated as a reference or lookup entity that supplies calendar context to scorecard calculations rather than as a transactional hub or link.

Key Information Stored

The table contains 16 documented columns. The most significant are:

• CALENDAR_ID — the surrogate primary key uniquely identifying each calendar record.
• CALENDAR_TYPE — classifies the calendar definition (for example, fiscal versus standard reporting calendars).
• REPORT_DATE — the effective date used to anchor the reporting period.
• PERIOD, YEAR — the period number and fiscal or calendar year to which the record belongs.
• PERIOD_TYPE_ID, PERIODICITY_ID — reference identifiers that bind the record to a periodicity definition such as weekly, monthly, or quarterly.
• RECORD_TYPE_ID — distinguishes the nature of the record within the calendar set.
• HIERARCHY — captures the organizational or rollup hierarchy associated with the calendar entry.
• DAY_COUNT — the number of days covered by the period.
• ROLLING_FLAG — indicates whether the calendar rolls forward automatically between periods.
• CREATED_BY, CREATION_DATE, LAST_UPDATE_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle WHO columns providing audit lineage.

The surrogate key is CALENDAR_ID. No alternate unique index is documented in the supplied metadata; business-key uniqueness is implied by the combination of calendar type, year, period, and periodicity rather than enforced by a named constraint.

Common Use Cases and Queries

Typical scenarios include verifying which reporting periods exist for a given year, reconciling scorecard results against declared period boundaries, and diagnosing periodicity mismatches when dashboard figures appear misaligned.

A representative diagnostic query joins the calendar to its periodicity reference:

• SELECT c.calendar_id, c.calendar_type, c.year, c.period, c.report_date, p.periodicity_id FROM bsc.bsc_reporting_calendar c, bsc.bsc_sys_periodicities p WHERE c.periodicity_id = p.periodicity_id AND c.year = :year;

Additional patterns include filtering on rolling_flag = 'Y' to identify auto-advancing calendars, and aggregating day_count by period_type_id to confirm that period definitions cover the expected span without gaps or overlaps.

Related Objects

The documented foreign-key relationship links the calendar to the periodicity reference table, and several supporting objects complete the calendar model:

• BSC_SYS_PERIODICITIES — referenced through BSC_REPORTING_CALENDAR.PERIODICITY_ID → BSC_SYS_PERIODICITIES.PERIODICITY_ID; the primary lookup supplying valid periodicity values.
• BSC_SYS_PERIOD_TYPES — presumed reference for PERIOD_TYPE_ID, defining the category of each period.
• BSC_SYS_RECORD_TYPES — presumed reference for RECORD_TYPE_ID, classifying calendar record variants.
• BSC_SYS_CALENDARS — the header-level calendar definition that aggregates individual reporting periods.
• BSC_SCORECARD_FACTS — fact tables whose period-based aggregation depends on the intervals defined here.
• BSC_ANALYTIC_CRITERIA — criteria definitions that select calendar ranges when producing scorecard results.

Because the object is marked Oracle internal use only, joins against these related tables should be confined to diagnostic and reporting contexts rather than embedded in custom application logic.