Microsoft Dynamics NAV flow fields

Good day all

I wrote some sql script to monitor overdue customer and their amounts outstanding. However I am unable to detect the correct Amount / Amount LCY value. I am querying the

[Detailed Cust_ Ledg_ Entry] table  as the main source and also [Cust_ Ledger Entry] table. how can I work around this issue to get the correct value as on NAV. below is a screenshot of the values and flow field in NAV as well as my SQL script (which do not display the correct value)

 

 

SELECT

b.[Posting Date], --generally the posting date will be the same as the document date or after the document date

a.[Document Date],

a.[Due Date],

a.[Closed at Date],

b.[Entry Type],

CAST(c.[Blocked] AS NVARCHAR(5)) AS [Blocked],

CAST(b.[Document Type] AS NVARCHAR(10)) AS [Document Type],

CAST(b.[Customer No_] AS NVARCHAR(20)) AS [Customer No_],

--CAST(a.[Sell-to Customer No_] AS NVARCHAR(20)) AS [Sell-to Customer No_],

CAST(c.[Name] AS NVARCHAR(60)) [Customer Name],

CAST(b.[Document No_] AS NVARCHAR(10)) AS [Document No_],

CAST(a.[Description] AS NVARCHAR(50)) AS [Description],

CAST(b.[Source Code] AS NVARCHAR(10)) AS [Source Code],

CAST(b.[Currency Code] AS NVARCHAR(10))[Currency Code],

CAST(c.[Salesperson Code] AS NVARCHAR(10)) AS [Salesperson Code],

CAST(c.[Country_Region Code] AS NVARCHAR(10)) AS [Country_Region],

CAST(c.[Responsibility Center] AS NVARCHAR(10)) AS [Responsibility Center],

CAST(c.[Payment Terms Code] AS NVARCHAR(10)) AS [Payment Terms Code],

CAST(a.[Open] AS NVARCHAR(5)) AS [Open],

CAST(c.[Liquor License No_] AS [nvarchar](50)) AS [Liquor License No_],

c.[Suspension Period],

d.Name as [Country Name],

b.[Cust_ Ledger Entry No_],

c.[Credit Limit Annex No_],

sum(a.[Sales (LCY)]) as [Sales (LCY)],

sum(a.[Profit (LCY)])AS [Profit (LCY)],

sum(b.[Amount])AS [Amount],

sum(b.[Amount (LCY)])AS [Amount (LCY)], --LCY measure are financial amount in Local Currency

sum(b.[Debit Amount])AS [Debit Amount],

sum(b.[Credit Amount])AS [Credit Amount],

sum(b.[Debit Amount (LCY)])AS [Debit Amount (LCY)],

sum(b.[Credit Amount (LCY)] ) AS [Credit Amount (LCY)],

c.[Credit Limit (FCY)] AS [Credit Limit (FCY)],

c.[Credit Limit (LCY)] AS [Credit Limit (LCY)]

FROM [Spier Live$Detailed Cust_ Ledg_ Entry] b

 

LEFT JOIN [Spier Live$Customer] c

ON b.[Customer No_] = c.[No_]

 

LEFT JOIN [dbo].[Spier Live$Cust_ Ledger Entry] a

ON a.[Entry No_] = b.[Cust_ Ledger Entry No_]

 

LEFT JOIN [dbo].[Spier Live$Country_Region] d

ON c.[Country_Region Code] = d.[Code]

WHERE b.[Document No_] <> '0'

AND b.[Customer No_] NOT LIKE ('%ZZ%') --exclude sample customers

AND b.[Document No_] in ('117296','118830')

AND b.[Document Type] = '2'

GROUP BY b.[Posting Date], a.[Document Date], b.[Customer No_],

b.[Document Type],b.[Document No_], a.[Description], c.[Salesperson Code],

b.[Currency Code], b.[Source Code],a.[Open], a.[Due Date],c.[Blocked],

a.[Closed at Date],c.[Country_Region Code],c.[Responsibility Center],

c.[Name],c.[Payment Terms Code], b.[Entry Type], d.[Name], b.[Cust_ Ledger Entry No_],

[Credit Limit (FCY)],c.[Credit Limit (LCY)],c.[Credit Limit Annex No_],c.[Suspension Period],c.[Liquor License No_]

  • Hello, here you have a simple example which shows the Balance and Overdue Amounts.

     

    SELECT [CRONUS AG$Customer].No_, [CRONUS AG$Customer].Name, SUM([CRONUS AG$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) AS [Amount(LCY)], sum(case when [CRONUS AG$Cust_ Ledger Entry].[Due Date]<getdate() then [CRONUS AG$Detailed Cust_ Ledg_ Entry].[Amount (LCY)] else 0 end) as [OverDueAmount(LCY)]
    FROM     [CRONUS AG$Cust_ Ledger Entry] INNER JOIN
                      [CRONUS AG$Detailed Cust_ Ledg_ Entry] ON [CRONUS AG$Cust_ Ledger Entry].[Entry No_] = [CRONUS AG$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_] INNER JOIN
                      [CRONUS AG$Customer] ON [CRONUS AG$Cust_ Ledger Entry].[Customer No_] = [CRONUS AG$Customer].No_
    GROUP BY [CRONUS AG$Customer].No_, [CRONUS AG$Customer].Name
    HAVING ([CRONUS AG$Customer].No_ = '20000')

    Hope it helps you. . .

Related