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