Search Results msc_shift_dates_pk

Overview

MSC_SHIFT_DATES is a table within the MSC schema, belonging to the Advanced Supply Chain Planning (ASCP) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores workday calendar shift dates, providing the date-level expansion of shift definitions used by ASCP planning engines. While its parent table, MSC_CALENDAR_SHIFTS, defines the reusable shift pattern (for example, the number and timing of shifts within a workday), MSC_SHIFT_DATES flattens that pattern onto actual calendar dates so that planning calculations can determine capacity, resource availability, and sourcing lead times on a specific day.

From a dimensional modeling perspective, the relationship metadata suggests a satellite-leaning classification. The table's grain is defined by the combination of calendar, source instance, exception set, shift number, and shift date, and it derives its identity from MSC_CALENDAR_SHIFTS rather than introducing new business entities on its own. It therefore behaves less like an independent hub and more like a dependent satellite that records the resolved state of a shift calendar over time.

Key Information Stored

The documented schema contains twenty columns. The most operationally significant are:

• CALENDAR_CODE — Identifies the workday calendar to which the shift date belongs; a core component of both the primary key and the unique index.
• SR_INSTANCE_ID — The source instance identifier, allowing the same calendar code to exist across multiple source systems. It participates in the foreign key to MSC_CALENDAR_SHIFTS.
• EXCEPTION_SET_ID — Links the shift date to a specific exception set, enabling holiday or alternate-pattern overrides.
• SHIFT_NUM — The ordinal number of the shift within the workday.
• SHIFT_DATE — The actual calendar date on which the shift occurs.
• SEQ_NUM, NEXT_SEQ_NUM, PRIOR_SEQ_NUM — Sequencing columns that allow navigation across chronological shift records.
• NEXT_DATE, PRIOR_DATE — Adjacent shift dates, supporting forward and backward date traversal without recursive queries.
• REFRESH_NUMBER — The collection refresh identifier supporting multi-pass planning data collections.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, and PROGRAM_UPDATE_DATE track record provenance.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID — Identify the concurrent request and program that populated or refreshed the record.

The physical primary key is MSC_SHIFT_DATES_PK, defined across CALENDAR_CODE, SR_INSTANCE_ID, EXCEPTION_SET_ID, SHIFT_NUM, and SHIFT_DATE. A separate unique index, MSC_SHIFT_DATES_U1, covers the same logical attributes in a different column order (SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, SHIFT_DATE, SHIFT_NUM), reinforcing these as the true business-key candidates.

Common Use Cases and Queries

The primary scenario for MSC_SHIFT_DATES is capacity and calendar resolution during an ASCP plan run. Planners and developers query it to confirm which shifts fall on a given date, or to join shift dates back to workday calendars when validating plan output.

A typical query returns all shifts for a calendar over a date range:

• SELECT shift_date, shift_num FROM msc_shift_dates WHERE calendar_code = :calendar AND sr_instance_id = :instance AND shift_date BETWEEN :from_date AND :to_date ORDER BY shift_date, shift_num;

A second pattern uses the NEXT_DATE and PRIOR_DATE columns to walk consecutive shift days without recursion:

• SELECT shift_date, next_date, prior_date FROM msc_shift_dates WHERE calendar_code = :calendar AND shift_date = :anchor_date;

Reporting use cases include capacity utilization reporting, exception set auditing (identifying dates overridden by EXCEPTION_SET_ID), and troubleshooting planning collections by filtering on REFRESH_NUMBER or REQUEST_ID.

Related Objects

The most significant dependencies are:

• MSC_CALENDAR_SHIFTS — The parent table joined on SR_INSTANCE_ID, CALENDAR_CODE, and SHIFT_NUM; it supplies shift definitions that MSC_SHIFT_DATES expands onto dates.
• MSC_WORKDAY_CALENDARS / calendar definition tables — Provide the calendar_code context and exception set relationships.
• MSC_EXCEPTIONS / exception set tables — Define the EXCEPTION_SET_ID overrides applied to specific dates.
• MSC_SR_INSTANCES — Resolves SR_INSTANCE_ID to the source system that owns the calendar.
• ASCP planning engine views and concurrent programs — The collection and plan-run processes that populate and consume these shift dates.

Together these objects form the calendar backbone that ASCP relies upon for time-phased planning.