GL and Cash Balance are incorrect from incorrect date entered

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (11)

Answers (2)

Community Member on at

Like (0)

Report

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

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.

Was this reply helpful? Yes No
Community Member 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).

Was this reply helpful? Yes No
Verified answer

Community Member 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

Was this reply helpful? Yes No
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?

Was this reply helpful? Yes No
Community Member 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.

Was this reply helpful? Yes No
Community Member 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?

Was this reply helpful? Yes No
Verified answer

Community Member 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.

Was this reply helpful? Yes No
Community Member 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?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Run again in the current dbase

Was this reply helpful? Yes No
Community Member 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).

Was this reply helpful? Yes No