Tables and Views for Shift Schedules

Tables Removed

The following tables have been removed:

• shift_sched

• shift_exc

• temp_shift_exc

Shift_Pattern Table Added

Defines shift patterns, which are assigned to entities.

• A shift pattern can contain shift schedules to define when shifts change within the shift pattern.

• Shift patterns can be a standard recurring pattern that defines normal shift operations.

• Shift patterns have a start and end effective date, which can be used to define high and low seasonal work or rotational periods. For example, a site might have a high season of 7 days a week with 3 shifts a day and then a low season of 5 days a week with 2 shifts a day.

• Shift patterns can also be used to define holidays and overtime. These exceptions to the standard shift pattern take precedence over standard shift patterns to remove (holiday) or add (overtime) shifts from/to the standard shift pattern.

• Standard recurring shift patterns can be assigned to any physical entity ID that has the Can Schedule Shifts capability. A shift pattern that is assigned to any entity will by default be applied to all its child entities that have not been directly assigned a shift pattern. If an entity has more than one parent entity that has a shift pattern assigned to them, then the shift pattern is inherited from the default parent (parent_ent_id in the ent table).

• Holiday and overtime shift patterns can be assigned to any physical entity that has the Can Schedule Shifts capability or is a child of such an entity.

  Field	Datatype	PK	Nulls?	Description
  pattern_id	int32	Y	N	The shift pattern ID.
  pattern_name	string80	N	N	The shift pattern name. Must be unique.
  start_eff_local	tsmalldatetime	N	N	Defines the time range, in local time, when the shift pattern is in effect. start_eff_local is required. end_eff_local can be null for a regular schedule, in which case there is no effectivity end time. It must have a non-null value for a holiday or overtime shift pattern. Since the shift schedules are checked and changed on a minutely basis, datetime resolution will be up to minutes; seconds will not be stored.
  end_eff_local	tsmalldatetime	N	Y
  recurring	logical	N	N	True (the default) for regular shift patterns. The shift pattern's schedule is repeated weekly from the start time to the end time (if configured) or repeated perpetually. When true, the additive setting is ignored. False for overtime and holiday shift patterns.
  additive	logical	N	N	True for an overtime shift pattern. An overtime schedule will be merged with a regular shift pattern's schedule for the days that are selected. The unselected days are ignored as they are expected to follow their original schedule. False for a regular or holiday schedule. A holiday schedule blocks out time periods from regular and overtime shifts, do not have shifts, and are identified as No Shift in entity schedules.
  enabled	logical	N	N	Indicates whether the shift pattern is active and so can be used. When true, the shift pattern and its schedules are used at runtime. When false (the default), the shift pattern and its schedules are ignored at runtime It is best to have all the shift schedule links established and have this flag set to true so that the middleware maintenance services will process the shift change next minute. But when it is set to true, the maintenance services will start processing shift changes every minute while the entities are linked/schedules are attached, etc. Therefore, this flag is set to false by default and the user has to enable this flag once all the configuration is complete.
  comments	string2000	N	Y	Any additional comments about this shift pattern, which will be recorded in the shift_history table when this pattern is used.
  last_edit_comment	string254	N	Y	Reserved for internal use to indicate why this record was changed.
  last_edit_by	string40	N	Y	The user who added or last changed this record.
  last_edit_at	tdatetime	N	N	When this record was added or last changed.

Shift_Schedule Table Added

Defines a shift schedule that is associated to a shift pattern and a shift. The shift schedule can include up to 3 breaks. Breaks are optional and could be used for capacity planning. Only standard recurring shift patterns and overtime shift patterns can have shift schedules.

There is a one-to-many relationship between shift and shift_schedule.

If there is no shift schedule defined for an entity or any of its descendants, then automatic shift changes will not work.

Breaks are not normalized for ease of accessing data. Exceptions also need to be able to override any defined breaks.

FK from pattern_id to shift_pattern (Cascade delete)

FK from shift_id to shift (Cascade delete)

Shift_Pattern_Ent_Link Table Added

Links shift patterns to the entities to which they apply.

FK from shift_sched_ent_id to ent (Cascade delete)

FK from ent_id to ent (Set Null, to prevent circular references)

FK from pattern_id to shift_pattern (Cascade delete)

Shift_To_Go Table

• The italicized text in the table description has been modified or added:

  Details the imminent shift changes over the short term for each entity (typically over the next week, taking into account any standard shift patterns and any defined shift exceptions (i.e., holiday or overtime shift patterns). All times are local.

  Null shifts are not included in the schedule, so the end_time is used to determine when to end a shift. The break times are inherited from the shift_schedule table, but if a shift exception shortens a shift to exclude a break, then its relevant break times are set to null in the appropriate row in this table.

  The data in this table is kept updated internally by the background service and is available as a read‑only table for any application or user. It should not be updated by any application or user. The data in this table is not used for shift changes, which are evaluated every minute by the background service.

• The ent_id description has been updated to: "The ID of an entity to which the shift pattern is linked, either directly or through inheritance from the parent entity specified in shift_sched_ent_id."

• The break column data types have been changed from tdatetime to time0.

• The following columns have been added:

  • shift_start_utc, which indicates the shift start time in UTC.

  • shift_end_utc, which indicates that shift end time in UTC.

  • shift_sched_ent_id, which indicates the ID of the entity to which the shift pattern is directly assigned.

  • pattern_id, which indicates the shift pattern to which the shift schedule is associated.

  • ent_tz_id, which is the entity's time zone.

  • last_edit_comment, which indicates why the record was changed.

  • created_at_utc, which is when the shift was created.

  • row_id, which is the unique row identifier for the record.

Shift_History Table

The following columns have been added to the shift_history table:

• pattern_id, which indicates the ID of the shift pattern to which the shift is associated.

• comments, for comments that were entered for the shift pattern to which the shift is associated.

• user_comments, for comments entered about the shift.

• spare1 to spare4, for user-defined content.

• last_edit_comments, last_edit_at, and last_edit_by.

Shift_Sched View Added

The shift_sched view was added. It returns shift_schedule records linked to shift_patterns and shift_pattern_ent_link to match what was available in previous releases of MES for which this was a table. With the changes in the table design, the data in this view is only guaranteed after migration and might not match in new configurations.