I am trying to run a detailed debtors listing through SQL to provide a workflow for credit controllers without needing to log in to Nav.
I cannot find the table/view which shows remaining balance for customer ledgers, I have tried $Cust_ Ledger Entry] and $Detailed Cust_ Ledg_ Entry]
*This post is locked for comments
Hi, remaining amount is calculated field (no physical field for it in SQL) . it is calculated from Detailed Cust. Ledger Entry linked by entry no.
Below is the sample script on how to calculate :
SELECT
CLE.[Customer No_]
,CLE.[Document No_]
,SUM(DCLE.Amount) as 'Remaining Amount'
FROM [dbo].[CRONUS New Zealand Ltd_$Cust_ Ledger Entry] CLE
LEFT OUTER JOIN [dbo].[CRONUS New Zealand Ltd_$Detailed Cust_ Ledg_ Entry] DCLE ON DCLE.[Cust_ Ledger Entry No_] = CLE.[Entry No_]
GROUP BY CLE.[Customer No_],CLE.[Document No_]
<deleted>
Share
Report
All responses (
Answers (
3,594
