Results for “msc_shift_exceptions”

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

Overview

MSC_SHIFT_EXCEPTIONS is a table within the MSC schema, belonging to the Advanced Supply Chain Planning (ASCP) product family. Its documented purpose is to store workday calendar shift exceptions — records that override or annotate the standard shift definitions established for a given calendar. Where the base calendar-shift model defines recurring shift patterns for a workday calendar, MSC_SHIFT_EXCEPTIONS captures the date-specific deviations and exception entries that plan engines must honor during scheduling and capacity calculations.

The table's primary key, MSC_SHIFT_EXCEPTIONS_PK, is a composite of CALENDAR_CODE, SR_INSTANCE_ID, EXCEPTION_SET_ID, EXCEPTION_DATE, and SHIFT_NUM. A separate unique index, MSC_SHIFT_EXCEPTIONS_U1, covers SR_INSTANCE_ID, CALENDAR_CODE, EXCEPTION_SET_ID, SHIFT_NUM, and EXCEPTION_DATE — effectively the same business key in a different column order, and the strongest candidate for the natural business key. The documented foreign-key path from MSC_SHIFT_EXCEPTIONS.SR_INSTANCE_ID to MSC_CALENDAR_SHIFTS confirms that exceptions are anchored to the calendar-shift definition they modify. Heuristic Data Vault classification mined from the foreign-key structure suggests a satellite-leaning object: it carries descriptive, context-dependent attributes about shifts rather than acting as an independent hub or as a pure associative link.

Key Information Stored

The table contains 32 documented columns. The most operationally significant are:

• CALENDAR_CODE, SR_INSTANCE_ID, SHIFT_NUM — the composite reference identifying the calendar, source instance, and specific shift definition to which the exception applies.
• EXCEPTION_SET_ID — groups exceptions into named or versioned sets, allowing multiple exception scenarios to coexist for the same calendar.
• EXCEPTION_DATE — the specific date on which the shift exception takes effect.
• EXCEPTION_TYPE — the classification of the exception (for example, a shift cancellation, override, or additional working period).
• REFRESH_NUMBER — supports collection and refresh cycles during planning data loads.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — the concurrent program and request context that inserted or last modified the row.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard EBS audit columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the DFF (descriptive flexfield) columns for extensibility.

The surrogate primary key is the composite MSC_SHIFT_EXCEPTIONS_PK, whereas the business-key candidate is MSC_SHIFT_EXCEPTIONS_U1. The presence of both reflects the standard EBS pattern of enforcing uniqueness on the business identity while retaining a composite PK for referential integrity.

Common Use Cases and Queries

Typical use cases include verifying that a plan run honored exception dates, auditing shift overrides for capacity planning, and reconciling exception data propagated from a source instance. A representative query joining to the parent shift table:

• SELECT e.CALENDAR_CODE, e.EXCEPTION_DATE, e.SHIFT_NUM, e.EXCEPTION_TYPE FROM MSC.MSC_SHIFT_EXCEPTIONS e WHERE e.SR_INSTANCE_ID = :instance AND e.CALENDAR_CODE = :calendar AND e.EXCEPTION_DATE BETWEEN :start AND :end;
• Join pattern: MSC_SHIFT_EXCEPTIONS e JOIN MSC_CALENDAR_SHIFTS s ON e.SR_INSTANCE_ID = s.SR_INSTANCE_ID AND e.CALENDAR_CODE = s.CALENDAR_CODE AND e.SHIFT_NUM = s.SHIFT_NUM.

Reporting scenarios commonly filter by EXCEPTION_SET_ID to isolate a specific exception set, and by REFRESH_NUMBER to examine the latest collection cycle. Audit queries favor LAST_UPDATE_DATE and PROGRAM_ID to trace which concurrent program introduced a given exception.

Related Objects

The most significant related objects, based on the documented foreign-key relationship and the surrounding ASCP calendar model, are:

• MSC_CALENDAR_SHIFTS — parent table referenced via SR_INSTANCE_ID, CALENDAR_CODE, and SHIFT_NUM; supplies the base shift definitions that this table exceptions.
• The workday calendar and calendar-definition tables in the MSC schema that own CALENDAR_CODE.
• ASCP planning data collection concurrent programs that populate REFRESH_NUMBER and the PROGRAM_* audit columns.
• Backing views or DBA utilities that expose the DFF attributes (ATTRIBUTE_CATEGORY and ATTRIBUTE1–15).
• Referenced MSC instance records identified by SR_INSTANCE_ID, which tie exceptions to a specific source system.

Administrators should treat MSC_SHIFT_EXCEPTIONS as a satellite-style extension of the calendar-shift model and validate any change against the U1 unique index to prevent duplicate exception entries for the same shift and date.