if object_id('tempdb..#FilialLager') is not null drop table #FilialLager
Select  
df.FilID,
lass.LagerId,
MAX(case when dsc.TypeFormulaID in (10,14,21) then 1 else 0 end) as Ovoch,
MAX(case when dsc.TypeFormulaID in (11,13) then 1 else 0 end) as Tort,
case when MAX(isnull(ltf.[TypePrognoz],(Isnull(lfp.[TypePrognoz],
case when dsc.TypeFormulaID in (10,14,21,13,5) then 3 else (CASE WHEN df.FilID = ISNULL(@Fil_idParam59, 0) AND 
l.macroid in (13,15,16,20,21,23,26,29,35,36,37,38,39,40,41,42,43,45,49,50,51,52,53,56,62,65,67,70,73,77,80,81,84,87) THEN 4 
WHEN l.macroid in (13,15,16,17,20,21,23,26,27,29,35,36,37,38,39,40,41,42,43,45,49,50,51,52,53,56,62,65,67,70,73,75,77,80,81,84,87,88,89,105,109) THEN 2 
WHEN  @Type <> 1 THEN 2
ELSE 4 END ) end)))) in (4, 9) then 1 else 0 end as RegularSales,
MAX(isnull(ltf.[TypePrognoz],(Isnull(lfp.[TypePrognoz],
case when dsc.TypeFormulaID in (10,14,21,13,5) then 3 else (CASE WHEN df.FilID = ISNULL(@Fil_idParam59, 0) AND 
l.macroid in (13,15,16,20,21,23,26,29,35,36,37,38,39,40,41,42,43,45,49,50,51,52,53,56,62,65,67,70,73,77,80,81,84,87) THEN 4 
WHEN l.macroid in (13,15,16,17,20,21,23,26,27,29,35,36,37,38,39,40,41,42,43,45,49,50,51,52,53,56,62,65,67,70,73,75,77,80,81,84,87,88,89,105,109) THEN 2 
WHEN 1=1
and @Type <> 1 THEN 2
ELSE 4 END ) end)))) as TypePrognoz,
MAX(l.ClassID) as EKT,
@macroRegionId as macroRegionId
Into #FilialLager		
From cb.dbo.DogovorFilials df with(nolock)
JOIN cb.dbo.DogovorArticles da with(nolock) ON df.DogovorID=da.DogovorId	
JOIN cb.dbo.LagerAssortiment lass with(nolock) ON df.FilID=lass.filId and da.LagerID=lass.LagerId	
JOIN cb.dbo.Lager l with(nolock) ON lass.LagerId=l.id
JOIN cb.dbo.DogovorServiceContracts dsc with(nolock) ON df.DogovorID=dsc.DogovorID
LEFT JOIN cb.[dbo].[LagerTypeFormulaPrognozByFil] ltf with(nolock) ON ltf.[LagerId]=lass.LagerId and lass.filId=ltf.[FilID]
LEFT JOIN cb.dbo.LagerTypeFormulaPrognozManual lfp with(nolock) ON lfp.[BusinessID]=@Type and lfp.[LagerId]=lass.LagerId
Where 1=1
and lass.SAPSTATUS is null
and df.FilID = @FilID
Group By  df.FilID,  lass.LagerId

Declare @DatePrognoz datetime
DECLARE @Fil_idParam59 INT --OAB: 20171023
-- ================================================================
Declare @FilID int, @Type int, @macroRegionId int
DECLARE cFilial CURSOR FOR 
		Select ID, Type, macroRegionId From #FilialMail  --!!!
		
Open cFilial

 FETCH NEXT FROM cFilial
  INTO @FilID, @Type, @macroRegionId

WHILE @@Fetch_Status=0  
   BEGIN		
--------------------------------------------------------------------
	Print @FilID
		
		/* OAB: 20171023 #36729 */
		SET @Fil_idParam59 = 0
		SELECT @Fil_idParam59=gpl.ValueInt
		FROM cb..GlobalParams gp with(nolock)
			INNER JOIN cb..GlobalParamsLines gpl with(nolock) ON gp.idParams=gpl.idParams
		WHERE gp.ParamName = 'FIL_FourWeeksForecastRefinement' --Исключение филиалов из фильтра для 4-хнедельного прогноза
				AND gp.Enabled = 1 AND gpl.Enabled = 1
				 AND gpl.ValueInt = @FilID

if object_id('tempdb..#FilialLager') is not null drop table #FilialLager
Select  
df.FilID,
lass.LagerId,
MAX(case when dsc.TypeFormulaID in (10,14,21) then 1 else 0 end) as Ovoch,
MAX(case when dsc.TypeFormulaID in (11,13) then 1 else 0 end) as Tort,
case when MAX(isnull(ltf.[TypePrognoz],(Isnull(lfp.[TypePrognoz],
case when dsc.TypeFormulaID in (10,14,21,13,5) then 3 else (CASE WHEN df.FilID = ISNULL(@Fil_idParam59, 0) AND 
l.macroid in (13,15,16,20,21,23,26,29,35,36,37,38,39,40,41,42,43,45,49,50,51,52,53,56,62,65,67,70,73,77,80,81,84,87) THEN 4 
WHEN l.macroid in (13,15,16,17,20,21,23,26,27,29,35,36,37,38,39,40,41,42,43,45,49,50,51,52,53,56,62,65,67,70,73,75,77,80,81,84,87,88,89,105,109) THEN 2 
WHEN  @Type <> 1 THEN 2
ELSE 4 END ) end)))) in (4, 9) then 1 else 0 end as RegularSales,
MAX(isnull(ltf.[TypePrognoz],(Isnull(lfp.[TypePrognoz],
case when dsc.TypeFormulaID in (10,14,21,13,5) then 3 else (CASE WHEN df.FilID = ISNULL(@Fil_idParam59, 0) AND 
l.macroid in (13,15,16,20,21,23,26,29,35,36,37,38,39,40,41,42,43,45,49,50,51,52,53,56,62,65,67,70,73,77,80,81,84,87) THEN 4 
WHEN l.macroid in (13,15,16,17,20,21,23,26,27,29,35,36,37,38,39,40,41,42,43,45,49,50,51,52,53,56,62,65,67,70,73,75,77,80,81,84,87,88,89,105,109) THEN 2 
WHEN 1=1
and @Type <> 1 THEN 2
ELSE 4 END ) end)))) as TypePrognoz,
MAX(l.ClassID) as EKT,
@macroRegionId as macroRegionId
Into #FilialLager		
From cb.dbo.DogovorFilials df with(nolock)
JOIN cb.dbo.DogovorArticles da with(nolock) ON df.DogovorID=da.DogovorId	
JOIN cb.dbo.LagerAssortiment lass with(nolock) ON df.FilID=lass.filId and da.LagerID=lass.LagerId	
JOIN cb.dbo.Lager l with(nolock) ON lass.LagerId=l.id
JOIN cb.dbo.DogovorServiceContracts dsc with(nolock) ON df.DogovorID=dsc.DogovorID
LEFT JOIN cb.[dbo].[LagerTypeFormulaPrognozByFil] ltf with(nolock) ON ltf.[LagerId]=lass.LagerId and lass.filId=ltf.[FilID]
LEFT JOIN cb.dbo.LagerTypeFormulaPrognozManual lfp with(nolock) ON lfp.[BusinessID]=@Type and lfp.[LagerId]=lass.LagerId
Where 1=1
and lass.SAPSTATUS is null
and df.FilID = @FilID
Group By  df.FilID,  lass.LagerId