web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / Payment Application Ba...
Microsoft Dynamics SL (Archived)

Payment Application Batch Posted To Wrong Period

(0) ShareShare
ReportReport
Posted on by jmthayer 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

I have the same question (0)
  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    If you have someone that is comfortable with SQL queries you can execute the following queries to correct the issue.

    update ardoc set perpost = 201401 where batnbr = 'nnnnnn'    note: nnnnnn is the batch number for the payment application batch

    update artran set perpost = 201401 where batnbr = 'nnnnnn'

    update aradjust set adjgperpost where adjbatnbr = 'nnnnnn'

    The above will correct the AR side.  Next, you can correct the GL side by turning on initialize mode with a user id that has initialize mode rights (e,g, the SL administrator), going to the Journal Transactions menu item in GL, changing the module to AR, selecting the batch number in question and changing the posting period.  When you are done, turn initialize mode back off.  This is assuming that you have not already posted the batch to 01-2013.  If you have, do not do this as it will not fix the GL account balances that were already updated by the posting of this batch.  If it has been posted, you can use the GL feature to create a reversing batch and a replacement batch.

    If you are not using bank reconciliation or cash manager, this will solve the issue.  If you are using one of these modules then reply back and we can go over what to do for that module.

  • jmthayer Profile Picture
    jmthayer 415 on at

    Rick,

    Thank you very much for your detailed response.

    The GL entry has already been posted, so it will need to be reversed.

    I am comfortable executing the queries to adjust as necessary.  Not a problem there.

    Just as a final confirmation from you, this is my sequence of items to complete:

    1.)  Update ardoc for this batch;

    2.)  Update artran for this batch;

    3.)  Update aradjust for this batch;

    4.)  Reverse original GL entry (201301);

    5.)  Reenter GL transaction in proper period (201401).

    And just as a confirmation, there are no pitfalls regarding aging balances, etc. with this approach?

    Thanks again for your help!

  • Community Member Profile Picture
    Community Member on at

    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.

  • jmthayer Profile Picture
    jmthayer 415 on at

    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

  • Community Member Profile Picture
    Community Member on at

    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.

  • Community Member Profile Picture
    Community Member on at

    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

  • Community Member Profile Picture
    Community Member on at

    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.

  • Community Member Profile Picture
    Community Member on at

    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?

  • Community Member Profile Picture
    Community Member on at

    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.

  • Community Member Profile Picture
    Community Member on at

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans