Search Results msc_cal_week_start_dates_pk

Overview

MSC_CAL_WEEK_START_DATES is a table in the MSC schema owned by Oracle Advanced Supply Chain Planning (ASCP). It stores the weekly start dates for workday calendars used by the planning engine. In Oracle EBS 12.1.1 and 12.2.2, ASCP relies on calendar definitions to translate planning buckets, lead times, and capacity windows into discrete workdays. This table supplies the anchor points — one row per calendar, source instance, exception set, and week — that the planning engine uses to roll forward and backward through time when building supply and demand plans.

Because ASCP may collect data from multiple source instances (for example, multiple EBS instances or a mix of EBS and legacy sources), the table is keyed by SR_INSTANCE_ID in addition to the calendar identifier. EXCEPTION_SET_ID allows alternative calendar variants — typically holiday or shutdown exception sets — to define their own weekly boundaries without disturbing the base calendar. The heuristic Data Vault classification for this object is standalone, meaning it is modeled as an independent reference table with no enforced foreign-key links to other tables in the documented FK structure. In a Data Vault model it would most naturally be treated as a reference or lookup hub keyed on the calendar business key.

Key Information Stored

The table contains 17 documented columns. The primary key, MSC_CAL_WEEK_START_DATES_PK, is a composite business key rather than a generated surrogate: (CALENDAR_CODE, SR_INSTANCE_ID, EXCEPTION_SET_ID, WEEK_START_DATE). A unique index, MSC_CAL_WEEK_START_DATES_U1, mirrors the same column set in a slightly different order (SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, WEEK_START_DATE), confirming that no surrogate identifier is used and that all four columns together constitute the row’s business identity. The most important columns are:

• CALENDAR_CODE — Identifies the workday calendar to which the week belongs; a core planning reference.
• SR_INSTANCE_ID — Source instance identifier, allowing calendars from multiple source systems to coexist.
• EXCEPTION_SET_ID — Distinguishes the base calendar from a specific exception set (for example, holidays).
• WEEK_START_DATE — The start date of the week; the anchor for bucket calculations.
• NEXT_DATE and PRIOR_DATE — Adjacent week boundaries, enabling fast forward and backward traversal without re-querying the calendar.
• SEQ_NUM — Ordinal position of the week, useful for deterministic ordering and range scans.
• REFRESH_NUMBER — Identifies the collection refresh cycle that produced the row, supporting incremental planning runs.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program context that generated the row.

Common Use Cases and Queries

The table is used primarily by ASCP internal logic and by planners or developers building custom calendar-aware reports. Typical scenarios include verifying that a calendar was collected for a given instance, inspecting the sequence of week start dates, and joining week boundaries to planned orders or demand to bucket activity by week.

To retrieve the weeks for a calendar:

SELECT week_start_date, next_date, prior_date, seq_num
FROM   msc.msc_cal_week_start_dates
WHERE  calendar_code = :p_calendar
AND    sr_instance_id = :p_instance
ORDER BY week_start_date;

To find the week containing a given transaction date, a range predicate on week_start_date and next_date is the standard pattern. To locate the most recently collected data, filter on REFRESH_NUMBER or order by CREATION_DATE. To audit which concurrent program populated a calendar, group by PROGRAM_ID and PROGRAM_APPLICATION_ID.

Related Objects

The documented FK structure marks this object as standalone, so joins are logical rather than enforced. The most significant related objects are:

• MSC_CALENDARS / MSC_WORKDAY_CALENDARS — definition of the calendar referenced by CALENDAR_CODE and SR_INSTANCE_ID.
• MSC_CAL_EXCEPTIONS — exception set definitions keyed by EXCEPTION_SET_ID.
• MSC_SR_INSTANCES — source instance definitions matching SR_INSTANCE_ID.
• MSC_SYSTEM_ITEMS / MSC_ITEM_LEAD_TIMES — planning attributes whose date arithmetic depends on workday calendars.
• MSC_SUPPLIES and MSC_DEMANDS — transaction tables bucketed using week start dates.
• FND_CONCURRENT_PROGRAMS / FND_CONCURRENT_REQUESTS — programs and request IDs recorded in the audit columns.
• MSC_CAL_WEEK_START_DATES_U1 and MSC_CAL_WEEK_START_DATES_PK — indexes supporting lookup by calendar, instance, exception set, and week.