There are times that after a while of entering of entering timesheet (months, or even years) with a specific Week Ending date, the company decides that the last day of the week needs to change.
This is a significant change and needs to be discussed with Consultants or Workbench Support, as it is not a minor process.
There is a script that can be used to chnage 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 WeekEnding newly define din the Controls table, which does not match the original week ending dates.
Timesheets that were possibly exported in Payroll, will now how a different weekending date than what it is on the financial system.
Consider that depending on the direction of the change, the Payroll Admin might need to make manual changes for the current week on the Payroll system.
What does the script do?
The script changes existing Timesheets WeekEnding date to a WeekEnding date indicated in a parameter that must be edited by the user 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 Endind day, and execute it. Or you can update it via Scaffold on Web Workbench.
UPDATE dbo.control SET WeekEndingDate = 'Friday'
‘Validate Timesheets: existing ones and new ones. And create new timesheets. Specifically validate the current, past and next week.
Once validation is done, inform users they can log back in.