Alert if GRN Quantity is greater than PO quantity

[former_member574008]

Hi Gurus,

Can anyone help me to make a alert if GRPO Or AP Invoice Quantity is greater than PO quantity.

I have SP Notification for the same.

But my requirement is not to block GRPO or AP quantity if the quantity is greater than PO quantity but to send a alert to a user.

[Abdul · Accepted solution]

Use Query Only Not Complete SP like Below Because Alert run the select query and if there is any results it will send to users

try the below query

SELECT T0.BaseEntry, SUM(T0.Quantity)

FROM [dbo].[PDN1] T0 INNER JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = T0.BASEENTRY

WHERE T0.BaseType = 22 AND T0.ItemCode = T1.ItemCode

AND T0.BaseLine = T1.LineNum

GROUP BY T0.BaseEntry

HAVING (SUM(T0. Quantity) > SUM(T0.BaseOpnQty))

[former_member601069 · Accepted solution]

Abdul Mannan, Please help me how to convert this Below Sp Validation In Sap B1 Hana Studio

SELECT T0.BaseEntry, SUM(T0.Quantity)

FROM [dbo].[PDN1] T0 INNER JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = T0.BASEENTRY

WHERE T0.BaseType = 22 AND T0.ItemCode = T1.ItemCode

AND T0.BaseLine = T1.LineNum

GROUP BY T0.BaseEntry

HAVING (SUM(T0. Quantity) > SUM(T0.BaseOpnQty))

[former_member574008]

Thanks Abdul its working. Appreciate for your time 🙂

[Abdul]

if your problem is solved please close the thread

[Abdul]

Use the same query you used in SP and Make alert based on same query with frequency of 1 minute query will run each minute and will check if there is any Results and alert will be generated

[former_member574008]

Hi Abdul, Thanks for the reply,

I tried the SP Notification query and made alert with freqvency 1 minute but it did not worked for me,

This is the query which i used

IF @transaction_type IN (N'A', N'U') AND

(@Object_type = N'20')

begin

if exists (SELECT T0.BaseEntry, SUM(T0.Quantity)

FROM [dbo].[PDN1] T0 INNER JOIN [dbo].[POR1] T1 ON T1.DOCENTRY = T0.BASEENTRY

WHERE T0.BaseType = 22 AND T0.ItemCode = T1.ItemCode

AND T0.BaseLine = T1.LineNum

and T0.DOCENTRY = @list_of_cols_val_tab_del

GROUP BY T0.BaseEntry

HAVING (SUM(T0. Quantity) > SUM(T0.BaseOpnQty)))

begin

select @Error = 10,

@error_message = 'GRPO quantity is Greater than the PO Quantity'

end

end