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