Skip to Content

The SP_TransactionNotification is the only legal way in SAP Business One to receive notification of data-driven events – in order 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 and a default SP looks like this:

 

image

What can i do with the SBO_SP_TransactionNotification stored procedure?

You can add your own code in the section ‘ADD YOUR CODE HERE’. Since Version 2007 you can now change the Error Code returned by the transaction notification and  prevent the transaction from being committed to the database. The SBO_SP_TransactionNotification must return two values – @error and @error_messages. If what is returned in @error is anything other than zero, the transaction will rollback.

There are five parameters in the TransactionNotification:

  1. @objecttype – the object type you want to use – this is a string value and represents the object eg Business Partner, Invoice etc.
  2. @transaction_type – this is the action type eg Add, Update, Cancel etc.
  3. @num_of_cols_in_key – number of columns/fields in the key of the record.
  4. @list_of_key_cols_tab_del – returns a tab delimited list of column/field names that represent the object key.
  5. @list_of_cols_val_tab_del – returns a tab delimited list of values required to retrieve the object from B1.

 

Examples of how it can be used.

Example 1: Prevent a user from adding a Sales Invoice if the field ‘Customer Ref. No’ is empty/blank. 

image

 

Example 2: Prevent a Purchase Invoice from being added if it does not have a base document (Purchase Order or Goods Receipt PO)

image

 

Example 3: Prevent an Incoming Payment from being added if the amount is greater than 2500 for all users except the manager.

image

As you can see the structure for all samples remains the same and only the query/logic changes. 

What the other SBO Stored Procedures do.

In the 2007 version there are 4 stored procedures:
– SBO_SP_PostTransactionSupport and SBO_SP_TransactionSupport (since SAP Business One 2007)
– SBO_SP_PostTransactionNotice (since SAP Business One 2007)
– SBO_SP_TransactionNotification

The stored procedures SBO_SP_TransactionSupport and SBO_SP_PostTransactionSupport are encoded and cannot be edited by the user. They are database notification stored procedures which can be used by SAP support to add diagnostic code so they are of no use for customer’s daily work. Since they are of no use it is planned to remove them in SAP Business One Version 8.8.

SBO_SP_PostTransactionNotice and SBO_SP_TransactionNotification can by edited by the user to add sql code as part of a notification process. The functionalities of both  are the same, 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). It is preferable to use SBO_SP_PostTransactionNotice – although as this occurs after the transaction, the @error rollback functionality can not be used.

Further Documentation

The following documenatation is available on the SDN:                                                                                
    o  Using the SBO_SP_TransactionNotification Stored Procedure:              
                                                                               
       http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uu       
       id/e991e2b9-0901-0010-0395-ef5268b00aaf                                                 
                                                                                                   
                                                                                
    o  SAP Business One Add-On Solution Certification                          
                                                                               
       http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/libra       
       ry/uuid/81a22ee1-0701-0010-45aa-ec852e882de3                

             

In two weeks time, our blog will cover the topic of UI folders/tabs and how to create a new folder/tab in your form. So catch you then 🙂

To report this post you need to login first.

46 Comments

You must be Logged on to comment or reply to a post.

  1. Andrew Tribe
    Very informative but could you please clarify

    1 Are there any restriction regarding the @error value? (You have used values of the form -500x; is this mandated, suggested or merely a coincidence?)

    2 Is there a maximum length to the @error_message string, and if so what?

    Many thanks in advance.

    (0) 
    1. Lisa Mulchinock Post author
      Hi Andrew,

      Thank you for your positive feedback and your questions.

      1. Are there any restriction regarding the @error value?
      Answer: For an easy example i used the error code series -500x. You can see in the blank stored procedure @error is defined as an int (declare @error int). So the only rule is this value must be an integer.

      2 Is there a maximum length to the @error_message string, and if so what?
      Answer: You can see in the blank stored procedure the length of the @error_message is defined when we declare it (declare @error_message nvarchar (200) ) so in this case we have defined that the error string returned is a maximum of 200 characters. You can vary this length just ensure you test it to prevent it bring truncated in the status bar of Business One. The max for the status bar in Business One is about 250 characters.

      I hope this answers your questions,
      Cheers,
      Lisa

      (1) 
    2. Chi Wa Ken Chan
      For outgoing payment, do the SP_Trans cannot be triggered when the outgoing payment is added?
      Since the follwoing code can be triggerd only update the outgoing payment…

      IF (@object_type = ’46’) And @transaction_type In ( ‘A’, ‘U’ )
           BEGIN
                insert into tempKen
                values
                (@list_of_cols_val_tab_del)
                EXEC CPS_SP_IM_UpdateOPComChkNo @list_of_cols_val_tab_del
           END

      (0) 
  2. Chi Wa Ken Chan
    The following code in the SP_Trans can be triggered only when the Outgoing Payment is updated, but cannot for the add.

    IF (@object_type = ’46’) And @transaction_type In ( ‘A’, ‘U’ )
         BEGIN
              insert into tempKen
              values
              (@list_of_cols_val_tab_del)
              EXEC CPS_SP_IM_UpdateOPComChkNo @list_of_cols_val_tab_del
         END

    (0) 
    1. Lisa Mulchinock Post author
      Hello,

      I have tested your TransactionNotification code on 2007A PL49 using this simplified example:

      IF @object_type = ’46’ And @transaction_type In (‘A’, ‘U’)
      BEGIN
      set @error = 4001
      set @error_message = ‘Stop Outgoing Payment’
      END

      And in my testing the error is thrown both during the Add and Update of the Incoming Payment so it seems to be working as expected.

      Thanks,
      Lisa

      (0) 
      1. Chi Wa Ken Chan
        thx Lisa,

        I found that when adding the outgoing payment, the object_type is 30, it is the type of JE.

        There is Patch 10 in my B1 environment, is it related?

        Ken

        (0) 
        1. Lisa Mulchinock Post author
          Hi Ken,

          This should not occur so in this case it may be a bug. Can you check in the latest version to see if it still occurs. If it does it would be best to log a message for support to investigate.

          Thanks,
          Lisa

          (0) 
  3. Humberto Pantoja
    Hi,

    can this example works if i have triggered an authorization in the Invoice? because if i have an some kind of autorization this trigger first and only when this was autorized the execute the validation of store procedure. exist some way of avoid this? i want use autorization and this validation of Sp in the same time.

    thanks

    (0) 
    1. Lisa Mulchinock Post author
      Hello Humberto,

      Do you mean you first do an authorization or you mean you first do an Approval? Can you provide more information on your Business Process to help me understand the issue in more detail.

      Thanks,
      Lisa

      (0) 
      1. Humberto Pantoja
        sure Lisa thanks,

        I had already created a approval process for purchase invoice and this process is triggered for anytime someone try to create an purchase  invoice.

        So plus this i want to add  validation using this SP_prevent, but the problem is the validation is executed after the execution of approval procedure and in this company this procedure can delay about one or two days so after two days when the invoice is approved then is executed the SP and block the invoice because this invoice does not have a base document like and purchase order.

        i hope u understand now.

        thanks for your help

        (0) 
        1. István Kőrös
          Hello Humberto

          When an approval procedure is activated, the transaction is stored as a draft in the ODRF table. And the object type passed to the procedure is 112 !

          So you should write different code to manage the draft documents. For example for a PO you should start your code with something like this:
          IF @transaction_type = ‘A’ AND @object_type = ‘112’
          And  (Select ObjType From ODRF
                 Where DocEntry = @list_of_cols_val_tab_del)=’22’ — for PO.

          Regards
          István Körös

          (0) 
          1. Bernard Anthony

            Hi Istvan

            For Approvals 112 works when Originator Doc is sent to approver

            However… when Approver gets the Alert in SAP … The approver clicks and then selects Approve … that time 112 does not get recognized…

            I would like to know which Object type gets activated ?

            Regards

            Bernard

            (0) 
        2. Lisa Mulchinock Post author
          Hello Humberto,

          the approval process in Business One will always overwrite whatever is in the SP_TransactionNotification stored procedure. However you do have some options to overcome this:

          1. As István suggested below do the SP_TransactionNotification validation for the draft document

          2. Put the validation that is done in the approval procedure in the SP_TransactionNotification stored procedure also (this may not suit your Business Process)

          3. Create a small UI addon that will do the validation before the autorisation screen opens. I tested this issue but the validation does not seem to activate. If i solve it i will post the code.

          Cheers,
          Lisa

          (0) 
  4. Xiaoqin Liang
    Hello Lisa:
    I found that there were three new procedures in B1 2007B version.Except the procedure called SP_TransactionNotification you mentioned on this topic, the rest are:
    1)SBO_SP_SBO_SP_PostTransactionNotice
    2)SBO_SP_PostTransactionSupport
    3)SBO_SP_TransactionSupport

    Could you please tell me something about them ?such as:
    1 What’s the difference between SP_TransactionNotification and the rest ?
    2What’s the usage of the three procedures ?
    3How do they work ? If any samples ?

    Many thanks in advance

    (0) 
    1. Lisa Mulchinock Post author
      Hello Xiao,

      As you said in the 2007 version there are 4 stored procedures.
      – SBO_SP_PostTransactionSupport and SBO_SP_TransactionSupport (since SAP Business One 2007)
      – SBO_SP_PostTransactionNotice (since SAP Business One 2007)
      – SBO_SP_TransactionNotification

      The stored procedures SBO_SP_TransactionSupport and SBO_SP_PostTransactionSupport are encoded and cannot be edited by the user. They are database notification stored procedures which can be used by SAP support to add diagnostic code so they are of no use for customer’s daily work. Since they are of no use it is planned to remove them in SAP Business One Version 8.8.

      SBO_SP_PostTransactionNotice and SBO_SP_TransactionNotification can by edited by the user to add sql code as part of a notification process.

      As you saw in the blog, the purpose of the Transaction Notifications are to enable the partner/customer to receive notification on any transaction taking place within SAP Business One database.

      The functionalities of both SBO_SP_PostTransactionNotice and SBO_SP_TransactionNotification are the same, 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), whereas SBO_SP_TransactionNotification can be triggered at the beginning, middle or end of an action. It is preferable to use SBO_SP_PostTransactionNotice – although as this occurs after the transaction, the ‘@error’ rollback functionality can not be used.

      I hope that provides you the info you need about them. I will also add this text to the main blog.

      Cheers,
      Lisa

      (0) 
  5. Daniel Rivas
    At the time to add a new BP or Item, can I use SP_TN to validate the adding?  Is it possible what are the object types for BP data master ?

    Thanks.

    (0) 
    1. Lisa Mulchinock Post author
      Hello Daniel,

      Yes the TransactionNotification stored procedure can be used for any object including master data.
      The object code for Items is 4 and for Business Partner is 2.

      Cheers,
      Lisa

      (0) 
  6. Antonio Burgos
    You mention in your blog that  the Transaction Notification is triggered at the beginning, middle or end of an action. How can i identify these three states?
    (0) 
    1. Lisa Mulchinock Post author
      Hi Antonio,

      I think my wording here may be a bit misleading so i have removed this part to prevent further confusion.

      As mentioned in the blog the PostTransactionNotice can only be triggered at the end of an action i.e. once it has been committed to the database.

      With the TransactionNotification store procedure you have much more control as you have the ability to use @error functionality to prevent the object being committed to the database.

      I hope that helps.

      Cheers,
      Lisa

      (0) 
  7. Donald Frantum
    Would it be possible to use this SP to prevent users from changing existing field level entries?  What comes to mind is Business Partner Master Data information.  I would like a user to be able to add contact information, but not change the the BP Name.  If they do, I want an them to receive an error upon UPDATE.  Do you think it is possible within this SP and if so, what might be your approach?
    (0) 
    1. Donald Frantum
      I recognize that this SP won’t work for master data, as it is transaction based.  Is there another SP that would work for this application?

      Thanks again,
      Donald

      (0) 
        1. Donald Frantum
          I tried this, but it didn’t work…perhaps someone can shed some light on how I could change it to work.
          [code]
          — Prohibit anyone but accounting to add Business Partners
          IF @object_type = 2 AND @transaction_type = ‘A’
          BEGIN
               IF EXISTS(SELECT T0.CardCode FROM OCRD T0 WHERE T0.UserSign NOT IN (4,84) AND T0.DocEntry = @list_of_cols_val_tab_del)
                BEGIN
                 SELECT @error = 0,
                        @error_message = N’You do not have permission to add Business Partners.  Please see Accounting.’
                END
          END
          [/code]

          Thanks again,
          Donald

          (0) 
          1. Lisa Mulchinock Post author
            Hi Donald,

            As you saw from Daniel’s reply its not just transaction data this sp applies to. In your sample there are a couple of issues.
            – First the object type must be enclosed e.g. ‘2’
            – The query has also changed. We’re selecing UserSign and not CardCode. I’ve replaced DocEntry wtih CardCode since DocEntry only applies to docuemnts.

            Below is the corrected sp for the scenario of preventing users from adding Business Partners:

            IF @object_type = ‘2’ AND @transaction_type = ‘A’
            BEGIN
            IF EXISTS (SELECT T0.UserSign FROM OCRD T0 WHERE T0.UserSign NOT IN (4,84) and T0.CardCode = @list_of_cols_val_tab_del)
            BEGIN
            SELECT @error = -500,
            @error_message = N’You do not have permission to add Business Partners. Please see Accounting.’
            END
            END

            If you just want to prevent certain users from adding a BP then you could do this a little easier via Authorisations in Business One.

            Cheers,
            Lisa

            (0) 
            1. Donald Frantum
              Thanks Lisa.  I know about the Authorization, but I am playing with this and I wanted to give an easy example with which to experiment.  Thank you so much for the feedback.  BTW, can this be adapted to @transaction_type IN (‘D’,’U’) as well or does it have to be modified.  If so, how does it recognize the current user vs. the usersign in OCRD?
              (0) 
              1. Lisa Mulchinock Post author
                Hi Donald,

                Yes it can be modified for all action types: [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose.

                I would recommend you search the SDK forum for your answers as it contains quite alot of these type of questions. You can also post these type of questions on the SDK forum SAP Business One SDK

                Cheers,
                Lisa

                (0) 
                1. shiva shanker
                  Hi Lisa,

                  this very very informative.

                  i have wrote some logic in the stored procedure
                  SP_TransactionNotification.

                  the logic is for object type 30 (JE). my logic is working properly when JE is posting. The problem while a particular JE (i mean already created JE) is posting reversal the stored procedure logic is not working. could you please explain me why this is happening.
                  i mean when reversal is posting (alternatively it is creating new JE right) so my logic also should work for this Reversal JE.

                  hope you will provide the answer as early as possible.

                  thanks in advance.

                  Regards
                  shiva shanker

                  (0) 
                  1. Lisa Mulchinock Post author
                    Hi Shiva,

                    I tested this and i think the Reversal works in a different way to the regular JE. From what i observed the reversal is done in the backround so the data add event is not triggered like it is with the regular je.

                    Cheers,
                    Lisa

                    (0) 
  8. Wendy Tranter
    The Approval procedure doesnt include update of PO Doc Total which is a real deficiency in B1.  It’s because if you set an approval of any PO over $10,000 needs to be approved.  The user can simply create a PO of $10 and then update to $10K and it wont go through the approval procedure.  I would like to SP to stop user from updating the PO DocTotal by using the the instance stored in ADOC.  Do you think it’s possible??  Do you have any suggestion of codes ???
    (0) 
    1. Lisa Mulchinock Post author
      Hi Wendy,

      Yes you can use the SP_TransactionNotification to do this. In the example below i have prevented a user updating any PO whose doctotal is greater than 0.

      IF @object_type = ’22’ and @transaction_type = ‘U’

      Begin

        IF (SELECT doctotal from OPOR WHERE OPOR.DocEntry = @list_of_cols_val_tab_del) > 0
            Begin
                  Set @error = -5015
                  Set @error_message  = N’DocTotal cannot be changed’                
            END
      END

      Cheers,
      Lisa

      (0) 
      1. Wendy Tranter
        Hi Lisa

        Thank you very much for replying.  Unfortunately though my customer is a little fussy and he would like user to be able to update other details such as delivery date or address, so I wont be able to use your suggestion.  In the end, one of the other consultants has helped me with the codes using the DocTotal stored in ADOC for comparison. 

        As a feedback, I think SAP should do something about this business logic deficiency.  It really defeat the purpose of approval if user can get around it so easily.

        Lisa, I really appreciate your time in writing me the code and replying.  This blog has been very helpful, thank you very much for writing it.

        Kind Rgds
        Wendy

        (0) 
  9. Rupa Sarkar
    Hi Experts,
    What will be the code for writing a stored procedure so that a user table gets updated at a certain date of every month.
    Regards,
    Rupa Sarkar

    (0) 
    1. Lisa Mulchinock Post author
      Hi Rupa,

      This would not be possible via the SP_TransactionNotification stored procedure since this only reacts to data driven events.

      You may have to look at another method like Scheduling a batch job etc.

      Cheers,
      Lisa

      (0) 
  10. Christian Boos
    Is there anyway to use the SP_TRANSACTION_NOTIFICATION to issue an error that serves as a warning or allows the user to accept rather than stopping them from continuing?
    (0) 
  11. Christian Boos
    Is there a way to log the fact that the SP_TRANSACTION_NOTIFICATION procedure has prevented someone from doing something, i.e. the error was displayed? I tried inserting information to a user defined table withing the procedure but the data was not inserted. I expect it was because of a rollback somewhere in the process.
    (0) 
    1. Lisa Mulchinock Post author
      Hi Christian,

      yes this should be possible – you can write to a log file or a user defined table as you tried to do. Perhaps you just put this after the procedure had finished but i see no reason why it should not succeed.

      Cheers,
      Lisa

      (0) 
    2. Christian Boos
      It appears that I cannot do an insert along with the select for the error message. If I comment out the select for the error message, the insert works. For example, if the following code is executed, the error message gets displayed but the insert does not work. But if I comment out the Select statement, the insert works.

      If the condition exists
      .
      .
      .
      BEGIN
            BEGIN
           INSERT INTO ERROR_LOG_CDB values (2,’yyy’)
              SELECT @Error = 3, @error_message = ‘FUTURE DATED CANNOT BE RELEASED TO WHSE CONTACT LOGISTICS’
            END
      END

      I have other logic where I do additional selects along with the one that populates the error message and that all works.  I also have this problem when trying to perform and UPDATE. It appears this is only an issue when “modifying” my table in some fashion. I have also reversed the order of the actions, doing the insert after the select. No difference.

      (0) 
  12. Barry Gleeson
    Hi is it possible to update a udf on the oitw table with the value from a udf on a transfer. Ie udf on oitw called bin, udf on marketing docs called bin. When transfer takes place the bin udf on the oitw table from the bin udf on the transfer rows where oitw.whscode is = to the target warehouse on the transfer . Is this possible using sp notification.
    (0) 
    1. Lisa Mulchinock Post author
      Hi Barry,

      It should be possible to check that via the SP_TransactionNotification since you would hold the values in variables and then compare them. However just be aware that direct updates via SQL on System tables are not supported by SAP.

      Cheers,
      Lisa

      (0) 
  13. Wee Ling

    Hi Lisa,

    Is it possible to trigger transaction notification in a document generation wizard such as the  Procurement Confirmation Wizard? For example, we want to be able to make the selection ‘Create Draft’ as mandatory. The purpose is to ensure that all POs created from this Wizard are drafts.

    Thanks,

    Wee Ling

    (0) 
  14. Bernard Anthony

    Hi

    As SAP B One does not send email notification to users till the user Logs In to B one.  I tried to work on the Transaction Notification Procedure.

    If @Object_Type = ‘112’ and @Transaction_Type In (‘A’,’U’)

    Begin

       Then I write procedure for sending email to the approver notify the Approver to Logon to SAP and approve the PR

    End

    This works fine when a user makes a Purchase Request, mail goes to Approver

    However, when the Approver logs on and then selects approved and updates… the mail does not go

    As 112 is not the object …

    I tried Object type 122 but still cannot

    I tried Object Type 81 but still cannot

    Any help ???

    Regards

    Bernard

    (0) 

Leave a Reply