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