Job Billing - Example 1 - Daily Job Billing Overtime Rule Setup
The simplest Job Billing Overtime rule setup is at a daily level as the system calculates the adjusted quantity for each job for each day entered in the posted Payroll timesheet with the following logic:
First, the daily REG (Regular) billing quantity is calculated. Depending on whether it is a weekday/weekend, the total hours for the job on the day (across all hour types) is compared with the daily REG hourly limit in the Job Billing Overtime Rule setup.
In a given day,
-
If Total Daily Payroll Quantity ≤ Daily REG Hour Limit, then
Payroll Job Billing Adjusted Quantity = Total Daily Payroll Quantity − Daily REG Payroll Quantity and
Daily REG Billing Quantity = Daily REG Payroll Quantity + (Total Daily Payroll Quantity − Daily REG Payroll Quantity) so that
Daily REG Billing Quantity = Total Daily Payroll Quantity
-
If Total Daily Payroll Quantity > Daily REG Hour Limit, then
Payroll Job Billing Adjusted Quantity = Daily REG Hour Limit − Daily REG Payroll Quantity and
Daily REG Billing Quantity = Daily REG Payroll Quantity + (Daily REG Hour Limit − Daily REG Payroll Quantity) so that
Daily REG Billing Quantity = Daily REG Hour Limit
Next, the Daily OT (Overtime) Billing Quantity is calculated. Depending on whether it is a weekday/weekend, the total hours for the job on the day (across all hour types) is compared with the daily OT and REG hourly limits in the Job Billing Overtime Rule setup.
In a given day,
-
If Total Daily Payroll Quantity ≤ Daily REG Hour Limit, then
Payroll Job Billing Adjusted Quantity = 0 − Daily OT Payroll Quantity and
Daily OT Billing Quantity = Daily OT Payroll Quantity + (0 − Daily OT Payroll Quantity) so that
Daily OT Billing Quantity = 0
-
If Daily REG Hour Limit < Total Daily Payroll Quantity ≤ Daily OT Hour Limit, then
Payroll Job Billing Adjusted Quantity = Total Daily Payroll Quantity − Daily REG Billing Quantity − Daily OT Payroll Quantity and
Daily OT Billing Quantity = Daily OT Payroll Quantity + (Total Daily Payroll Quantity − Daily REG Billing Quantity − Daily OT Payroll Quantity) so that
Daily OT Billing Quantity = Total Daily Payroll Quantity − Daily REG Billing Quantity
-
If Total Daily Payroll Quantity > Daily OT Hour Limit, then
Payroll Job Billing Adjusted Quantity = Daily OT Hour Limit − Daily REG Hour Limit − Daily OT Payroll Quantity and
Daily OT Billing Quantity = Daily OT Payroll Quantity + (Daily OT Hour Limit − Daily REG Hour Limit − Daily OT Payroll Quantity) so that
Daily OT Billing Quantity = Daily OT Hour Limit − Daily REG Hour Limit
In the end, the Daily DOT (Double Overtime) Billing Quantity is calculated. Depending on whether it is a weekday/weekend, the Payroll DOT quantity is compared with the daily REG, OT, and DOT hourly limits in the Job Billing Overtime Rule setup.
In a given day,
-
If Total Daily Payroll Quantity ≤ Daily OT Hour Limit, then
Payroll Job Billing Adjusted Quantity = 0 − Daily DOT Payroll Quantity and
Daily DOT Billing Quantity = Daily DOT Payroll Quantity + (0 − Daily DOT Payroll Quantity) so that
Daily DOT Billing Quantity = 0
-
If Total Daily Payroll Quantity > Daily OT Hour Limit, then
Payroll Job Billing Adjusted Quantity = Total Daily Payroll Quantity − Daily OT Hour Limit − Daily DOT Payroll Quantity and
Daily DOT Billing Quantity = Daily DOT Payroll Quantity + (Total Daily Payroll Quantity − Daily OT Hour Limit − Daily DOT Payroll Quantity) so that
Daily DOT Billing Quantity = Daily Total Payroll Quantity − Daily OT Hour Limit
Consider the following Job Billing Overtime Rules setup, where only daily hour limits are entered for the two jobs including limits for weekends:
Pgm: JBOVTMRU – Billing Overtime Rules; standard Treeview path: Job Billing > Setup > Local Tables > Billing Overtime Rules
For Job – PYJOB1,
-
The daily limits for weekdays are:
-
Up to 8 hours for REG
-
Greater than 8 and up to 10 hours for OT
-
Greater than 10 hours for DOT
-
-
The daily limits for weekends/holidays are:
-
Up to 4 hours for REG
-
Greater than 4 and up to 8 hours for OT
-
Greater than 8 hours for DOT
-
For Job – PYJOB2,
-
The daily limits for weekdays are:
-
REG = Up to 9 hours
-
OT = Greater than 9 and up to 11 hours
-
DOT = Greater than 11 hours.
-
-
The daily limits for weekends/holidays are:
-
Up to 4.5 hours for REG
-
Greater than 4.5 and up to 9 hours for OT
-
Greater than 9 hours for DOT
-
The following weekly Payroll timesheet is posted for the two employees, ID-EMP-01 and RV-WK-HR-02, against two jobs, PYJOB1 and PYJOB2, after going through the Payroll Job Overtime Rules processing:
Pgm: PYTIMSTM – Timesheet Entry; standard Treeview path: US Payroll > Timesheet > Timesheet Entry
The hours by type are distributed as follows:
Date | PYJOB1 | PYJOB2 | ||||
---|---|---|---|---|---|---|
ID-EMP-01 | RV-WK-HR-02 | |||||
REG (Hours) | OT (Hours) | DOT (Hours) | REG (Hours) | OT (Hours) | DOT (Hours) | |
24-JUN-2024 | 8 | 0 | 0 | 9 | 2 | 0 |
25-JUN-2024 | 9 | 0 | 0 | 9 | 2 | 0 |
26-JUN-2024 | 9 | 3 | 0 | 9 | 0 | 0 |
27-JUN-2024 | 9 | 2 | 0 | 9 | 3 | 0 |
28-JUN-2024 | 5 | 5 | 0 | 4 | 8 | 1 |
29-JUN-2024 | 0 | 0 | 0 | 0 | 0 | 7 |
30-JUN-2024 | 0 | 0 | 0 | 0 | 0 | 0 |
Weekly Total | 40 | 10 | 0 | 40 | 15 | 8 |
The resulting CSV file extract of the Job Costing Transaction Detail Log showcases how the JCDT_PYJB_BILLING_ADJ_QTY is used to calculate the Billing Quantity column and the resulting Unbilled Revenue Amount column (JCDT_UNBILLED_REV_Amt).
NOTE: The Billing Quantity, Qty Per Wage Code, Billing Qty Per Job, and Rate Calculated columns were added manually for better understanding. The Billing Quantity column value can be obtained by adding Quantity (H) and JcdtPyjbBillingAdjQty (I). JcdtPyjbBillingAdjQty is hidden by default in the log and can be selected from the column list.
Below are the detailed calculations for one of the jobs and the same logic applies for the other job as well.
Job – PYJOB2
Date: 24-JUN-2024 (Weekday)
11 hours are posted for the day from Payroll, where REG = 9, OT = 2, and DOT = 0.
Since the total hours exceed the weekday REG limit of nine hours, but not the OT limit of 11 hours, the Billing Quantity column only includes REG and OT hours.
REG Billing Quantity = 9 + 0 = 9, where 9 hours is the Daily REG Payroll Quantity and 0 hours is the Payroll Job Billing Adjusted Quantity
REG Unbilled Revenue Amount = 9 × 68.2 = $613.80
OT Billing Quantity = 2 + 0 = 2
OT Unbilled Revenue Amount = 2 × 99.2 = $198.4
DOT Billing Quantity = 0 + 0 = 0
DOT Unbilled Revenue Amount = 0 × 130.2 = $0
Date: 25-JUN-2024 (Weekday)
11 hours are posted for the day from Payroll, where REG = 9, OT = 2, and DOT = 0.
Since the total hours exceed the weekday REG limit of 9 hours, but not the OT limit of 11 hours, the Billing Quantity column only includes REG and OT hours.
REG Billing Quantity = 9 + 0 = 9
REG Unbilled Revenue Amount = 9 × 68.2 = $613.8
OT Billing Quantity = 2 + 0 = 2
OT Unbilled Revenue Amount = 2 × 99.2 = $198.4
DOT Billing Quantity = 0 + 0 = 0
DOT Unbilled Revenue Amount = 0 × 130.2 = $0
Date: 26-JUN-2024 (Weekday)
9 hours are posted for the day, where REG = 9, OT = 0, and DOT = 0.
Since the total hours doesn’t exceed the weekday REG limit of 9 hours, the Billing Quantity column only includes REG hours.
REG Billing Quantity = 9 + 0 = 9
REG Unbilled Revenue Amount = 9 × 68.2 = $613.8
OT Billing Quantity = 0 + 0 = 0
OT Unbilled Revenue Amount = 0 × 99.2 = $0
DOT Billing Quantity = 0 + 0 = 0
DOT Unbilled Revenue Amount = 0 × 130.2 = $0
Date: 27-JUN-2024 (Weekday)
12 hours are posted for the day, where REG = 9, OT = 3, and DOT = 0.
Since the total hours exceed the weekday REG limit of 9 hours and also the OT limit of 11 hours, the Billing Quantity column includes REG, OT, and DOT hours.
REG Billing Quantity = 9 + 0 = 9
REG Unbilled Revenue Amount = 9 × 68.2 = $613.8
OT Billing Quantity = 3 + (-1) = 2
OT Unbilled Revenue Amount = 2 × 99.2 = $198.4
DOT Billing Quantity = 0 + 1 = 1
DOT Unbilled Revenue Amount = 1 × 130.2 = $130.2
Date: 28-JUN-2024 (Weekday)
13 hours are posted for the day, where REG = 4, OT = 8, and DOT = 1.
Since the total hours exceed the weekday REG limit of 9 hours and also the OT limit of 11 hours, the Billing Quantity column includes REG, OT, and DOT hours.
REG Billing Quantity = 4 + 5 = 9
REG Unbilled Revenue Amount = 9 × 68.2 = $613.8
OT Billing Quantity = 8 + (-6) = 2
OT Unbilled Revenue Amount = 2 × 99.2 = $198.4
DOT Billing Quantity = 1 + 1 = 2
DOT Unbilled Revenue Amount = 2 × 130.2 = $260.4
Date: 29-JUN-2024 (Weekend)
7 hours are posted for the day, where REG = 0, OT = 0, and DOT = 7.
Since the total hours exceed the weekend REG limit of 4.5 hours, but not the OT limit of 9 hours, the Billing Quantity column only includes REG and OT hours.
REG Billing Quantity = 0 + 4.5 = 4.5
REG Unbilled Revenue Amount = 4.5 × 68.2 = $306.9
OT Billing Quantity = 0 + 2.5 = 2.5
OT Unbilled Revenue Amount = 2.5 × 99.2 = $248
DOT Billing Quantity = 7 + (-7) = 0
DOT Unbilled Revenue Amount = 0 × 130.2 = $0