Alert - for Gross Profit Calculation with Invoice Documents

[Former Member]

How create an alert when the Gross profit % is below 5% in invoice document base on each line?

[kothandaraman_nagarajan]

Hi,

Try this query for row level GP. Let me know your feedback

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[GrssProfSC], T1.[Price], case when T3.[AvgPrice] = '0' then 100 else
(T1.[GrssProfSC] /  T1.[Price] ) * 100 end as ' GP%%' FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode WHERE T0.[cardcode]  =[%0] GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription],T1.[Price],T1.[GrssProfSC],T3.[AvgPrice]

having (case when T3.[AvgPrice] = '0' then 100 else
(T1.[GrssProfSC] /  T1.[Price] ) * 100 end ) <= 5

Thanks & Regards,

Nagarajan

[Former Member]

I have below query for alert in sales order, but i need for invoice and in line level calculate.

SELECT T0.DocNum, T0.CardCode, T0.DocDate, T0.DocTotal, T0.GrosProfit, (T0.GrosProfit/T0.DocTotal) * 100 'Profit%'

FROM ordr T0

WHERE (T0.GrosProfit/T0.DocTotal < 0.05)

[kothandaraman_nagarajan]

Hi,

How did you calculate Gross profit for row level? Is there is any direct field to capture Gross profit %?

Thanks & Regards,

Nagarajan

[Former Member]

Base on item master cost price list and invoice item selling price.

[Former Member]

hi

go to Administration --->Alerts Management

& search system default Alert for 'Deviation from % of Gross Profit'

set Profit Percentage & Documents on which alert to be generated

alternatively you can create query base approval process

regards,

raviraj

[Former Member]

Default system alert for 'Deviation from % of Gross Profit' cannot set each item line gross profit in invoice