Results for “msc_calendar_shifts”

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

Overview

MSC.MSC_CALENDAR_SHIFTS is a table within the Oracle Advanced Supply Chain Planning (MSC) product module, described in the ETRM repository as the store for "Workday calendar shifts." It defines the shift patterns that make up a workday calendar used by the MSC planning engine. Each row describes a shift or shift pattern associated with a production or workday calendar, identified by a composite of calendar code, source instance, and shift number. In Oracle EBS 12.1.1 and 12.2.2, this table is populated and maintained in the MSC schema and is referenced during planning runs to determine available working time, capacity buckets, and day-level calendar behavior.

Under a heuristic Data Vault classification mined from its foreign key structure, MSC_CALENDAR_SHIFTS is hub-leaning. In Data Vault modeling terms, this suggests the table behaves primarily as a hub: it holds the durable business key (calendar, instance, shift) that other tables reference, with satellite-like descriptive and audit attributes attached. Modelers designing a Data Vault or dimensional representation of EBS planning data should treat MSC_CALENDAR_SHIFTS as the anchor for shift definitions, with dependent date and exception tables attached as links or satellites.

Key Information Stored

The table contains 33 documented columns. The most important of these are the key and business-critical attributes:

• CALENDAR_CODE — Identifies the workday calendar to which the shift belongs.
• SR_INSTANCE_ID — The source system instance identifier, used to distinguish data originating from different source instances.
• SHIFT_NUM — The sequence number identifying a specific shift within the calendar.
• SHIFT_NAME — The descriptive name of the shift.
• DAYS_ON — The number of working days in the shift pattern cycle.
• DAYS_OFF — The number of non-working days in the shift pattern cycle.
• DESCRIPTION — A textual explanation of the shift definition.
• REFRESH_NUMBER — Tracks the refresh or collection cycle to which the record belongs.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrency and program audit columns recording the process that created or last updated the row.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY — Standard EBS WHO-column audit trail.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1 through ATTRIBUTE15 — Flexfield descriptive columns reserved for extensibility.

The primary key is MSC_CALENDAR_SHIFTS_PK, defined on (CALENDAR_CODE, SR_INSTANCE_ID, SHIFT_NUM). A unique index, MSC_CALENDAR_SHIFTS_U1, covers the business key (SR_INSTANCE_ID, CALENDAR_CODE, SHIFT_NUM), confirming that the combination of instance, calendar, and shift number uniquely identifies a shift. In this schema, the primary key composite serves as the business-key surrogate; there is no separate single-column surrogate ID documented.

Common Use Cases and Queries

This table is primarily consumed by the planning engine and by reporting on workday calendar configuration. Typical scenarios include:

• Listing all shifts defined for a given calendar and instance to validate planning calendar setup.
• Reviewing the on/off day pattern (DAYS_ON, DAYS_OFF) to model capacity and resource availability in plans.
• Auditing when shifts were last collected or updated via REFRESH_NUMBER, REQUEST_ID, and LAST_UPDATE_DATE.
• Comparing shift definitions across source instances to reconcile multi-instance planning data.

A representative query joins the shift header to its associated detail tables:

SELECT cs.CALENDAR_CODE, cs.SR_INSTANCE_ID, cs.SHIFT_NUM, cs.SHIFT_NAME, cs.DAYS_ON, cs.DAYS_OFF FROM MSC.MSC_CALENDAR_SHIFTS cs WHERE cs.SR_INSTANCE_ID = :instance_id AND cs.CALENDAR_CODE = :calendar_code ORDER BY cs.SHIFT_NUM;

Because calendar data flows into planning capacity calculations, reports on this table are frequently paired with the dependent date and exception tables described below.

Related Objects

Two foreign key relationships are documented, both of which reference MSC_CALENDAR_SHIFTS:

• MSC_SHIFT_DATES — References MSC_CALENDAR_SHIFTS on (SR_INSTANCE_ID, CALENDAR_CODE, SHIFT_NUM). This table stores the individual dated occurrences (workday instances) generated from the shift pattern.
• MSC_SHIFT_EXCEPTIONS — References MSC_CALENDAR_SHIFTS on (SR_INSTANCE_ID, CALENDAR_CODE, SHIFT_NUM). This table stores exceptions or overrides applied to specific shift dates.

Joins between these tables use the three-column business key: MSC_CALENDAR_SHIFTS.SR_INSTANCE_ID = MSC_SHIFT_DATES.SR_INSTANCE_ID AND MSC_CALENDAR_SHIFTS.CALENDAR_CODE = MSC_SHIFT_DATES.CALENDAR_CODE AND MSC_CALENDAR_SHIFTS.SHIFT_NUM = MSC_SHIFT_DATES.SHIFT_NUM, with the same pattern applying to MSC_SHIFT_EXCEPTIONS. In the broader MSC data model, these shifts ultimately support the workday calendar definitions consumed by the supply chain planning engine.