How to get debit balance and credit from sql server in dynam

ahmedba

Hi guys i have dynamic nav 2009 and sql server 2005

I have this query show data from sales line and sales header



SELECT ROW_NUMBER() OVER (ORDER BY dbo.[Jeddah-Live$Sales Header].No_) AS [م], dbo.[Jeddah-Live$Sales Line].[Document No_] AS 'رقم الطلب',

dbo.[Jeddah-Live$Sales Header].[Bill-to Name] AS 'العميل', dbo.[Jeddah-Live$Sales Line].Area AS 'نوع الصبه', dbo.[Jeddah-Live$Sales Line].Description AS 'البيان',

dbo.[Jeddah-Live$Sales Header].[Pump No_] AS 'المضخه',dbo.[Jeddah-Live$Sales Line].[Outstanding Quantity]

FROM dbo.[Jeddah-Live$Sales Header] INNER JOIN

dbo.[Jeddah-Live$Sales Line] ON dbo.[Jeddah-Live$Sales Header].No_ = dbo.[Jeddah-Live$Sales Line].[Document No_] AND

dbo.[Jeddah-Live$Sales Header].[Sell-to Customer No_] = dbo.[Jeddah-Live$Sales Line].[Sell-to Customer No_]

WHERE DATE DIFF(d,dbo.[Jeddah-Live$Sales Line].[Shipment Date],GETDATE()) = 0

The query above success work and show data based on date today

My question :I need to add debit balance and credit balance to every customer

from starting period until today

How i do that

bbrown

First your query above is wrong. You need to relate the lines based on the full primary key of the header. Don't assume a document number is unique. It is possible to have the same document number as post an order or credit memo, as an example.

On your questions, I'd suggest looking at how NAV produced those numbers. Look to reports or pages where that information would normally be viewed in NAV. Then use the logic of that function to design your SQL queries. It will also give you something to test your queries against. For example, run a "Customer Trial Balance" or "Customer AR Aging". Do your SQL queries yield the same results.

ahmedba

Thank you for reply
if i need to make query
How i do
if it is wrong
show to me the true if possible

bbrown

Add "Document Type" to your join relation. The primary key for the header is ["Document Type", "No."]. You need to include both in your join to insure line records for other documents are not included.

ahmedba

Thank you for reply
Can you help me in write view collect IN SQL SERVER 2005 TO GET
Sum of debit balance to every customer from (details customer ledger entry table)
AND
Sum of credit balance to every customer from (details customer ledger entry table)
AND
Customer No from (customer table)
AND
Customer Name from (customer table)
Relation between customer table and details customer ledger entry table

Faulcon

Isn't that what the "Credit Amount" and "Debit Amount" fields on the Customer table already do? If you want to display it externally and not within Navision then you need to take the CalcFormula property from those fields and translate it to a SQL statement.

eg Debit Amount CalcFormula is:

Sum("Detailed Cust. Ledg. Entry"."Debit Amount" WHERE (Customer No.=FIELD(No.),Entry Type=FILTER(<>Application),Initial Entry Global Dim. 1=FIELD(Global Dimension 1 Filter),Initial Entry Global Dim. 2=FIELD(Global Dimension 2 Filter),Posting Date=FIELD(Date Filter),Currency Code=FIELD(Currency Filter)))

Which would be (ignoring global dim 1, 2, Posting Date - since you want everything -, and currency code):

SELECT     SUM([Debit Amount]) AS SumDebitAmount, [Customer No_]
FROM         [<Company>$Detailed Cust_ Ledg_ Entry]
GROUP BY [Customer No_], [Entry Type]
HAVING      ([Entry Type] <> 2)

Change the HAVING line to "HAVING ([Entry Type] <> 2) AND ([Customer No_] = '<CustomerNo>')" if you want a specific customer, not just a full list.

To get both Debit and Credit just make the select statement read
SELECT SUM([Debit Amount]) AS SumDebitAmount, SUM([Credit Amount]) AS SumCreditAmount, [Customer No_]

And finally including the Customer Name:

SELECT     SUM([<Company>$Detailed Cust_ Ledg_ Entry].[Debit Amount]) AS SumDebitAmount, 
                      SUM([<Company>$Detailed Cust_ Ledg_ Entry].[Credit Amount]) AS SumCreditAmount, 
                      [<Company>$Detailed Cust_ Ledg_ Entry].[Customer No_], [<Company>$Customer].Name
FROM         [<Company>$Detailed Cust_ Ledg_ Entry] INNER JOIN
                      [<Company>$Customer] ON [<Company>$Detailed Cust_ Ledg_ Entry].[Customer No_] = [<Company>$Customer].No_
GROUP BY [<Company>$Detailed Cust_ Ledg_ Entry].[Customer No_], [<Company>$Detailed Cust_ Ledg_ Entry].[Entry Type], 
                      [<Company>$Customer].Name
HAVING      ([<Company>$Detailed Cust_ Ledg_ Entry].[Entry Type] <> 2)