- Determine the Recon# of the reconciliation to be un-done (replace XXXXX with the appropriate Checkbook ID):
- Change the transactions in that recon to not reconciled (replace the ##.##### with the exact RECONUM determined in #1 above):
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):
- 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):
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 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.
