Another One To Share: Reconciling Payroll Summary

A quick share to end the day, if you can successfully reconcile your 941 manually by taking gross wages and subtracting tax sheltered deductions and adding taxable benefits, but your payroll summary does not match...this script is for you!


This will display the wages as calculated for the payroll summary, and then also display (based on the transaction history) the tax sheltered deductions (divided in to two categories, sheltered from all taxes or just fed and state) and taxable benefits (also divided in to two categories, taxable by all taxes or just fed and state).  This will allow you to manually calculate (by employee) what you would expect the federal and fica wages to be.  If the manual calculation differs from the amounts display for the payroll summary, then you know you need to make an adjustment to correct.  Most often these discrepancies can be traced to tax flags being changed after transactions were processed.


Happy hunting!


--Summarizes deductions by employee for a period of time that are sheltered from all taxes
With CTE_DedTax as (Select employid, SUM(UPRTRXAM) as TotalDedTaxSheltered from UPR30300 where CHEKDate<='6/30/2016' and chekdate>='4/1/2016'
and payrolcd in ('POPULATE WITH LIST OF DEDUCTIONS SHELTERED FROM ALL TAXES')
and PYRLRTYP=2 group by EMPLOYID),


--Summarizes deductions by employee for a period of time that are sheltered just from federal and state
CTE_DedFS as (Select employid, SUM(UPRTRXAM) as TotalDedFedStateSheltered from UPR30300 where CHEKDate<='6/30/2016' and chekdate>='4/1/2016'
and payrolcd in ('POPULATE WITH LIST OF DEDUCTIONS SHELTERED FROM JUST FEDERAL AND STATE')
and PYRLRTYP=2 group by EMPLOYID),


--Summarizes benefits by employee for a period of time that are taxable by all taxes
CTE_BenTax as (Select employid, SUM(UPRTRXAM) as TotalBenTaxSheltered from UPR30300 where CHEKDate<='6/30/2016' and chekdate>='4/1/2016'
and payrolcd in ('POPULATE WITH LIST OF BENEFITS TAXABLE BY ALL TAXES')
and PYRLRTYP=3 group by EMPLOYID),


--Summarizes benefits by employee for a period of time that are taxable by just federal and state
CTE_BenFS as (Select employid, SUM(UPRTRXAM) as TotalBenFedStateSheltered from UPR30300 where CHEKDate<='6/30/2016' and chekdate>='4/1/2016'
and payrolcd in ('POPULATE WITH LIST OF BENEFITS TAXABLE BY JUST FEDERAL AND STATE')
and PYRLRTYP=3 group by EMPLOYID),


--Summarizes wage information by employee as reported on the payroll summary report
CTE_PRSummary as (Select sum(fdwgpyrn) as FedWages,
sum(ficasswp) as FICASSWages, sum(ficamwgp) as FICAMWages, sum(GRWGPRN) as GrossWages, Employid as EmployID
from UPR30100 where year1='2016' and CHEKDate<='6/30/2016' and chekdate>='4/1/2016' group by EMPLOYID)


--Pulls all information together for comparison
select CTE_PRSummary.Employid, CTE_PRSummary.FedWages, CTE_PRSummary.FICASSWages, CTE_PRSummary.FICAMWages,
CTE_PRSummary.GrossWages, isnull(CTE_DedTax.TotalDedTaxSheltered,0) as TotalDedTax, isnull(CTE_DedFS.TotalDedFedStateSheltered,0) as TotalDedFS,
isnull(CTE_BenTax.TotalBenTaxSheltered,0) as TotalBenTax, isnull(CTE_BenFS.TotalBenFedStateSheltered,0) as TotalBenFS from CTE_PRSummary
left outer join CTE_DedTax
on CTE_PRSummary.EmployID=CTE_DedTax.EmployID
left outer join CTE_DedFS on CTE_PRSummary.EmployID=CTE_DedFs.EmployID left outer join
CTE_BenTax on CTE_PRSummary.EmployID=CTE_BenTAX.EmployID left outer join CTE_BenFS on CTE_PRSummary.EmployID=CTE_Benfs.EMPLOYID


Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a director 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.