How to Execute Custom Code via SP_TransactionNotification
if you read theThe SP_TransactionNotification Stored Procedure Stored Procedure you understood the advantage of procedure: “you can do some validation in SAP Business One and prevent the user from doing an action if the validation condition fails.”
Probably Everybody can have the question:
Is It possible to execute custom code via SP_TransactionNotification procedure?
Basically the answer is Yes.
Let’s investigate the following scenario: Company sales process requires to send delivery notification email to specific customers at time when delivery note has been sucessfully issued.
We can solve this problem via DI API Messages Object using .NET code. There is more elegant another way: Sending email without Addons using the same .NET code from SQL Server.
Write a .NET Class Based DLL which send an email to an address given by parameters. The DLL is using DI API to send an email over SBO mailer service.
I have used C# and Mesasges Object of DI API to send an email.
Sending message via DI API using Messages Object – without GUI
Create a New Windows Class Library Project in C#, give name SimpleDeliveryNotification and include the SAP Business One DI API 2007 as a reference.
Import namespaces required to MS SQL 2005 CLR code
Write a Class which can connect to SAP B1 company and send message.
– SAP B1 UserName and Password should provided for access to Company (single sign on is not working here)
– UseTrusted propety can be used (we are inside SQL – the code is running on sql server directly)
– For future flexible usage: Queries can be used to determine Company Name and Server name for connection parameters of DI API Company Object.
Compile the Class, and Copy the SimpleDeliveryNotification.dll into anywhere on the MS SQL 2005 server.
MS SQL 2005 Settings
Enable CLR on MS SQL Server, connect by Management Studio, and open a new Query. There enter the following commands.
Register the DLL for MS SQL server by CREATE ASSEMBLY command from your copied (compiled path)
Create a Stored Procedure for Executing your managed code (DLL). Procedure name: SP_Send_Delivery_Notification and this will be called when sending the email message.
You can test the Stored procedure by Calling it from MS SQL Query Analizer or from an SAP B1 Query.
The SP_TransactionNotification code
When a delivery note has been issued, the SP_TransactionNotification is executed. In the following example the numatcard field holds the Customer Order Number. E-Mail field from Customers master data can used for populating the email address where the Simple Delivery Notification should be sent.
The CLR comined with SP_TransactionNotification stored procedure is a flexible, fast and elegant possiblity to handle background processes like sending notifications, creating automatic post operations using DI API .
Sample code can be downloaded from here
The Common Language Runtime (CLR) is a core component of Microsoft’s .NET initiative. It is Microsoft’s implementation of the Common Language Infrastructure (CLI) standard, which defines an execution environment for program code. Developers using the CLR write code in a language such as C# or VB.NET. At compile time, a .NET compiler converts such code into CIL code. At runtime, the CLR’s just-in-time compiler converts the CIL code into code native to the operating system.
Alternatively, the CIL code can be compiled to native code in a separate step prior to runtime. This speeds up all later runs of the software as the CIL-to-native
compilation is no longer necessary.”
definition source: Wikipedia