How to Block AR Invoice Based on Due Date and Counter Date

[mervz02]

Hi Experts,
I am about to do a simple task in my organization to block creating or updating or saving AR Invoices when due date and counter date is more than Customers Payment Terms + 10 Days. i have created simple Transaction Notification it fails to block the transaction. Here my code 

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

begin

IF Exists (SELECT T0.[DocEntry] FROM

OINV T0 INNER JOIN OCTG T1 ON T0.[GroupNum] = T1.[GroupNum]

WHERE DATEDIFF(DAY,T0.[U_CounterDate],T0.[DocDueDate]) > (T1.[ExtraDays] + 10) and T0.Docentry= @List_of_cols_val_tab_del )

Begin

SET @error = 1001

SET @error_message = 'You Cannot not add Counter date more than 10 Days. Please See Administrator!'

End

End

[clas_hortien]

Hello,

you can control this by creating a instead-of-trigger on the tables that the invoices are inserted into.

CREATE TRIGGER (Transact-SQL) - SQL Server | Microsoft Learn

Using INSTEAD OF triggers in SQL Server for DML operations (mssqltips.com)

Best regards

Clas