A Tailored Made Order Confirmation
Introduction
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.
Solution
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.
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 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.
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 Select @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) end -- 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) end -- 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) end -- 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) end -- flag event ready and return gKey update Boomerang..EVENT_MASTER set Status=0 where gKey=@gKey end end |
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.
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.
select ine.[From_Name] ,ine.[Subject] ,ine.Body ,oute.[From] SendbyRep from IN_EMAIL ine inner join OUT_EMAIL oute on oute.jKey = ine.jKey inner join EVENT_MASTER em on oute.gKey = em.gKey where em.Int1 = 1 -- Order Number |
Result:
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) select @from_name = ine.[From_Name] ,@subject = ine.[Subject] ,@body = ine.Body ,@orderid = convert(varchar(10), em.Int1) from IN_EMAIL ine 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 begin 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)+ @body 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 end return 0 end |
Result:
Conclusion
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.
