Search Results msc_shift_times




Overview
Explore in AI Chat

Overview

MSC_SHIFT_TIMES is a table within the MSC schema (Advanced Supply Chain Planning) in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores workday calendar shift times — the discrete intervals that define when a manufacturing or planning resource is available during a workday on a given calendar. Each row represents one shift slot for a specific calendar code within a given source instance, capturing the shift number and its start and end times. This data provides the time-of-day foundation that planning engines use when converting resource capacity into time-phased supply.

Based on the heuristic Data Vault classification derived from the foreign-key structure, MSC_SHIFT_TIMES is documented as standalone. In Data Vault modeling terms, this suggests it functions most naturally as a satellite-like structure attached to a workday calendar hub, though the mined metadata indicates no explicit parent hub relationships within the object itself. The table carries the standard Oracle EBS audit and concurrency columns, indicating it is maintained through concurrent programs and the Advanced Supply Chain Planning collection processes.

Key Information Stored

The table contains 31 documented columns. The most significant are the following:

  • CALENDAR_CODE — Identifies the workday calendar to which the shift belongs; the primary organizational key for the calendar definition.
  • SR_INSTANCE_ID — The source instance identifier, distinguishing data collected from different source systems.
  • SHIFT_NUM — The ordinal number of the shift within the calendar workday.
  • FROM_TIME — The start time of the shift interval.
  • TO_TIME — The end time of the shift interval.
  • REFRESH_NUMBER — Tracks the collection or refresh cycle that populated the row.
  • LAST_UPDATE_DATE / CREATION_DATE — Standard audit timestamps recording when the row was created and last modified.
  • LAST_UPDATED_BY / CREATED_BY — The users who performed the last update and the initial insert.
  • REQUEST_ID / PROGRAM_ID / PROGRAM_APPLICATION_ID — Concurrent request and program identifiers that produced the row, enabling traceability back to the collecting concurrent program.
  • ATTRIBUTE1 through ATTRIBUTE15 — Descriptive flexfield segments reserved for customer-specific extensions.

The surrogate primary key is MSC_SHIFT_TIMES_PK, composed of CALENDAR_CODE, SR_INSTANCE_ID, SHIFT_NUM, FROM_TIME, and TO_TIME. A unique index, MSC_SHIFT_TIMES_U1, covers the same column set (SR_INSTANCE_ID, CALENDAR_CODE, SHIFT_NUM, FROM_TIME, TO_TIME), confirming the natural business key that guarantees no duplicate shift interval exists for a calendar.

Common Use Cases and Queries

The primary use case is reporting and validating calendar shift definitions feeding the Advanced Supply Chain Planning engine. A typical query retrieves all shifts for a specific calendar:

  • SELECT SHIFT_NUM, FROM_TIME, TO_TIME FROM MSC_SHIFT_TIMES WHERE CALENDAR_CODE = :calendar_code AND SR_INSTANCE_ID = :instance ORDER BY SHIFT_NUM;
  • Determining total planned hours per day by summing interval durations across shifts.
  • Auditing which concurrent request populated a given calendar using REQUEST_ID and PROGRAM_ID.
  • Comparing shift definitions between source instances via SR_INSTANCE_ID.
  • Identifying refresh staleness through REFRESH_NUMBER and LAST_UPDATE_DATE.

Because times are stored at the interval level, aggregations such as total available minutes per workday are straightforward using TO_TIME minus FROM_TIME.

Related Objects

The metadata classifies MSC_SHIFT_TIMES as standalone, so no explicit foreign-key parents are documented. In practice it is referenced alongside the workday calendar definitions in the Advanced Supply Chain Planning calendar tables, which share the CALENDAR_CODE and SR_INSTANCE_ID keys. Standard related objects include the MSC workday calendar and shift exception tables, the MSC_ calendars used by the planning engine, and the collection concurrent programs identified through PROGRAM_ID and REQUEST_ID. Oracle's standard audit columns (CREATED_BY, LAST_UPDATED_BY) reference the FND_USER identity, and the descriptive flexfield attributes align with the FND flexfield infrastructure. Reporting joins are typically made on CALENDAR_CODE plus SR_INSTANCE_ID to associate shift intervals with their parent calendar definition.