Auto Recalc Part 2: Weekly Overtime and Holidays

To compound the requirement from the last blog post of paying a premium at the beginning of the week based on the remaining week’s work, an additional requirement was also included. The premium to be paid would actually be based on the overtime, if any, inserted by the weekly overtime rule. This is not a big deal if the overtime is actually added on the last day of the week (Saturday in our case). As discussed in Part 1, when the user inputs clocks onto Saturday, Saturday would be recalculated first, followed by the auto-recalculation of the full week, starting with Sunday, so the premium rule would easily see the overtime that had been inserted by the weekly overtime rule on Saturday.

But suppose this overtime was rolled back to Friday. The client requires that in the scenario that Saturday is a holiday, that the overtime not be applied on the holiday (since the employee is already receiving the holiday rate), but rather be rolled back to the day before. Additionally, the time worked on Saturday count towards the weekly overtime.

An example, using a Time Code/Hour Type format, could look like this, assuming anything over 40 hours is overtime:

Monday: 8 hours WORK/REG, 1 hour OTPREM/REG
Tuesday: 8 hours WORK/REG
Wednesday: 8 hours WORK/REG
Thursday: 8 hours WORK/REG
Friday: 7 hours WORK/REG, 1 hour WORK/OT
Saturday (holiday): 1 hour HOL/REG

The employee works for one hour on the Saturday holiday. The employee has now worked 41 hours in a week so 1 hour is eligible for weekly overtime on the Friday.

The recalculation process behaves such that when the employee clocks in and out on Saturday, that day gets its pay calculated first, but no weekly overtime is applied that day since it is a holiday. The auto-recalculation then restarts at the beginning of the week on Sunday, with our aforementioned premium rule running and looking for overtime later in the week. Since Friday has not be recalculated yet, no overtime will be found. After moving through the previous days of the week Friday will be recalculated, and the overtime will be applied correctly on Friday as required. Since Sunday is recalculated first, it will not insert the required OTPREM premium.

You can easily get this premium to appear on Sunday from the timesheet by again manually recalculating Sunday again. This is known amongst my peers as a “double-recalc”. When Sunday is recalculated again, the overtime will have been applied on Friday, and thus the OTPREM premium will get inserted.

This second recalculation could be implemented as a scheduled task just before payroll export as well. While this is the most oft used solution, it creates confusion amongst many users and causes unnecessary CPU usage.

If the actual client requirement is limited to exporting the premium through the payroll export associated with the Sunday, but not necessarily seeing it on the Sunday on the timesheet, then we can propose an alternate solution.

In this alternate solution, the premium can actually be inserted on the Saturday on the timesheet, but we change the wrkd_work_date (not wrks_work_date) on the premium record to be the previous Sunday as part of the rule. The timesheet ends up looking like this, however the 1 hour OTPREM/REG record has had its wrkd_work_date updated to be Sunday’s date:

Monday: 8 hours WORK/REG
Tuesday: 8 hours WORK/REG
Wednesday: 8 hours WORK/REG
Thursday: 8 hours WORK/REG
Friday: 7 hours WORK/REG, 1 hour WORK/OT
Saturday (holiday): 1 hour HOL/REG, 1 hour OTPREM/REG

So in the same scenario when the employee clocks in and out on Saturday, Saturday is the first day recalculated. But since the WORK/OT is not yet inserted on the Friday, the OTPREM is not yet inserted on Saturday. Again the auto-recalculation process starts on Sunday. By the time it gets to Saturday again, the WORK/OT has been applied on the Friday, and our premium rule now sees it and can insert the necessary OTPREM.

And finally, in the payroll export, when grouping records by day (which I assumed since the requirement was to put the premium on the Sunday in the first place), we will use the wrkd_work_date, rather than the wrks_work_date, which is typically used to output the daily pay summary to the payroll system.

Using this solution, the user will have to view the premium on the Saturday from the timesheet, but it will be grouped with Sunday on the payroll export. And the best part, no “double recalc” will be required!

Got other questions? Post them here and you'll have answers.