Blocking of Sales Order thru SP Transaction Notification

[mervz02]

Hi Experts,

Im about to block Sales Order thru SP Notification, I Create a simple code to check if the customer has Open Invoices in the AR Module and if it does, it will block the sales order for that particular order unless the AR Document of the previous transaction is closed here is my code.

IF(@object_type = '17') and (@transaction_type in ('A', 'U'))

BEGIN

IF Exists (SELECT T0.CardCode FROM OINV T0

WHERE T0.DocEntry = @List_of_cols_val_tab_del AND T0.[DocStatus] = 'O' AND T0.CANCELED='N' )

BEGIN

SET @error = 1001

SET @error_message = 'You have Pending AR invoice Open, Please Settle Customer Account. Thank you!'

End

End

Apparently the code block Sales Order for all Customers even they dont have AR Invoice Open Documents.

what you think is the problem? anyone can help me? 

[a_grootens]

Try this

IF(@object_type = '17') and (@transaction_type in ('A', 'U'))

BEGIN

DECLARE @Customer NVARCHAR(50)
DECLARE @check INT
SET @Customer = (SELECT CardCode FROM OINV T0 WHERE T0.DocEntry = @List_of_cols_val_tab_del)
SET @check = (SELECT COUNT(T0.Docentry) FROM OINV T0 WHERE T0.CardCode = @Customer AND T0.[DocStatus] = 'O' AND T0.CANCELED='N' )
IF @check > 1 
    BEGIN
    SET @error = 1001
    SET @error_message = 'You have Pending AR invoice Open, Please Settle Customer Account. Thank you!'
    END
END

Cheers Andy