There are cases where after a while of entering timesheets (months, or even years) with a specific Week Ending date, the company decides to change their working week days. This means changes on Payroll and changes on the Week Ending Date in Workbench are required.
It is a significant change and needs to be discussed with Consultants or Workbench Support, as it is not a minor process and can have a big impact if not done properly.
There is a script that can be used to change the dates.
Considerations:
When this script is about to be run no one should be entering or editing Timesheets (including reviews, approvals, exports to financials), because there will be validation rules temporarily disabled when this script is run (triggers disabled).
Is recommended that the script is run when no other uses are using the application, as it is run in a SQL transaction, and this blocks any actions where the users might be traying to save data to the database.
If not all historic Timesheets data has Week Ending Dates updated, those previous Timesheets won’t be accessible from Timesheet screens and anywhere where the data is displayed based on a WeekEnding date, because the system will use the new WeekEnding defined in the Controls table, which does not match the original week ending dates.
Timesheets that were possibly exported in Payroll, will now how a different week ending date in Workbench compared to what it is on the Payroll system (timesheets exported).
Consider that depending on the direction of the change (example: current week ends on Sunday, but we are moving the week to finish earlier, on the Wednesday of the current week; this is 'Back'), the Payroll Admin might need to make manual changes for the current week on the Payroll system.
We recommend that Timesheets are only entered until the new week ending date. Export Payroll for that week, which will have an incomplete week: example timesheet for Monday, Tuesday and Wednesday. Once the week ending date has been changed from Sunday to Wednesday, then the rest of the time can be entered. Provided that the working week has also been changed on the Payroll system, then the rest of the days (Thursday, Friday, Saturd, Sunday) will be part of the new week that finishes on the next Wednesday, and can be exported in the next Payroll batch.
What does the script do?
The script changes existing Timesheets WeekEnding date to a date with the new WeekEnding DAY. This new Week Ending DAY must be indicated in a parameter before executing the script.
Once the script is executed the WeekEnding DAY must be changed on Controls table, either via Scaffold or with a SQL statement included in the steps below.
The script must have 4 parameters set before is executed:
@SourceDay: This is the original week ending day that the database currently has and requires to be changed. Possible values: 'Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri' or 'Sat'
@TargetDay: This Is the day that the client wants to change the week ending day TO. Possible values: 'Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri' or 'Sat'
@Direction: Is the direction the change will be done. Possible values are: Forward or Back.
For example, if my current WE day is a Friday, and I want to move to a Sunday, making a change Forward would change the current Timesheet week from ending on a Friday to ending on the coming Sunday.
If using Back as the direction of the change, the current Timesheet week that ends on a Friday, will me modified and will finish on the Sunday that just past.@StartDate: This determines from what date the existing timesheet data is going to be changed. The script will find any timesheet with TimesheetDate> or = to that date and change them. Any timesheets before that date will remain the same.
Steps:
Decide dates and direction of the change.
Take a backup of the database before running script.
Open script wbData_ChangeWEDate.sql in Notepadd++ (or any text editor) and edit parameters. Save and then open this script in the CORRECT SQL database.
This is a sample of the parameters that must be configured:
Execute script.
Then populate the statement below with the new Week Ending day, and execute it. Or you can update it using Scaffold on Web Workbench, on the Control table.
UPDATE dbo.control SET WeekEndingDate = 'Friday'Validate Timesheets:
Open existing ones and new ones.
Create new timesheets. Specifically validate the current, past and next week.
Once validation is done, inform users they can log back in.