Share
ReportIs there a way in GP Payroll to set a max amount for deduction, but apply to two different deductions (401k and Roth)?
Carol
*This post is locked for comments
You’re offline. This is a read only version of the page.
Is there a way in GP Payroll to set a max amount for deduction, but apply to two different deductions (401k and Roth)?
Carol
*This post is locked for comments
Lisa, I'm just now seeing your post regarding the retirement plan product from Integrity Data. Did you end up using it? How do you like it? I'm facing the same problem, and may need to use such a product as well.
There is a product by Integrity Data that is an Enhancemed Retirement Plan add-on that allows for managing multiple deductions within a single maximum. We are in the processing of implementing it to overcome the same hurdle.
Hey Carol,
As I hinted at earlier, we use two different benefit codes, so I had not noticed that. Guess you can teach an old dog new tricks. :)
You are correct about the error you received, as we have never really tried it, I never noticed. Seems like after you select the first code the list would "grey out" or something to let you know you cannot select another code rather than just waiting for you to try and give you an error. But that is beside the point.
So, with two Deduction codes for your Pension Plan, one possibility is what we do. Have two Benefit Codes for the Pension Plan as well, one for each deduction. Both codes are setup the same with the exception of which deduction they are based on. Additionally, and the main point for Financial Reporting in that on the "Tools > Setup > Posting > Payroll Accounts" both benefit codes are mapped to post to the same account while the two deduction codes are mapped to different accounts. That way finance can look at the three accounts and see how much the employee put into their 401k or their ROTH and how much the company matched them.
Additionally, keeping in mind that as the Deductions max out the benefits stop. However, depending on your exact Pension Plan there may possibly be a problem with the employer over matching the Pension Plan because it is split into two codes. If this is the case at your company you may or may not need to have some sort of alert to let you know that the benefit side is incorrectly setup. If that is the case feel free to let me know and I should be able to post the code that we use to insure that the company does not over match employees because they have both deductions. But again, it is dependent upon you company's specific Pension Plan. You may not have a problem with that at all. Most other companies that I know of do not have a problem with over matching because of the specifics in their Pension Plan. Ours is just different from theirs in that respect.
Hope this helps,
Hi John,
We tried this and in the Benefit Code setup window, if you select more than one deduction, you get error.
"A benefit can only be based on one deduction".
Thanks for the input.
Carol
Hum, to be honest I never really thought about it that much. We use a separate code for each of the benefits to match the two deductions. Our setup, is to match 100% of the deduction up to an employer max of 2%. so that when the deduction maxes out for the year the benefit stops as well.
So on the Benefit Setup screen the Method dropdown is set to Percent of Deduction. Benefit Tiers is set to Single with 100% for the value. Employer Maximum is set to 2%. The Based on: is set to Deduction. The Selected option button is selected and the only code under the Selected box is the single deduction that we want that benefit to based on.
However, as we are selecting the single deduction code for each of those benefit codes, I see no reason why you could not select both the 401k deduction and the Roth deduction in the Selected box for your single benefit code. And if both of the deductions are maxed out then the benefit should stop as well.
hope this helps,
Hi John,
I also see the client asked if there can be one employer match applied to two pension benefits. Would this monitoring tool you suggest be something they can use if we associated with the Benefit code instead of Deduction code?
Carol
Thanks John! This is quite a extensive reply. I will be talking to my client this morning and hopefully can offer that we can assist with some monitoring tool like this.
Too bad that there isn't an option for apply a max accross multiple deductions similiar to Garnishment Max.
Thanks,
Carol
You have got to love how the editor changed for font colors and spacing. But hopefully your DBA can figure out the code.
Hey Carol,
I am not sure which version of Microsoft Dynamics GP you are using, nor if you are using percentage or fixed amounts on your Pension Plan deductions. Also, please keep in mind there is always more than one way of doing anything. In addition, I am unaware if this is fixed in the current I just have not checked, as our solution works, at least for us.
We are using percentages where I work and two different codes just as you are, so we ran across this problem, years ago, the following is what we came up with, which does include some work on the part of your Database Administrator.
First, if you consider that an employee’s Pension Plan has a max dependent upon their age. Currently it is 17,500 or 23,000.
Now, if you are using percentages you have to determine the dollar amount for the max for each code based upon the total percentage times the employees max.
Therefore, you end up with the following math:
Pension Plan Max = some number dependent upon age
Sum of the Percent’s = 401k Percent + Roth Percent
401k Difference = 401k Percent / Sum of the Percent’s
Roth Difference = Roth Percent / Sum of the Percent’s
Thus
The 401k Yearly Max = Pension Plan Max times the 401k Difference
The Roth Yearly Max = Pension Plan Max times the Roth Difference
With the math behind us, we can then create a stored procedure to check everyone and email someone if the amounts are incorrect. We scheduled it to run every hour, because if nothing is wrong no one gets an email but if something is wrong Payroll needs to know before processing a payroll.
The following stored procedure gives the recipients of the email the formula in the message of the email and an attachment listing the employee id number along with what the amounts currently are and what they should be. Notice also that if for some reason they do not have an 401k yearly max or a Roth yearly max this procedure will also catch those errors and inform the email recipients.
Here is the stored procedure: (Please be careful with the wrapping of code, and the other changes the editor made to the code as I pasted it into this post. Additionally I cannot stress enough that you should test this fully in a test system before placing it in production. Also, you will need to make some changes for your system, such as whom to email.)
Create procedure as_PensionPlanMaxError as
set nocount on
declare @ToAddress as varchar(8000)
--A semicolon delimited list of who receives the email message.
set @ToAddress = 'someone@yourcompany.com; someoneelse@yourcompany.com'
--Changed per script
-- As I have lots of these I like to keep track of which script is generating which email
-- Note that the Script Name is simular too the name of the stored procedure
declare @ScriptName as varchar(100)
set @ScriptName = 'Pension Plan Max Error'
declare @MessageBody as varchar(8000)
--Body of email message.
--Message should be formatted as Script Name: message text.
set @MessageBody = @ScriptName + ': Employee has an error on their Max Year to Date amount on one of their Pension Plan DEDUCTION Codes.' + char(13) + char(13)
set @MessageBody = @MessageBody + 'Pension Plan Max = 17,500 or 23,000 for 50 and over (Let me know when this amount changes)' + char(13) + char(13)
set @MessageBody = @MessageBody + 'SumPercents = 401kPercent + RothPercent' + char(13) + char(13)
set @MessageBody = @MessageBody + '401kDelta = 401kPercent / SumPercents' + char(13) + char(13)
set @MessageBody = @MessageBody + 'RothDelta = RothPercent / SumPercents' + char(13) + char(13)
Set @MessageBody = @MessageBody + '401kYearMax = PensionMax * 401kDelta' + char(13) + char(13)
Set @MessageBody = @MessageBody + 'RothYearMax = PensionMax * RothDelta' + char(13) + char(13)
Set @MessageBody = @MessageBody + 'IF 401kYearMax is null or RothYearMax is null ' + CHAR(13)
set @MessageBody = @MessageBody + 'THEN YOU HAVE THE DEDUCTION PERCENTAGE IN THE DEDUCATION DOLLAR AMOUNT FIELD!' + CHAR(13)
set @MessageBody = @MessageBody + 'THIS MUST BE CORRECTED. ' + CHAR(13)
----Get a list of employees that have both a 401K and a Roth Deduction
select employid
into #E
from upr00500
where deducton in ('401K', 'ROTH')
and inactive = 0
group by Corp, employid
create table #Corrections ( employid varchar(10),
[401kPercent] numeric(5,2),
RothPercent numeric(5,2),
SumPercents numeric(5,2),
[401kDelta] numeric(5,2),
RothDelta numeric(5,2),
DOB datetime,
Age numeric(3,0),
PensionMax numeric(7,2),
[401kYearMax] numeric(7,2),
RothYearMax numeric(7,2))
---Insert employid into table
insert into #Corrections (employid) select * from #E
---Get the 401K deduction percent
update #Corrections set [401kPercent] = isnull(d.dednprct_1, 0.0)
from #Corrections
inner join upr00500 as d on #Corrections.employid = d.employid
where d.deducton = '401K' and d.inactive = 0
---Get the Roth deduction percent
update #Corrections set RothPercent = isnull(d.dednprct_1, 0.0)
from #Corrections
inner join upr00500 as d on #Corrections.employid = d.employid
where d.deducton = 'ROTH' and d.inactive = 0
---Get Rid of Nulls
update #Corrections set [401kPercent] = 0.0 where [401kPercent] is null
update #Corrections set RothPercent = 0.0 where RothPercent is null
---Add the two percents together
update #Corrections set SumPercents = isnull([401kPercent], 0.0) + isnull(RothPercent, 0.0)
---401kDelta = 401kPercent / SumPercents
update #Corrections set [401kDelta] = [401kPercent] / SumPercents where SumPercents <> 0
---RothDelta = RothPercent / SumPercents
update #Corrections set RothDelta = RothPercent / SumPercents where SumPercents <> 0
---Get the employee's date of birth
update #Corrections set dob = e.brthdate
from #Corrections
inner join upr00100 as e on #Corrections.employid = e.employid
---What age did the employee turn this year
update #Corrections set Age = datepart(year, getdate()) - datepart(year, dob)
---For employees less than 50 the PensionMax is 17500
update #Corrections set PensionMax = 17500.00 where age < 50.0
---For employees 50 and over the PensionMax is 23000.00
update #Corrections set PensionMax = 23000.00 where age >= 50.0
---401kYearMax = PensionMax * 401kDelta
update #Corrections set [401kYearMax] = PensionMax * [401kDelta]
---RothYearMax = PensionMax * RothDelta
update #Corrections set RothYearMax = PensionMax * RothDelta
select c.employid,
c.[401kPercent],
c.RothPercent,
c.Age,
c.PensionMax,
c.[401kYearMax],
c.RothYearMax,
convert(numeric(7,2), isnull(d401k.dedyrmax, 0.0)) as Actual401kMax,
convert(numeric(7,2), isnull(dRoth.dedyrmax, 0.0)) as ActualRothMax
into ##PPME
from #Corrections as c
left join upr00500 as d401k on c.employid = d401k.employid
and d401k.deducton = '401k'
and d401k.inactive = 0
left join upr00500 as dRoth on c.employid = dRoth.employid
and dRoth.deducton = 'Roth'
and dRoth.inactive = 0
where (isnull(c.[401kYearMax], 0.0) <> convert(numeric(7,2), isnull(d401k.dedyrmax, 0.0)))
or (isnull(c.RothYearMax, 0.0) <> convert(numeric(7,2), isnull(dRoth.dedyrmax, 0.0)))
declare @MessageSubject as varchar(250)
--Subject line of email message. Always set to 'Great Plains Alert'.
--As I also have alerts like this for multiple other systems
--I like to know which system has the problem
Set @MessageSubject = 'Great Plains Alert: ' + @ScriptName
if (select count(*) from ##PPME) > 0
--If anything to report then mail the results back to the user
begin
exec msdb..sp_send_dbmail @recipients = @ToAddress,
@blind_copy_recipients = 'myself@mycompany.com',
@body = @MessageBody,
@subject = @MessageSubject,
@query = 'SELECT * from ##PPME order by employid',
@query_result_width = 500,
@attach_query_result_as_file = 1
end
drop table ##PPME
go
grant exec on as_PensionPlanMaxError to public
go
Hope this helps, and let me know how it goes,
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (