Bill of Materials Notification
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" <email@example.com>', @subj, @body, 0); -- add recipients to email exec Boomerang..sp_Add_Email_List@jKey, 'firstname.lastname@example.org, email@example.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:
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
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.
This application scenario has been modified to fit MS-SQL sample database AdventureWorks.