Dynamics GP – Undo a Bank Account Reconciliation

Posted on: October 2, 2017 | By: Jim Bertler | Microsoft Dynamics GP

Authored by: Brent Stabiner

An issue that has appeared lately for some companies is how to redo a bank reconciliation. For companies that are reconciling bank accounts daily or every other day, there will be times where the incorrect balance is keyed, adjustments are entered incorrectly, etc. Thus, the reconciliation must be deleted and the last reconciled balance and date need to be updated. This article will demonstrate how to undo the reconciliation using SQL Management Studio.

  1. Determine the Recon# of the reconciliation to be un-done (replace XXXXX with the appropriate Checkbook ID):

select * from CM20500 where CHEKBKID = ‘XXXXX’

order by RECONUM

 

2.  Change the transactions in that recon to not reconciled (replace the ##.##### with the exact RECONUM determined in #1 above):

  • update CM20200 set Recond = 0 where RECONUM = ‘##.#####’
  • update CM20200 set ClrdAmt = 0 where RECONUM = ‘##.#####’
  • update CM20200 set clearedate = 0000-00-00 where RECONUM = ‘##.#####’
  • update CM20200 set RECONUM = 0 where RECONUM = ‘##.#####’

 3. Remove the Recon from history (replace the ##.##### with the exact RECONUM determined in #1 above):

delete CM20500 where RECONUM = ‘##.#####’

4.  Update the Checkbook Master with the revised Last Recon Date and Last Recon Amount (Replace MM with the desired Month and DD with desired day. Replace #######.## with the appropriate amount – no commas. Replace XXXXX with the appropriate Checkbook ID):

  •  update CM00100 set Last_Reconciled_Date = ‘2017-MM-DD 00:00:00.000′ where CHEKBKID = ‘XXXXX’
  • update CM00100 set Last_Reconciled_BALANCE = ‘#######.##’ where CHEKBKID = ‘XXXXX’

For further insight on Microsoft Dynamics GP installation and implementation, contact Logan Consulting, your Microsoft Dynamics GP Partner of Chicago.