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