Skip to Content
avatar image
Former Member

VAT Number must not be changed on an AR invoice with SBO_SP_TransactionNotification

Dear Experts!

I would like to disable any users to change the VAT Number on an AR invoice if it is printed and I would like to use the SBO_SP_TransactionNotification stored procedure to do it. I wrote a simple query and it works fine if I only edit the field, but if I delete the whole VAT Number or if I write something instead of the empty (NULL) value I can still update it. My query is:

if @object_type = '13' and @transaction_type = 'U'

BEGIN
SET ANSI_NULLS OFF
    IF
    (
		select LicTradNum
		from OINV
		where (Printed = 'Y'
		and DocEntry = @list_of_cols_val_tab_del)
    )
		<>
	(
		select LicTradNum
		from ADOC
		where (DocEntry = @list_of_cols_val_tab_del
		and LogInstanc = (select max(LogInstanc) from ADOC where DocEntry = @list_of_cols_val_tab_del))
	)

        SET @error = 1
        SET @error_message = 'VAT Number cannot be changed'
END

If I try to Update the invoice the following should happen:

  1. Let's say there is an X in the OINV.LicTradNum
  2. I delete it so on the form there will be NULL value
  3. I click Update
  4. X goes to ADOC with the latest LogInstanc
  5. SBO_SP_TransactionNotification is run and it compares X in ADOC and NULL on the form
  6. Because of the ANSI_NULLS OFF it will be true that X <> NULL
  7. There should be the error message and ROLLBACK but it updates the document

Any idea why?

Thank you,
Bálint

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Dec 20, 2016 at 01:09 PM

    Hi Bálint,

    Try this:

    if @object_type = '13' and @transaction_type = 'U'
    
    
    BEGIN
    IF
    (
    	ISNULL((select LicTradNum
    	from OINV
    	where (Printed = 'Y'
    	and DocEntry = @list_of_cols_val_tab_del)), '')
    )
    	<>
    (
    	ISNULL((select LicTradNum
    	from ADOC
    	where (DocEntry = @list_of_cols_val_tab_del
    	and ObjType = 13
    	and LogInstanc = (select max(LogInstanc) from ADOC where DocEntry = @list_of_cols_val_tab_del))), '')
    )
    
    
        SET @error = 1
        SET @error_message = 'VAT Number cannot be changed'
    END
    
    

    Hope it helps,

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded