Aged Debts Report

Hi all,

I have difficulty in aged accounts recievable report. I can work out the balance column but the columns that are divided in months i cannot figure out how the report is calculating the amounts. I am trying to replicate it in the query using table cust ledger and detailed cust ledger but cant figure out how the report is calculating the aged debt. I am using the posting date method and using period length 1M.

I cant figure how the report calculates the amount on current month and then other months going back. Please I will need a help

Thanks.

Any body reply on this please? I have checked the coding in the report but its too complicated. So I need to understand this.

Thanks.

Hi no one has the idea?? Please any one can help i will appreciate it.

a/ why are you doing this?

b/ what’s the question?

Are you trying to replicate this report in SQL? If so, NAV is using arrays to calulate the totals and you will not want to replicate this functionality that way. The way I do it is to use a date dimention table. If this is what you are trying to do, let me know and I think I can send you a sample pretty quick.

If you are trying to duplicate in a differant NAV report, please give me more details on exactly where you are getting confused (the sum or just coming up with the period dates) and I will do my best to explain.

Hi Dave and Teresa

Thanks for replies. As Teresa noted I am trying to replicate this report in SQL and also I was to understand the report as well how it works bascially in NAV and might replicate it in other report as well. The report is Aged accounts Receivable in DB database of NAV. The parameters I am using on the report is Period Length 1M and aged by posting date. Its the standard report so no modifications are on it.

On the report first there is balance column. Then 5 columns divided on months going back. I can work out on the amounts in balance column as its easy :). But on other columns I am not getting correct amounts as report shows , I am getting different amounts when I try to replicate in SQL and even if i try to replicate in NAV report.

Teresa I am trying to figure out on which table and on which column and what filteration I should use to get same amount as report is getting. Then I can easily do the coding. Report is using customer ledger entries and detailed customer ledger entries which I am doing same but not getting even close. Maybe I am using wrong filteration on tables?

Help :slight_smile:

This would not be advisable in SQL because there are so many variations and the business logic or base data can change from version to version or over time. With that said, you may have a good reason to do it. I don’t know.

The logic that I see is to join a customer with it’s customer ledger entries (CL1). Then left join those customer ledger entries with the customer ledger entries again (CLAE) based on the first customer ledger entries [Closed by Entry No_] field = the Entry field. You also need to limit the Detailed customer ledger entries to this list 'Initial Entry|Unrealized Loss|Unrealized Gain|Realized Loss|Realized Gain|Payment Discount|‘Payment Discount (VAT Excl.)’|‘Payment Discount (VAT Adjustment)’|Payment Tolerance|Payment Discount Tolerance|‘Payment Tolerance (VAT Excl.)’|‘Payment Tolerance (VAT Adjustment)’|‘Payment Discount Tolerance (VAT Excl.)’|‘Payment Discount Tolerance (VAT Adjustment)’

that will give you the right entries then you want every entry for each period where the CL1<=@PeriodEndingDate AND CLAE<=@PeriodEndingDate or is null

As I mentioned before, I deal with Accounting periods by making a DateDimesion table for every date that has things that are relivant for that date. In this case, that relivant data is the date, the accountingPeriod, and the last day in that AccountingPeriod. Then you can join this in and pivot on the period.

This form does not keep the format of the sql query very well and it would take a lot of space for the dateDimension data.

I hope this helps. If you can, I really suggest the users just use the NAV report. If it is e-mail capabilities that you want or something like that, there are other ways to pull off that solution.

after looking at this again, I was able to simplify the original query by just using the customer ledger entries and Detailed Cust Ledger entries both filtered by the PeriodEndingDate. The filters for the Detailed Cust Ledger Entries stays the same.

Thanks Teresa for your help. You are a Star!

if posting dates and quantity of the report same at that time why it shows two columns ,I want if both posting dates and quantities are same it shows sum of in one line