An Alert required for Purchaser if he exceeds the max qty of an item

[reno1]

Dear All,

An alert is required to be created so that this alert pops up whenever a purchaser is creating a PO which Involves an Item crossing its maximum inventory level ......how can this be done ?

Note :

this should also consider the available stock into consideration and the Minimum and Maximum Inventory is already set against the default warehouse .

[Former Member]

Hi,

You may try this user alert query:

SELECT T2.DOCNUM, T0.ITEMCODE, T0.OpenQty, T3.MaxStock

FROM DBO.POR1 T0

inner join oitm t1 on t1.itemcode = t0.itemcode

INNER JOIN DBO.OPOR T2 ON T0.DOCENTRY = T2.DOCENTRY

inner join oitw t3 on t3.itemcode = t1.itemcode and t3.whscode = t0.whscode

WHERE T0.LINESTATUS = 'O' AND DateDiff(DD,T2.DOCDate, GetDate()) = 0 AND (T3.OnHand+T0.OpenQty > T3.MaxStock)

Thanks,

Gordon

[former_member206437]

Hi

If u PopUps the Alert than also the Purchaser can go ahead and place the PO.

I feel make user to not allow to post the PO when inventory is having Maximum stock by SP Transation.

Try with SP Transaction...

IF @transaction_type IN ('A','U') AND @object_type = '22'
BEGIN
IF EXISTS(SELECT T0.ITEMCODE FROM DBO.POR1 T0 inner join oitm t1 on t1.itemcode = t0.itemcode
INNER JOIN DBO.OPOR T2 ON T0.DOCENTRY = T2.DOCENTRY inner join oitw t3 on t3.itemcode = t1.itemcode and t3.whscode = t0.whscode
WHERE T2.DOCSTATUS = 'O' AND T0.LINESTATUS = 'O' AND T0.DOCENTRY = @list_of_cols_val_tab_del AND (T3.OnHand+T0.OpenQty) > T3.MaxStock)
BEGIN
SELECT @Error = 1, @error_message = 'Onhand Quantity + PO Quantity exceeds Max Quantity'
END
END

Giri

Edited by: Giri dharan on Jun 4, 2010 12:59 PM