Question Status

Verified
Casey McCarthy asked a question on 14 Jan 2014 10:14 AM

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

Reply
Elaine Rettig responded on 14 Jan 2014 11:09 AM

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.

Reply
Casey McCarthy responded on 16 Jan 2014 8:13 AM

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

Reply
Verified Answer
Elaine Rettig responded on 16 Jan 2014 10:29 AM

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

Reply
Camille V. Prado responded on 23 Jan 2014 12:18 PM

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?

Reply
Casey McCarthy responded on 23 Jan 2014 12:35 PM

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.

Reply
Casey McCarthy responded on 28 Jan 2014 11:08 AM

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?

Reply
Verified Answer
Elaine Rettig responded on 28 Jan 2014 11:20 AM

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.

Reply
Casey McCarthy responded on 28 Jan 2014 11:52 AM

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?

Reply
Elaine Rettig responded on 28 Jan 2014 11:55 AM

Run again in the current dbase

Reply
Casey McCarthy responded on 28 Jan 2014 12:30 PM

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

Reply
Casey McCarthy responded on 30 Jan 2014 8:26 AM

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!

Reply
Verified Answer
Elaine Rettig responded on 16 Jan 2014 10:29 AM

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

Reply
Verified Answer
Elaine Rettig responded on 28 Jan 2014 11:20 AM

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.

Reply