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?
*This post is locked for comments
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'
is there a specific table name to run the query against? None of the fields were recognized when I tried.
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.
I am using the open item statement format.
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.
#1
André Arnaud de Cal...
292,494
Super User 2025 Season 1
#2
Martin Dráb
231,307
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
