Skip to Content
0

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

Dec 20, 2016 at 12:54 PM

28

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
DIEGO LOTHER Dec 20, 2016 at 01:09 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Dear Diego!

Thank you, it's working fine now :)

1