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? 

[SonTran · Accepted solution]

Hi,

try this

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

BEGIN

IF Exists (SELECT T0.CardCode FROM ORDR T0 JOIN OINV T1 ON T0.CardCode=T1.CardCode

WHERE T0.DocEntry = _of_cols_val_tab_del AND T1.[DocStatus] = 'O' AND T1.CANCELED='N' )

BEGIN

SET  = 1001

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

End

End

Hope this helps,

SonTran

[mervz02 · Accepted solution]

Hi SonTran

[mervz02 · Accepted solution]

Hi, Can you explain this code whats the difference from my code aside from connection into INV1? i tried the code it doesnt work

[SonTran · Accepted solution]

Somehow my above store missed "@list". Please correct it and try again: 

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

[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