Bill of Material Notification | Boomerang Notification Services for SQL Server

Bill of Materials Notification

Background

There are many pitfalls in the area of Bill of Materials maintenance. BOM’s may be maintained in multiple systems that are partly or not at all integrated. There may be a disconnect between PDM and ERP systems or, as in this case, information vital to the BOM change process that is not properly utilized within the same system.

In this specific case items (SKU) scheduled to be discontinued were added to a BOM. This created a problem if the scheduled date was reached and the BOM still containing the discontinued component.

Solution – Bill of Material Notification

In order to effectively utilize already existing product data a Boomerang Notification was added to the BOM maintenance application. In this example it is illustrated as a Add BOM stored procedure that will send a notification every time a BOM is manipulated. An e-mail will be sent to notify engineering staff that:

  • A change occurred.
  • The added product (SKU) will reach its “End of Sales” date or being discontinued.

The notification is in this sample is embedded in a the stored procedure that adds BOM lines. Top item (parent item) and component (child item) together with BOM quantity and unit of measure will be passed to the stored procedure.

Adding BOM Lines

The first part of the stored procedure sets up the parameters and handles the insert of a new BOM line. The section highlighted in gray color retrieves values for the variables that is going to end up in the email body.

-------- Bill of Material Notification --------------------------
USE [AdventureWorks]
Create proc [Production].[Add_BOM](@Parent asint, @Child asint, @BOMQty decimal (8,2), @UoM nchar(3))as
begin
declare @StartDate datetime;set @StartDate =GETDATE()-- BOM Start Date
declare @ModifiedDate datetime;set @ModifiedDate =GETDATE()-- BOM Modified Date
declare @BOMLevel smallint;-- BOM level
declare @SellEndDate datetime-- Sales end date
declare @DiscontinuedDate datetime-- Components discontined date
declare @Name asnvarchar(50)-- Components description
 
-- Look up components level in the BOM to be used when inserting new BOM line
select top 1 @BOMLevel = BOMLevel from Production.BillOfMaterials where EndDate isnulland ProductAssemblyID = @Parent
 
-- Look up Sales End Date, discontinued date and description from Product table
select @SellEndDate = SellEndDate, @DiscontinuedDate = DiscontinuedDate, @Name = Name from Production.Product where ProductID = @Child
 
-- Abort and raise error if parent and child combination already exist in BOM
if exists(select 1 from Production.BillOfMaterials where ProductAssemblyID = @Parent and ComponentID = @Child)
begin raiserror('Item already exist in BOM',11,1)withseterror;return 1 end
 
-- Insert new BOM line
insert Production.BillOfMaterials (ProductAssemblyID, ComponentID, StartDate, UnitMeasureCode, BOMLevel, PerAssemblyQty, ModifiedDate)
values (@Parent, @Child, @StartDate, @UoM, @BOMLevel, @BOMQty, @ModifiedDate)
 
-------- Bill of Material Notification --------------------------

Creating an Email Notification – Step 1

In the second half of the stored procedure we are setting up the notification. To begin with we declare the necessary variables including the unique identifiers that are expected in necessary Boomerang tables; EVENT_MASTER, OUT_EMAIL and OUT_EMAIL_RECIPEINT. Secondly we construct the email body by using static text, variables, line feed (char(10)) and carriage return (char(13)).

-------- Bill of Material Notification --------------------------
-------- Begin Email Notification -------------------------------
declare @gKey uniqueidentifier; set @gKey = newid(); -- Boomerang Key
declare @jKey uniqueidentifier; set @jKey = newid(); -- Boomerang Key
declare @subj varchar(100); set @subj = 'BOM Notification'; -- Email Subject
declare @body varchar(1500); -- Email Body
 
-- Set email body. Add lines if sales end date and discontinued date exist
set @body = 'This is to notify you that the component ' + convert(varchar(10), @Child)+ ' | ' + @Name + ' has been added to BOM '
            + convert(varchar(10), @Parent) + ' at ' + convert(varchar(20), @ModifiedDate, 101) + ':'
		    if(@SellEndDate is not null) set @body = @body + char(13) + char(10) + '* component has a Sell End Date of ' + convert(varchar (20), @SellEndDate, 101)
		    if(@DiscontinuedDate is not null) set @body = @body + char(13) + char(10) + '* component will be Discontinued on ' + CONVERT(varchar(20), @DiscontinuedDate, 101);
print @body
------------------------------------------------------------------

The output of this section i.e. print @body will look like something like this if component has a end of sales and discontinued -date:

This is to notify you that the component 845 | Mountain Pump has been added to BOM 732 at 03/15/2012:
* component has a Sell End Date of 06/30/2003
* component will be Discontinued on 08/11/2012

Creating an Email Notification – Step 2

First we create a new event in the EVENT_MASTER table. Since this is a email notification we insert a record in OUT_EMAIL. Next we add recipients to the email. In this case we use one of the built in helpers Boomerang..sp_Add_Email_List. Another option would have been to insert one or more records directly into OUT_EMAIL_RECIPIENT. Finally we update the EVENT_MASTER. Status to indicate that the notification is ready to be processed.

-------- Bill of Material Notification --------------------------
------- Create new event by inserting a row in event_master -----
insert Boomerang..EVENT_MASTER(gKey, Str1, Created_By,Source)
values (@gKey,'BOM Notification','Production.Add_BOM', 4);
 
-- Create email our event
insert Boomerang..OUT_EMAIL(gKey, jKey, [From],Subject, Body, IncludeKey)
values (@gKey, @jKey,'"Production Notification Services" <info@fuel9.com>', @subj, @body, 0);
 
-- add recipients to email
exec Boomerang..sp_Add_Email_List@jKey, 'bob.smith@bom.com, tim.byron@bom.com'
 
-- release event
update Boomerang..EVENT_MASTER setStatus=0 where gKey=@gKey;
 
end

Execute Stored Procedure

To test the stored procure run the following code snippet which will add component 845 to the BOM of top item 732 with the quantify 10 and unit each.

exec Production.Add_BOM732, 845, 10,'EA'

The email message will look something like this:

BOM Alert | Boomerang Notification Services for SQL Server

Bill of Material Notification | Boomerang Notification Framwork

Delaying Notification Message

In the above scenario a message is sent right after a BOM is changed but let’s say we would like to hold off the notification until the recently added component are close to being expired. In other words, how to dynamically schedule the notification based on known product information. We also like to add a condition, based on the same information, whether to send a message or not.

-------- Validate whether to send a notification or not
if @SellEndDate is not null or @DiscontinuedDate is not null
begin
   -- Begin Email Notification -------------------
   declare @gKey uniqueidentifier;set @gKey =newid();-- Boomerang Key
   declare @jKey uniqueidentifier;set @jKey =newid();-- Boomerang Key

To delay sending the message until the component is about to be discontinued could look like this:

-------- Set delayed delivery for notification ------------------
update Boomerang..EVENT_STATUS set Run_When =dateadd(day,-1, @DiscontinuedDate)where gKey = @gKey and jKey = @jKey -- send message 1 day before being discontinued
 
-- release event ---------------------

The following message will now be sent on August 10 2012:

This is to notify you that the component 845 | Mountain Pump added to BOM 732 on 03/16/2012 needs to be replaced before: 08/11/2012
* component has a Sell End Date of 06/30/2003
* component will be Discontinued on 08/11/2012

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 notification framework IT Professionals can create robust and dynamic notification solution with a minimum amount of T-SQL code. As demonstrated above we created a tailored Bill of Material Notification using typical supply chain information widely available to bridge short comings in available inventory and production management system.

Scenario Information

This application scenario has been modified to fit MS-SQL sample database AdventureWorks.

Download Sample

Bill of Material notification sample