Recently, I ran into an unusual problem where a client entered a manual check to adjust an employee’s pay record, but they used the same check number as the original paycheck they were adjusting. As a rule of thumb, duplicate check numbers are a no-no in generally accepted accounting practice. But in Dynamics GP you can set up the checkbook to allow duplicate check numbers if you desire.
- Note: Allowing duplicate check numbers is not a best accounting practice. Every check, including manual checks, should have a unique check number.
Later, the client decided that they needed to void the original check. After voiding the original check they realized that they needed to void the manual check too. However, when they tried, they received a message informing them that “There are no checks in this range to void…” But we know that the manual check still exists and needs to be voided. We know that because we can see it on the Payroll check inquiry window.
Figure 1 shows an example of this issue. In this scenario the original check (audit trail Code UPRCC00000088) was voided (audit trail code UPRVC00000004) and the manual check (audit trail code UPRMS00000004) is not voided but will not show up in the Void Payroll Checks window.
While the solution to this problem is relatively simple, it requires SQL update scripting to change the check number for the target transaction in three SQL tables if the paycheck was a paper check transaction, or 4 SQL tables if the check was a direct deposit transaction.
- Note: Running update statements in SQL can cause unexpected results and damage your Dynamics GP data. Be sure you consult a qualified SQL Database professional or contact your Microsoft partner for assistance.
- Note: Always create a working backup of your company and system databases before running any UPDATE, DELETE, or INSERT statements against your data.
Figure 2 shows the scripts to change the check number for the manual check from 11677 to MC1167:
Because this was not a direct deposit transaction the three tables that need to be updated are the Payroll Check History table, Payroll Transaction History table, and the CM Transaction table (Checkbook Transactions). If the check had been a direct deposit check then you would need to update the Direct Deposit Transaction History Detail table (DD30101) too.
After the scripts in Figure 2 were executed the manual check, now numbered MC0011677, is available to void in the Void Payables Check Window – Figure 3:
Remember, always use a unique check number for every check transaction. You can set the checkbook to not allow duplicate check numbers in the Checkbook maintenance window.
At RSM, it’s all about our clients. Our strong, client-centric approach differentiates us from the rest. To be considered your advisor of choice, we strive to understand you, your business, and your aspirations. By sharing the ideas and insight of our most senior professionals, we bring our local and global knowledge and resources to your environment so you feel empowered to move forward with confidence. This is the power of being understood.® This is RSM.
Contact our experts at RSM with your questions or concerns or call us at 1-855-437-7202