Menu

Query to block posting a GRPO entry with quantity more than the quantity on the Purchase Order

When posting a Good Receipt Purchase Order (GRPO), Users may post GRPO with more quantity than the quantity on the Purchase Order. To prevent this, use the query below to block posting the entry. The system will generate an error message to warn the user.

Follow the path to post the query:

Login to Microsoft SQL Server Management Studio

Expand Databases.

  1. Expand Your Database in this case (SVTL2022).
  2. Expand Programmability.
  3. Expand Stored Procedures.
  4. Scroll down to dbo.SBO_SP_TransactionNotification.
  5. Right Click on dbo.SBO_SP_TransactionNotification
Query
SBO_SP_TransactionNotification
  1. Click Modify
  2. Paste Query in the section indicated above “Insert your query here”
  3. Click Execute

 

—–GRPO Qty cannot be more than PO

 

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(T1.Quantity)) or sum(t0.quantity) > sum(t0.BaseOpnQty))

Begin

Select @Error = 10, @error_message = ‘GRPO quantity is greater PO quantity’

End

End

Tagged