  
CREATE PROCEDURE [dbo].[MXC_Forecast_GetOrderItemForecastByDateRangeWithProcessingTimes_Orig]  
 @entityId BIGINT  
 , @startDate DATETIME  
 , @endDate DATETIME  
 , @serviceTypeIds VARCHAR(100)  
AS   
  
BEGIN  
  
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
  
DECLARE @serviceTypes TABLE (TypeId INT)    
 INSERT INTO @serviceTypes    
 SELECT [Value] FROM [dbo].[fn_split](@serviceTypeIds, ',')    
    
 IF object_id ('#entityItem') IS NOT NULL    
  DROP TABLE tempdb..#entityItem    
    
 CREATE TABLE #entityItem (ItemID BIGINT, ProcessingTime TinyInt)    
 INSERT INTO #entityItem (ItemID, ProcessingTime)    
  select ItemID,ProcessingTime from tbEntityitem ei where entityid = @entityId AND ei.UseProcessingTime = 1 AND ei.ProcessingTime > 0    
    
 DECLARE @tmpForecast TABLE (ForecastID BIGINT)    
    
 DECLARE @tmp TABLE (InventoryItemId BIGINT, ServiceType INT, RawTransactionCount FLOAT, SystemTransactionCount FLOAT, ManagerTransactionCount FLOAT, LastYearTransactionCount FLOAT, Excluded BIT)    
    
 DECLARE @closedDays UDT_ClosedDates    
    
 INSERT INTO @closedDays(ClosedDate)    
 SELECT DayDate FROM dbo.fn_GetEntityClosedDaysInRange(@entityId, @startDate, DATEADD(DAY, 4, @endDate))    
     
 DECLARE @AdjustedStart datetime    
   ,@adjustedEnd datetime    
 SELECT @AdjustedStart = dbo.fn_AdjustDateForItemProcessTime(@entityId, DATEADD(DAY,-1,@startDate), (select MIN(processingtime) from #entityItem ), @closedDays)     
 SELECT @adjustedEnd = dbo.fn_AdjustDateForItemProcessTime(@entityId, @endDate, (select MAX(processingtime) from #entityItem), @closedDays)    
    
 INSERT INTO @tmpForecast (ForecastID)    
 SELECT ForecastID    
 FROM (    
   SELECT ForecastID, ROW_NUMBER() OVER (PARTITION BY EntityID, BusinessDay ORDER BY GenerationDate DESC) RowNumber    
   FROM Forecasting.Forecast    
   WHERE EntityID = @entityId     
   AND BusinessDay BETWEEN CONVERT(date, @startDate) AND DATEADD(DAY, 4, CONVERT(date, @endDate)) -- Shift forecast range out 4 days to cater for our max 48hrs processing time +2 closed days    
   ) f    
 WHERE RowNumber = 1    
     
 INSERT INTO @tmp (InventoryItemId, ServiceType, RawTransactionCount, SystemTransactionCount, ManagerTransactionCount, LastYearTransactionCount)    
 SELECT i.InventoryItemId    
  , i.ServiceType    
  , SUM(i.RawTransactionCount) AS RawTransactionCount    
  , SUM(i.SystemTransactionCount) AS SystemTransactionCount    
  , SUM(i.ManagerTransactionCount) AS ManagerTransactionCount    
  , SUM(i.LastYearTransactionCount) AS LastYearTransactionCount    
 FROM @tmpForecast f    
 JOIN [Forecasting].[InventoryItemMetricDetail] i ON ( f.ForecastId = i.ForecastId )    
 AND i.IntervalStart BETWEEN @startDate AND @endDate    
 AND i.InventoryItemId NOT IN (SELECT ItemId FROM [dbo].[tbEntityItem] WHERE EntityId = @entityId AND UseProcessingTime = 1 AND ProcessingTime > 0)    
 GROUP BY i.InventoryItemId, i.ServiceType    
    
 INSERT INTO @tmp (InventoryItemId, ServiceType, RawTransactionCount, SystemTransactionCount, ManagerTransactionCount, LastYearTransactionCount)    
 SELECT i.InventoryItemId    
  , i.ServiceType    
  , SUM(i.RawTransactionCount) AS RawTransactionCount    
  , SUM(i.SystemTransactionCount) AS SystemTransactionCount    
  , SUM(i.ManagerTransactionCount) AS ManagerTransactionCount    
  , SUM(i.LastYearTransactionCount) AS LastYearTransactionCount    
 FROM @tmpForecast f    
 JOIN [Forecasting].[InventoryItemMetricDetail] i ON ( f.ForecastId = i.ForecastId )    
 JOIN #entityItem ei ON (ei.ItemId = i.InventoryItemId)    
 WHERE i.IntervalStart >   @AdjustedStart and     
 i.IntervalStart  <= @adjustedEnd    
 AND i.IntervalStart     
 BETWEEN    
  dbo.fn_AdjustDateForItemProcessTime(@entityId, @startDate, ei.ProcessingTime, @closedDays) AND     
  dbo.fn_AdjustDateForItemProcessTime(@entityId, @endDate, ei.ProcessingTime, @closedDays)    
 GROUP BY i.InventoryItemId, i.ServiceType    
    
 UPDATE @tmp    
 SET Excluded = CAST(CASE WHEN ServiceType IN (SELECT TypeId FROM @serviceTypes) THEN 1 ELSE 0 END AS BIT)    
    
 SELECT InventoryItemId    
  , Excluded    
  , SUM(RawTransactionCount) AS RawTransactionCount    
  , SUM(SystemTransactionCount) AS SystemTransactionCount    
  , SUM(ManagerTransactionCount) AS ManagerTransactionCount    
  , SUM(LastYearTransactionCount) AS LastYearTransactionCount    
 FROM @tmp    
 GROUP BY InventoryItemId, Excluded    
  
END  