Skip to Content
0
Former Member
Feb 25, 2009 at 01:18 PM

Stored Procedure - Block Negative Quantity by Warehouse

278 Views

Dear Expert,

I need to block a Goods Issue when the quantity on a specific warehouse falls below 0. I am not 'Managing Stock by Warehouse' for various reasons.

I have used the following Stored Procedure. This works fine but when the Warehouse Onhand Quantity - Quantity to Issue = 0 the SP still kicks in. I am suspecting that the T0.OnHand value is not pulling through correctly, because when I change this to a static value the SP allows the transaction.

--Block Negative Goods Issue per Warehouse

IF @transaction_type = 'A' AND @object_type = '60'

BEGIN

IF EXISTS (SELECT T0.DocNum FROM [dbo].[OIGE] T0

INNER JOIN [dbo].[IGE1] T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN [dbo].[OITW] T2 ON T1.ItemCode = T2.ItemCode

WHERE T2.WhsCode = T1.WhsCode

AND T2.OnHand - T1.Quantity < 0

AND T0.DocEntry = @list_of_cols_val_tab_del)

BEGIN

SELECT @error = 2, @Error_Message = 'Stock falls into negative for this warehouse.'

END

END

Please would you be so kind to scan your eyes over this, as I haven't had any success.

Best regards,

Adriaan