Tracking Payroll Master Record Changes
Many of our Dynamics GP clients ask if there's a way to track changes to employee master records. The reasons for tracking changes are varied; having an audit trail to find out who's updating records and when can be very useful for preventing fraud.
You may be familiar with the human resource functionality in Dynamics GP that tracks changes to pay rates, employee status and a few other pieces of information. This functionality is helpful, and the data is easily accessed from the Inquiry and Card menus.
There are other options for tracking changes when this option doesn’t suffice. We’ll describe three of these options. You’ll notice there is a free utility from Microsoft that offers functionality you might be able to immediately put to use.
You may be aware of the Activity Tracking option in Dynamics GP. This utility provides the ability to track who's making changes and when. However, it does not tell you what was changed, and it does not track information across all modules. For example, it does not track information related to direct deposit tables.
Here's an example of the information the system does track for a change to the employee master table.
In the example above, user ‘sa’ made a change to the record for employee 00100. However, there's nothing to show you what was changed.
The Audit Trail module is a great, optional module licensed as part of the Risk Management Suite. It enables you to turn on auditing for any table in the system. Due to its price, your company may not have chosen the module when Dynamics GP was licensed unless there was a specific need for it.
In the example below, the employee’s job title was changed to TEC. The system recorded an update change in the audit trails database, which is separate from the company database. The system also tracks the date, time and user who made the change.
In addition to changes made through Dynamics GP, the tool also will track changes made behind the scenes, so if a user who has access to SQL Server Management Studio makes a change to a record, an audit trail record will be created. The system also tracks additions and deletions.
Detail Payroll Activity Tracking Tool
The last option is one you may not be aware exists. It's called the Detail Payroll Activity Tracking Tool, and it tracks changes to the following tables:
UPR00300—Payroll Tax Information Master
UPR00900—Payroll Employee Summary
UPR00400—Pay Code Master
UPR00501—Based on Pay Codes (deductions)
UPR00601—Based on Pay Codes (benefits)
UPR00700—Employee State Taxes
UPR00800—Employee Local Taxes
BE010130—Benefits and Deductions
DD00100—Direct Deposit Setup
DD00200—Direct Deposit Account Setup
As you make changes to employee data, either in Dynamics GP or through a tool like SQL Server Management Studio, a record will be created in a custom table created when running a SQL script. The table, called TrackPayrollChanges, is in the company database.
The last record in the above image was edited using SQL Server Management Studio, which you can tell by the USERID column. Notice the value is BKD\callen; BKD is the domain, and callen is the user—in this case, me.
As you can see, your company has multiple options when it comes to tracking changes to employee data. If you have any questions about any of them, please contact our Microsoft Dynamics GP support center or post a message for this article.