· 7 years ago · Jan 29, 2019, 05:54 PM
1USE [btprod]
2GO
3/****** Object: StoredProcedure [dbo].[CalculateStockAgeAndTurnsAndGMROI] Script Date: 1/29/2019 10:37:07 AM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER OFF
7GO
8ALTER PROCEDURE [dbo].[CalculateStockAgeAndTurnsAndGMROI]
9 @ForProductID INT = NULL,
10 @BranchID INT = NULL,
11 @ProductGroupID INT = NULL,
12 @UpdatePermanentTables BIT = 1,
13 @OverrideStartDate DATE = NULL,
14 @OverrideEndDate DATE = NULL
15AS
16SET NOCOUNT ON
17SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
18
19IF (@OverrideStartDate IS NOT NULL OR @OverrideEndDate IS NOT NULL) AND @UpdatePermanentTables = 1
20BEGIN
21 RAISERROR('You cannot override the dates and update the permanent tables.', 16, -1)
22 RETURN
23END
24
25DECLARE @StartDate DATE
26DECLARE @Today DATE
27DECLARE @Now DATETIME
28DECLARE @EndDate DATE
29DECLARE @ProductDefaultStocked BIT
30
31CREATE TABLE #ProductStart
32 (ProductID INT, BranchID INT, StartDate DATE)
33
34CREATE TABLE #Product
35 (ProductID INT, BranchID INT,
36 StartDate DATE,
37 DaysAnalysed INT NOT NULL DEFAULT 0,
38 AverageValuation MONEY NOT NULL DEFAULT 0,
39 COGS MONEY NOT NULL DEFAULT 0,
40 SalesMargin MONEY NOT NULL DEFAULT 0,
41 FullStockTurn NUMERIC(18,1) NOT NULL DEFAULT 0,
42 FullStockAge NUMERIC(18,1) NOT NULL DEFAULT 0,
43 GMROI NUMERIC(18,1) NOT NULL DEFAULT 0,
44 PeriodsAnalysed INT NOT NULL DEFAULT 0)
45
46CREATE TABLE #ProductGroup1
47 (ProductGroupID INT, BranchID INT,
48 COGSPerYear MONEY NOT NULL DEFAULT 0,
49 AverageValuation MONEY NOT NULL DEFAULT 0,
50 SalesMargin MONEY NOT NULL DEFAULT 0,
51 PeriodsAnalysed INT NOT NULL DEFAULT 0)
52
53CREATE TABLE #ProductGroup2
54 (ProductGroupID INT, BranchID INT,
55 COGSPerYear MONEY NOT NULL DEFAULT 0,
56 AverageValuation MONEY NOT NULL DEFAULT 0,
57 SalesMargin MONEY NOT NULL DEFAULT 0,
58 PeriodsAnalysed INT NOT NULL DEFAULT 0)
59
60CREATE TABLE #ProductGroup3
61 (ProductGroupID INT, BranchID INT,
62 COGSPerYear MONEY NOT NULL DEFAULT 0,
63 AverageValuation MONEY NOT NULL DEFAULT 0,
64 SalesMargin MONEY NOT NULL DEFAULT 0,
65 FullStockTurn NUMERIC(18,1) NOT NULL DEFAULT 0,
66 FullStockAge NUMERIC(18,1) NOT NULL DEFAULT 0,
67 GMROI NUMERIC(18,1) NOT NULL DEFAULT 0,
68 PeriodsAnalysed INT NOT NULL DEFAULT 0)
69
70CREATE TABLE #BranchLevel
71 (BranchID INT,
72 COGSPerYear MONEY NOT NULL DEFAULT 0,
73 AverageValuation MONEY NOT NULL DEFAULT 0,
74 SalesMargin MONEY NOT NULL DEFAULT 0,
75 FullStockTurn NUMERIC(18,1) NOT NULL DEFAULT 0,
76 FullStockAge NUMERIC(18,1) NOT NULL DEFAULT 0,
77 GMROI NUMERIC(18,1) NOT NULL DEFAULT 0,
78 PeriodsAnalysed INT NOT NULL DEFAULT 0)
79
80CREATE TABLE #CompanyLevel
81 (COGSPerYear MONEY NOT NULL DEFAULT 0,
82 AverageValuation MONEY NOT NULL DEFAULT 0,
83 SalesMargin MONEY NOT NULL DEFAULT 0,
84 FullStockTurn NUMERIC(18,1) NOT NULL DEFAULT 0,
85 FullStockAge NUMERIC(18,1) NOT NULL DEFAULT 0,
86 GMROI NUMERIC(18,1) NOT NULL DEFAULT 0,
87 PeriodsAnalysed INT NOT NULL DEFAULT 0)
88
89CREATE TABLE #ProductValuationDate
90 (ValProductID INT,
91 ValBranchID INT,
92 ValDateTime DATETIME,
93 ValStartDate DATE,
94 ValEndDate DATE,
95 ValDays INT NOT NULL DEFAULT 0,
96 Valuation MONEY NOT NULL DEFAULT 0)
97
98CREATE TABLE #ProductAnalysisSales
99 (PAProductID INT, PABranchID INT, PASalesMargin MONEY)
100
101CREATE TABLE #ProductAnalysisCost
102 (PAProductID INT, PABranchID INT, PATotalCost MONEY)
103
104CREATE TABLE #Output
105 (Type varchar(20) COLLATE database_default,
106 ProductID INT, ProductGroupID INT, BranchID INT,
107 StartDate DATE, EndDate DATE,
108 DaysAnalysed INT,
109 ProductCOGS MONEY,
110 COGSPerYear MONEY ,
111 AverageValuation MONEY ,
112 SalesMargin MONEY ,
113 FullStockTurn NUMERIC(18,1) ,
114 FullStockAge NUMERIC(18,1) ,
115 GMROI NUMERIC(18,1) ,
116 PeriodsAnalysed INT )
117
118 SET @Now = GETDATE() -- Current DateTime
119 SET @Today = @Now -- Current Date
120
121 IF @OverrideStartDate IS NULL SET @StartDate = DATEADD(DD,-365,@Today) ELSE SET @StartDate = @OverrideStartDate
122
123 -- We are adding a day here because the queries below use < with @EndDate
124 IF @OverrideEndDate IS NULL SET @EndDate = DATEADD(DD,1,@Today) ELSE SET @EndDate = DATEADD(DD,1,@OverrideEndDate)
125
126 SET @ProductDefaultStocked = dbo.psiGetSystemOptionValue(13018) -- soidProductDefaultStocked = 13018
127
128 INSERT #ProductStart
129 (ProductID, BranchID, StartDate)
130 SELECT P.ProductID, B.BranchID, @StartDate
131 FROM Product P
132 CROSS JOIN Branch B
133 WHERE (P.ProductID = @ForProductID OR @ForProductID IS NULL)
134 AND (B.BranchID = @BranchID OR @BranchID IS NULL)
135 AND P.ProductType < 100
136 AND P.Deleted = 0
137 AND P.Special = 0
138 AND B.Deleted = 0
139 AND P.ProductSubType <> 1 -- No Parents
140 AND P.ProductSubType <> 4 -- No Kits
141 GROUP BY B.BranchID, P.ProductID
142
143 IF @ProductGroupID IS NOT NULL
144 BEGIN
145 DELETE #ProductStart
146 FROM Product P
147 INNER JOIN ProductGroup PG on PG.ProductGroupID = P.ProductGroupID
148 WHERE P.ProductID = #ProductStart.ProductID
149 AND PG.Level1ID <> @ProductGroupID
150 AND (PG.Level2ID <> @ProductGroupID OR PG.Level2ID IS NULL)
151 AND (PG.Level3ID <> @ProductGroupID OR PG.Level3ID IS NULL)
152 AND (PG.Level4ID <> @ProductGroupID OR PG.Level4ID IS NULL)
153 AND (PG.Level5ID <> @ProductGroupID OR PG.Level5ID IS NULL)
154 END
155
156 INSERT #Product (ProductID, BranchID, StartDate)
157 SELECT P.ProductID, P.BranchID,
158 CASE WHEN MIN(PPH.ChangeDateTime) > MIN(P.StartDate) THEN MIN(PPH.ChangeDateTime) ELSE MIN(P.StartDate) END
159
160 FROM #ProductStart P
161 INNER JOIN ProductPriceHistory2 PPH -- Inner, If we don't have any of these, there is nothing to analyse
162 ON PPH.ProductID = P.ProductID
163 AND PPH.BranchID = P.BranchID
164 AND PPH.Length = 0
165 AND PPH.PriceType = 3
166 GROUP BY P.ProductID, P.BranchID
167
168 DROP TABLE #ProductStart
169
170 -- If the StartDate ends up being greater then the start date of our analysis, we can't analyse it
171 DELETE #Product WHERE StartDate >= @EndDate
172
173 UPDATE #Product
174 SET DaysAnalysed = DATEDIFF(D,StartDate,DATEADD(D,-1,@EndDate)), PeriodsAnalysed = DATEDIFF(M,StartDate,DATEADD(D,-1,@EndDate))
175
176 -- We don't need to analyse rows where DaysAnalysed = 0
177 DELETE #Product WHERE DaysAnalysed = 0
178
179 -- get each product / branch starting valuation
180 INSERT #ProductValuationDate
181 (ValProductID, ValBranchID, ValStartDate, ValDateTime)
182 SELECT P.ProductID, P.BranchID,
183 CASE WHEN PPH.ChangeDateTime <= P.StartDate THEN P.StartDate ELSE CONVERT(DATE,PPH.ChangeDateTime) END,
184 MAX(ChangeDateTime)
185 FROM #Product P
186 LEFT OUTER JOIN ProductPriceHistory2 PPH
187 ON PPH.ProductID = P.ProductID
188 AND PPH.BranchID = P.BranchID
189 AND PPH.Length = 0
190 AND PPH.PriceType = 3
191 AND PPH.ChangeDateTime < @EndDate
192 GROUP BY P.ProductID, P.BranchID, CASE WHEN PPH.ChangeDateTime <= P.StartDate THEN P.StartDate ELSE CONVERT(DATE,PPH.ChangeDateTime) END
193
194 UPDATE #ProductValuationDate
195 SET ValEndDate =
196 (SELECT MIN(PV2.ValStartDate)
197 FROM #ProductValuationDate PV2
198 WHERE PV2.ValProductID = PV1.ValProductID
199 AND PV2.ValBranchID = PV1.ValBranchID
200 AND PV2.ValStartDate > PV1.ValStartDate)
201 FROM #ProductValuationDate PV1
202
203 UPDATE #ProductValuationDate
204 SET ValEndDate = DateAdd(d,-1,@EndDate)
205 WHERE ValEndDate IS NULL
206
207 UPDATE #ProductValuationDate
208 SET ValDays = DateDiff(D,ValStartDate,ValEndDate)
209
210 UPDATE #ProductValuationDate
211 SET Valuation = NewTotalStockValuation
212 FROM ProductPriceHistory2
213 WHERE ProductID = ValProductID
214 AND BranchID = ValBranchID
215 AND Length = 0
216 AND PriceType = 3
217 AND ChangeDateTime = ValDateTime
218 AND ISNULL(NewTotalStockValuation,0) <> 0
219
220 UPDATE #Product
221 SET AverageValuation =
222 ISNULL((SELECT SUM(PVD.ValDays * PVD.Valuation) / SUM(PVD.ValDays)
223 FROM #ProductValuationDate PVD
224 WHERE P.ProductID = PVD.ValProductID AND P.BranchID = PVD.ValBranchID),0)
225 FROM #Product P
226
227 DROP TABLE #ProductValuationDate
228
229 INSERT #ProductAnalysisSales
230 (PAProductID, PABranchID, PASalesMargin)
231 SELECT P.ProductID, P.BranchID, SUM(SA.TotalSales - SA.TotalCost)
232 FROM #Product P
233 INNER JOIN SalesAnalysis3 SA
234 ON SA.ProductID = P.ProductID
235 AND SA.BranchID = P.BranchID
236 AND SA.DocumentDate >= P.StartDate
237 AND SA.DocumentDate < @EndDate
238 AND SA.SaleType <> 4 -- Exclude Directs
239 GROUP BY P.ProductID, P.BranchID
240
241 INSERT #ProductAnalysisCost
242 (PAProductID, PABranchID, PATotalCost)
243 SELECT P.ProductID, P.BranchID, -1 * SUM(ISNULL(PPH.TransactionTotalPriceWithAdd,0))
244 FROM #Product P
245 LEFT JOIN ProductPriceHistory2 PPH
246 ON PPH.ProductID = P.ProductID
247 AND PPH.BranchID = P.BranchID
248 AND PPH.Length = 0
249 AND PPH.PriceType = 3
250 AND PPH.transactionquantity < 0
251 AND ISNULL(PPH.PostingDate, PPH.ChangeDateTime) >= P.StartDate
252 AND ISNULL(PPH.PostingDate, PPH.ChangeDateTime) < @EndDate
253 GROUP BY P.ProductID, P.BranchID
254
255 UPDATE #Product
256 SET SalesMargin = PASalesMargin
257 FROM #ProductAnalysisSales
258 WHERE ProductID = PAProductID
259 AND BranchID = PABranchID
260
261 UPDATE #Product
262 SET COGS = PATotalCost
263 FROM #ProductAnalysisCost
264 WHERE ProductID = PAProductID
265 AND BranchID = PABranchID
266
267 DROP TABLE #ProductAnalysisSales, #ProductAnalysisCost
268
269 -- Turns Per Year = (Total COGS * (365.0 / Actual Days)) / Average Value of Inventory
270 -- Gross Margin = (Total Sales - Total COGS)
271 -- GMROI = Gross Margin / Average Value of Inventory
272 UPDATE #Product
273 SET FullStockTurn = (COGS * (365.0 / DaysAnalysed)) / AverageValuation,
274 GMROI = SalesMargin / AverageValuation
275 WHERE DaysAnalysed <> 0 AND AverageValuation <> 0
276
277 -- Age In Days = 365.0 / (Turns Per Year)
278 UPDATE #Product
279 SET FullStockAge = 365.0 / FullStockTurn
280 WHERE FullStockTurn <> 0
281
282 DELETE #Product
283 WHERE FullStockTurn = 0 and FullStockAge = 0 and GMROI = 0
284
285 -- We can only calculate for product groups when running for all products
286 IF @ForProductID IS NULL
287 BEGIN
288 INSERT INTO #ProductGroup1
289 (ProductGroupID, BranchID, COGSPerYear, AverageValuation, SalesMargin, PeriodsAnalysed)
290 SELECT P.ProductGroupID, TP.BranchID
291 , SUM((TP.COGS * (365.0 / TP.DaysAnalysed))) -- Finding out the Cost per year to use at group & higher level as period could have been short
292 , SUM(TP.AverageValuation), SUM(TP.SalesMargin), MAX(TP.PeriodsAnalysed)
293 FROM #Product TP
294 INNER JOIN Product P
295 ON TP.ProductID = P.ProductID
296 WHERE TP.BranchID <> 0
297 GROUP BY P.ProductGroupID, TP.BranchID
298
299 INSERT INTO #ProductGroup2
300 (ProductGroupID, BranchID, COGSPerYear, AverageValuation, SalesMargin, PeriodsAnalysed)
301 SELECT PG.Level5ID, TPG.BranchID, SUM(TPG.COGSPerYear), SUM(TPG.AverageValuation), SUM(TPG.SalesMargin), MAX(TPG.PeriodsAnalysed)
302 FROM #ProductGroup1 TPG
303 INNER JOIN ProductGroup PG
304 ON PG.ProductGroupID = TPG.ProductGroupID
305 AND PG.Level5ID = TPG.ProductGroupID
306 GROUP BY PG.Level5ID, TPG.BranchID
307
308 INSERT INTO #ProductGroup2
309 (ProductGroupID, BranchID, COGSPerYear, AverageValuation, SalesMargin, PeriodsAnalysed)
310 SELECT PG.Level4ID, TPG.BranchID, SUM(TPG.COGSPerYear), SUM(TPG.AverageValuation), SUM(TPG.SalesMargin), MAX(TPG.PeriodsAnalysed)
311 FROM #ProductGroup1 TPG
312 INNER JOIN ProductGroup PG
313 ON PG.ProductGroupID = TPG.ProductGroupID
314 AND TPG.ProductGroupID IN (PG.Level5ID,PG.Level4ID)
315 GROUP BY PG.Level4ID, TPG.BranchID
316
317 INSERT INTO #ProductGroup2
318 (ProductGroupID, BranchID, COGSPerYear, AverageValuation, SalesMargin, PeriodsAnalysed)
319 SELECT PG.Level3ID, TPG.BranchID, SUM(TPG.COGSPerYear), SUM(TPG.AverageValuation), SUM(TPG.SalesMargin), MAX(TPG.PeriodsAnalysed)
320 FROM #ProductGroup1 TPG
321 INNER JOIN ProductGroup PG
322 ON PG.ProductGroupID = TPG.ProductGroupID
323 AND TPG.ProductGroupID IN (PG.Level5ID,PG.Level4ID,PG.Level3ID)
324 GROUP BY PG.Level3ID, TPG.BranchID
325
326 INSERT INTO #ProductGroup2
327 (ProductGroupID, BranchID, COGSPerYear, AverageValuation, SalesMargin, PeriodsAnalysed)
328 SELECT PG.Level2ID, TPG.BranchID, SUM(TPG.COGSPerYear), SUM(TPG.AverageValuation), SUM(TPG.SalesMargin), MAX(TPG.PeriodsAnalysed)
329 FROM #ProductGroup1 TPG
330 INNER JOIN ProductGroup PG
331 ON PG.ProductGroupID = TPG.ProductGroupID
332 AND TPG.ProductGroupID IN (PG.Level5ID,PG.Level4ID,PG.Level3ID,PG.Level2ID)
333 GROUP BY PG.Level2ID, TPG.BranchID
334
335 INSERT INTO #ProductGroup2
336 (ProductGroupID, BranchID, COGSPerYear, AverageValuation, SalesMargin, PeriodsAnalysed)
337 SELECT PG.Level1ID, TPG.BranchID, SUM(TPG.COGSPerYear), SUM(TPG.AverageValuation), SUM(TPG.SalesMargin), MAX(TPG.PeriodsAnalysed)
338 FROM #ProductGroup1 TPG
339 INNER JOIN ProductGroup PG
340 ON PG.ProductGroupID = TPG.ProductGroupID
341 GROUP BY PG.Level1ID, TPG.BranchID
342
343 INSERT INTO #ProductGroup3
344 (ProductGroupID, BranchID, COGSPerYear, AverageValuation, SalesMargin, PeriodsAnalysed)
345 SELECT PG.ProductGroupID, B.BranchID, SUM(ISNULL(TPG.COGSPerYear,0)), SUM(ISNULL(TPG.AverageValuation,0)), SUM(ISNULL(TPG.SalesMargin,0)), MAX(ISNULL(TPG.PeriodsAnalysed,0))
346 FROM ProductGroup PG
347 CROSS JOIN Branch B
348 LEFT OUTER JOIN #ProductGroup2 TPG
349 ON TPG.ProductGroupID = PG.ProductGroupID
350 AND TPG.BranchID = B.BranchID
351 WHERE PG.Deleted = 0
352 AND (PG.ProductGroupID = @ProductGroupID OR @ProductGroupID IS NULL)
353 AND B.Deleted = 0
354 AND (B.BranchID = @BranchID OR @BranchID IS NULL)
355 GROUP BY PG.ProductGroupID, B.BranchID
356
357 -- Turns Per Year = COGSPerYear / Average Value of Inventory
358 -- Gross Margin = (Total Sales - Total COGS)
359 -- GMROI = Gross Margin / Average Value of Inventory
360 UPDATE #ProductGroup3
361 SET FullStockTurn = COGSPerYear / AverageValuation,
362 GMROI = SalesMargin / AverageValuation
363 WHERE AverageValuation <> 0
364
365 -- Age In Days = 365.0 / (Turns Per Year)
366 UPDATE #ProductGroup3
367 SET FullStockAge = 365.0 / FullStockTurn
368 WHERE FullStockTurn <> 0
369
370 DELETE #ProductGroup3
371 WHERE FullStockTurn = 0 and FullStockAge = 0 and GMROI = 0
372
373 IF @UpdatePermanentTables = 1
374 BEGIN
375 -- Store history
376 INSERT INTO ProductGroupAnalysisHistory
377 (ProductGroupID, BranchID, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, CalculatedDateTime)
378 SELECT ProductGroupID, BranchID, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, @Now
379 FROM #ProductGroup3
380 ORDER BY ProductGroupID, BranchID
381
382 UPDATE ProductGroupAnalysis
383 SET FullStockAge = TPG.FullStockAge,
384 FullStockTurn = TPG.FullStockTurn,
385 GMROI = TPG.GMROI,
386 PeriodsAnalysed = TPG.PeriodsAnalysed,
387 CalculatedDateTime = @Now
388 FROM #ProductGroup3 TPG
389 WHERE ProductGroupAnalysis.ProductGroupID = TPG.ProductGroupID
390 AND ProductGroupAnalysis.BranchID = TPG.BranchID
391
392 DELETE #ProductGroup3
393 FROM ProductGroupAnalysis
394 WHERE ProductGroupAnalysis.ProductGroupID = #ProductGroup3.ProductGroupID
395 AND ProductGroupAnalysis.BranchID = #ProductGroup3.BranchID
396
397 IF EXISTS (SELECT * FROM #ProductGroup3)
398 BEGIN
399 INSERT ProductGroupAnalysis
400 (ProductGroupID, BranchID, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, CalculatedDateTime)
401 SELECT ProductGroupID, BranchID, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, @Now
402 FROM #ProductGroup3
403 ORDER BY ProductGroupID, BranchID
404 END
405 END
406 END
407
408 -- We can only calculate branch level age / turn / GMROI when running for all products and for all product groups
409 IF @ForProductID IS NULL AND @ProductGroupID IS NULL
410 BEGIN
411 INSERT INTO #BranchLevel
412 (BranchID, COGSPerYear, AverageValuation, SalesMargin, PeriodsAnalysed)
413 SELECT TP.BranchID,
414 ISNULL(SUM((TP.COGS * (365.0 / TP.DaysAnalysed))),0),
415 ISNULL(SUM(TP.AverageValuation),0),
416 ISNULL(SUM(TP.SalesMargin),0),
417 ISNULL(MAX(TP.PeriodsAnalysed),0)
418 FROM #Product TP
419 WHERE TP.BranchID <> 0
420 GROUP BY TP.BranchID
421
422 -- Turns Per Year = COGSPerYear / Average Value of Inventory
423 -- Gross Margin = (Total Sales - Total COGS)
424 -- GMROI = Gross Margin / Average Value of Inventory
425 UPDATE #BranchLevel
426 SET FullStockTurn = COGSPerYear / AverageValuation,
427 GMROI = SalesMargin / AverageValuation
428 WHERE AverageValuation <> 0
429
430 -- Age In Days = 365.0 / (Turns Per Year)
431 UPDATE #BranchLevel
432 SET FullStockAge = 365.0 / FullStockTurn
433 WHERE FullStockTurn <> 0
434
435 DELETE #BranchLevel WHERE FullStockTurn = 0 AND FullStockAge = 0 AND GMROI = 0
436
437 IF @UpdatePermanentTables = 1
438 BEGIN
439
440 -- Store history
441 INSERT INTO ProductGroupAnalysisHistory
442 (ProductGroupID, BranchID, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, CalculatedDateTime)
443 SELECT 0, BranchID, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, @Now
444 FROM #BranchLevel
445 ORDER BY BranchID
446
447 UPDATE ProductGroupAnalysis
448 SET FullStockAge = BL.FullStockAge,
449 FullStockTurn = BL.FullStockTurn,
450 GMROI = BL.GMROI,
451 PeriodsAnalysed = BL.PeriodsAnalysed,
452 CalculatedDateTime = @Now
453 FROM #BranchLevel BL
454 WHERE ProductGroupAnalysis.ProductGroupID = 0
455 AND ProductGroupAnalysis.BranchID = BL.BranchID
456
457 DELETE #BranchLevel
458 FROM ProductGroupAnalysis
459 WHERE ProductGroupAnalysis.ProductGroupID = 0
460 AND ProductGroupAnalysis.BranchID = #BranchLevel.BranchID
461
462 IF EXISTS (SELECT * FROM #BranchLevel)
463 BEGIN
464 INSERT ProductGroupAnalysis
465 (ProductGroupID, BranchID, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, CalculatedDateTime)
466 SELECT 0, BranchID, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, @Now
467 FROM #BranchLevel
468 ORDER BY BranchID
469 END
470 END
471
472 -- We can do Company level as well
473 IF @BranchID IS NULL
474 BEGIN
475 INSERT INTO #CompanyLevel
476 (COGSPerYear, AverageValuation, SalesMargin, PeriodsAnalysed)
477 SELECT ISNULL(SUM((TP.COGS * (365.0 / TP.DaysAnalysed))),0),
478 ISNULL(SUM(TP.AverageValuation),0),
479 ISNULL(SUM(TP.SalesMargin),0),
480 ISNULL(MAX(TP.PeriodsAnalysed),0)
481 FROM #Product TP
482
483 -- Turns Per Year = COGSPerYear / Average Value of Inventory
484 -- Gross Margin = (Total Sales - Total COGS)
485 -- GMROI = Gross Margin / Average Value of Inventory
486 UPDATE #CompanyLevel
487 SET FullStockTurn = COGSPerYear / AverageValuation,
488 GMROI = SalesMargin / AverageValuation
489 WHERE AverageValuation <> 0
490
491 -- Age In Days = 365.0 / (Turns Per Year)
492 UPDATE #CompanyLevel
493 SET FullStockAge = 365.0 / FullStockTurn
494 WHERE FullStockTurn <> 0
495
496 IF @UpdatePermanentTables = 1
497 BEGIN
498 -- Store history
499 INSERT INTO ProductGroupAnalysisHistory
500 (ProductGroupID, BranchID, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, CalculatedDateTime)
501 SELECT 0, 0, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, @Now
502 FROM #CompanyLevel
503
504 UPDATE ProductGroupAnalysis
505 SET FullStockAge = CL.FullStockAge,
506 FullStockTurn = CL.FullStockTurn,
507 GMROI = CL.GMROI,
508 PeriodsAnalysed = CL.PeriodsAnalysed,
509 CalculatedDateTime = @Now
510 FROM #CompanyLevel CL
511 WHERE ProductGroupAnalysis.ProductGroupID = 0
512 AND ProductGroupAnalysis.BranchID = 0
513
514 DELETE #CompanyLevel
515 FROM ProductGroupAnalysis
516 WHERE ProductGroupAnalysis.ProductGroupID = 0
517 AND ProductGroupAnalysis.BranchID = 0
518
519 IF EXISTS (SELECT * FROM #CompanyLevel)
520 BEGIN
521 INSERT ProductGroupAnalysis
522 (ProductGroupID, BranchID, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, CalculatedDateTime)
523 SELECT 0, 0, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed, @Now
524 FROM #CompanyLevel
525 END
526 END
527 END
528 END
529
530 IF @UpdatePermanentTables = 1
531 BEGIN
532 -- Are we trying to do everything, if so, clear out any old values that didn't calculate new values
533 IF @ForProductID IS NULL AND @BranchID IS NULL AND @ProductGroupID IS NULL
534 BEGIN
535 UPDATE ProductStockOption
536 SET FullStockTurn = 0,
537 FullStockAge = 0,
538 GMROI = 0,
539 PeriodsAnalysed = 0
540 FROM ProductStockOption PSO
541 LEFT OUTER JOIN #Product P
542 ON PSO.ProductID = P.ProductID
543 AND PSO.BranchID = P.BranchID
544 WHERE P.ProductID IS NULL
545 AND (PSO.FullStockTurn <> 0 OR PSO.FullStockAge <> 0 OR PSO.GMROI <> 0)
546 END
547
548 UPDATE ProductStockOption
549 SET FullStockTurn = P.FullStockTurn,
550 FullStockAge = P.FullStockAge,
551 GMROI = P.GMROI,
552 PeriodsAnalysed = P.PeriodsAnalysed
553 FROM #Product P
554 WHERE ProductStockOption.ProductID = P.ProductID
555 AND ProductStockOption.BranchID = P.BranchID
556 AND (ProductStockOption.FullStockTurn <> P.FullStockTurn
557 OR ProductStockOption.FullStockAge <> P.FullStockAge
558 OR ProductStockOption.GMROI <> P.GMROI
559 OR ProductStockOption.PeriodsAnalysed <> P.PeriodsAnalysed)
560
561 DELETE #Product
562 FROM ProductStockOption
563 WHERE ProductStockOption.ProductID = #Product.ProductID
564 AND ProductStockOption.BranchID = #Product.BranchID
565
566 IF EXISTS (SELECT * FROM #Product)
567 BEGIN
568 INSERT INTO ProductStockOption
569 (ProductID, BranchID, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed,
570 StockClass, HowSourced, Stocked)
571 SELECT P.ProductID, P.BranchID, P.FullStockTurn, P.FullStockAge, P.GMROI, P.PeriodsAnalysed,
572 ISNULL(PSO.StockClass,'U'), ISNULL(PSO.HowSourced,7), COALESCE(PSO.Stocked,@ProductDefaultStocked,PR.KeepStockFigures)
573 FROM #Product P
574 LEFT OUTER JOIN ProductStockOption PSO
575 ON PSO.ProductID = P.ProductID
576 AND PSO.BranchID = 0
577 INNER JOIN Product PR
578 ON PR.ProductID = P.ProductID
579 END
580 END
581 ELSE
582 BEGIN
583 INSERT #Output
584 (Type,ProductID,ProductGroupID,BranchID,StartDate,EndDate,DaysAnalysed,
585 ProductCOGS, COGSPerYear, AverageValuation,
586 SalesMargin,FullStockTurn,FullStockAge,GMROI,PeriodsAnalysed)
587 SELECT '4: Product' AS [Type], ProductID, NULL AS ProductGroupID, BranchID, StartDate, DateAdd(d,-1,@EndDate) AS EndDate, DaysAnalysed,
588 COGS AS ProductCOGS, NULL AS COGSPerYear, AverageValuation,
589 SalesMargin, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed
590 FROM #Product
591
592 INSERT #Output
593 (Type,ProductID,ProductGroupID,BranchID,StartDate,EndDate,DaysAnalysed,
594 ProductCOGS, COGSPerYear, AverageValuation,
595 SalesMargin,FullStockTurn,FullStockAge,GMROI,PeriodsAnalysed)
596 SELECT '3: Product Group' AS [Type], NULL AS ProductID, ProductGroupID, BranchID, NULL AS StartDate, Null as EndDate, NULL AS DaysAnalysed,
597 NULL AS ProductCOGS, COGSPerYear, AverageValuation,
598 SalesMargin, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed
599 FROM #ProductGroup3
600
601 INSERT #Output
602 (Type,ProductID,ProductGroupID,BranchID,StartDate,EndDate,DaysAnalysed,
603 ProductCOGS, COGSPerYear, AverageValuation,
604 SalesMargin,FullStockTurn,FullStockAge,GMROI,PeriodsAnalysed)
605 SELECT '2: Branch' AS [Type], NULL AS ProductID, NULL AS ProductGroupID, BranchID, NULL AS StartDate, Null as EndDate, NULL AS DaysAnalysed,
606 NULL AS ProductCOGS, COGSPerYear, AverageValuation,
607 SalesMargin, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed
608 FROM #BranchLevel
609
610 INSERT #Output
611 (Type,ProductID,ProductGroupID,BranchID,StartDate,EndDate,DaysAnalysed,
612 ProductCOGS, COGSPerYear, AverageValuation,
613 SalesMargin,FullStockTurn,FullStockAge,GMROI,PeriodsAnalysed)
614 SELECT '1: Company' AS [Type], NULL AS ProductID, NULL AS ProductGroupID, NULL AS BranchID, NULL AS StartDate, Null as EndDate, NULL AS DaysAnalysed,
615 NULL AS ProductCOGS, COGSPerYear, AverageValuation,
616 SalesMargin, FullStockTurn, FullStockAge, GMROI, PeriodsAnalysed
617 FROM #CompanyLevel
618
619 SELECT op.Type,op.ProductID,p.ProductCode,op.ProductGroupID,pg.name as ProductGroupName,
620 op.BranchID,b.name as BranchName, op.StartDate,op.EndDate,op.DaysAnalysed,op.ProductCOGS,op.COGSPerYear, op.AverageValuation,
621 op.SalesMargin,op.FullStockTurn,op.FullStockAge,op.GMROI,op.PeriodsAnalysed
622 FROM #Output op
623 left outer join branch b on b.branchid = op.branchid
624 left outer join product p on p.productid = op.productid
625 left outer join productgroup pg on pg.productgroupid = op.productgroupid
626 Order By op.type, b.name, pg.name, p.productcode
627 END
628
629 DROP TABLE #Product, #ProductGroup1, #ProductGroup2, #ProductGroup3, #BranchLevel, #CompanyLevel, #Output
630
631SET NOCOUNT OFF