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