Payroll report by EEOC category

A GP payroll client requested a report showing payroll and benefits by EEOC category.  This report needed to break down the EEOC amounts by full time or part time.  Here are the components I used to assemble this report.


First the EEOC category is stored in the Position Setup table with the Position Code (also called Job Title).  The position master file with this data is the UPR40301 table.  The EEOClass_I column in that table contains a code that correlates to the EEOC category.


While the Position Setup window has the EEOC codes hard coded for the field that is defined, there is a place in GP where the descriptive value can be looked up.  Thanks to this blog I was able to find this data in the SY10997 table in the Dynamics database.  The data must be restricted by columns FuncName = 'HR_FUNC_EEOClass' and Language_ID = 0.  The index value is in FuncEnum and the descriptive value is in SQL_Msg.


Now the position code is recorded with the payroll data so I could cross-reference the payroll history from UPR30300 to the Position Setup table then to the SY10997.  And lastly I needed to grab the Full-/Part-Time status from the Employee Master UPR00100 table in column EmploymentType.


There are a few tricks here that you need to be cognizant of.  First if an employee was in different positions that crossed different EEOC categories during the year they would show up on this report in each of the different categories. 


Also the employment status from the master is only as of right now so when an employee changes from full- to part-time then his wages for that report would also change regardless of what he was at the time payroll was run.  This is unavoidable without a customization that records the employee status with the pay run.