Skip to Content
0
Jun 07, 2020 at 04:13 PM

Warehouse Wise Sale Analaysiscs With AR Credit Note

83 Views Last edit Jun 08, 2020 at 02:11 AM 3 rev

Hi,

I am trying to create a query showing warehouse wise Sales with GP for each warehouse. This was resolved in an earlier question but I realised the sales numbers did not include AR Credit note. I tried adding ORIN/RIN1 to this query format but unsuccessful. Can anyone help to add the fix for CN?. Thanks.

SELECT

T1.WHSCODE AS "POS Code",T2.WHSNAME AS "POS Name" , SUM (T1.GTotal) AS "Gross Sales ( + IVA )", SUM (T1.LineTotal) AS 'Net Sales ( - IVA )', SUM (T1.LineVat) AS "IVA Value", SUM ( ( T1.LineVat)/ ( T1.LineTotal)) As "% IVA", SUM (T1.GPTtlBasPr) AS "Stocks Value", SUM (T1.GrssProfit) AS "Gross Profit", SUM(ISNULL(T1.GrssProfit,0))/SUM(ISNULL(T1.LineTotal,1) )*100 As "% GP", SUM (T0.DiscSum) AS "Discount",COUNT(T1.LineTotal) AS 'Sale In SKU', SUM (T1.quantity) AS 'Sale In Qty',SUM (T1.Weight1) AS "Sale In KG", SUM (T1.Volume) AS "Sale In Mtr"

FROM OINV T0

LEFT JOIN INV1 T1 ON T0.DOCENTRY = T1.DOCENTRY

LEFT JOIN OWHS T2 ON T1.WHSCODE = T2.WHSCODE

WHERE

T0.DocDate BETWEEN [%1] and [%2]

GROUP BY

T1.WHSCODE,T2.WHSNAME