Mappings to new MES version 7​.0 stored procedures

The shift schedule improvements that were implemented in the MES version 7.0 database schema include removing and replacing shift-related stored procedures. The affected previous stored procedures are listed in the following table. For example:

• The shift_sched stored procedures were replaced with shift_schedule stored procedures. Shift schedules are now linked to shift patterns (which are linked to entities) instead of shift schedules being linked directly to entities.

• The shift_exc stored procedures are replaced with shift_pattern stored procedures. Shift exceptions are now modeled as shift patterns of type holiday or overtime.

• The MES middleware maintenance service now handles tasks that were performed by several other shift-related stored procedures that have been removed. There are no replacement stored procedures for these.

• Some shift-related stored procedures that were removed were meant for internal use only and should not have had extensibility hooks configured for them. These are identified in the table in their comments section.

  If any of the pre-7.0 stored procedures that were configured for extensibility hooks have a replacement stored procedure, the hooks should be reconfigured with the new stored procedures so that they will work properly with the current schema.

  Pre-7.0 Stored Procedures That Were Replaced or Removed	Replacement 7.0 Stored Procedure	Comments
  sp_I_Shift_Sched	sp_I_Shift_Schedule	Adds a shift schedule linked to a shift pattern. To link a shift schedule to an entity, call the MES Stateless API ShiftPatternEntLink.Add() method.
  sp_D_Shift_Sched	sp_D_Shift_Schedule	Removes a shift schedule, which implicitly removes the link from all the entities linked to the pattern.
  sp_S_Shift_Sched_GetSched	N/A	The previous stored procedure returned the shift schedules for an entity. Now, similar information can be obtained by querying the shift_sched view.
  sp_U_Shift_Sched	sp_U_Shift_Schedule	Updates a shift schedule, which implicitly updates the schedule for all the entities linked to the pattern.
  sp_I_Shift_To_Go	N/A	This was an internal stored procedure and should not have been configured with extensibility hooks. Records in the shift_to_go table are internally handled by the MES middleware maintenance service.
  sp_D_Shift_To_Go	N/A	Records in the shift_to_go table are now maintained by the MES middleware maintenance service.
  sp_D_Shift_To_Go_DelBetWDates	N/A	This was an internal stored procedure and should not have been configured with an extensibility hook.
  sp_S_Shift_To_Go_GetCurShifts sp_S_Shift_To_Go_GetSched	Sp_SA_Shift_To_Go	The previous stored procedures returned the projected shift schedules from the shift_to_go table for a given entity based on the time filter. Now, the same information can be queried directly using sp_SA_Shift_To_Go.
  sp_U_Shift_To_Go	N/A	Now an internal function performed by the MES middleware maintenance service and cannot have extensibility hooks.
  sp_I_Shift_Exc	sp_I_Shift_Pattern	The previous stored procedure created only shift exceptions. The new stored procedure can create exception (overtime and holiday) shift patterns as well as regular shift patterns.
  sp_D_Shift_Exc	sp_D_Shift_Pattern	The previous stored procedure deleted only shift exceptions. The new stored procedure can delete exception (overtime and holiday) shift patterns as well as regular shift patterns.
  sp_S_Shift_Exc sp_SA_Shift_Exc	sp_S_Shift_Pattern sp_SA_Shift_Pattern sp_S_Shift_Schedule sp_SA_Shift_Schedule	The previous stored procedures retrieved only shift exceptions. The new stored procedures can retrieve exception (overtime and holiday) shift patterns and schedules as well as regular shift patterns and schedules.
  sp_S_Shift_Exc_GetExc sp_SA_Shift_Exc_GetExceptions	sp_S_Shift_Pattern_Ent_Link sp_SA_Shift_Pattern_Ent_Link	The previous stored procedures retrieved only shift exceptions given the entity or its descendants. The new stored procedures can retrieve exception (overtime and holiday) shift patterns as well as regular shift patterns.
  sp_U_Shift_Exc	sp_u_Shift_Pattern	The previous stored procedure updated only shift exceptions. The new stored procedure can update exception (overtime and holiday) shift patterns as well as regular shift patterns.
  sp_SA_Ent_GetShiftBreaks	N/A	Entity shift breaks are now calculated internally.
  sp_U_Ent_AddShiftsForTheDay	N/A	This was an internal stored procedure and should not have been configured with extensibility hooks.
  sp_U_Ent_ApplyAdditiveShiftExc	N/A	This was an internal stored procedure and should not have been configured with extensibility hooks.
  sp_U_Ent_ApplyShiftExceptions	N/A	This was an internal stored procedure and should not have been configured with extensibility hooks.
  sp_U_Ent_ApplySubShiftExc	N/A	This was an internal stored procedure and should not have been configured with extensibility hooks.
  sp_U_Ent_ClearBreaksAnd0Shifts	N/A	This was an internal stored procedure and should not have been configured with extensibility hooks.
  sp_U_Ent_CopyShifts	sp_U_Shift_Pattern_Ent_ Link_AddMany	The previous model linked shift schedules to entities. The new model links shift patterns to entities.
  sp_U_Ent_CreateShiftsToGo	N/A	This was an internal stored procedure and should not have been configured with extensibility hooks.
  sp_U_Ent_DoAutoShifChngsByName sp_U_Ent_DoAutoShiftChanges sp_U_Ent_DoAutShifChangsByName sp_U_Ent_DoPastShiftChanges	sp_U_Ent_StartShiftCustom	The previous stored procedures used the entity name. Now, the entity ID is required by the stored procedure. When an entity is linked to a shift pattern, then shift changes for that entity and its descendants are managed by the MES middleware maintenance service. When an entity is not linked to a shift pattern, then it is expected that the shift changes are managed externally from MES. Therefore, the external implementation can call the new stored procedure to change shifts in MES.
  sp_U_Ent_RefreshShiftSched	N/A	Now an internal function performed by the MES middleware maintenance service and cannot have extensibility hooks.
  sp_U_Ent_ScheduleShifts	N/A	This was an internal stored procedure and should not have been configured with extensibility hooks.
  sp_U_JSE_DropTempShiftExc	N/A	This was an internal stored procedure and should not have been configured with extensibility hooks.
  sp_U_JSE_RefreshTempShiftExc	N/A	This was an internal stored procedure and should not have been configured with extensibility hooks.