Introduction
SAP Business ONE offers two different entry points at DB level (on both HANA and SQL) for implementing custom logic when transactions are being passed over to the ERP.
The purpose of Transaction Notifications is to enable the partner/customer to receive notification and register logs on any transaction taking place within the SAP Business One database. SAP Business One provides two stored procedure mechanisms for receiving notification of data-driven events.
- SBO_SP_TransactionNotification
- SBO_SP_PostTransactionNotice
SBO_SP_TransactionNotification is triggered before the SAP Business One transaction is committed. It can be used to roll back transactions and display custom error messages in the SAP Business One Client.
SBO_SP_PostTransactionNotice is triggered after the SAP Business One transaction is committed.
Depending on the Business Object and the operation performed, there could be multiple notification procedures triggered in a sequence. For example, when you update an item code, both notifications will be triggered for every warehouse associated to the item code and also for the item code itself.
The guidelines below should be followed when building the logic in the notification procedures.
- Do not change ISOLATION LEVELS within the procedures.
- Do not COMMIT/ROLLBACK transactions within the procedure.
- Do not directly update/delete/insert SAP Business One or Boyum products tables within the procedures.
- Do not use long, time-consuming logic because it may introduce performance issues.
More information on the use of the SAP Notification procedures can be found at this SAP note (nr 1320484):
In particular, concerning point 4 mentioned above, here's some additional guidelines we would like to provide as Boyum when using any of our products:
SBO_SP_PostTransactionNotice
the Boyum code in the SAP post transaction procedure is normally presented as follows:
----START: Boyum Solutions BOY_CLOUD (Boyum Cloud Installer) event notifications
EXEC BOY_SP_CLOUD_POSTTRANSACTIONNOTICE
@object_type = @object_type,
@transaction_type = @transaction_type,
@num_of_cols_in_key = @num_of_cols_in_key,
@list_of_key_cols_tab_del = @list_of_key_cols_tab_del,
@list_of_cols_val_tab_del = @list_of_cols_val_tab_del,
@error = @error OUTPUT,
@error_message = @error_message OUTPUT
----END: Boyum Solutions BOY_CLOUD (Boyum Cloud Installer) event notifications
The Boyum custom code should not be modified in any way and should always be the first one, on top and before any additional custom code you might decide to write.
SBO_SP_TransactionNotification
the Boyum code on the SAP transaction notification is normally presented as follows:
-- ADD YOUR CODE HERE
-- beasarea
IF @error = 0 BEGIN
BEGIN TRY
EXECUTE [dbo].[beas_SP_TransactionNotification] @object_type, @transaction_type, @num_of_cols_in_key, @list_of_key_cols_tab_del, @list_of_cols_val_tab_del, @error OUTPUT, @error_message OUTPUT
END TRY
BEGIN CATCH
SET @error = ERROR_NUMBER()
SET @error_message = ERROR_MESSAGE()
END CATCH;
END
-- /beasarea
The Boyum custom code should not be modified in any way and should always be the first one, on top and before any additional custom code you might decide to write.
In case you're running BOTH beas and WMS in your installation, make sure that the SBO_SP_TransactionNotification looks as follows:
--**********************************************************************************************************
--Start executing Produmex Logex Addon code
--**********************************************************************************************************
IF @error = 0 BEGIN
BEGIN TRY
EXEC [dbo].[PMX_SP_TransactionNotification]
@object_type,
@transaction_type,
@num_of_cols_in_key,
@list_of_key_cols_tab_del,
@list_of_cols_val_tab_del,
@error = @error OUTPUT,
@error_message = @error_message OUTPUT
END TRY
BEGIN CATCH
SET @error = ERROR_NUMBER()
SET @error_message = ERROR_MESSAGE()
DECLARE @msg as NVARCHAR(255)
SET @msg = SUBSTRING('PMX_SP: sql error ' + CAST(ERROR_NUMBER() AS NVARCHAR) + ' : ' + ERROR_MESSAGE()
+ ISNULL( ' line ' + CAST(ERROR_LINE() AS NVARCHAR), '' ) + ISNULL( ' in ' + ERROR_PROCEDURE(), '' ),1,255)
EXEC xp_logevent 999999, @msg, ERROR
END CATCH;
END
--**********************************************************************************************************
--End executing Produmex Logex Addon code
--**********************************************************************************************************
-- beasarea
IF @error = 0 BEGIN
BEGIN TRY
EXECUTE [dbo].[beas_SP_TransactionNotification] @object_type, @transaction_type, @num_of_cols_in_key, @list_of_key_cols_tab_del, @list_of_cols_val_tab_del, @error OUTPUT, @error_message OUTPUT
END TRY
BEGIN CATCH
SET @error = ERROR_NUMBER()
SET @error_message = ERROR_MESSAGE()
END CATCH;
END
-- /beasarea
The Boyum custom code should appear in the same sequence as shown above (the Produmex part on top with the beas one immediately after it), should not be modified in any way and should always be the first one, on top and before any additional custom code you might decide to write.
SBO_SP_PostTransactionNotice & SBO_SP_TransactionNotification
- If you're implementing complex logic at DB level, always use custom stored procedures and call them with a TRY / CATCH syntax for making sure you have the best possible performance and always return errors from your custom stored procedure to the main SAP procedure.
- if you're are checking any SAP object type (for ex. object 17, sales orders) for any condition / data constraint make sure you execute your code for object type 17 ONLY ONCE. The code in the SAP transactions is executed sequentially (top/down) and multiple instances of your code for object type 17 could mean that for every single update/creation of a sales order it will be run those many times (if not more when the logic is over a line of a document) with severe degradation of performance on the whole system landscape.
- try to avoid code blocks that create temporary tables with inserts / updates
- do not perform any insert / update on any of the SAP tables / Boyum tables. More info here on the SAP policy on data integrity . Boyum policy is that you're not allowed to do any sort of update / insert on any of the Boyum products database tables / entities.
- do not use CURSORS as this might result in serious performance degradation. More info here: https://blogs.sap.com/2020/02/26/performance-why-you-should-avoid-using-cursors/
Final notes
The Transaction Notification stored procedures are provided "as is" and the support SLAs do not apply (neither at SAP or at Boyum IT) for the custom logic you have implemented in the procedures . If you're having an issue with any of the Boyum products, please make sure you have tested your business process WITH NO CUSTOM CODE in the SAP Transactions other than the one supplied by Boyum when the add-on is installed.
Comments
0 comments
Please sign in to leave a comment.