Answered

Date Error Dynamics SL 7.0

(0) Share

Report

Posted on by Community Member Microsoft Employee

When I applied the finance charges the date was supposed to be 03/15/14 and I entered 03/15/15 so when I went to run my statements the finance charges were incorrect.

I want to see if the following solution will work:

1. Do a small balance write off for the finance charges applied for 03/15/15

2. In the AR Module under Statement Cycle (08.280.00) change the following:

*Last Aging Date to 02/15/14

*Last Statement Date to 02/15/14

*Last Finance Charge to 02/15/14

3. Apply Finance Charges 03/15/14 and Close Statement Cycle

Do you think this would be a solution? I look forward to hearing from you!

All responses (10)

Answers (1)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Date Error Dynamics SL 7.0

Hi Rick,

Here are the statements we ran on the SL database, in order:

select custid, doctype,origdocamt,* from ardoc where batnbr in ('002351', '002352') order by custid

update ardoc set stmtdate = '02/15/2014' where batnbr in ('002351', '002352')

update arstmt set closedatetime = '02/15/2014', lastagedate = '02/15/2014', lastfinchrgdate = '02/15/2014', laststmtdate = '02/15/2014' where stmtcycleid = '01'

update ar_balances set lastagedate = '02/15/2014', lastfinchrgdate = '02/15/2014', laststmtdate = '02/15/2014' where lastfinchrgdate = '03/15/2015'

WORKED PERFECT!! Thank you so much for all your assistance!
Community Member Microsoft Employee on at

Like (0)

Report

RE: Date Error Dynamics SL 7.0

Sorry, that field name is closedatetime and not just closedate.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Date Error Dynamics SL 7.0

We have done everything so far with success however the following we are having trouble with:

You will have to change those using a SQL query such as the following:

update arstmt set closedate = '02/15/2014', lastagedate = '02/15/2014', lastfinchrgdate = '02/15/2014', laststmtdate = /02/15/2014' where stmtcycleid = 'xx' note: xx is your statement cycle id.

You will also have to run the following query to fix up the ar_balances file:

update ar_balances set lastagedate = '02/15/2014', last finchrgdate = '02/15/2014', last stmtdate = '02/15/2014' where lastfinchrgdate = '03/15/2015'.

The error states:

Invalid column name 'closedate'

Help!!!!
Community Member Microsoft Employee on at

Like (0)

Report

RE: Date Error Dynamics SL 7.0

OK, in order to do this you will need the batch number of the small balance write-off batch and the batch number for the incorrect finance charges. what we will be doing is marking documents from both of those batches as if they already existed in a prior close statement cycle run. That will make them be part of the beginning balance amount and not new documents on the statements. Since the two sides offset each other, this will work and not distort the beginning balance.

First, do the following select statement to make sure it finds only the finance charges and the small balance write-offs

select custid, doctype,origdocamt,* from ardoc where batnbr in ('xxxxxx', 'yyyyyy') order by custid

The xxxxxx is the batch number of the finance charge batch and yyyyyy is the batch number of the small balance write off batch. Scan the results making sure you only have those 2 doc types and that the amounts are the same for each customer.

Once the above looks good then execute the following:

update ardoc set stmtdate = '02/15/2014' where batnbr in ('xxxxxx', 'yyyyyy')

When that is done, and you have completed the other SQL statements I provided, you should be able to close the statement cycle and get good statements. DO NOT FORGET TO CHANGE THE BUSINESS DATE BACK TO 3/18/2014.

Also, when doing things like this, it is strongly advised that you make a backup before running the SQL update statements so you have a fall-back point to get to if one of the SQL commands is entered incorrectly.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Date Error Dynamics SL 7.0

If you are interested in hiding the bad finance charge and the small balance write-off documents from the replacement statements, let me know and I can provide the queries to do that.

Yes please!!
Community Member Microsoft Employee on at

Like (0)

Report

RE: Date Error Dynamics SL 7.0

From this it looks like you closed the statement cycle on 3/18/2014. Because you use a balance forward statement presentation, you do not want to close the statement cycle again today without changing the SL business date back to 3/18/2014. This is because the close statement cycle process rounds up all the open documents that have been part of a previous statement cycle (one with a statement date less than the current statement date) and sums those to be the beginning balance on the statement. It also marks the new documents shown on the statement with a statement date equal to the day you closed the statement cycle (03/18/2014 in this case). Therefore, if you were to close the statement cycle again today it would treat all those documents that came out on the statement as documents that were on a previous statement and will make their balances part of the beginning balance amount and not show those documents. If the statement date on the close statement cycle screen is the same as the last statement close date, then those new documents are considered as part of the current statement cycle and will not be part of the beginning balance and will show as detail on the statement. Since you cannot change the statement date on that screen, the only way to force it back to the actual date that you first closed the statement cycle is to change the SL business date. If you do not change the business date to the last statement cycle close date and close the statement cycle again and you use a balance forward presentation, there is no reasonable way to get back to the correct beginning balance (should be equal to the ending balance on the previous statement) on the statements. So, be very careful when closing a statement cycle over again.

So, run the two queries a provided, do you small balance write-off, calculate financial charges again and then change the business date back to 3/18/2014 and close the statement cycle again. If you are interested in hiding the bad finance charge and the small balance write-off documents from the replacement statements, let me know and I can provide the queries to do that.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Date Error Dynamics SL 7.0

Here is the information you requested - let me know if you need anything else!

Close Statement Cycle (08.590.00) screen:

Statement Date - 03/19/2014

Last Aging Date - 03/18/2014

Last Statement Date - 03/18/2014

Apply Finance Charges (08.520.00) screen:

Finance Charge Date - 03/19/2014 (this is where the 03/15/15 date was entered in error)

Last Fin Chrg Date - 03/15/2015

Last Statement Date - 03/18/2014
Community Member Microsoft Employee on at

Like (0)

Report

RE: Date Error Dynamics SL 7.0

After providing my response I started thinking that you had to have entered the 03/15/2015 date on the apply finance charges screen because the close statement cycle screen does not allow you to enter a date. It simply picks up the current date. You may have also closed the statement cycle but that would have been as of the current date. To confirm this, open the close statement cycle screen and note the last aging date and the last statement date. I suspect that the last aging date will be the 2015 date but the last statement date will be in the current year. Please confirm back to me what you see on that screen before I go any further. I do suspect that, once you get a correct finance charge amount you will want to reprint statements with that amount and the new charges so there will be some more work to do. Also, since you sue a balance forward presentation, there is a way to hide the incorrect finance charge amount and the small balance write-off amount from the statements so the customers do not see your mistake.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Date Error Dynamics SL 7.0

Thanks Rick - we do use the balance forward and close the statement cycle process so I guess we will need more information.

We were also thinking about restoring using our daily backup but I am waiting to hear from our IT department.
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Date Error Dynamics SL 7.0

First, you will not be able to change those dates on the close statement cycle screen because they are read-only even with initialize mode set. You will have to change those using a SQL query such as the following:

update arstmt set closedate = '02/15/2014', lastagedate = '02/15/2014', lastfinchrgdate = '02/15/2014', laststmtdate = /02/15/2014' where stmtcycleid = 'xx' note: xx is your statement cycle id.

You will also have to run the following query to fix up the ar_balances file:

update ar_balances set lastagedate = '02/15/2014', last finchrgdate = '02/15/2014', last stmtdate = '02/15/2014' where lastfinchrgdate = '03/15/2015'.

The next issue may or may not apply depending on whether you use a balance forward or open item statement presentation and whether the finance charges were created as part of the close statement cycle process or were done using the apply finance charge screen.. You stated that you used the apply finance charge process and not the close statement cycle process so I will not go into the issue if you did not use the close statement cycle process. Let me know if my assumption is incorrect.

Date Error Dynamics SL 7.0

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Announcing Our 2024 Season 2 Super Users!

Dynamics 365 Community Newsletter - September 2024

more Community News

Leaderboard

Featured topics

Product updates

Date Error Dynamics SL 7.0

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Announcing Our 2024 Season 2 Super Users!

Dynamics 365 Community Newsletter - September 2024

more Community News

Subscribe to

Leaderboard

Featured topics

Product updates