I have had a requirement to match the numbers generated by the standard sales analysis report in a hana query, i have done that a lot before but this time i just can't match it. There is always a slight difference missing which is the header discount in OINV.
Usually when i need to group on item level i just get the Price from OINM and the quantity from the invoice rows and it works perfectly, in this system there are items that are basically service items so they don't appear in OINM as there was no inventory movement on them.
So i was wondering how does the standard sales analysis report calculate the price of an item after the header discount? if i do that in a query and group by item code the header DiscSum will just get duplicated because of the row numbers, and if i get max disc sum then i can't group by item code.
Also the system has cancellation invoices so i have to subtract those from the query.
Here is my query that matches the analysis report without item grouping:
SELECT F0."CardName", sum("Total") AS "Total"
FROM
(
SELECT
T0."CardName", T0."DocNum",
CASE WHEN T0."CANCELED" = 'C' THEN -(SUM("LineTotal")-MAX("DiscSum")) else
CASE WHEN T0."CANCELED" <> 'C' THEN (SUM("LineTotal")-MAX("DiscSum")) end end as "Total"
FROM OINV T0
JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
GROUP BY T0."CardName", T0."DocNum", T0."CANCELED"
UNION ALL
SELECT
T0."CardName", T0."DocNum",
CASE WHEN T0."CANCELED" = 'C' THEN (SUM("LineTotal")-MAX("DiscSum")) else
CASE WHEN T0."CANCELED" <> 'C' THEN -(SUM("LineTotal")-MAX("DiscSum")) end end as "Total"
FROM ORIN T0
JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"
JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
GROUP BY T0."CardName", T0."DocNum", T0."CANCELED"
) F0
GROUP BY F0."CardName"
Any help would be appreciated.
Thanks,
Ashraf
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.
Isn't the discount on rows and header separate? because i have seen cases where there is like 8% discount on the invoice but all the line discounts are at 0%.
Row discount is already included in line total. You just need to apply header discount on line total amount to get correct revenue.
Yes that is exactly my question, how do i apply the header discount on the rows without duplication?
Say i have an invoice with 10 rows, how would you write a query to calculate INV1.LineTotal - OINV.DiscSum without duplicating DiscSum 10 times?
Hi Ashraf,
The query Hrvoje provided is applying the discount percentage ("OINV.DiscPrcnt") to the line amount, instead of subtracting the header discount dollar amount ("OINV.DiscSum") at each line. So give it a try.
Cheers,
Jimmy
aaah my bad i thought he meant that the header discount is the same as the row discount, i'll give this a shot in my case and see if it works, thanks a lot.