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