Skip to Content
0
Former Member
Oct 17, 2017 at 02:02 AM

SAP B1 QUERY ITEM COST NOT MATCH WITH GROSS PROFIT

867 Views

Hi Experts,

I really need your help I've created a query that can generate item Sales Analysis Per Item Category with my clients Requirements but It seem I got a Problem with my query LineTotal - ItemCost is not match with the gross profit.But the summary of my gross profit query is match in the sales analysis. I think there's wrong with my RowTotal or ItemCost but I don't know where. hmmmmm Can you help me with it? Here's my Query Below thanks in advance.

SELECT DISTINCT 'Invoice' As DocType,(T1.ItemCode), T1.Dscription,SUM(T1.Quantity) AS 'QNTY',T2.[SalUnitMsr] AS 'UoM',SUM(T1.LineTotal)as 'Amount',SUM( T1.Quantity *T1.[StockPrice] ) AS 'Item Cost',SUM(T1.GrssProfit ) as 'Gross Profit',T2.[U_JTEC_SUB_PRINCIPAL],Case WHEN Sum(Isnull(T1.LineTotal,0)) = 0 THEN 0 ELSE SUM(IsNUll(T1.GrssProfit ,0))/Sum(isnull(T1.LineTotal,0)) * 100 END 'Gross Profit %'


FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]


WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] BETWEEN [%0] AND [%1]


GROUP BY T1.ItemCode, T1.Dscription,T2.[SalUnitMsr],T2.[U_JTEC_SUB_PRINCIPAL]

UNION ALL


SELECT DISTINCT 'Return' As DocType,(T1.ItemCode), T1.Dscription,-SUM(T1.Quantity) AS 'QNTY',T2.[SalUnitMsr] AS 'UoM',-SUM(T1.LineTotal)as 'Amount',-SUM( T1.Quantity *T1.[StockPrice] ) as 'Item Cost',-SUM(T1.GrssProfit ) as 'Gross Profit',T2.[U_JTEC_SUB_PRINCIPAL],Case WHEN Sum(Isnull(T1.LineTotal,0)) = 0 THEN 0 ELSE SUM(IsNUll(T1.GrssProfit ,0))/Sum(isnull(T1.LineTotal,0)) * 100 END 'Gross Profit %'

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]


WHERE T0.[CANCELED] = 'N' AND T0.[DocDate] BETWEEN [%0] AND [%1]


GROUP BY T1.ItemCode, T1.Dscription,T2.[SalUnitMsr],T2.[U_JTEC_SUB_PRINCIPAL]