This document aims to guide you through how to customize SAP Business One, version for SAP HANA with the built-in procedure of transaction notification SBO_SP_TransactionNotification, which allows you to add additional logic during transactions in SAP Business One, such as adding or updating a Business Partner Master data and Marketing Documents, etc. The purpose and interface of SBO_SP_TransactionNotification remains exactly the same as in the SAP Business One SQL Server version. One typical use is creating additional validation rules for data entry of SAP Business One. This document will guide you through with samples of making BP Name as a mandatory field and a certain name convention rule Customer Code of Business Partners master data, by implementing the procedure SBO_SP_TransactionNotification with SAP HANA SQL Script. Here you can find more detailed information about the SQL Server version of SBO_SP_TransactionNotification:
Using the SBO_SP_TransactionNotification Stored Procedure
Business Requirements:
For example, your client requires end users to enter BP Name and Customer Code with a certain naming conversion when adding or updating a Business Partner in SAP Business One.
Rule #1: The BP Name must be entered when adding or updating a BP in SAP Business One.
When the end user is entering or updating a Business Partner master data record in SAP Business One, they won’t able to save the BP unless the BP Name is entered.
If the BP Name field is blank, SAP Business One blocks adding or updating this BP, and displays the error message “Enter BP Name” in the status bar.
Rule #2: The customer code must follow the naming convention “C******” when adding or updating a customer record in SAP Business One.
The customer code must start with prefix “C” indicating it as a customer. Any other leading character will not be allowed for customer entry and will result in the error message “Customer Code must start with C”.
Solution Options:
Option 1: Implement these additional validation rules with a customized UI API Add-On by listening for ItemEvent(et_Click) of the Add/Update button in the BP Master Data form. The validation will be triggered when the button is clicked. Before SAP Business One commits the transaction the BPName is validated against the rule. If the BPNAme violates the rule, SAP Business One stops the transaction by setting BubbleEvent as false. However, this document doesn’t aim to discuss this in detail.
Option 2: Customizing SAP Business One, Version for SAP HANA with SSP add-on solutions, such as B1UP (SAP Business One Usability Package) from BoyumIT, CoreSuites from CoreSystems etc. Please contact the SSP directly if you’re interested on it.
Option 3: Customizing SAP Business One, version for SAP HANA by implementing the built-in procedure SBO_SP_TransactionNotification with SAP HANA SQL Script. The following sections will discuss the solution in detail.
Implementing SBO_SP_TransactionNotification with SAP HANA SQL Script
SBO_SP_TransactionNotification is a built-in procedure of SAP Business One which is triggered when Adding/Updating/Deleting a record of any business data, such as Business Partner Master Data, Marketing Documents (Sales Order, A/R Invoice etc.), etc.
Prerequisites: You should have knowledge of SAP HANA SQL and SQL Script. More details about SAP HANA SQL and SQL Script are available here.
http://help.sap.com/hana_platform#section7
http://help.sap.com/hana/SAP_HANA_SQL_and_System_Views_Reference_en.pdf
http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf
The interface of SBO_SP_TransactionNotification:
CREATE PROCEDURE SBO_SP_TransactionNotification ( in object_type nvarchar(20),
-- SBO Object Type
in transaction_type nchar(1),
-- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
in num_of_cols_in_key int,
in list_of_key_cols_tab_del nvarchar(255),
in list_of_cols_val_tab_del nvarchar(255) ) LANGUAGE SQLSCRIPT AS -- Return values
error int; -- Result (0 for no error)
error_message nvarchar (200); -- Error string to be displayed
begin error := 0;
error_message := N'Ok';
----------------------------------------------------------------------
-- ADD YOUR CODE HERE
---------------------------------------------------------------------- -- Select the return values
select
:error,
:error_message
FROM dummy;
end;
Parameters |
Example |
|
@objecttype |
2 (Business Partner) |
Since version 2005 the procedure returns a string representing the type of object (it used to be a numerical value in previous versions).
A full list of object types exposed through the DI API can be found in the DI API documentation under the section Enumerations. The enumeration is called BoObjectTypes. |
@transaction_type |
U (update) |
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)
|
@num_of_cols_in_key |
1 |
Returns the number of columns or fields in the key to the record. A Business Partner, for example, has a key consisting of a single field (CardCode), so this variable would have a value of "1". A Special Prices object, however, has a key consisting of the CardCode and ItemCode, so for a Special Prices object this variable would have a value of "2". |
@List_of_key_cols_tab_del |
CardCode |
Returns a tab-delimited list of column names (field names) that represent the object key.
For a Business Partner, this would be
"CardCode". For a Special Prices object, it would be "CardCode ItemCode". |
@List_of_cols_val_tab_del |
C40001 |
Returns a tab-delimited list of values required to retrieve the object from SAP Business One.
For example, a Business Partner record might have a value of "C40001". A Special Prices object would contain 2 fields separated by a tab character. For example: "V10005 ... A00001" |
Implementing the procedure by adding custom code.
You are not able to modify the procedure from SAP HANA Studio directly; likewise you are used to modifying the one in SQL Server Version with SQL Server Management Studio. Therefore we recommend you drop the procedure, and create it again with your custom code instead of altering the procedure command.
Steps to modify the SBO_SP_TransactionNotification procedure:
1) Copy the procedure definition of SBO_SP_TransactionNotification into SQL Console of HANA, preparing for the modification.
2) Drop the SBO_SP_TransactionNotification with SQL Command:
DROP PROCEDURE "YOUR_COMPANY_SHCMEMA".SBO_SP_TransactionNotification;
3) Add the code into SBO_SP_TransactionNotification as below which is copied in step 1.
4) Run the procedure creation SQL Script within SQL Console. It will create the procedure again with modification.
Sample code:
CREATE PROCEDURE SBO_SP_TransactionNotification ( in object_type nvarchar(20), -- SBO Object Type
in transaction_type nchar(1),
-- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
in num_of_cols_in_key int,
in list_of_key_cols_tab_del nvarchar(255),
in list_of_cols_val_tab_del nvarchar(255) ) LANGUAGE SQLSCRIPT AS -- Return values
cnt int; --Important: User defined additional local variable as the count of records, used in the procedure for validation
error int; -- Result (0 for no error)
error_message nvarchar (200); -- Error string to be displayed
begin
error := 0;
error_message := N'Ok';
----------------------------------------------------------------------
-- ADD YOUR CODE HERE
--Adding additional validation rules for BP.
if :object_type='2' and (:transaction_type='A' or :transaction_type='U') then
--Scenario#1: Check if records count number of given CardCode and --CardName is blank
select count(*) into cnt from "SBODEMOUS"."OCRD"
where "CardCode" = :list_of_cols_val_tab_del and IFNULL("CardName",'')='';
if :cnt>0 then
error := -111;
error_message := 'Enter BP Name';
end if;
--Scenario#2: Check if records count number of given CardCode and --it is a customer
--and CardCode doesn't with C
cnt := 0; --Reset the count again for next validation
select count(*) into cnt from "SBODEMOUS"."OCRD"
where "CardType" = 'C' and "CardCode" = :list_of_cols_val_tab_del and not("CardCode" like 'C%');
if :cnt>0 then
error := -112;
error_message := 'Customer Code must start with C';
end if;
END if;
----------------------------------------------------------------------
-- Select the return values
select
:error,
:error_message
FROM dummy;
end;
About SAP HANA SQLScript
SQLScript is a collection of extensions to Structured Query Language (SQL). The extensions are:
- Data extension, which allows the definition of table types without corresponding tables.
- Functional extension, which allows definitions of (side-effect free) functions which can be used to express and encapsulate complex data flows.
- Procedural extension, which provides imperative constructs executed in the context of the database process.
It supports complicated control logic such as conditional processing, loop, cursor, etc., as well as many built-in functions for string, mathematical, datetime etc.
vedios about SAP HANA SQL Script on youtube
