Skip to main content

Notifications

Microsoft Dynamics SL forum
Unanswered

AR Statements

Posted on by Microsoft Employee

I have an AR statement which is not picking up all detail lines with a balance.  The total amount due and aging buckets are correct.  I have invoice, payment and credit memo transactions with open balances that do not display on the statement.  I have run the aging and generated statements. Any suggestions?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AR Statements

    So sorry, I was busy specifying the field names and forgot to include the table name in the query.  Here is the correct query:

    select perpost,docdate,docbal,curydocbal,stmtbal,stmtdate,opendoc,perclosed from ardoc where custid = 'xxxxx' and refnbr = 'nnnnnn'

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AR Statements

    is there a specific table name to run the query against? None of the fields were recognized when I tried.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AR Statements

    The only reasons that I have found for a document to not show on an open item statement would be that it either has no balance or it has not been picked up by the close statement cycle process or you have a modification to the statement report to ignore certain documents.

    You did not indicate if the missing items belong to certain customers only and whether some documents show for those customers while other documents do not.

    Run the following SQL query:

    select perpost,docdate,docbal,curydocbal,stmtbal,stmtdate,opendoc,perclosed where custid = 'xxxxx' and refnbr = 'nnnnnn'

    xxxxx is the customer id for a missing document and nnnnnn is the reference number for that document.

    Note the values returned.  If opendoc = 0 or stmtdate is blank or docbal = 0 then you need to figure out why.  You stated that the document shows on the aging with a balance so docbal should not be 0.00.  Let me know what values are returned.  I am most curious about the docdate, perpost and stmtdate values.

    Also, look at the customer maintenance screen to see what statement cycle this customer belongs to and look at the close statement cycle screen to see what date that statement cycle was closed.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AR Statements

    I am using the open item statement format.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: AR Statements

    The following applies if you are using a balance forward statement presentation.

    When you close the statement cycle, it rounds up all new documents that have been on a prior statement and have a balance and totals them to be the balance forward amount.  It rounds up all new documents that have not been on a statement and prepares them for detail on the statement and marks them as belonging to the statement run dated when you closed the statement cycle.  So, if these items were part of a close statement run done prior to your latest run and the latest run was on a date prior to the most recent closed date, those items are now part of the beginning balance and do not show as detail.

    The most common thing that happens is that the user either accidentally closes the statement cycle prematurely or closes it, reviews the statements and finds that they forgot to enter some charges so they enter those charges and close the statement cycle again but now on the same day the originally closed the statement cycle thereby causing the situation I believe you are describing.  There is a process you can use to reclose the same statement cycle of subsequent days (closing more than once on the same day is fine) but, once you don't follow that, you will have the issue.

    There are a couple of options to re-expose these documents as detail but before going into that we should confirm this is the case.  You can open the close statement cycle screen and look at the last closed date and see if that date is beyond when you first closed the statement cycle.  You can also run a SQL query similar to the following to further explore this.

    select * from ardoc where docbal <> 0

    Look at the field called StmtDate on the documents that are not showing but you think they should be and at the date of some documents that are showing.  If the dates are different, you have this issue.

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

Dynamics 365 Community Update – Sep 16th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,522 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,441 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans