Using the Issue Detective Effectively
Over the years working with the Vista system, we found that we were often running queries on the database each pay period to check for scenarios that would regularly cause us problems. The collection of these queries continued to grow as we addressed and then proactively looked out for new issues during subsequent payroll processing periods. Over time, we created two processes (the Payroll Consistency Check and the Data Integrity Check) that helped us identify and correct issues.
The Payroll Consistency Check was based solely on queries and email commands in a large ES process. This would run checks and notify appropriate recipients if the scenarios returned results. It quickly became unwieldy and difficult to maintain using the ES command dialog.
The later Data Integrity Check was more sophisticated in that it used a custom table to define the scenario queries and intended recipients so that adding and updating checks was simpler. A stored procedure executed each of the queries and notified the defined recipients if issues were identified.
Here are some examples of what we would look for in the Payroll Consistency Checks:
- Employees approaching one-year anniversary – To ensure employees were enrolled in the matching 401k after the one-year waiting period had passed.
- Mismatches between earning, deduction, tax, fringe, etc. YTD values – We compared the employee tables to the pay history tables to ensure that our YTD records stored in both locations matched appropriately. Due to manual corrections, these would sometimes get out of sync.
- Mismatched locations and organizations – Often, we would find that the employee’s position and their home GL number (distribution code) would have non-matching location and organization codes.
- Employees missing direct deposit setup information – All AECI employees are on direct deposit. We need to ensure that there is a direct deposit record for each person prior to paying them. Also, we needed to ensure that HSA deposit accounts were set up for those active in the HSA plan.
- 401k deductions and YTD amounts – Similar to a previous check, these values have to match between EMPDED, PHDED, PHBASIC, and BENFUNDA. Sometimes these got out of sync due to our custom matching process or manual corrections.
- And many more, about 20 more of them…
Most of the Data Integrity Check queries were related to our process of cleaning up employee and history data to assist with our reporting needs. Examples include:
- Invalid dates on union records – start date <> effective date, etc.
- Mismatched effective dates between EMPCOMP, POSREC – employment changes should correspond directly with a position change, etc.
- Employees missing a union record
- Employees with multiple consecutive employment history records with same status, reason and effective date
- Employees with more than one employment record marked as New Hire
- Spouse not primary beneficiary on 401k/pension plan – requires a document confirming this
- Trust as primary beneficiary – requires a document confirming this
- And many more, to the tune of around 40!
These two processes ran in parallel for several years and worked very well keeping us aware of hidden issues so we could try to stay ahead of the game.
Then, along came PTF 93 in early fall of 2014.
As I read through the enhancement documentation and stumbled across the Issue Detective on page 67, things started feeling eerily familiar. I started to wonder if maybe I had shared our Payroll Consistency Check and Data Integrity Check processes with PDS or if they had just improved their customer mind-reading capabilities. The Issue Detective was basically what we had created twice before but was an evolutionary step beyond what we had done.
We delved into the database tables and the ES process to understand exactly how it worked. We were quickly able to transfer all of the Data Integrity Checks into the new Notifications area under System Administration with minimal changes because the process was very similar to the one we had in place already. This meant we were able to retire and remove the custom table and stored procedure we had created that drove our custom process.
As some of you may know, we LOVE our customizations at AECI, but I am always willing to retire one if we can.
This just left our Payroll Consistency Checks. We have not been able to retire those just yet, but I have been in discussions with Marco Padovani to implement some changes to allow each of the Notification items to be sent to an appropriate list of recipients. Some Notifications are only intended for Payroll while others only affect Benefits. Currently, the requester of the Issue Detective process gets the resulting email by default. The structure appears to be in place to allow this functionality in a future release.
Additionally, the Notifications won’t necessarily have to be delivered via email. PDS is also working on providing a dashboard of your Notifications so that each time you log in to Vista, you are able to identify where problem areas may exist at a glance.
If you haven’t started investigating how you might leverage the Issue Detective, start now! Bust out your magnifying glass, your deerstalker hat, and your calabash pipe and go full Sherlock Holmes with the Issue Detective. There is great potential to automate the processes that you are already doing to ensure that your data and your system are configured correctly. If you haven’t been doing these types of checks, take a look at the Notifications that have been delivered in the last couple of PTFs and see if any of them identify issues that you weren’t even aware you had. Most of the Notifications that have been delivered with base system are related to Benefits setup and are very helpful. As you can see by the examples I provided earlier, many of our Notifications are very specific to our processes and practices. That is the beauty of the structure being provided; you can create any custom notification you choose to identify your particular areas of concern.
Stay tuned as the Notifications and Issue Detective continue to evolve and provide even more powerful tools in Vista to ensure that we are getting the most value out of our Vista implementations.
Derek Hime
Development Team Supervisor, Human Resources Services Team
Associated Electric Cooperative, Inc.
dhime@AECI.org