· 6 years ago · Oct 10, 2019, 09:54 AM
1USE [BELBaseline]
2GO
3/****** Object: StoredProcedure [dbo].[sp_DMS_Baseline_KPI] Script Date: 10/10/2019 4:49:48 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9-- =============================================
10-- Author Creator: Dinh Hoang Lam
11-- Create date: 2015/04/02
12-- Description:
13-- =============================================
14
15-- =============================================
16-- Author Update: Dang.Huynh
17-- Create date: 2017-10-31
18-- Description:
19-- + Cập nhật cách lấy số liệu để tính toán từ Raw sales theo tham số tối thiểu về số lượng (MinVolume), sản lượng (MinRevenue)
20-- + Cập nhật cách lấy số liệu để tính toán từ Raw sales bao gồm đơn trả hàng
21-- + Cập nhật cách tính bình % đơn hàng (AVGPC) theo doanh số( Revnue) hoặc sản lượng ( Volume) theo lịch viếng thăm actual hoặc lịch MCP
22-- + Cập nhật cách tính bình quân số lượng đơn hàng (DROPSIZE) theo doanh số( Revnue) hoặc sản lượng ( Volume)
23-- + Cập nhật cách tính KPIs mở mới outlet
24-- =============================================
25
26-- EXEC sp_DMS_Baseline_KPI 3, '2017/02/14'
27ALTER PROCEDURE [dbo].[sp_DMS_Baseline_KPI]
28 @CompanyID INT,
29 @Date DATETIME
30AS
31BEGIN
32 SET NOCOUNT ON;
33
34 DECLARE @_CompanyID INT = @CompanyID;
35 DECLARE @_Date DATETIME = @Date;
36 DECLARE @_FirstDayOfWeek DATETIME;
37 DECLARE @_LastDayOfWeek DATETIME;
38 DECLARE @_FirstDayOfQuater DATETIME;
39 DECLARE @_LastDayOfQuater DATETIME;
40 DECLARE @_FirstDayOfMonth DATETIME
41 = CONVERT(VARCHAR(4), YEAR(@_Date)) + '/' + CONVERT(VARCHAR(2), MONTH(@_Date)) + '/1';
42 DECLARE @_LastDayOfMonth DATETIME = DATEADD(dd, -1, DATEADD(mm, 1, @_FirstDayOfMonth));
43 DECLARE @_Week CHAR(2);
44 DECLARE @_Month CHAR(2) = RIGHT('0' + CONVERT(VARCHAR(2), MONTH(@_Date)), 2);
45 DECLARE @_Quater CHAR(2);
46 DECLARE @_Year CHAR(4) = CONVERT(CHAR(4), YEAR(@_Date));
47 DECLARE @_FromDate DATETIME;
48 DECLARE @_ToDate DATETIME;
49
50 CREATE TABLE #tmpKPI
51 (
52 RowNumber INT,
53 CompanyID INT,
54 CodeListSalesID INT,
55 CodeListSalesCD VARCHAR(30),
56 Descr NVARCHAR(100),
57 Source CHAR(2),
58 Type CHAR(1),
59 GroupBy CHAR(1),
60 GroupID VARCHAR(30),
61 Formula CHAR(1),
62 Template VARCHAR(15),
63 KPIPeriodNbr VARCHAR(30),
64 RefNbr VARCHAR(30),
65 PERIOD CHAR(1),
66 FromDate DATETIME,
67 ToDate DATETIME,
68 ObjectID INT,
69 TempObjectID INT,
70 ObjectType CHAR(1),
71 ObjectAssignment VARCHAR(50),
72
73 --Hieu.Bui : 10-Apr_2019 : Nang decimal tu 18,2 len 18,3 de tinh KPI trong luong do SP cua bel co trong luong theo gram nhung lai nhap theo KG
74 TempTargetsSuggest DECIMAL(18, 3),
75 TempTargetsAssigned DECIMAL(18, 3),
76 TargetsSuggest DECIMAL(18, 3),
77 TargetsAssigned DECIMAL(18, 3),
78 Actual1 DECIMAL(18, 3),
79 Actual2 DECIMAL(18, 3),
80 Actual3 DECIMAL(18, 3),
81 Actual4 DECIMAL(18, 3),
82 Actual DECIMAL(18, 3)
83 );
84
85 CREATE TABLE #tmpSalesTerritory
86 (
87 RowNumber INT,
88 CompanyID INT,
89 SalesOrg_0_ValueID INT,
90 SalesOrg_1_ValueID INT,
91 SalesOrg_2_ValueID INT,
92 SalesOrg_3_ValueID INT,
93 SalesOrg_4_ValueID INT,
94 SalesOrg_5_ValueID INT,
95 SalesOrg_6_ValueID INT,
96 SalesOrg_7_ValueID INT,
97 SalesOrg_8_ValueID INT,
98 SalesOrg_9_ValueID INT
99 );
100
101 CREATE TABLE #tmpRoute
102 (
103 CompanyID INT,
104 SalespersonID INT,
105 Route VARCHAR(30),
106 SalesForceID INT,
107 SalesAreaID INT,
108 EffectiveDate DATETIME,
109 ExpirationDate DATETIME,
110 SalesOrg_0_ValueID INT,
111 SalesOrg_1_ValueID INT,
112 SalesOrg_2_ValueID INT,
113 SalesOrg_3_ValueID INT,
114 SalesOrg_4_ValueID INT,
115 SalesOrg_5_ValueID INT,
116 SalesOrg_6_ValueID INT,
117 SalesOrg_7_ValueID INT,
118 SalesOrg_8_ValueID INT,
119 SalesOrg_9_ValueID INT
120 );
121
122 CREATE TABLE #tmpSalesForce
123 (
124 CompanyID INT,
125 EmployeeID INT,
126 SFHierachyID INT,
127 TerritoryType CHAR(1),
128 SalesOrgValueID INT,
129 SalesOrg_0_ValueID INT,
130 SalesOrg_1_ValueID INT,
131 SalesOrg_2_ValueID INT,
132 SalesOrg_3_ValueID INT,
133 SalesOrg_4_ValueID INT,
134 SalesOrg_5_ValueID INT,
135 SalesOrg_6_ValueID INT,
136 SalesOrg_7_ValueID INT,
137 SalesOrg_8_ValueID INT,
138 SalesOrg_9_ValueID INT
139 );
140 --Hieu.Bui : 10-Apr_2019 : Nang decimal tu 18,2 len 18,3 de tinh KPI trong luong do SP cua bel co trong luong theo gram nhung lai nhap theo KG
141 CREATE TABLE #tmpTotalPC
142 (
143 CompanyID INT,
144 ObjectID INT,
145 ObjectAssignment VARCHAR(50),
146 Actual1 DECIMAL(18, 3),
147 Actual2 DECIMAL(18, 3),
148 Actual3 DECIMAL(18, 3),
149 Actual4 DECIMAL(18, 3),
150 Actual DECIMAL(18, 3)
151 );
152 --Hieu.Bui : 10-Apr_2019 : Nang decimal tu 18,2 len 18,3 de tinh KPI trong luong do SP cua bel co trong luong theo gram nhung lai nhap theo KG
153 CREATE TABLE #tmpTotalLine
154 (
155 CompanyID INT,
156 ObjectID INT,
157 ObjectAssignment VARCHAR(50),
158 Actual1 DECIMAL(18, 3),
159 Actual2 DECIMAL(18, 3),
160 Actual3 DECIMAL(18, 3),
161 Actual4 DECIMAL(18, 3),
162 Actual DECIMAL(18, 3)
163 );
164
165 DELETE FROM dbo.DMSBLKPI
166 WHERE CompanyID = @_CompanyID
167 AND @_Date
168 BETWEEN FromDate AND ToDate;
169
170 ---- Lấy tuần trong năm
171 SELECT @_Week = Week,
172 @_FirstDayOfWeek = StartDate,
173 @_LastDayOfWeek = EndDate
174 FROM dbo.DMSBLSalesCalendarWeek WITH (NOLOCK)
175 WHERE CompanyID = @_CompanyID
176 AND @_Date
177 BETWEEN StartDate AND EndDate;
178
179 ---- Lấy Quý trong năm
180 SELECT @_Quater = Quarter,
181 @_FirstDayOfQuater = StartDate,
182 @_LastDayOfQuater = EndDate
183 FROM BELDMS.dbo.DMSQuarter WITH (NOLOCK)
184 WHERE CompanyID = @_CompanyID
185 AND @_Date
186 BETWEEN StartDate AND EndDate;
187
188 ---- Lấy KPI tháng có kỳ đánh giá hiệu lực trong khoảng thời gian baseline
189 SELECT kpi.CompanyID,
190 kpi.CodeListSalesID,
191 kpi.CodeListSalesCD,
192 kpi.Descr,
193 kpi.[Source],
194 kpi.Type,
195 kpi.GroupBy,
196 kpi.GroupID,
197 kpi.Formula,
198 Template = kpi.Template,
199 periodHeader.KPIPeriodNbr,
200 periodHeader.Period,
201 sov.SalesOrgID,
202 SalesAreaID = sov.ValueID,
203 sov.Level,
204 kpi.CorrectRouteAndCorrectCoordinates,
205 kpi.CorrectRouteAndIncorrectCoordinates,
206 kpi.IncorrectRoute,
207 kpi.DistributorsSell,
208 kpi.MinSKUVolume,
209 kpi.MinSKURevenue,
210 kpi.ExcludeReturnOrder,
211 periodConfigHeader.RefNbr,
212 FromDate = @_FirstDayOfMonth,
213 ToDate = @_LastDayOfMonth,
214 ObjectID = periodConfigDetail.ThisObject,
215 ObjectType = CASE
216 WHEN cate.Distributor = 1 THEN
217 'D'
218 WHEN cate.Sales = 1 THEN
219 'S'
220 END,
221 TargetsSuggest = periodConfigDetail.TargetsSuggest,
222 TargetsAssigned = COALESCE(periodConfigDetail.TargetsAdjust, periodConfigDetail.TargetsAssigned)
223 INTO #tmpKPIDetail
224 FROM BELDMS..DMSKPIListSales kpi WITH (NOLOCK)
225 JOIN BELDMS..DMSKPISalesPeriodDetail periodDetail WITH (NOLOCK)
226 ON periodDetail.CompanyID = kpi.CompanyID
227 AND periodDetail.CodeListSalesID = kpi.CodeListSalesID
228 JOIN BELDMS..DMSKPISalesPeriodHeader periodHeader WITH (NOLOCK)
229 ON periodHeader.CompanyID = periodDetail.CompanyID
230 AND periodHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
231 JOIN BELDMS..DMSKPISalesPeriodConfigurationHeader periodConfigHeader WITH (NOLOCK)
232 ON periodConfigHeader.CompanyID = periodDetail.CompanyID
233 AND periodConfigHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
234 AND periodConfigHeader.CodeListSalesID = periodDetail.CodeListSalesID
235 AND periodConfigHeader.Year = @_Year
236 AND periodConfigHeader.Month = @_Month
237 JOIN BELDMS..DMSKPISalesPeriodConfigurationDetail periodConfigDetail WITH (NOLOCK)
238 ON periodConfigDetail.CompanyID = periodConfigHeader.CompanyID
239 AND periodConfigDetail.RefNbr = periodConfigHeader.RefNbr
240 JOIN BELDMS..DMSKPICategory cate WITH (NOLOCK)
241 ON cate.CompanyID = periodHeader.CompanyID
242 AND cate.CategoryID = periodHeader.KPICategoryID
243 LEFT JOIN BELDMS..DMSSalesOrgValue sov WITH (NOLOCK)
244 ON sov.CompanyID = periodHeader.CompanyID
245 AND sov.ValueID = periodHeader.SalesArea
246 WHERE kpi.CompanyID = @_CompanyID
247 AND kpi.Active = 1
248 AND periodHeader.Release = 1
249 AND periodHeader.Period = 'M';
250
251 ---- Lấy KPI Quý có kỳ đánh giá hiệu lực trong khoảng thời gian baseline
252 INSERT INTO #tmpKPIDetail
253 SELECT kpi.CompanyID,
254 kpi.CodeListSalesID,
255 kpi.CodeListSalesCD,
256 kpi.Descr,
257 kpi.[Source],
258 kpi.Type,
259 kpi.GroupBy,
260 kpi.GroupID,
261 kpi.Formula,
262 Template = kpi.Template,
263 periodHeader.KPIPeriodNbr,
264 periodHeader.Period,
265 sov.SalesOrgID,
266 SalesAreaID = sov.ValueID,
267 sov.Level,
268 kpi.CorrectRouteAndCorrectCoordinates,
269 kpi.CorrectRouteAndIncorrectCoordinates,
270 kpi.IncorrectRoute,
271 kpi.DistributorsSell,
272 kpi.MinSKUVolume,
273 kpi.MinSKURevenue,
274 kpi.ExcludeReturnOrder,
275 periodConfigHeader.RefNbr,
276 FromDate = @_FirstDayOfQuater,
277 ToDate = @_LastDayOfQuater,
278 ObjectID = periodConfigDetail.ThisObject,
279 ObjectType = CASE
280 WHEN cate.Distributor = 1 THEN
281 'D'
282 WHEN cate.Sales = 1 THEN
283 'S'
284 END,
285 TargetsSuggest = periodConfigDetail.TargetsSuggest,
286 TargetsAssigned = COALESCE(periodConfigDetail.TargetsAdjust, periodConfigDetail.TargetsAssigned)
287 FROM BELDMS..DMSKPIListSales kpi WITH (NOLOCK)
288 JOIN BELDMS..DMSKPISalesPeriodDetail periodDetail WITH (NOLOCK)
289 ON periodDetail.CompanyID = kpi.CompanyID
290 AND periodDetail.CodeListSalesID = kpi.CodeListSalesID
291 JOIN BELDMS..DMSKPISalesPeriodHeader periodHeader WITH (NOLOCK)
292 ON periodHeader.CompanyID = periodDetail.CompanyID
293 AND periodHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
294 JOIN BELDMS..DMSKPICategory cate WITH (NOLOCK)
295 ON cate.CompanyID = periodHeader.CompanyID
296 AND cate.CategoryID = periodHeader.KPICategoryID
297 JOIN BELDMS..DMSKPISalesPeriodConfigurationHeader periodConfigHeader WITH (NOLOCK)
298 ON periodConfigHeader.CompanyID = periodDetail.CompanyID
299 AND periodConfigHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
300 AND periodConfigHeader.CodeListSalesID = periodDetail.CodeListSalesID
301 AND periodConfigHeader.Year = @_Year
302 AND periodConfigHeader.Quarter = @_Quater
303 JOIN BELDMS..DMSKPISalesPeriodConfigurationDetail periodConfigDetail WITH (NOLOCK)
304 ON periodConfigDetail.CompanyID = periodConfigHeader.CompanyID
305 AND periodConfigDetail.RefNbr = periodConfigHeader.RefNbr
306 LEFT JOIN BELDMS..DMSSalesOrgValue sov WITH (NOLOCK)
307 ON sov.CompanyID = periodHeader.CompanyID
308 AND sov.ValueID = periodHeader.SalesArea
309 WHERE kpi.CompanyID = @_CompanyID
310 AND kpi.Active = 1
311 AND periodHeader.Release = 1
312 AND periodHeader.Period = 'Q';
313
314 ---- Lấy KPI tuần có kỳ đánh giá hiệu lực trong khoảng thời gian baseline
315 INSERT INTO #tmpKPIDetail
316 SELECT kpi.CompanyID,
317 kpi.CodeListSalesID,
318 kpi.CodeListSalesCD,
319 kpi.Descr,
320 kpi.[Source],
321 kpi.Type,
322 kpi.GroupBy,
323 kpi.GroupID,
324 kpi.Formula,
325 Template = kpi.Template,
326 periodHeader.KPIPeriodNbr,
327 periodHeader.Period,
328 sov.SalesOrgID,
329 SalesAreaID = sov.ValueID,
330 sov.Level,
331 kpi.CorrectRouteAndCorrectCoordinates,
332 kpi.CorrectRouteAndIncorrectCoordinates,
333 kpi.IncorrectRoute,
334 kpi.ExcludeReturnOrder,
335 kpi.MinSKUVolume,
336 kpi.MinSKURevenue,
337 kpi.DistributorsSell,
338 periodConfigHeader.RefNbr,
339 FromDate = @_FirstDayOfWeek,
340 ToDate = @_LastDayOfWeek,
341 ObjectID = periodConfigDetail.ThisObject,
342 ObjectType = CASE
343 WHEN cate.Distributor = 1 THEN
344 'D'
345 WHEN cate.Sales = 1 THEN
346 'S'
347 END,
348 TargetsSuggest = periodConfigDetail.TargetsSuggest,
349 TargetsAssigned = COALESCE(periodConfigDetail.TargetsAdjust, periodConfigDetail.TargetsAssigned)
350 FROM BELDMS..DMSKPIListSales kpi WITH (NOLOCK)
351 JOIN BELDMS..DMSKPISalesPeriodDetail periodDetail WITH (NOLOCK)
352 ON periodDetail.CompanyID = kpi.CompanyID
353 AND periodDetail.CodeListSalesID = kpi.CodeListSalesID
354 JOIN BELDMS..DMSKPISalesPeriodHeader periodHeader WITH (NOLOCK)
355 ON periodHeader.CompanyID = periodDetail.CompanyID
356 AND periodHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
357 JOIN BELDMS..DMSKPICategory cate WITH (NOLOCK)
358 ON cate.CompanyID = periodHeader.CompanyID
359 AND cate.CategoryID = periodHeader.KPICategoryID
360 JOIN BELDMS..DMSKPISalesPeriodConfigurationHeader periodConfigHeader WITH (NOLOCK)
361 ON periodConfigHeader.CompanyID = periodHeader.CompanyID
362 AND periodConfigHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
363 AND periodConfigHeader.CodeListSalesID = periodDetail.CodeListSalesID
364 AND periodConfigHeader.Year = @_Year
365 AND periodConfigHeader.Week = @_Week
366 JOIN BELDMS..DMSKPISalesPeriodConfigurationDetail periodConfigDetail WITH (NOLOCK)
367 ON periodConfigDetail.CompanyID = periodConfigHeader.CompanyID
368 AND periodConfigDetail.RefNbr = periodConfigHeader.RefNbr
369 LEFT JOIN BELDMS..DMSSalesOrgValue sov WITH (NOLOCK)
370 ON sov.CompanyID = periodHeader.CompanyID
371 AND sov.ValueID = periodHeader.SalesArea
372 WHERE kpi.CompanyID = @_CompanyID
373 AND kpi.Active = 1
374 AND periodHeader.Release = 1
375 AND periodHeader.Period = 'W';
376
377 ---- Lấy KPI ngày có kỳ đánh giá hiệu lực trong khoảng thời gian baseline
378 INSERT INTO #tmpKPIDetail
379 SELECT kpi.CompanyID,
380 kpi.CodeListSalesID,
381 kpi.CodeListSalesCD,
382 kpi.Descr,
383 kpi.[Source],
384 kpi.Type,
385 kpi.GroupBy,
386 kpi.GroupID,
387 kpi.Formula,
388 Template = kpi.Template,
389 periodHeader.KPIPeriodNbr,
390 periodHeader.Period,
391 sov.SalesOrgID,
392 SalesAreaID = sov.ValueID,
393 sov.Level,
394 kpi.CorrectRouteAndCorrectCoordinates,
395 kpi.CorrectRouteAndIncorrectCoordinates,
396 kpi.IncorrectRoute,
397 kpi.DistributorsSell,
398 kpi.MinSKUVolume,
399 kpi.MinSKURevenue,
400 kpi.ExcludeReturnOrder,
401 periodConfigHeader.RefNbr,
402 FromDate = periodConfigHeader.FromDate,
403 ToDate = periodConfigHeader.ToDate,
404 ObjectID = periodConfigDetail.ThisObject,
405 ObjectType = CASE
406 WHEN cate.Distributor = 1 THEN
407 'D'
408 WHEN cate.Sales = 1 THEN
409 'S'
410 END,
411 TargetsSuggest = periodConfigDetail.TargetsSuggest,
412 TargetsAssigned = COALESCE(periodConfigDetail.TargetsAdjust, periodConfigDetail.TargetsAssigned)
413 FROM BELDMS..DMSKPIListSales kpi WITH (NOLOCK)
414 JOIN BELDMS..DMSKPISalesPeriodDetail periodDetail WITH (NOLOCK)
415 ON periodDetail.CompanyID = kpi.CompanyID
416 AND periodDetail.CodeListSalesID = kpi.CodeListSalesID
417 JOIN BELDMS..DMSKPISalesPeriodHeader periodHeader WITH (NOLOCK)
418 ON periodHeader.CompanyID = periodDetail.CompanyID
419 AND periodHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
420 JOIN BELDMS..DMSKPICategory cate WITH (NOLOCK)
421 ON cate.CompanyID = periodHeader.CompanyID
422 AND cate.CategoryID = periodHeader.KPICategoryID
423 JOIN BELDMS..DMSKPISalesPeriodConfigurationHeader periodConfigHeader WITH (NOLOCK)
424 ON periodConfigHeader.CompanyID = periodDetail.CompanyID
425 AND periodConfigHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
426 AND periodConfigHeader.CodeListSalesID = periodDetail.CodeListSalesID
427 JOIN BELDMS..DMSKPISalesPeriodConfigurationDetail periodConfigDetail WITH (NOLOCK)
428 ON periodConfigDetail.CompanyID = periodConfigHeader.CompanyID
429 AND periodConfigDetail.RefNbr = periodConfigHeader.RefNbr
430 LEFT JOIN BELDMS..DMSSalesOrgValue sov WITH (NOLOCK)
431 ON sov.CompanyID = periodHeader.CompanyID
432 AND sov.ValueID = periodHeader.SalesArea
433 WHERE kpi.CompanyID = @_CompanyID
434 AND kpi.Active = 1
435 AND periodHeader.Release = 1
436 AND periodHeader.Period = 'D'
437 AND @_Date
438 BETWEEN periodConfigHeader.FromDate AND periodConfigHeader.ToDate;
439
440 ---- Xóa các KPI chưa có kì DK
441 DELETE #tmpKPIDetail
442 WHERE RefNbr IS NULL;
443 --DELETE #tmpKPIDetail WHERE RefNbr <>'00000062' --1
444 --SELECT * FROM #tmpKPIDetail --1
445 ---- Lấy khoảng thời gian tính KPI lớn nhất
446 SELECT @_FromDate = MIN(FromDate),
447 @_ToDate = MAX(ToDate)
448 FROM #tmpKPIDetail;
449
450 ---- Lấy danh sách NPP
451 SELECT DISTINCT
452 bldl.CompanyID,
453 bldl.ID,
454 bldl.DistributorID,
455 DistributorLocationID = bldl.LocationID,
456 blst.SalesOrg_0_ValueID,
457 blst.SalesOrg_1_ValueID,
458 blst.SalesOrg_2_ValueID,
459 blst.SalesOrg_3_ValueID,
460 blst.SalesOrg_4_ValueID,
461 blst.SalesOrg_5_ValueID,
462 blst.SalesOrg_6_ValueID,
463 blst.SalesOrg_7_ValueID,
464 blst.SalesOrg_8_ValueID,
465 blst.SalesOrg_9_ValueID,
466 SalesForce_0_ID = blsf0.EmployeeID,
467 SalesForce_1_ID = blsf1.EmployeeID,
468 SalesForce_2_ID = blsf2.EmployeeID,
469 SalesForce_3_ID = blsf3.EmployeeID,
470 SalesForce_4_ID = blsf4.EmployeeID,
471 SalesForce_5_ID = blsf5.EmployeeID,
472 SalesForce_6_ID = blsf6.EmployeeID,
473 SalesForce_7_ID = blsf7.EmployeeID,
474 SalesForce_8_ID = blsf8.EmployeeID,
475 SalesForce_9_ID = blsf9.EmployeeID,
476 bldl.ExpirationDate
477 INTO #tmpDistributorLocation
478 FROM dbo.DMSBLDistributorLocation bldl WITH (NOLOCK)
479 LEFT JOIN dbo.DMSBLSalesTerritory blst WITH (NOLOCK)
480 ON blst.CompanyID = bldl.CompanyID
481 AND blst.ID = bldl.BLSalesTerritoryID
482 LEFT JOIN dbo.DMSBLSalesForce blsf0 WITH (NOLOCK)
483 ON blsf0.CompanyID = blst.CompanyID
484 AND blsf0.SalesOrgValueID = blst.SalesOrg_0_ValueID
485 AND blsf0.ExpirationDate IS NULL
486 LEFT JOIN dbo.DMSBLSalesForce blsf1 WITH (NOLOCK)
487 ON blsf1.CompanyID = blst.CompanyID
488 AND blsf1.SalesOrgValueID = blst.SalesOrg_1_ValueID
489 AND blsf1.ExpirationDate IS NULL
490 LEFT JOIN dbo.DMSBLSalesForce blsf2 WITH (NOLOCK)
491 ON blsf2.CompanyID = blst.CompanyID
492 AND blsf2.SalesOrgValueID = blst.SalesOrg_2_ValueID
493 AND blsf2.ExpirationDate IS NULL
494 LEFT JOIN dbo.DMSBLSalesForce blsf3 WITH (NOLOCK)
495 ON blsf3.CompanyID = blst.CompanyID
496 AND blsf3.SalesOrgValueID = blst.SalesOrg_3_ValueID
497 AND blsf3.ExpirationDate IS NULL
498 LEFT JOIN dbo.DMSBLSalesForce blsf4 WITH (NOLOCK)
499 ON blsf4.CompanyID = blst.CompanyID
500 AND blsf4.SalesOrgValueID = blst.SalesOrg_4_ValueID
501 AND blsf4.ExpirationDate IS NULL
502 LEFT JOIN dbo.DMSBLSalesForce blsf5 WITH (NOLOCK)
503 ON blsf5.CompanyID = blst.CompanyID
504 AND blsf5.SalesOrgValueID = blst.SalesOrg_5_ValueID
505 AND blsf5.ExpirationDate IS NULL
506 LEFT JOIN dbo.DMSBLSalesForce blsf6 WITH (NOLOCK)
507 ON blsf6.CompanyID = blst.CompanyID
508 AND blsf6.SalesOrgValueID = blst.SalesOrg_6_ValueID
509 AND blsf6.ExpirationDate IS NULL
510 LEFT JOIN dbo.DMSBLSalesForce blsf7 WITH (NOLOCK)
511 ON blsf7.CompanyID = blst.CompanyID
512 AND blsf7.SalesOrgValueID = blst.SalesOrg_7_ValueID
513 AND blsf7.ExpirationDate IS NULL
514 LEFT JOIN dbo.DMSBLSalesForce blsf8 WITH (NOLOCK)
515 ON blsf8.CompanyID = blst.CompanyID
516 AND blsf8.SalesOrgValueID = blst.SalesOrg_8_ValueID
517 AND blsf8.ExpirationDate IS NULL
518 LEFT JOIN dbo.DMSBLSalesForce blsf9 WITH (NOLOCK)
519 ON blsf9.CompanyID = blst.CompanyID
520 AND blsf9.SalesOrgValueID = blst.SalesOrg_9_ValueID
521 AND blsf9.ExpirationDate IS NULL
522 WHERE bldl.CompanyID = @_CompanyID;
523
524 ---- Lấy dữ liệu RawData SalesOut để tính KPI
525 SELECT tmp.*,
526 dl.DistributorLocationID,
527 dl.SalesOrg_0_ValueID,
528 dl.SalesOrg_1_ValueID,
529 dl.SalesOrg_2_ValueID,
530 dl.SalesOrg_3_ValueID,
531 dl.SalesOrg_4_ValueID,
532 dl.SalesOrg_5_ValueID,
533 dl.SalesOrg_6_ValueID,
534 dl.SalesOrg_7_ValueID,
535 dl.SalesOrg_8_ValueID,
536 dl.SalesOrg_9_ValueID,
537 dl.SalesForce_0_ID,
538 dl.SalesForce_1_ID,
539 dl.SalesForce_2_ID,
540 dl.SalesForce_3_ID,
541 dl.SalesForce_4_ID,
542 dl.SalesForce_5_ID,
543 dl.SalesForce_6_ID,
544 dl.SalesForce_7_ID,
545 dl.SalesForce_8_ID,
546 dl.SalesForce_9_ID
547 INTO #tmpSORawSales
548 FROM
549 (
550 SELECT blrs.CompanyID,
551 blrs.BLDistributorLocationID,
552 blrs.SalespersonID,
553 blrs.OrderType,
554 blrs.OrderNbr,
555 blrs.OrderDate,
556 blrs.SourceType,
557 blrs.Route,
558 blrs.CustomerID,
559 blrs.CustomerLocationID,
560 blrs.InventoryID,
561 TranAmt = CASE
562 WHEN blrs.OrderType = 'CM' THEN
563 blrs.TranAmt * -1
564 ELSE
565 blrs.TranAmt
566 END,
567 blrs.ShippedQty,
568 blrs.IsValidDate,
569 blrs.IsValidDistance,
570 blrs.IsRevenueDeduction,
571 blrs.ReasonCode
572 FROM dbo.DMSBLRawSales blrs WITH (NOLOCK)
573 WHERE blrs.CompanyID = @_CompanyID
574 AND blrs.OrderDate
575 BETWEEN @_FromDate AND @_ToDate
576 AND blrs.IsDispose = 0
577 AND blrs.IsFree = 0
578 AND blrs.SourceType != 'TMK'
579 AND
580 (
581 blrs.OrderType != 'CM'
582 OR blrs.IsRevenueDeduction = 1
583 ) --Thu.Nguyen 2019-07-18: Loai nhung don CM duoc tra hang trc ngay baseline, chi lay don CM tra hang sau ngay baseline IsRevenueDeduction = 1
584
585 UNION ALL
586 SELECT blrsh.CompanyID,
587 blrsh.BLDistributorLocationID,
588 blrsh.SalespersonID,
589 blrsh.OrderType,
590 blrsh.OrderNbr,
591 blrsh.OrderDate,
592 blrsh.SourceType,
593 blrsh.Route,
594 blrsh.CustomerID,
595 blrsh.CustomerLocationID,
596 blrsh.InventoryID,
597 TranAmt = CASE
598 WHEN blrsh.OrderType = 'CM' THEN
599 blrsh.TranAmt * -1
600 ELSE
601 blrsh.TranAmt
602 END,
603 blrsh.ShippedQty,
604 blrsh.IsValidDate,
605 blrsh.IsValidDistance,
606 blrsh.IsRevenueDeduction,
607 blrsh.ReasonCode
608 FROM dbo.DMSBLRawSalesHistory blrsh WITH (NOLOCK)
609 JOIN dbo.DMSBLDistributorLocation dl WITH (NOLOCK)
610 ON dl.CompanyID = blrsh.CompanyID
611 AND dl.ID = blrsh.BLDistributorLocationID
612 WHERE blrsh.CompanyID = @_CompanyID
613 AND blrsh.OrderDate
614 BETWEEN @_FromDate AND @_ToDate
615 AND blrsh.IsDispose = 0
616 AND blrsh.IsFree = 0
617 AND blrsh.SourceType != 'TMK'
618 -- đối với PNC có config trừ doanh số giao dịch trả hàng
619 -- nên phải lấy tất cả các giao dịch và tùy config để lấy
620 AND
621 (
622 blrsh.OrderType != 'CM'
623 OR blrsh.IsRevenueDeduction = 1
624 ) --Thu.Nguyen 2019-07-18: Loai nhung don CM duoc tra hang trc ngay baseline, chi lay don CM tra hang sau ngay baseline IsRevenueDeduction = 1
625 ) tmp
626 JOIN #tmpDistributorLocation dl WITH (NOLOCK)
627 ON dl.CompanyID = tmp.CompanyID
628 AND dl.ID = tmp.BLDistributorLocationID;
629
630 ---- Lấy dữ liệu RawData SalesOut để tính KPI trọng lượng
631 SELECT tmp.*,
632 dl.DistributorLocationID,
633 dl.SalesOrg_0_ValueID,
634 dl.SalesOrg_1_ValueID,
635 dl.SalesOrg_2_ValueID,
636 dl.SalesOrg_3_ValueID,
637 dl.SalesOrg_4_ValueID,
638 dl.SalesOrg_5_ValueID,
639 dl.SalesOrg_6_ValueID,
640 dl.SalesOrg_7_ValueID,
641 dl.SalesOrg_8_ValueID,
642 dl.SalesOrg_9_ValueID,
643 dl.SalesForce_0_ID,
644 dl.SalesForce_1_ID,
645 dl.SalesForce_2_ID,
646 dl.SalesForce_3_ID,
647 dl.SalesForce_4_ID,
648 dl.SalesForce_5_ID,
649 dl.SalesForce_6_ID,
650 dl.SalesForce_7_ID,
651 dl.SalesForce_8_ID,
652 dl.SalesForce_9_ID
653 INTO #tmpSOWeightRawSales
654 FROM
655 (
656 SELECT blrs.CompanyID,
657 blrs.BLDistributorLocationID,
658 blrs.SalespersonID,
659 blrs.OrderType,
660 blrs.OrderNbr,
661 blrs.OrderDate,
662 blrs.SourceType,
663 blrs.Route,
664 blrs.CustomerID,
665 blrs.CustomerLocationID,
666 blrs.InventoryID,
667 TranAmt = CASE
668 WHEN blrs.OrderType = 'CM' THEN
669 blrs.TranAmt * -1
670 ELSE
671 blrs.TranAmt
672 END,
673 blrs.ShippedQty,
674 blrs.IsValidDate,
675 blrs.IsValidDistance,
676 blrs.IsRevenueDeduction,
677 blrs.ReasonCode,
678 blrs.LineWeight,
679 blrs.IsFree
680 FROM dbo.DMSBLRawSales blrs WITH (NOLOCK)
681 WHERE blrs.CompanyID = @_CompanyID
682 AND blrs.OrderDate
683 BETWEEN @_FromDate AND @_ToDate
684 AND blrs.IsDispose = 0
685 AND
686 (
687 blrs.OrderType != 'CM'
688 OR blrs.IsRevenueDeduction = 1
689 )
690 UNION ALL
691 SELECT blrsh.CompanyID,
692 blrsh.BLDistributorLocationID,
693 blrsh.SalespersonID,
694 blrsh.OrderType,
695 blrsh.OrderNbr,
696 blrsh.OrderDate,
697 blrsh.SourceType,
698 blrsh.Route,
699 blrsh.CustomerID,
700 blrsh.CustomerLocationID,
701 blrsh.InventoryID,
702 TranAmt = CASE
703 WHEN blrsh.OrderType = 'CM' THEN
704 blrsh.TranAmt * -1
705 ELSE
706 blrsh.TranAmt
707 END,
708 blrsh.ShippedQty,
709 blrsh.IsValidDate,
710 blrsh.IsValidDistance,
711 blrsh.IsRevenueDeduction,
712 blrsh.ReasonCode,
713 blrsh.LineWeight,
714 blrsh.IsFree
715 FROM dbo.DMSBLRawSalesHistory blrsh WITH (NOLOCK)
716 JOIN dbo.DMSBLDistributorLocation dl WITH (NOLOCK)
717 ON dl.CompanyID = blrsh.CompanyID
718 AND dl.ID = blrsh.BLDistributorLocationID
719 WHERE blrsh.CompanyID = @_CompanyID
720 AND blrsh.OrderDate
721 BETWEEN @_FromDate AND @_ToDate
722 AND blrsh.IsDispose = 0
723 -- đối với PNC có config trừ doanh số giao dịch trả hàng
724 -- nên phải lấy tất cả các giao dịch và tùy config để lấy
725 AND
726 (
727 blrsh.OrderType != 'CM'
728 OR blrsh.IsRevenueDeduction = 1
729 )
730 ) tmp
731 JOIN #tmpDistributorLocation dl WITH (NOLOCK)
732 ON dl.CompanyID = tmp.CompanyID
733 AND dl.ID = tmp.BLDistributorLocationID;
734
735 ---- Lấy dữ liệu RawData SalesIn để tính KPI
736 SELECT blsi.CompanyID,
737 blsi.TranDate,
738 dl.DistributorID,
739 dl.DistributorLocationID,
740 blsi.InventoryID,
741 blsi.ReceivedAmt,
742 blsi.ReceivedQty,
743 dl.SalesOrg_0_ValueID,
744 dl.SalesOrg_1_ValueID,
745 dl.SalesOrg_2_ValueID,
746 dl.SalesOrg_3_ValueID,
747 dl.SalesOrg_4_ValueID,
748 dl.SalesOrg_5_ValueID,
749 dl.SalesOrg_6_ValueID,
750 dl.SalesOrg_7_ValueID,
751 dl.SalesOrg_8_ValueID,
752 dl.SalesOrg_9_ValueID,
753 dl.SalesForce_0_ID,
754 dl.SalesForce_1_ID,
755 dl.SalesForce_2_ID,
756 dl.SalesForce_3_ID,
757 dl.SalesForce_4_ID,
758 dl.SalesForce_5_ID,
759 dl.SalesForce_6_ID,
760 dl.SalesForce_7_ID,
761 dl.SalesForce_8_ID,
762 dl.SalesForce_9_ID
763 INTO #tmpSIRawSales
764 FROM dbo.DMSBLSalesIn blsi WITH (NOLOCK)
765 JOIN #tmpDistributorLocation dl
766 ON dl.CompanyID = blsi.CompanyID
767 AND dl.ID = blsi.BLDistributorLocationID
768 WHERE blsi.CompanyID = @_CompanyID
769 AND blsi.TranDate
770 BETWEEN @_FromDate AND @_ToDate;
771
772 ---- Lấy ra dữ liệu BL Route Sales Để tính các KPIs theo %
773 SELECT *
774 INTO #tmpRouteSalesData
775 FROM
776 (
777 SELECT blrs.CompanyID,
778 blrs.BLDistributorLocationID,
779 blrs.TranDate,
780 blrs.SalespersonID,
781 blrs.Route,
782 blrs.Revenue1,
783 blrs.Revenue2,
784 blrs.Revenue3,
785 blrs.PromotionAmt,
786 blrs.SalesOut1,
787 blrs.SalesOut2,
788 blrs.SalesOut3,
789 blrs.PromotionQty,
790 blrs.ASO,
791 blrs.TotalASO,
792 blrs.TotalMCPVisit,
793 blrs.TotalActualVisit,
794 blrs.PC1,
795 blrs.PC2,
796 blrs.PC3
797 FROM dbo.DMSBLRouteSales blrs WITH (NOLOCK)
798 WHERE blrs.CompanyID = @_CompanyID
799 AND blrs.TranDate
800 BETWEEN @_FromDate AND @_ToDate
801 UNION ALL
802 SELECT blrsh.CompanyID,
803 blrsh.BLDistributorLocationID,
804 blrsh.TranDate,
805 blrsh.SalespersonID,
806 blrsh.Route,
807 blrsh.Revenue1,
808 blrsh.Revenue2,
809 blrsh.Revenue3,
810 blrsh.PromotionAmt,
811 blrsh.SalesOut1,
812 blrsh.SalesOut2,
813 blrsh.SalesOut3,
814 blrsh.PromotionQty,
815 blrsh.ASO,
816 blrsh.TotalASO,
817 blrsh.TotalMCPVisit,
818 blrsh.TotalActualVisit,
819 blrsh.PC1,
820 blrsh.PC2,
821 blrsh.PC3
822 FROM dbo.DMSBLRouteSalesHistory blrsh WITH (NOLOCK)
823 WHERE blrsh.CompanyID = @_CompanyID
824 AND blrsh.TranDate
825 BETWEEN @_FromDate AND @_ToDate
826 ) tmp;
827
828 ---- Lấy danh sách KPI ngoại trừ các KPI đặc biệt
829 SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
830 *
831 INTO #tmpKPIList
832 FROM
833 (
834 SELECT CompanyID,
835 CodeListSalesID,
836 CodeListSalesCD,
837 RefNbr,
838 KPIPeriodNbr,
839 Descr = MAX(Descr),
840 Source = MAX(Source),
841 Type = MAX(Type),
842 GroupBy = MAX(GroupBy),
843 GroupID = MAX(GroupID),
844 Formula = MAX(Formula),
845 Period = MAX(Period),
846 SalesOrgID = MAX(SalesOrgID),
847 SalesAreaID = MAX(SalesAreaID),
848 Level = MAX(Level),
849 Template = MAX(Template),
850 FromDate,
851 ToDate,
852 ObjectType = MAX(ObjectType),
853 CorrectRouteAndCorrectCoordinates,
854 CorrectRouteAndIncorrectCoordinates,
855 IncorrectRoute,
856 DistributorsSell,
857 MinSKUVolume,
858 MinSKURevenue,
859 ExcludeReturnOrder
860 FROM #tmpKPIDetail
861 WHERE Template IS NULL
862 OR Template NOT IN ( 'LPPC', 'AVGPC', 'AVGPCAC', 'DROPSIZER', 'DROPSIZEV', 'DISPLAY%PASS' )
863 GROUP BY CompanyID,
864 CodeListSalesID,
865 CodeListSalesCD,
866 RefNbr,
867 KPIPeriodNbr,
868 FromDate,
869 ToDate,
870 CorrectRouteAndCorrectCoordinates,
871 CorrectRouteAndIncorrectCoordinates,
872 IncorrectRoute,
873 DistributorsSell,
874 MinSKUVolume,
875 MinSKURevenue,
876 ExcludeReturnOrder
877 ) tmp;
878
879 DECLARE @_Min INT;
880 DECLARE @_Max INT;
881 DECLARE @_KPIID INT;
882 DECLARE @_KPICD VARCHAR(50);
883 DECLARE @_KPIDescr NVARCHAR(100);
884 DECLARE @_RefNbr VARCHAR(50);
885 DECLARE @_KPIPeriodNbr VARCHAR(50);
886 DECLARE @_Source CHAR(2);
887 DECLARE @_Type CHAR(1);
888 DECLARE @_GroupBy CHAR(1);
889 DECLARE @_GroupID VARCHAR(50);
890 DECLARE @_Formula CHAR(1);
891 DECLARE @_Template VARCHAR(50);
892 DECLARE @_Period CHAR(1);
893 DECLARE @_SalesAreaID INT;
894 DECLARE @_Level INT;
895 DECLARE @_ObjectType CHAR(1);
896 DECLARE @_IsValiDateValidDistance BIT;
897 DECLARE @_IsValidDateInvalidDistance BIT;
898 DECLARE @_ExcludeReturnOrder BIT;
899 DECLARE @_IsInvalidDate BIT;
900 DECLARE @_IsDistributorSell BIT;
901 DECLARE @_IncludePromotionItem BIT;
902 DECLARE @_IncludeWarrantyItem BIT;
903 DECLARE @_MinSKUVolume DECIMAL(18, 6);
904 DECLARE @_MinSKURevenue DECIMAL(18, 6);
905 SELECT @_Min = 1;
906 SELECT @_Max = MAX(RowNumber)
907 FROM #tmpKPIList;
908 DECLARE @_TempFromDate DATETIME = NULL;
909 DECLARE @_TempToDate DATETIME = NULL;
910 ---- Tính các KPI bình thường
911 ---- Lặp qua tất cả KPI trong danh sách để tính
912 WHILE @_Min <= @_Max
913 BEGIN
914 ---- Xóa dữ liệu SM để tính lại đối với KPI mới
915 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
916 DELETE FROM dbo.DMSBLObjectKPITemp;
917
918 ---- Lấy thông tin công thức KPI
919 SELECT @_KPIID = CodeListSalesID,
920 @_KPICD = CodeListSalesCD,
921 @_KPIDescr = Descr,
922 @_RefNbr = RefNbr,
923 @_KPIPeriodNbr = KPIPeriodNbr,
924 @_Source = Source,
925 @_Type = Type,
926 @_GroupBy = ISNULL(GroupBy, 'A'),
927 @_GroupID = GroupID,
928 @_Formula = Formula,
929 @_Template = Template,
930 @_Period = Period,
931 @_SalesAreaID = SalesAreaID,
932 @_Level = Level,
933 @_FromDate = FromDate,
934 @_ToDate = ToDate,
935 @_ObjectType = ObjectType,
936 @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
937 @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
938 @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
939 @_IsDistributorSell = ISNULL(DistributorsSell, 0),
940 @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
941 @_MinSKURevenue = MinSKURevenue,
942 @_MinSKUVolume = MinSKUVolume
943 FROM #tmpKPIList
944 WHERE RowNumber = @_Min;
945
946 IF @_TempFromDate IS NULL
947 OR @_TempToDate IS NULL
948 OR @_TempFromDate != @_FromDate
949 OR @_TempToDate != @_ToDate
950 BEGIN
951 DELETE FROM #tmpSalesTerritory;
952 DELETE FROM #tmpRoute;
953 DELETE FROM #tmpSalesForce;
954
955 ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
956 INSERT INTO #tmpSalesTerritory
957 SELECT *
958 FROM
959 (
960 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
961 st.SalesOrg_0_ValueID
962 ORDER BY st.EffectiveDate DESC
963 ),
964 st.CompanyID,
965 st.SalesOrg_0_ValueID,
966 st.SalesOrg_1_ValueID,
967 st.SalesOrg_2_ValueID,
968 st.SalesOrg_3_ValueID,
969 st.SalesOrg_4_ValueID,
970 st.SalesOrg_5_ValueID,
971 st.SalesOrg_6_ValueID,
972 st.SalesOrg_7_ValueID,
973 st.SalesOrg_8_ValueID,
974 st.SalesOrg_9_ValueID
975 FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
976 WHERE st.CompanyID = @_CompanyID
977 AND
978 (
979 @_FromDate
980 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
981 OR @_ToDate
982 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
983 OR st.EffectiveDate
984 BETWEEN @_FromDate AND @_ToDate
985 )
986 ) tmp
987 WHERE tmp.RowNumber = 1;
988
989 ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
990 INSERT INTO #tmpRoute
991 SELECT tmp.CompanyID,
992 tmp.SalespersonID,
993 tmp.Route,
994 tmp.SalesForceID,
995 tmp.SalesAreaID,
996 tmp.EffectiveDate,
997 tmp.ExpirationDate,
998 st.SalesOrg_0_ValueID,
999 st.SalesOrg_1_ValueID,
1000 st.SalesOrg_2_ValueID,
1001 st.SalesOrg_3_ValueID,
1002 st.SalesOrg_4_ValueID,
1003 st.SalesOrg_5_ValueID,
1004 st.SalesOrg_6_ValueID,
1005 st.SalesOrg_7_ValueID,
1006 st.SalesOrg_8_ValueID,
1007 st.SalesOrg_9_ValueID
1008 FROM
1009 (
1010 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
1011 rs.SalespersonID
1012 ORDER BY rs.EffectiveDate DESC
1013 ),
1014 rs.CompanyID,
1015 rs.SalespersonID,
1016 Route = rs.RouteCD,
1017 rs.SalesForceID,
1018 rs.SalesAreaID,
1019 rs.EffectiveDate,
1020 ExpirationDate = ISNULL(rs.EndDate, GETDATE())
1021 FROM BELDMS..DMSRouteSetting rs WITH (NOLOCK)
1022 WHERE rs.CompanyID = @_CompanyID
1023 AND
1024 (
1025 @_FromDate
1026 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
1027 OR @_ToDate
1028 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
1029 OR rs.EffectiveDate
1030 BETWEEN @_FromDate AND @_ToDate
1031 )
1032 UNION ALL
1033 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
1034 rs.PreviousSalesMan
1035 ORDER BY rs.StartDatePre DESC
1036 ),
1037 rs.CompanyID,
1038 SalespersonID = rs.PreviousSalesMan,
1039 ROUTE = rs.RouteCD,
1040 rs.SalesForceID,
1041 rs.SalesAreaID,
1042 EffectiveDate = rs.StartDatePre,
1043 ExpirationDate = rs.EndDatePre
1044 FROM BELDMS..DMSRouteSetting rs WITH (NOLOCK)
1045 WHERE rs.CompanyID = @_CompanyID
1046 AND
1047 (
1048 @_FromDate
1049 BETWEEN rs.StartDatePre AND ISNULL(rs.EndDatePre, GETDATE())
1050 OR @_ToDate
1051 BETWEEN rs.StartDatePre AND ISNULL(rs.EndDatePre, GETDATE())
1052 OR rs.StartDatePre
1053 BETWEEN @_FromDate AND @_ToDate
1054 )
1055 AND rs.PreviousSalesMan IS NOT NULL
1056 ) tmp
1057 JOIN #tmpSalesTerritory st
1058 ON st.CompanyID = tmp.CompanyID
1059 AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
1060 WHERE tmp.RowNumber = 1;
1061
1062 ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
1063 INSERT INTO #tmpSalesForce
1064 SELECT tmp.CompanyID,
1065 tmp.EmployeeID,
1066 tmp.SFHierachyID,
1067 tmp.TerritoryType,
1068 tmp.SalesOrgValueID,
1069 st.SalesOrg_0_ValueID,
1070 st.SalesOrg_1_ValueID,
1071 st.SalesOrg_2_ValueID,
1072 st.SalesOrg_3_ValueID,
1073 st.SalesOrg_4_ValueID,
1074 st.SalesOrg_5_ValueID,
1075 st.SalesOrg_6_ValueID,
1076 st.SalesOrg_7_ValueID,
1077 st.SalesOrg_8_ValueID,
1078 st.SalesOrg_9_ValueID
1079 FROM
1080 (
1081 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
1082 sf.EmployeeID
1083 ORDER BY sf.EffectiveDate DESC
1084 ),
1085 sf.CompanyID,
1086 sf.EmployeeID,
1087 sf.SFHierachyID,
1088 sf.TerritoryType,
1089 sf.SalesOrgValueID
1090 FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
1091 WHERE sf.CompanyID = @_CompanyID
1092 AND
1093 (
1094 @_FromDate
1095 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
1096 OR @_ToDate
1097 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
1098 OR sf.EffectiveDate
1099 BETWEEN @_FromDate AND @_ToDate
1100 )
1101 ) tmp
1102 LEFT JOIN #tmpSalesTerritory st
1103 ON st.CompanyID = tmp.CompanyID
1104 AND
1105 (
1106 st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
1107 OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
1108 OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
1109 OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
1110 OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
1111 OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
1112 OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
1113 OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
1114 OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
1115 OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
1116 )
1117 WHERE tmp.RowNumber = 1;
1118
1119 SET @_TempFromDate = @_FromDate;
1120 SET @_TempToDate = @_ToDate;
1121 END;
1122 ---- Tính KPI của đối tượng Sales
1123 IF @_ObjectType = 'S'
1124 BEGIN
1125
1126 ---- Tính KPI doanh số và số lượng, PC, SKU, ASO theo đơn hàng
1127 IF @_Template IN ( 'REVENUE', 'QUANTITY', 'PC', 'SKU', 'ASO', 'ASOROUTE' )
1128 OR @_Source = 'SO'
1129 OR @_Source = 'SD'
1130 OR
1131 (
1132 @_Source = 'CU'
1133 AND @_Type = 'Q'
1134 )
1135 BEGIN
1136 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
1137 SELECT rs.CompanyID,
1138 rs.SalespersonID,
1139 r.SalesForceID,
1140 r.SalesAreaID,
1141 rs.Route,
1142 rs.OrderDate,
1143 rs.SourceType,
1144 rs.CustomerID,
1145 rs.CustomerLocationID,
1146 rs.IsValidDate,
1147 rs.IsValidDistance,
1148 Multiply = CASE
1149 WHEN rs.IsRevenueDeduction = 1 THEN
1150 -1
1151 ELSE
1152 1
1153 END,
1154 PC = 1,
1155 SKU = COUNT(DISTINCT rs.InventoryID),
1156 TranAmt = SUM(rs.TranAmt),
1157 ShippedQty = SUM(rs.ShippedQty),
1158 rs.SalesOrg_0_ValueID,
1159 rs.SalesOrg_1_ValueID,
1160 rs.SalesOrg_2_ValueID,
1161 rs.SalesOrg_3_ValueID,
1162 rs.SalesOrg_4_ValueID,
1163 rs.SalesOrg_5_ValueID,
1164 rs.SalesOrg_6_ValueID,
1165 rs.SalesOrg_7_ValueID,
1166 rs.SalesOrg_8_ValueID,
1167 rs.SalesOrg_9_ValueID,
1168 rs.SalesForce_0_ID,
1169 rs.SalesForce_1_ID,
1170 rs.SalesForce_2_ID,
1171 rs.SalesForce_3_ID,
1172 rs.SalesForce_4_ID,
1173 rs.SalesForce_5_ID,
1174 rs.SalesForce_6_ID,
1175 rs.SalesForce_7_ID,
1176 rs.SalesForce_8_ID,
1177 rs.SalesForce_9_ID,
1178 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
1179 FROM #tmpSORawSales rs
1180 LEFT JOIN BELDMS..DMSViewInventoryItem inv
1181 ON rs.CompanyID = inv.CompanyID
1182 AND rs.InventoryID = inv.InventoryID
1183 LEFT JOIN #tmpRoute r
1184 ON r.CompanyID = rs.CompanyID
1185 AND r.SalespersonID = rs.SalespersonID
1186 AND r.Route = rs.Route
1187 AND rs.OrderDate
1188 BETWEEN r.EffectiveDate AND r.ExpirationDate
1189 WHERE rs.OrderDate
1190 BETWEEN @_TempFromDate AND @_TempToDate
1191 AND rs.OrderType IN ( (CASE
1192 WHEN @_ExcludeReturnOrder = 0 THEN
1193 'CM'
1194 ELSE
1195 ''
1196 END
1197 ), 'SO', 'IN'
1198 )
1199 AND
1200 (
1201 ISNULL(@_GroupID, '') = ''
1202 OR
1203 (
1204 EXISTS
1205 (
1206 SELECT TOP 1
1207 *
1208 FROM BELDMS..DMSKPIGroupProductSalesDetail igd
1209 WHERE igd.CompanyID = @_CompanyID
1210 AND igd.CodegGroupProd = @_GroupID
1211 AND igd.InventoryID = rs.InventoryID
1212 )
1213 OR EXISTS
1214 (
1215 SELECT *
1216 FROM BELDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
1217 WHERE gr.CompanyID = @_CompanyID
1218 AND gr.CodegGroupProd = @_GroupID
1219 AND inv.[HierarchyID] = gr.[HierarchyID]
1220 OR gr.Attribute0 = inv.Attribute0ID
1221 OR gr.Attribute1 = inv.Attribute1ID
1222 OR gr.Attribute2 = inv.Attribute2ID
1223 OR gr.Attribute3 = inv.Attribute3ID
1224 OR gr.Attribute4 = inv.Attribute4ID
1225 OR gr.Attribute5 = inv.Attribute5ID
1226 OR gr.Attribute6 = inv.Attribute6ID
1227 OR gr.Attribute7 = inv.Attribute7ID
1228 OR gr.Attribute8 = inv.Attribute8ID
1229 OR gr.Attribute9 = inv.Attribute9ID
1230 )
1231 )
1232 )
1233 --AND rs.ReasonCode = ( CASE
1234 -- WHEN @_IncludePromotionItem = 1
1235 -- THEN 'ISSKM'
1236 -- WHEN @_IncludeWarrantyItem = 1
1237 -- THEN 'ISSBH'
1238 -- ELSE 'ISS'
1239 -- END )
1240 GROUP BY rs.CompanyID,
1241 rs.SalespersonID,
1242 r.SalesForceID,
1243 r.SalesAreaID,
1244 rs.ROUTE,
1245 rs.OrderDate,
1246 rs.SourceType,
1247 rs.CustomerID,
1248 rs.CustomerLocationID,
1249 rs.IsValidDate,
1250 rs.IsValidDistance,
1251 rs.IsRevenueDeduction,
1252 rs.SalesOrg_0_ValueID,
1253 rs.SalesOrg_1_ValueID,
1254 rs.SalesOrg_2_ValueID,
1255 rs.SalesOrg_3_ValueID,
1256 rs.SalesOrg_4_ValueID,
1257 rs.SalesOrg_5_ValueID,
1258 rs.SalesOrg_6_ValueID,
1259 rs.SalesOrg_7_ValueID,
1260 rs.SalesOrg_8_ValueID,
1261 rs.SalesOrg_9_ValueID,
1262 rs.SalesForce_0_ID,
1263 rs.SalesForce_1_ID,
1264 rs.SalesForce_2_ID,
1265 rs.SalesForce_3_ID,
1266 rs.SalesForce_4_ID,
1267 rs.SalesForce_5_ID,
1268 rs.SalesForce_6_ID,
1269 rs.SalesForce_7_ID,
1270 rs.SalesForce_8_ID,
1271 rs.SalesForce_9_ID
1272 HAVING SUM(rs.ShippedQty) >= @_MinSKUVolume
1273 AND SUM(rs.TranAmt) >= @_MinSKURevenue;
1274
1275 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
1276 @_Template,
1277 @_Source,
1278 @_Type,
1279 @_Formula,
1280 @_IsValiDateValidDistance,
1281 @_IsValidDateInvalidDistance,
1282 @_IsInvalidDate,
1283 @_IsDistributorSell;
1284
1285 ---- KPI for PDA
1286 --EXEC dbo.sp_DMS_Baseline_KPI_For_SFA @_CompanyID,
1287 -- @_Template, @_Source, @_Type, @_Formula,
1288 -- @_IsValiDateValidDistance,
1289 -- @_IsValidDateInvalidDistance,
1290 -- @_IsInvalidDate, @_IsDistributorSell, @_KPICD;
1291
1292 END;
1293 ---- Tính KPI ASO theo hình
1294 ELSE IF @_Template = 'ASOIMG'
1295 OR @_Source = 'CU'
1296 AND @_Type = 'I'
1297 BEGIN
1298 PRINT 'Lấy dữ liệu hình ảnh SM';
1299 END;
1300 ---- Tính KPI viếng thăm KH
1301 ELSE IF @_Template = 'VISIT'
1302 OR @_Source = 'CU'
1303 AND @_Type = 'V'
1304 BEGIN
1305 INSERT INTO dbo.DMSBLObjectKPITemp
1306 (
1307 CompanyID,
1308 ObjectID,
1309 ObjectAssignment,
1310 Actual
1311 )
1312 SELECT rsd.CompanyID,
1313 rsd.SalespersonID,
1314 rsd.ROUTE,
1315 SUM(rsd.TotalActualVisit)
1316 FROM #tmpRouteSalesData rsd
1317 WHERE rsd.TranDate
1318 BETWEEN @_FromDate AND @_ToDate
1319 GROUP BY rsd.CompanyID,
1320 rsd.SalespersonID,
1321 rsd.ROUTE;
1322 END;
1323 ---- Tính KPI mua hàng
1324 ELSE IF @_Source = 'PO'
1325 BEGIN
1326 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
1327 (
1328 CompanyID,
1329 TranAmt,
1330 ShippedQty,
1331 SalesOrg_0_ValueID,
1332 SalesOrg_1_ValueID,
1333 SalesOrg_2_ValueID,
1334 SalesOrg_3_ValueID,
1335 SalesOrg_4_ValueID,
1336 SalesOrg_5_ValueID,
1337 SalesOrg_6_ValueID,
1338 SalesOrg_7_ValueID,
1339 SalesOrg_8_ValueID,
1340 SalesOrg_9_ValueID,
1341 SalesForce_0_ID,
1342 SalesForce_1_ID,
1343 SalesForce_2_ID,
1344 SalesForce_3_ID,
1345 SalesForce_4_ID,
1346 SalesForce_5_ID,
1347 SalesForce_6_ID,
1348 SalesForce_7_ID,
1349 SalesForce_8_ID,
1350 SalesForce_9_ID
1351 )
1352 SELECT rs.CompanyID,
1353 rs.ReceivedAmt,
1354 rs.ReceivedQty,
1355 rs.SalesOrg_0_ValueID,
1356 rs.SalesOrg_1_ValueID,
1357 rs.SalesOrg_2_ValueID,
1358 rs.SalesOrg_3_ValueID,
1359 rs.SalesOrg_4_ValueID,
1360 rs.SalesOrg_5_ValueID,
1361 rs.SalesOrg_6_ValueID,
1362 rs.SalesOrg_7_ValueID,
1363 rs.SalesOrg_8_ValueID,
1364 rs.SalesOrg_9_ValueID,
1365 rs.SalesForce_0_ID,
1366 rs.SalesForce_1_ID,
1367 rs.SalesForce_2_ID,
1368 rs.SalesForce_3_ID,
1369 rs.SalesForce_4_ID,
1370 rs.SalesForce_5_ID,
1371 rs.SalesForce_6_ID,
1372 rs.SalesForce_7_ID,
1373 rs.SalesForce_8_ID,
1374 rs.SalesForce_9_ID
1375 FROM #tmpSIRawSales rs
1376 WHERE rs.TranDate
1377 BETWEEN @_FromDate AND @_ToDate
1378 AND
1379 (
1380 @_GroupBy = 'A'
1381 OR
1382 (
1383 @_GroupBy = 'I'
1384 AND EXISTS
1385 (
1386 SELECT TOP 1
1387 *
1388 FROM BELDMS..DMSKPIGroupProductSalesDetail igd
1389 WHERE igd.CompanyID = @_CompanyID
1390 AND igd.CodegGroupProd = @_GroupID
1391 AND igd.InventoryID = rs.InventoryID
1392 AND ISNULL(igd.MinQty, 1) <= rs.ReceivedQty
1393 )
1394 )
1395 );
1396 EXEC dbo.sp_DMS_Baseline_KPI_SalesInObjectSales @_Type;
1397 END;
1398 ELSE IF @_Template = 'NEWCUSTOMER'
1399 BEGIN
1400
1401 INSERT INTO dbo.DMSBLObjectKPITemp
1402 (
1403 CompanyID,
1404 ObjectID,
1405 Actual
1406 )
1407 SELECT CompanyID,
1408 Salesperson,
1409 TotalOutlet = COUNT(1)
1410 FROM BELDMS.dbo.DMSCalculatorFrequencyPropose
1411 WHERE CompanyID = @_CompanyID
1412 AND createdDate
1413 BETWEEN @_FromDate AND @_ToDate
1414 AND ActionType = 'A'
1415 GROUP BY CompanyID,
1416 Salesperson;
1417 END;
1418 ELSE IF @_Template = 'WEIGHT'
1419 BEGIN
1420 PRINT ('WEIGHT');
1421 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
1422 SELECT rs.CompanyID,
1423 rs.SalespersonID,
1424 r.SalesForceID,
1425 r.SalesAreaID,
1426 rs.ROUTE,
1427 rs.OrderDate,
1428 rs.SourceType,
1429 rs.CustomerID,
1430 rs.CustomerLocationID,
1431 rs.IsValidDate,
1432 rs.IsValidDistance,
1433 Multiply = CASE
1434 WHEN rs.IsRevenueDeduction = 1 THEN
1435 -1
1436 ELSE
1437 1
1438 END,
1439 PC = 1,
1440 SKU = COUNT(DISTINCT rs.InventoryID),
1441 TranAmt = SUM(rs.LineWeight),
1442 ShippedQty = SUM(rs.LineWeight),
1443 rs.SalesOrg_0_ValueID,
1444 rs.SalesOrg_1_ValueID,
1445 rs.SalesOrg_2_ValueID,
1446 rs.SalesOrg_3_ValueID,
1447 rs.SalesOrg_4_ValueID,
1448 rs.SalesOrg_5_ValueID,
1449 rs.SalesOrg_6_ValueID,
1450 rs.SalesOrg_7_ValueID,
1451 rs.SalesOrg_8_ValueID,
1452 rs.SalesOrg_9_ValueID,
1453 rs.SalesForce_0_ID,
1454 rs.SalesForce_1_ID,
1455 rs.SalesForce_2_ID,
1456 rs.SalesForce_3_ID,
1457 rs.SalesForce_4_ID,
1458 rs.SalesForce_5_ID,
1459 rs.SalesForce_6_ID,
1460 rs.SalesForce_7_ID,
1461 rs.SalesForce_8_ID,
1462 rs.SalesForce_9_ID,
1463 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
1464 FROM #tmpSOWeightRawSales rs
1465 LEFT JOIN BELDMS..DMSViewInventoryItem inv
1466 ON rs.CompanyID = inv.CompanyID
1467 AND rs.InventoryID = inv.InventoryID
1468 LEFT JOIN #tmpRoute r
1469 ON r.CompanyID = rs.CompanyID
1470 AND r.SalespersonID = rs.SalespersonID
1471 AND r.ROUTE = rs.ROUTE
1472 AND rs.OrderDate
1473 BETWEEN r.EffectiveDate AND r.ExpirationDate
1474 WHERE rs.OrderDate
1475 BETWEEN @_TempFromDate AND @_TempToDate
1476 AND rs.OrderType IN ( (CASE
1477 WHEN @_ExcludeReturnOrder = 0 THEN
1478 'CM'
1479 ELSE
1480 ''
1481 END
1482 ), 'SO', 'IN'
1483 )
1484 AND
1485 (
1486 ISNULL(@_GroupID, '') = ''
1487 OR
1488 (
1489 EXISTS
1490 (
1491 SELECT TOP 1
1492 *
1493 FROM BELDMS..DMSKPIGroupProductSalesDetail igd
1494 WHERE igd.CompanyID = @_CompanyID
1495 AND igd.CodegGroupProd = @_GroupID
1496 AND igd.InventoryID = rs.InventoryID
1497 )
1498 OR EXISTS
1499 (
1500 SELECT *
1501 FROM BELDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
1502 WHERE gr.CompanyID = @_CompanyID
1503 AND gr.CodegGroupProd = @_GroupID
1504 AND inv.[HierarchyID] = gr.[HierarchyID]
1505 OR gr.Attribute0 = inv.Attribute0ID
1506 OR gr.Attribute1 = inv.Attribute1ID
1507 OR gr.Attribute2 = inv.Attribute2ID
1508 OR gr.Attribute3 = inv.Attribute3ID
1509 OR gr.Attribute4 = inv.Attribute4ID
1510 OR gr.Attribute5 = inv.Attribute5ID
1511 OR gr.Attribute6 = inv.Attribute6ID
1512 OR gr.Attribute7 = inv.Attribute7ID
1513 OR gr.Attribute8 = inv.Attribute8ID
1514 OR gr.Attribute9 = inv.Attribute9ID
1515 )
1516 )
1517 )
1518 AND
1519 (
1520 rs.IsFree = 1
1521 OR rs.ShippedQty >= @_MinSKUVolume
1522 )
1523 AND
1524 (
1525 rs.IsFree = 1
1526 OR ABS(rs.TranAmt) >= @_MinSKURevenue
1527 )
1528 --AND rs.ReasonCode = ( CASE
1529 -- WHEN @_IncludePromotionItem = 1
1530 -- THEN 'ISSKM'
1531 -- WHEN @_IncludeWarrantyItem = 1
1532 -- THEN 'ISSBH'
1533 -- ELSE 'ISS'
1534 -- END )
1535 GROUP BY rs.CompanyID,
1536 rs.SalespersonID,
1537 r.SalesForceID,
1538 r.SalesAreaID,
1539 rs.ROUTE,
1540 rs.OrderDate,
1541 rs.SourceType,
1542 rs.CustomerID,
1543 rs.CustomerLocationID,
1544 rs.IsValidDate,
1545 rs.IsValidDistance,
1546 rs.IsRevenueDeduction,
1547 rs.SalesOrg_0_ValueID,
1548 rs.SalesOrg_1_ValueID,
1549 rs.SalesOrg_2_ValueID,
1550 rs.SalesOrg_3_ValueID,
1551 rs.SalesOrg_4_ValueID,
1552 rs.SalesOrg_5_ValueID,
1553 rs.SalesOrg_6_ValueID,
1554 rs.SalesOrg_7_ValueID,
1555 rs.SalesOrg_8_ValueID,
1556 rs.SalesOrg_9_ValueID,
1557 rs.SalesForce_0_ID,
1558 rs.SalesForce_1_ID,
1559 rs.SalesForce_2_ID,
1560 rs.SalesForce_3_ID,
1561 rs.SalesForce_4_ID,
1562 rs.SalesForce_5_ID,
1563 rs.SalesForce_6_ID,
1564 rs.SalesForce_7_ID,
1565 rs.SalesForce_8_ID,
1566 rs.SalesForce_9_ID;
1567
1568 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
1569 @_Template,
1570 @_Source,
1571 @_Type,
1572 @_Formula,
1573 @_IsValiDateValidDistance,
1574 @_IsValidDateInvalidDistance,
1575 @_IsInvalidDate,
1576 @_IsDistributorSell;
1577 END;
1578
1579
1580
1581 END;
1582 ELSE IF @_ObjectType = 'D'
1583 BEGIN
1584 ---- Tính KPI doanh số và số lượng
1585 IF @_Template IN ( 'REVENUE', 'QUANTITY' )
1586 OR @_Source = 'SO'
1587 BEGIN
1588 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
1589 SELECT rs.CompanyID,
1590 rs.DistributorLocationID,
1591 NULL,
1592 NULL,
1593 NULL,
1594 rs.OrderDate,
1595 rs.SourceType,
1596 rs.CustomerID,
1597 rs.CustomerLocationID,
1598 rs.IsValidDate,
1599 rs.IsValidDistance,
1600 Multiply = CASE
1601 WHEN rs.IsRevenueDeduction = 1 THEN
1602 -1
1603 ELSE
1604 1
1605 END,
1606 PC = 1,
1607 SKU = COUNT(DISTINCT rs.InventoryID),
1608 TranAmt = SUM(rs.TranAmt),
1609 ShippedQty = SUM(rs.ShippedQty),
1610 NULL,
1611 NULL,
1612 NULL,
1613 NULL,
1614 NULL,
1615 NULL,
1616 NULL,
1617 NULL,
1618 NULL,
1619 NULL,
1620 NULL,
1621 NULL,
1622 NULL,
1623 NULL,
1624 NULL,
1625 NULL,
1626 NULL,
1627 NULL,
1628 NULL,
1629 NULL,
1630 NULL
1631 FROM #tmpSORawSales rs
1632 LEFT JOIN BELDMS..DMSViewInventoryItem inv
1633 ON rs.CompanyID = inv.CompanyID
1634 AND rs.InventoryID = inv.InventoryID
1635 LEFT JOIN #tmpRoute r
1636 ON r.CompanyID = rs.CompanyID
1637 AND r.SalespersonID = rs.SalespersonID
1638 AND r.ROUTE = rs.ROUTE
1639 AND rs.OrderDate
1640 BETWEEN r.EffectiveDate AND r.ExpirationDate
1641 WHERE rs.OrderDate
1642 BETWEEN @_FromDate AND @_ToDate
1643 AND rs.OrderType IN ( (CASE
1644 WHEN @_ExcludeReturnOrder = 0 THEN
1645 'CM'
1646 ELSE
1647 ''
1648 END
1649 ), 'SO'
1650 )
1651 AND
1652 (
1653 ISNULL(@_GroupID, '') = ''
1654 OR
1655 (
1656 EXISTS
1657 (
1658 SELECT TOP 1
1659 *
1660 FROM BELDMS..DMSKPIGroupProductSalesDetail igd
1661 WHERE igd.CompanyID = @_CompanyID
1662 AND igd.CodegGroupProd = @_GroupID
1663 AND igd.InventoryID = rs.InventoryID
1664 )
1665 OR EXISTS
1666 (
1667 SELECT *
1668 FROM BELDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
1669 WHERE gr.CompanyID = @_CompanyID
1670 AND gr.CodegGroupProd = @_GroupID
1671 AND inv.[HierarchyID] = gr.[HierarchyID]
1672 OR gr.Attribute0 = inv.Attribute0ID
1673 OR gr.Attribute1 = inv.Attribute1ID
1674 OR gr.Attribute2 = inv.Attribute2ID
1675 OR gr.Attribute3 = inv.Attribute3ID
1676 OR gr.Attribute4 = inv.Attribute4ID
1677 OR gr.Attribute5 = inv.Attribute5ID
1678 OR gr.Attribute6 = inv.Attribute6ID
1679 OR gr.Attribute7 = inv.Attribute7ID
1680 OR gr.Attribute8 = inv.Attribute8ID
1681 OR gr.Attribute9 = inv.Attribute9ID
1682 )
1683 )
1684 )
1685 AND rs.ShippedQty >= @_MinSKUVolume
1686 AND ABS(rs.TranAmt) >= @_MinSKURevenue
1687 --AND rs.ReasonCode = ( CASE
1688 -- WHEN @_IncludePromotionItem = 1
1689 -- THEN 'ISSKM'
1690 -- WHEN @_IncludeWarrantyItem = 1
1691 -- THEN 'ISSBH'
1692 -- ELSE 'ISS'
1693 -- END )
1694 GROUP BY rs.CompanyID,
1695 rs.DistributorLocationID,
1696 rs.OrderDate,
1697 rs.SourceType,
1698 rs.CustomerID,
1699 rs.CustomerLocationID,
1700 rs.IsValidDate,
1701 rs.IsValidDistance,
1702 rs.IsRevenueDeduction;
1703
1704 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectDistributor @_CompanyID,
1705 @_Template,
1706 @_Source,
1707 @_Type,
1708 @_Formula,
1709 @_IsValiDateValidDistance,
1710 @_IsValidDateInvalidDistance,
1711 @_IsInvalidDate,
1712 @_IsDistributorSell;
1713 END;
1714 ---- Tính KPI mua hàng
1715 ELSE IF @_Source = 'PO'
1716 BEGIN
1717 INSERT INTO dbo.DMSBLObjectKPITemp
1718 (
1719 CompanyID,
1720 ObjectID,
1721 ObjectAssignment,
1722 Actual
1723 )
1724 SELECT rs.CompanyID,
1725 rs.DistributorLocationID,
1726 rs.SalesOrg_0_ValueID,
1727 SUM( CASE
1728 WHEN @_Type = 'A' THEN
1729 rs.ReceivedAmt
1730 ELSE
1731 rs.ReceivedQty
1732 END
1733 )
1734 FROM #tmpSIRawSales rs
1735 WHERE rs.TranDate
1736 BETWEEN @_FromDate AND @_ToDate
1737 AND
1738 (
1739 @_GroupBy = 'A'
1740 OR
1741 (
1742 @_GroupBy = 'I'
1743 AND EXISTS
1744 (
1745 SELECT TOP 1
1746 *
1747 FROM BELDMS..DMSKPIGroupProductSalesDetail igd
1748 WHERE igd.CompanyID = @_CompanyID
1749 AND igd.CodegGroupProd = @_GroupID
1750 AND igd.InventoryID = rs.InventoryID
1751 AND ISNULL(igd.MinQty, 1) <= rs.ReceivedQty
1752 )
1753 )
1754 )
1755 GROUP BY rs.CompanyID,
1756 rs.DistributorLocationID,
1757 rs.SalesOrg_0_ValueID;
1758 END;
1759 END;
1760
1761 ---- Insert vào dữ liệu KPI
1762
1763 IF @_ObjectType = 'S'
1764 BEGIN
1765 INSERT INTO #tmpKPI
1766 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID,
1767 tmp.EmployeeID
1768 ORDER BY tmp.EmployeeID ASC
1769 ),
1770 CompanyID = tmp.CompanyID,
1771 @_KPIID,
1772 @_KPICD,
1773 @_KPIDescr,
1774 @_Source,
1775 @_Type,
1776 @_GroupBy,
1777 @_GroupID,
1778 @_Formula,
1779 @_Template,
1780 @_KPIPeriodNbr,
1781 ISNULL(
1782 @_RefNbr,
1783 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
1784 + CONVERT(VARCHAR(20), @_ToDate, 111)
1785 ),
1786 @_Period,
1787 @_FromDate,
1788 @_ToDate,
1789 ObjectID = tmp.EmployeeID,
1790 TempObjectID = ISNULL(kpid.ObjectID, 0),
1791 @_ObjectType,
1792 tmp.ObjectAssignment,
1793 kpid.TargetsSuggest, ---- temp
1794 kpid.TargetsAssigned, ---- temp
1795 kpid.TargetsSuggest,
1796 kpid.TargetsAssigned,
1797 okpit.Actual1,
1798 okpit.Actual2,
1799 okpit.Actual3,
1800 okpit.Actual4,
1801 okpit.Actual
1802 FROM
1803 (
1804 SELECT sf.CompanyID,
1805 sf.EmployeeID,
1806 ObjectAssignment = CASE
1807 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
1808 r.ROUTE
1809 ELSE
1810 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
1811 END
1812 FROM BELDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
1813 JOIN #tmpSalesForce sf WITH (NOLOCK)
1814 ON sf.CompanyID = objectPeriod.CompanyID
1815 AND sf.SFHierachyID = objectPeriod.KPIObjectID
1816 LEFT JOIN #tmpRoute r
1817 ON r.CompanyID = sf.CompanyID
1818 AND
1819 (
1820 r.SalespersonID = sf.EmployeeID
1821 OR r.SalesForceID = sf.EmployeeID
1822 )
1823 WHERE objectPeriod.CompanyID = @_CompanyID
1824 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
1825 AND
1826 (
1827 @_SalesAreaID IS NULL
1828 OR @_Level = -1
1829 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
1830 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
1831 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
1832 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
1833 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
1834 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
1835 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
1836 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
1837 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
1838 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
1839 )
1840 GROUP BY sf.CompanyID,
1841 sf.EmployeeID,
1842 CASE
1843 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
1844 r.ROUTE
1845 ELSE
1846 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
1847 END
1848 ) tmp
1849 LEFT JOIN
1850 (
1851 SELECT kpid.CompanyID,
1852 kpid.ObjectID,
1853 kpid.TargetsSuggest,
1854 kpid.TargetsAssigned
1855 FROM #tmpKPIDetail kpid
1856 WHERE kpid.CodeListSalesID = @_KPIID
1857 AND
1858 (
1859 @_RefNbr LIKE 'Temp-%'
1860 OR kpid.RefNbr = @_RefNbr
1861 )
1862 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
1863 AND kpid.FromDate = @_FromDate
1864 AND kpid.ToDate = @_ToDate
1865 ) kpid
1866 ON kpid.CompanyID = tmp.CompanyID
1867 AND kpid.ObjectID = tmp.EmployeeID
1868 LEFT JOIN dbo.DMSBLObjectKPITemp okpit WITH (NOLOCK)
1869 ON okpit.CompanyID = tmp.CompanyID
1870 AND okpit.ObjectID = tmp.EmployeeID
1871 AND okpit.ObjectAssignment = tmp.ObjectAssignment
1872 WHERE kpid.ObjectID IS NOT NULL;
1873
1874
1875 END;
1876 ELSE IF @_ObjectType = 'D'
1877 BEGIN
1878 INSERT INTO #tmpKPI
1879 SELECT ROW_NUMBER() OVER (PARTITION BY dl.CompanyID,
1880 dl.DistributorLocationID
1881 ORDER BY dl.DistributorLocationID ASC
1882 ),
1883 CompanyID = dl.CompanyID,
1884 @_KPIID,
1885 @_KPICD,
1886 @_KPIDescr,
1887 @_Source,
1888 @_Type,
1889 @_GroupBy,
1890 @_GroupID,
1891 @_Formula,
1892 @_Template,
1893 @_KPIPeriodNbr,
1894 ISNULL(
1895 @_RefNbr,
1896 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
1897 + CONVERT(VARCHAR(20), @_ToDate, 111)
1898 ),
1899 @_Period,
1900 @_FromDate,
1901 @_ToDate,
1902 ObjectID = dl.DistributorLocationID,
1903 TempObjectID = ISNULL(kpid.ObjectID, 0),
1904 @_ObjectType,
1905 ObjectAssignment = dl.SalesOrg_0_ValueID,
1906 kpid.TargetsSuggest, ---- temp
1907 kpid.TargetsAssigned, ---- temp
1908 kpid.TargetsSuggest,
1909 kpid.TargetsAssigned,
1910 okpit.Actual1,
1911 okpit.Actual2,
1912 okpit.Actual3,
1913 okpit.Actual4,
1914 okpit.Actual
1915 FROM
1916 (
1917 SELECT CompanyID,
1918 DistributorLocationID,
1919 SalesOrg_0_ValueID
1920 FROM #tmpDistributorLocation
1921 WHERE ExpirationDate IS NULL
1922 AND
1923 (
1924 @_SalesAreaID IS NULL
1925 OR @_Level = -1
1926 OR SalesOrg_0_ValueID = @_SalesAreaID
1927 OR SalesOrg_1_ValueID = @_SalesAreaID
1928 OR SalesOrg_2_ValueID = @_SalesAreaID
1929 OR SalesOrg_3_ValueID = @_SalesAreaID
1930 OR SalesOrg_4_ValueID = @_SalesAreaID
1931 OR SalesOrg_5_ValueID = @_SalesAreaID
1932 OR SalesOrg_6_ValueID = @_SalesAreaID
1933 OR SalesOrg_7_ValueID = @_SalesAreaID
1934 OR SalesOrg_8_ValueID = @_SalesAreaID
1935 OR SalesOrg_9_ValueID = @_SalesAreaID
1936 )
1937 GROUP BY CompanyID,
1938 DistributorLocationID,
1939 SalesOrg_0_ValueID
1940 ) dl
1941 LEFT JOIN
1942 (
1943 SELECT kpid.CompanyID,
1944 kpid.ObjectID,
1945 kpid.TargetsSuggest,
1946 kpid.TargetsAssigned
1947 FROM #tmpKPIDetail kpid
1948 WHERE kpid.CodeListSalesID = @_KPIID
1949 AND
1950 (
1951 @_RefNbr LIKE 'Temp-%'
1952 OR kpid.RefNbr = @_RefNbr
1953 )
1954 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
1955 AND kpid.FromDate = @_FromDate
1956 AND kpid.ToDate = @_ToDate
1957 ) kpid
1958 ON kpid.CompanyID = dl.CompanyID
1959 AND kpid.ObjectID = dl.DistributorLocationID
1960 LEFT JOIN dbo.DMSBLObjectKPITemp okpit WITH (NOLOCK)
1961 ON okpit.CompanyID = dl.CompanyID
1962 AND okpit.ObjectID = dl.DistributorLocationID
1963 AND okpit.ObjectAssignment = dl.SalesOrg_0_ValueID
1964 WHERE kpid.ObjectID IS NOT NULL;
1965
1966 END;
1967
1968 SET @_Min = @_Min + 1;
1969 END;
1970
1971 ---- Lấy danh sách MCP trong khoảng thời gian baseline
1972 SELECT *
1973 INTO #tmpMCP
1974 FROM
1975 (
1976 SELECT mcp.CompanyID,
1977 mcp.SalespersonID,
1978 mcp.ROUTE,
1979 mcp.VisitDate
1980 FROM dbo.DMSBLMCP mcp WITH (NOLOCK)
1981 WHERE mcp.CompanyID = @_CompanyID
1982 AND mcp.VisitDate
1983 BETWEEN @_FromDate AND @_ToDate
1984 UNION ALL
1985 SELECT mcp.CompanyID,
1986 mcp.SalespersonID,
1987 mcp.ROUTE,
1988 mcp.VisitDate
1989 FROM dbo.DMSBLMCPHistory mcp WITH (NOLOCK)
1990 WHERE mcp.CompanyID = @_CompanyID
1991 AND mcp.VisitDate
1992 BETWEEN @_FromDate AND @_ToDate
1993 ) tmp;
1994
1995 ---- Tính KPI AVGPC (%PC)
1996 ---- Formula: Total PC/ Visit ( Actual or MCP)
1997 DELETE FROM #tmpKPIList;
1998
1999 INSERT INTO #tmpKPIList
2000 SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
2001 *
2002 FROM
2003 (
2004 SELECT CompanyID,
2005 CodeListSalesID,
2006 CodeListSalesCD,
2007 RefNbr,
2008 KPIPeriodNbr,
2009 Descr = MAX(Descr),
2010 SOURCE = MAX(SOURCE),
2011 TYPE = MAX(TYPE),
2012 GroupBy = MAX(GroupBy),
2013 GroupID = MAX(GroupID),
2014 Formula = MAX(Formula),
2015 PERIOD = MAX(PERIOD),
2016 SalesOrgID = MAX(SalesOrgID),
2017 SalesAreaID = MAX(SalesAreaID),
2018 LEVEL = MAX(LEVEL),
2019 Template = MAX(Template),
2020 FromDate,
2021 ToDate,
2022 ObjectType = MAX(ObjectType),
2023 CorrectRouteAndCorrectCoordinates,
2024 CorrectRouteAndIncorrectCoordinates,
2025 IncorrectRoute,
2026 DistributorsSell,
2027 MinSKUVolume,
2028 MinSKURevenue,
2029 ExcludeReturnOrder
2030 FROM #tmpKPIDetail
2031 -- lấy AVG theo actual visit hoặc MCP
2032 WHERE Template IN ( 'AVGPC', 'AVGPCAC' )
2033 AND ObjectType = 'S'
2034 GROUP BY CompanyID,
2035 CodeListSalesID,
2036 CodeListSalesCD,
2037 RefNbr,
2038 KPIPeriodNbr,
2039 FromDate,
2040 ToDate,
2041 CorrectRouteAndCorrectCoordinates,
2042 CorrectRouteAndIncorrectCoordinates,
2043 IncorrectRoute,
2044 DistributorsSell,
2045 MinSKUVolume,
2046 MinSKURevenue,
2047 ExcludeReturnOrder
2048 ) tmp;
2049
2050 SELECT @_Max = MAX(RowNumber)
2051 FROM #tmpKPIList;
2052 DECLARE @_step INT = 1;
2053
2054 WHILE @_step <= 2
2055 BEGIN
2056 SELECT @_Min = 1;
2057 WHILE @_Min <= @_Max
2058 BEGIN
2059 ---- Xóa dữ liệu SM để tính lại đối với KPI mới
2060 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
2061 DELETE FROM dbo.DMSBLObjectKPITemp;
2062 DELETE FROM #tmpTotalPC;
2063 DELETE FROM #tmpTotalLine;
2064
2065 ---- Lấy thông tin công thức KPI
2066 SELECT @_KPIID = CodeListSalesID,
2067 @_KPICD = CodeListSalesCD,
2068 @_KPIDescr = Descr,
2069 @_RefNbr = RefNbr,
2070 @_KPIPeriodNbr = KPIPeriodNbr,
2071 @_Source = SOURCE,
2072 @_Type = TYPE,
2073 @_GroupBy = ISNULL(GroupBy, 'A'),
2074 @_GroupID = GroupID,
2075 @_Formula = Formula,
2076 @_Template = Template,
2077 @_Period = PERIOD,
2078 @_SalesAreaID = SalesAreaID,
2079 @_Level = LEVEL,
2080 @_FromDate = FromDate,
2081 @_ToDate = ToDate,
2082 @_ObjectType = ObjectType,
2083 @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
2084 @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
2085 @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
2086 @_IsDistributorSell = ISNULL(DistributorsSell, 0),
2087 @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
2088 @_MinSKURevenue = MinSKURevenue,
2089 @_MinSKUVolume = MinSKUVolume
2090 FROM #tmpKPIList
2091 WHERE RowNumber = @_Min;
2092
2093 IF @_TempFromDate IS NULL
2094 OR @_TempToDate IS NULL
2095 OR @_TempFromDate != @_FromDate
2096 OR @_TempToDate != @_ToDate
2097 BEGIN
2098 DELETE FROM #tmpSalesTerritory;
2099 DELETE FROM #tmpRoute;
2100 DELETE FROM #tmpSalesForce;
2101
2102 ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
2103 INSERT INTO #tmpSalesTerritory
2104 SELECT *
2105 FROM
2106 (
2107 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
2108 st.SalesOrg_0_ValueID
2109 ORDER BY st.EffectiveDate DESC
2110 ),
2111 st.CompanyID,
2112 st.SalesOrg_0_ValueID,
2113 st.SalesOrg_1_ValueID,
2114 st.SalesOrg_2_ValueID,
2115 st.SalesOrg_3_ValueID,
2116 st.SalesOrg_4_ValueID,
2117 st.SalesOrg_5_ValueID,
2118 st.SalesOrg_6_ValueID,
2119 st.SalesOrg_7_ValueID,
2120 st.SalesOrg_8_ValueID,
2121 st.SalesOrg_9_ValueID
2122 FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
2123 WHERE st.CompanyID = @_CompanyID
2124 AND
2125 (
2126 @_FromDate
2127 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
2128 OR @_ToDate
2129 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
2130 OR st.EffectiveDate
2131 BETWEEN @_FromDate AND @_ToDate
2132 )
2133 ) tmp
2134 WHERE tmp.RowNumber = 1;
2135
2136 ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
2137 INSERT INTO #tmpRoute
2138 SELECT tmp.CompanyID,
2139 tmp.SalespersonID,
2140 tmp.ROUTE,
2141 tmp.SalesForceID,
2142 tmp.SalesAreaID,
2143 tmp.EffectiveDate,
2144 tmp.ExpirationDate,
2145 st.SalesOrg_0_ValueID,
2146 st.SalesOrg_1_ValueID,
2147 st.SalesOrg_2_ValueID,
2148 st.SalesOrg_3_ValueID,
2149 st.SalesOrg_4_ValueID,
2150 st.SalesOrg_5_ValueID,
2151 st.SalesOrg_6_ValueID,
2152 st.SalesOrg_7_ValueID,
2153 st.SalesOrg_8_ValueID,
2154 st.SalesOrg_9_ValueID
2155 FROM
2156 (
2157 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
2158 rs.SalespersonID
2159 ORDER BY rs.EffectiveDate DESC
2160 ),
2161 rs.CompanyID,
2162 rs.SalespersonID,
2163 ROUTE = rs.RouteCD,
2164 rs.SalesForceID,
2165 rs.SalesAreaID,
2166 rs.EffectiveDate,
2167 ExpirationDate = ISNULL(rs.EndDate, GETDATE())
2168 FROM BELDMS..DMSRouteSetting rs WITH (NOLOCK)
2169 WHERE rs.CompanyID = @_CompanyID
2170 AND
2171 (
2172 @_FromDate
2173 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
2174 OR @_ToDate
2175 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
2176 OR rs.EffectiveDate
2177 BETWEEN @_FromDate AND @_ToDate
2178 )
2179 UNION ALL
2180 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
2181 rs.PreviousSalesMan
2182 ORDER BY rs.StartDatePre DESC
2183 ),
2184 rs.CompanyID,
2185 SalespersonID = rs.PreviousSalesMan,
2186 ROUTE = rs.RouteCD,
2187 rs.SalesForceID,
2188 rs.SalesAreaID,
2189 EffectiveDate = rs.StartDatePre,
2190 ExpirationDate = rs.EndDatePre
2191 FROM BELDMS..DMSRouteSetting rs WITH (NOLOCK)
2192 WHERE rs.CompanyID = @_CompanyID
2193 AND
2194 (
2195 @_FromDate
2196 BETWEEN rs.StartDatePre AND ISNULL(rs.EndDatePre, GETDATE())
2197 OR @_ToDate
2198 BETWEEN rs.StartDatePre AND ISNULL(rs.EndDatePre, GETDATE())
2199 OR rs.StartDatePre
2200 BETWEEN @_FromDate AND @_ToDate
2201 )
2202 AND rs.PreviousSalesMan IS NOT NULL
2203 ) tmp
2204 JOIN #tmpSalesTerritory st
2205 ON st.CompanyID = tmp.CompanyID
2206 AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
2207 WHERE tmp.RowNumber = 1;
2208
2209 ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
2210 INSERT INTO #tmpSalesForce
2211 SELECT tmp.CompanyID,
2212 tmp.EmployeeID,
2213 tmp.SFHierachyID,
2214 tmp.TerritoryType,
2215 tmp.SalesOrgValueID,
2216 st.SalesOrg_0_ValueID,
2217 st.SalesOrg_1_ValueID,
2218 st.SalesOrg_2_ValueID,
2219 st.SalesOrg_3_ValueID,
2220 st.SalesOrg_4_ValueID,
2221 st.SalesOrg_5_ValueID,
2222 st.SalesOrg_6_ValueID,
2223 st.SalesOrg_7_ValueID,
2224 st.SalesOrg_8_ValueID,
2225 st.SalesOrg_9_ValueID
2226 FROM
2227 (
2228 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
2229 sf.EmployeeID
2230 ORDER BY sf.EffectiveDate DESC
2231 ),
2232 sf.CompanyID,
2233 sf.EmployeeID,
2234 sf.SFHierachyID,
2235 sf.TerritoryType,
2236 sf.SalesOrgValueID
2237 FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
2238 WHERE sf.CompanyID = @_CompanyID
2239 AND
2240 (
2241 @_FromDate
2242 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
2243 OR @_ToDate
2244 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
2245 OR sf.EffectiveDate
2246 BETWEEN @_FromDate AND @_ToDate
2247 )
2248 ) tmp
2249 LEFT JOIN #tmpSalesTerritory st
2250 ON st.CompanyID = tmp.CompanyID
2251 AND
2252 (
2253 st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
2254 OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
2255 OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
2256 OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
2257 OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
2258 OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
2259 OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
2260 OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
2261 OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
2262 OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
2263 )
2264 WHERE tmp.RowNumber = 1;
2265
2266 SET @_TempFromDate = @_FromDate;
2267 SET @_TempToDate = @_ToDate;
2268 END;
2269
2270
2271
2272 ---- Chỉ tính KPI cho SM
2273 IF @_step = 1
2274 BEGIN
2275 ---- Tính KPI PC tạm để tính ra AVG PC
2276 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
2277 SELECT rs.CompanyID,
2278 rs.SalespersonID,
2279 r.SalesForceID,
2280 r.SalesAreaID,
2281 rs.ROUTE,
2282 rs.OrderDate,
2283 rs.SourceType,
2284 rs.CustomerID,
2285 rs.CustomerLocationID,
2286 rs.IsValidDate,
2287 rs.IsValidDistance,
2288 Multiply = CASE
2289 WHEN rs.IsRevenueDeduction = 1 THEN
2290 -1
2291 ELSE
2292 1
2293 END,
2294 PC = COUNT(DISTINCT rs.OrderNbr),
2295 SKU = COUNT(DISTINCT rs.InventoryID),
2296 TranAmt = SUM(rs.TranAmt),
2297 ShippedQty = SUM(rs.ShippedQty),
2298 rs.SalesOrg_0_ValueID,
2299 rs.SalesOrg_1_ValueID,
2300 rs.SalesOrg_2_ValueID,
2301 rs.SalesOrg_3_ValueID,
2302 rs.SalesOrg_4_ValueID,
2303 rs.SalesOrg_5_ValueID,
2304 rs.SalesOrg_6_ValueID,
2305 rs.SalesOrg_7_ValueID,
2306 rs.SalesOrg_8_ValueID,
2307 rs.SalesOrg_9_ValueID,
2308 rs.SalesForce_0_ID,
2309 rs.SalesForce_1_ID,
2310 rs.SalesForce_2_ID,
2311 rs.SalesForce_3_ID,
2312 rs.SalesForce_4_ID,
2313 rs.SalesForce_5_ID,
2314 rs.SalesForce_6_ID,
2315 rs.SalesForce_7_ID,
2316 rs.SalesForce_8_ID,
2317 rs.SalesForce_9_ID,
2318 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
2319 FROM #tmpSORawSales rs
2320 LEFT JOIN BELDMS..DMSViewInventoryItem inv
2321 ON rs.CompanyID = inv.CompanyID
2322 AND rs.InventoryID = inv.InventoryID
2323 LEFT JOIN #tmpRoute r
2324 ON r.CompanyID = rs.CompanyID
2325 AND r.SalespersonID = rs.SalespersonID
2326 AND r.ROUTE = rs.ROUTE
2327 AND rs.OrderDate
2328 BETWEEN r.EffectiveDate AND r.ExpirationDate
2329 WHERE rs.OrderDate
2330 BETWEEN @_TempFromDate AND @_TempToDate
2331 AND rs.OrderType IN ( (CASE
2332 WHEN @_ExcludeReturnOrder = 0 THEN
2333 'CM'
2334 ELSE
2335 ''
2336 END
2337 ), 'SO', 'IN'
2338 )
2339 AND
2340 (
2341 ISNULL(@_GroupID, '') = ''
2342 OR
2343 (
2344 EXISTS
2345 (
2346 SELECT TOP 1
2347 *
2348 FROM BELDMS..DMSKPIGroupProductSalesDetail igd
2349 WHERE igd.CompanyID = @_CompanyID
2350 AND igd.CodegGroupProd = @_GroupID
2351 AND igd.InventoryID = rs.InventoryID
2352 )
2353 OR EXISTS
2354 (
2355 SELECT *
2356 FROM BELDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
2357 WHERE gr.CompanyID = @_CompanyID
2358 AND gr.CodegGroupProd = @_GroupID
2359 AND inv.[HierarchyID] = gr.[HierarchyID]
2360 OR gr.Attribute0 = inv.Attribute0ID
2361 OR gr.Attribute1 = inv.Attribute1ID
2362 OR gr.Attribute2 = inv.Attribute2ID
2363 OR gr.Attribute3 = inv.Attribute3ID
2364 OR gr.Attribute4 = inv.Attribute4ID
2365 OR gr.Attribute5 = inv.Attribute5ID
2366 OR gr.Attribute6 = inv.Attribute6ID
2367 OR gr.Attribute7 = inv.Attribute7ID
2368 OR gr.Attribute8 = inv.Attribute8ID
2369 OR gr.Attribute9 = inv.Attribute9ID
2370 )
2371 )
2372 )
2373 AND rs.ShippedQty >= @_MinSKUVolume
2374 AND ABS(rs.TranAmt) >= @_MinSKURevenue --nghia.tran: 2019.07.13
2375 --AND rs.ReasonCode = ( CASE
2376 -- WHEN @_IncludePromotionItem = 1
2377 -- THEN 'ISSKM'
2378 -- WHEN @_IncludeWarrantyItem = 1
2379 -- THEN 'ISSBH'
2380 -- ELSE 'ISS'
2381 -- END )
2382 GROUP BY rs.CompanyID,
2383 rs.SalespersonID,
2384 r.SalesForceID,
2385 r.SalesAreaID,
2386 rs.ROUTE,
2387 rs.OrderDate,
2388 rs.SourceType,
2389 rs.CustomerID,
2390 rs.CustomerLocationID,
2391 rs.IsValidDate,
2392 rs.IsValidDistance,
2393 rs.IsRevenueDeduction,
2394 rs.SalesOrg_0_ValueID,
2395 rs.SalesOrg_1_ValueID,
2396 rs.SalesOrg_2_ValueID,
2397 rs.SalesOrg_3_ValueID,
2398 rs.SalesOrg_4_ValueID,
2399 rs.SalesOrg_5_ValueID,
2400 rs.SalesOrg_6_ValueID,
2401 rs.SalesOrg_7_ValueID,
2402 rs.SalesOrg_8_ValueID,
2403 rs.SalesOrg_9_ValueID,
2404 rs.SalesForce_0_ID,
2405 rs.SalesForce_1_ID,
2406 rs.SalesForce_2_ID,
2407 rs.SalesForce_3_ID,
2408 rs.SalesForce_4_ID,
2409 rs.SalesForce_5_ID,
2410 rs.SalesForce_6_ID,
2411 rs.SalesForce_7_ID,
2412 rs.SalesForce_8_ID,
2413 rs.SalesForce_9_ID;
2414
2415 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
2416 'PC',
2417 NULL,
2418 NULL,
2419 NULL,
2420 @_IsValiDateValidDistance,
2421 @_IsValidDateInvalidDistance,
2422 @_IsInvalidDate,
2423 @_IsDistributorSell;
2424
2425 INSERT INTO #tmpTotalPC
2426 SELECT *
2427 FROM dbo.DMSBLObjectKPITemp;
2428 ---- Kết thúc tính KPI PC tạm
2429 -- TÍNH AVG PC theo lịch đi viếng thăm
2430
2431 UPDATE kpipc
2432 SET kpipc.Actual1 = CASE
2433 WHEN @_IsValiDateValidDistance = 1
2434 AND ISNULL(pd.PlanDay, 0) > 0 THEN
2435 kpipc.Actual1 / pd.PlanDay
2436 ELSE
2437 0
2438 END,
2439 kpipc.Actual2 = CASE
2440 WHEN @_IsValidDateInvalidDistance = 1
2441 AND ISNULL(pd.PlanDay, 0) > 0 THEN
2442 kpipc.Actual2 / pd.PlanDay
2443 ELSE
2444 0
2445 END,
2446 kpipc.Actual3 = CASE
2447 WHEN @_IsInvalidDate = 1
2448 AND ISNULL(pd.PlanDay, 0) > 0 THEN
2449 kpipc.Actual3 / pd.PlanDay
2450 ELSE
2451 0
2452 END,
2453 kpipc.Actual4 = CASE
2454 WHEN @_IsDistributorSell = 1
2455 AND ISNULL(pd.PlanDay, 0) > 0 THEN
2456 kpipc.Actual4 / pd.PlanDay
2457 ELSE
2458 0
2459 END,
2460 kpipc.Actual = CASE
2461 WHEN ISNULL(pd.PlanDay, 0) > 0 THEN
2462 kpipc.Actual / pd.PlanDay
2463 ELSE
2464 0
2465 END
2466 FROM #tmpTotalPC kpipc
2467 LEFT JOIN
2468 (
2469 --- Dựa theo template của KPI lấy số viếng thăm theo MCP hoặc Actual để chia cho số đơn đơn hàng
2470 SELECT CompanyID,
2471 SalespersonID,
2472 PlanDay = CASE
2473 WHEN @_Template = 'AVGPC' THEN
2474 SUM(TotalMCPVisit)
2475 WHEN @_Template = 'AVGPCPC' THEN
2476 SUM(TotalActualVisit)
2477 END
2478 FROM #tmpRouteSalesData rsd
2479 WHERE rsd.TranDate
2480 BETWEEN @_FromDate AND @_ToDate
2481 GROUP BY rsd.CompanyID,
2482 rsd.SalespersonID
2483 ) pd
2484 ON pd.CompanyID = kpipc.CompanyID
2485 AND pd.SalespersonID = kpipc.ObjectID;
2486
2487
2488 INSERT INTO #tmpKPI
2489 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
2490 tmp.CompanyID,
2491 @_KPIID,
2492 @_KPICD,
2493 @_KPIDescr,
2494 @_Source,
2495 @_Type,
2496 @_GroupBy,
2497 @_GroupID,
2498 @_Formula,
2499 @_Template,
2500 @_KPIPeriodNbr,
2501 ISNULL(
2502 @_RefNbr,
2503 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
2504 + CONVERT(VARCHAR(20), @_ToDate, 111)
2505 ),
2506 @_Period,
2507 @_FromDate,
2508 @_ToDate,
2509 ObjectID = tmp.EmployeeID,
2510 TempObjectID = ISNULL(kpid.ObjectID, 0),
2511 'S',
2512 tmp.ObjectAssignment,
2513 kpid.TargetsSuggest, ---- temp
2514 kpid.TargetsAssigned, ---- temp
2515 kpid.TargetsSuggest,
2516 kpid.TargetsAssigned,
2517 kpia.Actual1,
2518 kpia.Actual2,
2519 kpia.Actual3,
2520 kpia.Actual4,
2521 kpia.Actual
2522 FROM
2523 (
2524 SELECT sf.CompanyID,
2525 sf.EmployeeID,
2526 ObjectAssignment = r.ROUTE
2527 FROM BELDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
2528 JOIN #tmpSalesForce sf WITH (NOLOCK)
2529 ON sf.CompanyID = objectPeriod.CompanyID
2530 AND sf.SFHierachyID = objectPeriod.KPIObjectID
2531 JOIN #tmpRoute r
2532 ON r.CompanyID = sf.CompanyID
2533 AND r.SalespersonID = sf.EmployeeID
2534 WHERE objectPeriod.CompanyID = @_CompanyID
2535 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
2536 AND sf.TerritoryType = 'S'
2537 AND
2538 (
2539 @_SalesAreaID IS NULL
2540 OR @_Level = -1
2541 OR r.SalesOrg_1_ValueID = @_SalesAreaID
2542 OR r.SalesOrg_2_ValueID = @_SalesAreaID
2543 OR r.SalesOrg_3_ValueID = @_SalesAreaID
2544 OR r.SalesOrg_4_ValueID = @_SalesAreaID
2545 OR r.SalesOrg_5_ValueID = @_SalesAreaID
2546 OR r.SalesOrg_6_ValueID = @_SalesAreaID
2547 OR r.SalesOrg_7_ValueID = @_SalesAreaID
2548 OR r.SalesOrg_8_ValueID = @_SalesAreaID
2549 OR r.SalesOrg_9_ValueID = @_SalesAreaID
2550 )
2551 GROUP BY sf.CompanyID,
2552 sf.EmployeeID,
2553 r.ROUTE
2554 ) tmp
2555 LEFT JOIN
2556 (
2557 SELECT kpid.CompanyID,
2558 kpid.ObjectID,
2559 kpid.KPIPeriodNbr,
2560 kpid.FromDate,
2561 kpid.ToDate,
2562 kpid.TargetsSuggest,
2563 kpid.TargetsAssigned
2564 FROM #tmpKPIDetail kpid
2565 WHERE kpid.CodeListSalesID = @_KPIID
2566 AND
2567 (
2568 @_RefNbr LIKE 'Temp-%'
2569 OR kpid.RefNbr = @_RefNbr
2570 )
2571 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
2572 AND kpid.FromDate = @_FromDate
2573 AND kpid.ToDate = @_ToDate
2574 ) kpid
2575 ON kpid.CompanyID = tmp.CompanyID
2576 AND kpid.ObjectID = tmp.EmployeeID
2577 LEFT JOIN #tmpTotalPC kpia
2578 ON kpia.CompanyID = tmp.CompanyID
2579 AND kpia.ObjectID = tmp.EmployeeID
2580 AND kpia.ObjectAssignment = tmp.ObjectAssignment
2581 WHERE kpid.ObjectID IS NOT NULL;
2582 END;
2583 ---- Tính KPI cho các đối tượng còn lại
2584 ELSE IF @_step = 2
2585 BEGIN
2586 INSERT INTO #tmpKPI
2587 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
2588 tmp.CompanyID,
2589 @_KPIID,
2590 @_KPICD,
2591 @_KPIDescr,
2592 @_Source,
2593 @_Type,
2594 @_GroupBy,
2595 @_GroupID,
2596 @_Formula,
2597 @_Template,
2598 @_KPIPeriodNbr,
2599 ISNULL(
2600 @_RefNbr,
2601 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
2602 + CONVERT(VARCHAR(20), @_ToDate, 111)
2603 ),
2604 @_Period,
2605 @_FromDate,
2606 @_ToDate,
2607 ObjectID = tmp.EmployeeID,
2608 TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
2609 'S',
2610 tmp.ObjectAssignment,
2611 TargetsSuggest = MAX(kpid.TargetsSuggest), ---- temp
2612 TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
2613 TargetsSuggest = MAX(kpid.TargetsSuggest),
2614 TargetsAssigned = MAX(kpid.TargetsAssigned),
2615 Actual1 = SUM(kpi.Actual1),
2616 Actual2 = SUM(kpi.Actual2),
2617 Actual3 = SUM(kpi.Actual3),
2618 Actual4 = SUM(kpi.Actual4),
2619 Actual = SUM(kpi.Actual)
2620 FROM
2621 (
2622 SELECT sf.CompanyID,
2623 sf.EmployeeID,
2624 ObjectAssignment = CASE
2625 WHEN sf.TerritoryType = 'D' THEN
2626 r.ROUTE
2627 ELSE
2628 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
2629 END,
2630 sf.SalesOrg_0_ValueID
2631 FROM BELDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
2632 JOIN #tmpSalesForce sf WITH (NOLOCK)
2633 ON sf.CompanyID = objectPeriod.CompanyID
2634 AND sf.SFHierachyID = objectPeriod.KPIObjectID
2635 LEFT JOIN #tmpRoute r
2636 ON r.CompanyID = sf.CompanyID
2637 AND r.SalesForceID = sf.EmployeeID
2638 WHERE objectPeriod.CompanyID = @_CompanyID
2639 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
2640 AND sf.TerritoryType != 'S'
2641 AND
2642 (
2643 @_SalesAreaID IS NULL
2644 OR @_Level = -1
2645 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
2646 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
2647 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
2648 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
2649 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
2650 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
2651 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
2652 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
2653 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
2654 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
2655 )
2656 GROUP BY sf.CompanyID,
2657 sf.EmployeeID,
2658 CASE
2659 WHEN sf.TerritoryType = 'D' THEN
2660 r.ROUTE
2661 ELSE
2662 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
2663 END,
2664 sf.SalesOrg_0_ValueID
2665 ) tmp
2666 LEFT JOIN
2667 (
2668 SELECT kpid.CompanyID,
2669 kpid.ObjectID,
2670 kpid.KPIPeriodNbr,
2671 kpid.FromDate,
2672 kpid.ToDate,
2673 kpid.TargetsSuggest,
2674 kpid.TargetsAssigned
2675 FROM #tmpKPIDetail kpid
2676 WHERE kpid.CodeListSalesID = @_KPIID
2677 AND
2678 (
2679 @_RefNbr LIKE 'Temp-%'
2680 OR kpid.RefNbr = @_RefNbr
2681 )
2682 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
2683 AND kpid.FromDate = @_FromDate
2684 AND kpid.ToDate = @_ToDate
2685 ) kpid
2686 ON kpid.CompanyID = tmp.CompanyID
2687 AND kpid.ObjectID = tmp.EmployeeID
2688 LEFT JOIN
2689 (
2690 SELECT DISTINCT
2691 kpi.CompanyID,
2692 kpi.Actual1,
2693 kpi.Actual2,
2694 kpi.Actual3,
2695 kpi.Actual4,
2696 kpi.Actual,
2697 r.ROUTE,
2698 r.SalesAreaID
2699 FROM #tmpKPI kpi
2700 JOIN #tmpSalesForce sf
2701 ON sf.CompanyID = kpi.CompanyID
2702 AND sf.EmployeeID = kpi.ObjectID
2703 AND sf.TerritoryType = 'S'
2704 JOIN #tmpRoute r
2705 ON r.CompanyID = sf.CompanyID
2706 AND r.SalespersonID = sf.EmployeeID
2707 WHERE kpi.CodeListSalesID = @_KPIID
2708 AND kpi.FromDate = @_FromDate
2709 AND kpi.ToDate = @_ToDate
2710 ) kpi
2711 ON kpi.CompanyID = tmp.CompanyID
2712 AND
2713 (
2714 kpi.ROUTE = tmp.ObjectAssignment
2715 OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
2716 )
2717 WHERE kpid.ObjectID IS NOT NULL
2718 GROUP BY tmp.CompanyID,
2719 tmp.EmployeeID,
2720 tmp.ObjectAssignment;
2721 END;
2722
2723 SET @_Min = @_Min + 1;
2724 END;
2725 SET @_step = @_step + 1;
2726 END;
2727
2728 ---- Tính KPI LPPC
2729 DELETE FROM #tmpKPIList;
2730
2731 INSERT INTO #tmpKPIList
2732 SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
2733 *
2734 FROM
2735 (
2736 SELECT CompanyID,
2737 CodeListSalesID,
2738 CodeListSalesCD,
2739 RefNbr,
2740 KPIPeriodNbr,
2741 Descr = MAX(Descr),
2742 SOURCE = MAX(SOURCE),
2743 TYPE = MAX(TYPE),
2744 GroupBy = MAX(GroupBy),
2745 GroupID = MAX(GroupID),
2746 Formula = MAX(Formula),
2747 PERIOD = MAX(PERIOD),
2748 SalesOrgID = MAX(SalesOrgID),
2749 SalesAreaID = MAX(SalesAreaID),
2750 LEVEL = MAX(LEVEL),
2751 Template = MAX(Template),
2752 FromDate,
2753 ToDate,
2754 ObjectType = MAX(ObjectType),
2755 CorrectRouteAndCorrectCoordinates,
2756 CorrectRouteAndIncorrectCoordinates,
2757 IncorrectRoute,
2758 DistributorsSell,
2759 MinSKUVolume,
2760 MinSKURevenue,
2761 ExcludeReturnOrder
2762 FROM #tmpKPIDetail
2763 WHERE Template = 'LPPC'
2764 AND ObjectType = 'S'
2765 GROUP BY CompanyID,
2766 CodeListSalesID,
2767 CodeListSalesCD,
2768 RefNbr,
2769 KPIPeriodNbr,
2770 FromDate,
2771 ToDate,
2772 CorrectRouteAndCorrectCoordinates,
2773 CorrectRouteAndIncorrectCoordinates,
2774 IncorrectRoute,
2775 DistributorsSell,
2776 MinSKUVolume,
2777 MinSKURevenue,
2778 ExcludeReturnOrder
2779 ) tmp;
2780
2781 SELECT @_Max = MAX(RowNumber)
2782 FROM #tmpKPIList;
2783
2784 SELECT @_step = 1;
2785 WHILE @_step <= 2
2786 BEGIN
2787 SELECT @_Min = 1;
2788 WHILE @_Min <= @_Max
2789 BEGIN
2790 ---- Xóa dữ liệu SM để tính lại đối với KPI mới
2791 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
2792 DELETE FROM dbo.DMSBLObjectKPITemp;
2793 DELETE FROM #tmpTotalPC;
2794 DELETE FROM #tmpTotalLine;
2795
2796 ---- Lấy thông tin công thức KPI
2797 SELECT @_KPIID = CodeListSalesID,
2798 @_KPICD = CodeListSalesCD,
2799 @_KPIDescr = Descr,
2800 @_RefNbr = RefNbr,
2801 @_KPIPeriodNbr = KPIPeriodNbr,
2802 @_Source = SOURCE,
2803 @_Type = TYPE,
2804 @_GroupBy = ISNULL(GroupBy, 'A'),
2805 @_GroupID = GroupID,
2806 @_Formula = Formula,
2807 @_Template = Template,
2808 @_Period = PERIOD,
2809 @_SalesAreaID = SalesAreaID,
2810 @_Level = LEVEL,
2811 @_FromDate = FromDate,
2812 @_ToDate = ToDate,
2813 @_ObjectType = ObjectType,
2814 @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
2815 @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
2816 @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
2817 @_IsDistributorSell = ISNULL(DistributorsSell, 0),
2818 @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
2819 @_MinSKURevenue = MinSKURevenue,
2820 @_MinSKUVolume = MinSKUVolume
2821 FROM #tmpKPIList
2822 WHERE RowNumber = @_Min;
2823
2824 IF @_TempFromDate IS NULL
2825 OR @_TempToDate IS NULL
2826 OR @_TempFromDate != @_FromDate
2827 OR @_TempToDate != @_ToDate
2828 BEGIN
2829 DELETE FROM #tmpSalesTerritory;
2830 DELETE FROM #tmpRoute;
2831 DELETE FROM #tmpSalesForce;
2832
2833 ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
2834 INSERT INTO #tmpSalesTerritory
2835 SELECT *
2836 FROM
2837 (
2838 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
2839 st.SalesOrg_0_ValueID
2840 ORDER BY st.EffectiveDate DESC
2841 ),
2842 st.CompanyID,
2843 st.SalesOrg_0_ValueID,
2844 st.SalesOrg_1_ValueID,
2845 st.SalesOrg_2_ValueID,
2846 st.SalesOrg_3_ValueID,
2847 st.SalesOrg_4_ValueID,
2848 st.SalesOrg_5_ValueID,
2849 st.SalesOrg_6_ValueID,
2850 st.SalesOrg_7_ValueID,
2851 st.SalesOrg_8_ValueID,
2852 st.SalesOrg_9_ValueID
2853 FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
2854 WHERE st.CompanyID = @_CompanyID
2855 AND
2856 (
2857 @_FromDate
2858 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
2859 OR @_ToDate
2860 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
2861 OR st.EffectiveDate
2862 BETWEEN @_FromDate AND @_ToDate
2863 )
2864 ) tmp
2865 WHERE tmp.RowNumber = 1;
2866
2867 ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
2868 INSERT INTO #tmpRoute
2869 SELECT tmp.CompanyID,
2870 tmp.SalespersonID,
2871 tmp.ROUTE,
2872 tmp.SalesForceID,
2873 tmp.SalesAreaID,
2874 tmp.EffectiveDate,
2875 tmp.ExpirationDate,
2876 st.SalesOrg_0_ValueID,
2877 st.SalesOrg_1_ValueID,
2878 st.SalesOrg_2_ValueID,
2879 st.SalesOrg_3_ValueID,
2880 st.SalesOrg_4_ValueID,
2881 st.SalesOrg_5_ValueID,
2882 st.SalesOrg_6_ValueID,
2883 st.SalesOrg_7_ValueID,
2884 st.SalesOrg_8_ValueID,
2885 st.SalesOrg_9_ValueID
2886 FROM
2887 (
2888 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
2889 rs.SalespersonID
2890 ORDER BY rs.EffectiveDate DESC
2891 ),
2892 rs.CompanyID,
2893 rs.SalespersonID,
2894 ROUTE = rs.RouteCD,
2895 rs.SalesForceID,
2896 rs.SalesAreaID,
2897 rs.EffectiveDate,
2898 ExpirationDate = ISNULL(rs.EndDate, GETDATE())
2899 FROM BELDMS..DMSRouteSetting rs WITH (NOLOCK)
2900 WHERE rs.CompanyID = @_CompanyID
2901 AND
2902 (
2903 @_FromDate
2904 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
2905 OR @_ToDate
2906 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
2907 OR rs.EffectiveDate
2908 BETWEEN @_FromDate AND @_ToDate
2909 )
2910 UNION ALL
2911 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
2912 rs.PreviousSalesMan
2913 ORDER BY rs.StartDatePre DESC
2914 ),
2915 rs.CompanyID,
2916 SalespersonID = rs.PreviousSalesMan,
2917 ROUTE = rs.RouteCD,
2918 rs.SalesForceID,
2919 rs.SalesAreaID,
2920 EffectiveDate = rs.StartDatePre,
2921 ExpirationDate = rs.EndDatePre
2922 FROM BELDMS..DMSRouteSetting rs WITH (NOLOCK)
2923 WHERE rs.CompanyID = @_CompanyID
2924 AND
2925 (
2926 @_FromDate
2927 BETWEEN rs.StartDatePre AND ISNULL(rs.EndDatePre, GETDATE())
2928 OR @_ToDate
2929 BETWEEN rs.StartDatePre AND ISNULL(rs.EndDatePre, GETDATE())
2930 OR rs.StartDatePre
2931 BETWEEN @_FromDate AND @_ToDate
2932 )
2933 AND rs.PreviousSalesMan IS NOT NULL
2934 ) tmp
2935 JOIN #tmpSalesTerritory st
2936 ON st.CompanyID = tmp.CompanyID
2937 AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
2938 WHERE tmp.RowNumber = 1;
2939
2940 ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
2941 INSERT INTO #tmpSalesForce
2942 SELECT tmp.CompanyID,
2943 tmp.EmployeeID,
2944 tmp.SFHierachyID,
2945 tmp.TerritoryType,
2946 tmp.SalesOrgValueID,
2947 st.SalesOrg_0_ValueID,
2948 st.SalesOrg_1_ValueID,
2949 st.SalesOrg_2_ValueID,
2950 st.SalesOrg_3_ValueID,
2951 st.SalesOrg_4_ValueID,
2952 st.SalesOrg_5_ValueID,
2953 st.SalesOrg_6_ValueID,
2954 st.SalesOrg_7_ValueID,
2955 st.SalesOrg_8_ValueID,
2956 st.SalesOrg_9_ValueID
2957 FROM
2958 (
2959 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
2960 sf.EmployeeID
2961 ORDER BY sf.EffectiveDate DESC
2962 ),
2963 sf.CompanyID,
2964 sf.EmployeeID,
2965 sf.SFHierachyID,
2966 sf.TerritoryType,
2967 sf.SalesOrgValueID
2968 FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
2969 WHERE sf.CompanyID = @_CompanyID
2970 AND
2971 (
2972 @_FromDate
2973 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
2974 OR @_ToDate
2975 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
2976 OR sf.EffectiveDate
2977 BETWEEN @_FromDate AND @_ToDate
2978 )
2979 ) tmp
2980 LEFT JOIN #tmpSalesTerritory st
2981 ON st.CompanyID = tmp.CompanyID
2982 AND
2983 (
2984 st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
2985 OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
2986 OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
2987 OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
2988 OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
2989 OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
2990 OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
2991 OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
2992 OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
2993 OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
2994 )
2995 WHERE tmp.RowNumber = 1;
2996
2997 SET @_TempFromDate = @_FromDate;
2998 SET @_TempToDate = @_ToDate;
2999 END;
3000
3001 ---- Tính KPI PC tạm để tính ra LPPC
3002 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
3003 SELECT rs.CompanyID,
3004 rs.SalespersonID,
3005 r.SalesForceID,
3006 r.SalesAreaID,
3007 rs.ROUTE,
3008 rs.OrderDate,
3009 rs.SourceType,
3010 rs.CustomerID,
3011 rs.CustomerLocationID,
3012 rs.IsValidDate,
3013 rs.IsValidDistance,
3014 Multiply = CASE
3015 WHEN rs.IsRevenueDeduction = 1 THEN
3016 -1
3017 ELSE
3018 1
3019 END,
3020 PC = COUNT(DISTINCT rs.OrderNbr),
3021 SKU = COUNT(DISTINCT rs.InventoryID),
3022 TranAmt = SUM(rs.TranAmt),
3023 ShippedQty = SUM(rs.ShippedQty),
3024 rs.SalesOrg_0_ValueID,
3025 rs.SalesOrg_1_ValueID,
3026 rs.SalesOrg_2_ValueID,
3027 rs.SalesOrg_3_ValueID,
3028 rs.SalesOrg_4_ValueID,
3029 rs.SalesOrg_5_ValueID,
3030 rs.SalesOrg_6_ValueID,
3031 rs.SalesOrg_7_ValueID,
3032 rs.SalesOrg_8_ValueID,
3033 rs.SalesOrg_9_ValueID,
3034 rs.SalesForce_0_ID,
3035 rs.SalesForce_1_ID,
3036 rs.SalesForce_2_ID,
3037 rs.SalesForce_3_ID,
3038 rs.SalesForce_4_ID,
3039 rs.SalesForce_5_ID,
3040 rs.SalesForce_6_ID,
3041 rs.SalesForce_7_ID,
3042 rs.SalesForce_8_ID,
3043 rs.SalesForce_9_ID,
3044 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
3045 FROM #tmpSORawSales rs
3046 LEFT JOIN BELDMS..DMSViewInventoryItem inv
3047 ON rs.CompanyID = inv.CompanyID
3048 AND rs.InventoryID = inv.InventoryID
3049 LEFT JOIN #tmpRoute r
3050 ON r.CompanyID = rs.CompanyID
3051 AND r.SalespersonID = rs.SalespersonID
3052 AND r.ROUTE = rs.ROUTE
3053 AND rs.OrderDate
3054 BETWEEN r.EffectiveDate AND r.ExpirationDate
3055 WHERE rs.OrderDate
3056 BETWEEN @_TempFromDate AND @_TempToDate
3057 AND rs.OrderType IN ( (CASE
3058 WHEN @_ExcludeReturnOrder = 0 THEN
3059 'CM'
3060 ELSE
3061 ''
3062 END
3063 ), 'SO', 'IN'
3064 )
3065 AND
3066 (
3067 ISNULL(@_GroupID, '') = ''
3068 OR
3069 (
3070 EXISTS
3071 (
3072 SELECT TOP 1
3073 *
3074 FROM BELDMS..DMSKPIGroupProductSalesDetail igd
3075 WHERE igd.CompanyID = @_CompanyID
3076 AND igd.CodegGroupProd = @_GroupID
3077 AND igd.InventoryID = rs.InventoryID
3078 )
3079 OR EXISTS
3080 (
3081 SELECT *
3082 FROM BELDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
3083 WHERE gr.CompanyID = @_CompanyID
3084 AND gr.CodegGroupProd = @_GroupID
3085 AND inv.[HierarchyID] = gr.[HierarchyID]
3086 OR gr.Attribute0 = inv.Attribute0ID
3087 OR gr.Attribute1 = inv.Attribute1ID
3088 OR gr.Attribute2 = inv.Attribute2ID
3089 OR gr.Attribute3 = inv.Attribute3ID
3090 OR gr.Attribute4 = inv.Attribute4ID
3091 OR gr.Attribute5 = inv.Attribute5ID
3092 OR gr.Attribute6 = inv.Attribute6ID
3093 OR gr.Attribute7 = inv.Attribute7ID
3094 OR gr.Attribute8 = inv.Attribute8ID
3095 OR gr.Attribute9 = inv.Attribute9ID
3096 )
3097 )
3098 )
3099 AND rs.ShippedQty >= @_MinSKUVolume
3100 AND ABS(rs.TranAmt) >= @_MinSKURevenue
3101 --AND rs.ReasonCode = ( CASE
3102 -- WHEN @_IncludePromotionItem = 1
3103 -- THEN 'ISSKM'
3104 -- WHEN @_IncludeWarrantyItem = 1
3105 -- THEN 'ISSBH'
3106 -- ELSE 'ISS'
3107 -- END )
3108 GROUP BY rs.CompanyID,
3109 rs.SalespersonID,
3110 r.SalesForceID,
3111 r.SalesAreaID,
3112 rs.ROUTE,
3113 rs.OrderDate,
3114 rs.SourceType,
3115 rs.CustomerID,
3116 rs.CustomerLocationID,
3117 rs.IsValidDate,
3118 rs.IsValidDistance,
3119 rs.IsRevenueDeduction,
3120 rs.SalesOrg_0_ValueID,
3121 rs.SalesOrg_1_ValueID,
3122 rs.SalesOrg_2_ValueID,
3123 rs.SalesOrg_3_ValueID,
3124 rs.SalesOrg_4_ValueID,
3125 rs.SalesOrg_5_ValueID,
3126 rs.SalesOrg_6_ValueID,
3127 rs.SalesOrg_7_ValueID,
3128 rs.SalesOrg_8_ValueID,
3129 rs.SalesOrg_9_ValueID,
3130 rs.SalesForce_0_ID,
3131 rs.SalesForce_1_ID,
3132 rs.SalesForce_2_ID,
3133 rs.SalesForce_3_ID,
3134 rs.SalesForce_4_ID,
3135 rs.SalesForce_5_ID,
3136 rs.SalesForce_6_ID,
3137 rs.SalesForce_7_ID,
3138 rs.SalesForce_8_ID,
3139 rs.SalesForce_9_ID,
3140 rs.OrderNbr;
3141
3142
3143 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
3144 'PC',
3145 NULL,
3146 NULL,
3147 NULL,
3148 @_IsValiDateValidDistance,
3149 @_IsValidDateInvalidDistance,
3150 @_IsInvalidDate,
3151 @_IsDistributorSell;
3152
3153 INSERT INTO #tmpTotalPC
3154 SELECT *
3155 FROM dbo.DMSBLObjectKPITemp;
3156
3157
3158 DELETE FROM dbo.DMSBLObjectKPITemp;
3159
3160 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
3161 'SKU',
3162 NULL,
3163 NULL,
3164 NULL,
3165 @_IsValiDateValidDistance,
3166 @_IsValidDateInvalidDistance,
3167 @_IsInvalidDate,
3168 @_IsDistributorSell;
3169
3170 INSERT INTO #tmpTotalLine
3171 SELECT *
3172 FROM dbo.DMSBLObjectKPITemp;
3173 ---- Kết thúc tính KPI LPPC tạm
3174
3175 IF @_step = 1
3176 BEGIN
3177 INSERT INTO #tmpKPI
3178 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
3179 CompanyID = tmp.CompanyID,
3180 @_KPIID,
3181 @_KPICD,
3182 @_KPIDescr,
3183 @_Source,
3184 @_Type,
3185 @_GroupBy,
3186 @_GroupID,
3187 @_Formula,
3188 @_Template,
3189 @_KPIPeriodNbr,
3190 ISNULL(
3191 @_RefNbr,
3192 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
3193 + CONVERT(VARCHAR(20), @_ToDate, 111)
3194 ),
3195 @_Period,
3196 @_FromDate,
3197 @_ToDate,
3198 ObjectID = tmp.EmployeeID,
3199 TempObjectID = ISNULL(kpid.ObjectID, 0),
3200 'S',
3201 tmp.ObjectAssignment,
3202 kpid.TargetsSuggest, ---- temp
3203 kpid.TargetsAssigned, ---- temp
3204 kpid.TargetsSuggest,
3205 kpid.TargetsAssigned,
3206 kpia.Actual1,
3207 kpia.Actual2,
3208 kpia.Actual3,
3209 kpia.Actual4,
3210 kpia.Actual
3211 FROM
3212 (
3213 SELECT sf.CompanyID,
3214 sf.EmployeeID,
3215 ObjectAssignment = CASE
3216 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
3217 r.ROUTE
3218 ELSE
3219 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
3220 END
3221 FROM BELDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
3222 JOIN #tmpSalesForce sf WITH (NOLOCK)
3223 ON sf.CompanyID = objectPeriod.CompanyID
3224 AND sf.SFHierachyID = objectPeriod.KPIObjectID
3225 LEFT JOIN #tmpRoute r
3226 ON r.CompanyID = sf.CompanyID
3227 AND
3228 (
3229 r.SalespersonID = sf.EmployeeID
3230 OR r.SalesForceID = sf.EmployeeID
3231 )
3232 WHERE objectPeriod.CompanyID = @_CompanyID
3233 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
3234 AND
3235 (
3236 @_SalesAreaID IS NULL
3237 OR @_Level = -1
3238 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
3239 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
3240 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
3241 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
3242 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
3243 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
3244 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
3245 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
3246 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
3247 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
3248 )
3249 GROUP BY sf.CompanyID,
3250 sf.EmployeeID,
3251 CASE
3252 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
3253 r.ROUTE
3254 ELSE
3255 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
3256 END
3257 ) tmp
3258 LEFT JOIN
3259 (
3260 SELECT kpid.CompanyID,
3261 kpid.ObjectID,
3262 kpid.KPIPeriodNbr,
3263 kpid.FromDate,
3264 kpid.ToDate,
3265 kpid.TargetsSuggest,
3266 kpid.TargetsAssigned
3267 FROM #tmpKPIDetail kpid
3268 WHERE kpid.CodeListSalesID = @_KPIID
3269 AND
3270 (
3271 @_RefNbr LIKE 'Temp-%'
3272 OR kpid.RefNbr = @_RefNbr
3273 )
3274 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
3275 AND kpid.FromDate = @_FromDate
3276 AND kpid.ToDate = @_ToDate
3277 ) kpid
3278 ON kpid.CompanyID = tmp.CompanyID
3279 AND kpid.ObjectID = tmp.EmployeeID
3280 LEFT JOIN
3281 (
3282 SELECT kpipc.CompanyID,
3283 kpipc.ObjectID,
3284 kpipc.ObjectAssignment,
3285 Actual1 = CASE
3286 WHEN @_IsValiDateValidDistance = 1
3287 AND ISNULL(kpipc.Actual1, 0) > 0 THEN
3288 kpisku.Actual1 / kpipc.Actual1
3289 ELSE
3290 0
3291 END,
3292 Actual2 = CASE
3293 WHEN @_IsValidDateInvalidDistance = 1
3294 AND ISNULL(kpipc.Actual2, 0) > 0 THEN
3295 kpisku.Actual2 / kpipc.Actual2
3296 ELSE
3297 0
3298 END,
3299 Actual3 = CASE
3300 WHEN @_IsInvalidDate = 1
3301 AND ISNULL(kpipc.Actual3, 0) > 0 THEN
3302 kpisku.Actual3 / kpipc.Actual3
3303 ELSE
3304 0
3305 END,
3306 Actual4 = CASE
3307 WHEN @_IsDistributorSell = 1
3308 AND ISNULL(kpipc.Actual4, 0) > 0 THEN
3309 kpisku.Actual4 / kpipc.Actual4
3310 ELSE
3311 0
3312 END,
3313 Actual = CASE
3314 WHEN ISNULL(kpipc.Actual, 0) > 0 THEN
3315 kpisku.Actual / kpipc.Actual
3316 ELSE
3317 0
3318 END
3319 FROM #tmpTotalPC kpipc
3320 JOIN #tmpTotalLine kpisku
3321 ON kpisku.CompanyID = kpipc.CompanyID
3322 AND kpisku.ObjectID = kpipc.ObjectID
3323 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
3324 JOIN #tmpSalesForce sf
3325 ON sf.CompanyID = kpipc.CompanyID
3326 AND sf.EmployeeID = kpipc.ObjectID
3327 AND sf.TerritoryType = 'S'
3328 JOIN #tmpRoute r
3329 ON r.CompanyID = sf.CompanyID
3330 AND r.SalespersonID = sf.EmployeeID
3331 ) kpia
3332 ON kpia.CompanyID = tmp.CompanyID
3333 AND kpia.ObjectID = tmp.EmployeeID
3334 AND kpia.ObjectAssignment = tmp.ObjectAssignment
3335 WHERE kpid.ObjectID IS NOT NULL;
3336 END;
3337 ELSE IF @_step = 2
3338 BEGIN
3339 INSERT INTO #tmpKPI
3340 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
3341 tmp.CompanyID,
3342 @_KPIID,
3343 @_KPICD,
3344 @_KPIDescr,
3345 @_Source,
3346 @_Type,
3347 @_GroupBy,
3348 @_GroupID,
3349 @_Formula,
3350 @_Template,
3351 @_KPIPeriodNbr,
3352 ISNULL(
3353 @_RefNbr,
3354 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
3355 + CONVERT(VARCHAR(20), @_ToDate, 111)
3356 ),
3357 @_Period,
3358 @_FromDate,
3359 @_ToDate,
3360 ObjectID = tmp.EmployeeID,
3361 TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
3362 'S',
3363 tmp.ObjectAssignment,
3364 TargetsSuggest = MAX(kpid.TargetsSuggest), ---- temp
3365 TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
3366 TargetsSuggest = MAX(kpid.TargetsSuggest),
3367 TargetsAssigned = MAX(kpid.TargetsAssigned),
3368 Actual1 = CASE
3369 WHEN @_IsValiDateValidDistance = 1
3370 AND SUM(kpi.PC1) > 0 THEN
3371 SUM(kpi.SKU1) / SUM(kpi.PC1)
3372 ELSE
3373 0
3374 END,
3375 Actual2 = CASE
3376 WHEN @_IsValidDateInvalidDistance = 1
3377 AND SUM(kpi.PC2) > 0 THEN
3378 SUM(kpi.SKU2) / SUM(kpi.PC2)
3379 ELSE
3380 0
3381 END,
3382 Actual3 = CASE
3383 WHEN @_IsInvalidDate = 1
3384 AND SUM(kpi.PC3) > 0 THEN
3385 SUM(kpi.SKU3) / SUM(kpi.PC3)
3386 ELSE
3387 0
3388 END,
3389 Actual4 = CASE
3390 WHEN @_IsDistributorSell = 1
3391 AND SUM(kpi.PC4) > 0 THEN
3392 SUM(kpi.SKU4) / SUM(kpi.PC4)
3393 ELSE
3394 0
3395 END,
3396 Actual = CASE
3397 WHEN SUM(kpi.PC) > 0 THEN
3398 SUM(kpi.SKU) / SUM(kpi.PC)
3399 ELSE
3400 0
3401 END
3402 FROM
3403 (
3404 SELECT sf.CompanyID,
3405 sf.EmployeeID,
3406 ObjectAssignment = CASE
3407 WHEN sf.TerritoryType = 'D' THEN
3408 r.ROUTE
3409 ELSE
3410 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
3411 END,
3412 sf.SalesOrg_0_ValueID
3413 FROM BELDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
3414 JOIN #tmpSalesForce sf WITH (NOLOCK)
3415 ON sf.CompanyID = objectPeriod.CompanyID
3416 AND sf.SFHierachyID = objectPeriod.KPIObjectID
3417 LEFT JOIN #tmpRoute r
3418 ON r.CompanyID = sf.CompanyID
3419 AND r.SalesForceID = sf.EmployeeID
3420 WHERE objectPeriod.CompanyID = @_CompanyID
3421 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
3422 AND sf.TerritoryType != 'S'
3423 AND
3424 (
3425 @_SalesAreaID IS NULL
3426 OR @_Level = -1
3427 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
3428 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
3429 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
3430 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
3431 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
3432 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
3433 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
3434 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
3435 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
3436 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
3437 )
3438 GROUP BY sf.CompanyID,
3439 sf.EmployeeID,
3440 CASE
3441 WHEN sf.TerritoryType = 'D' THEN
3442 r.ROUTE
3443 ELSE
3444 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
3445 END,
3446 sf.SalesOrg_0_ValueID
3447 ) tmp
3448 LEFT JOIN
3449 (
3450 SELECT kpid.CompanyID,
3451 kpid.ObjectID,
3452 kpid.KPIPeriodNbr,
3453 kpid.FromDate,
3454 kpid.ToDate,
3455 kpid.TargetsSuggest,
3456 kpid.TargetsAssigned
3457 FROM #tmpKPIDetail kpid
3458 WHERE kpid.CodeListSalesID = @_KPIID
3459 AND
3460 (
3461 @_RefNbr LIKE 'Temp-%'
3462 OR kpid.RefNbr = @_RefNbr
3463 )
3464 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
3465 AND kpid.FromDate = @_FromDate
3466 AND kpid.ToDate = @_ToDate
3467 ) kpid
3468 ON kpid.CompanyID = tmp.CompanyID
3469 AND kpid.ObjectID = tmp.EmployeeID
3470 LEFT JOIN
3471 (
3472 SELECT DISTINCT
3473 kpipc.CompanyID,
3474 kpipc.ObjectID,
3475 kpipc.ObjectAssignment,
3476 SKU1 = kpisku.Actual1,
3477 SKU2 = kpisku.Actual2,
3478 SKU3 = kpisku.Actual3,
3479 SKU4 = kpisku.Actual4,
3480 SKU = kpisku.Actual,
3481 PC1 = kpipc.Actual1,
3482 PC2 = kpipc.Actual2,
3483 PC3 = kpipc.Actual3,
3484 PC4 = kpipc.Actual4,
3485 PC = kpipc.Actual,
3486 r.ROUTE,
3487 r.SalesAreaID
3488 FROM #tmpTotalPC kpipc
3489 JOIN #tmpTotalLine kpisku
3490 ON kpisku.CompanyID = kpipc.CompanyID
3491 AND kpisku.ObjectID = kpipc.ObjectID
3492 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
3493 JOIN #tmpSalesForce sf
3494 ON sf.CompanyID = kpipc.CompanyID
3495 AND sf.EmployeeID = kpipc.ObjectID
3496 AND sf.TerritoryType = 'S'
3497 JOIN #tmpRoute r
3498 ON r.CompanyID = sf.CompanyID
3499 AND r.SalespersonID = sf.EmployeeID
3500 ) kpi
3501 ON kpi.CompanyID = tmp.CompanyID
3502 AND
3503 (
3504 kpi.ROUTE = tmp.ObjectAssignment
3505 OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
3506 )
3507 WHERE kpid.ObjectID IS NOT NULL
3508 GROUP BY tmp.CompanyID,
3509 tmp.EmployeeID,
3510 tmp.ObjectAssignment;
3511 END;
3512 SET @_Min = @_Min + 1;
3513 END;
3514
3515 SELECT @_step = @_step + 1;
3516 END;
3517
3518
3519 ---- KPI BPPC
3520
3521 DELETE FROM #tmpKPIList;
3522
3523 INSERT INTO #tmpKPIList
3524 SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
3525 *
3526 FROM
3527 (
3528 SELECT CompanyID,
3529 CodeListSalesID,
3530 CodeListSalesCD,
3531 RefNbr,
3532 KPIPeriodNbr,
3533 Descr = MAX(Descr),
3534 SOURCE = MAX(SOURCE),
3535 TYPE = MAX(TYPE),
3536 GroupBy = MAX(GroupBy),
3537 GroupID = MAX(GroupID),
3538 Formula = MAX(Formula),
3539 PERIOD = MAX(PERIOD),
3540 SalesOrgID = MAX(SalesOrgID),
3541 SalesAreaID = MAX(SalesAreaID),
3542 LEVEL = MAX(LEVEL),
3543 Template = MAX(Template),
3544 FromDate,
3545 ToDate,
3546 ObjectType = MAX(ObjectType),
3547 CorrectRouteAndCorrectCoordinates,
3548 CorrectRouteAndIncorrectCoordinates,
3549 IncorrectRoute,
3550 DistributorsSell,
3551 MinSKUVolume,
3552 MinSKURevenue,
3553 ExcludeReturnOrder
3554 FROM #tmpKPIDetail
3555 WHERE Template = 'BPPC'
3556 AND ObjectType = 'S'
3557 GROUP BY CompanyID,
3558 CodeListSalesID,
3559 CodeListSalesCD,
3560 RefNbr,
3561 KPIPeriodNbr,
3562 FromDate,
3563 ToDate,
3564 CorrectRouteAndCorrectCoordinates,
3565 CorrectRouteAndIncorrectCoordinates,
3566 IncorrectRoute,
3567 DistributorsSell,
3568 MinSKUVolume,
3569 MinSKURevenue,
3570 ExcludeReturnOrder
3571 ) tmp;
3572
3573 SELECT @_Max = MAX(RowNumber)
3574 FROM #tmpKPIList;
3575
3576 SELECT @_step = 1;
3577 WHILE @_step <= 2
3578 BEGIN
3579 SELECT @_Min = 1;
3580 WHILE @_Min <= @_Max
3581 BEGIN
3582 ---- Xóa dữ liệu SM để tính lại đối với KPI mới
3583 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
3584 DELETE FROM dbo.DMSBLObjectKPITemp;
3585 DELETE FROM #tmpTotalPC;
3586 DELETE FROM #tmpTotalLine;
3587
3588 ---- Lấy thông tin công thức KPI
3589 SELECT @_KPIID = CodeListSalesID,
3590 @_KPICD = CodeListSalesCD,
3591 @_KPIDescr = Descr,
3592 @_RefNbr = RefNbr,
3593 @_KPIPeriodNbr = KPIPeriodNbr,
3594 @_Source = SOURCE,
3595 @_Type = TYPE,
3596 @_GroupBy = ISNULL(GroupBy, 'A'),
3597 @_GroupID = GroupID,
3598 @_Formula = Formula,
3599 @_Template = Template,
3600 @_Period = PERIOD,
3601 @_SalesAreaID = SalesAreaID,
3602 @_Level = LEVEL,
3603 @_FromDate = FromDate,
3604 @_ToDate = ToDate,
3605 @_ObjectType = ObjectType,
3606 @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
3607 @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
3608 @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
3609 @_IsDistributorSell = ISNULL(DistributorsSell, 0),
3610 @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
3611 @_MinSKURevenue = MinSKURevenue,
3612 @_MinSKUVolume = MinSKUVolume
3613 FROM #tmpKPIList
3614 WHERE RowNumber = @_Min;
3615
3616 IF @_TempFromDate IS NULL
3617 OR @_TempToDate IS NULL
3618 OR @_TempFromDate != @_FromDate
3619 OR @_TempToDate != @_ToDate
3620 BEGIN
3621 DELETE FROM #tmpSalesTerritory;
3622 DELETE FROM #tmpRoute;
3623 DELETE FROM #tmpSalesForce;
3624
3625 ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
3626 INSERT INTO #tmpSalesTerritory
3627 SELECT *
3628 FROM
3629 (
3630 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
3631 st.SalesOrg_0_ValueID
3632 ORDER BY st.EffectiveDate DESC
3633 ),
3634 st.CompanyID,
3635 st.SalesOrg_0_ValueID,
3636 st.SalesOrg_1_ValueID,
3637 st.SalesOrg_2_ValueID,
3638 st.SalesOrg_3_ValueID,
3639 st.SalesOrg_4_ValueID,
3640 st.SalesOrg_5_ValueID,
3641 st.SalesOrg_6_ValueID,
3642 st.SalesOrg_7_ValueID,
3643 st.SalesOrg_8_ValueID,
3644 st.SalesOrg_9_ValueID
3645 FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
3646 WHERE st.CompanyID = @_CompanyID
3647 AND
3648 (
3649 @_FromDate
3650 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
3651 OR @_ToDate
3652 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
3653 OR st.EffectiveDate
3654 BETWEEN @_FromDate AND @_ToDate
3655 )
3656 ) tmp
3657 WHERE tmp.RowNumber = 1;
3658
3659 ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
3660 INSERT INTO #tmpRoute
3661 SELECT tmp.CompanyID,
3662 tmp.SalespersonID,
3663 tmp.ROUTE,
3664 tmp.SalesForceID,
3665 tmp.SalesAreaID,
3666 tmp.EffectiveDate,
3667 tmp.ExpirationDate,
3668 st.SalesOrg_0_ValueID,
3669 st.SalesOrg_1_ValueID,
3670 st.SalesOrg_2_ValueID,
3671 st.SalesOrg_3_ValueID,
3672 st.SalesOrg_4_ValueID,
3673 st.SalesOrg_5_ValueID,
3674 st.SalesOrg_6_ValueID,
3675 st.SalesOrg_7_ValueID,
3676 st.SalesOrg_8_ValueID,
3677 st.SalesOrg_9_ValueID
3678 FROM
3679 (
3680 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
3681 rs.SalespersonID
3682 ORDER BY rs.EffectiveDate DESC
3683 ),
3684 rs.CompanyID,
3685 rs.SalespersonID,
3686 ROUTE = rs.RouteCD,
3687 rs.SalesForceID,
3688 rs.SalesAreaID,
3689 rs.EffectiveDate,
3690 ExpirationDate = ISNULL(rs.EndDate, GETDATE())
3691 FROM BELDMS..DMSRouteSetting rs WITH (NOLOCK)
3692 WHERE rs.CompanyID = @_CompanyID
3693 AND
3694 (
3695 @_FromDate
3696 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
3697 OR @_ToDate
3698 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
3699 OR rs.EffectiveDate
3700 BETWEEN @_FromDate AND @_ToDate
3701 )
3702 UNION ALL
3703 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
3704 rs.PreviousSalesMan
3705 ORDER BY rs.StartDatePre DESC
3706 ),
3707 rs.CompanyID,
3708 SalespersonID = rs.PreviousSalesMan,
3709 ROUTE = rs.RouteCD,
3710 rs.SalesForceID,
3711 rs.SalesAreaID,
3712 EffectiveDate = rs.StartDatePre,
3713 ExpirationDate = rs.EndDatePre
3714 FROM BELDMS..DMSRouteSetting rs WITH (NOLOCK)
3715 WHERE rs.CompanyID = @_CompanyID
3716 AND
3717 (
3718 @_FromDate
3719 BETWEEN rs.StartDatePre AND ISNULL(rs.EndDatePre, GETDATE())
3720 OR @_ToDate
3721 BETWEEN rs.StartDatePre AND ISNULL(rs.EndDatePre, GETDATE())
3722 OR rs.StartDatePre
3723 BETWEEN @_FromDate AND @_ToDate
3724 )
3725 AND rs.PreviousSalesMan IS NOT NULL
3726 ) tmp
3727 JOIN #tmpSalesTerritory st
3728 ON st.CompanyID = tmp.CompanyID
3729 AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
3730 WHERE tmp.RowNumber = 1;
3731
3732 ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
3733 INSERT INTO #tmpSalesForce
3734 SELECT tmp.CompanyID,
3735 tmp.EmployeeID,
3736 tmp.SFHierachyID,
3737 tmp.TerritoryType,
3738 tmp.SalesOrgValueID,
3739 st.SalesOrg_0_ValueID,
3740 st.SalesOrg_1_ValueID,
3741 st.SalesOrg_2_ValueID,
3742 st.SalesOrg_3_ValueID,
3743 st.SalesOrg_4_ValueID,
3744 st.SalesOrg_5_ValueID,
3745 st.SalesOrg_6_ValueID,
3746 st.SalesOrg_7_ValueID,
3747 st.SalesOrg_8_ValueID,
3748 st.SalesOrg_9_ValueID
3749 FROM
3750 (
3751 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
3752 sf.EmployeeID
3753 ORDER BY sf.EffectiveDate DESC
3754 ),
3755 sf.CompanyID,
3756 sf.EmployeeID,
3757 sf.SFHierachyID,
3758 sf.TerritoryType,
3759 sf.SalesOrgValueID
3760 FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
3761 WHERE sf.CompanyID = @_CompanyID
3762 AND
3763 (
3764 @_FromDate
3765 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
3766 OR @_ToDate
3767 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
3768 OR sf.EffectiveDate
3769 BETWEEN @_FromDate AND @_ToDate
3770 )
3771 ) tmp
3772 LEFT JOIN #tmpSalesTerritory st
3773 ON st.CompanyID = tmp.CompanyID
3774 AND
3775 (
3776 st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
3777 OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
3778 OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
3779 OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
3780 OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
3781 OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
3782 OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
3783 OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
3784 OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
3785 OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
3786 )
3787 WHERE tmp.RowNumber = 1;
3788
3789 SET @_TempFromDate = @_FromDate;
3790 SET @_TempToDate = @_ToDate;
3791 END;
3792
3793 ---- Tính KPI PC tạm
3794 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
3795 SELECT rs.CompanyID,
3796 rs.SalespersonID,
3797 r.SalesForceID,
3798 r.SalesAreaID,
3799 rs.ROUTE,
3800 rs.OrderDate,
3801 rs.SourceType,
3802 rs.CustomerID,
3803 rs.CustomerLocationID,
3804 rs.IsValidDate,
3805 rs.IsValidDistance,
3806 Multiply = CASE
3807 WHEN rs.IsRevenueDeduction = 1 THEN
3808 -1
3809 ELSE
3810 1
3811 END,
3812 PC = COUNT(DISTINCT rs.OrderNbr),
3813 SKU = COUNT(DISTINCT rs.InventoryID),
3814 TranAmt = SUM(rs.TranAmt),
3815 ShippedQty = SUM(rs.ShippedQty),
3816 rs.SalesOrg_0_ValueID,
3817 rs.SalesOrg_1_ValueID,
3818 rs.SalesOrg_2_ValueID,
3819 rs.SalesOrg_3_ValueID,
3820 rs.SalesOrg_4_ValueID,
3821 rs.SalesOrg_5_ValueID,
3822 rs.SalesOrg_6_ValueID,
3823 rs.SalesOrg_7_ValueID,
3824 rs.SalesOrg_8_ValueID,
3825 rs.SalesOrg_9_ValueID,
3826 rs.SalesForce_0_ID,
3827 rs.SalesForce_1_ID,
3828 rs.SalesForce_2_ID,
3829 rs.SalesForce_3_ID,
3830 rs.SalesForce_4_ID,
3831 rs.SalesForce_5_ID,
3832 rs.SalesForce_6_ID,
3833 rs.SalesForce_7_ID,
3834 rs.SalesForce_8_ID,
3835 rs.SalesForce_9_ID,
3836 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
3837 FROM #tmpSORawSales rs
3838 LEFT JOIN BELDMS..DMSViewInventoryItem inv
3839 ON rs.CompanyID = inv.CompanyID
3840 AND rs.InventoryID = inv.InventoryID
3841 LEFT JOIN #tmpRoute r
3842 ON r.CompanyID = rs.CompanyID
3843 AND r.SalespersonID = rs.SalespersonID
3844 AND r.ROUTE = rs.ROUTE
3845 AND rs.OrderDate
3846 BETWEEN r.EffectiveDate AND r.ExpirationDate
3847 WHERE rs.OrderDate
3848 BETWEEN @_TempFromDate AND @_TempToDate
3849 AND rs.OrderType IN ( (CASE
3850 WHEN @_ExcludeReturnOrder = 0 THEN
3851 'CM'
3852 ELSE
3853 ''
3854 END
3855 ), 'SO', 'IN'
3856 )
3857 AND
3858 (
3859 ISNULL(@_GroupID, '') = ''
3860 OR
3861 (
3862 EXISTS
3863 (
3864 SELECT TOP 1
3865 *
3866 FROM BELDMS..DMSKPIGroupProductSalesDetail igd
3867 WHERE igd.CompanyID = @_CompanyID
3868 AND igd.CodegGroupProd = @_GroupID
3869 AND igd.InventoryID = rs.InventoryID
3870 )
3871 OR EXISTS
3872 (
3873 SELECT *
3874 FROM BELDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
3875 WHERE gr.CompanyID = @_CompanyID
3876 AND gr.CodegGroupProd = @_GroupID
3877 AND inv.[HierarchyID] = gr.[HierarchyID]
3878 OR gr.Attribute0 = inv.Attribute0ID
3879 OR gr.Attribute1 = inv.Attribute1ID
3880 OR gr.Attribute2 = inv.Attribute2ID
3881 OR gr.Attribute3 = inv.Attribute3ID
3882 OR gr.Attribute4 = inv.Attribute4ID
3883 OR gr.Attribute5 = inv.Attribute5ID
3884 OR gr.Attribute6 = inv.Attribute6ID
3885 OR gr.Attribute7 = inv.Attribute7ID
3886 OR gr.Attribute8 = inv.Attribute8ID
3887 OR gr.Attribute9 = inv.Attribute9ID
3888 )
3889 )
3890 )
3891 AND rs.ShippedQty >= @_MinSKUVolume
3892 AND ABS(rs.TranAmt) >= @_MinSKURevenue
3893 --AND rs.ReasonCode = ( CASE
3894 -- WHEN @_IncludePromotionItem = 1
3895 -- THEN 'ISSKM'
3896 -- WHEN @_IncludeWarrantyItem = 1
3897 -- THEN 'ISSBH'
3898 -- ELSE 'ISS'
3899 -- END )
3900 GROUP BY rs.CompanyID,
3901 rs.SalespersonID,
3902 r.SalesForceID,
3903 r.SalesAreaID,
3904 rs.ROUTE,
3905 rs.OrderDate,
3906 rs.SourceType,
3907 rs.CustomerID,
3908 rs.CustomerLocationID,
3909 rs.IsValidDate,
3910 rs.IsValidDistance,
3911 rs.IsRevenueDeduction,
3912 rs.SalesOrg_0_ValueID,
3913 rs.SalesOrg_1_ValueID,
3914 rs.SalesOrg_2_ValueID,
3915 rs.SalesOrg_3_ValueID,
3916 rs.SalesOrg_4_ValueID,
3917 rs.SalesOrg_5_ValueID,
3918 rs.SalesOrg_6_ValueID,
3919 rs.SalesOrg_7_ValueID,
3920 rs.SalesOrg_8_ValueID,
3921 rs.SalesOrg_9_ValueID,
3922 rs.SalesForce_0_ID,
3923 rs.SalesForce_1_ID,
3924 rs.SalesForce_2_ID,
3925 rs.SalesForce_3_ID,
3926 rs.SalesForce_4_ID,
3927 rs.SalesForce_5_ID,
3928 rs.SalesForce_6_ID,
3929 rs.SalesForce_7_ID,
3930 rs.SalesForce_8_ID,
3931 rs.SalesForce_9_ID,
3932 rs.OrderNbr;
3933
3934 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
3935 'PC',
3936 NULL,
3937 NULL,
3938 NULL,
3939 @_IsValiDateValidDistance,
3940 @_IsValidDateInvalidDistance,
3941 @_IsInvalidDate,
3942 @_IsDistributorSell;
3943
3944 INSERT INTO #tmpTotalPC
3945 SELECT *
3946 FROM dbo.DMSBLObjectKPITemp;
3947
3948 DELETE FROM dbo.DMSBLObjectKPITemp;
3949
3950 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
3951 'Hie3',
3952 NULL,
3953 NULL,
3954 NULL,
3955 @_IsValiDateValidDistance,
3956 @_IsValidDateInvalidDistance,
3957 @_IsInvalidDate,
3958 @_IsDistributorSell;
3959
3960 INSERT INTO #tmpTotalLine
3961 SELECT *
3962 FROM dbo.DMSBLObjectKPITemp;
3963
3964 --Step 1 for Sales Rep, step 2 for sales Sup
3965 IF @_step = 1
3966 BEGIN
3967 INSERT INTO #tmpKPI
3968 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
3969 CompanyID = tmp.CompanyID,
3970 @_KPIID,
3971 @_KPICD,
3972 @_KPIDescr,
3973 @_Source,
3974 @_Type,
3975 @_GroupBy,
3976 @_GroupID,
3977 @_Formula,
3978 @_Template,
3979 @_KPIPeriodNbr,
3980 ISNULL(
3981 @_RefNbr,
3982 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
3983 + CONVERT(VARCHAR(20), @_ToDate, 111)
3984 ),
3985 @_Period,
3986 @_FromDate,
3987 @_ToDate,
3988 ObjectID = tmp.EmployeeID,
3989 TempObjectID = ISNULL(kpid.ObjectID, 0),
3990 'S',
3991 tmp.ObjectAssignment,
3992 kpid.TargetsSuggest, ---- temp
3993 kpid.TargetsAssigned, ---- temp
3994 kpid.TargetsSuggest,
3995 kpid.TargetsAssigned,
3996 kpia.Actual1,
3997 kpia.Actual2,
3998 kpia.Actual3,
3999 kpia.Actual4,
4000 kpia.Actual
4001 FROM
4002 (
4003 SELECT sf.CompanyID,
4004 sf.EmployeeID,
4005 ObjectAssignment = CASE
4006 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
4007 r.ROUTE
4008 ELSE
4009 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
4010 END
4011 FROM BELDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
4012 JOIN #tmpSalesForce sf WITH (NOLOCK)
4013 ON sf.CompanyID = objectPeriod.CompanyID
4014 AND sf.SFHierachyID = objectPeriod.KPIObjectID
4015 LEFT JOIN #tmpRoute r
4016 ON r.CompanyID = sf.CompanyID
4017 AND
4018 (
4019 r.SalespersonID = sf.EmployeeID
4020 OR r.SalesForceID = sf.EmployeeID
4021 )
4022 WHERE objectPeriod.CompanyID = @_CompanyID
4023 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
4024 AND
4025 (
4026 @_SalesAreaID IS NULL
4027 OR @_Level = -1
4028 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
4029 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
4030 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
4031 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
4032 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
4033 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
4034 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
4035 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
4036 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
4037 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
4038 )
4039 GROUP BY sf.CompanyID,
4040 sf.EmployeeID,
4041 CASE
4042 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
4043 r.ROUTE
4044 ELSE
4045 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
4046 END
4047 ) tmp
4048 LEFT JOIN
4049 (
4050 SELECT kpid.CompanyID,
4051 kpid.ObjectID,
4052 kpid.KPIPeriodNbr,
4053 kpid.FromDate,
4054 kpid.ToDate,
4055 kpid.TargetsSuggest,
4056 kpid.TargetsAssigned
4057 FROM #tmpKPIDetail kpid
4058 WHERE kpid.CodeListSalesID = @_KPIID
4059 AND
4060 (
4061 @_RefNbr LIKE 'Temp-%'
4062 OR kpid.RefNbr = @_RefNbr
4063 )
4064 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
4065 AND kpid.FromDate = @_FromDate
4066 AND kpid.ToDate = @_ToDate
4067 ) kpid
4068 ON kpid.CompanyID = tmp.CompanyID
4069 AND kpid.ObjectID = tmp.EmployeeID
4070 LEFT JOIN
4071 (
4072 SELECT kpipc.CompanyID,
4073 kpipc.ObjectID,
4074 kpipc.ObjectAssignment,
4075 Actual1 = CASE
4076 WHEN @_IsValiDateValidDistance = 1
4077 AND ISNULL(kpipc.Actual1, 0) > 0 THEN
4078 kpisku.Actual1 / kpipc.Actual1
4079 ELSE
4080 0
4081 END,
4082 Actual2 = CASE
4083 WHEN @_IsValidDateInvalidDistance = 1
4084 AND ISNULL(kpipc.Actual2, 0) > 0 THEN
4085 kpisku.Actual2 / kpipc.Actual2
4086 ELSE
4087 0
4088 END,
4089 Actual3 = CASE
4090 WHEN @_IsInvalidDate = 1
4091 AND ISNULL(kpipc.Actual3, 0) > 0 THEN
4092 kpisku.Actual3 / kpipc.Actual3
4093 ELSE
4094 0
4095 END,
4096 Actual4 = CASE
4097 WHEN @_IsDistributorSell = 1
4098 AND ISNULL(kpipc.Actual4, 0) > 0 THEN
4099 kpisku.Actual4 / kpipc.Actual4
4100 ELSE
4101 0
4102 END,
4103 Actual = CASE
4104 WHEN ISNULL(kpipc.Actual, 0) > 0 THEN
4105 kpisku.Actual / kpipc.Actual
4106 ELSE
4107 0
4108 END
4109 FROM #tmpTotalPC kpipc
4110 JOIN #tmpTotalLine kpisku
4111 ON kpisku.CompanyID = kpipc.CompanyID
4112 AND kpisku.ObjectID = kpipc.ObjectID
4113 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
4114 JOIN #tmpSalesForce sf
4115 ON sf.CompanyID = kpipc.CompanyID
4116 AND sf.EmployeeID = kpipc.ObjectID
4117 AND sf.TerritoryType = 'S'
4118 JOIN #tmpRoute r
4119 ON r.CompanyID = sf.CompanyID
4120 AND r.SalespersonID = sf.EmployeeID
4121 ) kpia
4122 ON kpia.CompanyID = tmp.CompanyID
4123 AND kpia.ObjectID = tmp.EmployeeID
4124 AND kpia.ObjectAssignment = tmp.ObjectAssignment
4125 WHERE kpid.ObjectID IS NOT NULL;
4126 END;
4127 ELSE IF @_step = 2
4128 BEGIN
4129 INSERT INTO #tmpKPI
4130 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
4131 tmp.CompanyID,
4132 @_KPIID,
4133 @_KPICD,
4134 @_KPIDescr,
4135 @_Source,
4136 @_Type,
4137 @_GroupBy,
4138 @_GroupID,
4139 @_Formula,
4140 @_Template,
4141 @_KPIPeriodNbr,
4142 ISNULL(
4143 @_RefNbr,
4144 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
4145 + CONVERT(VARCHAR(20), @_ToDate, 111)
4146 ),
4147 @_Period,
4148 @_FromDate,
4149 @_ToDate,
4150 ObjectID = tmp.EmployeeID,
4151 TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
4152 'S',
4153 tmp.ObjectAssignment,
4154 TargetsSuggest = MAX(kpid.TargetsSuggest), ---- temp
4155 TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
4156 TargetsSuggest = MAX(kpid.TargetsSuggest),
4157 TargetsAssigned = MAX(kpid.TargetsAssigned),
4158 Actual1 = CASE
4159 WHEN @_IsValiDateValidDistance = 1
4160 AND SUM(kpi.PC1) > 0 THEN
4161 SUM(kpi.SKU1) / SUM(kpi.PC1)
4162 ELSE
4163 0
4164 END,
4165 Actual2 = CASE
4166 WHEN @_IsValidDateInvalidDistance = 1
4167 AND SUM(kpi.PC2) > 0 THEN
4168 SUM(kpi.SKU2) / SUM(kpi.PC2)
4169 ELSE
4170 0
4171 END,
4172 Actual3 = CASE
4173 WHEN @_IsInvalidDate = 1
4174 AND SUM(kpi.PC3) > 0 THEN
4175 SUM(kpi.SKU3) / SUM(kpi.PC3)
4176 ELSE
4177 0
4178 END,
4179 Actual4 = CASE
4180 WHEN @_IsDistributorSell = 1
4181 AND SUM(kpi.PC4) > 0 THEN
4182 SUM(kpi.SKU4) / SUM(kpi.PC4)
4183 ELSE
4184 0
4185 END,
4186 Actual = CASE
4187 WHEN SUM(kpi.PC) > 0 THEN
4188 SUM(kpi.SKU) / SUM(kpi.PC)
4189 ELSE
4190 0
4191 END
4192 FROM
4193 (
4194 SELECT sf.CompanyID,
4195 sf.EmployeeID,
4196 ObjectAssignment = CASE
4197 WHEN sf.TerritoryType = 'D' THEN
4198 r.ROUTE
4199 ELSE
4200 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
4201 END,
4202 sf.SalesOrg_0_ValueID
4203 FROM BELDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
4204 JOIN #tmpSalesForce sf WITH (NOLOCK)
4205 ON sf.CompanyID = objectPeriod.CompanyID
4206 AND sf.SFHierachyID = objectPeriod.KPIObjectID
4207 LEFT JOIN #tmpRoute r
4208 ON r.CompanyID = sf.CompanyID
4209 AND r.SalesForceID = sf.EmployeeID
4210 WHERE objectPeriod.CompanyID = @_CompanyID
4211 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
4212 AND sf.TerritoryType != 'S'
4213 AND
4214 (
4215 @_SalesAreaID IS NULL
4216 OR @_Level = -1
4217 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
4218 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
4219 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
4220 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
4221 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
4222 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
4223 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
4224 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
4225 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
4226 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
4227 )
4228 GROUP BY sf.CompanyID,
4229 sf.EmployeeID,
4230 CASE
4231 WHEN sf.TerritoryType = 'D' THEN
4232 r.ROUTE
4233 ELSE
4234 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
4235 END,
4236 sf.SalesOrg_0_ValueID
4237 ) tmp
4238 LEFT JOIN
4239 (
4240 SELECT kpid.CompanyID,
4241 kpid.ObjectID,
4242 kpid.KPIPeriodNbr,
4243 kpid.FromDate,
4244 kpid.ToDate,
4245 kpid.TargetsSuggest,
4246 kpid.TargetsAssigned
4247 FROM #tmpKPIDetail kpid
4248 WHERE kpid.CodeListSalesID = @_KPIID
4249 AND
4250 (
4251 @_RefNbr LIKE 'Temp-%'
4252 OR kpid.RefNbr = @_RefNbr
4253 )
4254 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
4255 AND kpid.FromDate = @_FromDate
4256 AND kpid.ToDate = @_ToDate
4257 ) kpid
4258 ON kpid.CompanyID = tmp.CompanyID
4259 AND kpid.ObjectID = tmp.EmployeeID
4260 LEFT JOIN
4261 (
4262 SELECT DISTINCT
4263 kpipc.CompanyID,
4264 kpipc.ObjectID,
4265 kpipc.ObjectAssignment,
4266 SKU1 = kpisku.Actual1,
4267 SKU2 = kpisku.Actual2,
4268 SKU3 = kpisku.Actual3,
4269 SKU4 = kpisku.Actual4,
4270 SKU = kpisku.Actual,
4271 PC1 = kpipc.Actual1,
4272 PC2 = kpipc.Actual2,
4273 PC3 = kpipc.Actual3,
4274 PC4 = kpipc.Actual4,
4275 PC = kpipc.Actual,
4276 r.ROUTE,
4277 r.SalesAreaID
4278 FROM #tmpTotalPC kpipc
4279 JOIN #tmpTotalLine kpisku
4280 ON kpisku.CompanyID = kpipc.CompanyID
4281 AND kpisku.ObjectID = kpipc.ObjectID
4282 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
4283 JOIN #tmpSalesForce sf
4284 ON sf.CompanyID = kpipc.CompanyID
4285 AND sf.EmployeeID = kpipc.ObjectID
4286 AND sf.TerritoryType = 'S'
4287 JOIN #tmpRoute r
4288 ON r.CompanyID = sf.CompanyID
4289 AND r.SalespersonID = sf.EmployeeID
4290 ) kpi
4291 ON kpi.CompanyID = tmp.CompanyID
4292 AND
4293 (
4294 kpi.ROUTE = tmp.ObjectAssignment
4295 OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
4296 )
4297 WHERE kpid.ObjectID IS NOT NULL
4298 GROUP BY tmp.CompanyID,
4299 tmp.EmployeeID,
4300 tmp.ObjectAssignment;
4301 END;
4302 SET @_Min = @_Min + 1;
4303 END;
4304
4305 SELECT @_step = @_step + 1;
4306 END;
4307
4308
4309 ---- Tính KPI DropSize by revnue or quantity
4310 ---- Formula: Total (Revenue or Volumn) / Quantity of PC
4311 ---- DROPSIZEV: drop size by qty
4312 ---- DROPSIZER: drop size by revenue
4313 DELETE FROM #tmpKPIList;
4314
4315 INSERT INTO #tmpKPIList
4316 SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
4317 *
4318 FROM
4319 (
4320 SELECT CompanyID,
4321 CodeListSalesID,
4322 CodeListSalesCD,
4323 RefNbr,
4324 KPIPeriodNbr,
4325 Descr = MAX(Descr),
4326 SOURCE = MAX(SOURCE),
4327 TYPE = MAX(TYPE),
4328 GroupBy = MAX(GroupBy),
4329 GroupID = MAX(GroupID),
4330 Formula = MAX(Formula),
4331 PERIOD = MAX(PERIOD),
4332 SalesOrgID = MAX(SalesOrgID),
4333 SalesAreaID = MAX(SalesAreaID),
4334 LEVEL = MAX(LEVEL),
4335 Template = MAX(Template),
4336 FromDate,
4337 ToDate,
4338 ObjectType = MAX(ObjectType),
4339 CorrectRouteAndCorrectCoordinates,
4340 CorrectRouteAndIncorrectCoordinates,
4341 IncorrectRoute,
4342 DistributorsSell,
4343 MinSKUVolume,
4344 MinSKURevenue,
4345 ExcludeReturnOrder
4346 FROM #tmpKPIDetail
4347 WHERE Template IN ( 'DROPSIZER', 'DROPSIZEV' )
4348 AND ObjectType = 'S'
4349 GROUP BY CompanyID,
4350 CodeListSalesID,
4351 CodeListSalesCD,
4352 RefNbr,
4353 KPIPeriodNbr,
4354 FromDate,
4355 ToDate,
4356 CorrectRouteAndCorrectCoordinates,
4357 CorrectRouteAndIncorrectCoordinates,
4358 IncorrectRoute,
4359 DistributorsSell,
4360 MinSKUVolume,
4361 MinSKURevenue,
4362 ExcludeReturnOrder
4363 ) tmp;
4364
4365 SELECT @_Max = MAX(RowNumber)
4366 FROM #tmpKPIList;
4367
4368 SELECT @_step = 1;
4369 WHILE @_step <= 2
4370 BEGIN
4371 SELECT @_Min = 1;
4372 WHILE @_Min <= @_Max
4373 BEGIN
4374 ---- Xóa dữ liệu SM để tính lại đối với KPI mới
4375 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
4376 DELETE FROM dbo.DMSBLObjectKPITemp;
4377 DELETE FROM #tmpTotalPC;
4378 DELETE FROM #tmpTotalLine;
4379
4380 ---- Lấy thông tin công thức KPI
4381 SELECT @_KPIID = CodeListSalesID,
4382 @_KPICD = CodeListSalesCD,
4383 @_KPIDescr = Descr,
4384 @_RefNbr = RefNbr,
4385 @_KPIPeriodNbr = KPIPeriodNbr,
4386 @_Source = SOURCE,
4387 @_Type = TYPE,
4388 @_GroupBy = ISNULL(GroupBy, 'A'),
4389 @_GroupID = GroupID,
4390 @_Formula = Formula,
4391 @_Template = Template,
4392 @_Period = PERIOD,
4393 @_SalesAreaID = SalesAreaID,
4394 @_Level = LEVEL,
4395 @_FromDate = FromDate,
4396 @_ToDate = ToDate,
4397 @_ObjectType = ObjectType,
4398 @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
4399 @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
4400 @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
4401 @_IsDistributorSell = ISNULL(DistributorsSell, 0),
4402 @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
4403 @_MinSKURevenue = MinSKURevenue,
4404 @_MinSKUVolume = MinSKUVolume
4405 FROM #tmpKPIList
4406 WHERE RowNumber = @_Min;
4407
4408 IF @_TempFromDate IS NULL
4409 OR @_TempToDate IS NULL
4410 OR @_TempFromDate != @_FromDate
4411 OR @_TempToDate != @_ToDate
4412 BEGIN
4413 DELETE FROM #tmpSalesTerritory;
4414 DELETE FROM #tmpRoute;
4415 DELETE FROM #tmpSalesForce;
4416
4417 ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
4418 INSERT INTO #tmpSalesTerritory
4419 SELECT *
4420 FROM
4421 (
4422 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
4423 st.SalesOrg_0_ValueID
4424 ORDER BY st.EffectiveDate DESC
4425 ),
4426 st.CompanyID,
4427 st.SalesOrg_0_ValueID,
4428 st.SalesOrg_1_ValueID,
4429 st.SalesOrg_2_ValueID,
4430 st.SalesOrg_3_ValueID,
4431 st.SalesOrg_4_ValueID,
4432 st.SalesOrg_5_ValueID,
4433 st.SalesOrg_6_ValueID,
4434 st.SalesOrg_7_ValueID,
4435 st.SalesOrg_8_ValueID,
4436 st.SalesOrg_9_ValueID
4437 FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
4438 WHERE st.CompanyID = @_CompanyID
4439 AND
4440 (
4441 @_FromDate
4442 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
4443 OR @_ToDate
4444 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
4445 OR st.EffectiveDate
4446 BETWEEN @_FromDate AND @_ToDate
4447 )
4448 ) tmp
4449 WHERE tmp.RowNumber = 1;
4450
4451 ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
4452 INSERT INTO #tmpRoute
4453 SELECT tmp.CompanyID,
4454 tmp.SalespersonID,
4455 tmp.ROUTE,
4456 tmp.SalesForceID,
4457 tmp.SalesAreaID,
4458 tmp.EffectiveDate,
4459 tmp.ExpirationDate,
4460 st.SalesOrg_0_ValueID,
4461 st.SalesOrg_1_ValueID,
4462 st.SalesOrg_2_ValueID,
4463 st.SalesOrg_3_ValueID,
4464 st.SalesOrg_4_ValueID,
4465 st.SalesOrg_5_ValueID,
4466 st.SalesOrg_6_ValueID,
4467 st.SalesOrg_7_ValueID,
4468 st.SalesOrg_8_ValueID,
4469 st.SalesOrg_9_ValueID
4470 FROM
4471 (
4472 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
4473 rs.SalespersonID
4474 ORDER BY rs.EffectiveDate DESC
4475 ),
4476 rs.CompanyID,
4477 rs.SalespersonID,
4478 ROUTE = rs.RouteCD,
4479 rs.SalesForceID,
4480 rs.SalesAreaID,
4481 rs.EffectiveDate,
4482 ExpirationDate = ISNULL(rs.EndDate, GETDATE())
4483 FROM BELDMS..DMSRouteSetting rs WITH (NOLOCK)
4484 WHERE rs.CompanyID = @_CompanyID
4485 AND
4486 (
4487 @_FromDate
4488 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
4489 OR @_ToDate
4490 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
4491 OR rs.EffectiveDate
4492 BETWEEN @_FromDate AND @_ToDate
4493 )
4494 UNION ALL
4495 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
4496 rs.PreviousSalesMan
4497 ORDER BY rs.StartDatePre DESC
4498 ),
4499 rs.CompanyID,
4500 SalespersonID = rs.PreviousSalesMan,
4501 ROUTE = rs.RouteCD,
4502 rs.SalesForceID,
4503 rs.SalesAreaID,
4504 EffectiveDate = rs.StartDatePre,
4505 ExpirationDate = rs.EndDatePre
4506 FROM BELDMS..DMSRouteSetting rs WITH (NOLOCK)
4507 WHERE rs.CompanyID = @_CompanyID
4508 AND
4509 (
4510 @_FromDate
4511 BETWEEN rs.StartDatePre AND ISNULL(rs.EndDatePre, GETDATE())
4512 OR @_ToDate
4513 BETWEEN rs.StartDatePre AND ISNULL(rs.EndDatePre, GETDATE())
4514 OR rs.StartDatePre
4515 BETWEEN @_FromDate AND @_ToDate
4516 )
4517 AND rs.PreviousSalesMan IS NOT NULL
4518 ) tmp
4519 JOIN #tmpSalesTerritory st
4520 ON st.CompanyID = tmp.CompanyID
4521 AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
4522 WHERE tmp.RowNumber = 1;
4523
4524 ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
4525 INSERT INTO #tmpSalesForce
4526 SELECT tmp.CompanyID,
4527 tmp.EmployeeID,
4528 tmp.SFHierachyID,
4529 tmp.TerritoryType,
4530 tmp.SalesOrgValueID,
4531 st.SalesOrg_0_ValueID,
4532 st.SalesOrg_1_ValueID,
4533 st.SalesOrg_2_ValueID,
4534 st.SalesOrg_3_ValueID,
4535 st.SalesOrg_4_ValueID,
4536 st.SalesOrg_5_ValueID,
4537 st.SalesOrg_6_ValueID,
4538 st.SalesOrg_7_ValueID,
4539 st.SalesOrg_8_ValueID,
4540 st.SalesOrg_9_ValueID
4541 FROM
4542 (
4543 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
4544 sf.EmployeeID
4545 ORDER BY sf.EffectiveDate DESC
4546 ),
4547 sf.CompanyID,
4548 sf.EmployeeID,
4549 sf.SFHierachyID,
4550 sf.TerritoryType,
4551 sf.SalesOrgValueID
4552 FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
4553 WHERE sf.CompanyID = @_CompanyID
4554 AND
4555 (
4556 @_FromDate
4557 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
4558 OR @_ToDate
4559 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
4560 OR sf.EffectiveDate
4561 BETWEEN @_FromDate AND @_ToDate
4562 )
4563 ) tmp
4564 LEFT JOIN #tmpSalesTerritory st
4565 ON st.CompanyID = tmp.CompanyID
4566 AND
4567 (
4568 st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
4569 OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
4570 OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
4571 OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
4572 OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
4573 OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
4574 OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
4575 OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
4576 OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
4577 OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
4578 )
4579 WHERE tmp.RowNumber = 1;
4580
4581 SET @_TempFromDate = @_FromDate;
4582 SET @_TempToDate = @_ToDate;
4583 END;
4584
4585 ---- Tính KPI PC tạm để tính
4586 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
4587 SELECT rs.CompanyID,
4588 rs.SalespersonID,
4589 r.SalesForceID,
4590 r.SalesAreaID,
4591 rs.ROUTE,
4592 rs.OrderDate,
4593 rs.SourceType,
4594 rs.CustomerID,
4595 rs.CustomerLocationID,
4596 rs.IsValidDate,
4597 rs.IsValidDistance,
4598 Multiply = CASE
4599 WHEN rs.IsRevenueDeduction = 1 THEN
4600 -1
4601 ELSE
4602 1
4603 END,
4604 PC = COUNT(DISTINCT rs.OrderNbr),
4605 SKU = COUNT(DISTINCT rs.InventoryID),
4606 TranAmt = SUM(rs.TranAmt),
4607 ShippedQty = SUM(rs.ShippedQty),
4608 rs.SalesOrg_0_ValueID,
4609 rs.SalesOrg_1_ValueID,
4610 rs.SalesOrg_2_ValueID,
4611 rs.SalesOrg_3_ValueID,
4612 rs.SalesOrg_4_ValueID,
4613 rs.SalesOrg_5_ValueID,
4614 rs.SalesOrg_6_ValueID,
4615 rs.SalesOrg_7_ValueID,
4616 rs.SalesOrg_8_ValueID,
4617 rs.SalesOrg_9_ValueID,
4618 rs.SalesForce_0_ID,
4619 rs.SalesForce_1_ID,
4620 rs.SalesForce_2_ID,
4621 rs.SalesForce_3_ID,
4622 rs.SalesForce_4_ID,
4623 rs.SalesForce_5_ID,
4624 rs.SalesForce_6_ID,
4625 rs.SalesForce_7_ID,
4626 rs.SalesForce_8_ID,
4627 rs.SalesForce_9_ID,
4628 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
4629 FROM #tmpSORawSales rs
4630 LEFT JOIN BELDMS..DMSViewInventoryItem inv
4631 ON rs.CompanyID = inv.CompanyID
4632 AND rs.InventoryID = inv.InventoryID
4633 LEFT JOIN #tmpRoute r
4634 ON r.CompanyID = rs.CompanyID
4635 AND r.SalespersonID = rs.SalespersonID
4636 AND r.ROUTE = rs.ROUTE
4637 AND rs.OrderDate
4638 BETWEEN r.EffectiveDate AND r.ExpirationDate
4639 WHERE rs.OrderDate
4640 BETWEEN @_TempFromDate AND @_TempToDate
4641 AND rs.OrderType IN ( (CASE
4642 WHEN @_ExcludeReturnOrder = 0 THEN
4643 'CM'
4644 ELSE
4645 ''
4646 END
4647 ), 'SO'
4648 )
4649 AND
4650 (
4651 ISNULL(@_GroupID, '') = ''
4652 OR
4653 (
4654 EXISTS
4655 (
4656 SELECT TOP 1
4657 *
4658 FROM BELDMS..DMSKPIGroupProductSalesDetail igd
4659 WHERE igd.CompanyID = @_CompanyID
4660 AND igd.CodegGroupProd = @_GroupID
4661 AND igd.InventoryID = rs.InventoryID
4662 )
4663 OR EXISTS
4664 (
4665 SELECT *
4666 FROM BELDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
4667 WHERE gr.CompanyID = @_CompanyID
4668 AND gr.CodegGroupProd = @_GroupID
4669 AND inv.[HierarchyID] = gr.[HierarchyID]
4670 OR gr.Attribute0 = inv.Attribute0ID
4671 OR gr.Attribute1 = inv.Attribute1ID
4672 OR gr.Attribute2 = inv.Attribute2ID
4673 OR gr.Attribute3 = inv.Attribute3ID
4674 OR gr.Attribute4 = inv.Attribute4ID
4675 OR gr.Attribute5 = inv.Attribute5ID
4676 OR gr.Attribute6 = inv.Attribute6ID
4677 OR gr.Attribute7 = inv.Attribute7ID
4678 OR gr.Attribute8 = inv.Attribute8ID
4679 OR gr.Attribute9 = inv.Attribute9ID
4680 )
4681 )
4682 )
4683 AND rs.ShippedQty >= @_MinSKUVolume
4684 AND ABS(rs.TranAmt) >= @_MinSKURevenue
4685 --AND rs.ReasonCode = ( CASE
4686 -- WHEN @_IncludePromotionItem = 1
4687 -- THEN 'ISSKM'
4688 -- WHEN @_IncludeWarrantyItem = 1
4689 -- THEN 'ISSBH'
4690 -- ELSE 'ISS'
4691 -- END )
4692 GROUP BY rs.CompanyID,
4693 rs.SalespersonID,
4694 r.SalesForceID,
4695 r.SalesAreaID,
4696 rs.ROUTE,
4697 rs.OrderDate,
4698 rs.SourceType,
4699 rs.CustomerID,
4700 rs.CustomerLocationID,
4701 rs.IsValidDate,
4702 rs.IsValidDistance,
4703 rs.IsRevenueDeduction,
4704 rs.SalesOrg_0_ValueID,
4705 rs.SalesOrg_1_ValueID,
4706 rs.SalesOrg_2_ValueID,
4707 rs.SalesOrg_3_ValueID,
4708 rs.SalesOrg_4_ValueID,
4709 rs.SalesOrg_5_ValueID,
4710 rs.SalesOrg_6_ValueID,
4711 rs.SalesOrg_7_ValueID,
4712 rs.SalesOrg_8_ValueID,
4713 rs.SalesOrg_9_ValueID,
4714 rs.SalesForce_0_ID,
4715 rs.SalesForce_1_ID,
4716 rs.SalesForce_2_ID,
4717 rs.SalesForce_3_ID,
4718 rs.SalesForce_4_ID,
4719 rs.SalesForce_5_ID,
4720 rs.SalesForce_6_ID,
4721 rs.SalesForce_7_ID,
4722 rs.SalesForce_8_ID,
4723 rs.SalesForce_9_ID;
4724
4725
4726
4727 DECLARE @tempTemplate nvarchar(50) = (
4728 SELECT CASE
4729 WHEN @_Template = 'DROPSIZER' THEN
4730 'REVENUE'
4731 WHEN @_Template = 'DROPSIZEV' THEN
4732 'QUANTITY'
4733 END
4734 );
4735 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
4736 @tempTemplate,
4737 NULL,
4738 NULL,
4739 NULL,
4740 @_IsValiDateValidDistance,
4741 @_IsValidDateInvalidDistance,
4742 @_IsInvalidDate,
4743 @_IsDistributorSell;
4744
4745 DELETE FROM dbo.DMSBLObjectKPITemp;
4746
4747 INSERT INTO #tmpTotalLine
4748 SELECT *
4749 FROM dbo.DMSBLObjectKPITemp;
4750 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
4751 'PC',
4752 NULL,
4753 NULL,
4754 NULL,
4755 @_IsValiDateValidDistance,
4756 @_IsValidDateInvalidDistance,
4757 @_IsInvalidDate,
4758 @_IsDistributorSell;
4759
4760 INSERT INTO #tmpTotalPC
4761 SELECT *
4762 FROM dbo.DMSBLObjectKPITemp;
4763 ---- Kết thúc tính KPI ra các tham số để tính KPI
4764 ---- Bắt đầu tính data từ các cấp dự trên số liệu param đã execute ở 2 bảng dữ liệu tạm
4765
4766 IF @_step = 1
4767 BEGIN
4768 INSERT INTO #tmpKPI
4769 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
4770 CompanyID = tmp.CompanyID,
4771 @_KPIID,
4772 @_KPICD,
4773 @_KPIDescr,
4774 @_Source,
4775 @_Type,
4776 @_GroupBy,
4777 @_GroupID,
4778 @_Formula,
4779 @_Template,
4780 @_KPIPeriodNbr,
4781 ISNULL(
4782 @_RefNbr,
4783 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
4784 + CONVERT(VARCHAR(20), @_ToDate, 111)
4785 ),
4786 @_Period,
4787 @_FromDate,
4788 @_ToDate,
4789 ObjectID = tmp.EmployeeID,
4790 TempObjectID = ISNULL(kpid.ObjectID, 0),
4791 'S',
4792 tmp.ObjectAssignment,
4793 kpid.TargetsSuggest, ---- temp
4794 kpid.TargetsAssigned, ---- temp
4795 kpid.TargetsSuggest,
4796 kpid.TargetsAssigned,
4797 kpia.Actual1,
4798 kpia.Actual2,
4799 kpia.Actual3,
4800 kpia.Actual4,
4801 kpia.Actual
4802 FROM
4803 (
4804 SELECT sf.CompanyID,
4805 sf.EmployeeID,
4806 ObjectAssignment = CASE
4807 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
4808 r.ROUTE
4809 ELSE
4810 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
4811 END
4812 FROM BELDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
4813 JOIN #tmpSalesForce sf WITH (NOLOCK)
4814 ON sf.CompanyID = objectPeriod.CompanyID
4815 AND sf.SFHierachyID = objectPeriod.KPIObjectID
4816 LEFT JOIN #tmpRoute r
4817 ON r.CompanyID = sf.CompanyID
4818 AND
4819 (
4820 r.SalespersonID = sf.EmployeeID
4821 OR r.SalesForceID = sf.EmployeeID
4822 )
4823 WHERE objectPeriod.CompanyID = @_CompanyID
4824 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
4825 AND
4826 (
4827 @_SalesAreaID IS NULL
4828 OR @_Level = -1
4829 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
4830 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
4831 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
4832 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
4833 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
4834 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
4835 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
4836 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
4837 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
4838 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
4839 )
4840 GROUP BY sf.CompanyID,
4841 sf.EmployeeID,
4842 CASE
4843 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
4844 r.ROUTE
4845 ELSE
4846 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
4847 END
4848 ) tmp
4849 LEFT JOIN
4850 (
4851 SELECT kpid.CompanyID,
4852 kpid.ObjectID,
4853 kpid.KPIPeriodNbr,
4854 kpid.FromDate,
4855 kpid.ToDate,
4856 kpid.TargetsSuggest,
4857 kpid.TargetsAssigned
4858 FROM #tmpKPIDetail kpid
4859 WHERE kpid.CodeListSalesID = @_KPIID
4860 AND
4861 (
4862 @_RefNbr LIKE 'Temp-%'
4863 OR kpid.RefNbr = @_RefNbr
4864 )
4865 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
4866 AND kpid.FromDate = @_FromDate
4867 AND kpid.ToDate = @_ToDate
4868 ) kpid
4869 ON kpid.CompanyID = tmp.CompanyID
4870 AND kpid.ObjectID = tmp.EmployeeID
4871 LEFT JOIN
4872 (
4873 SELECT kpipc.CompanyID,
4874 kpipc.ObjectID,
4875 kpipc.ObjectAssignment,
4876 Actual1 = CASE
4877 WHEN @_IsValiDateValidDistance = 1
4878 AND ISNULL(kpipc.Actual1, 0) > 0 THEN
4879 kpisku.Actual1 / kpipc.Actual1
4880 ELSE
4881 0
4882 END,
4883 Actual2 = CASE
4884 WHEN @_IsValidDateInvalidDistance = 1
4885 AND ISNULL(kpipc.Actual2, 0) > 0 THEN
4886 kpisku.Actual2 / kpipc.Actual2
4887 ELSE
4888 0
4889 END,
4890 Actual3 = CASE
4891 WHEN @_IsInvalidDate = 1
4892 AND ISNULL(kpipc.Actual3, 0) > 0 THEN
4893 kpisku.Actual3 / kpipc.Actual3
4894 ELSE
4895 0
4896 END,
4897 Actual4 = CASE
4898 WHEN @_IsDistributorSell = 1
4899 AND ISNULL(kpipc.Actual4, 0) > 0 THEN
4900 kpisku.Actual4 / kpipc.Actual4
4901 ELSE
4902 0
4903 END,
4904 Actual = CASE
4905 WHEN ISNULL(kpipc.Actual, 0) > 0 THEN
4906 kpisku.Actual / kpipc.Actual
4907 ELSE
4908 0
4909 END
4910 FROM #tmpTotalPC kpipc
4911 JOIN #tmpTotalLine kpisku
4912 ON kpisku.CompanyID = kpipc.CompanyID
4913 AND kpisku.ObjectID = kpipc.ObjectID
4914 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
4915 JOIN #tmpSalesForce sf
4916 ON sf.CompanyID = kpipc.CompanyID
4917 AND sf.EmployeeID = kpipc.ObjectID
4918 AND sf.TerritoryType = 'S'
4919 JOIN #tmpRoute r
4920 ON r.CompanyID = sf.CompanyID
4921 AND r.SalespersonID = sf.EmployeeID
4922 ) kpia
4923 ON kpia.CompanyID = tmp.CompanyID
4924 AND kpia.ObjectID = tmp.EmployeeID
4925 AND kpia.ObjectAssignment = tmp.ObjectAssignment
4926 WHERE kpid.ObjectID IS NOT NULL;
4927 END;
4928 ELSE IF @_step = 2
4929 BEGIN
4930 INSERT INTO #tmpKPI
4931 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
4932 tmp.CompanyID,
4933 @_KPIID,
4934 @_KPICD,
4935 @_KPIDescr,
4936 @_Source,
4937 @_Type,
4938 @_GroupBy,
4939 @_GroupID,
4940 @_Formula,
4941 @_Template,
4942 @_KPIPeriodNbr,
4943 ISNULL(
4944 @_RefNbr,
4945 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
4946 + CONVERT(VARCHAR(20), @_ToDate, 111)
4947 ),
4948 @_Period,
4949 @_FromDate,
4950 @_ToDate,
4951 ObjectID = tmp.EmployeeID,
4952 TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
4953 'S',
4954 tmp.ObjectAssignment,
4955 TargetsSuggest = MAX(kpid.TargetsSuggest), ---- temp
4956 TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
4957 TargetsSuggest = MAX(kpid.TargetsSuggest),
4958 TargetsAssigned = MAX(kpid.TargetsAssigned),
4959 Actual1 = CASE
4960 WHEN @_IsValiDateValidDistance = 1
4961 AND SUM(kpi.PC1) > 0 THEN
4962 SUM(kpi.AC1) / SUM(kpi.PC1)
4963 ELSE
4964 0
4965 END,
4966 Actual2 = CASE
4967 WHEN @_IsValidDateInvalidDistance = 1
4968 AND SUM(kpi.PC2) > 0 THEN
4969 SUM(kpi.AC2) / SUM(kpi.PC2)
4970 ELSE
4971 0
4972 END,
4973 Actual3 = CASE
4974 WHEN @_IsInvalidDate = 1
4975 AND SUM(kpi.PC3) > 0 THEN
4976 SUM(kpi.AC3) / SUM(kpi.PC3)
4977 ELSE
4978 0
4979 END,
4980 Actual4 = CASE
4981 WHEN @_IsDistributorSell = 1
4982 AND SUM(kpi.PC4) > 0 THEN
4983 SUM(kpi.AC4) / SUM(kpi.PC4)
4984 ELSE
4985 0
4986 END,
4987 Actual = CASE
4988 WHEN SUM(kpi.PC) > 0 THEN
4989 SUM(kpi.AC) / SUM(kpi.PC)
4990 ELSE
4991 0
4992 END
4993 FROM
4994 (
4995 SELECT sf.CompanyID,
4996 sf.EmployeeID,
4997 ObjectAssignment = CASE
4998 WHEN sf.TerritoryType = 'D' THEN
4999 r.ROUTE
5000 ELSE
5001 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
5002 END,
5003 sf.SalesOrg_0_ValueID
5004 FROM BELDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
5005 JOIN #tmpSalesForce sf WITH (NOLOCK)
5006 ON sf.CompanyID = objectPeriod.CompanyID
5007 AND sf.SFHierachyID = objectPeriod.KPIObjectID
5008 LEFT JOIN #tmpRoute r
5009 ON r.CompanyID = sf.CompanyID
5010 AND r.SalesForceID = sf.EmployeeID
5011 WHERE objectPeriod.CompanyID = @_CompanyID
5012 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
5013 AND sf.TerritoryType != 'S'
5014 AND
5015 (
5016 @_SalesAreaID IS NULL
5017 OR @_Level = -1
5018 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
5019 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
5020 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
5021 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
5022 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
5023 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
5024 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
5025 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
5026 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
5027 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
5028 )
5029 GROUP BY sf.CompanyID,
5030 sf.EmployeeID,
5031 CASE
5032 WHEN sf.TerritoryType = 'D' THEN
5033 r.ROUTE
5034 ELSE
5035 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
5036 END,
5037 sf.SalesOrg_0_ValueID
5038 ) tmp
5039 LEFT JOIN
5040 (
5041 SELECT kpid.CompanyID,
5042 kpid.ObjectID,
5043 kpid.KPIPeriodNbr,
5044 kpid.FromDate,
5045 kpid.ToDate,
5046 kpid.TargetsSuggest,
5047 kpid.TargetsAssigned
5048 FROM #tmpKPIDetail kpid
5049 WHERE kpid.CodeListSalesID = @_KPIID
5050 AND
5051 (
5052 @_RefNbr LIKE 'Temp-%'
5053 OR kpid.RefNbr = @_RefNbr
5054 )
5055 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
5056 AND kpid.FromDate = @_FromDate
5057 AND kpid.ToDate = @_ToDate
5058 ) kpid
5059 ON kpid.CompanyID = tmp.CompanyID
5060 AND kpid.ObjectID = tmp.EmployeeID
5061 LEFT JOIN
5062 (
5063 SELECT DISTINCT
5064 kpipc.CompanyID,
5065 kpipc.ObjectID,
5066 kpipc.ObjectAssignment,
5067 AC1 = kpisku.Actual1,
5068 AC2 = kpisku.Actual2,
5069 AC3 = kpisku.Actual3,
5070 AC4 = kpisku.Actual4,
5071 AC = kpisku.Actual,
5072 PC1 = kpipc.Actual1,
5073 PC2 = kpipc.Actual2,
5074 PC3 = kpipc.Actual3,
5075 PC4 = kpipc.Actual4,
5076 PC = kpipc.Actual,
5077 r.ROUTE,
5078 r.SalesAreaID
5079 FROM #tmpTotalPC kpipc
5080 JOIN #tmpTotalLine kpisku
5081 ON kpisku.CompanyID = kpipc.CompanyID
5082 AND kpisku.ObjectID = kpipc.ObjectID
5083 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
5084 JOIN #tmpSalesForce sf
5085 ON sf.CompanyID = kpipc.CompanyID
5086 AND sf.EmployeeID = kpipc.ObjectID
5087 AND sf.TerritoryType = 'S'
5088 JOIN #tmpRoute r
5089 ON r.CompanyID = sf.CompanyID
5090 AND r.SalespersonID = sf.EmployeeID
5091 ) kpi
5092 ON kpi.CompanyID = tmp.CompanyID
5093 AND
5094 (
5095 kpi.ROUTE = tmp.ObjectAssignment
5096 OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
5097 )
5098 WHERE kpid.ObjectID IS NOT NULL
5099 GROUP BY tmp.CompanyID,
5100 tmp.EmployeeID,
5101 tmp.ObjectAssignment;
5102 END;
5103 SET @_Min = @_Min + 1;
5104 END;
5105
5106 SELECT @_step = @_step + 1;
5107 END;
5108
5109
5110 --- Cập nhật lại các số target sau khi hoàn tất tính KPIs
5111 -- Thu.Nguyen 2019-06-07 - Remove Update Target - RedmineID: 59419
5112 --UPDATE kpi
5113 --SET kpi.TargetsSuggest = ROUND(kpi.TargetsSuggest / tmp.TotalAssignment, 2),
5114 -- kpi.TargetsAssigned = ROUND(kpi.TargetsAssigned / tmp.TotalAssignment, 2)
5115 --FROM #tmpKPI kpi
5116 -- JOIN
5117 -- (
5118 -- SELECT CompanyID,
5119 -- CodeListSalesID,
5120 -- KPIPeriodNbr,
5121 -- RefNbr,
5122 -- ObjectID,
5123 -- TotalAssignment = COUNT(*)
5124 -- FROM #tmpKPI
5125 -- GROUP BY CompanyID,
5126 -- CodeListSalesID,
5127 -- KPIPeriodNbr,
5128 -- RefNbr,
5129 -- ObjectID
5130 -- ) tmp
5131 -- ON tmp.CompanyID = kpi.CompanyID
5132 -- AND tmp.CodeListSalesID = kpi.CodeListSalesID
5133 -- AND tmp.KPIPeriodNbr = kpi.KPIPeriodNbr
5134 -- AND tmp.RefNbr = kpi.RefNbr
5135 -- AND tmp.ObjectID = kpi.ObjectID;
5136
5137 --UPDATE kpi
5138 --SET kpi.TargetsSuggest = kpi.TempTargetsSuggest - tmp.TargetsSuggest,
5139 -- kpi.TargetsAssigned = kpi.TempTargetsAssigned - tmp.TargetsAssigned
5140 --FROM #tmpKPI kpi
5141 -- JOIN
5142 -- (
5143 -- SELECT CompanyID,
5144 -- CodeListSalesID,
5145 -- KPIPeriodNbr,
5146 -- RefNbr,
5147 -- ObjectID,
5148 -- TargetsSuggest = SUM(TargetsSuggest),
5149 -- TargetsAssigned = SUM(TargetsAssigned)
5150 -- FROM #tmpKPI
5151 -- WHERE RowNumber > 1
5152 -- GROUP BY CompanyID,
5153 -- CodeListSalesID,
5154 -- KPIPeriodNbr,
5155 -- RefNbr,
5156 -- ObjectID
5157 -- ) tmp
5158 -- ON tmp.CompanyID = kpi.CompanyID
5159 -- AND tmp.CodeListSalesID = kpi.CodeListSalesID
5160 -- AND tmp.KPIPeriodNbr = kpi.KPIPeriodNbr
5161 -- AND tmp.RefNbr = kpi.RefNbr
5162 -- AND tmp.ObjectID = kpi.ObjectID
5163 --WHERE kpi.RowNumber = 1;
5164
5165
5166 INSERT INTO dbo.DMSBLKPI
5167 (
5168 BaselineDate,
5169 CompanyID,
5170 KPIID,
5171 KPICD,
5172 KPIDescr,
5173 SOURCE,
5174 TYPE,
5175 GroupBy,
5176 GroupID,
5177 Formula,
5178 Template,
5179 KPIPeriodNbr,
5180 RefNbr,
5181 PERIOD,
5182 FromDate,
5183 ToDate,
5184 ObjectID,
5185 ObjectType,
5186 ObjectAssignment,
5187 TargetsSuggest,
5188 TargetsAssigned,
5189 Actual1,
5190 Actual2,
5191 Actual3,
5192 Actual4,
5193 Actual
5194 )
5195 SELECT GETDATE(),
5196 CompanyID,
5197 CodeListSalesID,
5198 CodeListSalesCD,
5199 Descr,
5200 SOURCE,
5201 TYPE,
5202 GroupBy,
5203 GroupID,
5204 Formula,
5205 Template,
5206 KPIPeriodNbr,
5207 RefNbr,
5208 PERIOD,
5209 FromDate,
5210 ToDate,
5211 ObjectID,
5212 ObjectType,
5213 ObjectAssignment,
5214 TargetsSuggest,
5215 TargetsAssigned,
5216 Actual1,
5217 Actual2,
5218 Actual3,
5219 Actual4,
5220 Actual
5221 FROM
5222 (
5223 SELECT Temp = ROW_NUMBER() OVER (PARTITION BY CompanyID,
5224 CodeListSalesID,
5225 KPIPeriodNbr,
5226 ObjectID
5227 ORDER BY TempObjectID DESC,
5228 RefNbr DESC,
5229 Actual DESC
5230 ),
5231 *
5232 FROM #tmpKPI
5233 WHERE ObjectAssignment IS NOT NULL
5234 ) tmp
5235 WHERE tmp.Temp = 1;
5236
5237 UPDATE kpispcd
5238 SET kpispcd.ValuesBaseline = ISNULL(kpi.Actual, 0)
5239 FROM BELDMS..DMSKPISalesPeriodConfigurationDetail kpispcd
5240 JOIN BELDMS..DMSKPISalesPeriodConfigurationHeader kpispch
5241 ON kpispcd.CompanyID = kpispch.CompanyID
5242 AND kpispcd.RefNbr = kpispch.RefNbr
5243 JOIN
5244 (
5245 SELECT CompanyID,
5246 CodeListSalesID,
5247 KPIPeriodNbr,
5248 RefNbr,
5249 ObjectID,
5250 Actual = SUM(Actual)
5251 FROM #tmpKPI
5252 GROUP BY CompanyID,
5253 CodeListSalesID,
5254 KPIPeriodNbr,
5255 RefNbr,
5256 ObjectID
5257 ) kpi
5258 ON kpi.CompanyID = kpispcd.CompanyID
5259 AND kpi.RefNbr = kpispcd.RefNbr
5260 AND kpi.KPIPeriodNbr = kpispch.KPIPeriodNbr
5261 AND kpi.CodeListSalesID = kpispch.CodeListSalesID
5262 AND kpi.ObjectID = kpispcd.ThisObject;
5263
5264-- Tính KPI display
5265--EXEC dbo.sp_DMS_Baseline_KPI_Display 3, @_Date
5266END;