Search Results msc_cal_year_start_dates_pk

Overview

MSC_CAL_YEAR_START_DATES is a table within the MSC schema (Advanced Supply Chain Planning) in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the yearly start dates for workday calendars used by the Advanced Supply Chain Planning engine. Planning calendars define which days are working days and which are non-working, and the yearly start date establishes the reference point from which each calendar's repeating workday pattern is projected forward for a given year. Without an accurate yearly start date, the planning engine cannot reliably identify working versus non-working days across a planning horizon, which in turn affects material availability, capacity loading, and scheduling calculations.

From a dimensional modeling perspective, the heuristic Data Vault classification mined from the foreign-key structure is standalone. This suggests the table acts as an independent reference or setup entity rather than a purely transactional satellite or a classical hub-and-link pair. It can therefore be treated as a descriptive reference set that planning processes read when resolving calendar-driven dates.

Key Information Stored

The table contains 14 documented columns. The most significant are the following:

• CALENDAR_CODE — identifies the workday calendar to which the yearly start date belongs. This is the primary business identifier for the calendar.
• SR_INSTANCE_ID — the source instance identifier, distinguishing records originating from different source systems or organization configurations.
• EXCEPTION_SET_ID — identifies the exception set associated with the calendar, which defines non-working days and deviations from the standard pattern.
• YEAR_START_DATE — the actual date marking the start of the calendar year for the workday calendar.
• REFRESH_NUMBER — a versioning or refresh sequence indicator, typically used to track collections or data refresh cycles.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns recording when, by whom, and through which login the row was last modified.
• CREATION_DATE, CREATED_BY — audit columns recording row creation metadata.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context columns identifying the program or request that created or last updated the record.

The primary key is MSC_CAL_YEAR_START_DATES_PK, defined on (CALENDAR_CODE, SR_INSTANCE_ID, EXCEPTION_SET_ID, YEAR_START_DATE). A separate unique index, MSC_CAL_YEAR_START_DATES_U1, is defined on (SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, YEAR_START_DATE), which represents the business-key candidate. Consequently, the table does not rely on a single surrogate key column; the business identity is composite, formed by the calendar, source instance, exception set, and year start date combination.

Common Use Cases and Queries

Typical use cases center on validating and reporting planning calendar configurations. Planners and implementers query this table to confirm that each active workday calendar has a correctly defined yearly start date, since a missing or incorrect entry can cause planning engine errors or unexpected working-day calculations.

A common query pattern retrieves all start dates for a given calendar and source instance:

• SELECT calendar_code, exception_set_id, year_start_date FROM msc.msc_cal_year_start_dates WHERE sr_instance_id = :instance AND calendar_code = :calendar ORDER BY year_start_date;

Another frequent pattern identifies calendars lacking a start date for a planning year, or lists all calendars refreshed by a specific concurrent program request ID. Because REFRESH_NUMBER and the standard audit columns exist, data lineage and change-history reporting are also feasible — for example, determining the most recent refresh that touched calendar definitions.

Related Objects

The table is classified as standalone in the FK relationship data, meaning it does not participate in documented foreign-key dependencies with other tables. It is, however, logically related to the broader calendar and planning configuration objects in the MSC schema through its business columns:

• MSC_CALENDARS — the parent calendar definition, joined on CALENDAR_CODE and SR_INSTANCE_ID.
• MSC_EXCEPTION_SETS — defines calendar exception sets, joined on EXCEPTION_SET_ID.
• MSC_WORKDAY_CALENDARS / workday calendar views — use YEAR_START_DATE to project workday patterns.
• Planning engine and collections concurrent programs — referenced via REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID.
• MSC_SR_INSTANCES — source instance definitions, joined on SR_INSTANCE_ID.

Because the table is read primarily as reference data during planning calendar resolution, joins are typically driven by the CALENDAR_CODE, SR_INSTANCE_ID, and EXCEPTION_SET_ID columns rather than by enforced referential constraints. Database administrators should therefore ensure data integrity through setup validation and collection processes rather than relying on database-level FK enforcement.