If you have header discount in OINV, this percentage is applied to all rows in INV1. This is how I got the exact result as system Sales Analysis (not showing full query):
SUM(CASE WHEN T1."CANCELED" ='C' THEN T0."LineTotal"*-1 ELSE T0."LineTotal" END)*(1-(IFNULL(T1."DiscPrcnt", 0)/100))
AS "Net revenue"
FROM INV1 T0 FULL OUTER JOIN OINV T1 ON T0."DocEntry" = T1."DocEntry" FULL OUTER JOIN OITM T2 ON T0."ItemCode" = T2."ItemCode"
UNION ALL
SUM(CASE WHEN T5."CANCELED" ='C' THEN T4."LineTotal" ELSE T4."LineTotal"*-1 END)*(1-(IFNULL(T5."DiscPrcnt",0)/100))
FROM RIN1 T4 FULL OUTER JOIN ORIN T5 ON T4."DocEntry" = T5."DocEntry" FULL OUTER JOIN OITM T6 ON T4."ItemCode" = T6."ItemCode"
Using full join is useful if you want to get the same results as Sales Analysis by custumers (using inner join shows the same results as SA by items). Also, you need to add correction invoice and correction invoice reversal for exact results.
Add comment