Skip to Content
0
Former Member
May 30, 2008 at 06:44 AM

Stored procedure

23 Views

Hi

My client wanted a solution where a user will be prevented/warned from ordering stock if the on hand qty + order qty exceeds the maximum stock level.

I did a stored procedure and to allow the user to override the error code I created a UDF with Yes/No (default = 'No') values on row level.

IF @transaction_type IN ('A','U') AND @object_type = '22'

BEGIN

IF EXISTS(SELECT T0.ITEMCODE FROM [DBO].[POR1] T0 INNER JOIN [DBO].[OITM] T1

ON T1.ITEMCODE = T0.ITEMCODE INNER JOIN [DBO].[OITW] T2 ON T2.ITEMCODE = T1.ITEMCODE

WHERE T0.DOCENTRY = @list_of_cols_val_tab_del AND T2.OnHand+T0.Quantity > T2.MaxStock AND T0.U_MaxStock = 'No')

BEGIN

SELECT @Error = 1, @error_message = 'In Stock Qty + PO Qty exceeds Max Level'

END

END

The client is satisfied with this solution but would like the itemcode and/or row no to appear in the error message since they do purchase orders with many lines.

Is it possible to achieve this with a stored procedure?

Regards