Undoing a bank reconciliation after it’s already been posted for Dynamics GP

blog pictureWhen I was a freshman in a U.S. college I hung out with a group of friends from my little hometown in Canada. We were appalled by the lack of knowledge of our American roommates (and most of our fellow student body) about our beloved homeland. On October 13th that year we decided to celebrate Canadian Halloween. We dressed up in costumes and went around trick or treating (mostly around girls housing complexes.)

There is no special Canadian Halloween (unlike Canadian Thanksgiving, Independence Day, Remembrance day, Family Day etc.) but my recollection is that most of the people we trick or treated to were quite open to our Canadian culture when we announced it was Canadian Halloween. If they didn’t have candy we said we’d take canned soup or any type of food. I don’t think we had to go shopping that week and we met a ton of girls. Although I’m sure most of the girls we met decided that they would never date a Canadian after meeting our motley crew.

What does this have to do with opening back up a bank reconciliation after it has already been posted? I’m going to discuss a method below that some of our consultants use to open back up a bank reconciliation. You can believe me without question or you can test this out first on a test company.

******************************

SQL queries:

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. 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’

**********************************

Although I’ve told anyone that asks for the past several years that you can’t unpost a bank reconciliation that’s not entirely true. The above method works great. It just takes a little SQL knowledge and you can be re-reconciling in no time.

But don’t just take my word for it. I once swindled a whole apartment complex out of all their precious chocolates and candy.

Happy Canadian Halloween.