Order Confirmation | Boomerang Notification Services for SQL Server

A Tailored Made Order Confirmation


In a high pressure customer support center every second counts, a streamlined and efficient workflow is imperative to any thriving business. We have seen numerous examples of representatives having to navigate through multiple applications to perform basic customer service functions like printing a order confirmation.

The Boomerang framework may be used to quickly create solid solutions where communication of any sort is needed. In this specific example we will illustrate how a customer order confirmation can be dynamically distributed depending on customer preferences and other business requirements.


In this scenario we set out to improve how a typical order confirmation is presented and delivered to customers. We tailor the order confirmation to individual customers preferences like language, layout, special pricing agreement and how it is to be delivered for example by fax, email (inline), email (pdf attachment) or XML. A order confirmation workflow typically contains several steps that are manual or somewhat manual but that can with the help of Boomerang quickly be automated and streamlined.

Order confirmation manual vs automated

Customer Preferences

Our first step in automating the order confirmation workflow is to establish a way to manage customer preferences. How does each customer or group of customers like to be notified? What special formatting and content apply to your specific business? What information is and is not available in your CRM/ERP system? These questions will obviously have different answers depending your type of business and legacy systems, however for the purpose of this example we have created a simple application screen linked to an ERP customer master table. With this screen customer preferences and preferred lines of communication are noted. From this screen default values and preferences can easily be maintained.

Order Confirmation | Customer Preferences

Order Entry

For order entry we added an additional order confirmation tab to accompany the order head and order lines tab. Default values are picked up from our customer preferences but may be modified on the order confirmation tab.

Once the sales order record have been saved, a stored procedure is called and executed (linked to event CreateNewSalesOrder_Saved()). If this type of change cannot be made to your legacy systems you may trigger the event on a database event or as a scheduled job.

Order Confirmation | Order Entry

Sending Order Confirmation – Step 1

The first step in sending the confirmation is to gather all details needed for the order confirmation like fax number, email address, who is sending the confirmation, formatting (PDF, TIF, EXCEL, XML). Since we are using SSRS to create the layout of actual order confirmation we also define what report template (RDL) to use and what parameters to pass to the report.

2012-04-08	Order Confiramtion sample
alter procedure [dbo].[sp_Demo_Order_Confirmation] (@Id int)
as begin
set nocount on
set xact_abort on
declare @OrderId int, @SendToEmail nvarchar(max), @SendToFaxNo nvarchar(255), @PrinterPath nvarchar(255),
		@SendToFileShare nvarchar(255), @SendDate datetime, @SentBy nvarchar(255), @SSRS_Rpt nvarchar(255),
		@SSRS_Rpt_Par1 nvarchar(255), @Subject nvarchar(255), @EmailFormat nvarchar(10), @FileOutName nvarchar(255)
-- Get order confirmation details
 @OrderId = OrderConfirmation_SalesOrder
,@SendToEmail = case when SendEmail = 1 then SendToEmail end
,@SendToFaxNo = case when SendFax = 1 then SendToFaxNo end
,@PrinterPath = case when SendToPrinter = 1 then '\\BOSFS\hp_1525' end
,@SendToFileShare = case when SaveToFileShare = 1 then SendToFileShare end
,@SendDate = getdate()
,@SentBy  = '"' + SentBy + '"' + ''
,@SSRS_Rpt = 'Boomerang_Demo/Order_Confirmation'
,@SSRS_Rpt_Par1 = 'OrderId'
,@Subject = 'Order Confirmation | ' + convert(nvarchar(10), @OrderId)
--,@EmailFormat = EmailFormat -- PDF, TIF, EXCEL
,@FileOutName = convert(nvarchar(10), @OrderId) + '-' + convert(nvarchar(10), @Id)
From CustomerService.dbo.OrderConfirmation
where (SendFax = 1 or SendToPrinter = 1 or SaveToFileShare = 1 or SendEmail = 1)
and Id = @Id

Sending Order Confirmation – Step 2

In the second step we insert a record in each of the applicable Boomerang service tables OUT_PRINT, OUT_EMAIL, OUT_FAX, OUT_FILE to create all the different types of order confirmation simultaneously.

-- Notifications to process? 
if (@@rowcount > 0) begin 
	-- Create Keys
	declare @gKey uniqueidentifier; set @gKey = newid()
	declare @aKey uniqueidentifier, @jKey uniqueidentifier;
	-- Create New event
	insert Boomerang..EVENT_MASTER (gKey, Created_By, Int1, Int2) values (@gKey, @SentBy, @OrderId, @Id)
	-- print output
	if( not @PrinterPath is null ) begin
		select @jKey = newid(), @aKey = newid()
		insert Boomerang..OUT_PRINT (gKey,jKey,Printer_Path) values (@gKey, @jKey, @PrinterPath)
		insert Boomerang..EVENT_CONTENT (gKey, jKey, aKey, Src_Type, [Path]) values (@gKey, @jKey, @aKey, 2, @SSRS_Rpt) -- 2 = SSRS Report
		insert Boomerang..CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey, @SSRS_Rpt_Par1, @OrderId)	
	-- e-mail output
	if(not @SendToEmail is null ) begin
		select @jKey = newid(), @aKey = newid()
		insert Boomerang..OUT_EMAIL (gKey, jKey, [Subject], [From]) values (@gKey, @jKey, @Subject, @SentBy)
		exec   Boomerang..sp_Add_Email_List @jKey, @SendToEmail -- add each recipient to OUT_EMAIL_RECIPIENT
		insert Boomerang..EVENT_CONTENT (gKey, jKey, aKey, Src_Type, [Path], [Format]) values (@gKey, @jKey, @aKey, 2, @SSRS_Rpt, @EmailFormat)  -- 2 = SSRS Report
		insert Boomerang..CONTENT_PARAMETER (aKey,[Name],[Value]) values (@aKey, @SSRS_Rpt_Par1, @OrderId)
	-- fax output
	if( not @SendToFaxNo is null ) begin
	select @jKey = newid(), @aKey = newid()
		insert Boomerang..OUT_FAX (gKey, jKey, Fax_Path, Receiver_Fax_Number) values (@gKey, @jKey, '\\faxserver\fax', @SendToFaxNo)
		insert Boomerang..EVENT_CONTENT (gKey, jKey, aKey, Src_Type, [Path]) values (@gKey , @jKey, @aKey ,2, @SSRS_Rpt)
		insert Boomerang..CONTENT_PARAMETER (aKey, [Name], [Value]) values (@aKey, @SSRS_Rpt_Par1, @OrderId)
	-- file output
	if( not @SendToFileShare is null ) begin
		select @jKey = newid(), @aKey = newid()
		insert Boomerang..OUT_FILE (gKey,jKey,[Path]) values (@gKey, @jKey, @SendToFileShare)
		insert Boomerang..EVENT_CONTENT (gKey, jKey, aKey, Src_Type, [Path],[Name], [Format]) values (@gKey, @jKey, @aKey, 2, @SSRS_Rpt, @FileOutName, 'PDF')
		insert Boomerang..CONTENT_PARAMETER (aKey, [Name], [Value]) values (@aKey, @SSRS_Rpt_Par1, @OrderId)
	-- flag event ready and return gKey
	update Boomerang..EVENT_MASTER set Status=0 where gKey=@gKey

Order Confirmation – Receiving Feedback

Each email sent out is automatically traceable. This means that if a customer replies to a specific email it will automatically be tagged with the identification of the original outgoing email. In a customer support environment this is often very useful and helpful when creating effective workflows.

Order Confirmation | Email In

Email In

Any email received by Boomerang will be parsed and stored in the Boomerang database. If the receiving email was originated by Boomerang it will also be tagged and can be linked to OUT_EMAIL as below query illustrates.

,oute.[From] SendbyRep
inner join OUT_EMAIL oute on oute.jKey = ine.jKey
inner join EVENT_MASTER em on oute.gKey = em.gKey
em.Int1 = 1 -- Order Number


From_Name      Subject Body                                                                          SendByRep
-------------- ---------------------------- -------------------------------------------------------- --------------------------------------------------------
Bob            Re: Order Confirmation | 1 Hello, please push delivery to May 1st. Regards Bob        "Tom"
(1 row(s) affected)

On Email In

Another alternative to list replies for a specific order confirmation would be to process the incoming email and take additional actions. This is made easy with an email in event handler called sp_On_Email_In that will fired after each new incoming email. Below is an example that will forward email replies that are sent with a high importance to the manager of the customer service center.

ALTER procedure [dbo].[sp_On_Email_In]
@rKey uniqueidentifier
as begin
--- Forward messages sent with high importance to manager of customer services --------------
declare @from_name varchar(100), @subject varchar(100), @body varchar(max), @orderid varchar(10)
 @from_name = ine.[From_Name]
,@subject = ine.[Subject]
,@body = ine.Body
,@orderid = convert(varchar(10), em.Int1)
inner join OUT_EMAIL oute on oute.jKey = ine.jKey
inner join EVENT_MASTER em on oute.gKey = em.gKey
inner join IN_EMAIL_HEADER ineh on ineh.rKey = ine.rKey and ineh.Name = 'Importance' and ineh.Value = 'high' 
where rKey = @rKey
if @@ROWCOUNT = 1 
	declare @gKey uniqueidentifier; set @gKey = NEWID()
	declare @jKey uniqueidentifier; set @jKey = NEWID()
	set @body = 'The following email was sent by: ' + @from_name + ' with high importance' + CHAR(10)+CHAR(13)+
	            'Messge sent: ' + CHAR(10)+CHAR(13)+
	insert EVENT_MASTER (gKey, Created_By) values (@gKey, 'sp_On_Email_In')
	insert OUT_EMAIL (gKey,jKey,Subject,Body,IncludeKey, [From]) values (@gKey,@jKey,'Customer Feedback | Order Id:' + @orderid, @body, 0, '"Customer Support" ')
	insert OUT_EMAIL_RECIPIENT (jKey, Email) values (@jKey, 'info@fuel9.com')
	update EVENT_MASTER set Status = 0 where gKey = @gKey
return 0

Order Confirmation | Email In


Boomerang takes away the chore of writing complex code to interface with notification infrastructure like e-mail, printers, file and fax servers. By means of the Boomerang framework IT Professionals can create robust and dynamic notification solution with a minimum amount of T-SQL code.

As demonstrated above we created a customer tailored order confirmation delivered in any preferred way as well as handling customer feedback with just a few T-SQL statements.

Scenario Information

This application scenario is based on the following technologies: SQL Express, Visual Studio Ligthswitch, SSRS.

Download Sample

Dynamic Order Confirmation Sample