Multiple problems with Payroll

[volkswagner]

This is the first year using payroll. I thought I had everything figured out and working but I’m now seeing several issues. Any help is greatly appreciated.

1. When opening a Salary Structure assigned to an employee, with several pay periods already completed, the year_to_date field is zero for all components. Is this normal? I’m expecting to see $2,000 * 4 pay periods or $8,000.

2. It seems Condition statements are ignored, but perhaps the issue is related to the above missing YTD amount.

The following component is still being calculated even after ‘gross_year_to_date’ is $8,000.

My environment:
ERPNext: v15.58.2 (version-15)
Frappe Framework: v15.65.2 (version-15)
Frappe HR: v15.43.1 (version-15)

Montly payroll with one employee.

I’m using the built-in workflow (Create Payroll Entry, Submit, then Submit Salary Slips from Payroll Entry docType).

Salary slips and journal entries are correct.

Thanks in advance for looking.

[peterg]

@volkswagner

When opening a Salary Structure assigned to an employee, with several pay periods already completed, the year_to_date field is zero for all components. Is this normal? I’m expecting to see $2,000 * 4 pay periods or $8,000.

Where exactly are you seeing this? The Salary Structure document a template, not specific to any particular employee, and likewise the YTD value there will always be zero. There should be, however a running total in the component child tables in the salary slips. (YTD values are, if I remember correctly, exclusive of the current month, but if you’ve run 4 months already there should be something there.)

@volkswagner

It seems Condition statements are ignored, but perhaps the issue is related to the above missing YTD amount.

Very possibly. It’s worth figuring out the first issue before troubleshooting this one, I think.

[volkswagner]

Thank you @peterg.

The screenshot is indeed from a Salary Component, from my Salary Structure (Submitted and assigned to a single Employee). If you are correct, it seems that field should not exist in this doctype (it would always return zero). This would mean it does not indicate an issue with my setup.

I believe you are referring to tabSalary Detail which has all of the entries for each component including transactional detail from Salary Slips.

Indeed Salary Detail has the field ‘year_to_date’ and it correctly is accumating the amount from each salary slip.

Using the following query I can confirm gross_year_to_date is over 7000.

According to ChatGPT I should be using ‘base_year_to_date’ not ‘gross_year_to_date’ in my conditional statement.

Can anyone confirm, or let me know how to verify if my statement is true or false?

EDIT:
I don’t think that is correct. Since I’m not using base. When I look at the most recent salary slip, base_year_to_date = 0, gross_year_to_date = 8000

[peterg]

@volkswagner

The screenshot is indeed from a Salary Component, from my Salary Structure (Submitted and assigned to a single Employee). If you are correct, it seems that field should not exist in this doctype (it would always return zero). This would mean it does not indicate an issue with my setup.

I believe you are referring to tabSalary Detail which has all of the entries for each component including transactional detail from Salary Slips.

The reason it exists there is that both Salary Structure (the template) and Salary Slip (the employee- and period-specific instance) use the Salary Detail child doctype. If you’re seeing accurate values in the salary slip components, that’s what’s being counted.

@volkswagner

According to ChatGPT I should be using ‘base_year_to_date’ not ‘gross_year_to_date’ in my conditional statement.

gross_year_to_date is a field in your salary slip, and the formula should have access to it. I have no idea what base_year_to_date is. I can’t find any reference to it in the source other than the field def. My hunch would be that it’s a legacy field of some sort.

As for why gross_year_to_date isn’t being picked up correctly, it’s tricky to troubleshoot. I’ve usually had to use the debugger in my development environment to pinpoint exactly what the issue is. I’m not sure offhand, for example, when the gross_year_to_date value is populated.

[volkswagner]

This is not the first time the Condition statement has caused issues. I think moving forward, I will avoid using it and put all logic in the formula instead.

[fiveoaks]

@peterg

The reason it exists there is that both Salary Structure (the template) and Salary Slip (the employee- and period-specific instance) use the Salary Detail child doctype. If you’re seeing accurate values in the salary slip components, that’s what’s being counted.

This is correct. You have to use the Salary Slip to see the year to date. Both are sharing the same child table.

@volkswagner

This is not the first time the Condition statement has caused issues. I think moving forward, I will avoid using it and put all logic in the formula instead.

I’ve never tried using the gross_year_to_date field in a condition but have successfully used it in a formula which both basically are using the same set of formulas. Were you trying this for the FUTA calculation? This is how I did it without use a condition. While I think I am doing it correctly, I hold no responsibility if it’s wrong.

0 if ( ( gross_year_to_date - BS ) >= 7000 ) else ( ( 7000 - gross_year_to_date ) * ( 6 / 100 ) ) if ( ( 7000 - gross_year_to_date ) < BS ) else BS * ( 6 / 100 )

[volkswagner]

Thank you @fiveoaks.

Yes, my example was for FUTA. I have settled on the following formula (also no longer using the condition field as it seems to be ignored).

0.006 * (
    (7000 - gross_year_to_date) 
    if gross_year_to_date < 7000 and gross_pay > (7000 - gross_year_to_date)
    else gross_pay
) if gross_year_to_date < 7000 else 0

I’m curious if your formula works when the current pay period causes gross_year_to_date to cross the $7,000.00 threshold. It seems the first part
of the formula can yield true and assign zero FUTA.

I think the following can be true if the gross_year_to_date is 6500 and the bs > 500 as an example, the following would be true, no?

0 if ( ( gross_year_to_date - BS ) >= 7000 )

[fiveoaks]

I’d have to dig into it again to confirm but I think the gross_year_to_date was including the current salary slip in it’s total and hence why I was removing the base salary before comparing.

In your equation, I think you have an extra 0 in your percent. Shouldn’t it be 0.06 * ?

[volkswagner]

@fiveoaks I think the FUTA rate may vary by state. In NY, our unemployment is assessed on the first 11,800. On form 940 for FUTA, every state pays .006 X 7000 of applicable earnings. If you’re in a state without state unemployment, then you’ll also add .054 * 7000 or a total of 6%.

Reference form 940 lines 8 & 9.

You had me “scared” for a minute

[fiveoaks]

@volkswagner

I think the FUTA rate may vary by state.

Yes, your right. It varies by state. We have to pay the full 6%.

[trustedcomputer]

@volkswagner I’m a little late to the party, but I’d like to help if I can. I have what I consider to be a working v.15 setup in the US with no “state” portion so all 6% goes to the feds.

1. YTD “should” be working in your calculations in your salary components–> salary structure–> salary structure assignment–>salary slips. If it’s not, its a configuration issue (which is very understandable due to the complexity of it all), not a bug.

2. I have never used conditions for any of the salary components, I don’t think I could ever get them to work. But happily, they all work great in the formula. Here is what I do for FUTA. After the first 7K, even in the middle of a check, it results in no more FUTA for that employee until the end of the year:

0 if ((gross_year_to_date - BS) >= 7000) else 
0.06 * (7000 - (gross_year_to_date - BS)) if (gross_year_to_date > 7000) else 
0.06 * BS

I’d be happy to share any more of my configuration with you that might be helpful, even though some of it is a bit hackish (though I try to avoid that when possible).

[volkswagner]

@trustedcomputer

Who says code can’t be poetic?

Love how your formula leads either the zero argument!

Perhaps I should file a bug report regarding the condition being ignored.

[jroyDC]

I’m testing with using the gross_year_to_date field from the salary slip and it works well for the most part, but I’m seeing some strange behaviour in some cases.

For example, on the first pay of the year, you’ll notice the gross_year_to_date is $0, you can process a salary slip and all the deductions are correct, hit save and notice the gross_year_to_date now includes the the current amount, now hit Submit, and your deductions will be re-calculated using the new gross_year_to_date which can be problematic if you’re using marginal tax rates like we do in North America.

[volkswagner]

The gross_year_to_date is appended immediately (for Earnings Component) in the salary slip. This is why it’s important to maintain the proper order of the Earnings Components. You shouldn’t use gross_YTD in an earnings component, then later add/subtract in another earnings component.

The salary slip calculations occur in order (earnings components first, then deduction components).

@jroyDC Your findings are expected.

[jroyDC]

TY, this is good to know.
I was using the gross year to date to calculate some tax deductions.
In Canada we have for example, the Canada Pension Plan deduction, it has 2 components.

CPP - 5.95% up to $71,300, with the first $3,500 exemption, maximum $4034.10

CPP2 - 4% from $71,300.01 to $81,200, maximum $396

Say you reach $80,000 and add your weekly base of $2000, reaching $82,000, Using the gross year to date, you can figure out the deduction of 4% of the remaining $1,200.
But I was struggling in my test because when you run payroll entry or salary slip, the gross year to date is one thing, but when you submit the slip, the gross is now included and changed the deduction calculation.

In this case, what’s the best approach to calculate deductions based on %, maximum, and exemptions such as CPP?

And this structure is common across most Canadian income taxes.

And like some previous comments, I have not been able to get the Conditions to work.

[volkswagner]

I’ll take a stab at it (using what I’ve learned from @trustedcomputer)

0 if ((gross_year_to_date - BS) >= 71300 or (gross_year_to_date - BS) <= 3500) else 
0.0595 * (71300 - (gross_year_to_date - BS)) if gross_year_to_date > 71300 else
0.0595 * (gross_year_to_date - 3500) if (gross_year_to_date >= 3500 and (gross_year_to_date - BS) <= 3500) else
0.0595 * BS

The first line should run when they’ve reached the cap of 71300.
The second line should run when they have a pay period that crests
over the 71300 (only applies against the difference up to the 71300.
The thrid line should run when they crest above 3500 in a pay period
and the last line should run for all other conditions.

Please note that I’m not an expert and haven’t even tested this formula.

[jroyDC]

This formula is similar to what I had, and I tested it and I can say it works well, but using Gross Year To Date still causing issues in my Save-> Submit where things are being recalculated.
In my example, I’m using a single slip for $80k to span CPP and CPP2, the numbers on Salary Slip are bang on, see screen shot Before Save.
The I save the Form, see Screenshot after Save-> Gross Year To Date changed from $0 to $80k.
Now see the screen shot on Submit, the Deductible Components are dropped and re-calculated based on the Gross Year To Date after the save Event.
this is what I’m fighting with right now, and I’m not sure this is normal behaviour.

Once a save is done, that should be it, Submitting should post the saved values as displayed on the scree otherwise all your GL entries are going to be changed between Save Submit. No More re-calculations after a save!!

[volkswagner]

Can you post your formulas?

The behavior seems to be related to misconfigured calculations.

Is this a real-world scenario? Will employees receive 80K in one pay period?

The formula I posted would likely fail in this scenario, because gross and gross_year_to_date are equal.

In order to cover the scenario you are testing, you would need to add additional lines to capture this specific situation.

In your test slip the following is true, but not matched in the formula.

.0695 * (71300 - 3500) if (gross_year_to_date - gross_pay) < 3500 and gross_pay >= 71300

[jroyDC]

thanks for the help, I appreciate the time spent on your part.
Maybe I’m not explaining myself well, or maybe I’m simply misunderstanding, but using the formula for CPP

0 if ((gross_year_to_date - base) >= 71300 or (gross_year_to_date - base) <= 3500) else
0.0595 * (71300 - (gross_year_to_date - base)) if gross_year_to_date > 71300 else
0.0595 * (gross_year_to_date - 3500) if (gross_year_to_date >= 3500 and (gross_year_to_date - base) <= 3500) else
0.0595 * base

I changed the base salary to $5000, the first pay slip, the deductions are $0 since the first $3,500 are exempt, but we should be taxing the $1500, but that I think I can address in the formula.

The 2nd pay slip, of another $5,000 gets us an initial Earning and Deductions screen with $0 deductions, and $0 in Gross Year to Date

Before Save - This looks perfectly fine.

Hit Save
now Gross Year to Date is updated to the correct OK, looks good, no deductions, well odd, but OK, should have a CPP Deduction.
I expect the ERPNext to calculate the Deductions and update Gross after the save, but it only does the gross.

Hit Submit
This is my main issue, why are things being re-calculated on Submit, shouldn’t the salary slip be calculated on SAVE? Submit should essentially just confirm what’s in the screen.

But Looks what happens here after I submit, deductions calculated. This should have been done prior to submit and during the save.

Again, I appreciate the help, if I’m doing something fundamentally wrong here, please let me know, and I can modify things on my end, but I really don’t think calculations after user hit Submit that can potentially change the Slip should be happening at all.

[volkswagner]

Do you have other salary slips that are not submitted?

There is something wrong if you have zero for gross_ytd and 5K for base, then saving changes gross ytd to 10K.

You are using the variable ‘base’ which comes from the salary structure assignment. What is the value for base in the assignment?