You’re offline. This is a read only version of the page.
Announcements
415
Basic System Information:
Microsoft Dynamics SL Service Pack 3 - Advanced
Version 7.04.40409.00 (7.0.0.0)
Issue:
One of our staff members accidentally posted a payment application batch to the incorrect period. Instead of posting to 201401, she posted to 201301. This, of course, creates havoc in reporting accurate balances.
I can't seem to find a good way to reverse this transaction and hoping that someone here can give solid guidance.
Any help you can provide would be greatly appreciated! Thanks in advance.
*This post is locked for comments
Something new to me, I did not realize that the table CASMSUMD also had a period field. the unique key on that table includes the date and period values (among other things) so that is why you get a key violation if you try to edit the CASHSUMD record.
The solution is to have the Cash manager Integrity Check process rebuild CASHSUMD. That will read the cash related transactions (now with the correct posting period) and put things together properly.
That's because there's a record for that day and account, maybe you can try to modify the amount of the record to match the sum of both.
Thanks for the reply. The only odd thing that I noted is the Daily Cash Balance inquiry and Report both those this one transaction as a 5/2 but it is currently sitting in the Cash Manager with a fiscal period of 04-2012 so it maintains its own identity but is on the correct date. Id id try to update CashSumD and it violates a primary key and will not accept the change.
I do not have a lot of clients using Cash Manager so my response should be tempered some.
Cash Manager operates on the transaction date and not the posting period (one of the reasons that it can be in some many conflicts with the other modules). The only exception to this is the balancing to the GL. If, as you say, the transaction date is 5/02 and you changed the posting period to period 5 then both sides should be fine. I believe that you will be able to reconcile the bank account.
I am wondering what you would need to change if you are using the Cash Manager. I see that the daily cash balance has two lines in the inquiry one for the transaction dated 5/2 but with the incorrect period of 04/2014 instead of 05/2015 as the rest of the AR and GL Tables have already been changed to reflect the correct period 05-2014. What needs to be changed to show that one transaction in the 05-2015 bucket?
If the batch has already been posted then the accthist table has been updated as per the posting period specified in the batch before making my changes. You would need to use the neat feature in Journal Transactions to reverse that batch and create a new one with the correct posting period. If the batch has not been posted then you can change the posting period on the GL side. You will need to turn on initialize mode and be an SL user who has initialize mode rights to the 0101000 screen in order to gain access to the AR batches so that you can either change the posting period or use the new reversing feature. Also, as I recall, if you are just changing the posting period (because the batch was not posted yet) you will also have to fill in the journal type field making in nonrecurring since AR batches have no value in that field.
Hello Rick
I have gone through your solution as mentioned above. But i have one clarification towards this suggested solution.
How the AccountHist table has been updated properly for the last fiscal year? & current fiscal year?
Do we need to update that accthist table too?
Just wanted to know..
Thanks & Regards
Jonathan,
Good to hear it has been resolved.
I typically do also look at and update the perappl field in aradjust if it is wrong but as I was writing my response I was thinking that perappl is set to the current AR period you are in and not the posting period specified in the payment application batch so I left that piece out.
Using the wrong posting period around year changes is fairly common for my clients. I bet I correct posting periods on batches 8 to 10 times each month within my client base. I am strongly considering creating a program to handle this (with the appropriate safety checks like making sure the batch is only released and not posted) so that the clients can self-service this situation. If I do create such a program I will post it in this forum incase anyone is interested in it.
Rick,
Thank you again for your assistance with this issue. I believe that I have it all resolved at this point.
The only thing missing from your advice was to set the perappl to the correct period for aradjust transactions. (See below).
For future reference purposes, here are the exactly SQL updates that I ran after completing simple selects to confirm it was the data set I wanted affected.
update ardoc set perpost='201401' where batnbr='019475'
update ardoc set perclosed='201401' where batnbr='019475' and perclosed<'201401' and perclosed!=''
update artran set perpost='201401' where batnbr='019475'
update aradjust set adjgperpost='201401' where adjbatnbr='019475'
update aradjust set perappl='201401' where adjbatnbr='019475'
update ardoc set perclosed='201401' where custid+doctype+refnbr in (select custid+adjddoctype+adjdrefnbr from aradjust where adjbatnbr = '019475') and docbal = 0 and perclosed!='' and perclosed<'201401'
If I were doing this more frequently, I would write variables for the batch number and correct period number, but hopefully I won't need to do this much in the future. The values '201401' and '019475' need to be updated as necessary.
As a word of caution to anyone reading this in the future - backup your database before performing any of these procedures and always select data and review before running an update query.
Rick, thanks again for helping to solidify the correction plan.
Here's my LinkedIn - please stay in touch: http://lnkd.in/dzqQnU2
Best,
Jonathan Thayer
If you are not using bank recon or cash manager then you have the steps correct and that will handle the issue. I do this with some frequency for my clients so I know it works. There should not be any aging balance issues and your aging should tie to your GL receivable balances.
There is one additional field on the ARDOC side that should be fine but you might want to check it with the following query since you are comfortable with that. This field is the perclosed field which should reflect the period the paid invoice or debit memo was closed.
select * from ardoc where custid+doctype+refnbr in (select custid+adjddoctype+adjdrefnbr from aradjust where adjbatbr = 'nnnnnn') and docbal = 0 and perclosed <> '' substitute the batch number for the nnnnnn.
The number of lines it finds can be different that the number of payment documents in the payment batch because it is possible that some payments do not clear the entire balance on an invoice and some payments may actually pay off several invoices.
If the perclosed field from the above query is showing 201301 (which I do not think it will) you could execute the following query to fix that field.
update ardoc set perclosed = 201401 where custid+doctype+refnbr in (select custid+adjddoctype+adjdrefnbr from aradjust where adjbatbr = 'nnnnnn') and docbal = 0 and perclosed <> '' substitute the batch number for the nnnnnn.
As always, it is a good idea to wrap an update statement with a begin transaction and a commit transaction line and only execute the begin transaction and update lines first to see how many lines it actually updates and then, if it matches the select statement that I provided first, execute the commit transaction. If it does not match, execute a rollback transaction and find the difference between the select statement and the update statement that is causing the variance.
#1
André Arnaud de Cal...
291,359
Super User 2024 Season 2
#2
Martin Dráb
230,370
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
