Payroll adjustments

re: CATS-186, etc

Multiple adjustments may need to be added to a shift’s record to influence the final financial impact of a shift - both to the worker and to the employing hospital.

Examples of adjustments include:

• flat dollar adjustment per shift
• percent dollar adjustment per shift
• flat dollar adjustment per hour
• percent dollar adjustment per hour

Each adjustment may affect either the worker or the employing hospital (but not both at the same time).

Adjustments will be created and recorded against the original shift, but the final calculations will use information from the related timesheet entry. Rationale for this split is that some adjustments may be created/recorded before a timesheet entry record exists (those are not created until a timeperiod exists).

Note: adjustments which are applied to hours worked would be calculated agains the ‘final’ recorded time, which would reflect any administrative changes made against someone’s actual recorded time. Example - someone clocked in at 9am and clocked out at 7pm, but should have clocked out at 5:30pm. The 5:30pm is adjusted by IV admins in the timesheet entry (as ‘final’ value) although the original clock in/out times are also kept. A shift adjustment of a $10/hr bonus would be applied to 8.5 hours, not 10, in the above example.

payroll adjustment data

Each payroll adjustment will be the same type of record, and a shift may have multiple adjustment rules applied to it.

|shift_id|created|updated|target|type|amount|party|actor|note

Fields:

• Target: ‘shift’, ‘time’ - is the adjustment for the shift, or time on the shift?

• Type: ‘fixed’, ‘percent’ - is the adjustment a fixed value or a percent?

• Amount: 50, -5, 8, 20

• Party: ‘hospital’, ‘doctor’

• Actor: who added the adjustment - may be ‘system’ if automatically applied

• Note: note describing why this adjustment was added

Examples:

• shift_fixed, 50, doctor = Doctor gets a flat $50 extra for this shift
• shift_percent, 10, doctor = Doctor gets a flat 5% bonus for this shift - 8 hrs @ $70 = $560 - adjustment is an additional $28.
• time_fixed, 5, doctor = Doctor at $65/hr for 8 hr shift is $480 - adjustment is $5/hr = extra $40

• time_percent, 10, doctor = Doctor with $60/hr for 8 hr shift is $480 - adjustment is extra $48

• shift_fixed, -30, hospital = Hospital gets a $30 flat deduction (credit?)

• shift_fixed, 50, hospital = Hospital gets a $50 surcharge adjustment
• shift_percent, 15, hospital = Hospital pays a 15% surcharge - doctor fee of $110/hr for 6 hr shift = $660. 15% surcharge is a $99 adjustment
• shift_percent, -10, hospital = Hospital get a 10% discount - doctor fee of $110/hr for 6 hr shift = $660.
  -10% adjustment is -$66 adjustment
• time_fixed, 12, hospital = Hospital get a $12/hr rate fee - doctor fee of $110/hr for 6 hr shift = $660 12/amount is 6 * 12 = $72 adjustment
• time_percent, 10, hospital = Hospital get a 10%/hr fee - doctor fee of $110/hr for 6 hr shift = $660 6 hrs @ (10% of $110 = $11) = $66 adjustment

Many of the adjustments may end up looking or behaving similarly, especially from the hospital’s perspective, but this will allow for data to be accounted for in a more granular fashion. A “$5/hr surcharge” can be an across the board applicable charge which is irrespective of percent of initial hourly expense - $100->$105, $110->$115, etc.

notes

Basic behaviour * create adjustment on shift * read list of all adjustments * alter individual adjustment info * purge individual adjustment (soft delete)

Can generally only be calculated with a related timesheet entry. * calculate individual adjustment value * calculate total adjustment value

when adding, need a way to group together multiple incentives related to each other - first(only?) use of this would be an incentive which affects each party which would create 2 incentives. we can add the incentive id, but having a logical ‘grouping’ id would be more generalizable to other non-incentive-related uses.
The UI could show ‘both parties’ and allow ad-hoc editing of both but still treat them as 2 separate, but grouped, adjustments.

The calculations need to get initial doctor rate for the date of the shift, hospital rate for the date of the shift, etc.

The ‘hospital rate’ is not historical or managed like employee rate, and would need to be, to determine ‘rate at historical point in time’, and to allow for audited changes. new ticket?

need to ‘stack’?

60 base rate @ 6 hrs = $360 incentive A add $5/hr = $65/hr @ 6 hrs = $390 incentive B add 10% = add 10% on to $390 (not $360) : $39 = $429

5/19/21

Initial thought was that each adjustment entry could store the value of its own calculation (‘calculated’ column) - multiple straight dollar amount adjustments could be done that way, but it wouldn’t necessarily work with percentage adjustments. My initial thinking was that those might be chained - 5% then 10% yields a different result from 10% then 5%. It seems they’re thinking that those values should just be combined in to a 15% adjustment (may need to confirm that is inteded), but that still sort of necessitates a separate ‘final adjustment amount’ to be stored some place.

Unsure where best to store this yet. It doesn’t really belong to ‘timesheet’ because we’ll need to store something similar (or… identical) related to other parties (hospital/invoices). A ‘payroll adjustment rollup’ or ‘payroll adjustment total’ may be the ticket?

Leaning towards another ‘shift_adjustment’ record with a type of ‘final’.

there are other ‘things’ that aren’t captured still - non-shift pay entries - that we’re ignoring(? don’t want to forget entirely)

5/20/21

Separated out ‘final’ payroll adjustments. We have 2 CalculatedPayrollAdjustment entries for each shift:

• one for ‘rate’ (which would affect ‘hourly’ calculations)
• one for ‘flat’ (per shift calculations)

open question

https://phpspreadsheet.readthedocs.io/en/latest/topics/calculation-engine/#performing-formula-calculations

might it be useful to use that for business calculations and let people just upload a spreadsheet with defined calculations and plug in values? you’d need to identify/name key cells as input/output, but… might be handy?

retrieved rows

payrollAdjustmentService::getAdjustmentRowsForShift will return rows

Row structure * type = string :’rate’ if it’s a rate adjustment or ‘shift’ if it’s a payroll adjustment * list = array of adjustments * each array has 2 keys: * party (target party) * adjustment (full adjustment record - amount, label, etc)

NOTE - ‘type’ could also be ‘final_rate’ or ‘final_flat’ These are records indicating the calculated final adjustment for each ‘section’ (hourly rate adjustments and ‘flat’ adjustments to the shift overall)

This was structured in a way to make it (hopefully) useful for front-end manipulation.

5/21/21

Building issues on gh action at the moment.

Some conflation of ‘rate’ and ‘hours’ and ‘flat’ and ‘shift’ between target/type in frontend/backend. Worked around, for now but need to iron that out.

5/26/21

for ‘position’ and ‘incentive’ mgt, we’ll have a separate table - shift_adjustment_templates - with pointers to incentive and position ids.

The shift_adjustments table will also have pointers to incentive and position ids, and will have a pointer to the template_id used as well.

Was going to skip a SAT model, but am putting one in after all for some convenience.

The shift_adjustments_template table will also have ‘effective date’ for which template to use. When looking at a shift, the system will

• check the position of the shift, then look for any related adjustments to the position which have a matching effective date
• check the incentives on the shift, then look for any related adjustments to the incentives which have a matching effective date

future

Thinking about this further, it would be possible to extend this model some to allow for adjustments to be associated with specific hospitals as well, which might be useful. One could already associate a specific incentive to a specific hospital and associate an adjustment with that incentive that way, but we could explore just associating with hospitals directly in future.

5/28/2021

Thinking out loud here - by just doing an ‘effective date’, (instead of date range), we have some potential issue with getting multiple adjustment templates for a specific position or incentive (or do we?). Default ‘go to’ move is to grab the items past ‘effective_date’ then sort by date and grab the first one, getting the ‘correct’ one. But should we support multiple adjustments/templates per position/incentive?

template_id	position	incentive	date	value....
2	7	null	2021-05-03	..values1..
3	7	null	2021-05-03	..values2..
4	7	null	2021-05-03	..values3..
4	7	null	2021-05-05	..values4..

on may 3 and 4, we have 3 templates active for ‘position 7’. is this correct/desired? if we just go with ‘effective date’, we’d need a way to mark one (or more) as ‘inactive’. Or… use an effective date range. There may be a time when we’d want to completely disable adjustments for a particular position/incentive, but with only effective date, we can’t do that - there’s always something ‘current’ (unless you edit one to have a ‘future’ date, I suppose?)

WORKAROUND also could be to just set a new adjustment of $0.

WRT issue on multiple adjustments effective on same day, do we limit this to just one?

Example: if there’s an adjustment on 5/3/21 for position ER, then another one submitted for the same day/position, that second one replaces the first?

6/1/21

Working on position/incentive payroll adjustment template UI - we should allow deleting or… perhaps just ‘inactivating’.
We’d need to make this ‘inactive’ - because we
point to the shift_adjustment_template in shift_adjustments. This may not be strictly necessary, but… even if soft deleted, that may still cause some linking problems later (or may have to start putting ‘with trashed’ in some queries, but not all…)

Thought - “closed/open” vs “inactive/active”?
or “closed/available” ? potentially less confusion?

6/10/21

Payroll Adjustment Template mangagement screen is now linked from ‘tools’ page. Currently taken away from individual ‘position’ and ‘incentive’ mgt screens - we could potentially put back some read-only view of related data there if desired (may make sense).

SAT mgt screen explicitly refers to ‘incentive’, ‘position’ and ‘custom’ types of templates. Incentive is related incentive_core_id, Position is related to ‘position_core_id’ and an SAT record unrelated to either *_core_id column is an ‘custom’ type.

Adjustment template records that are past ‘now’ are not allowed to be deleted, but later ones can be affected.

The ‘deletion’ process may be something we tie to ‘closed’ time periods in future, but not in place currently.

6/11/21

This has to be rewritten to just be “one adjustment has all information for all parties in one record”. Trying to do this as “per party”, even with logical groupings, is creating an impossible headache trying to map to the screens already in place.

Having each adjustment definition be separate doesn’t fit the vision, so the data and code is having to change.

6/15/21

endpoints

get list

payrollAdjustments.getlist

/ajax/payrollAdjustment/getlist/{shift}

Keys: * timesheet_entry_hours * timesheet_entry * adjustment_rows * shift * employee_rate * employee_job

adjustment_rows key

{
  "hours": [],
  "flat": [],
  "rate": [],
  "final_rate": {
    "type": "final_rate",
    "adjustment": {
      "id": 899,
      "label": "",
      "target": "final",
      "type": "final_rate",
      "doctor_amount": "0.00",
      "hospital_amount": "0.00",
      "doctor_calculated": null,
      "hospital_calculated": null,
      "note": "final adjustment",
      "actor_id": null,
      "shift_incentive_id": null,
      "shift_id": "7240c0f0-cd64-4d83-8c94-c73d615e4a66",
      "core_id": "01f87z559txn8zetx5szc8t1km",
      "deleted_at": null,
      "created_at": "2021-05-14 14:00:00",
      "updated_at": "2021-05-14 14:00:00",
      "position_core_id": null,
      "incentive_core_id": null,
      "template_core_id": null
    }
  },
  "final_flat": {
    "type": "final_flat",
    "adjustment": {
      "id": 900,
      "label": "",
      "target": "final",
      "type": "final_flat",
      "doctor_amount": "0.00",
      "hospital_amount": "0.00",
      "doctor_calculated": null,
      "hospital_calculated": null,
      "note": "final adjustment",
      "actor_id": null,
      "shift_incentive_id": null,
      "shift_id": "7240c0f0-cd64-4d83-8c94-c73d615e4a66",
      "core_id": "01f87z55a0hatvdh0c57japm5j",
      "deleted_at": null,
      "created_at": "2021-05-14 14:00:00",
      "updated_at": "2021-05-14 14:00:00",
      "position_core_id": null,
      "incentive_core_id": null,
      "template_core_id": null
    }
  },
  "final_hour": {
    "type": "final_hour",
    "adjustment": {
      "id": 898,
      "label": "",
      "target": "final",
      "type": "final_hour",
      "doctor_amount": "4.00",
      "hospital_amount": "4.00",
      "doctor_calculated": null,
      "hospital_calculated": null,
      "note": "final adjustment",
      "actor_id": null,
      "shift_incentive_id": null,
      "shift_id": "7240c0f0-cd64-4d83-8c94-c73d615e4a66",
      "core_id": "01f87z559k5pmahy0vt9kzv1jv",
      "deleted_at": null,
      "created_at": "2021-05-14 14:00:00",
      "updated_at": "2021-05-14 14:00:00",
      "position_core_id": null,
      "incentive_core_id": null,
      "template_core_id": null
    }
  }
}

timesheet entry key

"timesheet_entry": {
    "id": 754,
    "shift_id": "4bf1d761-ad4c-4af8-939b-9591d1bb9a35",
    "timesheet_id": 377,
    "public_note": null,
    "admin_note": null,
    "reviewed_at": null,
    "reviewed_by": null,
    "scheduled_start": "2021-05-14 14:00:00",
    "scheduled_end": "2021-05-14 18:00:00",
    "clocked_start": "2021-05-14 14:00:00",
    "clocked_start_note": "testing clockin",
    "clocked_end": "2021-05-14 18:00:00",
    "clocked_end_note": "testing clockout",
    "final_start": "2021-05-14 14:00:00",
    "final_end": "2021-05-14 18:00:00",
    "created_at": "2021-05-14 14:00:00",
    "updated_at": "2021-05-14 14:00:00",
    "deleted_at": null,
    "core_id": "01f881bsyk1m9zjp8dbfcwmzcz"
  },

employee_rate and employee_job keys

  "employee_rate": {
    "id": 24,
    "effective_date": "2020-01-01",
    "hourly_rate": "60.00",
    "employee_core_id": "01f881bsm7eyxp34v6djynts95",
    "actor_id": null,
    "note": "testing",
    "deleted_at": null,
    "created_at": "2021-05-14 14:00:00",
    "updated_at": "2021-05-14 14:00:00"
  },
  "employee_job": {
    "id": 24,
    "effective_date": "2020-01-01",
    "job_classification_id": 29,
    "employee_core_id": "01f881bsm7eyxp34v6djynts95",
    "actor_id": null,
    "note": "testing job",
    "deleted_at": null,
    "created_at": "2021-05-14 14:00:00",
    "updated_at": "2021-05-14 14:00:00",
    "job_classification": {
      "id": 29,
      "title": "Full Time",
      "abbreviation": "FT",
      "paid_hourly": true,
      "minimum_weekly_hours": "34.00",
      "minimum_monthly_shifts": null,
      "deleted_at": null,
      "created_at": "2021-05-14 14:00:00",
      "updated_at": "2021-05-14 14:00:00"
    }
  },

payrollAdjustments.addrow

/ajax/payrollAdjustment/addrow/{shift}

Return payload

{
  "status": "OK",
  "rows": [
    {
      "shift_id": "4028036b-5af1-4918-90c5-dce03efad91a",
      "doctor_amount": 10,
      "hospital_amount": 0,
      "target": "rate",
      "type": "fixed",
      "actor_id": "08ce11a7-6ca5-4ec5-8b65-b3f4d94360fc",
      "label": "sample adjustment 1",
      "note": "sample note",
      "shift_incentive_id": null,
      "core_id": "01f87z90rx4zh55fwa85mh2r0x",
      "updated_at": "2021-05-14 14:00:00",
      "created_at": "2021-05-14 14:00:00",
      "id": 904
    }
  ]
}

payrollAdjustments.updaterow

/ajax/payrollAdjustment/update/{payrollAdjustment}

Payload

{
    "status": "OK",
    "rows": [
        {
            "id": 941,
            "label": "sample adjustment 1",
            "target": "rate",
            "type": "fixed",
            "doctor_amount": 5,
            "hospital_amount": 0,
            "doctor_calculated": null,
            "hospital_calculated": null,
            "note": "sample note",
            "actor_id": "3b061517-8f9b-4f02-b7a3-b61e5085d9d6",
            "shift_incentive_id": null,
            "shift_id": "993b8f8b-8cfd-4846-9c1e-629ce1cbd440",
            "core_id": "01f87zgf1ht7b713ffj68fmmxb",
            "deleted_at": null,
            "created_at": "2021-05-14 14:00:00",
            "updated_at": "2021-05-14 14:00:00",
            "position_core_id": null,
            "incentive_core_id": null,
            "template_core_id": null
        }
    ]
}

payrollAdjustments.deleterow

/ajax/payrollAdjustment/delete/{payrollAdjustment}

Payload

{
    "status":"OK"
}

6/16/21

Note: employees table needs to have job_classification_id and relation - employee job classifications should be looked up with a date via employeeService. Removing the field itself breaks existing tests, so there’s more refactoring to do.

UPDATE: ad-hoc ‘type’ was changed to ‘custom’. UPDATE: ‘final_calculated’ was added as a top level key in list payload to show the final calculated rates/costs.

6/17/21

Note: ‘custom_templates’ key is returned in payroll adjustment list with custom templates that were available at that time. There may be deleted templates included - check for deleted_at flag if necessary.

7/5/21

Pushing first version of this. There have been some changes and some of the endpoints may not be used any more - need to review and determine which aren’t.

‘addAutomaticAdjustments’ method was added and is called before calculatePayrollAdjustments. This is called in multiple places, but Timesheetservice::updateRelatedPayrollAdjustments is most useful, as it will recalculate adjustments any time any change triggers ‘updateTimesheetsInPeriod’. There will need to be a related equivalent trigger for invoice time periods, (and… invoice time periods will need to be created).

ParyollAdjustmentService::addAutomaticAdjustments will delete any existing automatic adjustments (based on associated templates) then re-add.
Initially caused problem because of ‘notes’ being lost - code and test was added to retain the note from old row and re-apply to related new row. There is likely a small efficiency to be gained by not deleting up front, but marking for deletion, then remove the ‘marked for deletion’ flag if the same row/rule would be added (but… this may still cause problems it the amount/values have changed, which is why the ‘delete all first’ approach was taken).

The UI - there’s some awkwardness with automatic ‘POST’ changes on each cell change and auto-tab focus changes. No clear/easy win here, but we can likely review in the upcoming weeks.

The ‘hospital’ changes were done as well as the ‘doctor’ changes, which means ‘invoice’ export is jumpstarted.

NEED: upcoming need for ‘time period type’ to differentiate between ‘payroll’ time periods and ‘invoice’ time periods. Invoicing may be monthly, for example, and payroll is every 2 weeks - some overlap, but they’re not the same.

7/7/21

Previous night push triggered slowdown to point of unusability.

Updating of a shift triggers ShiftDetailsChanged event, picked up by ShiftDetailsChangedNotification. This called updateTimesheetEntryForShift, which called updateTimesheetsInPeriod, which eventually would call updateRelatedPayrollAdjustments for each sheet. This iterates over each entry in a doctor’s timesheet, and reprocesses the automatic adjustments and recalculates all adjustments and payroll info.

This was done primarily for completeness - to ensure this happened transparently without requiring any explicit steps by the user. Any changes to incentives or adjustment templates would generally be recalculated without explicit user interaction. However, this is a bit too expensive with larger sets.

CHANGES:

Calls to updateTimesheetsInPeriod were changed to be either updateTimesheetsWithoutAdjustmentsInPeriod or updateTimesheetsAndAdjustmentsInPeriod, and updateTimesheetsInPeriod was marked as protected, to prevent direct external use.

updateTimesheetsAndAdjustmentsInPeriod will trigger full recalculation of all adjustment/financial data, and is currently only triggered from the refreshPeriod() ajax endpoint, available by pressing the “refresh period” blue button in the timesheets view. Other calls were converted to updateTimesheetsWithoutAdjustmentsInPeriod which operates as before, updating timesheet info (clocking, hours, etc) without involving financial/adjustment/payroll information.

The ‘recalculate everything’ approach generally shouldn’t be neeed outside of situations where modifications to templates are made (perhaps a new incentive adjustment is created?). In the cases where an individual shift is edited, the adjustments are recalculated for that specific shift.

The ‘blue button’ will be necessary to click when wanting the most accurate up-to-date view of data. Individual shift changes will generally update as needed, but ‘new’ shifts won’t have all the attendant related data (but… in most cases, they won’t have a doctor assigned anyway, so there’s not much impact there).

The ‘update individual shift adjustments’ should probably be looked at being added to shift creation, shift clock in/out, and shift take/untake steps, to try to keep the timesheet view relatively up to date without huge impact. However, the ‘recalculate everything’ should still be regarded as a necessary step to ensure any incentive/position/adjustment changes done after people work are reflected accordingly.

locally the full updateTimesheetsAndAdjustmentsInPeriod process takes around 13-16 seconds (with full data from the server). Originally, locally, this took ~30 seconds. A few logical optimizations and database indexes were added to reduce the execution time. The production server seemed to take over a minute in some cases, but should see a corresponding ~50% execution reduction.

walking through this, some SQL logging was put in place, and some indexes were added in to tables which seemed appropriate candidates, and speed improvements were noted (see above). More may be able to be done in future.

Quick approach to log/view app queries to quickly filter for potential speed problems.

// in AppServiceProvider.php

public function boot() {
    \DB::listen(function($query) {
        $sql = $query->sql;
        $bindings = $query->bindings;
        $executionTime = $query->time; // in ms
    });
}

afternoon update

Have done some rework of the ‘export’ for payroll, largely with a few more indexes and some local static caching in PayrollAdjustmentService::getAdjustmentRowsForShift call.

This took ‘export’ query count from ~25000 to ~2000. (Other export query runs were higher and lower, but this caching seems to help.)

Added config/core.php flag to enable dynamic toggling of cache flag. The earlier “caching in the class only” without a way to toggle it was interfering with the tests (and could possibly interfere with other code that assumed it would always recalculate when asked, vs using cached info).
caching flag defaults to false, and only payroll export and timesheet ‘refresh’ will enable the caching.

public function getAdjustmentRowsForShift(Shift $shift): Collection
{
        $useCache = config('core.payroll_adjustment_service.cache');
        // etc...

7/14/21

Updating payrollAdjustmentTemplateService to prevent adding template/rules that have effective dates in a closed period.

7/28/21

Reviewing CATS-321 work, ‘template_core_id’ needed to be added to created adjustments when a template was the basis. When recalculating, if an adjustment was based on a template, and the template’s update date is after the current row’s update date, we’ll need to delete the adjustment and recalculate (presumably the template values have changed and require new calculation).

The ‘template_core_id’ existed in the payroll_adjustment table, but had not been (yet?) filled in, so there was nothing to compare against (this date comparison was a potential optimization that needed to be pushed up to now).

This is largely in service of reducing duplicated messaging. When an adjustment is deleted, it will also be noted/logged.

NOTE - because earlier adjustment rows do not have a template_core_id attached, there is a migration to find and attach template_core_id values to the existing payroll adjustments where they match. There are only a handful of distinct template IDs in use at this point (under 20) so the change shouldn’t be too far-reaching. We may see one additional ‘adding’ activity log entry of an existing adjustment based on a template for already existing shifts, but that should be the end of it.

update payroll_adjustments
set template_core_id=pat.core_id
from payroll_adjustments pat
where (
              payroll_adjustments.type = ''
              and payroll_adjustments.target = pat.target
              and payroll_adjustments.doctor_amount_type = pat.doctor_amount_type
              and payroll_adjustments.doctor_amount = pat.doctor_amount
              and payroll_adjustments.hospital_amount = pat.hospital_amount
              and payroll_adjustments.hospital_amount_type = pat.hospital_amount_type
          )

Aug 17, 2021

DD-328 came up - I can’t find earlier reference, but thought there was a note that the potential for duplicates might come up. Initially, ‘template_core_id’ was not associated with adjustment entries, even if they were built from a template.

This was changed in a recent version, but we have a situation where some existing template_core_id in payroll_adjustment entries, although I’m not sure how/why they got there. Initial review of code doesn’t show how that happened.

Code change and migration added to remediate this.