New York - New Jersey Regional Chapter

Expand all | Collapse all

Undo bank Reconciliations

  • 1.  Undo bank Reconciliations

    Posted Jan 05, 2018 04:24 PM
    hi,

    does anyone know if there is a way to undo a bank reconciliation after it's done?

    ------------------------------
    Yanna Zhang
    Stellar Services
    ------------------------------


  • 2.  RE: Undo bank Reconciliations

    Posted Feb 15, 2018 09:55 AM

    You can rollback a posted Bank Reconciliation with the use of a series of SQL queries, if you are comfortable with using SQL Queries:

    If you have copied your production company that includes the Bank Reconciliation you want to roll back to a Test Company, backup the Test Company. With a backup, you can go through the steps a number of times to make sure you are satisfied with the results.

    When you are comfortable that the process will provide the results you want, backup your production company and perform the steps there. (Hopefully, only once)!

    Query 1) Find the RECONUM of the reconciliation to be rolled back with the first query:

              select * from CM20500

              where CHEKBKID = 'ID of your Checkbook'

              order by RECONUM

    make a note of the LAST_RECONCILED__DATE  & the LAST_RECONCILED_BALANCE.

    These will be needed in the last steps.

    2) Reset the RECOND flag, the Cleared Amount and the Cleared Date, and the RECONUM on the transactions included in that reconciliation:

              update CM20200

              set RECOND = 0

              where RECONUM = 'The RECONUM identified by Query 1'

     

              update CM20200

              set CLRDAMT = 0

              where RECONUM = 'The RECONUM identified by Query 1'

     

              update CM20200

              set CLEAREDATE = 0000-00-00

              where RECONUM = 'The RECONUM identified by Query 1'

     

              update CM20200

              set RECONUM = 0

              where RECONUM = 'The RECONUM identified by Query 1'

    3) Remove the Reconciliation from history:

              delete CM20500

              where RECONUM = 'The RECONUM identified by Query 1'

    4) Update the Checkbook Master with the revised Last Recon Date and Last Recon Amount:

              update CM00100

              set LAST_RECONCILED _DATE = '2018-MM-DD 00:00:00.000′ (with whatever date is appropriate)

              where CHEKBKID = 'ID of your Checkbook'

     

              update CM00100

              set LAST_RECONCILED_BALANCE = 'Beginning amount of the new Reconciliation'

              where CHEKBKID = 'ID of your Checkbook'

     



    ------------------------------
    Michael Peck
    Microsoft Dynamics GP Consultant
    NTT Data
    Croydon PA
    ------------------------------



  • 3.  RE: Undo bank Reconciliations

    TOP CONTRIBUTOR
    Posted Feb 15, 2018 10:28 AM
    @Michael Peck thank you so much for the script I have had customers ask for this, but never thought it was possible.​  I am going  to add this to my Nightmare course.  Thanks so much for the information.
    Tanya


    ------------------------------
    Tanya Henderson
    Senior Consultant
    S2 Technology
    Park City UT
    ------------------------------



  • 4.  RE: Undo bank Reconciliations

    Posted Jan 21, 2019 09:23 AM
    I'm glad it helped.

    ------------------------------
    Michael Peck
    Microsoft Dynamics GP Consultant
    NTT Data
    Croydon PA
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role