I ran across an interesting issue I had not seen before when working with a customer’s Sage 300 ERP payroll last week. We found one pay period where employee “Bob” had a check amount of $1,500 that did not match their payroll register details. Bob’s payroll details usually included five earning details, one for each day of the week, as well as deductions and tax details. In this case, the report was only showing one earning detail for $400 and no deductions or taxes. It was clear that details were missing from this report.
The first thing I tried was running a data integrity check with the fix minor errors box checked. When that finished running, I checked the check amounts and payroll register details again, and the numbers now matched, but it actually changed the historical check amount to $400 to match the inaccurate payroll register report.
Next, I decided to dive into the database. Running a few SQL queries revealed something interesting. When my query filtered the results on the period end date (PEREND = 20140901), the result was one detail line for $400, matching what showed up in the payroll register report. However, when I changed my filters to include a range of dates instead, my query returned the full list of transaction details that totaled the $1,500 original check amount.
I tried updating the data in the PEREND column, but got an error message…
This led me to believe there to be an issue with the indexing in this table. I then ran this query to reindex the table…
After that, I ran a query using the period end date equals filter again and all the details matching the $1,500 total are returned. I ran the data integrity check with fix minor errors again, and now both the transaction history report and the check amount have a total of $1,500.
I thought this would be worthwhile to share because I had not seen this issue before. I hope this will help if you run into a similar issue.