Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.6K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 295 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Problem in AR Aging Report 10040 with closed entries...
jversusj
Member Posts: 489
First off, i have read through a lot of threads regarding the AR Aging and tried some of the tips - but I am still left with a problem.
I am using report 10040 from NAV version 3.7b.
When we ran the aging on Dec 17 for Nov 30, we got 1 number that matched the G/L. The auditors then asked for the same aging (as of Nov 30) to be run on Feb 25. The numbers did not match. We isolated the difference as an invoice that was posted Nov 17. It appeared in the Dec 17 Aging output, but no longer appears in the output when the report is re-ran today.
This invoice was posted Nov 17 for $819.15. On Dec 11, a customer payment arrived for $865.65 and was applied against the invoice (leaving a payment balance open). On Dec 30, a credit memo was issued for the Nov 17 invoice (apparently we forgot sales tax, hence the customer's overpayment), a new invoice was generated (with sales tax). Then, the new invoice, the credit memo, and the remaining payment were applied together (shift+f9 apply entries from CLE) - as a result, the customer payment was closed by the credit memo and the credit memo was closed by the customer payment (according to the Closed By Entry No. on the respective Cust. Ledger Entries).
I looked at the report design. My version still uses 4 instances of the Cust. Ledger Entry. The first instance is filtered to only Open entries. The second is named AfterAgeDateEntry, which serves as a parent to the indented ClosesAfterAgeDateEntry and ClosedByAfterAgeDateEntry instances of the CLE.
I noticed that on PreDataItem for ClosedByAfterAgeDateEntry, there is the following line of code:
Now, in this case, my payment entry is found (as an after age date entry) and the report should look to find my original invoice which has a Closed by Entry No. = Entry No. of the payment - but that does not happen because the Payment record's Amount - "Remaining Amount" - "Closed by amount" = 0, so it is skipped. my invoice is therefore overlooked and no longer appears on the aging.
So, i have tried the following according to what i read in other threads:
1. remove the Open = Yes filter on the first CLE dataitem. This resulted in a lot of entries showing on the report, but with no amounts. It did not impact the aging totals in any regard. I assume this is because this tip related to the version 4.0 report (which calculatated closed entries by way of a temp table and some other functionality my version simply does not have).
2. i tried to import the Nav4.0 version of the report but could not compile it because it was looking for a codeunit that does not exist in my system (Entry Application Management).
3. I commented out that code that I quoted above - the formula checking the AfterAgeDateEntry. This change resulted in the invoice once again appearing on the Aging! So, i ran the report for September 30 and compared to what we historically had record for September and the report did not match.
I then ran it for November and compared to the Dec 17 value, and it matched to the penny. i also reran December 31 aging, and it matched as well. 
i'm a little confused because the accounting department always confirms that the aging matches the G/L when they run it - why september is now different is strange.
so, any thoughts? by commenting out the code on the OnPreDataItem for ClosedByAfterAgeDateEntry, am i causing any other problems you can think of (assuming it is standard code)?
thanks in advance - let me know if you need more clarification, as i understand the above may be hard to follow.
I am using report 10040 from NAV version 3.7b.
When we ran the aging on Dec 17 for Nov 30, we got 1 number that matched the G/L. The auditors then asked for the same aging (as of Nov 30) to be run on Feb 25. The numbers did not match. We isolated the difference as an invoice that was posted Nov 17. It appeared in the Dec 17 Aging output, but no longer appears in the output when the report is re-ran today.
This invoice was posted Nov 17 for $819.15. On Dec 11, a customer payment arrived for $865.65 and was applied against the invoice (leaving a payment balance open). On Dec 30, a credit memo was issued for the Nov 17 invoice (apparently we forgot sales tax, hence the customer's overpayment), a new invoice was generated (with sales tax). Then, the new invoice, the credit memo, and the remaining payment were applied together (shift+f9 apply entries from CLE) - as a result, the customer payment was closed by the credit memo and the credit memo was closed by the customer payment (according to the Closed By Entry No. on the respective Cust. Ledger Entries).
I looked at the report design. My version still uses 4 instances of the Cust. Ledger Entry. The first instance is filtered to only Open entries. The second is named AfterAgeDateEntry, which serves as a parent to the indented ClosesAfterAgeDateEntry and ClosedByAfterAgeDateEntry instances of the CLE.
I noticed that on PreDataItem for ClosedByAfterAgeDateEntry, there is the following line of code:
IF ( AfterAgeDateEntry.Amount
- AfterAgeDateEntry."Remaining Amount"
- AfterAgeDateEntry."Closed by Amount" ) = 0
THEN
CurrReport.BREAK; // Optimization!
I do not know if this is standard code or a customization. Anyone have this answer?Now, in this case, my payment entry is found (as an after age date entry) and the report should look to find my original invoice which has a Closed by Entry No. = Entry No. of the payment - but that does not happen because the Payment record's Amount - "Remaining Amount" - "Closed by amount" = 0, so it is skipped. my invoice is therefore overlooked and no longer appears on the aging.
So, i have tried the following according to what i read in other threads:
1. remove the Open = Yes filter on the first CLE dataitem. This resulted in a lot of entries showing on the report, but with no amounts. It did not impact the aging totals in any regard. I assume this is because this tip related to the version 4.0 report (which calculatated closed entries by way of a temp table and some other functionality my version simply does not have).
2. i tried to import the Nav4.0 version of the report but could not compile it because it was looking for a codeunit that does not exist in my system (Entry Application Management).
3. I commented out that code that I quoted above - the formula checking the AfterAgeDateEntry. This change resulted in the invoice once again appearing on the Aging! So, i ran the report for September 30 and compared to what we historically had record for September and the report did not match.
I then ran it for November and compared to the Dec 17 value, and it matched to the penny. i also reran December 31 aging, and it matched as well. i'm a little confused because the accounting department always confirms that the aging matches the G/L when they run it - why september is now different is strange.so, any thoughts? by commenting out the code on the OnPreDataItem for ClosedByAfterAgeDateEntry, am i causing any other problems you can think of (assuming it is standard code)?
thanks in advance - let me know if you need more clarification, as i understand the above may be hard to follow.
kind of fell into this...
0
Comments
July:
My modifed report matched the original report in total AR value when ran today, but does not equal the Aging total that was run in August for July.
August:
My modified report matched the original report in total AR value when ran today, but does not equal the Aging total that was run in September for August.
September:
My modified report matched the original report in total AR value when ran today, but does not equal the Aging total that was run in October for September.
October:
My modified report matched the original report in total AR value when ran today, and both equal the Aging total that was run in November for October.
November:
My modified report does not match the original report in total AR value, but it does equal the Aging total that was run in December for November.
December:
My modified report matched the original report in total AR value when ran today, and both equal the Aging total that was run in January for December.
Any ideas? Only the last quarters' Aging values can be recreated with my modified report...
does anyone have any tips for me on what i can do to try to make our AR Aging report believable?
i can understand why amounts might shift in the aging buckets, but i do not see how the total value of the aging could change when running the reports for the same period, but several months apart. thanks in advance!