Payroll 401k Submission

Okay, we have been a little radio-silent over here at Dynamics GP Land.  Blame the holidays.  Blame the year end crush in our line of work.  Blame sick kids and sick spouses.  Know that we have thought of posting often, and just didn't quite make it :)   But here we are, back in the saddle.  And I have guilted Steve as well, so I am sure he will be coming along shortly with some mind-blowingly complicated and obscure technical problem. 

But for now, I will embrace the topic of the moment- Payroll.  January is always this way, right?  Just plain crazy with payroll year end and issues and payroll year end and issues.  So I thought I would share a few things I have developed over the years to help with payroll implementations in general.

Item number one is a common request, which is a file for 401k submission that can be sent to the 401k provider after each payroll.  Here is a view I have created that will summarize the payroll deduction, benefit (match), and catch-up contribution for each check date.  You can then use this as a basis for either a SQL Report or a SmartList Builder depending on what you already own or have in place.

The view is set up to allow for scenarios where you might have multiple deduction and/or benefit codes for each item (deduction, match, and catch-up).

CREATE VIEW [dbo].[CSVW_401K]


AS

With Benefit_CTE (AuditTrail, CheckNumber, CheckDate, EmployeeID, BenCode, BenAmount) as (Select B.AUCTRLCD AuditTrail,B.CHEKNMBR CheckNumber, B.CHEKDATE CheckDate,B.EMPLOYID EmployeeID, B.PAYROLCD BenCode, B.UPRTRXAM BenAmount from UPR30300 B where b.PAYROLCD in ('Insert401KMatchBenefit1', 'Insert401KMatchBenefit2') and B.pyrlrtyp=3),

Deduction_CTE (AuditTrail, CheckNumber, CheckDate, EmployeeID, DedCode, DedAmount) as (Select A.AUCTRLCD AuditTrail,A.CHEKNMBR CheckNumber, A.CHEKDATE CheckDate,A.EMPLOYID EmployeeID, A.PAYROLCD DedCode, A.UPRTRXAM DedAmount from UPR30300 A where A.PYRLRTYP=2 and A.PAYROLCD in ('Insert401KDeduction1','Insert401KDeduction2')),

Extra_CTE (AuditTrail, CheckNumber, CheckDate, EmployeeID, ExCode, ExAmount)as (Select c.AUCTRLCD AuditTrail,c.CHEKNMBR CheckNumber, c.CHEKDATE CheckDate,c.EMPLOYID EmployeeID, c.PAYROLCD ExCode, c.UPRTRXAM ExAmount from UPR30300 c where c.PYRLRTYP=2 and c.PAYROLCD in ('Insert401KCatchUpDed1','Insert401KCatchUpDed2'))

Select Deduction_CTE.EmployeeID, Deduction_CTE.CheckNumber, Deduction_CTE.CheckDate, Deduction_CTE.DedCode, Deduction_CTE.DedAmount, Benefit_CTE.BenCode, Benefit_CTE.BenAmount, Extra_CTE.ExCode, Extra_CTE.ExAmount from Deduction_CTE left outer join Benefit_CTE

On Deduction_CTE.AuditTrail=Benefit_CTE.AuditTrail and Deduction_CTE.CheckNumber=Benefit_CTE.CheckNumber and Deduction_CTE.CheckDate=Benefit_CTE.CheckDate and Deduction_CTE.EmployeeID=Benefit_CTE.EmployeeID left outer join Extra_CTE on Deduction_CTE.AuditTrail=Extra_CTE.AuditTrail and Deduction_CTE.CheckNumber=Extra_CTE.CheckNumber and Deduction_CTE.CheckDate=Extra_CTE.CheckDate and Deduction_CTE.EmployeeID=Extra_CTE.EmployeeID
GO  

Hope this helps those of you in need of 401k submission information :)

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.