NAV SQL Query question

Dear Collegs,

I am currently bussy with Navision reports making with one BI tool. In that process I faced with one issue for which do not know exact solution.
Namely, my problem is that customer want report in which will be presented Outstanding Amounts of their customers ordered by Due Date.
Theoreticly it means that they want to see report which all open Invoices and Credit Notes with the Due Date information.

How much I understand NAV database and tabels for this report should to use two tabels :
CRONUS $Cust_ Ledger Entry and CRONUS $Detailed Cust_ Ledg_ Entry .

In the tabel CRONUS $Cust_ Ledger Entry is one column with the name Open which can have values 0 or 1. How much I understand if value is 0 it means that this document (Invoice or Credit Note) is closed and if value is 1 it means that document (Invoice or Credit Note) is Open.

If it translate to my report it means that is neccesery to Select only document for which column Open has value 1.
Something like this :
Select [CRONUS Mexico S_A_$Cust_ Ledger Entry1000000021].[Document No_] As DocNo,
[CRONUS Mexico S_A_$Cust_ Ledger Entry1000000021].[Open] As Status,
[CRONUS Mexico S_A_$Cust_ Ledger Entry1000000021].[Due Date] As DueDate
From [CRONUS Mexico S_A_$Cust_ Ledger Entry1000000021]
Where [CRONUS Mexico S_A_$Cust_ Ledger Entry1000000021].[Open] = 1

Further how much I understand is neccesery to create Left Join with the tabel CRONUS $Detailed Cust_ Ledg_ Entry and to add Open Amounts to all documents which which in column Open has Value 1.
Here starts my problems.
My Question , how can do this, which querry is neccesery to make in CRONUS $Detailed Cust_ Ledg_ Entry to achive to have all correct Open Amounts for ceartin documents (Sales Invoices and Credit Notes).

Is this good way to build this query or not ? How can summarize this Outstanding Amounts in CRONUS $Detailed Cust_ Ledg_ Entry tabel.

Hope that somebody can help.

Thanks in advance.

Hi Stomar,

You need to create a LEFT JOIN on “Cust. Ledger Entry”.“Entry No.” = “Detailed Cust. Ledger Entry”.“Cust. LEdger Entry No.”. Filter on Cust Ledger Entry.Document Type to 2 or 3 to get Invoice or Credit Memo. Filter on Open = 1 to select open entries.

Do a SUM statement on Amount and/or Amount(LCY) in Detailed Cust. Ledger Entry. This will give you the remaining amount (outstanding amount)



I should add, that you can not fully rely on Open field - actually it means is the Invoice FULLY allocated to some Payment(s) or not. Partial payments (allocations) can be tracked by means of Allocate field - so you can report Amt still due. BUT - there might be a situation, when Invoice actually is fully PAID, but Allocation is NOT performed yet, so Open=Yes and Allocate also doesn’t correspond to real life situation.
This, however, is an administrative issue - if you perform such reporting, you must oblige your users to perform Allocation immediately after recording payments. Especially this refers to transactions in foreign currencies - as such “Open” entries are subject to calculate ForEx adjustments.