Demand History Outlier Script

The script to remove Demand History Outliers will replace the quantity for Demand History based on 5 parameters. The script will then rebuild the Part Details in RPlus.

1) locations - List of locations. If blank, it will run all locations. 2) days - Number of days in the past from the current date on the server. 3) selectionMultiplier - Multipled by ADU to determine records to modify. 4) quantityMultiplier - Reduce the Quantity to this number multiplied by the ADU. 5) rplusSite - Instance of RPlus.

Notes: 1) The ADU is based on the last ADU from Part History. Part History is calculated each night usually at Midnight and is the ADU that exists at that time. If the Part is not in Part History the ADU from Part Details will be used.

Script

IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = N'ADX' ) EXEC('CREATE SCHEMA [ADX]');

IF NOT EXISTS ( select * from INFORMATION_SCHEMA.tables where table_schema = 'ADX' and table_name = 'DemandHistoriesUpdates' ) 
Begin
  CREATE Table ADX.DemandHistoriesUpdates (
      Date datetime,
      Days int,
      SelectionMultiplier int,
      QuantityMultiplier int,
      Locations varchar(MAX),
      ServerName varchar(50),
      DatabaseName varchar(50),
      Id int,
      PartNumber nvarchar(50),
      Revision nvarchar(10),
      Location nvarchar(20),
      PartDetailsADU decimal(18,5),
      PartHistADU decimal(18,5),
      OrderDate datetime,
      OrigQuantity decimal(18,5),
      NewQuantity decimal(18,5)
  )
 End

Declare @days as int = -8;
Declare @selectionMultiplier as int = 4;
Declare @quantityMultiplier as int = 1;
Declare @locations as varchar(MAX) = '';

update DemandHistories
set Quantity = (coalesce(ph.AverageDailyUsage,pd.AverageDailyUsage)*@quantityMultiplier)
Output 
    GETDATE() [Date]
    ,@days [Days]
    ,@selectionMultiplier [SelectionMultiplier]
    ,@quantityMultiplier [QuantityMultiplier]
    ,@locations [Locations]
    ,@@SERVERNAME [ServerName]
    ,db_name() [DatabaseName]
    ,deleted.id [Id]
    ,p.PartNumber [PartNumber]
    ,p.Revision [Revision]
    ,p.Location [Location]
    ,pd.AverageDailyUsage [PartDetailsADU]
    ,ph.AverageDailyUsage [PartHistADU]
    ,deleted.OrderDate [OrderDate]
    ,deleted.Quantity [OrigQuantity]
    ,inserted.Quantity [NewQuantity]
  INTO ADX.DemandHistoriesUpdates
FROM DemandHistories dh
  join parts p on p.id=dh.id
  join partdetails pd on pd.id=dh.id
  left join (select
				ph.PartNumber,ph.Revision,ph.Location,ph.AverageDailyUsage
			from PartHistories ph
			inner join 
				(select 
					partnumber, Revision, Location,max(date) [max_date]
				from PartHistories
				group by partnumber, Revision, Location
				) md on md.PartNumber = ph.PartNumber and md.Revision=ph.Revision and md.Location=ph.Location and md.max_date=ph.date
		) ph on ph.PartNumber=p.PartNumber and ph.Revision=p.Revision and ph.Location=p.Location
where 1=1
	and (len(@locations)=0
          or
        p.Location in (select value from STRING_SPLIT(@locations,','))
      )
    and dh.OrderDate > dateadd(day,@days,getdate())
	and (coalesce(ph.AverageDailyUsage,pd.AverageDailyUsage)*@selectionMultiplier) < dh.Quantity
	and (coalesce(ph.AverageDailyUsage,pd.AverageDailyUsage)*@selectionMultiplier) > 0

This script is currently run daily for 4 different sets of parameters.

APAC 17:40 EST locations = 'DAT3' days = 7 selectionMultiplier = 4 quantityMultiplier = 1 rplusSite = aptiv-apac.demanddriventech.com

APAC_2201 17:40 EST locations = '2201' days = 5 selectionMultiplier = 4 quantityMultiplier = 1 rplusSite = aptiv-apac.demanddriventech.com

EMEA 00:40 EST locations = 'PL60,MA82,MA81' days = 7 selectionMultiplier = 4 quantityMultiplier = 1 rplusSite = aptiv-emea.demanddriventech.com

NA runs at 03:00 EST locations = '' days = 7 selectionMultiplier = 4 quantityMultiplier = 1 rplusSite = aptiv.demanddriventech.com