Job Billing - Example 2 - Weekly Job Billing Overtime Rule Setup

The other type of Job Billing Overtime Rule setup is at a weekly level and is more complex than the daily setup. The system using the weekly overtime rule hourly limits calculates the adjusted quantity for each job for each day entered in the posted Payroll timesheet with the following logic:

Firstly, the daily REG (Regular) Billing Quantity is calculated. The total hours for the job in the week (across all hour types) is compared with the weekly REG hourly limit in the Job Billing Overtime Rule setup and then split equally among all the days entered for the job in the timesheet.

Let n represent the number of distinct Payroll timesheet entries for the job.

In a given day,

1. If Total Weekly Payroll Quantity ≤ Weekly REG Hour Limit, then

   Daily Payroll Job Billing Adjusted Quantity = (Total Weekly Payroll Quantity ÷ n) − Daily REG Payroll Quantity and

   Daily REG Billing Quantity = Daily REG Payroll Quantity + (Total Weekly Payroll Quantity/n) − Daily REG Payroll Quantity so that

   Daily REG Billing Quantity = Total Weekly Payroll Quantity ÷ n

2. If Total Weekly Payroll Quantity > Weekly REG Hour Limit, then

   Daily Payroll Job Billing Adjusted Quantity = (Weekly REG hour limit ÷ n) - Daily REG Payroll Quantity and

   Daily REG Billing Quantity = Daily REG Payroll Quantity + (Weekly REG hour limit ÷ n) – Daily REG Payroll Quantity so that

   Daily REG Billing Quantity = Weekly REG hour limit ÷ n

Next, the daily OT (Overtime) Billing Quantity is calculated. The total hours for the job in the week (across all hour types) is compared with the weekly REG and OT hourly limits in the Job Billing Overtime Rule setup and then split equally among all the days entered for the job in the timesheet.

Let n represent the number of distinct Payroll timesheet entries for the job.

In a given day,

1. If Total Weekly Payroll Quantity ≤ Weekly REG Hour Limit, then

   Daily Payroll Job Billing Adjusted Quantity = 0 − Daily OT Payroll Quantity

   Daily OT Billing Quantity = Daily OT Payroll Quantity + (0 − Daily OT Payroll Quantity) = 0

2. If Weekly REG Hour Limit < Total Weekly Payroll Quantity ≤ Weekly OT Hour Limit, then

   Daily Payroll Job Billing Adjusted Quantity = ((Total Weekly Payroll Quantity − Weekly REG Billing Quantity) ÷ n) − Daily OT Payroll Quantity and

   Daily OT Billing Quantity = Daily OT Payroll Quantity + ((Total Weekly Payroll Quantity − Weekly REG Billing Quantity) ÷ n) − Daily OT Payroll Quantity so that

   Daily OT Billing Quantity = (Total Weekly Payroll Quantity − Weekly REG Billing Quantity) ÷ n

3. If Total Weekly Payroll Quantity > Weekly OT Hour Limit, then

   Daily Payroll Job Billing Adjusted Quantity = ((Weekly OT hour limit − Weekly REG hour limit) ÷ n) − Daily OT Payroll Quantity and

   Daily OT Billing Quantity = Daily OT Payroll Quantity + ((Weekly OT hour limit − Weekly REG hour limit) ÷ n) − Daily OT Payroll Quantity so that

   Daily OT Billing Quantity = (Weekly OT hour limit − Weekly REG hour limit) ÷ n

In the end, the daily DOT (Double Overtime) Billing Quantity is calculated. The total hours for the job in the week (across all hour types) is compared with the weekly REG, OT, and DOT hourly limits in the Job Billing Overtime Rule setup and then split equally among all the days entered for the job in the timesheet.

Let n represents the number of distinct Payroll timesheet entries for the job.

In a given day,

1. If Total Weekly Payroll Quantity ≤ Weekly OT Hour Limit, then

   Daily Payroll Job Billing Adjusted Quantity = 0 − Daily DOT Payroll Quantity and

   Daily Billing DOT Quantity = Daily DOT Payroll Quantity + (0 − Daily DOT Payroll Quantity) = 0

2. If Total Weekly Payroll Quantity > Weekly OT Hour Limit, then

   Daily Payroll Job Billing Adjusted Quantity = ((Total Weekly Payroll Quantity − Weekly OT hour limit) ÷ n) − Daily DOT Payroll Quantity and

   Daily Billing OT Quantity = Daily DOT Payroll Quantity + ((Total Weekly Payroll Quantity − Weekly OT hour limit) ÷ n) − Daily DOT Payroll Quantity so that

   Daily Billing OT Quantity = (Total Weekly Payroll Quantity − Weekly OT hour limit) ÷ n

Consider the following Job Billing Overtime Rule setup, where only weekly hour limits are entered for the two jobs:

Pgm: JBOVTMRU – Billing Overtime Rules; standard Treeview path: Job Billing > Setup > Local Tables > Billing Overtime Rules

For Job – 1400M,

• The weekly limits are:

  • Up to 40 hours for REG

  • Greater than 40 and up to 45 hours for OT

  • Greater than 45 hours for DOT

For Job – 1700M,

• The weekly limits are:

  • Up to 35 hours for REG

  • Greater than 35 and up to 45 hours for OT

  • Greater than 45 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, 1400M and 1700M, after going through Payroll Job Overtime Rule processing:

Pgm: PYTIMSTM – Timesheet Entry; standard Treeview path: US Payroll > Timesheet > Timesheet Entry

Job 1400M has a weekly total of 47 hours, where REG = 46, OT = 1, and DOT = 0.

Job 1700M has a weekly total of 46 hours, where REG = 40, OT = 6, and DOT = 0.

The hours by type are distributed as follows:

The resulting CSV 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).

Below are the detailed calculations for one of the jobs and the same logic applies for the other job as well.

Job – 1400M

The total Payroll hours posted for the week for the job is 47 hours, where REG = 46, OT = 1, and DOT = 0.

The number of unique entries/days in the Payroll timesheet is 6 (entries are for the 24th, 25th, 26th, 27th, 28th, and 29th of June).

Based on the Weekly Job Billing Overtime Rule setup, the Weekly REG Hour Limit = 40 and the Weekly OT Hour Limit = 45.

Daily REG Hour Limits are first calculated by dividing the weekly limits by the number of timesheet entries.

For this job, the

Daily REG Hour Limit = 40 ÷ 6 = 6.67 hours per day.

Next, the Daily OT Hour Limits are calculated by dividing the difference between the Weekly OT Hour Limit by the number of timesheet entries.

For this job, the

Daily OT Hour Limit = 45 ÷ 6 = 7.5 hours per day.

Using these derived Daily REG and OT Hour Limits, we can calculate the Billing Quantities for each hour type for each day entered in the timesheet for the job.

Date: 24-JUN-2024

8 hours are posted for the day, where REG = 8, OT = 0, and DOT = 0.

Since the total hours exceed both the Daily REG Hour Limit of 6.67 hours and the Daily OT Hour Limit of 7.5 hours, the Billing Quantity column includes REG, OT, and DOT hours.

REG Billing Quantity = 8 + (-1.33) = 6.67, where 8 hours are the Payroll REG Quantity and -1.33 hours are the Daily Payroll Job Billing Adjusted Quantity.

REG Unbilled Revenue Amount = 6.67 × 65 = $433.55

OT Billing Quantity = 0 + 0.83 = 0.83

OT Unbilled Revenue Amount = 0.83 × 75 = $62.25

DOT Billing Quantity = 0 + 0.5 = 0.5

DOT Unbilled Revenue Amount = 0.5 × 85 = $42.5

Date: 25-JUN-2024

8 hours are posted for the day, where REG = 8, OT = 0, and DOT = 0.

Since the total hours exceed both the Daily REG Hour Limit of 6.67 hours and the Daily OT Hour Limit of 7.5 hours, the Billing Quantity column includes REG, OT, and DOT hours.

REG Billing Quantity = 8 + (-1.33) = 6.67

REG Unbilled Revenue Amount = 6.67 × 65 = $433.55

OT Billing Quantity = 0 + 0.83 = 0.83

OT Unbilled Revenue Amount = 0.83 × 75 = $62.25

DOT Billing Quantity = 0 + 0.5 = 0.5

DOT Unbilled Revenue Amount = 0.5 × 85 = $42.5

Date: 26-JUN-2024

10 hours are posted for the day, where REG = 9, OT = 1, and DOT = 0.

Since the total hours exceed both the Daily REG Hour Limit of 6.67 hours and the Daily OT Hour Limit of 7.5 hours, the Billing Quantity column includes REG, OT, and DOT hours.

REG Billing Quantity = 9 + (-2.33) = 6.67

REG Unbilled Revenue Amount = 6.67 × 65 = $433.55

OT Billing Quantity = 1 + (-0.17) = 0.83

OT Unbilled Revenue Amount = 0.83 × 75 = $62.25

DOT Billing Quantity = 0 + 2.5 = 2.5

DOT Unbilled Revenue Amount = 2.5 × 85 = $212.5

Date: 27-JUN-2024

8 hours are posted for the day, where REG = 8, OT = 0, and DOT = 0.

Since the total hours exceed both the Daily REG Hour Limit of 6.67 hours and the Daily OT Hour Limit of 7.5 hours, the Billing Quantity column includes REG, OT, and DOT hours.

REG Billing Quantity = 8 + (-1.33) = 6.67

REG Unbilled Revenue Amount = 6.67 × 65 = $433.55

OT Billing Quantity = 0 + 0.83 = 0.83

OT Unbilled Revenue Amount = 0.83 × 75 = $62.25

DOT Billing Quantity = 0 + 0.5 = 0.5

DOT Unbilled Revenue Amount = 0.5 × 85 = $42.5

Date: 28-JUN-2024

9 hours are posted for the day, where REG = 9, OT = 0, and DOT = 0.

Since the total hours exceed both the Daily REG Hour Limit of 6.67 hours and the Daily OT Hour Limit of 7.5 hours, the Billing Quantity community includes REG, OT, and DOT hours.

REG Billing Quantity = 9 + (-2.33) = 6.67

REG Unbilled Revenue Amount = 6.67 × 65 = $433.55

OT Billing Quantity = 0 + 0.83 = 0.83

OT Unbilled Revenue Amount = 0.83 × 75 = $62.25

DOT Billing Quantity = 0 + 1.5 = 1.5

DOT Unbilled Revenue Amount = 1.5 × 85 = $127.5

Date: 29-JUN-2024

4 hours are posted for the day, where REG = 4, OT = 0, and DOT = 0.

Since the total hours is less than the Daily REG Hour Limit of 6.67 hours, the Billing Quantity column includes only REG hours.

REG Billing Quantity = 4 + 0 = 4

REG Unbilled Revenue Amount = 4 × 65 = $260

OT Billing Quantity = 0 + 0 = 0

OT Unbilled Revenue Amount = 0 × 75 = $0

DOT Billing Quantity = 0 + 0 = 0

DOT Unbilled Revenue Amount = 0 × 85 = $0