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