Search Results msc_shift_dates




Overview
Explore in AI Chat

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:

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.