Energy Program Payroll Report Process

This process became necessary after a review of the Energy Program’s time submissions resulted in concerns regarding salaried employees not earning all of their salaries due to how their efforts were recorded. The agreed upon solution was that CAHNRS Personnel would review the Energy Program’s payroll submissions to ensure employees are paid the correct amount.

Energy Program

1.       Energy Program employees on exception pay and timeslip employees record hours via Timekeeper. Their payroll is similar to “billable hours” which means AP employees record hours the same as employees receiving positive pay.

2.       Supervisors review employees’ submitted timesheets in Timekeeper.

3.       Adria Banks submits LWOP leave reports to Becky Priebe.

4.       Administrative support staff use a combination of manual and electronic upload steps to input the collected payroll information into DEPPS.

5. Administrative support staff save a text file as EnergyPayrollHours onto the ftp site (server).

6.       Someone from the Energy Program staff emails Becky, Caitlin and Jill to notify them when Energy Program Payroll hours have been input into DEPPS and the text file has been made available on the FTP server for the current pay period.

Jill Wilson (Data Architect)

1. This process runs via a SQL Server Agent job named, EnergyPayrollHours. This SQL Agent relies upon two things in order to execute. Another SQL Agent named, MasterScheduleController, runs each morning to verify whether today is the day that this EnergyPayrollHours agent needs to execute. The MasterScheduleController executes a stored procedure named, spMasterSQLAgentScheduleController, which looks at the table tblPayrollSchedule. This stored procedure checks to see If today's date matches the date in the field named, PayrollSched_VerifyDateTime if it does then the schedule for the SQL Agent EnergyPayrollHours is changed from "Disabled" to "Enabled".

Note: The tblPayrollSchedule is manually updated with the dates published by Central Payroll made available in PDF format at this web address: Central Payroll Schedules.       

2. The SQL agent will execute on each Payroll Scheduled Verify Date (each pay period). The SQL Agent executes an SSIS package.

3.       This SSIS package executes the following stored procedure within the CAHNRS_BusSys db: spEnergyPayrollHoursBuilder

3.       This stored procedure builds the tblEnergyPayrollHours for the verification report which is displayed on the web page. (This table is built by joining the data whs and timekeeper data tables together, then saving the results into tblEnergyPayrollHours_Snapshot.)

4.       The procedure then flags records with an exception flg to identify records for those whose current appointment hours possible do not match the TimeKeeper Hours Charged. It excludes people who are not on positive pay.

5. Email generated by SQL server agent to Becky and Caitlin.

Bill Bonner (Web Application Developer)

1.       Bill has programmed the CAHNRS Business System to display the end resulting data table on the web page.

Caitlin Ruhs (Manager, Pay and Position)

1.       Receive LWOP leave reports from Energy, send to HRS and Payroll.

2.       Review CBS web page and search for instances where the exception is “true”.

3.       Make sure leave reports have been submitted for exceptions.

Energy Program

1.       Energy Program reviews PEARS to resolve payroll submission errors.