Skip to Content

Alert if GRN Quantity is greater than PO quantity

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.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    Jun 26, 2018 at 10:08 AM

    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))

    Add comment
    10|10000 characters needed characters exceeded

    • 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))

  • Jun 25, 2018 at 09:16 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Jun 26, 2018 at 10:58 AM

    Thanks Abdul its working. Appreciate for your time :)

    Add comment
    10|10000 characters needed characters exceeded