Undo Bank Reconciliation in Microsoft Dynamics GP

Here are the scripts for undo Bank Reconciliation in Microsoft Dynamics GP.

• 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

• 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 = ‘##.#####’

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

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

• 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. Repace #######.## with the appropriate amount – no commas. Replace XXXXX with the appropriate Checkbook ID):

          update CM00100 set Last_Reconciled_Date = ‘2010-MM-DD 00:00:00.000′ where
          CHEKBKID = ‘XXXXX’
          update CM00100 set Last_Reconciled_BALANCE = ‘#######.##’ where CHEKBKID =
          ‘XXXXX’
       
           In my case the customer wanted all bank reconciliations deleted and they only had one
           checkbook, so I didn’t have to worry about the “where” sections of the scripts in steps 2
           and 3.  I also then ran step 4 but when I went to look at the checkbook in GP the Last
           Reconciled Date and Amount fields weren’t available to edit like they normally are when
           you first setup a checkbook.

• After taking a look at the checkbook in the CM00100 I noticed one additional column that needed to get updated in my case.  Running the following script allowed me to have access to those two fields on the checkbook maintenance window:

          Update the Reconcilded column to a 0 to enter a Last Reconciled and Last Reconciled Amount
          Update CM00100 set Recond=0 where CHECKBKID = ‘XXXXX’


IMPORTANT: As always make sure you have good backups before performing any data manipulation in SQL tables and if at all possible first do it in a test company, so that you can validate that you get the results that you want.