1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
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
