Introduction

Sometimes it is necessary to block transactions in SAP. For example manual bookings to the inventory account, or a wrong currency in a price list and so on. Because there are many ways to modify data in SAP (UI, AddOn, B1i, other connectors), the one and only way to validate data is the SAP transaction notification.

There will be 2 parts about this topic:

• SAP Business One Transaction Notification Part 1 (this post)
  How the transaction notification works, How to build a debug table
• SAP Business One Transaction Notification Part 2
  Example for a price list validation
• SAP Business One Transaction Notification Part 3
  Example for a validation on multiple sales documents

What is Transaction Notification

Every SAP action, which results in a modification of the data base is logged in the transaction notification. The notification contains parameters, which can be read out. The transaction notification can also return values (true or false).

To see the transaction notification, please log in into the SQL Server management Studio and browse to this path “Databases > [Your Database] > Programmability > Stored Procedures > SBO_SP_TransactionNotification“. Then you need to use Right Click and Modify.

In the Editor, you are able to see the default content of the transaction notification. There are some input variables and output variables.

Input Variables

• @object_type: Object type which invokes the transaction notification
• @transaction_type: Type of transaction like Add or Update
• @num_of_cols_in_key: How many columns are in the key, usually 1
• @list_of_key_cols_tab_del: Name of the key
• @list_of_cols_val_tab_del: Value of the key

Output Variables

• @error: Error Code
• @error_message: Error Message

Setup Debug

To see, how the transaction notification works, we do a little debug environment. We create a user table with all the relevant columns and insert the input parameters of the transaction notification into the UDT.

Create UDT

Create a new UDT called TRANSACTIONDEBUG

Next step is the creation of the columns via adding new user defined fields to the user table. Please create and name the user defined fields exactly as shown in the following print screen.

Modify Transaction Notification

Next step is to modify the transaction notification. You are only allowed to put your own code between the lines which the comment “ADD YOUR CODE HERE”. Please copy the following code snippet between those lines and click F5 to update the transaction notification.

Declare @Counter AS INT
SET @Counter = ISNULL((SELECT MAX(CAST(Code AS INT)) FROM [@TRANSACTIONDEBUG]), 0) + 1

INSERT INTO [@TRANSACTIONDEBUG]
(Code, Name, U_ObjectType, U_TransactionType, U_NumOfCols, U_ListOfKey, U_ListOfCols)

SELECT @Counter, @Counter, @object_type, @transaction_type, @num_of_cols_in_key, @list_of_key_cols_tab_del, @list_of_cols_val_tab_del

Now, the transaction notification fills our UDT with the input parameters.

Create query

Create a query and save it into the query manager

SELECT * FROM [@TRANSACTIONDEBUG]

Result

Now, please add or modify some documents in SAP and then run the debug query. As you see, every transaction is logged.

Because there are so many transaction notifications, the data in this table will increase very fast. You can delete the data from time to time with following query:

DELETE FROM [@TRANSACTIONDEBUG]

Also important: When you are not debugging, please comment the part of the transaction notification. You can use /* before and */ after the code snippet you implemented.

Next step

In my next post (Part 2), I will show, what you can do with a transaction notification using a small example.