Year End is a busy time, especially for those using Dynamics GP Payroll! Below is a SQL Script that can pull the year end wage file data into a smartlist! COOL!!
It is built to pull in records for federal, state, local, special box and the other box. Because of this, employees may have multiple rows if the employee has multiple states, special boxes, or other box data. Using a pivot table cleans this right up! Check it out!
CREATE VIEW [dbo].[Year_End_Wage_Data_Payroll]
AS
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Year End Wage Data
-- Run Against Company Database
-- Only provides data populated by the Year End Closing Routine
-- Visit http://lifehacks365.com for more cool GP Stuff!
-- Updated by: Shawn Dorward - As of 01/14/2019
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SELECT
A.RPTNGYR Year1,
A.LASTNAME Last_Name,
A.FRSTNAME First_Name,
A.EMPLOYID Employee_ID,
A.ADDRESS1 Address_1,
A.ADDRESS2 Address_2,
A.Address3 Address_3,
A.CITY City,
A.STATE State,
A.ZIPCODE Zip_Code,
A.WGTPCOMP Gross_Wages,
A.FEDITXWH Federal_Tax_Withheld,
A.SSECWAGS SS_Wages,
A.SSTXWHLD SS_Tax_Withheld,
A.MCRWGTPS Medicare_Wages,
A.MDCRTXWH Medicare_Tax_Withheld,
B.STATECD State_Tax_Code,
B.STATEWGS State_Wages,
B.STATINTX State_Tax_Withheld,
C.LOCLCODE Local_Tax_Code,
C.LOCLWGES Local_Wages,
C.LCLINTAX Local_Tax_Withheld,
D.SPCLCODE Special_Code,
D.SPCLBNUM Special_Box_Number,
D.SPCLAMNT Special_Amount,
E.OTHRCODE Other_Code,
E.OTHBXNUM Other_Box_Number,
E.OTHRAMNT Other_Amount
--Payroll Year End Table
FROM UPR10101 A
-- Adding Payroll Year End State Table
left outer join
UPR10105 B on A.RPTNGYR = B.RPTNGYR and A.EMPLOYID = B.EMPLOYID
-- Adding Payroll Year End Local Table
left outer join
UPR10106 C on A.RPTNGYR = C.RPTNGYR and A.EMPLOYID = C.EMPLOYID
-- Adding Payroll Year End Special Table
left outer join
UPR10104 D on A.RPTNGYR = D.RPTNGYR and A.EMPLOYID = D.EMPLOYID
-- Adding Payroll Year End Other Table
left outer join
UPR10107 E on A.RPTNGYR = E.RPTNGYR and A.EMPLOYID = E.EMPLOYID
go
grant select on [Year_End_Wage_Data_Payroll] TO DYNGRP
