Hi Experts,
Need your help on the below query. I am trying to recreate the SAP Business One Sales Analysis Report by Items as there are additional fields that need to be presented in the report. Amount pulled out was Sales Amount & Quantity should be exact and same one generated by SAP default Sales Analysis Report, query below is getting me the Total Sales Amount correct but not the quantity as it's completely different than the one in the default report:
SELECT T0."DocEntry",T0."DocNum", T0."CANCELED", T0."ObjType",T0."GrosProfit", T0."DocDate",T0."DiscSum", T0."DocStatus", T0."DiscPrcnt", T2."SlpCode", T2."SlpName", T3."BPLName", T3."BPLId", T1."ItemCode",T1."TaxOnly", T1."NoInvtryMv", T1."InvQty", T1."Dscription", T6."GroupName",
Case
when T0."CANCELED" = 'C' then (T1."Quantity"*-1) else T1."Quantity" End as "Quantity",
Case
When T0."CANCELED" = 'C' then (T1."LineTotal" *-1) else T1."LineTotal" End as "Line Total" , T4."CardType",
Case
When T0."CANCELED" = 'C' then (T1."GTotal"*-1) else T1."GTotal" End as "Gross Total" ,
Case
When T0."CANCELED" = 'C' then (T1."GrssProfit"*-1) else T1."GrssProfit" End "Gross Profit" ,
T1."PriceBefDi", T5."ItmsGrpCod", T0."CardCode", T0."CardName" FROM OINV T0 INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OSLP T2 ON T0."SlpCode" = T2."SlpCode" INNER JOIN OBPL T3 ON T0."BPLId" = T3."BPLId" INNER JOIN OCRD T4 ON T0."CardCode" = T4."CardCode" INNER JOIN OITM T5 ON T1."ItemCode" = T5."ItemCode" INNER JOIN OCRG T6 ON T4."GroupCode" = T6."GroupCode"
Union All
(SELECT T0."DocEntry",T0."DocNum", T0."CANCELED", T0."ObjType",T0."GrosProfit", T0."DocDate",T0."DiscSum", T0."DocStatus", T0."DiscPrcnt",T2."SlpCode", T2."SlpName", T3."BPLName", T3."BPLId", T1."ItemCode",T1."TaxOnly",T1."NoInvtryMv", T1."InvQty", T1."Dscription", T6."GroupName",
Case
when T0."CANCELED" <> 'C' then (T1."Quantity"*-1) else T1."Quantity" End as "Quantity",
Case
When T0."CANCELED" <> 'C' then (T1."LineTotal" *-1) else T1."LineTotal" End as "Line Total" , T4."CardType",
Case
When T0."CANCELED" <> 'C' then (T1."GTotal"*-1) else T1."GTotal" End as "Gross Total" ,
Case
When T0."CANCELED" <> 'C' then (T1."GrssProfit"*-1) else T1."GrssProfit" End "Gross Profit" ,
T1."PriceBefDi", T5."ItmsGrpCod", T0."CardCode", T0."CardName" FROM ORIN T0 INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OSLP T2 ON T0."SlpCode" = T2."SlpCode" INNER JOIN OBPL T3 ON T0."BPLId" = T3."BPLId" INNER JOIN OCRD T4 ON T0."CardCode" = T4."CardCode" INNER JOIN OITM T5 ON T1."ItemCode" = T5."ItemCode" INNER JOIN OCRG T6 ON T4."GroupCode" = T6."GroupCode")