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:
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:
- @objecttype - the object type you want to use - this is a string value and represents the object eg Business Partner, Invoice etc.
- @transaction_type - this is the action type eg Add, Update, Cancel etc.
- @num_of_cols_in_key - number of columns/fields in the key of the record.
- @list_of_key_cols_tab_del - returns a tab delimited list of column/field names that represent the object key.
- @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.
Example 2: Prevent a Purchase Invoice from being added if it does not have a base document (Purchase Order or Goods Receipt PO)
Example 3: Prevent an Incoming Payment from being added if the amount is greater than 2500 for all users except the manager.
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 🙂
46 Comments
