Skip to Content
Author's profile photo Rafaee Mustafa

SBO_SP_TransactionNotification

The SP_TransactionNotification is to receive notification on any transaction – in other words to do some validation in SAP Business One and prevent the user from doing an action if the validation condition fails. This stored procedure(SP) is created automatically with each database.This stored procedure is fired whenever operations are performed on business objects like document or master records and User-Defined Objects.

 

The SBO_SP_TransactionNotification stored procedure is created automatically when new company is created in SAP Business One.

 

Modify.jpg

 

Below are the parameters:

 

Parameter Description
@objecttype returns a string representing the type of object which is a numerical value
@transaction_type

It may have values of:

  • A (where a record was added)
  • U (where a record was updated)
  • D (where a record was deleted),
  • C (where a document was canceled)
  • L (where a document was closed)
@list_of_cols_val_tab_del This returns a tab-delimited list of values required to retrieve the object from B1.
@error a integer value shows the number
@error_message message needs to be show as a message to user

 

please make sure you always go through the “@error, @error_message” at the end, especially in the case where you want the transaction to be considered as successful. Without the return of these 2 values, SAP Business One will consider the transaction as failed.

 

Adding Code to the Stored Procedure

Look at a sample code that uses it. The SBO_SP_TransactionNotification stored procedure should be modified to include the following Transact-SQL code. Add the following text to the section of the stored procedure that says “Add Your Code Here”

 

addCode.jpg

 

Sample Code

 

SP.JPG

 

Sample Code for multiple notifications

 

Only the query needs to be change according to your need.

 

Disabling\Comment Code

Comment within SQL statements do not affect the statement execution. You can comment statement in to ways.

In line comments < — > or block comment < /*  */>

 

Sample Code

 

/wp-content/uploads/2015/01/comm_674183.png

 

Difference between PostTransactionNotice and TransactionNotification procedure

 

The only differences being when they are triggered and the ability to use the @error functionality of SBO_SP_TransactionNotification to rollback transactions. SBO_SP_PostTransactionNotice can only be triggered at the end of an action (transaction committed). Most users only use SP_TransactionNotification because it can be used to verify data to block unwanted posting.

 

 

The following documentation is available:

 

TransactionNotification of SAP Business One HANA

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo AndakondaRamudu A
      AndakondaRamudu A

      hi  Mustafa

      Thanks for shared about SP here

      Author's profile photo Ngoc Nguyen Thanh
      Ngoc Nguyen Thanh

      Hi Mustafa
      I want to many SBO_TransactionNotification in sap b1. Can you explain me how to create many SBO_TransactionNotification at once time ?

      Thanks.

      Author's profile photo Rafaee Mustafa
      Rafaee Mustafa
      Blog Post Author

      Hello Ngco,

      SP_Transaction would be only one, you can insert your SQL code for many documents with proper syntax and terminators .

      On your request updating this blog with multiple Notifications on single SP_transaction.

      Regards,

      Author's profile photo Ngoc Nguyen Thanh
      Ngoc Nguyen Thanh

      Hi Rafaee Mustafa,
      Thanks so much. It's so usefull

      Regards.

      Author's profile photo Sapna Chaudhari
      Sapna Chaudhari

      Hi Rafaee Mustafa,

      Thanks for the information.

      I tried the below code for making  TaxCode field mandatory in Purchase Order. I add it SBO_SP_TransactionNotification but it cannot affect...plz give me solution...........

       

      If(@transaction_type= ‘A’ or @transaction_type = ‘U’) and @object_type =’22’)

      Begin

      If exists(select  T0.[ItemCode] from POR1 T0  inner join OPOR T1 on T0.[DocEntry]=T1.[DocEntry]

      Where (T0.[TaxCode] is null or T0.[TaxCode]=’-’  or T0.[LocCode]is null or T0.[Price]) and T0.[DocEntry]=@list_of_cols_val_tab_del)

      Begin

      Select @error=1, @error_message=’Enter the TaxCode’

      End

      End

      ************************

      Regards

      Author's profile photo Rafaee Mustafa
      Rafaee Mustafa
      Blog Post Author

      Hello Sapna,

      Please check with below code

      select  T0.[ItemCode] from POR1 T0  inner join OPOR T1 on T0.[DocEntry]=T1.[DocEntry] Where (T0.[TaxCode] is null or T0.[TaxCode]= '-'  or T0.[LocCode] is null) and T1.DocEntry] = @list_of_cols_val_tab_del

       

      further for the instant help from community members I would request you to post your query on https://answers.sap.com/questions/ask.html

      Author's profile photo Sapna Chaudhari
      Sapna Chaudhari

      Thank you...I'll try and get back to you..

      Author's profile photo Sapna Chaudhari
      Sapna Chaudhari

      Hi Rafaee Mustafa,

      Its very late for reply....I applied your code and it works properly....Thank you for that.

      But,now I use approval procedure for purchase order and i just want to block PO without taxcode before sending to approval...

      Previous code is work at final add i.e PO goes to approval and it get approved and when we finally add it to system it block it...but i want it block before sending to approval....

      I tried following code.

      declare @draft_object int

      if @object_type = '112' and @transaction_type in (N'A', N'U')

      begin

      select @draft_object = ObjType from ODRF where DocEntry = CAST(@list_of_cols_val_tab_del as int)
      if @draft_object = 22-- Draft PO
      If exists (select T2.ItemCode From OPOR T1 inner join POR1 T2 on T2.[DocEntry]=T1.[DocEntry]
      where (T2.TaxCode is null or T2.TaxCode='' ) and
      T2.DocEntry=@list_of_cols_val_tab_del)

      Begin

      select @error=1,@error_message ='Enter The Tax Code'
      End
      End

       

      It doesn't get any output..Please help me for that...Thank you in advance

       

      Author's profile photo Syed Adnan Mohiuddin
      Syed Adnan Mohiuddin

      Hi Sapna,

      Try this.

      if @object_type = ‘112’ and @transaction_type in (N’A’, N’U’)

      begin

      If exists (select T2.ItemCode From OPOR T1 inner join POR1 T2 on T2.[DocEntry]=T1.[DocEntry]
      where (T2.TaxCode is null or T2.TaxCode='' ) and T1.ObjType = 22 and
      T2.DocEntry=@list_of_cols_val_tab_del)

      Begin

      select @error=1,@error_message =’Enter The Tax Code’
      End
      End

      Author's profile photo Sapna Chaudhari
      Sapna Chaudhari

      Hi Syed,

      Thanks...I'll try and get back to you...

      Author's profile photo Steve Dakin
      Steve Dakin

      Hello Rafaee Mustafa,

      I am fairly new to SAP B1.

      I do not seem to have any Stored Procedures of this type defined in my DB:

       Is there something I need to do to load them?

      Many thanks

      Steve Dakin

      Author's profile photo Alexis Gaitan
      Alexis Gaitan

      Any example to check when a New Item is created/updated and generate a .txt or .csv file?