· 7 years ago · Nov 27, 2018, 03:32 AM
1SET QUOTED_IDENTIFIER ON
2SET ANSI_NULLS ON
3GO
4--[sp_DMS_RPT_BLKPI] 3,'sysadmin','2018-10-01','2018-10-31', null, 'KPIT10', 'M'
5ALTER PROCEDURE [dbo].[sp_DMS_RPT_BLKPI]
6 @CompanyID INT = NULL,
7 @LoginID VARCHAR(50) = NULL,
8 @FromDate DATETIME = NULL,
9 @ToDate DATETIME = NULL,
10 @RefNbr VARCHAR(50) = NULL,
11 @KPIPeriodNbr VARCHAR(50) = NULL,
12 @Period VARCHAR(1) = NULL
13AS
14BEGIN
15 SET NOCOUNT ON;
16 SET FMTONLY OFF;
17 SET NO_BROWSETABLE OFF;
18
19 DECLARE @_CompanyID INT = NULLIF(@CompanyID, 0);
20 DECLARE @_LoginID VARCHAR(50) = @LoginID;
21 DECLARE @_FromDate DATETIME = @FromDate;
22 DECLARE @_ToDate DATETIME = @ToDate;
23 DECLARE @_RefNbr VARCHAR(50) = @RefNbr;
24 DECLARE @_KPIPeriodNbr VARCHAR(50) = @KPIPeriodNbr;
25 DECLARE @_Period CHAR(1) = @Period;
26 DECLARE @_ObjectType CHAR(1);
27 DECLARE @_TerritoryType CHAR(1);
28 DECLARE @_ChangeTime DATETIME = '2018-10-01';
29
30
31 ---- Lấy thông tin permission theo user đăng nháºp
32 SELECT *
33 INTO #tmpPermission
34 FROM dbo.DMSBLSFPermission
35 WHERE CompanyID = @_CompanyID
36 AND LoginID = @_LoginID;
37
38 ---- Lấy working position của user đăng nháºp
39 SELECT @_TerritoryType = (SELECT TOP 1 TerritoryType FROM #tmpPermission);
40 IF @_TerritoryType IS NULL
41 SET @_TerritoryType = 'X';
42
43 ---- Group by các dữ liệu permission
44 SELECT CompanyID,
45 DistributorLocationID
46 INTO #tmpPrmDistributor
47 FROM #tmpPermission
48 GROUP BY CompanyID,
49 DistributorLocationID;
50
51 SELECT kpi.CompanyID,
52 kpi.KPIID,
53 kpi.KPICD,
54 kpi.KPIDescr,
55 kpi.ObjectID,
56 kpi.ObjectType,
57 kpi.ObjectAssignment,
58 kpi.FromDate,
59 kpi.ToDate,
60 kpi.KPIPeriodNbr,
61 kpi.RefNbr,
62 kpi.TargetsAssigned,
63 kpi.Actual1,
64 kpi.Actual2,
65 kpi.Actual3,
66 kpi.Actual4,
67 kpi.Actual
68 INTO #tmpKPI
69 FROM dbo.DMSBLKPI kpi WITH (NOLOCK)
70 WHERE kpi.CompanyID = @_CompanyID
71 AND kpi.KPIPeriodNbr = @_KPIPeriodNbr
72 AND ( ( @_Period = 'D'
73 AND kpi.Period = 'D'
74 AND kpi.RefNbr = @_RefNbr)
75 OR ( @_Period != 'D'
76 AND kpi.Period != 'D'
77 AND kpi.FromDate = @_FromDate
78 AND kpi.ToDate = @_ToDate))
79 AND kpi.TargetsAssigned IS NOT NULL;
80
81 -- Lấy ManDays
82
83 SELECT DISTINCT o.UsrSalesReps AS UserName,
84 c.CustomerCD,
85 c.LocationCD,
86 o.*
87 INTO #OList
88 FROM MRCDMS.dbo.SOOrder o
89 INNER JOIN MRCDMS..DMSViewCustomerLocation c
90 ON o.CustomerID = c.CustomerID
91 AND c.CompanyID = o.CompanyID
92 WHERE o.OrderDate BETWEEN @_FromDate AND @_ToDate
93 AND o.OrderType NOT IN ( 'CM' )
94 AND o.CompanyID = @CompanyID
95 ORDER BY o.UsrSalesReps;
96
97
98 UPDATE o
99 SET o.OrderType = 'CM'
100 FROM #OList o
101 WHERE EXISTS ( SELECT *
102 FROM MRCDMS.dbo.SOOrder so
103 WHERE so.OrderDate BETWEEN @_FromDate AND @_ToDate
104 AND so.CompanyID = @CompanyID
105 AND so.OrderType = 'CM'
106 AND so.UsrOriginDoc = o.OrderNbr);
107
108 --Mandays
109
110 ---Start count mandays before October 2018
111
112 IF (@_ToDate < @_ChangeTime)
113 BEGIN
114
115
116 SELECT mandaysTable.CompanyID,
117 mandaysTable.SalespersonID,
118 mandaysTable.StartWorkingDate,
119 COUNT(mandaysTable.IsSatisfy) AS Mandays,
120 mandaysTable.TotalWorkingday
121 INTO #TmpManDays
122 FROM ( SELECT tableTotal.SalespersonID,
123 tableTotal.CountAmount AS CountAmount,
124 tableTotal.CompanyID,
125 tableTotal.TotalAmount AS TotalAmount,
126 tableTotal.OrderDate AS OrderDate,
127 tableTotal.StartWorkingDate AS StartWorkingDate,
128 CASE
129 WHEN tableTotal.CountAmount >= 4 THEN 1
130 ELSE 0 END AS IsSatisfy,
131 tableTotal.TotalWorkingday
132 FROM ( SELECT ht.SalespersonID,
133 COUNT(ht.OrderAmount) AS CountAmount,
134 SUM(ht.OrderAmount) AS TotalAmount,
135 ht.OrderDate AS OrderDate,
136 ht.StartWorkingDate,
137 ht.TotalWorkingday,
138 ht.CompanyID
139 FROM (
140 --Test
141 SELECT h.OrderAmount,
142 h.OrderDate,
143 StartWorkingDate.StartWorkingDate,
144 u.SalespersonID,
145 visitPlan.TotalWorkingday,
146 h.CompanyID
147 FROM (
148 -------------------------------------Start Get History Order---------------------
149
150 SELECT o.UserName, -- UserName - nvarchar(50)
151 o.CustomerCD, -- CustomerCD - nvarchar(50)
152 o.LocationCD, -- LocationCD - nvarchar(50)
153 o.OrderNbr, -- OrderCode - nvarchar(50)
154 o.OrderDate, -- OrderDate - datetime
155 o.CompanyID, -- CompanyID - int
156 CASE
157 WHEN o.Status IN ( 'N', 'H', 'P' ) THEN 'N'
158 WHEN o.Status IN ( 'V', 'L' ) THEN 'L'
159 WHEN o.Status IN ( 'S', 'I' ) THEN 'S'
160 ELSE o.Status END OrderStatus, -- OrderStatus - nvarchar(2)
161 (o.OrderTotal + o.UsrDiscTot) OrderAmount, -- OrderAmount - float
162 o.OrderType,
163 o.UsrDiscTot,
164 o.OrderTotal,
165 o.OrderDesc,
166 CASE
167 WHEN ( o.CustomerOrderNbr IS NULL
168 OR o.CustomerOrderNbr = '') THEN o.OrderNbr
169 ELSE o.CustomerOrderNbr END AS RefNBR,
170 CASE
171 WHEN o.UsrExclusiveOrder = 1
172 AND ( o.UsrShipComplete IS NULL
173 OR o.UsrShipComplete = 0)
174 AND o.Status NOT IN ( 'V', 'L' ) THEN 1
175 ELSE 0 END AS NeedConfirm,
176 o.Completed,
177 ISNULL(p.ValidDate, 1) ValidDate,
178 ISNULL(p.ValidDistance, 1) ValidDistance
179 FROM #OList o
180 LEFT JOIN MRCDMS.dbo.DMSPDAOrder p
181 ON p.OrderNbr = o.CustomerOrderNbr
182 AND p.CompanyID = o.CompanyID
183 AND p.BranchID = o.BranchID
184 WHERE o.UsrSourceType = 'PDA'
185 AND o.Completed = 1) h
186 -------------------------------------End Get History Order---------------------
187 LEFT JOIN ( SELECT SalespersonID,
188 ISNULL(SUM(wrk.TotalWorkingday), 0) AS TotalWorkingday,
189 ISNULL(SUM(wrk.MTDWorkingday), 0) AS MTDWorkingday
190 FROM ( SELECT SalespersonID,
191 COUNT(DISTINCT VisitDate) AS TotalWorkingday,
192 0 AS MTDWorkingday
193 FROM MRCDMS.dbo.DMSVisitPlan o
194 WHERE DATEDIFF(mm, @_FromDate, VisitDate) = 0
195 AND o.CompanyID = @_CompanyID
196 GROUP BY SalespersonID
197 UNION ALL
198 SELECT SalespersonID,
199 0 AS TotalWorkingday,
200 COUNT(DISTINCT VisitDate) AS MTDWorkingday
201 FROM MRCDMS..DMSVisitPlan o
202 WHERE DATEDIFF(mm, @_FromDate, VisitDate) = 0
203 AND DATEDIFF(dd, @_FromDate, VisitDate) < 0
204 AND o.CompanyID = @_CompanyID
205 GROUP BY SalespersonID) AS wrk
206 GROUP BY SalespersonID) AS u
207 ON u.SalespersonID = h.UserName
208 -------------------------------------Start Get total working date follow visit plan---------------------
209 LEFT JOIN ( SELECT SalespersonID,
210 COUNT(DISTINCT VisitDate) AS TotalWorkingday
211 FROM MRCDMS.dbo.DMSVisitPlan o
212 WHERE DATEDIFF(mm, @_FromDate, VisitDate) = 0
213 AND o.CompanyID = @_CompanyID
214 GROUP BY o.SalespersonID) visitPlan
215 ON visitPlan.SalespersonID = u.SalespersonID
216
217 -------------------------------------End Get total working date follow visit plan---------------------
218 LEFT JOIN ( SELECT SalespersonID,
219 MIN(VisitDate) AS StartWorkingDate,
220 CompanyID
221 FROM MRCDMS.dbo.DMSVisitPlan
222 WHERE CompanyID = @_CompanyID
223 GROUP BY SalespersonID,
224 CompanyID) StartWorkingDate
225 ON StartWorkingDate.SalespersonID = u.SalespersonID
226 WHERE h.OrderAmount >= 50000
227 AND h.OrderDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @_FromDate), 0)
228 AND h.Completed = 1
229 AND h.OrderDate <= @_ToDate) ht
230 GROUP BY ht.OrderDate,
231 ht.StartWorkingDate,
232 ht.SalespersonID,
233 ht.TotalWorkingday,
234 ht.CompanyID) tableTotal ) mandaysTable
235 WHERE mandaysTable.IsSatisfy = 1 --Only get date satisfy;
236 GROUP BY mandaysTable.SalespersonID,
237 mandaysTable.StartWorkingDate,
238 mandaysTable.TotalWorkingday,
239 mandaysTable.IsSatisfy,
240 mandaysTable.CompanyID;
241
242
243 SELECT wrk.CompanyID,
244 SalespersonID,
245 ISNULL(SUM(wrk.TotalWorkingday), 0) AS TotalWorkingday,
246 ISNULL(SUM(wrk.MTDWorkingday), 0) AS MTDWorkingday
247 INTO #tmpTotalWorkingDate
248 FROM ( SELECT o.CompanyID,
249 SalespersonID,
250 COUNT(DISTINCT VisitDate) AS TotalWorkingday,
251 0 AS MTDWorkingday
252 FROM MRCDMS..DMSVisitPlan o
253 WHERE DATEDIFF(mm, @_FromDate, VisitDate) = 0
254 AND o.CompanyID = @_CompanyID
255 GROUP BY SalespersonID,
256 o.CompanyID
257 UNION ALL
258 SELECT o.CompanyID,
259 SalespersonID,
260 0 AS TotalWorkingday,
261 COUNT(DISTINCT VisitDate) AS MTDWorkingday
262 FROM MRCDMS..DMSVisitPlan o
263 WHERE DATEDIFF(mm, @_FromDate, VisitDate) = 0
264 AND DATEDIFF(dd, @_FromDate, VisitDate) < 0
265 AND o.CompanyID = @_CompanyID
266 GROUP BY SalespersonID,
267 o.CompanyID) wrk
268 GROUP BY SalespersonID,
269 wrk.CompanyID;
270
271
272 SELECT sf.CompanyID,
273 ds.DistributorID,
274 ds.SalesAreaCD AS SalessupCode,
275 ds.SalesAreaName AS SalesSupName,
276 sf.EmployeeCD,
277 sf.Descr AS EmployeeName,
278 StartWorkingDate.StartWorkingDate,
279 wd.TotalWorkingday AS WorkingDayStandard,
280 CASE
281 WHEN md.Mandays IS NOT NULL THEN md.Mandays
282 WHEN md.Mandays IS NULL THEN 0 END Mandays
283 INTO #tmpMandayOld
284 FROM MRCDMS.dbo.DMSSalesForce sf
285 INNER JOIN #tmpTotalWorkingDate wd
286 ON wd.CompanyID = sf.CompanyID
287 AND wd.SalespersonID = sf.EmployeeID
288 INNER JOIN MRCDMS.dbo.DMSViewL2InforDetailSalesman ds
289 ON ds.EmployeeID = sf.EmployeeID
290 LEFT JOIN #TmpManDays md
291 ON md.CompanyID = sf.CompanyID
292 AND md.SalespersonID = wd.SalespersonID
293 LEFT JOIN ( SELECT SalespersonID,
294 MIN(VisitDate) AS StartWorkingDate,
295 CompanyID
296 FROM MRCDMS.dbo.DMSVisitPlan
297 WHERE CompanyID = @_CompanyID
298 GROUP BY SalespersonID,
299 CompanyID) StartWorkingDate
300 ON StartWorkingDate.CompanyID = sf.CompanyID
301 AND StartWorkingDate.SalespersonID = sf.EmployeeID
302 WHERE sf.Active = 1;
303 END;
304 ---Starting count mandays from october 2018
305 ELSE IF (@_FromDate >= @_ChangeTime AND @_ToDate >= @_ChangeTime)
306 BEGIN
307
308 SELECT mandaysTable.CompanyID,
309 mandaysTable.SalespersonID,
310 mandaysTable.StartWorkingDate,
311 COUNT(mandaysTable.IsSatisfy) AS Mandays,
312 mandaysTable.TotalWorkingday
313 INTO #TmpManDays10
314 FROM ( SELECT tableTotal.SalespersonID,
315 tableTotal.CountAmount AS CountAmount,
316 tableTotal.CompanyID,
317 tableTotal.TotalAmount AS TotalAmount,
318 tableTotal.OrderDate AS OrderDate,
319 tableTotal.StartWorkingDate AS StartWorkingDate,
320 CASE
321 WHEN tableTotal.TotalCustomer >= 10 THEN 1
322 ELSE 0 END AS IsSatisfy,
323 tableTotal.TotalWorkingday
324 FROM ( SELECT ht.SalespersonID,
325 COUNT(ht.OrderAmount) AS CountAmount,
326 SUM(ht.OrderAmount) AS TotalAmount,
327 COUNT(DISTINCT ht.CustomerCD) AS TotalCustomer,
328 ht.OrderDate AS OrderDate,
329 ht.StartWorkingDate,
330 ht.TotalWorkingday,
331 ht.CompanyID
332 FROM ( SELECT h.OrderAmount,
333 h.OrderDate,
334 h.CustomerCD,
335 StartWorkingDate.StartWorkingDate,
336 u.SalespersonID,
337 visitPlan.TotalWorkingday,
338 h.CompanyID
339 FROM (
340 -------------------------------------Start Get History Order---------------------
341
342 SELECT o.UserName, -- UserName - nvarchar(50)
343 o.CustomerCD, -- CustomerCD - nvarchar(50)
344 o.LocationCD, -- LocationCD - nvarchar(50)
345 o.OrderNbr, -- OrderCode - nvarchar(50)
346 o.OrderDate, -- OrderDate - datetime
347 o.CompanyID, -- CompanyID - int
348 CASE
349 WHEN o.Status IN ( 'N', 'H', 'P' ) THEN 'N'
350 WHEN o.Status IN ( 'V', 'L' ) THEN 'L'
351 WHEN o.Status IN ( 'S', 'I' ) THEN 'S'
352 ELSE o.Status END OrderStatus, -- OrderStatus - nvarchar(2)
353 (o.OrderTotal + o.UsrDiscTot) OrderAmount, -- OrderAmount - float
354 o.OrderType,
355 o.UsrDiscTot,
356 o.OrderTotal,
357 o.OrderDesc,
358 CASE
359 WHEN ( o.CustomerOrderNbr IS NULL
360 OR o.CustomerOrderNbr = '') THEN o.OrderNbr
361 ELSE o.CustomerOrderNbr END AS RefNBR,
362 CASE
363 WHEN o.UsrExclusiveOrder = 1
364 AND ( o.UsrShipComplete IS NULL
365 OR o.UsrShipComplete = 0)
366 AND o.Status NOT IN ( 'V', 'L' ) THEN 1
367 ELSE 0 END AS NeedConfirm,
368 o.Completed,
369 ISNULL(p.ValidDate, 1) ValidDate,
370 ISNULL(p.ValidDistance, 1) ValidDistance
371 FROM #OList o
372 LEFT JOIN MRCDMS.dbo.DMSPDAOrder p
373 ON p.OrderNbr = o.CustomerOrderNbr
374 AND p.CompanyID = o.CompanyID
375 AND p.BranchID = o.BranchID
376 WHERE o.UsrSourceType = 'PDA'
377 AND o.Completed =1) h
378 -------------------------------------End Get History Order---------------------
379 LEFT JOIN ( SELECT SalespersonID,
380 ISNULL(SUM(wrk.TotalWorkingday), 0) AS TotalWorkingday,
381 ISNULL(SUM(wrk.MTDWorkingday), 0) AS MTDWorkingday
382 FROM ( SELECT SalespersonID,
383 COUNT(DISTINCT VisitDate) AS TotalWorkingday,
384 0 AS MTDWorkingday
385 FROM MRCDMS.dbo.DMSVisitPlan o
386 WHERE DATEDIFF(mm, @_FromDate, VisitDate) = 0
387 AND o.CompanyID = @_CompanyID
388 GROUP BY SalespersonID
389 UNION ALL
390 SELECT SalespersonID,
391 0 AS TotalWorkingday,
392 COUNT(DISTINCT VisitDate) AS MTDWorkingday
393 FROM MRCDMS..DMSVisitPlan o
394 WHERE DATEDIFF(mm, @_FromDate, VisitDate) = 0
395 AND DATEDIFF(dd, @_FromDate, VisitDate) < 0
396 AND o.CompanyID = @_CompanyID
397 GROUP BY SalespersonID) AS wrk
398 GROUP BY SalespersonID) AS u
399 ON u.SalespersonID = h.UserName
400 -------------------------------------Start Get total working date follow visit plan---------------------
401 LEFT JOIN ( SELECT SalespersonID,
402 COUNT(DISTINCT VisitDate) AS TotalWorkingday
403 FROM MRCDMS.dbo.DMSVisitPlan o
404 WHERE DATEDIFF(mm, @_FromDate, VisitDate) = 0
405 AND o.CompanyID = @_CompanyID
406 GROUP BY o.SalespersonID) visitPlan
407 ON visitPlan.SalespersonID = u.SalespersonID
408
409 -------------------------------------End Get total working date follow visit plan---------------------
410 LEFT JOIN ( SELECT SalespersonID,
411 MIN(VisitDate) AS StartWorkingDate,
412 CompanyID
413 FROM MRCDMS.dbo.DMSVisitPlan
414 WHERE CompanyID = @_CompanyID
415 GROUP BY SalespersonID,
416 CompanyID) StartWorkingDate
417 ON StartWorkingDate.SalespersonID = u.SalespersonID
418 WHERE h.OrderAmount >= 50000
419 AND h.OrderDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @_FromDate), 0)
420 AND h.OrderDate <= @_ToDate
421 AND h.Completed =1) ht
422 GROUP BY ht.OrderDate,
423 ht.StartWorkingDate,
424 ht.SalespersonID,
425 ht.TotalWorkingday,
426 ht.CompanyID) tableTotal ) mandaysTable
427 WHERE mandaysTable.IsSatisfy = 1 --Only get date satisfy;
428 GROUP BY mandaysTable.SalespersonID,
429 mandaysTable.StartWorkingDate,
430 mandaysTable.TotalWorkingday,
431 mandaysTable.IsSatisfy,
432 mandaysTable.CompanyID;
433
434
435 SELECT wrk.CompanyID,
436 SalespersonID,
437 ISNULL(SUM(wrk.TotalWorkingday), 0) AS TotalWorkingday,
438 ISNULL(SUM(wrk.MTDWorkingday), 0) AS MTDWorkingday
439 INTO #tmpTotalWorkingDate10
440 FROM ( SELECT o.CompanyID,
441 SalespersonID,
442 COUNT(DISTINCT VisitDate) AS TotalWorkingday,
443 0 AS MTDWorkingday
444 FROM MRCDMS..DMSVisitPlan o
445 WHERE DATEDIFF(mm, @_FromDate, VisitDate) = 0
446 AND o.CompanyID = @_CompanyID
447 GROUP BY SalespersonID,
448 o.CompanyID
449 UNION ALL
450 SELECT o.CompanyID,
451 SalespersonID,
452 0 AS TotalWorkingday,
453 COUNT(DISTINCT VisitDate) AS MTDWorkingday
454 FROM MRCDMS..DMSVisitPlan o
455 WHERE DATEDIFF(mm, @_FromDate, VisitDate) = 0
456 AND DATEDIFF(dd, @_FromDate, VisitDate) < 0
457 AND o.CompanyID = @_CompanyID
458 GROUP BY SalespersonID,
459 o.CompanyID) wrk
460 GROUP BY SalespersonID,
461 wrk.CompanyID;
462
463
464
465 SELECT sf.CompanyID,
466 ds.DistributorID,
467 ds.SalesAreaCD AS SalessupCode,
468 ds.SalesAreaName AS SalesSupName,
469 sf.EmployeeCD,
470 sf.Descr AS EmployeeName,
471 StartWorkingDate.StartWorkingDate,
472 wd.TotalWorkingday AS WorkingDayStandard,
473 CASE
474 WHEN md.Mandays IS NOT NULL THEN md.Mandays
475 WHEN md.Mandays IS NULL THEN 0 END Mandays
476 INTO #tmpMandayNew
477 FROM MRCDMS.dbo.DMSSalesForce sf
478 INNER JOIN #tmpTotalWorkingDate10 wd
479 ON wd.CompanyID = sf.CompanyID
480 AND wd.SalespersonID = sf.EmployeeID
481 INNER JOIN MRCDMS.dbo.DMSViewL2InforDetailSalesman ds
482 ON ds.EmployeeID = sf.EmployeeID
483 LEFT JOIN #TmpManDays10 md
484 ON md.CompanyID = sf.CompanyID
485 AND md.SalespersonID = wd.SalespersonID
486 LEFT JOIN ( SELECT SalespersonID,
487 MIN(VisitDate) AS StartWorkingDate,
488 CompanyID
489 FROM MRCDMS.dbo.DMSVisitPlan
490 WHERE CompanyID = @_CompanyID
491 GROUP BY SalespersonID,
492 CompanyID) StartWorkingDate
493 ON StartWorkingDate.CompanyID = sf.CompanyID
494 AND StartWorkingDate.SalespersonID = sf.EmployeeID
495 WHERE sf.Active = 1;
496 END;
497
498
499
500 --Combine Mandays, just only temp table has data (if user select data only one month)
501
502 CREATE TABLE [#tmpFinalMandays] ([CompanyID] INT NOT NULL,
503 [DistributorID] INT NOT NULL,
504 [SalessupCode] VARCHAR(15),
505 [SalesSupName] NVARCHAR(50),
506 [EmployeeCD] VARCHAR(15) NOT NULL,
507 [EmployeeName] NVARCHAR(50),
508 [StartWorkingDate] DATETIME,
509 [WorkingDayStandard] INT,
510 [Mandays] INT);
511
512 IF (@_ToDate < @_ChangeTime)
513 BEGIN
514 INSERT INTO #tmpFinalMandays (CompanyID,
515 DistributorID,
516 SalessupCode,
517 SalesSupName,
518 EmployeeCD,
519 EmployeeName,
520 StartWorkingDate,
521 WorkingDayStandard,
522 Mandays)
523 SELECT *
524 FROM #tmpMandayOld;
525 END;
526 ELSE IF (@_FromDate >= @_ChangeTime AND @_ToDate >= @_ChangeTime)
527 BEGIN
528 INSERT INTO #tmpFinalMandays (CompanyID,
529 DistributorID,
530 SalessupCode,
531 SalesSupName,
532 EmployeeCD,
533 EmployeeName,
534 StartWorkingDate,
535 WorkingDayStandard,
536 Mandays)
537 SELECT *
538 FROM #tmpMandayNew;
539 END;
540
541 ---- Lấy dữ liệu tháng hiện tại
542 SELECT DISTINCT blkpi.CompanyID,
543 blkpi.KPIID,
544 blkpi.KPICD,
545 blkpi.KPIDescr,
546 blkpi.ObjectID,
547 blkpi.FromDate,
548 blkpi.ToDate,
549 blkpi.KPIPeriodNbr,
550 blkpi.RefNbr,
551 SalesSupCD = CONVERT(VARCHAR(15), ''),
552 SalesSupName = CONVERT(NVARCHAR(50), ''),
553 ObjectCD = COALESCE(blsf.EmployeeCD, bldl.LocationCD),
554 ObjectName = COALESCE(blsf.EmployeeName, bldl.Descr),
555 blsf.SFHierachyName,
556 blkpi.ObjectType,
557 ObjectDetailType = COALESCE(blsf.TerritoryType, 'X'),
558 blkpi.ObjectAssignment,
559 Target = blkpi.TargetsAssigned,
560 CorrectRouteCorrectCoordinate = blkpi.Actual1,
561 CorrectRouteIncorrectCoordinate = blkpi.Actual2,
562 IncorrectRoute = blkpi.Actual3,
563 DistributorSell = blkpi.Actual4,
564 Actual = blkpi.Actual,
565 Incent = NULL,
566 bldl.BLSalesTerritoryID,
567 blsf.TerritoryType,
568 blsf.SalesOrgValueID,
569 DistributorID = bldl.DistributorID,
570 DistributorLocationID = bldl.LocationID,
571 DistributorCD = bldl.DistributorCD,
572 DistributorName = bldl.DistributorName,
573 Route = CONVERT(NVARCHAR(100), ''),
574 Province = CONVERT(NVARCHAR(100), ''),
575 District = CONVERT(NVARCHAR(100), ''),
576 Channel = CONVERT(NVARCHAR(100), ''),
577 SalesOrg_0_ValueName = CONVERT(NVARCHAR(100), ''),
578 SalesOrg_1_ValueName = CONVERT(NVARCHAR(100), ''),
579 SalesOrg_2_ValueName = CONVERT(NVARCHAR(100), ''),
580 SalesOrg_3_ValueName = CONVERT(NVARCHAR(100), ''),
581 SalesOrg_4_ValueName = CONVERT(NVARCHAR(100), ''),
582 SalesOrg_5_ValueName = CONVERT(NVARCHAR(100), ''),
583 SalesOrg_6_ValueName = CONVERT(NVARCHAR(100), ''),
584 SalesOrg_7_ValueName = CONVERT(NVARCHAR(100), ''),
585 SalesOrg_8_ValueName = CONVERT(NVARCHAR(100), ''),
586 SalesOrg_9_ValueName = CONVERT(NVARCHAR(100), ''),
587 SalesForce_0_Name = CONVERT(NVARCHAR(100), ''),
588 SalesForce_1_Name = CONVERT(NVARCHAR(100), ''),
589 SalesForce_2_Name = CONVERT(NVARCHAR(100), ''),
590 SalesForce_3_Name = CONVERT(NVARCHAR(100), ''),
591 SalesForce_4_Name = CONVERT(NVARCHAR(100), ''),
592 SalesForce_5_Name = CONVERT(NVARCHAR(100), ''),
593 SalesForce_6_Name = CONVERT(NVARCHAR(100), ''),
594 SalesForce_7_Name = CONVERT(NVARCHAR(100), ''),
595 SalesForce_8_Name = CONVERT(NVARCHAR(100), ''),
596 SalesForce_9_Name = CONVERT(NVARCHAR(100), ''),
597 ManDays = NULL
598 INTO #tmpResult
599 FROM #tmpKPI blkpi
600 LEFT JOIN dbo.DMSBLSalesForce blsf WITH (NOLOCK)
601 ON blsf.CompanyID = blkpi.CompanyID
602 AND blsf.EmployeeID = blkpi.ObjectID
603 AND ISNULL(blsf.ExpirationDate, @_ToDate) >= @_FromDate
604 AND ISNULL(blsf.ExpirationDate, @_ToDate) <= @_ToDate
605 LEFT JOIN dbo.DMSBLDistributorLocation bldl WITH (NOLOCK)
606 ON bldl.CompanyID = blkpi.CompanyID
607 AND bldl.LocationID = blkpi.ObjectID
608 AND bldl.ExpirationDate IS NULL
609 AND blkpi.ObjectType = 'D';
610
611 SELECT TOP 1 @_ObjectType = ObjectType
612 FROM #tmpResult
613 WHERE KPIPeriodNbr = @_KPIPeriodNbr;
614
615 ---- Lấy dữ liệu assignment hiện tại theo NPP
616 SELECT DISTINCT bldl.CompanyID,
617 bldl.DistributorID,
618 DistributorLocationID = bldl.LocationID,
619 bldl.DistributorCD,
620 bldl.DistributorName,
621 Province = blgt.ProvinceName,
622 District = blgt.DistrictName,
623 blst.ChannelID,
624 Channel = blst.ChannelName,
625 blst.SalesOrg_0_ValueID,
626 blst.SalesOrg_0_ValueName,
627 blst.SalesOrg_1_ValueID,
628 blst.SalesOrg_1_ValueName,
629 blst.SalesOrg_2_ValueID,
630 blst.SalesOrg_2_ValueName,
631 blst.SalesOrg_3_ValueID,
632 blst.SalesOrg_3_ValueName,
633 blst.SalesOrg_4_ValueID,
634 blst.SalesOrg_4_ValueName,
635 blst.SalesOrg_5_ValueID,
636 blst.SalesOrg_5_ValueName,
637 blst.SalesOrg_6_ValueID,
638 blst.SalesOrg_6_ValueName,
639 blst.SalesOrg_7_ValueID,
640 blst.SalesOrg_7_ValueName,
641 blst.SalesOrg_8_ValueID,
642 blst.SalesOrg_8_ValueName,
643 blst.SalesOrg_9_ValueID,
644 blst.SalesOrg_9_ValueName,
645 SalesForce_0_Name = blsf0.EmployeeName,
646 SalesForce_1_Name = blsf1.EmployeeName,
647 SalesForce_2_Name = blsf2.EmployeeName,
648 SalesForce_3_Name = blsf3.EmployeeName,
649 SalesForce_4_Name = blsf4.EmployeeName,
650 SalesForce_5_Name = blsf5.EmployeeName,
651 SalesForce_6_Name = blsf6.EmployeeName,
652 SalesForce_7_Name = blsf7.EmployeeName,
653 SalesForce_8_Name = blsf8.EmployeeName,
654 SalesForce_9_Name = blsf9.EmployeeName
655 INTO #tmpDistributorLocation
656 FROM dbo.DMSBLDistributorLocation bldl WITH (NOLOCK)
657 LEFT JOIN dbo.DMSBLGeoTerritory blgt WITH (NOLOCK)
658 ON blgt.CompanyID = bldl.CompanyID
659 AND blgt.ID = bldl.BLGeoTerritoryID
660 LEFT JOIN dbo.DMSBLSalesTerritory blst WITH (NOLOCK)
661 ON blst.CompanyID = bldl.CompanyID
662 AND blst.ID = bldl.BLSalesTerritoryID
663 LEFT JOIN dbo.DMSBLSalesForce blsf0 WITH (NOLOCK)
664 ON blsf0.CompanyID = blst.CompanyID
665 AND blsf0.SalesOrgValueID = blst.SalesOrg_0_ValueID
666 AND blsf0.ExpirationDate IS NULL
667 LEFT JOIN dbo.DMSBLSalesForce blsf1 WITH (NOLOCK)
668 ON blsf1.CompanyID = blst.CompanyID
669 AND blsf1.SalesOrgValueID = blst.SalesOrg_1_ValueID
670 AND blsf1.ExpirationDate IS NULL
671 LEFT JOIN dbo.DMSBLSalesForce blsf2 WITH (NOLOCK)
672 ON blsf2.CompanyID = blst.CompanyID
673 AND blsf2.SalesOrgValueID = blst.SalesOrg_2_ValueID
674 AND blsf2.ExpirationDate IS NULL
675 LEFT JOIN dbo.DMSBLSalesForce blsf3 WITH (NOLOCK)
676 ON blsf3.CompanyID = blst.CompanyID
677 AND blsf3.SalesOrgValueID = blst.SalesOrg_3_ValueID
678 AND blsf3.ExpirationDate IS NULL
679 LEFT JOIN dbo.DMSBLSalesForce blsf4 WITH (NOLOCK)
680 ON blsf4.CompanyID = blst.CompanyID
681 AND blsf4.SalesOrgValueID = blst.SalesOrg_4_ValueID
682 AND blsf4.ExpirationDate IS NULL
683 LEFT JOIN dbo.DMSBLSalesForce blsf5 WITH (NOLOCK)
684 ON blsf5.CompanyID = blst.CompanyID
685 AND blsf5.SalesOrgValueID = blst.SalesOrg_5_ValueID
686 AND blsf5.ExpirationDate IS NULL
687 LEFT JOIN dbo.DMSBLSalesForce blsf6 WITH (NOLOCK)
688 ON blsf6.CompanyID = blst.CompanyID
689 AND blsf6.SalesOrgValueID = blst.SalesOrg_6_ValueID
690 AND blsf6.ExpirationDate IS NULL
691 LEFT JOIN dbo.DMSBLSalesForce blsf7 WITH (NOLOCK)
692 ON blsf7.CompanyID = blst.CompanyID
693 AND blsf7.SalesOrgValueID = blst.SalesOrg_7_ValueID
694 AND blsf7.ExpirationDate IS NULL
695 LEFT JOIN dbo.DMSBLSalesForce blsf8 WITH (NOLOCK)
696 ON blsf8.CompanyID = blst.CompanyID
697 AND blsf8.SalesOrgValueID = blst.SalesOrg_8_ValueID
698 AND blsf8.ExpirationDate IS NULL
699 LEFT JOIN dbo.DMSBLSalesForce blsf9 WITH (NOLOCK)
700 ON blsf9.CompanyID = blst.CompanyID
701 AND blsf9.SalesOrgValueID = blst.SalesOrg_9_ValueID
702 AND blsf9.ExpirationDate IS NULL
703 WHERE bldl.CompanyID = @_CompanyID
704 AND bldl.ExpirationDate IS NULL
705 AND bldl.IsActive = 1;
706
707 ---- Nếu đối tượng là NPP thì cáºp nháºt sales territory cho NPP
708 IF @_ObjectType = 'D'
709 BEGIN
710 UPDATE r
711 SET r.Province = dl.Province,
712 r.District = dl.District,
713 r.SalesOrg_0_ValueName = dl.SalesOrg_0_ValueName,
714 r.SalesOrg_1_ValueName = dl.SalesOrg_1_ValueName,
715 r.SalesOrg_2_ValueName = dl.SalesOrg_2_ValueName,
716 r.SalesOrg_3_ValueName = dl.SalesOrg_3_ValueName,
717 r.SalesOrg_4_ValueName = dl.SalesOrg_4_ValueName,
718 r.SalesOrg_5_ValueName = dl.SalesOrg_5_ValueName,
719 r.SalesOrg_6_ValueName = dl.SalesOrg_6_ValueName,
720 r.SalesOrg_7_ValueName = dl.SalesOrg_7_ValueName,
721 r.SalesOrg_8_ValueName = dl.SalesOrg_8_ValueName,
722 r.SalesOrg_9_ValueName = dl.SalesOrg_9_ValueName,
723 r.SalesForce_0_Name = dl.SalesForce_0_Name,
724 r.SalesForce_1_Name = dl.SalesForce_1_Name,
725 r.SalesForce_2_Name = dl.SalesForce_2_Name,
726 r.SalesForce_3_Name = dl.SalesForce_3_Name,
727 r.SalesForce_4_Name = dl.SalesForce_4_Name,
728 r.SalesForce_5_Name = dl.SalesForce_5_Name,
729 r.SalesForce_6_Name = dl.SalesForce_6_Name,
730 r.SalesForce_7_Name = dl.SalesForce_7_Name,
731 r.SalesForce_8_Name = dl.SalesForce_8_Name,
732 r.SalesForce_9_Name = dl.SalesForce_9_Name
733 FROM #tmpResult r
734 JOIN #tmpDistributorLocation dl
735 ON dl.CompanyID = r.CompanyID
736 AND dl.DistributorLocationID = r.ObjectID;
737
738 DELETE FROM #tmpResult
739 WHERE NOT EXISTS ( SELECT *
740 FROM #tmpPrmDistributor d
741 WHERE d.CompanyID = #tmpResult.CompanyID
742 AND d.DistributorLocationID = #tmpResult.ObjectID);
743 END;
744 ---- Nếu đối tượng là Sales thì cáºp nháºt thông tin Route đối vá»›i SM, SS và thông tin Sales Area đối vá»›i ASM, RSM
745 ELSE
746 BEGIN
747 --DECLARE @_MonthID CHAR(6) = @_Month + @_Year
748 DECLARE @_StartDate DATETIME;
749 DECLARE @_EndDate DATETIME;
750 IF @_Period = 'D'
751 SELECT @_StartDate = MAX(FromDate),
752 @_EndDate = MAX(ToDate)
753 FROM #tmpResult
754 WHERE CompanyID = @_CompanyID
755 AND KPIPeriodNbr = @_KPIPeriodNbr;
756 ELSE
757 BEGIN
758 SET @_StartDate = @_FromDate;
759 SET @_EndDate = @_ToDate;
760 END;
761
762 SELECT sf.CompanyID,
763 sf.EmployeeID,
764 sf.EmployeeCD,
765 sf.EmployeeName,
766 sf.TerritoryType,
767 sf.SalesOrgValueID,
768 dl.DistributorID,
769 dl.DistributorLocationID,
770 dl.Channel,
771 dl.SalesOrg_0_ValueName,
772 dl.SalesOrg_1_ValueName,
773 dl.SalesOrg_2_ValueName,
774 dl.SalesOrg_3_ValueName,
775 dl.SalesOrg_4_ValueName,
776 dl.SalesOrg_5_ValueName,
777 dl.SalesOrg_6_ValueName,
778 dl.SalesOrg_7_ValueName,
779 dl.SalesOrg_8_ValueName,
780 dl.SalesOrg_9_ValueName,
781 dl.SalesForce_0_Name,
782 dl.SalesForce_1_Name,
783 dl.SalesForce_2_Name,
784 dl.SalesForce_3_Name,
785 dl.SalesForce_4_Name,
786 dl.SalesForce_5_Name,
787 dl.SalesForce_6_Name,
788 dl.SalesForce_7_Name,
789 dl.SalesForce_8_Name,
790 dl.SalesForce_9_Name
791 INTO #tmpSalesForce
792 FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
793 JOIN #tmpDistributorLocation dl
794 ON dl.CompanyID = sf.CompanyID
795 AND ( dl.SalesOrg_0_ValueID = sf.SalesOrgValueID
796 OR dl.SalesOrg_1_ValueID = sf.SalesOrgValueID
797 OR dl.SalesOrg_2_ValueID = sf.SalesOrgValueID
798 OR dl.SalesOrg_3_ValueID = sf.SalesOrgValueID
799 OR dl.SalesOrg_4_ValueID = sf.SalesOrgValueID
800 OR dl.SalesOrg_5_ValueID = sf.SalesOrgValueID
801 OR dl.SalesOrg_6_ValueID = sf.SalesOrgValueID
802 OR dl.SalesOrg_7_ValueID = sf.SalesOrgValueID
803 OR dl.SalesOrg_8_ValueID = sf.SalesOrgValueID
804 OR dl.SalesOrg_9_ValueID = sf.SalesOrgValueID)
805 WHERE sf.SalesOrgValueID IS NOT NULL;
806
807 SELECT tmp.CompanyID,
808 tmp.DistributorID,
809 tmp.DistributorLocationID,
810 tmp.CustomerID,
811 tmp.CustomerLocationID,
812 tmp.Route,
813 tmp.SalespersonID,
814 SalespersonCD = sp.EmployeeCD,
815 SalespersonName = sp.EmployeeName,
816 SalesSupCD = ss.EmployeeCD,
817 SalesSupName = ss.EmployeeName,
818 tmp.SalesForceID
819 INTO #tmpMCPSettings
820 FROM ( SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY mcp.CompanyID,
821 mcp.Route,
822 mcp.DistributorID,
823 mcp.DistributorLocationID,
824 mcp.SalespersonID
825 ORDER BY mcp.EffectiveDate DESC),
826 mcp.CompanyID,
827 mcp.DistributorID,
828 mcp.DistributorLocationID,
829 mcp.CustomerID,
830 mcp.CustomerLocationID,
831 mcp.SalespersonID,
832 mcp.SalesForceID,
833 mcp.Route
834 FROM dbo.DMSBLMCPSettings mcp WITH (NOLOCK)
835 WHERE mcp.CompanyID = @_CompanyID
836 GROUP BY mcp.CompanyID,
837 mcp.DistributorID,
838 mcp.DistributorLocationID,
839 mcp.CustomerID,
840 mcp.CustomerLocationID,
841 mcp.SalespersonID,
842 mcp.SalesForceID,
843 mcp.Route,
844 mcp.EffectiveDate) tmp
845 LEFT JOIN #tmpSalesForce sp
846 ON sp.CompanyID = tmp.CompanyID
847 AND sp.EmployeeID = tmp.SalespersonID
848 LEFT JOIN #tmpSalesForce ss
849 ON ss.CompanyID = tmp.CompanyID
850 AND ss.EmployeeID = tmp.SalesForceID
851 WHERE tmp.RowNumber = 1;
852
853 SELECT mcp.CompanyID,
854 mcp.Route,
855 bldl.DistributorID,
856 bldl.DistributorLocationID,
857 bldl.DistributorCD,
858 bldl.DistributorName,
859 mcp.SalespersonID,
860 mcp.SalesSupCD,
861 mcp.SalesSupName,
862 mcp.SalespersonName,
863 mcp.SalesForceID,
864 bldl.Province,
865 bldl.District,
866 bldl.Channel,
867 bldl.SalesOrg_0_ValueName,
868 bldl.SalesOrg_1_ValueName,
869 bldl.SalesOrg_2_ValueName,
870 bldl.SalesOrg_3_ValueName,
871 bldl.SalesOrg_4_ValueName,
872 bldl.SalesOrg_5_ValueName,
873 bldl.SalesOrg_6_ValueName,
874 bldl.SalesOrg_7_ValueName,
875 bldl.SalesOrg_8_ValueName,
876 bldl.SalesOrg_9_ValueName,
877 bldl.SalesForce_0_Name,
878 bldl.SalesForce_1_Name,
879 bldl.SalesForce_2_Name,
880 bldl.SalesForce_3_Name,
881 bldl.SalesForce_4_Name,
882 bldl.SalesForce_5_Name,
883 bldl.SalesForce_6_Name,
884 bldl.SalesForce_7_Name,
885 bldl.SalesForce_8_Name,
886 bldl.SalesForce_9_Name
887 INTO #tmpMCP
888 FROM #tmpMCPSettings mcp
889 JOIN #tmpDistributorLocation bldl WITH (NOLOCK)
890 ON bldl.CompanyID = mcp.CompanyID
891 AND bldl.DistributorID = mcp.DistributorID
892 AND bldl.DistributorLocationID = mcp.DistributorLocationID;
893
894 UPDATE r
895 SET r.DistributorID = mcp.DistributorID,
896 r.DistributorLocationID = mcp.DistributorLocationID,
897 r.DistributorCD = mcp.DistributorCD,
898 r.DistributorName = mcp.DistributorName,
899 r.Route = mcp.Route,
900 r.SalesSupCD = mcp.SalesSupCD,
901 r.SalesSupName = mcp.SalesSupName,
902 r.Province = mcp.Province,
903 r.District = mcp.District,
904 r.Channel = mcp.Channel,
905 r.SalesOrg_0_ValueName = mcp.SalesOrg_0_ValueName,
906 r.SalesOrg_1_ValueName = mcp.SalesOrg_1_ValueName,
907 r.SalesOrg_2_ValueName = mcp.SalesOrg_2_ValueName,
908 r.SalesOrg_3_ValueName = mcp.SalesOrg_3_ValueName,
909 r.SalesOrg_4_ValueName = mcp.SalesOrg_4_ValueName,
910 r.SalesOrg_5_ValueName = mcp.SalesOrg_5_ValueName,
911 r.SalesOrg_6_ValueName = mcp.SalesOrg_6_ValueName,
912 r.SalesOrg_7_ValueName = mcp.SalesOrg_7_ValueName,
913 r.SalesOrg_8_ValueName = mcp.SalesOrg_8_ValueName,
914 r.SalesOrg_9_ValueName = mcp.SalesOrg_9_ValueName,
915 r.SalesForce_0_Name = mcp.SalesForce_0_Name,
916 r.SalesForce_1_Name = mcp.SalesForce_1_Name,
917 r.SalesForce_2_Name = mcp.SalesForce_2_Name,
918 r.SalesForce_3_Name = mcp.SalesForce_3_Name,
919 r.SalesForce_4_Name = mcp.SalesForce_4_Name,
920 r.SalesForce_5_Name = mcp.SalesForce_5_Name,
921 r.SalesForce_6_Name = mcp.SalesForce_6_Name,
922 r.SalesForce_7_Name = mcp.SalesForce_7_Name,
923 r.SalesForce_8_Name = mcp.SalesForce_8_Name,
924 r.SalesForce_9_Name = mcp.SalesForce_9_Name
925 FROM #tmpResult r
926 JOIN #tmpMCP mcp
927 ON mcp.CompanyID = r.CompanyID
928 AND ( mcp.SalespersonID = r.ObjectID
929 AND mcp.Route = r.ObjectAssignment);
930
931 UPDATE r
932 SET r.DistributorID = sf.DistributorID,
933 r.DistributorLocationID = sf.DistributorLocationID,
934 r.Channel = sf.Channel,
935 r.SalesOrg_0_ValueName = sf.SalesOrg_0_ValueName,
936 r.SalesOrg_1_ValueName = sf.SalesOrg_1_ValueName,
937 r.SalesOrg_2_ValueName = sf.SalesOrg_2_ValueName,
938 r.SalesOrg_3_ValueName = sf.SalesOrg_3_ValueName,
939 r.SalesOrg_4_ValueName = sf.SalesOrg_4_ValueName,
940 r.SalesOrg_5_ValueName = sf.SalesOrg_5_ValueName,
941 r.SalesOrg_6_ValueName = sf.SalesOrg_6_ValueName,
942 r.SalesOrg_7_ValueName = sf.SalesOrg_7_ValueName,
943 r.SalesOrg_8_ValueName = sf.SalesOrg_8_ValueName,
944 r.SalesOrg_9_ValueName = sf.SalesOrg_9_ValueName,
945 r.SalesForce_0_Name = sf.SalesForce_0_Name,
946 r.SalesForce_1_Name = sf.SalesForce_1_Name,
947 r.SalesForce_2_Name = sf.SalesForce_2_Name,
948 r.SalesForce_3_Name = sf.SalesForce_3_Name,
949 r.SalesForce_4_Name = sf.SalesForce_4_Name,
950 r.SalesForce_5_Name = sf.SalesForce_5_Name,
951 r.SalesForce_6_Name = sf.SalesForce_6_Name,
952 r.SalesForce_7_Name = sf.SalesForce_7_Name,
953 r.SalesForce_8_Name = sf.SalesForce_8_Name,
954 r.SalesForce_9_Name = sf.SalesForce_9_Name
955 FROM #tmpResult r
956 JOIN #tmpSalesForce sf
957 ON sf.CompanyID = r.CompanyID
958 AND sf.EmployeeID = r.ObjectID;
959
960
961
962 DELETE FROM #tmpResult
963 WHERE NOT EXISTS ( SELECT *
964 FROM #tmpPrmDistributor d
965 WHERE d.CompanyID = #tmpResult.CompanyID
966 AND d.DistributorLocationID = #tmpResult.DistributorLocationID);
967 END;
968
969 -- Lấy incentive của đối tượng
970 CREATE TABLE #tmpIncentive (CompanyID INT NOT NULL,
971 RefNbr NVARCHAR(10) NULL,
972 CodeListSalesID INT NULL,
973 ThisObject INT NULL,
974 Incentive DECIMAL NULL);
975 INSERT INTO #tmpIncentive
976 SELECT DISTINCT *
977 FROM ( SELECT detail.CompanyID,
978 header.RefNbr,
979 incentive.CodeListSalesID,
980 detail.ThisObject,
981 Incentive = incentive.Incentive
982 + CASE
983 WHEN incentive.Prorate = CAST(1 AS BIT)
984 AND kpi.AccumulateBy = 'P' THEN
985 ((((NULLIF(detail.ValuesBaseline, 0)
986 / NULLIF(CASE
987 WHEN detail.TargetsAdjust IS NULL THEN detail.TargetsAssigned
988 ELSE detail.TargetsAdjust END, 0)) * 100) - incentive.[From])
989 * (level.Incentive - incentive.Incentive)) / (incentive.[To] - incentive.[From])
990 ELSE 0 END
991 FROM MRCDMS..DMSKPISalesPeriodConfigurationDetail detail
992 INNER JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader header
993 ON header.CompanyID = detail.CompanyID
994 AND header.RefNbr = detail.RefNbr
995 INNER JOIN MRCDMS..DMSKPICategory cat
996 ON cat.CompanyID = header.CompanyID
997 AND cat.CategoryID = header.KPICategoryID
998 INNER JOIN MRCDMS..DMSKPIIncentiveDetails incentive
999 ON incentive.CompanyID = header.CompanyID
1000 AND incentive.CodeListSalesID = header.CodeListSalesID
1001 AND incentive.Incentive > 0 -- l2 them
1002 LEFT JOIN MRCDMS..DMSKPIIncentiveDetails level
1003 ON level.CompanyID = incentive.CompanyID
1004 AND level.CodeListSalesID = incentive.CodeListSalesID
1005 AND level.[From] = incentive.[To]
1006 INNER JOIN MRCDMS..DMSKPIListSales kpi
1007 ON kpi.CompanyID = incentive.CompanyID
1008 AND kpi.CodeListSalesID = incentive.CodeListSalesID
1009 LEFT JOIN ( SELECT inc.CompanyID,
1010 inc.CodeListSalesID,
1011 MaxTo = MAX(inc.[To])
1012 FROM MRCDMS..DMSKPIIncentiveDetails inc
1013 INNER JOIN MRCDMS..DMSKPIListSales list
1014 ON list.CompanyID = inc.CompanyID
1015 AND list.CodeListSalesID = inc.CodeListSalesID
1016 GROUP BY inc.CompanyID,
1017 inc.CodeListSalesID) tmpMax
1018 ON tmpMax.CompanyID = incentive.CompanyID
1019 AND tmpMax.CodeListSalesID = incentive.CodeListSalesID
1020 AND tmpMax.MaxTo = incentive.[To]
1021 WHERE ( ( ( (NULLIF(detail.ValuesBaseline, 0)
1022 / NULLIF(CASE
1023 WHEN detail.TargetsAdjust IS NULL THEN detail.TargetsAssigned
1024 ELSE detail.TargetsAdjust END, 0)) * 100 >= incentive.[From]
1025 AND (NULLIF(detail.ValuesBaseline, 0)
1026 / NULLIF(CASE
1027 WHEN detail.TargetsAdjust IS NULL THEN detail.TargetsAssigned
1028 ELSE detail.TargetsAdjust END, 0)) * 100 < incentive.[To])
1029 OR ((detail.ValuesBaseline
1030 / NULLIF(CASE
1031 WHEN detail.TargetsAdjust IS NULL THEN detail.TargetsAssigned
1032 ELSE detail.TargetsAdjust END, 0)) * 100 >= tmpMax.MaxTo))
1033 AND kpi.AccumulateBy = 'P')
1034 OR ( ( ( detail.ValuesBaseline >= incentive.[From]
1035 AND detail.ValuesBaseline < incentive.[To])
1036 OR (detail.ValuesBaseline >= tmpMax.MaxTo))
1037 AND kpi.AccumulateBy = 'V')
1038 AND header.Status = 'R'
1039 AND cat.Sales = CAST(1 AS BIT)
1040 AND detail.CompanyID = @_CompanyID
1041 UNION ALL
1042 SELECT detail.CompanyID,
1043 header.RefNbr,
1044 incentive.CodeListSalesID,
1045 detail.ThisObject,
1046 Incentive = incentive.Incentive
1047 + CASE
1048 WHEN incentive.Prorate = CAST(1 AS BIT)
1049 AND kpi.AccumulateBy = 'P' THEN
1050 ((((ISNULL(detail.ValuesBaseline, 0)
1051 / ISNULL(CASE
1052 WHEN detail.TargetsAdjust IS NULL THEN detail.TargetsAssigned
1053 ELSE detail.TargetsAdjust END,
1054 0)) * 100) - incentive.[From]) * (level.Incentive - incentive.Incentive))
1055 / (incentive.[To] - incentive.[From])
1056 ELSE 0 END
1057 FROM MRCDMS..DMSKPISalesPeriodConfigurationDetail detail
1058 INNER JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader header
1059 ON header.CompanyID = detail.CompanyID
1060 AND header.RefNbr = detail.RefNbr
1061 INNER JOIN MRCDMS..DMSKPICategory cat
1062 ON cat.CompanyID = header.CompanyID
1063 AND cat.CategoryID = header.KPICategoryID
1064 INNER JOIN MRCDMS..DMSKPIIncentiveDetails incentive
1065 ON incentive.CompanyID = header.CompanyID
1066 AND incentive.CodeListSalesID = header.CodeListSalesID
1067 AND incentive.Incentive > 0 -- l2 them
1068 LEFT JOIN MRCDMS..DMSKPIIncentiveDetails level
1069 ON level.CompanyID = incentive.CompanyID
1070 AND level.CodeListSalesID = incentive.CodeListSalesID
1071 AND level.[From] = incentive.[To]
1072 INNER JOIN MRCDMS..DMSKPIListSales kpi
1073 ON kpi.CompanyID = incentive.CompanyID
1074 AND kpi.CodeListSalesID = incentive.CodeListSalesID
1075 LEFT JOIN ( SELECT inc.CompanyID,
1076 inc.CodeListSalesID,
1077 MaxTo = MAX(inc.[To])
1078 FROM MRCDMS..DMSKPIIncentiveDetails inc
1079 INNER JOIN MRCDMS..DMSKPIListSales list
1080 ON list.CompanyID = inc.CompanyID
1081 AND list.CodeListSalesID = inc.CodeListSalesID
1082 GROUP BY inc.CompanyID,
1083 inc.CodeListSalesID) tmpMax
1084 ON tmpMax.CompanyID = incentive.CompanyID
1085 AND tmpMax.CodeListSalesID = incentive.CodeListSalesID
1086 AND tmpMax.MaxTo = incentive.[To]
1087 WHERE ( ( ( (NULLIF(detail.ValuesBaseline, 0)
1088 / NULLIF(CASE
1089 WHEN detail.TargetsAdjust IS NULL THEN detail.TargetsAssigned
1090 ELSE detail.TargetsAdjust END, 0)) * 100 >= incentive.[From]
1091 AND (NULLIF(detail.ValuesBaseline, 0)
1092 / NULLIF(CASE
1093 WHEN detail.TargetsAdjust IS NULL THEN detail.TargetsAssigned
1094 ELSE detail.TargetsAdjust END, 0)) * 100 < incentive.[To])
1095 OR ((NULLIF(detail.ValuesBaseline, 0)
1096 / NULLIF(CASE
1097 WHEN detail.TargetsAdjust IS NULL THEN detail.TargetsAssigned
1098 ELSE detail.TargetsAdjust END, 0)) * 100 >= tmpMax.MaxTo))
1099 AND kpi.AccumulateBy = 'P')
1100 OR ( ( ( detail.ValuesBaseline >= incentive.[From]
1101 AND detail.ValuesBaseline < incentive.[To])
1102 OR (detail.ValuesBaseline >= tmpMax.MaxTo))
1103 AND kpi.AccumulateBy = 'V')
1104 AND header.Status = 'R'
1105 AND cat.Distributor = CAST(1 AS BIT)
1106 AND detail.CompanyID = @_CompanyID) tmp;
1107 --SELECT
1108 -- *
1109 --FROM
1110 -- #tmpResult
1111 --WHERE
1112 -- Actual > 0 OR Target > 0
1113
1114 SELECT rs.CompanyID,
1115 KPIID,
1116 KPICD,
1117 KPIDescr,
1118 rs.ObjectID,
1119 FromDate,
1120 ToDate,
1121 KPIPeriodNbr,
1122 SalesSupCD,
1123 SalesSupName,
1124 ObjectCD,
1125 ObjectName,
1126 SFHierachyName,
1127 ObjectType,
1128 ObjectDetailType,
1129 ObjectAssignment,
1130 Target,
1131 CorrectRouteCorrectCoordinate,
1132 CorrectRouteIncorrectCoordinate,
1133 IncorrectRoute,
1134 DistributorSell,
1135 Actual = Actual,
1136 Incentive,
1137 BLSalesTerritoryID,
1138 TerritoryType,
1139 SalesOrgValueID,
1140 DistributorID,
1141 DistributorLocationID,
1142 DistributorCD,
1143 DistributorName,
1144 Route,
1145 District,
1146 Channel,
1147 SalesOrg_0_ValueName,
1148 SalesOrg_1_ValueName,
1149 SalesOrg_2_ValueName,
1150 SalesOrg_3_ValueName,
1151 SalesOrg_4_ValueName,
1152 SalesOrg_5_ValueName,
1153 SalesOrg_6_ValueName,
1154 SalesOrg_7_ValueName,
1155 SalesOrg_8_ValueName,
1156 SalesOrg_9_ValueName,
1157 SalesForce_0_Name,
1158 SalesForce_1_Name,
1159 SalesForce_2_Name,
1160 SalesForce_3_Name,
1161 SalesForce_4_Name,
1162 SalesForce_5_Name,
1163 SalesForce_6_Name,
1164 SalesForce_7_Name,
1165 SalesForce_8_Name,
1166 SalesForce_9_Name,
1167 ManDays = CASE
1168 WHEN rs.ManDays IS NULL THEN 0
1169 ELSE rs.ManDays END
1170 INTO #tmpTong
1171 FROM #tmpResult rs
1172 LEFT JOIN #tmpIncentive inc
1173 ON inc.CompanyID = rs.CompanyID
1174 AND rs.KPIID = inc.CodeListSalesID
1175 AND rs.ObjectID = inc.ThisObject
1176 AND rs.RefNbr = inc.RefNbr;
1177
1178 UPDATE r
1179 SET r.ManDays = md.Mandays
1180 FROM #tmpTong r
1181 JOIN #tmpFinalMandays md
1182 ON md.CompanyID = r.CompanyID
1183 AND md.EmployeeCD = r.ObjectCD;
1184
1185 --SELECT t.CompanyID ,
1186 -- t.KPIID ,
1187 -- t.KPICD ,
1188 -- t.KPIDescr ,
1189 -- t.ObjectID ,
1190 -- t.FromDate ,
1191 -- t.ToDate ,
1192 -- t.KPIPeriodNbr ,
1193 -- t.SalesSupCD ,
1194 -- t.SalesSupName ,
1195 -- t.ObjectCD ,
1196 -- t.ObjectName ,
1197 -- t.SFHierachyName ,
1198 -- t.ObjectType ,
1199 -- t.ObjectDetailType ,
1200 -- t.ObjectAssignment ,
1201 -- t.Target ,
1202 -- t.CorrectRouteCorrectCoordinate ,
1203 -- t.CorrectRouteIncorrectCoordinate ,
1204 -- t.IncorrectRoute ,
1205 -- t.DistributorSell ,
1206 -- t.Actual ,
1207 -- Incentive = t.Incent ,
1208 -- t.BLSalesTerritoryID ,
1209 -- t.TerritoryType ,
1210 -- t.SalesOrgValueID ,
1211 -- t.DistributorID ,
1212 -- t.DistributorLocationID ,
1213 -- t.DistributorCD ,
1214 -- t.DistributorName ,
1215 -- t.Route ,
1216 -- t.District ,
1217 -- t.Channel ,
1218 -- t.SalesOrg_0_ValueName ,
1219 -- t.SalesOrg_1_ValueName ,
1220 -- t.SalesOrg_2_ValueName ,
1221 -- t.SalesOrg_3_ValueName ,
1222 -- t.SalesOrg_4_ValueName ,
1223 -- t.SalesOrg_5_ValueName ,
1224 -- t.SalesOrg_6_ValueName ,
1225 -- t.SalesOrg_7_ValueName ,
1226 -- t.SalesOrg_8_ValueName ,
1227 -- t.SalesOrg_9_ValueName ,
1228 -- t.SalesForce_0_Name ,
1229 -- t.SalesForce_1_Name ,
1230 -- t.SalesForce_2_Name ,
1231 -- t.SalesForce_3_Name ,
1232 -- t.SalesForce_4_Name ,
1233 -- t.SalesForce_5_Name ,
1234 -- t.SalesForce_6_Name ,
1235 -- t.SalesForce_7_Name ,
1236 -- t.SalesForce_8_Name ,
1237 -- t.SalesForce_9_Name ,
1238 -- t.ManDays
1239 --FROM #tmpResult t
1240 -- LEFT JOIN #tmpTong tong ON tong.CompanyID = t.CompanyID
1241 -- AND tong.KPIID = t.KPIID
1242 -- AND tong.ObjectID = t.ObjectID
1243 --WHERE tong.Incentive IS NULL
1244 --UNION ALL
1245 SELECT *
1246 FROM #tmpTong
1247 UNION ALL
1248 SELECT tempTotal.CompanyID,
1249 KPIID = NULL,
1250 KPICD = 'TONG',
1251 KPIDescr = 'TONG',
1252 tempTotal.ObjectID,
1253 FromDate = NULL,
1254 ToDate = NULL,
1255 KPIPeriodNbr = NULL,
1256 tempTotal.SalesSupCD,
1257 tempTotal.SalesSupName,
1258 tempTotal.ObjectCD,
1259 tempTotal.ObjectName,
1260 tempTotal.SFHierachyName,
1261 tempTotal.ObjectType,
1262 tempTotal.ObjectDetailType,
1263 tempTotal.ObjectAssignment,
1264 Target = NULL,
1265 CorrectRouteCorrectCoordinate = NULL,
1266 CorrectRouteIncorrectCoordinate = NULL,
1267 IncorrectRoute = NULL,
1268 DistributorSell = NULL,
1269 Actual = NULL,
1270 SUM(Incentive) AS Incentive,
1271 BLSalesTerritoryID = NULL,
1272 tempTotal.TerritoryType,
1273 SalesOrgValueID = NULL,
1274 tempTotal.DistributorID,
1275 tempTotal.DistributorLocationID,
1276 tempTotal.DistributorCD,
1277 tempTotal.DistributorName,
1278 Route = NULL,
1279 District = NULL,
1280 tempTotal.Channel,
1281 tempTotal.SalesOrg_0_ValueName,
1282 tempTotal.SalesOrg_1_ValueName,
1283 tempTotal.SalesOrg_2_ValueName,
1284 tempTotal.SalesOrg_3_ValueName,
1285 SalesOrg_4_ValueName = NULL,
1286 SalesOrg_5_ValueName = NULL,
1287 SalesOrg_6_ValueName = NULL,
1288 SalesOrg_7_ValueName = NULL,
1289 SalesOrg_8_ValueName = NULL,
1290 SalesOrg_9_ValueName = NULL,
1291 SalesForce_0_Name = NULL,
1292 SalesForce_1_Name = NULL,
1293 SalesForce_2_Name = NULL,
1294 SalesForce_3_Name = NULL,
1295 SalesForce_4_Name = NULL,
1296 SalesForce_5_Name = NULL,
1297 SalesForce_6_Name = NULL,
1298 SalesForce_7_Name = NULL,
1299 SalesForce_8_Name = NULL,
1300 SalesForce_9_Name = NULL,
1301 ManDays = CASE WHEN tempTotal.ManDays IS NULL THEN 0
1302 ELSE tempTotal.ManDays END
1303
1304
1305 FROM #tmpTong tempTotal
1306 WHERE tempTotal.Target > 0
1307 OR tempTotal.Actual > 0
1308 GROUP BY tempTotal.CompanyID,
1309 tempTotal.SalesSupCD,
1310 tempTotal.SalesSupName,
1311 tempTotal.ObjectID,
1312 tempTotal.ObjectCD,
1313 tempTotal.ObjectName,
1314 tempTotal.SFHierachyName,
1315 tempTotal.ObjectType,
1316 tempTotal.ObjectDetailType,
1317 tempTotal.ObjectAssignment,
1318 tempTotal.TerritoryType,
1319 tempTotal.DistributorID,
1320 tempTotal.DistributorLocationID,
1321 tempTotal.DistributorCD,
1322 tempTotal.DistributorName,
1323 tempTotal.Channel,
1324 tempTotal.SalesOrg_0_ValueName,
1325 tempTotal.SalesOrg_1_ValueName,
1326 tempTotal.SalesOrg_2_ValueName,
1327 tempTotal.SalesOrg_3_ValueName,
1328 tempTotal.ManDays;
1329END;
1330GO