Product End of Life Alert
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
select * from #_tmp_EOL
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
The end of life email message will look like this:
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.
This application scenario has been modified to fit MS-SQL sample database AdventureWorks.