GL and Cash Balance are incorrect from incorrect date entered

(0) Share

Report

Posted on by Community Member Microsoft Employee

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

*This post is locked for comments

All responses (11)

Answers (2)

Community Member Microsoft Employee on at

Like (0)

Report

RE: GL and Cash Balance are incorrect from incorrect date entered

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!
Community Member Microsoft Employee on at

Like (0)

Report

RE: GL and Cash Balance are incorrect from incorrect date entered

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).
Community Member Microsoft Employee on at

Like (0)

Report

RE: GL and Cash Balance are incorrect from incorrect date entered

Run again in the current dbase
Community Member Microsoft Employee on at

Like (0)

Report

RE: GL and Cash Balance are incorrect from incorrect date entered

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?
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: GL and Cash Balance are incorrect from incorrect date entered

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.
Community Member Microsoft Employee on at

Like (0)

Report

RE: GL and Cash Balance are incorrect from incorrect date entered

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?
Community Member Microsoft Employee on at

Like (0)

Report

RE: GL and Cash Balance are incorrect from incorrect date entered

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.
Camille V. Prado 5 on at

Like (0)

Report

RE: GL and Cash Balance are incorrect from incorrect date entered

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?
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: GL and Cash Balance are incorrect from incorrect date entered

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
Community Member Microsoft Employee on at

Like (0)

Report

RE: GL and Cash Balance are incorrect from incorrect date entered

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