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