Product End of Life Alert

Introduction

Most inventory management systems today has a way to handle discontinued products in some fashion but with an ever more complex supply chains available solutions are not always meeting business requirements sufficiently enough. This may result in that a part scheduled to be discontinued ends up on a new purchase order and subsequently you end up with inventory of unwanted parts (SKU).

In this case study we are going to demonstrate how with the help of the Boomerang notification framework you can create a robust End of Life notification solution with commonly available inventory information like:

• Inventory Balance
• Open Purchase Orders
• In Transit Inventory

Solution – End of Life Notification

If a product needs to be discontinued i.e. reached its end of life it is tagged in the product master to be discontinued. This will put the product on the EOL “short list” where on hand inventory and purchase order status are going to be monitored.

Once inventory has been depleted and there are no more purchase orders expected to be received the product can be discontinued and users notified. This is done with a SQL stored procedure that is scheduled to run continuously during hours of operation.

Current Inventory and Open Purchase Orders

The first part of the stored procedure gathers data from our ERP systems database (AdventureWorks). This data is needed to determine if any particular product that is tagged to be discontinued can be closed out. To answer this we are looking at on hand balance and if open purchase orders exists. We are also preparing for the email notification in the next step by declaring needed keys (gKey, jKey and aKey). The result set is inserted into a temporary table named #_tmp_EOL.

CREATE proc [Production].[EOL_Notification] as
/*
2012-04-18	End of life Demo
 
exec Production.EOL_Notification
*/
set xact_abort on
set nocount on
begin
 
select 
 p.ProductID
,p.Name
,p.ProductNumber
,p.ToBeDiscontinued
,p.DiscontinuedDate
,inv.Inventory_Balance
,po.On_PO
,NEWID() as gKey 
,NEWID() as jKey 
,NEWID() as aKey 
into #_tmp_EOL
from Production.Product p
left join 
	( -- Inventory balanace
	select ProductID, SUM(Quantity) as Inventory_Balance
	from Production.ProductInventory prin
	group by ProductID
	) inv on inv.ProductID = p.ProductID
left join 
	( -- purchase orders
	select ProductID, sum((ReceivedQty+RejectedQty)-OrderQty) as On_PO 
	from Purchasing.PurchaseOrderDetail 
	group by ProductID
	) po on po.ProductID = p.ProductID
 
where p.ToBeDiscontinued = 1 -- Flagged to be discontinued
and p.DiscontinuedDate is null -- Not yet discontinued
and (inv.Inventory_Balance = 0 or inv.Inventory_Balance is null) -- No inventory
and (po.On_PO = 0 or po.On_PO is null) -- No open purchase order

Result from:

select * from #_tmp_EOL

EOL Alert/Notification

If any records are returned we proceed by creating a new Boomerang event by inserting a record into EVENT_MASTER. Since we are going to send out a notification by email a record is also added to OUT_EMAIL and OUT_EMAIL_RECIPIENT. Having a unique OUT_EMAIL.jKey for each product id being discontinued is also going create an email for each product discontinued.

A SSRS report is this case used to render and display content for the notification, subsequently a record is added to EVENT_CONTENT and CONTENT_PARAMETERS with the path to the SSRS report (Boomerang_Demo/End Of Life Notification) and applicable parameter (ProductID). Lastly we update the Product database with the date when this product got discontinued and releasing the event to be processed. This is done by setting EVENT_MASTER.Status to 0 (zero).

-- End of Life Notification --------------------------------
-- Bail if there are no items to process
if @@rowcount = 0 begin return end
 
	-- Send notification out
	insert Boomerang.dbo.EVENT_MASTER (gKey, Created_By) select gKey, 'store proc: Production.EOL_Notification' from #_tmp_EOL
	-- Email Out
	insert Boomerang.dbo.OUT_EMAIL (gKey, jKey, [Subject], IncludeKey) 
	select gKey, jKey, 'End of Life Notification for Product Id #: ' + convert(varchar(10), ProductID), 0 from #_tmp_EOL 
 
	-- Email recipients 
	insert Boomerang.dbo.OUT_EMAIL_RECIPIENT (jKey, Email) 
	select jKey, '' from #_tmp_EOL 
 
	-- SSRS report path
	insert Boomerang.dbo.EVENT_CONTENT (gKey, jKey, aKey, Src_Type, [Path], IsAttachment) 
	select gKey, jKey, aKey, 2, 'Boomerang_Demo/End Of Life Notification', 0 from #_tmp_EOL 
 
	-- SSRS report parameters
	insert Boomerang.dbo.CONTENT_PARAMETER (aKey, Name, Value) 
	select aKey, 'ProductID', ProductID from #_tmp_EOL 
 
	-- Update Product Master with data discontinued
	update Production.Product 
	set DiscontinuedDate = GETDATE()
	where ProductID in (select ProductID from #_tmp_EOL)
 
	-- Release event to be sent off
	update Boomerang.dbo.EVENT_MASTER
	set Status=0
	where gKey in (select gKey from #_tmp_EOL)
 
end

Result

The end of life email message will look like this:

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 tailored End of Life process using typically supply chain information widely available to bridge short comings in many inventory management system.

Scenario Information

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

Download Sample

Product End of Life Sample