GL and Cash Balance are incorrect from incorrect date entered

GL and Cash Balance are incorrect from incorrect date entered

This question is answered

We input a payment application in December but the date that was used for that payment application was 2005 instead of 2013.  This has thrown our cash balance and GL balance off when we are trying to reconcile the bank account.

We attempted to void the selected payment application but it changed the date to today’s date so it is not offsetting it.

Is there a way to go in to the table and change the 12/27/2005 date to 2013?

AR PA 002399 12-13 8982 12/17/2005 project name 14,545.00 0.00

AR NS 002480 01-14 8982 1/13/2014 project name 0.00 14,545.00

Verified Answer
  • The only way to update something that is completed is by doing so in Microsoft SQL Server>Management Studio.   This should be available on the File server.

    You would make the update against your application database. You always want to have a current backup when you do updates directly to the tables in Management Studio.   It would look something like this:

    begin tran

    update batch set dateent = '12/17/2013' where module = 'ar' and batnbr = 'xxxxxx'

    If one row is updated, enter and run:

    COMMIT

    otherwise,

    ROLLBACK

  • You would need to run Ca Integrity for both the old date and the new date.

    You must run the CA Integrity for one year at a time.  If the old date and new date were in different years, then you would need to make the year match the transaction date.

    You should not run it from 2005 thru 2013.  mm/dd/2005 - mm/dd/2005  for year 2005.  Then  run again from 12/1/2013 thru 12/31/2013 for year 2013.

All Replies
  • Hi Casey,

    Cash manager uses the batch.dateent from the AR payment application screen.  

    Update that and then run the CA integrity for the original date with a matching year of 2005.  

    Run the CA Integrity for the new date and matching year.

    If you want to do an NS in application reversal,  the system uses the current AR period as the period to post.  However, it uses the business date for the transaction date.  So any time you want to NS a payment that hits Cash Manager, change the Business date before doing so.

  • Elaine,

    We are not sure how to update the batch.dateent in the AR Payment Application screen.

    We did find the CA Integrity screen but have not yet run it and will have to schedule a maintenance window to stop users of SL and Business Portal from using those products.

    I do however want to give some more historical information to this issue.

    The incorrect date was entered in: Accounts Receivable > Payment Application > Document > Date - entered as 12/17/2005.

    The record was posted to 12-2013.

    Our cash balance for that account is now out of balance.

    Our accounting manager tried to use a Journal Update to change the information, but it had not effect and was later realized that it would not have an effect on the cash balance for that account.

    The accounting manager then tried an Application Inquiry/Reversal (screen 08.240.00) and used Void Selected Application (NSF Check).

  • The only way to update something that is completed is by doing so in Microsoft SQL Server>Management Studio.   This should be available on the File server.

    You would make the update against your application database. You always want to have a current backup when you do updates directly to the tables in Management Studio.   It would look something like this:

    begin tran

    update batch set dateent = '12/17/2013' where module = 'ar' and batnbr = 'xxxxxx'

    If one row is updated, enter and run:

    COMMIT

    otherwise,

    ROLLBACK

  • We did something similar.  However the AR payment was posted in Payment Application (08.030.00) screen to a period of 2015 instead of 2013.  When we go to make a correction via the Application Inquiry/Reversal (08.240.00) it does not ask for a period to post and uses the current date.  Is there an easier to way to fix this?

  • I'm going to try the SQL and the CA Integrity check this weekend as I have other maintenance planned. (Migrating Dynamics CRM from 4.0 to 2011 to 2013)

    I'll perform the backups to the Application database, try the SQL command first. Then I'll try the CA Integrity Check after backing up the DB again.

    I will post my results next week.

  • I tried the following:

    In SQL Manager: Right-click Application database > New Query > pasted the following in the pane and checked the command: begin tran

    update batch set dateent = '12/17/2013' where module = 'ar' and batnbr = '002399'

    One row was affected so I committed.

    Then I ran CA Integrity check:

    I went back to the Cash Balance and noticed the balance was still out.

    Did I miss something?

  • You would need to run Ca Integrity for both the old date and the new date.

    You must run the CA Integrity for one year at a time.  If the old date and new date were in different years, then you would need to make the year match the transaction date.

    You should not run it from 2005 thru 2013.  mm/dd/2005 - mm/dd/2005  for year 2005.  Then  run again from 12/1/2013 thru 12/31/2013 for year 2013.

  • Can I run again with the data in the current state or should I restore my database?

    At this point do I need to run just one CA Integrity Check for year 2005 and one for year 2013?

  • Run again in the current dbase

  • I've run CA Integrity Checks for 1/1/2005 to 12/31/2005 for FY 2005 (no account specified in the optional section), for 12/1/2013 to 12/31/2013 for FY 2013 (no account specified in the optional section), for 1/1/2005 to 12/31/2005 for FY 2005 (specifying the cash account that is out of balance), for 12/1/2013 to 12/31/2013 for FY 2013 (specifying the cash account that is out of balance), and for 1/1/2013 to 12/31/2013 for FY 2013 (specifying the cash account that is out of balance). The account is still out of balance. Additionally, the payment application still has the same date of 12/17/2005 in the screen Application Inquiry/Reversal (08.240.00).

  • Below are the steps we took to resolve our cash out of balance. We first tried these steps in our offline copy of our environment. Then we were able to take a back up of our Application database and execute the steps in production, having all users exit Dynamics SL. (Note: I used the batch number from the Application Inquiry/Reversal (08.240.00) screen for the SQL statement below.)

     

    SQL command:

    begin tran

    update batch set dateent = '12/17/2013' where module = 'ar' and batnbr = '002399'

    Commit

     

    CA Integrity checks for 2005 and 2013:

    CA Integrity Check 1

    Start: 1/1/2005

    End: 12/31/2005

    FY: 2005

    Company ID: our company

    Bank Account: account out of balance

    Bank Subaccount: subaccount out of balance

     

    CA Integrity Check 2

    Start: 1/1/2013

    End: 12/31/2013

    FY: 2013

    Company ID: our company

    Bank Account: account out of balance

    Bank Subaccount: subaccount out of balance

     

    Delete Bank Recs and re-enter ones we wish to keep.

    Financial > Cash Manager > Bank Reconciliation > Delete all reconciliations > Re-entered the ones we wished to keep.

     

    Thank you Elaine for your help with this issue!