· 6 years ago · Jul 25, 2019, 03:20 AM
1USE [MRCBaseline]
2GO
3/****** Object: StoredProcedure [dbo].[sp_DMS_Baseline_KPI] Script Date: 7/25/2019 10:16:54 AM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9-- =============================================
10-- Author Creator: Dinh Hoang Lam
11-- Create date: 2015/04/02
12-- Description:
13-- =============================================
14
15-- =============================================
16-- Author Update: Dang.Huynh
17-- Create date: 2017-10-31
18-- Description:
19-- + Cập nhật cách lấy số liệu để tính toán từ Raw sales theo tham số tối thiểu về số lượng (MinVolume), sản lượng (MinRevenue)
20-- + Cập nhật cách lấy số liệu để tính toán từ Raw sales bao gồm đơn trả hàng
21-- + Cập nhật cách tính bình % đơn hàng (AVGPC) theo doanh số( Revnue) hoặc sản lượng ( Volume) theo lịch viếng thăm actual hoặc lịch MCP
22-- + Cập nhật cách tính bình quân số lượng đơn hàng (DROPSIZE) theo doanh số( Revnue) hoặc sản lượng ( Volume)
23-- + Cập nhật cách tính KPIs mở mới outlet
24-- =============================================
25
26-- EXEC sp_DMS_Baseline_KPI 3, '2019-04-30'
27ALTER PROCEDURE [dbo].[sp_DMS_Baseline_KPI]
28 @CompanyID INT,
29 @Date DATETIME
30AS
31BEGIN
32 SET NOCOUNT ON;
33
34 DECLARE @_CompanyID INT = @CompanyID;
35 DECLARE @_Date DATETIME = @Date;
36 DECLARE @_FirstDayOfWeek DATETIME;
37 DECLARE @_LastDayOfWeek DATETIME;
38 DECLARE @_FirstDayOfMonth DATETIME
39 = CONVERT(VARCHAR(4), YEAR(@_Date)) + '/' + CONVERT(VARCHAR(2), MONTH(@_Date)) + '/1';
40 DECLARE @_LastDayOfMonth DATETIME = DATEADD(dd, -1, DATEADD(mm, 1, @_FirstDayOfMonth));
41 DECLARE @_Week CHAR(2);
42 DECLARE @_Month CHAR(2) = RIGHT('0' + CONVERT(VARCHAR(2), MONTH(@_Date)), 2);
43 DECLARE @_Year CHAR(4) = CONVERT(CHAR(4), YEAR(@_Date));
44 DECLARE @_FromDate DATETIME;
45 DECLARE @_ToDate DATETIME;
46
47 BEGIN -- Khai báo bảng tạm
48 CREATE TABLE #tmpKPI
49 (
50 RowNumber INT,
51 CompanyID INT,
52 CodeListSalesID INT,
53 CodeListSalesCD VARCHAR(30),
54 Descr NVARCHAR(100),
55 Source CHAR(2),
56 Type CHAR(1),
57 GroupBy CHAR(1),
58 GroupID VARCHAR(30),
59 Formula CHAR(1),
60 Template VARCHAR(15),
61 KPIPeriodNbr VARCHAR(30),
62 RefNbr VARCHAR(30),
63 PERIOD CHAR(1),
64 FromDate DATETIME,
65 ToDate DATETIME,
66 ObjectID INT,
67 TempObjectID INT,
68 ObjectType CHAR(1),
69 ObjectAssignment VARCHAR(50),
70 TempTargetsSuggest DECIMAL(18, 2),
71 TempTargetsAssigned DECIMAL(18, 2),
72 TargetsSuggest DECIMAL(18, 2),
73 TargetsAssigned DECIMAL(18, 2),
74 Actual1 DECIMAL(18, 2),
75 Actual2 DECIMAL(18, 2),
76 Actual3 DECIMAL(18, 2),
77 Actual4 DECIMAL(18, 2),
78 Actual DECIMAL(18, 2)
79 );
80
81 CREATE TABLE #tmpSalesTerritory
82 (
83 RowNumber INT,
84 CompanyID INT,
85 SalesOrg_0_ValueID INT,
86 SalesOrg_1_ValueID INT,
87 SalesOrg_2_ValueID INT,
88 SalesOrg_3_ValueID INT,
89 SalesOrg_4_ValueID INT,
90 SalesOrg_5_ValueID INT,
91 SalesOrg_6_ValueID INT,
92 SalesOrg_7_ValueID INT,
93 SalesOrg_8_ValueID INT,
94 SalesOrg_9_ValueID INT
95 );
96
97 CREATE TABLE #tmpRoute
98 (
99 CompanyID INT,
100 SalespersonID INT,
101 Route VARCHAR(30),
102 SalesForceID INT,
103 SalesAreaID INT,
104 EffectiveDate DATETIME,
105 ExpirationDate DATETIME,
106 SalesOrg_0_ValueID INT,
107 SalesOrg_1_ValueID INT,
108 SalesOrg_2_ValueID INT,
109 SalesOrg_3_ValueID INT,
110 SalesOrg_4_ValueID INT,
111 SalesOrg_5_ValueID INT,
112 SalesOrg_6_ValueID INT,
113 SalesOrg_7_ValueID INT,
114 SalesOrg_8_ValueID INT,
115 SalesOrg_9_ValueID INT
116 );
117
118 CREATE TABLE #tmpSalesForce
119 (
120 CompanyID INT,
121 EmployeeID INT,
122 SFHierachyID INT,
123 TerritoryType CHAR(1),
124 SalesOrgValueID INT,
125 SalesOrg_0_ValueID INT,
126 SalesOrg_1_ValueID INT,
127 SalesOrg_2_ValueID INT,
128 SalesOrg_3_ValueID INT,
129 SalesOrg_4_ValueID INT,
130 SalesOrg_5_ValueID INT,
131 SalesOrg_6_ValueID INT,
132 SalesOrg_7_ValueID INT,
133 SalesOrg_8_ValueID INT,
134 SalesOrg_9_ValueID INT
135 );
136
137 CREATE TABLE #tmpTotalPC
138 (
139 CompanyID INT,
140 ObjectID INT,
141 ObjectAssignment VARCHAR(50),
142 Actual1 DECIMAL(18, 2),
143 Actual2 DECIMAL(18, 2),
144 Actual3 DECIMAL(18, 2),
145 Actual4 DECIMAL(18, 2),
146 Actual DECIMAL(18, 2)
147 );
148
149 CREATE TABLE #tmpTotalLine
150 (
151 CompanyID INT,
152 ObjectID INT,
153 ObjectAssignment VARCHAR(50),
154 Actual1 DECIMAL(18, 2),
155 Actual2 DECIMAL(18, 2),
156 Actual3 DECIMAL(18, 2),
157 Actual4 DECIMAL(18, 2),
158 Actual DECIMAL(18, 2)
159 );
160
161 CREATE TABLE #tmpCM
162 (
163 OrigOrderNbr NVARCHAR(50),
164 OrigOrderType NVARCHAR(5)
165 );
166
167 END; -- Khai báo bảng tạm
168
169 BEGIN -- Xóa dữ liệu KPI cũ
170 DELETE FROM dbo.DMSBLKPI
171 WHERE CompanyID = @_CompanyID
172 AND @_Date
173 BETWEEN FromDate AND ToDate;
174 END; -- Xóa dữ liệu KPI cũ
175 ---- Lấy tuần trong năm
176 SELECT @_Week = Week,
177 @_FirstDayOfWeek = StartDate,
178 @_LastDayOfWeek = EndDate
179 FROM dbo.DMSBLSalesCalendarWeek WITH (NOLOCK)
180 WHERE CompanyID = @_CompanyID
181 AND @_Date
182 BETWEEN StartDate AND EndDate;
183
184 ---- Lấy KPI tháng có kỳ đánh giá hiệu lực trong khoảng thời gian baseline
185 SELECT kpi.CompanyID,
186 kpi.CodeListSalesID,
187 kpi.CodeListSalesCD,
188 kpi.Descr,
189 kpi.[Source],
190 kpi.Type,
191 kpi.GroupBy,
192 kpi.GroupID,
193 kpi.Formula,
194 Template = kpi.Template,
195 periodHeader.KPIPeriodNbr,
196 periodHeader.Period,
197 sov.SalesOrgID,
198 SalesAreaID = sov.ValueID,
199 sov.Level,
200 kpi.CorrectRouteAndCorrectCoordinates,
201 kpi.CorrectRouteAndIncorrectCoordinates,
202 kpi.IncorrectRoute,
203 kpi.DistributorsSell,
204 kpi.MinSKUVolume,
205 kpi.MinSKURevenue,
206 kpi.ExcludeReturnOrder,
207 periodConfigHeader.RefNbr,
208 FromDate = @_FirstDayOfMonth,
209 ToDate = @_LastDayOfMonth,
210 ObjectID = periodConfigDetail.ThisObject,
211 ObjectType = CASE
212 WHEN cate.Distributor = 1 THEN
213 'D'
214 WHEN cate.Sales = 1 THEN
215 'S'
216 END,
217 TargetsSuggest = periodConfigDetail.TargetsSuggest,
218 TargetsAssigned = COALESCE(periodConfigDetail.TargetsAdjust, periodConfigDetail.TargetsAssigned)
219 INTO #tmpKPIDetail
220 FROM MRCDMS..DMSKPIListSales kpi WITH (NOLOCK)
221 JOIN MRCDMS..DMSKPISalesPeriodDetail periodDetail WITH (NOLOCK)
222 ON periodDetail.CompanyID = kpi.CompanyID
223 AND periodDetail.CodeListSalesID = kpi.CodeListSalesID
224 JOIN MRCDMS..DMSKPISalesPeriodHeader periodHeader WITH (NOLOCK)
225 ON periodHeader.CompanyID = periodDetail.CompanyID
226 AND periodHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
227 JOIN MRCDMS..DMSKPICategory cate WITH (NOLOCK)
228 ON cate.CompanyID = periodHeader.CompanyID
229 AND cate.CategoryID = periodHeader.KPICategoryID
230 LEFT JOIN MRCDMS..DMSSalesOrgValue sov WITH (NOLOCK)
231 ON sov.CompanyID = periodHeader.CompanyID
232 AND sov.ValueID = periodHeader.SalesArea
233 LEFT JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader periodConfigHeader WITH (NOLOCK)
234 ON periodConfigHeader.CompanyID = periodDetail.CompanyID
235 AND periodConfigHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
236 --AND periodConfigHeader.CodeListSalesID = periodDetail.CodeListSalesID
237 AND periodConfigHeader.Year = @_Year
238 AND periodConfigHeader.Month = @_Month
239 JOIN MRCDMS..DMSKPISalesPeriodConfigurationDetail periodConfigDetail WITH (NOLOCK)
240 ON periodConfigDetail.CompanyID = periodConfigHeader.CompanyID
241 AND periodConfigDetail.RefNbr = periodConfigHeader.RefNbr
242 AND periodConfigDetail.CodeListSalesID = periodDetail.CodeListSalesID
243 WHERE kpi.CompanyID = @_CompanyID
244 AND kpi.Active = 1
245 AND periodHeader.Release = 1
246 AND periodHeader.Period = 'M'
247 AND periodConfigHeader.Status = 'R';
248
249 ---- Lấy KPI tuần có kỳ đánh giá hiệu lực trong khoảng thời gian baseline
250 INSERT INTO #tmpKPIDetail
251 SELECT kpi.CompanyID,
252 kpi.CodeListSalesID,
253 kpi.CodeListSalesCD,
254 kpi.Descr,
255 kpi.[Source],
256 kpi.Type,
257 kpi.GroupBy,
258 kpi.GroupID,
259 kpi.Formula,
260 Template = kpi.Template,
261 periodHeader.KPIPeriodNbr,
262 periodHeader.Period,
263 sov.SalesOrgID,
264 SalesAreaID = sov.ValueID,
265 sov.Level,
266 kpi.CorrectRouteAndCorrectCoordinates,
267 kpi.CorrectRouteAndIncorrectCoordinates,
268 kpi.IncorrectRoute,
269 kpi.ExcludeReturnOrder,
270 kpi.MinSKUVolume,
271 kpi.MinSKURevenue,
272 kpi.DistributorsSell,
273 periodConfigHeader.RefNbr,
274 FromDate = @_FirstDayOfWeek,
275 ToDate = @_LastDayOfWeek,
276 ObjectID = periodConfigDetail.ThisObject,
277 ObjectType = CASE
278 WHEN cate.Distributor = 1 THEN
279 'D'
280 WHEN cate.Sales = 1 THEN
281 'S'
282 END,
283 TargetsSuggest = periodConfigDetail.TargetsSuggest,
284 TargetsAssigned = COALESCE(periodConfigDetail.TargetsAdjust, periodConfigDetail.TargetsAssigned)
285 FROM MRCDMS..DMSKPIListSales kpi WITH (NOLOCK)
286 JOIN MRCDMS..DMSKPISalesPeriodDetail periodDetail WITH (NOLOCK)
287 ON periodDetail.CompanyID = kpi.CompanyID
288 AND periodDetail.CodeListSalesID = kpi.CodeListSalesID
289 JOIN MRCDMS..DMSKPISalesPeriodHeader periodHeader WITH (NOLOCK)
290 ON periodHeader.CompanyID = periodDetail.CompanyID
291 AND periodHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
292 JOIN MRCDMS..DMSKPICategory cate WITH (NOLOCK)
293 ON cate.CompanyID = periodHeader.CompanyID
294 AND cate.CategoryID = periodHeader.KPICategoryID
295 LEFT JOIN MRCDMS..DMSSalesOrgValue sov WITH (NOLOCK)
296 ON sov.CompanyID = periodHeader.CompanyID
297 AND sov.ValueID = periodHeader.SalesArea
298 LEFT JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader periodConfigHeader WITH (NOLOCK)
299 ON periodConfigHeader.CompanyID = periodHeader.CompanyID
300 AND periodConfigHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
301 --AND periodConfigHeader.CodeListSalesID = periodDetail.CodeListSalesID
302 AND periodConfigHeader.Year = @_Year
303 AND periodConfigHeader.Week = @_Week
304 JOIN MRCDMS..DMSKPISalesPeriodConfigurationDetail periodConfigDetail WITH (NOLOCK)
305 ON periodConfigDetail.CompanyID = periodConfigHeader.CompanyID
306 AND periodConfigDetail.RefNbr = periodConfigHeader.RefNbr
307 AND periodConfigDetail.CodeListSalesID = periodDetail.CodeListSalesID
308 WHERE kpi.CompanyID = @_CompanyID
309 AND kpi.Active = 1
310 AND periodHeader.Release = 1
311 AND periodHeader.Period = 'W'
312 AND periodConfigHeader.Status = 'R';
313
314 ---- Lấy KPI ngày có kỳ đánh giá hiệu lực trong khoảng thời gian baseline
315 INSERT INTO #tmpKPIDetail
316 SELECT kpi.CompanyID,
317 kpi.CodeListSalesID,
318 kpi.CodeListSalesCD,
319 kpi.Descr,
320 kpi.[Source],
321 kpi.Type,
322 kpi.GroupBy,
323 kpi.GroupID,
324 kpi.Formula,
325 Template = kpi.Template,
326 periodHeader.KPIPeriodNbr,
327 periodHeader.Period,
328 sov.SalesOrgID,
329 SalesAreaID = sov.ValueID,
330 sov.Level,
331 kpi.CorrectRouteAndCorrectCoordinates,
332 kpi.CorrectRouteAndIncorrectCoordinates,
333 kpi.IncorrectRoute,
334 kpi.DistributorsSell,
335 kpi.MinSKUVolume,
336 kpi.MinSKURevenue,
337 kpi.ExcludeReturnOrder,
338 periodConfigHeader.RefNbr,
339 FromDate = periodConfigHeader.FromDate,
340 ToDate = periodConfigHeader.ToDate,
341 ObjectID = periodConfigDetail.ThisObject,
342 ObjectType = CASE
343 WHEN cate.Distributor = 1 THEN
344 'D'
345 WHEN cate.Sales = 1 THEN
346 'S'
347 END,
348 TargetsSuggest = periodConfigDetail.TargetsSuggest,
349 TargetsAssigned = COALESCE(periodConfigDetail.TargetsAdjust, periodConfigDetail.TargetsAssigned)
350 FROM MRCDMS..DMSKPIListSales kpi WITH (NOLOCK)
351 JOIN MRCDMS..DMSKPISalesPeriodDetail periodDetail WITH (NOLOCK)
352 ON periodDetail.CompanyID = kpi.CompanyID
353 AND periodDetail.CodeListSalesID = kpi.CodeListSalesID
354 JOIN MRCDMS..DMSKPISalesPeriodHeader periodHeader WITH (NOLOCK)
355 ON periodHeader.CompanyID = periodDetail.CompanyID
356 AND periodHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
357 JOIN MRCDMS..DMSKPICategory cate WITH (NOLOCK)
358 ON cate.CompanyID = periodHeader.CompanyID
359 AND cate.CategoryID = periodHeader.KPICategoryID
360 LEFT JOIN MRCDMS..DMSSalesOrgValue sov WITH (NOLOCK)
361 ON sov.CompanyID = periodHeader.CompanyID
362 AND sov.ValueID = periodHeader.SalesArea
363 JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader periodConfigHeader WITH (NOLOCK)
364 ON periodConfigHeader.CompanyID = periodDetail.CompanyID
365 AND periodConfigHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
366 --AND periodConfigHeader.CodeListSalesID = periodDetail.CodeListSalesID
367 JOIN MRCDMS..DMSKPISalesPeriodConfigurationDetail periodConfigDetail WITH (NOLOCK)
368 ON periodConfigDetail.CompanyID = periodConfigHeader.CompanyID
369 AND periodConfigDetail.RefNbr = periodConfigHeader.RefNbr
370 AND periodConfigDetail.CodeListSalesID = periodDetail.CodeListSalesID
371 WHERE kpi.CompanyID = @_CompanyID
372 AND kpi.Active = 1
373 AND periodHeader.Release = 1
374 AND periodHeader.Period = 'D'
375 AND @_Date
376 BETWEEN periodConfigHeader.FromDate AND periodConfigHeader.ToDate
377 AND periodConfigHeader.Status = 'R';
378
379 ---- Xóa các KPI chưa có kì DK
380 DELETE #tmpKPIDetail
381 WHERE RefNbr IS NULL;
382 --DELETE #tmpKPIDetail WHERE RefNbr not in ('00000936','00000937','00000938','00000939')--1
383 ---- Lấy khoảng thời gian tính KPI lớn nhất
384 SELECT @_FromDate = MIN(FromDate),
385 @_ToDate = MAX(ToDate)
386 FROM #tmpKPIDetail;
387
388 ---- Lấy danh sách NPP
389 SELECT DISTINCT
390 bldl.CompanyID,
391 bldl.ID,
392 bldl.DistributorID,
393 DistributorLocationID = bldl.LocationID,
394 blst.SalesOrg_0_ValueID,
395 blst.SalesOrg_1_ValueID,
396 blst.SalesOrg_2_ValueID,
397 blst.SalesOrg_3_ValueID,
398 blst.SalesOrg_4_ValueID,
399 blst.SalesOrg_5_ValueID,
400 blst.SalesOrg_6_ValueID,
401 blst.SalesOrg_7_ValueID,
402 blst.SalesOrg_8_ValueID,
403 blst.SalesOrg_9_ValueID,
404 SalesForce_0_ID = blsf0.EmployeeID,
405 SalesForce_1_ID = blsf1.EmployeeID,
406 SalesForce_2_ID = blsf2.EmployeeID,
407 SalesForce_3_ID = blsf3.EmployeeID,
408 SalesForce_4_ID = blsf4.EmployeeID,
409 SalesForce_5_ID = blsf5.EmployeeID,
410 SalesForce_6_ID = blsf6.EmployeeID,
411 SalesForce_7_ID = blsf7.EmployeeID,
412 SalesForce_8_ID = blsf8.EmployeeID,
413 SalesForce_9_ID = blsf9.EmployeeID,
414 bldl.ExpirationDate
415 INTO #tmpDistributorLocation
416 FROM dbo.DMSBLDistributorLocation bldl WITH (NOLOCK)
417 LEFT JOIN dbo.DMSBLSalesTerritory blst WITH (NOLOCK)
418 ON blst.CompanyID = bldl.CompanyID
419 AND blst.ID = bldl.BLSalesTerritoryID
420 LEFT JOIN dbo.DMSBLSalesForce blsf0 WITH (NOLOCK)
421 ON blsf0.CompanyID = blst.CompanyID
422 AND blsf0.SalesOrgValueID = blst.SalesOrg_0_ValueID
423 AND blsf0.ExpirationDate IS NULL
424 LEFT JOIN dbo.DMSBLSalesForce blsf1 WITH (NOLOCK)
425 ON blsf1.CompanyID = blst.CompanyID
426 AND blsf1.SalesOrgValueID = blst.SalesOrg_1_ValueID
427 AND blsf1.ExpirationDate IS NULL
428 LEFT JOIN dbo.DMSBLSalesForce blsf2 WITH (NOLOCK)
429 ON blsf2.CompanyID = blst.CompanyID
430 AND blsf2.SalesOrgValueID = blst.SalesOrg_2_ValueID
431 AND blsf2.ExpirationDate IS NULL
432 LEFT JOIN dbo.DMSBLSalesForce blsf3 WITH (NOLOCK)
433 ON blsf3.CompanyID = blst.CompanyID
434 AND blsf3.SalesOrgValueID = blst.SalesOrg_3_ValueID
435 AND blsf3.ExpirationDate IS NULL
436 LEFT JOIN dbo.DMSBLSalesForce blsf4 WITH (NOLOCK)
437 ON blsf4.CompanyID = blst.CompanyID
438 AND blsf4.SalesOrgValueID = blst.SalesOrg_4_ValueID
439 AND blsf4.ExpirationDate IS NULL
440 LEFT JOIN dbo.DMSBLSalesForce blsf5 WITH (NOLOCK)
441 ON blsf5.CompanyID = blst.CompanyID
442 AND blsf5.SalesOrgValueID = blst.SalesOrg_5_ValueID
443 AND blsf5.ExpirationDate IS NULL
444 LEFT JOIN dbo.DMSBLSalesForce blsf6 WITH (NOLOCK)
445 ON blsf6.CompanyID = blst.CompanyID
446 AND blsf6.SalesOrgValueID = blst.SalesOrg_6_ValueID
447 AND blsf6.ExpirationDate IS NULL
448 LEFT JOIN dbo.DMSBLSalesForce blsf7 WITH (NOLOCK)
449 ON blsf7.CompanyID = blst.CompanyID
450 AND blsf7.SalesOrgValueID = blst.SalesOrg_7_ValueID
451 AND blsf7.ExpirationDate IS NULL
452 LEFT JOIN dbo.DMSBLSalesForce blsf8 WITH (NOLOCK)
453 ON blsf8.CompanyID = blst.CompanyID
454 AND blsf8.SalesOrgValueID = blst.SalesOrg_8_ValueID
455 AND blsf8.ExpirationDate IS NULL
456 LEFT JOIN dbo.DMSBLSalesForce blsf9 WITH (NOLOCK)
457 ON blsf9.CompanyID = blst.CompanyID
458 AND blsf9.SalesOrgValueID = blst.SalesOrg_9_ValueID
459 AND blsf9.ExpirationDate IS NULL
460 WHERE bldl.CompanyID = @_CompanyID;
461
462 ---- Lấy dữ liệu RawData SalesOut để tính KPI
463 SELECT tmp.*,
464 dl.DistributorLocationID,
465 dl.SalesOrg_0_ValueID,
466 dl.SalesOrg_1_ValueID,
467 dl.SalesOrg_2_ValueID,
468 dl.SalesOrg_3_ValueID,
469 dl.SalesOrg_4_ValueID,
470 dl.SalesOrg_5_ValueID,
471 dl.SalesOrg_6_ValueID,
472 dl.SalesOrg_7_ValueID,
473 dl.SalesOrg_8_ValueID,
474 dl.SalesOrg_9_ValueID,
475 dl.SalesForce_0_ID,
476 dl.SalesForce_1_ID,
477 dl.SalesForce_2_ID,
478 dl.SalesForce_3_ID,
479 dl.SalesForce_4_ID,
480 dl.SalesForce_5_ID,
481 dl.SalesForce_6_ID,
482 dl.SalesForce_7_ID,
483 dl.SalesForce_8_ID,
484 dl.SalesForce_9_ID
485 INTO #tmpSORawSales
486 FROM
487 (
488 SELECT blrs.CompanyID,
489 blrs.BLDistributorLocationID,
490 blrs.SalespersonID,
491 blrs.OrderType,
492 blrs.OrderNbr,
493 blrs.OrderDate,
494 blrs.SourceType,
495 blrs.Route,
496 blrs.CustomerID,
497 blrs.CustomerLocationID,
498 blrs.InventoryID,
499 TranAmt = CASE
500 WHEN blrs.OrderType = 'CM' THEN
501 blrs.TranAmt * -1
502 ELSE
503 blrs.TranAmt
504 END,
505 blrs.ShippedQty,
506 blrs.IsValidDate,
507 blrs.IsValidDistance,
508 blrs.IsRevenueDeduction,
509 blrs.ReasonCode,
510 blrs.OrigOrderNbr,
511 blrs.OrigOrderType
512 FROM dbo.DMSBLRawSales blrs WITH (NOLOCK)
513 WHERE blrs.CompanyID = @_CompanyID
514 AND blrs.OrderDate
515 BETWEEN @_FromDate AND @_ToDate
516 AND blrs.IsDispose = 0
517 AND blrs.IsFree = 0
518 AND blrs.SourceType != 'TMK'
519 AND
520 (
521 blrs.OrderType <> 'CM'
522 OR blrs.IsRevenueDeduction = 1
523 )
524 UNION ALL
525 SELECT blrsh.CompanyID,
526 blrsh.BLDistributorLocationID,
527 blrsh.SalespersonID,
528 blrsh.OrderType,
529 blrsh.OrderNbr,
530 blrsh.OrderDate,
531 blrsh.SourceType,
532 blrsh.Route,
533 blrsh.CustomerID,
534 blrsh.CustomerLocationID,
535 blrsh.InventoryID,
536 TranAmt = CASE
537 WHEN blrsh.OrderType = 'CM' THEN
538 blrsh.TranAmt * -1
539 ELSE
540 blrsh.TranAmt
541 END,
542 blrsh.ShippedQty,
543 blrsh.IsValidDate,
544 blrsh.IsValidDistance,
545 blrsh.IsRevenueDeduction,
546 blrsh.ReasonCode,
547 blrsh.OrigOrderNbr,
548 blrsh.OrigOrderType
549 FROM dbo.DMSBLRawSalesHistory blrsh WITH (NOLOCK)
550 JOIN dbo.DMSBLDistributorLocation dl WITH (NOLOCK)
551 ON dl.CompanyID = blrsh.CompanyID
552 AND dl.ID = blrsh.BLDistributorLocationID
553 WHERE blrsh.CompanyID = @_CompanyID
554 AND blrsh.OrderDate
555 BETWEEN @_FromDate AND @_ToDate
556 AND blrsh.IsDispose = 0
557 AND blrsh.IsFree = 0
558 AND blrsh.SourceType != 'TMK'
559 -- đối với PNC có config trừ doanh số giao dịch trả hàng
560 -- nên phải lấy tất cả các giao dịch và tùy config để lấy
561 AND
562 (
563 blrsh.OrderType <> 'CM'
564 OR blrsh.IsRevenueDeduction = 1
565 )
566 UNION ALL
567 SELECT bi.CompanyID,
568 bldl.ID,
569 bi.SRCode,
570 bi.OrderType,
571 bi.OrderNbr,
572 bi.OrderDate,
573 SourceType = 'PDA',
574 bi.Route,
575 bi.CustomerCD,
576 bi.CustomerLocationID,
577 bi.InventoryID,
578 TranAmt = bi.Amount,
579 ShippedQty = bi.OrderQty,
580 IsValidDate = 1,
581 IsValidDistance = 1,
582 IsRevenueDeduction = NULL,
583 ReasonCode = NULL,
584 OrigOrderNbr = NULL,
585 OrigOrderType = NULL
586 FROM GESO..DMSBiHoDetail bi
587 JOIN dbo.DMSBLDistributorLocation bldl WITH (NOLOCK)
588 ON bldl.CompanyID = bi.CompanyID
589 AND bldl.DistributorID = bi.DistributorID
590 AND bldl.LocationID = bi.DistributorLocationID
591 WHERE bi.CompanyID = @_CompanyID
592 AND bi.OrderDate
593 BETWEEN @_FromDate AND @_ToDate
594 AND bi.Route IS NOT NULL
595 AND bi.CustomerCD IS NOT NULL
596 AND bi.CustomerLocationID IS NOT NULL
597 AND bi.Status = 'A'
598 --AND bldl.EffectiveDate BETWEEN @_FromDate
599 -- AND @_ToDate
600 AND ISNULL(bldl.ExpirationDate, @_ToDate)
601 BETWEEN @_FromDate AND @_ToDate
602 ) tmp
603 JOIN #tmpDistributorLocation dl WITH (NOLOCK)
604 ON dl.CompanyID = tmp.CompanyID
605 AND dl.ID = tmp.BLDistributorLocationID;
606
607 -- Thu.Nguyen 2019-03-19 bỏ do không dùng tới
608 -- them tinh doanh so route cu cho route moi
609 --SELECT tmp.RowNumber,
610 -- tmp.CompanyID,
611 -- tmp.SalespersonID,
612 -- tmp.ROUTE
613 --INTO #tempSRMCP
614 --FROM
615 --(
616 -- SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
617 -- rs.SalespersonID
618 -- ORDER BY rs.EffectiveDate DESC
619 -- ),
620 -- rs.CompanyID,
621 -- rs.SalespersonID,
622 -- ROUTE = rs.RouteCD
623 -- FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
624 -- WHERE rs.CompanyID = @_CompanyID
625 -- AND
626 -- (
627 -- @_FromDate
628 -- BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
629 -- OR @_ToDate
630 -- BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
631 -- OR rs.EffectiveDate
632 -- BETWEEN @_FromDate AND @_ToDate
633 -- )
634 --) tmp
635 --WHERE tmp.RowNumber = 1;
636
637 --UPDATE rs
638 --SET Route = mcp.ROUTE
639 --FROM #tmpSORawSales rs
640 -- JOIN #tempSRMCP mcp
641 -- ON mcp.CompanyID = rs.CompanyID
642 -- AND mcp.SalespersonID = rs.SalespersonID;
643
644
645 ---- Lấy dữ liệu RawData SalesIn để tính KPI
646
647 SELECT blsi.CompanyID,
648 blsi.TranDate,
649 dl.DistributorID,
650 dl.DistributorLocationID,
651 blsi.InventoryID,
652 blsi.ReceivedAmt,
653 blsi.ReceivedQty,
654 dl.SalesOrg_0_ValueID,
655 dl.SalesOrg_1_ValueID,
656 dl.SalesOrg_2_ValueID,
657 dl.SalesOrg_3_ValueID,
658 dl.SalesOrg_4_ValueID,
659 dl.SalesOrg_5_ValueID,
660 dl.SalesOrg_6_ValueID,
661 dl.SalesOrg_7_ValueID,
662 dl.SalesOrg_8_ValueID,
663 dl.SalesOrg_9_ValueID,
664 dl.SalesForce_0_ID,
665 dl.SalesForce_1_ID,
666 dl.SalesForce_2_ID,
667 dl.SalesForce_3_ID,
668 dl.SalesForce_4_ID,
669 dl.SalesForce_5_ID,
670 dl.SalesForce_6_ID,
671 dl.SalesForce_7_ID,
672 dl.SalesForce_8_ID,
673 dl.SalesForce_9_ID
674 INTO #tmpSIRawSales
675 FROM dbo.DMSBLSalesIn blsi WITH (NOLOCK)
676 JOIN #tmpDistributorLocation dl
677 ON dl.CompanyID = blsi.CompanyID
678 AND dl.ID = blsi.BLDistributorLocationID
679 WHERE blsi.CompanyID = @_CompanyID
680 AND blsi.TranDate
681 BETWEEN @_FromDate AND @_ToDate;
682
683 ---- Lấy ra dữ liệu BL Route Sales Để tính các KPIs theo %
684 SELECT *
685 INTO #tmpRouteSalesData
686 FROM
687 (
688 SELECT blrs.CompanyID,
689 blrs.DistributorID,
690 blrs.BLDistributorLocationID,
691 blrs.TranDate,
692 blrs.SalespersonID,
693 blrs.Route,
694 blrs.Revenue1,
695 blrs.Revenue2,
696 blrs.Revenue3,
697 blrs.PromotionAmt,
698 blrs.SalesOut1,
699 blrs.SalesOut2,
700 blrs.SalesOut3,
701 blrs.PromotionQty,
702 blrs.ASO,
703 blrs.TotalASO,
704 blrs.TotalMCPVisit,
705 blrs.TotalActualVisit,
706 blrs.PC1,
707 blrs.PC2,
708 blrs.PC3
709 FROM dbo.DMSBLRouteSales blrs WITH (NOLOCK)
710 WHERE blrs.CompanyID = @_CompanyID
711 AND blrs.TranDate
712 BETWEEN @_FromDate AND @_ToDate
713 UNION ALL
714 SELECT blrsh.CompanyID,
715 blrsh.DistributorID,
716 blrsh.BLDistributorLocationID,
717 blrsh.TranDate,
718 blrsh.SalespersonID,
719 blrsh.Route,
720 blrsh.Revenue1,
721 blrsh.Revenue2,
722 blrsh.Revenue3,
723 blrsh.PromotionAmt,
724 blrsh.SalesOut1,
725 blrsh.SalesOut2,
726 blrsh.SalesOut3,
727 blrsh.PromotionQty,
728 blrsh.ASO,
729 blrsh.TotalASO,
730 blrsh.TotalMCPVisit,
731 blrsh.TotalActualVisit,
732 blrsh.PC1,
733 blrsh.PC2,
734 blrsh.PC3
735 FROM dbo.DMSBLRouteSalesHistory blrsh WITH (NOLOCK)
736 WHERE blrsh.CompanyID = @_CompanyID
737 AND blrsh.TranDate
738 BETWEEN @_FromDate AND @_ToDate
739 ) tmp;
740
741 ---- Lấy danh sách KPI ngoại trừ các KPI đặc biệt
742 SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
743 *
744 INTO #tmpKPIList
745 FROM
746 (
747 SELECT CompanyID,
748 CodeListSalesID,
749 CodeListSalesCD,
750 RefNbr,
751 KPIPeriodNbr,
752 Descr = MAX(Descr),
753 Source = MAX(Source),
754 Type = MAX(Type),
755 GroupBy = MAX(GroupBy),
756 GroupID = MAX(GroupID),
757 Formula = MAX(Formula),
758 Period = MAX(Period),
759 SalesOrgID = MAX(SalesOrgID),
760 SalesAreaID = MAX(SalesAreaID),
761 Level = MAX(Level),
762 Template = MAX(Template),
763 FromDate,
764 ToDate,
765 ObjectType = MAX(ObjectType),
766 CorrectRouteAndCorrectCoordinates,
767 CorrectRouteAndIncorrectCoordinates,
768 IncorrectRoute,
769 DistributorsSell,
770 MinSKUVolume,
771 MinSKURevenue,
772 ExcludeReturnOrder
773 FROM #tmpKPIDetail
774 WHERE Template IS NULL
775 OR Template NOT IN ( 'LPPC', 'AVGPC', 'AVGPCAC', 'DROPSIZER', 'DROPSIZEV', 'DISPLAY%PASS', 'PCINDAY' )
776 GROUP BY CompanyID,
777 CodeListSalesID,
778 CodeListSalesCD,
779 RefNbr,
780 KPIPeriodNbr,
781 FromDate,
782 ToDate,
783 CorrectRouteAndCorrectCoordinates,
784 CorrectRouteAndIncorrectCoordinates,
785 IncorrectRoute,
786 DistributorsSell,
787 MinSKUVolume,
788 MinSKURevenue,
789 ExcludeReturnOrder
790 ) tmp;
791
792 DECLARE @_Min INT;
793 DECLARE @_Max INT;
794 DECLARE @_KPIID INT;
795 DECLARE @_KPICD VARCHAR(50);
796 DECLARE @_KPIDescr NVARCHAR(100);
797 DECLARE @_RefNbr VARCHAR(50);
798 DECLARE @_KPIPeriodNbr VARCHAR(50);
799 DECLARE @_Source CHAR(2);
800 DECLARE @_Type CHAR(1);
801 DECLARE @_GroupBy CHAR(1);
802 DECLARE @_GroupID VARCHAR(50);
803 DECLARE @_Formula CHAR(1);
804 DECLARE @_Template VARCHAR(50);
805 DECLARE @_Period CHAR(1);
806 DECLARE @_SalesAreaID INT;
807 DECLARE @_Level INT;
808 DECLARE @_ObjectType CHAR(1);
809 DECLARE @_IsValiDateValidDistance BIT;
810 DECLARE @_IsValidDateInvalidDistance BIT;
811 DECLARE @_ExcludeReturnOrder BIT;
812 DECLARE @_IsInvalidDate BIT;
813 DECLARE @_IsDistributorSell BIT;
814 DECLARE @_IncludePromotionItem BIT;
815 DECLARE @_IncludeWarrantyItem BIT;
816 DECLARE @_MinSKUVolume DECIMAL(18, 6);
817 DECLARE @_MinSKURevenue DECIMAL(18, 6);
818 SELECT @_Min = 1;
819 SELECT @_Max = MAX(RowNumber)
820 FROM #tmpKPIList;
821 DECLARE @_TempFromDate DATETIME = NULL;
822 DECLARE @_TempToDate DATETIME = NULL;
823
824 BEGIN ---- Tính các KPI bình thường
825 ---- Lặp qua tất cả KPI trong danh sách để tính
826 WHILE @_Min <= @_Max
827 BEGIN
828 ---- Xóa dữ liệu SM để tính lại đối với KPI mới
829 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
830 DELETE FROM dbo.DMSBLObjectKPITemp;
831
832 ---- Lấy thông tin công thức KPI
833 SELECT @_KPIID = CodeListSalesID,
834 @_KPICD = CodeListSalesCD,
835 @_KPIDescr = Descr,
836 @_RefNbr = RefNbr,
837 @_KPIPeriodNbr = KPIPeriodNbr,
838 @_Source = Source,
839 @_Type = Type,
840 @_GroupBy = ISNULL(GroupBy, 'A'),
841 @_GroupID = GroupID,
842 @_Formula = Formula,
843 @_Template = Template,
844 @_Period = Period,
845 @_SalesAreaID = SalesAreaID,
846 @_Level = Level,
847 @_FromDate = FromDate,
848 @_ToDate = ToDate,
849 @_ObjectType = ObjectType,
850 @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
851 @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
852 @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
853 @_IsDistributorSell = ISNULL(DistributorsSell, 0),
854 @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
855 @_MinSKURevenue = MinSKURevenue,
856 @_MinSKUVolume = MinSKUVolume
857 FROM #tmpKPIList
858 WHERE RowNumber = @_Min;
859
860 IF @_TempFromDate IS NULL
861 OR @_TempToDate IS NULL
862 OR @_TempFromDate != @_FromDate
863 OR @_TempToDate != @_ToDate
864 BEGIN
865 DELETE FROM #tmpSalesTerritory;
866 DELETE FROM #tmpRoute;
867 DELETE FROM #tmpSalesForce;
868
869 ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
870 INSERT INTO #tmpSalesTerritory
871 SELECT *
872 FROM
873 (
874 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
875 st.SalesOrg_0_ValueID
876 ORDER BY st.EffectiveDate DESC
877 ),
878 st.CompanyID,
879 st.SalesOrg_0_ValueID,
880 st.SalesOrg_1_ValueID,
881 st.SalesOrg_2_ValueID,
882 st.SalesOrg_3_ValueID,
883 st.SalesOrg_4_ValueID,
884 st.SalesOrg_5_ValueID,
885 st.SalesOrg_6_ValueID,
886 st.SalesOrg_7_ValueID,
887 st.SalesOrg_8_ValueID,
888 st.SalesOrg_9_ValueID
889 FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
890 WHERE st.CompanyID = @_CompanyID
891 AND
892 (
893 @_FromDate
894 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
895 OR @_ToDate
896 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
897 OR st.EffectiveDate
898 BETWEEN @_FromDate AND @_ToDate
899 )
900 ) tmp
901 WHERE tmp.RowNumber = 1;
902
903 ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
904 INSERT INTO #tmpRoute
905 SELECT tmp.CompanyID,
906 tmp.SalespersonID,
907 tmp.Route,
908 tmp.SalesForceID,
909 tmp.SalesAreaID,
910 tmp.EffectiveDate,
911 tmp.ExpirationDate,
912 st.SalesOrg_0_ValueID,
913 st.SalesOrg_1_ValueID,
914 st.SalesOrg_2_ValueID,
915 st.SalesOrg_3_ValueID,
916 st.SalesOrg_4_ValueID,
917 st.SalesOrg_5_ValueID,
918 st.SalesOrg_6_ValueID,
919 st.SalesOrg_7_ValueID,
920 st.SalesOrg_8_ValueID,
921 st.SalesOrg_9_ValueID
922 FROM
923 (
924 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
925 rs.SalespersonID
926 ORDER BY rs.EffectiveDate DESC
927 ),
928 rs.CompanyID,
929 rs.SalespersonID,
930 Route = rs.RouteCD,
931 rs.SalesForceID,
932 sph.SalesAreaID,
933 rs.EffectiveDate,
934 ExpirationDate = ISNULL(rs.EndDate, GETDATE())
935 FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
936 JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
937 WHERE rs.CompanyID = @_CompanyID
938 AND
939 (
940 @_FromDate
941 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
942 OR @_ToDate
943 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
944 OR rs.EffectiveDate
945 BETWEEN @_FromDate AND @_ToDate
946 )
947 UNION ALL
948 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
949 rs.PreviousSalesMan
950 ORDER BY rs.EffectiveDate DESC
951 ),
952 rs.CompanyID,
953 SalespersonID = rs.PreviousSalesMan,
954 Route = rs.RouteCD,
955 rs.SalesForceID,
956 sph.SalesAreaID,
957 rs.StartDatePre,
958 rs.EndDatePre
959 FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
960 JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
961 WHERE rs.CompanyID = @_CompanyID
962 AND
963 (
964 @_FromDate
965 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
966 OR @_ToDate
967 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
968 OR rs.EffectiveDate
969 BETWEEN @_FromDate AND @_ToDate
970 )
971 AND rs.PreviousSalesMan IS NOT NULL
972 UNION
973 SELECT 1 AS RowNumber,
974 @_CompanyID AS CompanyID,
975 1231 AS SalespersonID,
976 'R000918' AS Route,
977 1194 AS SalesForceID,
978 535 AS SalesAreaID,
979 '2019-06-01 00:00:00.000' AS StartDatePre,
980 '2019-06-14 00:00:00.000' AS EndDatePre
981 WHERE @_Year = '2019'
982 AND @_Month = '06'
983 AND @_CompanyID = 3
984 UNION
985 SELECT 1 AS RowNumber,
986 @_CompanyID AS CompanyID,
987 2766 AS SalespersonID,
988 'R000918' AS Route,
989 1194 AS SalesForceID,
990 535 AS SalesAreaID,
991 '2019-06-15 00:00:00.000' AS StartDatePre,
992 '2019-06-30 00:00:00.000' AS EndDatePre
993 WHERE @_Year = '2019'
994 AND @_Month = '06'
995 AND @_CompanyID = 3
996 ) tmp
997 JOIN #tmpSalesTerritory st
998 ON st.CompanyID = tmp.CompanyID
999 AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
1000 WHERE tmp.RowNumber = 1;
1001
1002 ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
1003 INSERT INTO #tmpSalesForce
1004 SELECT tmp.CompanyID,
1005 tmp.EmployeeID,
1006 tmp.SFHierachyID,
1007 tmp.TerritoryType,
1008 tmp.SalesOrgValueID,
1009 st.SalesOrg_0_ValueID,
1010 st.SalesOrg_1_ValueID,
1011 st.SalesOrg_2_ValueID,
1012 st.SalesOrg_3_ValueID,
1013 st.SalesOrg_4_ValueID,
1014 st.SalesOrg_5_ValueID,
1015 st.SalesOrg_6_ValueID,
1016 st.SalesOrg_7_ValueID,
1017 st.SalesOrg_8_ValueID,
1018 st.SalesOrg_9_ValueID
1019 FROM
1020 (
1021 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
1022 sf.EmployeeID
1023 ORDER BY sf.EffectiveDate DESC
1024 ),
1025 sf.CompanyID,
1026 sf.EmployeeID,
1027 sf.SFHierachyID,
1028 sf.TerritoryType,
1029 sf.SalesOrgValueID
1030 FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
1031 WHERE sf.CompanyID = @_CompanyID
1032 AND
1033 (
1034 @_FromDate
1035 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
1036 OR @_ToDate
1037 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
1038 OR sf.EffectiveDate
1039 BETWEEN @_FromDate AND @_ToDate
1040 )
1041 ) tmp
1042 LEFT JOIN #tmpSalesTerritory st
1043 ON st.CompanyID = tmp.CompanyID
1044 AND
1045 (
1046 st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
1047 OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
1048 OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
1049 OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
1050 OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
1051 OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
1052 OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
1053 OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
1054 OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
1055 OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
1056 )
1057 WHERE tmp.RowNumber = 1;
1058
1059 SET @_TempFromDate = @_FromDate;
1060 SET @_TempToDate = @_ToDate;
1061 END;
1062 ---- Tính KPI của đối tượng Sales
1063 IF @_ObjectType = 'S'
1064 BEGIN
1065
1066 ---- Tính KPI doanh số và số lượng, PC, SKU, ASO theo đơn hàng
1067 IF @_Template IN ( 'REVENUE', 'QUANTITY', 'PC', 'SKU', 'ASOROUTE' )
1068 OR @_Source = 'SO'
1069 OR @_Source = 'SD'
1070 OR
1071 (
1072 @_Source = 'CU'
1073 AND @_Type = 'Q'
1074 )
1075 BEGIN
1076 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
1077 SELECT rs.CompanyID,
1078 rs.SalespersonID,
1079 r.SalesForceID,
1080 r.SalesAreaID,
1081 rs.Route,
1082 rs.OrderDate,
1083 rs.SourceType,
1084 rs.CustomerID,
1085 rs.CustomerLocationID,
1086 rs.IsValidDate,
1087 rs.IsValidDistance,
1088 Multiply = CASE
1089 WHEN rs.IsRevenueDeduction = 1 THEN
1090 -1
1091 ELSE
1092 1
1093 END,
1094 PC = 1,
1095 SKU = COUNT(DISTINCT rs.InventoryID),
1096 TranAmt = SUM(rs.TranAmt),
1097 ShippedQty = SUM(rs.ShippedQty),
1098 rs.SalesOrg_0_ValueID,
1099 rs.SalesOrg_1_ValueID,
1100 rs.SalesOrg_2_ValueID,
1101 rs.SalesOrg_3_ValueID,
1102 rs.SalesOrg_4_ValueID,
1103 rs.SalesOrg_5_ValueID,
1104 rs.SalesOrg_6_ValueID,
1105 rs.SalesOrg_7_ValueID,
1106 rs.SalesOrg_8_ValueID,
1107 rs.SalesOrg_9_ValueID,
1108 rs.SalesForce_0_ID,
1109 rs.SalesForce_1_ID,
1110 rs.SalesForce_2_ID,
1111 rs.SalesForce_3_ID,
1112 rs.SalesForce_4_ID,
1113 rs.SalesForce_5_ID,
1114 rs.SalesForce_6_ID,
1115 rs.SalesForce_7_ID,
1116 rs.SalesForce_8_ID,
1117 rs.SalesForce_9_ID,
1118 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
1119 FROM #tmpSORawSales rs
1120 LEFT JOIN MRCDMS..DMSViewInventoryItem inv
1121 ON rs.CompanyID = inv.CompanyID
1122 AND rs.InventoryID = inv.InventoryID
1123 LEFT JOIN #tmpRoute r
1124 ON r.CompanyID = rs.CompanyID
1125 AND r.SalespersonID = rs.SalespersonID
1126 AND r.Route = rs.Route
1127 AND rs.OrderDate
1128 BETWEEN r.EffectiveDate AND r.ExpirationDate
1129 WHERE rs.OrderDate
1130 BETWEEN @_TempFromDate AND @_TempToDate
1131 AND rs.OrderType IN ( (CASE
1132 WHEN @_ExcludeReturnOrder = 0 THEN
1133 'CM'
1134 ELSE
1135 ''
1136 END
1137 ), 'SO', 'IN'
1138 )
1139 AND
1140 (
1141 ISNULL(@_GroupID, '') = ''
1142 OR
1143 (
1144 EXISTS
1145 (
1146 SELECT TOP 1
1147 *
1148 FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
1149 WHERE igd.CompanyID = @_CompanyID
1150 AND igd.CodegGroupProd = @_GroupID
1151 AND igd.InventoryID = rs.InventoryID
1152 )
1153 OR EXISTS
1154 (
1155 SELECT *
1156 FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
1157 WHERE gr.CompanyID = @_CompanyID
1158 AND gr.CodegGroupProd = @_GroupID
1159 AND inv.[HierarchyID] = gr.[HierarchyID]
1160 OR gr.Attribute0 = inv.Attribute0ID
1161 OR gr.Attribute1 = inv.Attribute1ID
1162 OR gr.Attribute2 = inv.Attribute2ID
1163 OR gr.Attribute3 = inv.Attribute3ID
1164 OR gr.Attribute4 = inv.Attribute4ID
1165 OR gr.Attribute5 = inv.Attribute5ID
1166 OR gr.Attribute6 = inv.Attribute6ID
1167 OR gr.Attribute7 = inv.Attribute7ID
1168 OR gr.Attribute8 = inv.Attribute8ID
1169 OR gr.Attribute9 = inv.Attribute9ID
1170 )
1171 )
1172 )
1173 AND rs.ShippedQty >= @_MinSKUVolume
1174 AND ABS(rs.TranAmt) >= @_MinSKURevenue
1175 GROUP BY rs.CompanyID,
1176 rs.SalespersonID,
1177 r.SalesForceID,
1178 r.SalesAreaID,
1179 rs.ROUTE,
1180 rs.OrderDate,
1181 rs.SourceType,
1182 rs.CustomerID,
1183 rs.CustomerLocationID,
1184 rs.IsValidDate,
1185 rs.IsValidDistance,
1186 rs.IsRevenueDeduction,
1187 rs.SalesOrg_0_ValueID,
1188 rs.SalesOrg_1_ValueID,
1189 rs.SalesOrg_2_ValueID,
1190 rs.SalesOrg_3_ValueID,
1191 rs.SalesOrg_4_ValueID,
1192 rs.SalesOrg_5_ValueID,
1193 rs.SalesOrg_6_ValueID,
1194 rs.SalesOrg_7_ValueID,
1195 rs.SalesOrg_8_ValueID,
1196 rs.SalesOrg_9_ValueID,
1197 rs.SalesForce_0_ID,
1198 rs.SalesForce_1_ID,
1199 rs.SalesForce_2_ID,
1200 rs.SalesForce_3_ID,
1201 rs.SalesForce_4_ID,
1202 rs.SalesForce_5_ID,
1203 rs.SalesForce_6_ID,
1204 rs.SalesForce_7_ID,
1205 rs.SalesForce_8_ID,
1206 rs.SalesForce_9_ID;
1207
1208 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
1209 @_Template,
1210 @_Source,
1211 @_Type,
1212 @_Formula,
1213 @_IsValiDateValidDistance,
1214 @_IsValidDateInvalidDistance,
1215 @_IsInvalidDate,
1216 @_IsDistributorSell;
1217
1218 ---- KPI for PDA
1219 EXEC dbo.sp_DMS_Baseline_KPI_For_SFA @_CompanyID,
1220 @_Template,
1221 @_Source,
1222 @_Type,
1223 @_Formula,
1224 @_IsValiDateValidDistance,
1225 @_IsValidDateInvalidDistance,
1226 @_IsInvalidDate,
1227 @_IsDistributorSell,
1228 @_KPICD;
1229
1230 END;
1231
1232 ELSE IF @_Template = 'ASO' -- ASO theo Group SP MRC
1233 BEGIN
1234 DELETE #tmpCM;
1235 INSERT INTO #tmpCM
1236 SELECT DISTINCT
1237 OrigOrderNbr,
1238 OrigOrderType
1239 FROM #tmpSORawSales ss
1240 WHERE ss.OrderDate
1241 BETWEEN @_TempFromDate AND @_TempToDate
1242 AND ss.OrderType = 'CM';
1243
1244 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
1245 SELECT rs.CompanyID,
1246 rs.SalespersonID,
1247 r.SalesForceID,
1248 r.SalesAreaID,
1249 rs.ROUTE,
1250 NULL,
1251 rs.SourceType,
1252 rs.CustomerID,
1253 rs.CustomerLocationID,
1254 rs.IsValidDate,
1255 rs.IsValidDistance,
1256 Multiply = CASE
1257 WHEN rs.IsRevenueDeduction = 1 THEN
1258 -1
1259 ELSE
1260 1
1261 END,
1262 PC = 1,
1263 SKU = COUNT(DISTINCT rs.InventoryID),
1264 TranAmt = SUM(rs.TranAmt),
1265 ShippedQty = SUM(rs.ShippedQty),
1266 rs.SalesOrg_0_ValueID,
1267 rs.SalesOrg_1_ValueID,
1268 rs.SalesOrg_2_ValueID,
1269 rs.SalesOrg_3_ValueID,
1270 rs.SalesOrg_4_ValueID,
1271 rs.SalesOrg_5_ValueID,
1272 rs.SalesOrg_6_ValueID,
1273 rs.SalesOrg_7_ValueID,
1274 rs.SalesOrg_8_ValueID,
1275 rs.SalesOrg_9_ValueID,
1276 rs.SalesForce_0_ID,
1277 rs.SalesForce_1_ID,
1278 rs.SalesForce_2_ID,
1279 rs.SalesForce_3_ID,
1280 rs.SalesForce_4_ID,
1281 rs.SalesForce_5_ID,
1282 rs.SalesForce_6_ID,
1283 rs.SalesForce_7_ID,
1284 rs.SalesForce_8_ID,
1285 rs.SalesForce_9_ID,
1286 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
1287 FROM #tmpSORawSales rs
1288 LEFT JOIN MRCDMS..DMSViewInventoryItem inv
1289 ON rs.CompanyID = inv.CompanyID
1290 AND rs.InventoryID = inv.InventoryID
1291 LEFT JOIN #tmpRoute r
1292 ON r.CompanyID = rs.CompanyID
1293 AND r.SalespersonID = rs.SalespersonID
1294 AND r.ROUTE = rs.ROUTE
1295 AND rs.OrderDate
1296 BETWEEN r.EffectiveDate AND r.ExpirationDate
1297 WHERE rs.OrderDate
1298 BETWEEN @_TempFromDate AND @_TempToDate
1299 AND
1300 (
1301 @_ExcludeReturnOrder = 1
1302 OR NOT EXISTS
1303 (
1304 SELECT *
1305 FROM #tmpCM cm
1306 WHERE cm.OrigOrderNbr = rs.OrderNbr
1307 AND cm.OrigOrderType = rs.OrderType
1308 )
1309 )
1310 AND
1311 (
1312 ISNULL(@_GroupID, '') = ''
1313 OR
1314 (
1315 EXISTS
1316 (
1317 SELECT TOP 1
1318 *
1319 FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
1320 WHERE igd.CompanyID = @_CompanyID
1321 AND igd.CodegGroupProd = @_GroupID
1322 AND igd.InventoryID = rs.InventoryID
1323 )
1324 OR EXISTS
1325 (
1326 SELECT *
1327 FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
1328 WHERE gr.CompanyID = @_CompanyID
1329 AND gr.CodegGroupProd = @_GroupID
1330 AND inv.[HierarchyID] = gr.[HierarchyID]
1331 OR gr.Attribute0 = inv.Attribute0ID
1332 OR gr.Attribute1 = inv.Attribute1ID
1333 OR gr.Attribute2 = inv.Attribute2ID
1334 OR gr.Attribute3 = inv.Attribute3ID
1335 OR gr.Attribute4 = inv.Attribute4ID
1336 OR gr.Attribute5 = inv.Attribute5ID
1337 OR gr.Attribute6 = inv.Attribute6ID
1338 OR gr.Attribute7 = inv.Attribute7ID
1339 OR gr.Attribute8 = inv.Attribute8ID
1340 OR gr.Attribute9 = inv.Attribute9ID
1341 )
1342 )
1343 )
1344 GROUP BY rs.CompanyID,
1345 rs.SalespersonID,
1346 r.SalesForceID,
1347 r.SalesAreaID,
1348 rs.ROUTE,
1349 rs.SourceType,
1350 rs.CustomerID,
1351 rs.CustomerLocationID,
1352 rs.IsValidDate,
1353 rs.IsValidDistance,
1354 rs.IsRevenueDeduction,
1355 rs.SalesOrg_0_ValueID,
1356 rs.SalesOrg_1_ValueID,
1357 rs.SalesOrg_2_ValueID,
1358 rs.SalesOrg_3_ValueID,
1359 rs.SalesOrg_4_ValueID,
1360 rs.SalesOrg_5_ValueID,
1361 rs.SalesOrg_6_ValueID,
1362 rs.SalesOrg_7_ValueID,
1363 rs.SalesOrg_8_ValueID,
1364 rs.SalesOrg_9_ValueID,
1365 rs.SalesForce_0_ID,
1366 rs.SalesForce_1_ID,
1367 rs.SalesForce_2_ID,
1368 rs.SalesForce_3_ID,
1369 rs.SalesForce_4_ID,
1370 rs.SalesForce_5_ID,
1371 rs.SalesForce_6_ID,
1372 rs.SalesForce_7_ID,
1373 rs.SalesForce_8_ID,
1374 rs.SalesForce_9_ID
1375 HAVING SUM(rs.ShippedQty) >= @_MinSKUVolume
1376 AND SUM(rs.TranAmt) >= @_MinSKURevenue;
1377
1378 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
1379 @_Template,
1380 @_Source,
1381 @_Type,
1382 @_Formula,
1383 @_IsValiDateValidDistance,
1384 @_IsValidDateInvalidDistance,
1385 @_IsInvalidDate,
1386 @_IsDistributorSell;
1387 --select * from DMSBLObjectKPITemp--1
1388 ---- KPI for PDA
1389 EXEC dbo.sp_DMS_Baseline_KPI_For_SFA @_CompanyID,
1390 @_Template,
1391 @_Source,
1392 @_Type,
1393 @_Formula,
1394 @_IsValiDateValidDistance,
1395 @_IsValidDateInvalidDistance,
1396 @_IsInvalidDate,
1397 @_IsDistributorSell,
1398 @_KPICD;
1399
1400 END;
1401 ---- Tính KPI ASO theo hình
1402 ELSE IF @_Template = 'ASOIMG'
1403 OR @_Source = 'CU'
1404 AND @_Type = 'I'
1405 BEGIN
1406 PRINT 'Lấy dữ liệu hình ảnh SM';
1407 END;
1408 ---- Tính KPI viếng thăm KH
1409 ELSE IF @_Template = 'VISIT'
1410 OR @_Source = 'CU'
1411 AND @_Type = 'V'
1412 BEGIN
1413 INSERT INTO dbo.DMSBLObjectKPITemp
1414 (
1415 CompanyID,
1416 ObjectID,
1417 Actual
1418 )
1419 SELECT rsd.CompanyID,
1420 rsd.SalespersonID,
1421 SUM(rsd.TotalActualVisit)
1422 FROM #tmpRouteSalesData rsd
1423 WHERE rsd.TranDate
1424 BETWEEN @_FromDate AND @_ToDate
1425 GROUP BY rsd.CompanyID,
1426 rsd.SalespersonID;
1427 END;
1428 ---- Tính KPI mua hàng
1429 ELSE IF @_Source = 'PO'
1430 BEGIN
1431 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
1432 (
1433 CompanyID,
1434 TranAmt,
1435 ShippedQty,
1436 SalesOrg_0_ValueID,
1437 SalesOrg_1_ValueID,
1438 SalesOrg_2_ValueID,
1439 SalesOrg_3_ValueID,
1440 SalesOrg_4_ValueID,
1441 SalesOrg_5_ValueID,
1442 SalesOrg_6_ValueID,
1443 SalesOrg_7_ValueID,
1444 SalesOrg_8_ValueID,
1445 SalesOrg_9_ValueID,
1446 SalesForce_0_ID,
1447 SalesForce_1_ID,
1448 SalesForce_2_ID,
1449 SalesForce_3_ID,
1450 SalesForce_4_ID,
1451 SalesForce_5_ID,
1452 SalesForce_6_ID,
1453 SalesForce_7_ID,
1454 SalesForce_8_ID,
1455 SalesForce_9_ID
1456 )
1457 SELECT rs.CompanyID,
1458 rs.ReceivedAmt,
1459 rs.ReceivedQty,
1460 rs.SalesOrg_0_ValueID,
1461 rs.SalesOrg_1_ValueID,
1462 rs.SalesOrg_2_ValueID,
1463 rs.SalesOrg_3_ValueID,
1464 rs.SalesOrg_4_ValueID,
1465 rs.SalesOrg_5_ValueID,
1466 rs.SalesOrg_6_ValueID,
1467 rs.SalesOrg_7_ValueID,
1468 rs.SalesOrg_8_ValueID,
1469 rs.SalesOrg_9_ValueID,
1470 rs.SalesForce_0_ID,
1471 rs.SalesForce_1_ID,
1472 rs.SalesForce_2_ID,
1473 rs.SalesForce_3_ID,
1474 rs.SalesForce_4_ID,
1475 rs.SalesForce_5_ID,
1476 rs.SalesForce_6_ID,
1477 rs.SalesForce_7_ID,
1478 rs.SalesForce_8_ID,
1479 rs.SalesForce_9_ID
1480 FROM #tmpSIRawSales rs
1481 WHERE rs.TranDate
1482 BETWEEN @_FromDate AND @_ToDate
1483 AND
1484 (
1485 @_GroupBy = 'A'
1486 OR
1487 (
1488 @_GroupBy = 'I'
1489 AND EXISTS
1490 (
1491 SELECT TOP 1
1492 *
1493 FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
1494 WHERE igd.CompanyID = @_CompanyID
1495 AND igd.CodegGroupProd = @_GroupID
1496 AND igd.InventoryID = rs.InventoryID
1497 AND ISNULL(igd.MinQty, 1) <= rs.ReceivedQty
1498 )
1499 )
1500 );
1501
1502 EXEC dbo.sp_DMS_Baseline_KPI_SalesInObjectSales @_Type;
1503 END;
1504 ELSE IF @_Template = 'NEWCUSTOMER'
1505 BEGIN
1506
1507 INSERT INTO dbo.DMSBLObjectKPITemp
1508 (
1509 CompanyID,
1510 ObjectID,
1511 Actual
1512 )
1513 SELECT CompanyID,
1514 Salesperson,
1515 TotalOutlet = COUNT(1)
1516 FROM MRCDMS.dbo.DMSCalculatorFrequencyPropose
1517 WHERE CompanyID = @_CompanyID
1518 AND createdDate
1519 BETWEEN @_FromDate AND @_ToDate
1520 AND ActionType = 'A'
1521 GROUP BY CompanyID,
1522 Salesperson;
1523 END;
1524
1525
1526 END;
1527
1528 ELSE IF @_ObjectType = 'D'
1529 BEGIN
1530 ---- Tính KPI doanh số và số lượng
1531 IF @_Template IN ( 'REVENUE', 'QUANTITY' )
1532 OR @_Source = 'SO'
1533 BEGIN
1534 INSERT INTO dbo.DMSBLObjectKPITemp
1535 (
1536 CompanyID,
1537 ObjectID,
1538 ObjectAssignment,
1539 Actual
1540 )
1541 SELECT rs.CompanyID,
1542 rs.DistributorLocationID,
1543 rs.SalesOrg_0_ValueID,
1544 SUM( CASE
1545 WHEN ISNULL(@_Type, 'A') = 'A' THEN
1546 rs.ReceivedAmt
1547 ELSE
1548 rs.ReceivedQty
1549 END
1550 )
1551 FROM #tmpSIRawSales rs
1552 WHERE rs.TranDate
1553 BETWEEN @_FromDate AND @_ToDate
1554 AND
1555 (
1556 @_GroupBy = 'A'
1557 OR
1558 (
1559 @_GroupBy = 'I'
1560 AND EXISTS
1561 (
1562 SELECT TOP 1
1563 *
1564 FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
1565 WHERE igd.CompanyID = @_CompanyID
1566 AND igd.CodegGroupProd = @_GroupID
1567 AND igd.InventoryID = rs.InventoryID
1568 AND ISNULL(igd.MinQty, 1) <= rs.ReceivedQty
1569 )
1570 )
1571 )
1572 GROUP BY rs.CompanyID,
1573 rs.DistributorLocationID,
1574 rs.SalesOrg_0_ValueID;
1575
1576 END;
1577
1578 END;
1579
1580 ---- Insert vào dữ liệu KPI
1581
1582 IF @_ObjectType = 'S'
1583 BEGIN
1584 INSERT INTO #tmpKPI
1585 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID,
1586 tmp.EmployeeID
1587 ORDER BY tmp.EmployeeID ASC
1588 ),
1589 CompanyID = tmp.CompanyID,
1590 @_KPIID,
1591 @_KPICD,
1592 @_KPIDescr,
1593 @_Source,
1594 @_Type,
1595 @_GroupBy,
1596 @_GroupID,
1597 @_Formula,
1598 @_Template,
1599 @_KPIPeriodNbr,
1600 ISNULL(
1601 @_RefNbr,
1602 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
1603 + CONVERT(VARCHAR(20), @_ToDate, 111)
1604 ),
1605 @_Period,
1606 @_FromDate,
1607 @_ToDate,
1608 ObjectID = tmp.EmployeeID,
1609 TempObjectID = ISNULL(kpid.ObjectID, 0),
1610 @_ObjectType,
1611 tmp.ObjectAssignment,
1612 kpid.TargetsSuggest, ---- temp
1613 kpid.TargetsAssigned, ---- temp
1614 kpid.TargetsSuggest,
1615 kpid.TargetsAssigned,
1616 okpit.Actual1,
1617 okpit.Actual2,
1618 okpit.Actual3,
1619 okpit.Actual4,
1620 okpit.Actual
1621 FROM
1622 (
1623 SELECT sf.CompanyID,
1624 sf.EmployeeID,
1625 ObjectAssignment = CASE
1626 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
1627 r.ROUTE
1628 ELSE
1629 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
1630 END
1631 FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
1632 JOIN #tmpSalesForce sf WITH (NOLOCK)
1633 ON sf.CompanyID = objectPeriod.CompanyID
1634 AND sf.SFHierachyID = objectPeriod.KPIObjectID
1635 LEFT JOIN #tmpRoute r
1636 ON r.CompanyID = sf.CompanyID
1637 AND
1638 (
1639 r.SalespersonID = sf.EmployeeID
1640 OR r.SalesForceID = sf.EmployeeID
1641 )
1642 WHERE objectPeriod.CompanyID = @_CompanyID
1643 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
1644 AND
1645 (
1646 @_SalesAreaID IS NULL
1647 OR @_Level = -1
1648 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
1649 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
1650 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
1651 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
1652 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
1653 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
1654 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
1655 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
1656 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
1657 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
1658 )
1659 GROUP BY sf.CompanyID,
1660 sf.EmployeeID,
1661 CASE
1662 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
1663 r.ROUTE
1664 ELSE
1665 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
1666 END
1667 ) tmp
1668 LEFT JOIN
1669 (
1670 SELECT kpid.CompanyID,
1671 kpid.ObjectID,
1672 kpid.TargetsSuggest,
1673 kpid.TargetsAssigned
1674 FROM #tmpKPIDetail kpid
1675 WHERE kpid.CodeListSalesID = @_KPIID
1676 AND
1677 (
1678 @_RefNbr LIKE 'Temp-%'
1679 OR kpid.RefNbr = @_RefNbr
1680 )
1681 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
1682 AND kpid.FromDate = @_FromDate
1683 AND kpid.ToDate = @_ToDate
1684 ) kpid
1685 ON kpid.CompanyID = tmp.CompanyID
1686 AND kpid.ObjectID = tmp.EmployeeID
1687 LEFT JOIN dbo.DMSBLObjectKPITemp okpit WITH (NOLOCK)
1688 ON okpit.CompanyID = tmp.CompanyID
1689 AND okpit.ObjectID = tmp.EmployeeID
1690 AND okpit.ObjectAssignment = tmp.ObjectAssignment
1691 WHERE kpid.ObjectID IS NOT NULL;
1692 END;
1693 ELSE IF @_ObjectType = 'D'
1694 BEGIN
1695 INSERT INTO #tmpKPI
1696 SELECT ROW_NUMBER() OVER (PARTITION BY dl.CompanyID,
1697 dl.DistributorLocationID
1698 ORDER BY dl.DistributorLocationID ASC
1699 ),
1700 CompanyID = dl.CompanyID,
1701 @_KPIID,
1702 @_KPICD,
1703 @_KPIDescr,
1704 @_Source,
1705 @_Type,
1706 @_GroupBy,
1707 @_GroupID,
1708 @_Formula,
1709 @_Template,
1710 @_KPIPeriodNbr,
1711 ISNULL(
1712 @_RefNbr,
1713 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
1714 + CONVERT(VARCHAR(20), @_ToDate, 111)
1715 ),
1716 @_Period,
1717 @_FromDate,
1718 @_ToDate,
1719 ObjectID = dl.DistributorLocationID,
1720 TempObjectID = ISNULL(kpid.ObjectID, 0),
1721 @_ObjectType,
1722 ObjectAssignment = dl.SalesOrg_0_ValueID,
1723 kpid.TargetsSuggest, ---- temp
1724 kpid.TargetsAssigned, ---- temp
1725 kpid.TargetsSuggest,
1726 kpid.TargetsAssigned,
1727 okpit.Actual1,
1728 okpit.Actual2,
1729 okpit.Actual3,
1730 okpit.Actual4,
1731 okpit.Actual
1732 FROM
1733 (
1734 SELECT CompanyID,
1735 DistributorLocationID,
1736 SalesOrg_0_ValueID
1737 FROM #tmpDistributorLocation
1738 WHERE ExpirationDate IS NULL
1739 AND
1740 (
1741 @_SalesAreaID IS NULL
1742 OR @_Level = -1
1743 OR SalesOrg_0_ValueID = @_SalesAreaID
1744 OR SalesOrg_1_ValueID = @_SalesAreaID
1745 OR SalesOrg_2_ValueID = @_SalesAreaID
1746 OR SalesOrg_3_ValueID = @_SalesAreaID
1747 OR SalesOrg_4_ValueID = @_SalesAreaID
1748 OR SalesOrg_5_ValueID = @_SalesAreaID
1749 OR SalesOrg_6_ValueID = @_SalesAreaID
1750 OR SalesOrg_7_ValueID = @_SalesAreaID
1751 OR SalesOrg_8_ValueID = @_SalesAreaID
1752 OR SalesOrg_9_ValueID = @_SalesAreaID
1753 )
1754 GROUP BY CompanyID,
1755 DistributorLocationID,
1756 SalesOrg_0_ValueID
1757 ) dl
1758 LEFT JOIN
1759 (
1760 SELECT kpid.CompanyID,
1761 kpid.ObjectID,
1762 kpid.TargetsSuggest,
1763 kpid.TargetsAssigned
1764 FROM #tmpKPIDetail kpid
1765 WHERE kpid.CodeListSalesID = @_KPIID
1766 AND
1767 (
1768 @_RefNbr LIKE 'Temp-%'
1769 OR kpid.RefNbr = @_RefNbr
1770 )
1771 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
1772 AND kpid.FromDate = @_FromDate
1773 AND kpid.ToDate = @_ToDate
1774 ) kpid
1775 ON kpid.CompanyID = dl.CompanyID
1776 AND kpid.ObjectID = dl.DistributorLocationID
1777 LEFT JOIN dbo.DMSBLObjectKPITemp okpit WITH (NOLOCK)
1778 ON okpit.CompanyID = dl.CompanyID
1779 AND okpit.ObjectID = dl.DistributorLocationID
1780 AND okpit.ObjectAssignment = dl.SalesOrg_0_ValueID
1781 WHERE kpid.ObjectID IS NOT NULL;
1782 END;
1783
1784 SET @_Min = @_Min + 1;
1785 END;
1786 END; ---- Tính các KPI bình thường
1787
1788
1789 BEGIN ---- Tính KPI AVGPC (%PC),
1790 ---- Formula: Total PC/ Visit ( Actual or MCP)
1791 DELETE FROM #tmpKPIList;
1792
1793 INSERT INTO #tmpKPIList
1794 SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
1795 *
1796 FROM
1797 (
1798 SELECT CompanyID,
1799 CodeListSalesID,
1800 CodeListSalesCD,
1801 RefNbr,
1802 KPIPeriodNbr,
1803 Descr = MAX(Descr),
1804 SOURCE = MAX(SOURCE),
1805 TYPE = MAX(TYPE),
1806 GroupBy = MAX(GroupBy),
1807 GroupID = MAX(GroupID),
1808 Formula = MAX(Formula),
1809 PERIOD = MAX(PERIOD),
1810 SalesOrgID = MAX(SalesOrgID),
1811 SalesAreaID = MAX(SalesAreaID),
1812 LEVEL = MAX(LEVEL),
1813 Template = MAX(Template),
1814 FromDate,
1815 ToDate,
1816 ObjectType = MAX(ObjectType),
1817 CorrectRouteAndCorrectCoordinates,
1818 CorrectRouteAndIncorrectCoordinates,
1819 IncorrectRoute,
1820 DistributorsSell,
1821 MinSKUVolume,
1822 MinSKURevenue,
1823 ExcludeReturnOrder
1824 FROM #tmpKPIDetail
1825 -- lấy AVG theo actual visit hoặc MCP
1826 WHERE Template IN ( 'AVGPC', 'AVGPCAC', 'PCINDAY' )
1827 AND ObjectType = 'S'
1828 GROUP BY CompanyID,
1829 CodeListSalesID,
1830 CodeListSalesCD,
1831 RefNbr,
1832 KPIPeriodNbr,
1833 FromDate,
1834 ToDate,
1835 CorrectRouteAndCorrectCoordinates,
1836 CorrectRouteAndIncorrectCoordinates,
1837 IncorrectRoute,
1838 DistributorsSell,
1839 MinSKUVolume,
1840 MinSKURevenue,
1841 ExcludeReturnOrder
1842 ) tmp;
1843
1844 SELECT @_Max = MAX(RowNumber)
1845 FROM #tmpKPIList;
1846 DECLARE @_step INT = 1;
1847
1848 WHILE @_step <= 2
1849 BEGIN
1850 SELECT @_Min = 1;
1851 WHILE @_Min <= @_Max
1852 BEGIN
1853 ---- Xóa dữ liệu SM để tính lại đối với KPI mới
1854 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
1855 DELETE FROM dbo.DMSBLObjectKPITemp;
1856 DELETE FROM #tmpTotalPC;
1857 DELETE FROM #tmpTotalLine;
1858
1859 ---- Lấy thông tin công thức KPI
1860 SELECT @_KPIID = CodeListSalesID,
1861 @_KPICD = CodeListSalesCD,
1862 @_KPIDescr = Descr,
1863 @_RefNbr = RefNbr,
1864 @_KPIPeriodNbr = KPIPeriodNbr,
1865 @_Source = SOURCE,
1866 @_Type = TYPE,
1867 @_GroupBy = ISNULL(GroupBy, 'A'),
1868 @_GroupID = GroupID,
1869 @_Formula = Formula,
1870 @_Template = Template,
1871 @_Period = PERIOD,
1872 @_SalesAreaID = SalesAreaID,
1873 @_Level = LEVEL,
1874 @_FromDate = FromDate,
1875 @_ToDate = ToDate,
1876 @_ObjectType = ObjectType,
1877 @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
1878 @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
1879 @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
1880 @_IsDistributorSell = ISNULL(DistributorsSell, 0),
1881 @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
1882 @_MinSKURevenue = MinSKURevenue,
1883 @_MinSKUVolume = MinSKUVolume
1884 FROM #tmpKPIList
1885 WHERE RowNumber = @_Min;
1886
1887 IF @_TempFromDate IS NULL
1888 OR @_TempToDate IS NULL
1889 OR @_TempFromDate != @_FromDate
1890 OR @_TempToDate != @_ToDate
1891 BEGIN
1892 DELETE FROM #tmpSalesTerritory;
1893 DELETE FROM #tmpRoute;
1894 DELETE FROM #tmpSalesForce;
1895
1896 ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
1897 INSERT INTO #tmpSalesTerritory
1898 SELECT *
1899 FROM
1900 (
1901 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
1902 st.SalesOrg_0_ValueID
1903 ORDER BY st.EffectiveDate DESC
1904 ),
1905 st.CompanyID,
1906 st.SalesOrg_0_ValueID,
1907 st.SalesOrg_1_ValueID,
1908 st.SalesOrg_2_ValueID,
1909 st.SalesOrg_3_ValueID,
1910 st.SalesOrg_4_ValueID,
1911 st.SalesOrg_5_ValueID,
1912 st.SalesOrg_6_ValueID,
1913 st.SalesOrg_7_ValueID,
1914 st.SalesOrg_8_ValueID,
1915 st.SalesOrg_9_ValueID
1916 FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
1917 WHERE st.CompanyID = @_CompanyID
1918 AND
1919 (
1920 @_FromDate
1921 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
1922 OR @_ToDate
1923 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
1924 OR st.EffectiveDate
1925 BETWEEN @_FromDate AND @_ToDate
1926 )
1927 ) tmp
1928 WHERE tmp.RowNumber = 1;
1929
1930 ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
1931 INSERT INTO #tmpRoute
1932 SELECT tmp.CompanyID,
1933 tmp.SalespersonID,
1934 tmp.ROUTE,
1935 tmp.SalesForceID,
1936 tmp.SalesAreaID,
1937 tmp.EffectiveDate,
1938 tmp.ExpirationDate,
1939 st.SalesOrg_0_ValueID,
1940 st.SalesOrg_1_ValueID,
1941 st.SalesOrg_2_ValueID,
1942 st.SalesOrg_3_ValueID,
1943 st.SalesOrg_4_ValueID,
1944 st.SalesOrg_5_ValueID,
1945 st.SalesOrg_6_ValueID,
1946 st.SalesOrg_7_ValueID,
1947 st.SalesOrg_8_ValueID,
1948 st.SalesOrg_9_ValueID
1949 FROM
1950 (
1951 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
1952 rs.SalespersonID
1953 ORDER BY rs.EffectiveDate DESC
1954 ),
1955 rs.CompanyID,
1956 rs.SalespersonID,
1957 ROUTE = rs.RouteCD,
1958 rs.SalesForceID,
1959 sph.SalesAreaID,
1960 rs.EffectiveDate,
1961 ExpirationDate = ISNULL(rs.EndDate, GETDATE())
1962 FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
1963 JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
1964 WHERE rs.CompanyID = @_CompanyID
1965 AND
1966 (
1967 @_FromDate
1968 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
1969 OR @_ToDate
1970 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
1971 OR rs.EffectiveDate
1972 BETWEEN @_FromDate AND @_ToDate
1973 )
1974 UNION ALL
1975 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
1976 rs.PreviousSalesMan
1977 ORDER BY rs.EffectiveDate DESC
1978 ),
1979 rs.CompanyID,
1980 SalespersonID = rs.PreviousSalesMan,
1981 ROUTE = rs.RouteCD,
1982 rs.SalesForceID,
1983 sph.SalesAreaID,
1984 rs.StartDatePre,
1985 rs.EndDatePre
1986 FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
1987 JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
1988 WHERE rs.CompanyID = @_CompanyID
1989 AND
1990 (
1991 @_FromDate
1992 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
1993 OR @_ToDate
1994 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
1995 OR rs.EffectiveDate
1996 BETWEEN @_FromDate AND @_ToDate
1997 )
1998 AND rs.PreviousSalesMan IS NOT NULL
1999 ) tmp
2000 JOIN #tmpSalesTerritory st
2001 ON st.CompanyID = tmp.CompanyID
2002 AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
2003 WHERE tmp.RowNumber = 1;
2004
2005 ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
2006 INSERT INTO #tmpSalesForce
2007 SELECT tmp.CompanyID,
2008 tmp.EmployeeID,
2009 tmp.SFHierachyID,
2010 tmp.TerritoryType,
2011 tmp.SalesOrgValueID,
2012 st.SalesOrg_0_ValueID,
2013 st.SalesOrg_1_ValueID,
2014 st.SalesOrg_2_ValueID,
2015 st.SalesOrg_3_ValueID,
2016 st.SalesOrg_4_ValueID,
2017 st.SalesOrg_5_ValueID,
2018 st.SalesOrg_6_ValueID,
2019 st.SalesOrg_7_ValueID,
2020 st.SalesOrg_8_ValueID,
2021 st.SalesOrg_9_ValueID
2022 FROM
2023 (
2024 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
2025 sf.EmployeeID
2026 ORDER BY sf.EffectiveDate DESC
2027 ),
2028 sf.CompanyID,
2029 sf.EmployeeID,
2030 sf.SFHierachyID,
2031 sf.TerritoryType,
2032 sf.SalesOrgValueID
2033 FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
2034 WHERE sf.CompanyID = @_CompanyID
2035 AND
2036 (
2037 @_FromDate
2038 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
2039 OR @_ToDate
2040 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
2041 OR sf.EffectiveDate
2042 BETWEEN @_FromDate AND @_ToDate
2043 )
2044 ) tmp
2045 LEFT JOIN #tmpSalesTerritory st
2046 ON st.CompanyID = tmp.CompanyID
2047 AND
2048 (
2049 st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
2050 OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
2051 OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
2052 OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
2053 OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
2054 OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
2055 OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
2056 OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
2057 OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
2058 OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
2059 )
2060 WHERE tmp.RowNumber = 1;
2061
2062 SET @_TempFromDate = @_FromDate;
2063 SET @_TempToDate = @_ToDate;
2064 END;
2065
2066 ---- Chỉ tính KPI cho SM
2067 IF @_step = 1
2068 BEGIN
2069 ---- Tính KPI PC tạm để tính ra AVG PC
2070 SELECT rs.CompanyID,
2071 rs.SalespersonID,
2072 r.SalesForceID,
2073 r.SalesAreaID,
2074 rs.ROUTE,
2075 rs.OrderDate,
2076 rs.OrderNbr,
2077 rs.SourceType,
2078 rs.CustomerID,
2079 rs.CustomerLocationID,
2080 rs.IsValidDate,
2081 rs.IsValidDistance,
2082 Multiply = CASE
2083 WHEN rs.IsRevenueDeduction = 1 THEN
2084 -1
2085 ELSE
2086 1
2087 END,
2088 PC = COUNT(DISTINCT rs.CustomerID),
2089 SKU = COUNT(DISTINCT rs.InventoryID),
2090 TranAmt = SUM(rs.TranAmt),
2091 ShippedQty = SUM(rs.ShippedQty),
2092 rs.SalesOrg_0_ValueID,
2093 rs.SalesOrg_1_ValueID,
2094 rs.SalesOrg_2_ValueID,
2095 rs.SalesOrg_3_ValueID,
2096 rs.SalesOrg_4_ValueID,
2097 rs.SalesOrg_5_ValueID,
2098 rs.SalesOrg_6_ValueID,
2099 rs.SalesOrg_7_ValueID,
2100 rs.SalesOrg_8_ValueID,
2101 rs.SalesOrg_9_ValueID,
2102 rs.SalesForce_0_ID,
2103 rs.SalesForce_1_ID,
2104 rs.SalesForce_2_ID,
2105 rs.SalesForce_3_ID,
2106 rs.SalesForce_4_ID,
2107 rs.SalesForce_5_ID,
2108 rs.SalesForce_6_ID,
2109 rs.SalesForce_7_ID,
2110 rs.SalesForce_8_ID,
2111 rs.SalesForce_9_ID,
2112 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
2113 INTO #tempDMSBLSalesObject
2114 FROM #tmpSORawSales rs
2115 LEFT JOIN MRCDMS..DMSViewInventoryItem inv
2116 ON rs.CompanyID = inv.CompanyID
2117 AND rs.InventoryID = inv.InventoryID
2118 LEFT JOIN #tmpRoute r
2119 ON r.CompanyID = rs.CompanyID
2120 AND r.SalespersonID = rs.SalespersonID
2121 AND r.ROUTE = rs.ROUTE
2122 AND rs.OrderDate
2123 BETWEEN r.EffectiveDate AND r.ExpirationDate
2124 WHERE rs.OrderDate
2125 BETWEEN @_TempFromDate AND @_TempToDate
2126 AND rs.OrderType IN ( (CASE
2127 WHEN @_ExcludeReturnOrder = 0 THEN
2128 'CM'
2129 ELSE
2130 ''
2131 END
2132 ), 'SO', 'IN'
2133 )
2134 AND
2135 (
2136 ISNULL(@_GroupID, '') = ''
2137 OR
2138 (
2139 EXISTS
2140 (
2141 SELECT TOP 1
2142 *
2143 FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
2144 WHERE igd.CompanyID = @_CompanyID
2145 AND igd.CodegGroupProd = @_GroupID
2146 AND igd.InventoryID = rs.InventoryID
2147 )
2148 OR EXISTS
2149 (
2150 SELECT *
2151 FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
2152 WHERE gr.CompanyID = @_CompanyID
2153 AND gr.CodegGroupProd = @_GroupID
2154 AND inv.[HierarchyID] = gr.[HierarchyID]
2155 OR gr.Attribute0 = inv.Attribute0ID
2156 OR gr.Attribute1 = inv.Attribute1ID
2157 OR gr.Attribute2 = inv.Attribute2ID
2158 OR gr.Attribute3 = inv.Attribute3ID
2159 OR gr.Attribute4 = inv.Attribute4ID
2160 OR gr.Attribute5 = inv.Attribute5ID
2161 OR gr.Attribute6 = inv.Attribute6ID
2162 OR gr.Attribute7 = inv.Attribute7ID
2163 OR gr.Attribute8 = inv.Attribute8ID
2164 OR gr.Attribute9 = inv.Attribute9ID
2165 )
2166 )
2167 )
2168 GROUP BY rs.CompanyID,
2169 rs.SalespersonID,
2170 r.SalesForceID,
2171 r.SalesAreaID,
2172 rs.ROUTE,
2173 rs.OrderDate,
2174 rs.OrderNbr,
2175 rs.SourceType,
2176 rs.CustomerID,
2177 rs.CustomerLocationID,
2178 rs.IsValidDate,
2179 rs.IsValidDistance,
2180 rs.IsRevenueDeduction,
2181 rs.SalesOrg_0_ValueID,
2182 rs.SalesOrg_1_ValueID,
2183 rs.SalesOrg_2_ValueID,
2184 rs.SalesOrg_3_ValueID,
2185 rs.SalesOrg_4_ValueID,
2186 rs.SalesOrg_5_ValueID,
2187 rs.SalesOrg_6_ValueID,
2188 rs.SalesOrg_7_ValueID,
2189 rs.SalesOrg_8_ValueID,
2190 rs.SalesOrg_9_ValueID,
2191 rs.SalesForce_0_ID,
2192 rs.SalesForce_1_ID,
2193 rs.SalesForce_2_ID,
2194 rs.SalesForce_3_ID,
2195 rs.SalesForce_4_ID,
2196 rs.SalesForce_5_ID,
2197 rs.SalesForce_6_ID,
2198 rs.SalesForce_7_ID,
2199 rs.SalesForce_8_ID,
2200 rs.SalesForce_9_ID
2201 HAVING SUM(rs.ShippedQty) >= @_MinSKUVolume
2202 AND ABS(SUM(rs.TranAmt)) >= @_MinSKURevenue;
2203
2204 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
2205 SELECT CompanyID,
2206 SalespersonID,
2207 SalesForceID,
2208 SalesAreaID,
2209 ROUTE,
2210 OrderDate,
2211 SourceType,
2212 CustomerID,
2213 CustomerLocationID,
2214 IsValidDate,
2215 IsValidDistance,
2216 Multiply,
2217 PC,
2218 SKU = SUM(SKU),
2219 TranAmt = SUM(TranAmt),
2220 ShippedQty = SUM(ShippedQty),
2221 SalesOrg_0_ValueID,
2222 SalesOrg_1_ValueID,
2223 SalesOrg_2_ValueID,
2224 SalesOrg_3_ValueID,
2225 SalesOrg_4_ValueID,
2226 SalesOrg_5_ValueID,
2227 SalesOrg_6_ValueID,
2228 SalesOrg_7_ValueID,
2229 SalesOrg_8_ValueID,
2230 SalesOrg_9_ValueID,
2231 SalesForce_0_ID,
2232 SalesForce_1_ID,
2233 SalesForce_2_ID,
2234 SalesForce_3_ID,
2235 SalesForce_4_ID,
2236 SalesForce_5_ID,
2237 SalesForce_6_ID,
2238 SalesForce_7_ID,
2239 SalesForce_8_ID,
2240 SalesForce_9_ID,
2241 Hie3 = SUM(Hie3)
2242 FROM #tempDMSBLSalesObject
2243 GROUP BY CompanyID,
2244 SalespersonID,
2245 SalesForceID,
2246 SalesAreaID,
2247 ROUTE,
2248 OrderDate,
2249 SourceType,
2250 CustomerID,
2251 CustomerLocationID,
2252 IsValidDate,
2253 IsValidDistance,
2254 Multiply,
2255 PC,
2256 SalesOrg_0_ValueID,
2257 SalesOrg_1_ValueID,
2258 SalesOrg_2_ValueID,
2259 SalesOrg_3_ValueID,
2260 SalesOrg_4_ValueID,
2261 SalesOrg_5_ValueID,
2262 SalesOrg_6_ValueID,
2263 SalesOrg_7_ValueID,
2264 SalesOrg_8_ValueID,
2265 SalesOrg_9_ValueID,
2266 SalesForce_0_ID,
2267 SalesForce_1_ID,
2268 SalesForce_2_ID,
2269 SalesForce_3_ID,
2270 SalesForce_4_ID,
2271 SalesForce_5_ID,
2272 SalesForce_6_ID,
2273 SalesForce_7_ID,
2274 SalesForce_8_ID,
2275 SalesForce_9_ID;
2276 DROP TABLE #tempDMSBLSalesObject;
2277
2278 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
2279 'PC',
2280 NULL,
2281 NULL,
2282 NULL,
2283 @_IsValiDateValidDistance,
2284 @_IsValidDateInvalidDistance,
2285 @_IsInvalidDate,
2286 @_IsDistributorSell;
2287
2288 INSERT INTO #tmpTotalPC
2289 SELECT *
2290 FROM dbo.DMSBLObjectKPITemp;
2291 ---- Kết thúc tính KPI PC tạm
2292 -- TÍNH AVG PC theo lịch đi viếng thăm
2293 CREATE TABLE #tmpSalesManDay
2294 (
2295 CompanyID INT NOT NULL,
2296 SalespersonID INT NULL,
2297 Minday DATETIME NULL
2298 );
2299 INSERT INTO #tmpSalesManDay
2300 SELECT mcp.CompanyID,
2301 mcp.SalespersonID,
2302 Minday = MIN(VisitDate)
2303 FROM DMSBLMCP mcp
2304 LEFT JOIN MRCDMS..DMSBaselineClosedDate cl
2305 ON mcp.CompanyID = cl.CompanyID
2306 AND mcp.DistributorID = cl.BranchID
2307 WHERE mcp.VisitDate
2308 BETWEEN @_FromDate AND CASE
2309 WHEN @_Template = 'PCINDAY'
2310 AND @_ToDate > cl.ClosedDate THEN
2311 cl.ClosedDate
2312 ELSE
2313 @_ToDate
2314 END
2315 AND mcp.VisitDate NOT IN (
2316 SELECT ToDate FROM MRCDMS..DMSHoliday WHERE CompanyID = @_CompanyID
2317 )
2318 GROUP BY mcp.CompanyID,
2319 mcp.SalespersonID;
2320 ------------------------------
2321 CREATE TABLE #tmpSubDay
2322 (
2323 CompanyID INT NOT NULL,
2324 SRCode VARCHAR(20) NULL,
2325 Mandays INT NULL
2326 );
2327 INSERT INTO #tmpSubDay
2328 SELECT dt.CompanyID,
2329 dt.SRCode,
2330 Mandays = COUNT(DISTINCT OrderDate)
2331 FROM GESO..DMSBiHoDetail dt
2332 LEFT JOIN MRCDMS..DMSBaselineClosedDate cl
2333 ON dt.CompanyID = cl.CompanyID
2334 AND dt.DistributorID = cl.BranchID
2335 WHERE dt.CompanyID = @_CompanyID
2336 AND dt.STATUS = 'A'
2337 AND dt.OrderDate
2338 BETWEEN @_FromDate AND CASE
2339 WHEN @_Template = 'PCINDAY'
2340 AND @_ToDate > cl.ClosedDate THEN
2341 cl.ClosedDate
2342 ELSE
2343 @_ToDate
2344 END
2345 AND dt.OrderDate <
2346 (
2347 SELECT Minday
2348 FROM #tmpSalesManDay
2349 WHERE dt.CompanyID = #tmpSalesManDay.CompanyID
2350 AND dt.SRCode = #tmpSalesManDay.SalespersonID
2351 )
2352 AND dt.OrderDate NOT IN (
2353 SELECT ToDate FROM MRCDMS..DMSHoliday WHERE CompanyID = @_CompanyID
2354 )
2355 GROUP BY dt.CompanyID,
2356 dt.SRCode;
2357
2358 DROP TABLE #tmpSalesManDay;
2359 UPDATE kpipc
2360 SET kpipc.Actual1 = CASE
2361 WHEN @_IsValiDateValidDistance = 1
2362 AND ISNULL(pd.PlanDay, 0) > 0 THEN
2363 kpipc.Actual1 / (pd.PlanDay + ISNULL(sub.Mandays, 0))
2364 ELSE
2365 0
2366 END,
2367 kpipc.Actual2 = CASE
2368 WHEN @_IsValidDateInvalidDistance = 1
2369 AND ISNULL(pd.PlanDay, 0) > 0 THEN
2370 kpipc.Actual2 / (pd.PlanDay + ISNULL(sub.Mandays, 0))
2371 ELSE
2372 0
2373 END,
2374 kpipc.Actual3 = CASE
2375 WHEN @_IsInvalidDate = 1
2376 AND ISNULL(pd.PlanDay, 0) > 0 THEN
2377 kpipc.Actual3 / (pd.PlanDay + ISNULL(sub.Mandays, 0))
2378 ELSE
2379 0
2380 END,
2381 kpipc.Actual4 = CASE
2382 WHEN @_IsDistributorSell = 1
2383 AND ISNULL(pd.PlanDay, 0) > 0 THEN
2384 kpipc.Actual4 / (pd.PlanDay + ISNULL(sub.Mandays, 0))
2385 ELSE
2386 0
2387 END,
2388 kpipc.Actual = CASE
2389 WHEN ISNULL(pd.PlanDay, 0) > 0 THEN
2390 kpipc.Actual / (pd.PlanDay + ISNULL(sub.Mandays, 0))
2391 ELSE
2392 0
2393 END
2394 FROM #tmpTotalPC kpipc
2395 LEFT JOIN
2396 (
2397 SELECT mcp.CompanyID,
2398 SalespersonID,
2399 PlanDay = CASE
2400 WHEN @_Template = 'PCINDAY' THEN
2401 COUNT(DISTINCT mcp.VisitDate)
2402 END
2403 FROM MRCDMS..DMSVisitPlan mcp
2404 LEFT JOIN MRCDMS..DMSBaselineClosedDate cl
2405 ON mcp.CompanyID = cl.CompanyID
2406 AND mcp.BranchID = cl.BranchID
2407 WHERE mcp.VisitDate
2408 BETWEEN @_FromDate AND CASE
2409 WHEN cl.ClosedDate < @_ToDate THEN
2410 cl.ClosedDate
2411 ELSE
2412 @_ToDate
2413 END
2414 AND mcp.VisitDate NOT IN (
2415 SELECT ToDate FROM MRCDMS..DMSHoliday WHERE CompanyID = @_CompanyID
2416 )
2417 GROUP BY mcp.CompanyID,
2418 mcp.SalespersonID
2419 ) pd
2420 ON pd.CompanyID = kpipc.CompanyID
2421 AND pd.SalespersonID = kpipc.ObjectID
2422 LEFT JOIN #tmpSubDay sub
2423 ON sub.CompanyID = kpipc.CompanyID
2424 AND sub.SRCode = kpipc.ObjectID;
2425
2426
2427 DROP TABLE #tmpSubDay;
2428
2429 INSERT INTO #tmpKPI
2430 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
2431 tmp.CompanyID,
2432 @_KPIID,
2433 @_KPICD,
2434 @_KPIDescr,
2435 @_Source,
2436 @_Type,
2437 @_GroupBy,
2438 @_GroupID,
2439 @_Formula,
2440 @_Template,
2441 @_KPIPeriodNbr,
2442 ISNULL(
2443 @_RefNbr,
2444 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
2445 + CONVERT(VARCHAR(20), @_ToDate, 111)
2446 ),
2447 @_Period,
2448 @_FromDate,
2449 @_ToDate,
2450 ObjectID = tmp.EmployeeID,
2451 TempObjectID = ISNULL(kpid.ObjectID, 0),
2452 'S',
2453 tmp.ObjectAssignment,
2454 kpid.TargetsSuggest, ---- temp
2455 kpid.TargetsAssigned, ---- temp
2456 kpid.TargetsSuggest,
2457 kpid.TargetsAssigned,
2458 kpia.Actual1,
2459 kpia.Actual2,
2460 kpia.Actual3,
2461 kpia.Actual4,
2462 kpia.Actual
2463 FROM
2464 (
2465 SELECT sf.CompanyID,
2466 sf.EmployeeID,
2467 ObjectAssignment = r.ROUTE
2468 FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
2469 JOIN #tmpSalesForce sf WITH (NOLOCK)
2470 ON sf.CompanyID = objectPeriod.CompanyID
2471 AND sf.SFHierachyID = objectPeriod.KPIObjectID
2472 JOIN #tmpRoute r
2473 ON r.CompanyID = sf.CompanyID
2474 AND r.SalespersonID = sf.EmployeeID
2475 WHERE objectPeriod.CompanyID = @_CompanyID
2476 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
2477 AND sf.TerritoryType = 'S'
2478 AND
2479 (
2480 @_SalesAreaID IS NULL
2481 OR @_Level = -1
2482 OR r.SalesOrg_1_ValueID = @_SalesAreaID
2483 OR r.SalesOrg_2_ValueID = @_SalesAreaID
2484 OR r.SalesOrg_3_ValueID = @_SalesAreaID
2485 OR r.SalesOrg_4_ValueID = @_SalesAreaID
2486 OR r.SalesOrg_5_ValueID = @_SalesAreaID
2487 OR r.SalesOrg_6_ValueID = @_SalesAreaID
2488 OR r.SalesOrg_7_ValueID = @_SalesAreaID
2489 OR r.SalesOrg_8_ValueID = @_SalesAreaID
2490 OR r.SalesOrg_9_ValueID = @_SalesAreaID
2491 )
2492 GROUP BY sf.CompanyID,
2493 sf.EmployeeID,
2494 r.ROUTE
2495 ) tmp
2496 LEFT JOIN
2497 (
2498 SELECT kpid.CompanyID,
2499 kpid.ObjectID,
2500 kpid.KPIPeriodNbr,
2501 kpid.FromDate,
2502 kpid.ToDate,
2503 kpid.TargetsSuggest,
2504 kpid.TargetsAssigned
2505 FROM #tmpKPIDetail kpid
2506 WHERE kpid.CodeListSalesID = @_KPIID
2507 AND
2508 (
2509 @_RefNbr LIKE 'Temp-%'
2510 OR kpid.RefNbr = @_RefNbr
2511 )
2512 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
2513 AND kpid.FromDate = @_FromDate
2514 AND kpid.ToDate = @_ToDate
2515 ) kpid
2516 ON kpid.CompanyID = tmp.CompanyID
2517 AND kpid.ObjectID = tmp.EmployeeID
2518 LEFT JOIN #tmpTotalPC kpia
2519 ON kpia.CompanyID = tmp.CompanyID
2520 AND kpia.ObjectID = tmp.EmployeeID
2521 AND kpia.ObjectAssignment = tmp.ObjectAssignment
2522 WHERE kpid.ObjectID IS NOT NULL;
2523 END;
2524 ---- Tính KPI cho các đối tượng còn lại
2525 ELSE IF @_step = 2
2526 BEGIN
2527 INSERT INTO #tmpKPI
2528 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
2529 tmp.CompanyID,
2530 @_KPIID,
2531 @_KPICD,
2532 @_KPIDescr,
2533 @_Source,
2534 @_Type,
2535 @_GroupBy,
2536 @_GroupID,
2537 @_Formula,
2538 @_Template,
2539 @_KPIPeriodNbr,
2540 ISNULL(
2541 @_RefNbr,
2542 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
2543 + CONVERT(VARCHAR(20), @_ToDate, 111)
2544 ),
2545 @_Period,
2546 @_FromDate,
2547 @_ToDate,
2548 ObjectID = tmp.EmployeeID,
2549 TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
2550 'S',
2551 tmp.ObjectAssignment,
2552 TargetsSuggest = MAX(kpid.TargetsSuggest), ---- temp
2553 TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
2554 TargetsSuggest = MAX(kpid.TargetsSuggest),
2555 TargetsAssigned = MAX(kpid.TargetsAssigned),
2556 Actual1 = SUM(kpi.Actual1),
2557 Actual2 = SUM(kpi.Actual2),
2558 Actual3 = SUM(kpi.Actual3),
2559 Actual4 = SUM(kpi.Actual4),
2560 Actual = SUM(kpi.Actual)
2561 FROM
2562 (
2563 SELECT sf.CompanyID,
2564 sf.EmployeeID,
2565 ObjectAssignment = CASE
2566 WHEN sf.TerritoryType = 'D' THEN
2567 r.ROUTE
2568 ELSE
2569 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
2570 END,
2571 sf.SalesOrg_0_ValueID
2572 FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
2573 JOIN #tmpSalesForce sf WITH (NOLOCK)
2574 ON sf.CompanyID = objectPeriod.CompanyID
2575 AND sf.SFHierachyID = objectPeriod.KPIObjectID
2576 LEFT JOIN #tmpRoute r
2577 ON r.CompanyID = sf.CompanyID
2578 AND r.SalesForceID = sf.EmployeeID
2579 WHERE objectPeriod.CompanyID = @_CompanyID
2580 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
2581 AND sf.TerritoryType != 'S'
2582 AND
2583 (
2584 @_SalesAreaID IS NULL
2585 OR @_Level = -1
2586 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
2587 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
2588 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
2589 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
2590 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
2591 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
2592 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
2593 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
2594 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
2595 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
2596 )
2597 GROUP BY sf.CompanyID,
2598 sf.EmployeeID,
2599 CASE
2600 WHEN sf.TerritoryType = 'D' THEN
2601 r.ROUTE
2602 ELSE
2603 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
2604 END,
2605 sf.SalesOrg_0_ValueID
2606 ) tmp
2607 LEFT JOIN
2608 (
2609 SELECT kpid.CompanyID,
2610 kpid.ObjectID,
2611 kpid.KPIPeriodNbr,
2612 kpid.FromDate,
2613 kpid.ToDate,
2614 kpid.TargetsSuggest,
2615 kpid.TargetsAssigned
2616 FROM #tmpKPIDetail kpid
2617 WHERE kpid.CodeListSalesID = @_KPIID
2618 AND
2619 (
2620 @_RefNbr LIKE 'Temp-%'
2621 OR kpid.RefNbr = @_RefNbr
2622 )
2623 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
2624 AND kpid.FromDate = @_FromDate
2625 AND kpid.ToDate = @_ToDate
2626 ) kpid
2627 ON kpid.CompanyID = tmp.CompanyID
2628 AND kpid.ObjectID = tmp.EmployeeID
2629 LEFT JOIN
2630 (
2631 SELECT DISTINCT
2632 kpi.CompanyID,
2633 kpi.Actual1,
2634 kpi.Actual2,
2635 kpi.Actual3,
2636 kpi.Actual4,
2637 kpi.Actual,
2638 r.ROUTE,
2639 r.SalesAreaID
2640 FROM #tmpKPI kpi
2641 JOIN #tmpSalesForce sf
2642 ON sf.CompanyID = kpi.CompanyID
2643 AND sf.EmployeeID = kpi.ObjectID
2644 AND sf.TerritoryType = 'S'
2645 JOIN #tmpRoute r
2646 ON r.CompanyID = sf.CompanyID
2647 AND r.SalespersonID = sf.EmployeeID
2648 WHERE kpi.CodeListSalesID = @_KPIID
2649 AND kpi.FromDate = @_FromDate
2650 AND kpi.ToDate = @_ToDate
2651 ) kpi
2652 ON kpi.CompanyID = tmp.CompanyID
2653 AND
2654 (
2655 kpi.ROUTE = tmp.ObjectAssignment
2656 OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
2657 )
2658 WHERE kpid.ObjectID IS NOT NULL
2659 GROUP BY tmp.CompanyID,
2660 tmp.EmployeeID,
2661 tmp.ObjectAssignment;
2662 END;
2663
2664 SET @_Min = @_Min + 1;
2665 END;
2666 SET @_step = @_step + 1;
2667 END;
2668
2669 END; ---- Tính KPI AVGPC (%PC),
2670
2671 BEGIN ---- Tính KPI LPPC
2672 DELETE FROM #tmpKPIList;
2673
2674 INSERT INTO #tmpKPIList
2675 SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
2676 *
2677 FROM
2678 (
2679 SELECT CompanyID,
2680 CodeListSalesID,
2681 CodeListSalesCD,
2682 RefNbr,
2683 KPIPeriodNbr,
2684 Descr = MAX(Descr),
2685 SOURCE = MAX(SOURCE),
2686 TYPE = MAX(TYPE),
2687 GroupBy = MAX(GroupBy),
2688 GroupID = MAX(GroupID),
2689 Formula = MAX(Formula),
2690 PERIOD = MAX(PERIOD),
2691 SalesOrgID = MAX(SalesOrgID),
2692 SalesAreaID = MAX(SalesAreaID),
2693 LEVEL = MAX(LEVEL),
2694 Template = MAX(Template),
2695 FromDate,
2696 ToDate,
2697 ObjectType = MAX(ObjectType),
2698 CorrectRouteAndCorrectCoordinates,
2699 CorrectRouteAndIncorrectCoordinates,
2700 IncorrectRoute,
2701 DistributorsSell,
2702 MinSKUVolume,
2703 MinSKURevenue,
2704 ExcludeReturnOrder
2705 FROM #tmpKPIDetail
2706 WHERE Template = 'LPPC'
2707 AND ObjectType = 'S'
2708 GROUP BY CompanyID,
2709 CodeListSalesID,
2710 CodeListSalesCD,
2711 RefNbr,
2712 KPIPeriodNbr,
2713 FromDate,
2714 ToDate,
2715 CorrectRouteAndCorrectCoordinates,
2716 CorrectRouteAndIncorrectCoordinates,
2717 IncorrectRoute,
2718 DistributorsSell,
2719 MinSKUVolume,
2720 MinSKURevenue,
2721 ExcludeReturnOrder
2722 ) tmp;
2723
2724 SELECT @_Max = MAX(RowNumber)
2725 FROM #tmpKPIList;
2726
2727 SELECT @_step = 1;
2728 WHILE @_step <= 2
2729 BEGIN
2730 SELECT @_Min = 1;
2731 WHILE @_Min <= @_Max
2732 BEGIN
2733 ---- Xóa dữ liệu SM để tính lại đối với KPI mới
2734 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
2735 DELETE FROM dbo.DMSBLObjectKPITemp;
2736 DELETE FROM #tmpTotalPC;
2737 DELETE FROM #tmpTotalLine;
2738
2739 ---- Lấy thông tin công thức KPI
2740 SELECT @_KPIID = CodeListSalesID,
2741 @_KPICD = CodeListSalesCD,
2742 @_KPIDescr = Descr,
2743 @_RefNbr = RefNbr,
2744 @_KPIPeriodNbr = KPIPeriodNbr,
2745 @_Source = SOURCE,
2746 @_Type = TYPE,
2747 @_GroupBy = ISNULL(GroupBy, 'A'),
2748 @_GroupID = GroupID,
2749 @_Formula = Formula,
2750 @_Template = Template,
2751 @_Period = PERIOD,
2752 @_SalesAreaID = SalesAreaID,
2753 @_Level = LEVEL,
2754 @_FromDate = FromDate,
2755 @_ToDate = ToDate,
2756 @_ObjectType = ObjectType,
2757 @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
2758 @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
2759 @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
2760 @_IsDistributorSell = ISNULL(DistributorsSell, 0),
2761 @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
2762 @_MinSKURevenue = MinSKURevenue,
2763 @_MinSKUVolume = MinSKUVolume
2764 FROM #tmpKPIList
2765 WHERE RowNumber = @_Min;
2766
2767 IF @_TempFromDate IS NULL
2768 OR @_TempToDate IS NULL
2769 OR @_TempFromDate != @_FromDate
2770 OR @_TempToDate != @_ToDate
2771 BEGIN
2772 DELETE FROM #tmpSalesTerritory;
2773 DELETE FROM #tmpRoute;
2774 DELETE FROM #tmpSalesForce;
2775
2776 ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
2777 INSERT INTO #tmpSalesTerritory
2778 SELECT *
2779 FROM
2780 (
2781 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
2782 st.SalesOrg_0_ValueID
2783 ORDER BY st.EffectiveDate DESC
2784 ),
2785 st.CompanyID,
2786 st.SalesOrg_0_ValueID,
2787 st.SalesOrg_1_ValueID,
2788 st.SalesOrg_2_ValueID,
2789 st.SalesOrg_3_ValueID,
2790 st.SalesOrg_4_ValueID,
2791 st.SalesOrg_5_ValueID,
2792 st.SalesOrg_6_ValueID,
2793 st.SalesOrg_7_ValueID,
2794 st.SalesOrg_8_ValueID,
2795 st.SalesOrg_9_ValueID
2796 FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
2797 WHERE st.CompanyID = @_CompanyID
2798 AND
2799 (
2800 @_FromDate
2801 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
2802 OR @_ToDate
2803 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
2804 OR st.EffectiveDate
2805 BETWEEN @_FromDate AND @_ToDate
2806 )
2807 ) tmp
2808 WHERE tmp.RowNumber = 1;
2809
2810 ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
2811 INSERT INTO #tmpRoute
2812 SELECT tmp.CompanyID,
2813 tmp.SalespersonID,
2814 tmp.ROUTE,
2815 tmp.SalesForceID,
2816 tmp.SalesAreaID,
2817 tmp.EffectiveDate,
2818 tmp.ExpirationDate,
2819 st.SalesOrg_0_ValueID,
2820 st.SalesOrg_1_ValueID,
2821 st.SalesOrg_2_ValueID,
2822 st.SalesOrg_3_ValueID,
2823 st.SalesOrg_4_ValueID,
2824 st.SalesOrg_5_ValueID,
2825 st.SalesOrg_6_ValueID,
2826 st.SalesOrg_7_ValueID,
2827 st.SalesOrg_8_ValueID,
2828 st.SalesOrg_9_ValueID
2829 FROM
2830 (
2831 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
2832 rs.SalespersonID
2833 ORDER BY rs.EffectiveDate DESC
2834 ),
2835 rs.CompanyID,
2836 rs.SalespersonID,
2837 ROUTE = rs.RouteCD,
2838 rs.SalesForceID,
2839 sph.SalesAreaID,
2840 rs.EffectiveDate,
2841 ExpirationDate = ISNULL(rs.EndDate, GETDATE())
2842 FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
2843 JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
2844 WHERE rs.CompanyID = @_CompanyID
2845 AND
2846 (
2847 @_FromDate
2848 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
2849 OR @_ToDate
2850 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
2851 OR rs.EffectiveDate
2852 BETWEEN @_FromDate AND @_ToDate
2853 )
2854 UNION ALL
2855 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
2856 rs.PreviousSalesMan
2857 ORDER BY rs.EffectiveDate DESC
2858 ),
2859 rs.CompanyID,
2860 SalespersonID = rs.PreviousSalesMan,
2861 ROUTE = rs.RouteCD,
2862 rs.SalesForceID,
2863 sph.SalesAreaID,
2864 rs.StartDatePre,
2865 rs.EndDatePre
2866 FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
2867 JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
2868 WHERE rs.CompanyID = @_CompanyID
2869 AND
2870 (
2871 @_FromDate
2872 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
2873 OR @_ToDate
2874 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
2875 OR rs.EffectiveDate
2876 BETWEEN @_FromDate AND @_ToDate
2877 )
2878 AND rs.PreviousSalesMan IS NOT NULL
2879 ) tmp
2880 JOIN #tmpSalesTerritory st
2881 ON st.CompanyID = tmp.CompanyID
2882 AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
2883 WHERE tmp.RowNumber = 1;
2884
2885 ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
2886 INSERT INTO #tmpSalesForce
2887 SELECT tmp.CompanyID,
2888 tmp.EmployeeID,
2889 tmp.SFHierachyID,
2890 tmp.TerritoryType,
2891 tmp.SalesOrgValueID,
2892 st.SalesOrg_0_ValueID,
2893 st.SalesOrg_1_ValueID,
2894 st.SalesOrg_2_ValueID,
2895 st.SalesOrg_3_ValueID,
2896 st.SalesOrg_4_ValueID,
2897 st.SalesOrg_5_ValueID,
2898 st.SalesOrg_6_ValueID,
2899 st.SalesOrg_7_ValueID,
2900 st.SalesOrg_8_ValueID,
2901 st.SalesOrg_9_ValueID
2902 FROM
2903 (
2904 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
2905 sf.EmployeeID
2906 ORDER BY sf.EffectiveDate DESC
2907 ),
2908 sf.CompanyID,
2909 sf.EmployeeID,
2910 sf.SFHierachyID,
2911 sf.TerritoryType,
2912 sf.SalesOrgValueID
2913 FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
2914 WHERE sf.CompanyID = @_CompanyID
2915 AND
2916 (
2917 @_FromDate
2918 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
2919 OR @_ToDate
2920 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
2921 OR sf.EffectiveDate
2922 BETWEEN @_FromDate AND @_ToDate
2923 )
2924 ) tmp
2925 LEFT JOIN #tmpSalesTerritory st
2926 ON st.CompanyID = tmp.CompanyID
2927 AND
2928 (
2929 st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
2930 OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
2931 OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
2932 OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
2933 OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
2934 OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
2935 OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
2936 OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
2937 OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
2938 OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
2939 )
2940 WHERE tmp.RowNumber = 1;
2941
2942 SET @_TempFromDate = @_FromDate;
2943 SET @_TempToDate = @_ToDate;
2944 END;
2945
2946 ---- Tính KPI PC tạm để tính ra LPPC
2947 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
2948 SELECT rs.CompanyID,
2949 rs.SalespersonID,
2950 r.SalesForceID,
2951 r.SalesAreaID,
2952 rs.ROUTE,
2953 rs.OrderDate,
2954 rs.SourceType,
2955 rs.CustomerID,
2956 rs.CustomerLocationID,
2957 rs.IsValidDate,
2958 rs.IsValidDistance,
2959 Multiply = CASE
2960 WHEN rs.IsRevenueDeduction = 1 THEN
2961 -1
2962 ELSE
2963 1
2964 END,
2965 PC = COUNT(DISTINCT rs.CustomerID),
2966 SKU = COUNT(DISTINCT rs.InventoryID),
2967 TranAmt = SUM(rs.TranAmt),
2968 ShippedQty = SUM(rs.ShippedQty),
2969 rs.SalesOrg_0_ValueID,
2970 rs.SalesOrg_1_ValueID,
2971 rs.SalesOrg_2_ValueID,
2972 rs.SalesOrg_3_ValueID,
2973 rs.SalesOrg_4_ValueID,
2974 rs.SalesOrg_5_ValueID,
2975 rs.SalesOrg_6_ValueID,
2976 rs.SalesOrg_7_ValueID,
2977 rs.SalesOrg_8_ValueID,
2978 rs.SalesOrg_9_ValueID,
2979 rs.SalesForce_0_ID,
2980 rs.SalesForce_1_ID,
2981 rs.SalesForce_2_ID,
2982 rs.SalesForce_3_ID,
2983 rs.SalesForce_4_ID,
2984 rs.SalesForce_5_ID,
2985 rs.SalesForce_6_ID,
2986 rs.SalesForce_7_ID,
2987 rs.SalesForce_8_ID,
2988 rs.SalesForce_9_ID,
2989 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
2990 FROM #tmpSORawSales rs
2991 LEFT JOIN MRCDMS..DMSViewInventoryItem inv
2992 ON rs.CompanyID = inv.CompanyID
2993 AND rs.InventoryID = inv.InventoryID
2994 LEFT JOIN #tmpRoute r
2995 ON r.CompanyID = rs.CompanyID
2996 AND r.SalespersonID = rs.SalespersonID
2997 AND r.ROUTE = rs.ROUTE
2998 AND rs.OrderDate
2999 BETWEEN r.EffectiveDate AND r.ExpirationDate
3000 WHERE rs.OrderDate
3001 BETWEEN @_TempFromDate AND @_TempToDate
3002 AND rs.OrderType IN ( (CASE
3003 WHEN @_ExcludeReturnOrder = 0 THEN
3004 'CM'
3005 ELSE
3006 ''
3007 END
3008 ), 'SO', 'IN'
3009 )
3010 AND
3011 (
3012 ISNULL(@_GroupID, '') = ''
3013 OR
3014 (
3015 EXISTS
3016 (
3017 SELECT TOP 1
3018 *
3019 FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
3020 WHERE igd.CompanyID = @_CompanyID
3021 AND igd.CodegGroupProd = @_GroupID
3022 AND igd.InventoryID = rs.InventoryID
3023 )
3024 OR EXISTS
3025 (
3026 SELECT *
3027 FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
3028 WHERE gr.CompanyID = @_CompanyID
3029 AND gr.CodegGroupProd = @_GroupID
3030 AND inv.[HierarchyID] = gr.[HierarchyID]
3031 OR gr.Attribute0 = inv.Attribute0ID
3032 OR gr.Attribute1 = inv.Attribute1ID
3033 OR gr.Attribute2 = inv.Attribute2ID
3034 OR gr.Attribute3 = inv.Attribute3ID
3035 OR gr.Attribute4 = inv.Attribute4ID
3036 OR gr.Attribute5 = inv.Attribute5ID
3037 OR gr.Attribute6 = inv.Attribute6ID
3038 OR gr.Attribute7 = inv.Attribute7ID
3039 OR gr.Attribute8 = inv.Attribute8ID
3040 OR gr.Attribute9 = inv.Attribute9ID
3041 )
3042 )
3043 )
3044 AND rs.ShippedQty >= @_MinSKUVolume
3045 AND ABS(rs.TranAmt) >= @_MinSKURevenue
3046 --AND rs.ReasonCode = ( CASE
3047 -- WHEN @_IncludePromotionItem = 1
3048 -- THEN 'ISSKM'
3049 -- WHEN @_IncludeWarrantyItem = 1
3050 -- THEN 'ISSBH'
3051 -- ELSE 'ISS'
3052 -- END )
3053 GROUP BY rs.CompanyID,
3054 rs.SalespersonID,
3055 r.SalesForceID,
3056 r.SalesAreaID,
3057 rs.ROUTE,
3058 rs.OrderDate,
3059 rs.SourceType,
3060 rs.CustomerID,
3061 rs.CustomerLocationID,
3062 rs.IsValidDate,
3063 rs.IsValidDistance,
3064 rs.IsRevenueDeduction,
3065 rs.SalesOrg_0_ValueID,
3066 rs.SalesOrg_1_ValueID,
3067 rs.SalesOrg_2_ValueID,
3068 rs.SalesOrg_3_ValueID,
3069 rs.SalesOrg_4_ValueID,
3070 rs.SalesOrg_5_ValueID,
3071 rs.SalesOrg_6_ValueID,
3072 rs.SalesOrg_7_ValueID,
3073 rs.SalesOrg_8_ValueID,
3074 rs.SalesOrg_9_ValueID,
3075 rs.SalesForce_0_ID,
3076 rs.SalesForce_1_ID,
3077 rs.SalesForce_2_ID,
3078 rs.SalesForce_3_ID,
3079 rs.SalesForce_4_ID,
3080 rs.SalesForce_5_ID,
3081 rs.SalesForce_6_ID,
3082 rs.SalesForce_7_ID,
3083 rs.SalesForce_8_ID,
3084 rs.SalesForce_9_ID,
3085 rs.OrderNbr;
3086
3087
3088 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
3089 'PC',
3090 NULL,
3091 NULL,
3092 NULL,
3093 @_IsValiDateValidDistance,
3094 @_IsValidDateInvalidDistance,
3095 @_IsInvalidDate,
3096 @_IsDistributorSell;
3097
3098 INSERT INTO #tmpTotalPC
3099 SELECT *
3100 FROM dbo.DMSBLObjectKPITemp;
3101
3102
3103 DELETE FROM dbo.DMSBLObjectKPITemp;
3104
3105 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
3106 'SKU',
3107 NULL,
3108 NULL,
3109 NULL,
3110 @_IsValiDateValidDistance,
3111 @_IsValidDateInvalidDistance,
3112 @_IsInvalidDate,
3113 @_IsDistributorSell;
3114
3115 INSERT INTO #tmpTotalLine
3116 SELECT *
3117 FROM dbo.DMSBLObjectKPITemp;
3118 ---- Kết thúc tính KPI LPPC tạm
3119
3120 IF @_step = 1
3121 BEGIN
3122 INSERT INTO #tmpKPI
3123 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
3124 CompanyID = tmp.CompanyID,
3125 @_KPIID,
3126 @_KPICD,
3127 @_KPIDescr,
3128 @_Source,
3129 @_Type,
3130 @_GroupBy,
3131 @_GroupID,
3132 @_Formula,
3133 @_Template,
3134 @_KPIPeriodNbr,
3135 ISNULL(
3136 @_RefNbr,
3137 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
3138 + CONVERT(VARCHAR(20), @_ToDate, 111)
3139 ),
3140 @_Period,
3141 @_FromDate,
3142 @_ToDate,
3143 ObjectID = tmp.EmployeeID,
3144 TempObjectID = ISNULL(kpid.ObjectID, 0),
3145 'S',
3146 tmp.ObjectAssignment,
3147 kpid.TargetsSuggest, ---- temp
3148 kpid.TargetsAssigned, ---- temp
3149 kpid.TargetsSuggest,
3150 kpid.TargetsAssigned,
3151 kpia.Actual1,
3152 kpia.Actual2,
3153 kpia.Actual3,
3154 kpia.Actual4,
3155 kpia.Actual
3156 FROM
3157 (
3158 SELECT sf.CompanyID,
3159 sf.EmployeeID,
3160 ObjectAssignment = CASE
3161 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
3162 r.ROUTE
3163 ELSE
3164 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
3165 END
3166 FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
3167 JOIN #tmpSalesForce sf WITH (NOLOCK)
3168 ON sf.CompanyID = objectPeriod.CompanyID
3169 AND sf.SFHierachyID = objectPeriod.KPIObjectID
3170 LEFT JOIN #tmpRoute r
3171 ON r.CompanyID = sf.CompanyID
3172 AND
3173 (
3174 r.SalespersonID = sf.EmployeeID
3175 OR r.SalesForceID = sf.EmployeeID
3176 )
3177 WHERE objectPeriod.CompanyID = @_CompanyID
3178 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
3179 AND
3180 (
3181 @_SalesAreaID IS NULL
3182 OR @_Level = -1
3183 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
3184 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
3185 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
3186 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
3187 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
3188 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
3189 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
3190 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
3191 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
3192 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
3193 )
3194 GROUP BY sf.CompanyID,
3195 sf.EmployeeID,
3196 CASE
3197 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
3198 r.ROUTE
3199 ELSE
3200 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
3201 END
3202 ) tmp
3203 LEFT JOIN
3204 (
3205 SELECT kpid.CompanyID,
3206 kpid.ObjectID,
3207 kpid.KPIPeriodNbr,
3208 kpid.FromDate,
3209 kpid.ToDate,
3210 kpid.TargetsSuggest,
3211 kpid.TargetsAssigned
3212 FROM #tmpKPIDetail kpid
3213 WHERE kpid.CodeListSalesID = @_KPIID
3214 AND
3215 (
3216 @_RefNbr LIKE 'Temp-%'
3217 OR kpid.RefNbr = @_RefNbr
3218 )
3219 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
3220 AND kpid.FromDate = @_FromDate
3221 AND kpid.ToDate = @_ToDate
3222 ) kpid
3223 ON kpid.CompanyID = tmp.CompanyID
3224 AND kpid.ObjectID = tmp.EmployeeID
3225 LEFT JOIN
3226 (
3227 SELECT kpipc.CompanyID,
3228 kpipc.ObjectID,
3229 kpipc.ObjectAssignment,
3230 Actual1 = CASE
3231 WHEN @_IsValiDateValidDistance = 1
3232 AND ISNULL(kpipc.Actual1, 0) > 0 THEN
3233 kpisku.Actual1 / kpipc.Actual1
3234 ELSE
3235 0
3236 END,
3237 Actual2 = CASE
3238 WHEN @_IsValidDateInvalidDistance = 1
3239 AND ISNULL(kpipc.Actual2, 0) > 0 THEN
3240 kpisku.Actual2 / kpipc.Actual2
3241 ELSE
3242 0
3243 END,
3244 Actual3 = CASE
3245 WHEN @_IsInvalidDate = 1
3246 AND ISNULL(kpipc.Actual3, 0) > 0 THEN
3247 kpisku.Actual3 / kpipc.Actual3
3248 ELSE
3249 0
3250 END,
3251 Actual4 = CASE
3252 WHEN @_IsDistributorSell = 1
3253 AND ISNULL(kpipc.Actual4, 0) > 0 THEN
3254 kpisku.Actual4 / kpipc.Actual4
3255 ELSE
3256 0
3257 END,
3258 Actual = CASE
3259 WHEN ISNULL(kpipc.Actual, 0) > 0 THEN
3260 kpisku.Actual / kpipc.Actual
3261 ELSE
3262 0
3263 END
3264 FROM #tmpTotalPC kpipc
3265 JOIN #tmpTotalLine kpisku
3266 ON kpisku.CompanyID = kpipc.CompanyID
3267 AND kpisku.ObjectID = kpipc.ObjectID
3268 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
3269 JOIN #tmpSalesForce sf
3270 ON sf.CompanyID = kpipc.CompanyID
3271 AND sf.EmployeeID = kpipc.ObjectID
3272 AND sf.TerritoryType = 'S'
3273 JOIN #tmpRoute r
3274 ON r.CompanyID = sf.CompanyID
3275 AND r.SalespersonID = sf.EmployeeID
3276 ) kpia
3277 ON kpia.CompanyID = tmp.CompanyID
3278 AND kpia.ObjectID = tmp.EmployeeID
3279 AND kpia.ObjectAssignment = tmp.ObjectAssignment
3280 WHERE kpid.ObjectID IS NOT NULL;
3281 END;
3282 ELSE IF @_step = 2
3283 BEGIN
3284 INSERT INTO #tmpKPI
3285 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
3286 tmp.CompanyID,
3287 @_KPIID,
3288 @_KPICD,
3289 @_KPIDescr,
3290 @_Source,
3291 @_Type,
3292 @_GroupBy,
3293 @_GroupID,
3294 @_Formula,
3295 @_Template,
3296 @_KPIPeriodNbr,
3297 ISNULL(
3298 @_RefNbr,
3299 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
3300 + CONVERT(VARCHAR(20), @_ToDate, 111)
3301 ),
3302 @_Period,
3303 @_FromDate,
3304 @_ToDate,
3305 ObjectID = tmp.EmployeeID,
3306 TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
3307 'S',
3308 tmp.ObjectAssignment,
3309 TargetsSuggest = MAX(kpid.TargetsSuggest), ---- temp
3310 TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
3311 TargetsSuggest = MAX(kpid.TargetsSuggest),
3312 TargetsAssigned = MAX(kpid.TargetsAssigned),
3313 Actual1 = CASE
3314 WHEN @_IsValiDateValidDistance = 1
3315 AND SUM(kpi.PC1) > 0 THEN
3316 SUM(kpi.SKU1) / SUM(kpi.PC1)
3317 ELSE
3318 0
3319 END,
3320 Actual2 = CASE
3321 WHEN @_IsValidDateInvalidDistance = 1
3322 AND SUM(kpi.PC2) > 0 THEN
3323 SUM(kpi.SKU2) / SUM(kpi.PC2)
3324 ELSE
3325 0
3326 END,
3327 Actual3 = CASE
3328 WHEN @_IsInvalidDate = 1
3329 AND SUM(kpi.PC3) > 0 THEN
3330 SUM(kpi.SKU3) / SUM(kpi.PC3)
3331 ELSE
3332 0
3333 END,
3334 Actual4 = CASE
3335 WHEN @_IsDistributorSell = 1
3336 AND SUM(kpi.PC4) > 0 THEN
3337 SUM(kpi.SKU4) / SUM(kpi.PC4)
3338 ELSE
3339 0
3340 END,
3341 Actual = CASE
3342 WHEN SUM(kpi.PC) > 0 THEN
3343 SUM(kpi.SKU) / SUM(kpi.PC)
3344 ELSE
3345 0
3346 END
3347 FROM
3348 (
3349 SELECT sf.CompanyID,
3350 sf.EmployeeID,
3351 ObjectAssignment = CASE
3352 WHEN sf.TerritoryType = 'D' THEN
3353 r.ROUTE
3354 ELSE
3355 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
3356 END,
3357 sf.SalesOrg_0_ValueID
3358 FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
3359 JOIN #tmpSalesForce sf WITH (NOLOCK)
3360 ON sf.CompanyID = objectPeriod.CompanyID
3361 AND sf.SFHierachyID = objectPeriod.KPIObjectID
3362 LEFT JOIN #tmpRoute r
3363 ON r.CompanyID = sf.CompanyID
3364 AND r.SalesForceID = sf.EmployeeID
3365 WHERE objectPeriod.CompanyID = @_CompanyID
3366 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
3367 AND sf.TerritoryType != 'S'
3368 AND
3369 (
3370 @_SalesAreaID IS NULL
3371 OR @_Level = -1
3372 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
3373 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
3374 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
3375 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
3376 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
3377 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
3378 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
3379 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
3380 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
3381 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
3382 )
3383 GROUP BY sf.CompanyID,
3384 sf.EmployeeID,
3385 CASE
3386 WHEN sf.TerritoryType = 'D' THEN
3387 r.ROUTE
3388 ELSE
3389 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
3390 END,
3391 sf.SalesOrg_0_ValueID
3392 ) tmp
3393 LEFT JOIN
3394 (
3395 SELECT kpid.CompanyID,
3396 kpid.ObjectID,
3397 kpid.KPIPeriodNbr,
3398 kpid.FromDate,
3399 kpid.ToDate,
3400 kpid.TargetsSuggest,
3401 kpid.TargetsAssigned
3402 FROM #tmpKPIDetail kpid
3403 WHERE kpid.CodeListSalesID = @_KPIID
3404 AND
3405 (
3406 @_RefNbr LIKE 'Temp-%'
3407 OR kpid.RefNbr = @_RefNbr
3408 )
3409 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
3410 AND kpid.FromDate = @_FromDate
3411 AND kpid.ToDate = @_ToDate
3412 ) kpid
3413 ON kpid.CompanyID = tmp.CompanyID
3414 AND kpid.ObjectID = tmp.EmployeeID
3415 LEFT JOIN
3416 (
3417 SELECT DISTINCT
3418 kpipc.CompanyID,
3419 kpipc.ObjectID,
3420 kpipc.ObjectAssignment,
3421 SKU1 = kpisku.Actual1,
3422 SKU2 = kpisku.Actual2,
3423 SKU3 = kpisku.Actual3,
3424 SKU4 = kpisku.Actual4,
3425 SKU = kpisku.Actual,
3426 PC1 = kpipc.Actual1,
3427 PC2 = kpipc.Actual2,
3428 PC3 = kpipc.Actual3,
3429 PC4 = kpipc.Actual4,
3430 PC = kpipc.Actual,
3431 r.ROUTE,
3432 r.SalesAreaID
3433 FROM #tmpTotalPC kpipc
3434 JOIN #tmpTotalLine kpisku
3435 ON kpisku.CompanyID = kpipc.CompanyID
3436 AND kpisku.ObjectID = kpipc.ObjectID
3437 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
3438 JOIN #tmpSalesForce sf
3439 ON sf.CompanyID = kpipc.CompanyID
3440 AND sf.EmployeeID = kpipc.ObjectID
3441 AND sf.TerritoryType = 'S'
3442 JOIN #tmpRoute r
3443 ON r.CompanyID = sf.CompanyID
3444 AND r.SalespersonID = sf.EmployeeID
3445 ) kpi
3446 ON kpi.CompanyID = tmp.CompanyID
3447 AND
3448 (
3449 kpi.ROUTE = tmp.ObjectAssignment
3450 OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
3451 )
3452 WHERE kpid.ObjectID IS NOT NULL
3453 GROUP BY tmp.CompanyID,
3454 tmp.EmployeeID,
3455 tmp.ObjectAssignment;
3456 END;
3457 SET @_Min = @_Min + 1;
3458 END;
3459
3460 SELECT @_step = @_step + 1;
3461 END;
3462 END; ---- Tính KPI LPPC
3463
3464 BEGIN ---- KPI BPPC
3465
3466 DELETE FROM #tmpKPIList;
3467
3468 INSERT INTO #tmpKPIList
3469 SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
3470 *
3471 FROM
3472 (
3473 SELECT CompanyID,
3474 CodeListSalesID,
3475 CodeListSalesCD,
3476 RefNbr,
3477 KPIPeriodNbr,
3478 Descr = MAX(Descr),
3479 SOURCE = MAX(SOURCE),
3480 TYPE = MAX(TYPE),
3481 GroupBy = MAX(GroupBy),
3482 GroupID = MAX(GroupID),
3483 Formula = MAX(Formula),
3484 PERIOD = MAX(PERIOD),
3485 SalesOrgID = MAX(SalesOrgID),
3486 SalesAreaID = MAX(SalesAreaID),
3487 LEVEL = MAX(LEVEL),
3488 Template = MAX(Template),
3489 FromDate,
3490 ToDate,
3491 ObjectType = MAX(ObjectType),
3492 CorrectRouteAndCorrectCoordinates,
3493 CorrectRouteAndIncorrectCoordinates,
3494 IncorrectRoute,
3495 DistributorsSell,
3496 MinSKUVolume,
3497 MinSKURevenue,
3498 ExcludeReturnOrder
3499 FROM #tmpKPIDetail
3500 WHERE Template = 'BPPC'
3501 AND ObjectType = 'S'
3502 GROUP BY CompanyID,
3503 CodeListSalesID,
3504 CodeListSalesCD,
3505 RefNbr,
3506 KPIPeriodNbr,
3507 FromDate,
3508 ToDate,
3509 CorrectRouteAndCorrectCoordinates,
3510 CorrectRouteAndIncorrectCoordinates,
3511 IncorrectRoute,
3512 DistributorsSell,
3513 MinSKUVolume,
3514 MinSKURevenue,
3515 ExcludeReturnOrder
3516 ) tmp;
3517
3518 SELECT @_Max = MAX(RowNumber)
3519 FROM #tmpKPIList;
3520
3521 SELECT @_step = 1;
3522 WHILE @_step <= 2
3523 BEGIN
3524 SELECT @_Min = 1;
3525 WHILE @_Min <= @_Max
3526 BEGIN
3527 ---- Xóa dữ liệu SM để tính lại đối với KPI mới
3528 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
3529 DELETE FROM dbo.DMSBLObjectKPITemp;
3530 DELETE FROM #tmpTotalPC;
3531 DELETE FROM #tmpTotalLine;
3532
3533 ---- Lấy thông tin công thức KPI
3534 SELECT @_KPIID = CodeListSalesID,
3535 @_KPICD = CodeListSalesCD,
3536 @_KPIDescr = Descr,
3537 @_RefNbr = RefNbr,
3538 @_KPIPeriodNbr = KPIPeriodNbr,
3539 @_Source = SOURCE,
3540 @_Type = TYPE,
3541 @_GroupBy = ISNULL(GroupBy, 'A'),
3542 @_GroupID = GroupID,
3543 @_Formula = Formula,
3544 @_Template = Template,
3545 @_Period = PERIOD,
3546 @_SalesAreaID = SalesAreaID,
3547 @_Level = LEVEL,
3548 @_FromDate = FromDate,
3549 @_ToDate = ToDate,
3550 @_ObjectType = ObjectType,
3551 @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
3552 @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
3553 @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
3554 @_IsDistributorSell = ISNULL(DistributorsSell, 0),
3555 @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
3556 @_MinSKURevenue = MinSKURevenue,
3557 @_MinSKUVolume = MinSKUVolume
3558 FROM #tmpKPIList
3559 WHERE RowNumber = @_Min;
3560
3561 IF @_TempFromDate IS NULL
3562 OR @_TempToDate IS NULL
3563 OR @_TempFromDate != @_FromDate
3564 OR @_TempToDate != @_ToDate
3565 BEGIN
3566 DELETE FROM #tmpSalesTerritory;
3567 DELETE FROM #tmpRoute;
3568 DELETE FROM #tmpSalesForce;
3569
3570 ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
3571 INSERT INTO #tmpSalesTerritory
3572 SELECT *
3573 FROM
3574 (
3575 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
3576 st.SalesOrg_0_ValueID
3577 ORDER BY st.EffectiveDate DESC
3578 ),
3579 st.CompanyID,
3580 st.SalesOrg_0_ValueID,
3581 st.SalesOrg_1_ValueID,
3582 st.SalesOrg_2_ValueID,
3583 st.SalesOrg_3_ValueID,
3584 st.SalesOrg_4_ValueID,
3585 st.SalesOrg_5_ValueID,
3586 st.SalesOrg_6_ValueID,
3587 st.SalesOrg_7_ValueID,
3588 st.SalesOrg_8_ValueID,
3589 st.SalesOrg_9_ValueID
3590 FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
3591 WHERE st.CompanyID = @_CompanyID
3592 AND
3593 (
3594 @_FromDate
3595 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
3596 OR @_ToDate
3597 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
3598 OR st.EffectiveDate
3599 BETWEEN @_FromDate AND @_ToDate
3600 )
3601 ) tmp
3602 WHERE tmp.RowNumber = 1;
3603
3604 ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
3605 INSERT INTO #tmpRoute
3606 SELECT tmp.CompanyID,
3607 tmp.SalespersonID,
3608 tmp.ROUTE,
3609 tmp.SalesForceID,
3610 tmp.SalesAreaID,
3611 tmp.EffectiveDate,
3612 tmp.ExpirationDate,
3613 st.SalesOrg_0_ValueID,
3614 st.SalesOrg_1_ValueID,
3615 st.SalesOrg_2_ValueID,
3616 st.SalesOrg_3_ValueID,
3617 st.SalesOrg_4_ValueID,
3618 st.SalesOrg_5_ValueID,
3619 st.SalesOrg_6_ValueID,
3620 st.SalesOrg_7_ValueID,
3621 st.SalesOrg_8_ValueID,
3622 st.SalesOrg_9_ValueID
3623 FROM
3624 (
3625 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
3626 rs.SalespersonID
3627 ORDER BY rs.EffectiveDate DESC
3628 ),
3629 rs.CompanyID,
3630 rs.SalespersonID,
3631 ROUTE = rs.RouteCD,
3632 rs.SalesForceID,
3633 sph.SalesAreaID,
3634 rs.EffectiveDate,
3635 ExpirationDate = ISNULL(rs.EndDate, GETDATE())
3636 FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
3637 JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
3638 WHERE rs.CompanyID = @_CompanyID
3639 AND
3640 (
3641 @_FromDate
3642 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
3643 OR @_ToDate
3644 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
3645 OR rs.EffectiveDate
3646 BETWEEN @_FromDate AND @_ToDate
3647 )
3648 UNION ALL
3649 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
3650 rs.PreviousSalesMan
3651 ORDER BY rs.EffectiveDate DESC
3652 ),
3653 rs.CompanyID,
3654 SalespersonID = rs.PreviousSalesMan,
3655 ROUTE = rs.RouteCD,
3656 rs.SalesForceID,
3657 sph.SalesAreaID,
3658 rs.StartDatePre,
3659 rs.EndDatePre
3660 FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
3661 JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
3662 WHERE rs.CompanyID = @_CompanyID
3663 AND
3664 (
3665 @_FromDate
3666 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
3667 OR @_ToDate
3668 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
3669 OR rs.EffectiveDate
3670 BETWEEN @_FromDate AND @_ToDate
3671 )
3672 AND rs.PreviousSalesMan IS NOT NULL
3673
3674 --Hieu.Bui : Them vao de xu ly case chuyen nhieu SM 1 route trong 1 thang cua SM 1218 - RedmineID : #62296
3675 UNION
3676 SELECT 1 AS RowNumber,
3677 @_CompanyID AS CompanyID,
3678 1231 AS SalespersonID,
3679 'R000918' AS Route,
3680 1194 AS SalesForceID,
3681 535 AS SalesAreaID,
3682 '2019-06-01 00:00:00.000' AS StartDatePre,
3683 '2019-06-14 00:00:00.000' AS EndDatePre
3684 WHERE @_Year = '2019'
3685 AND @_Month = '06'
3686 AND @_CompanyID = 3
3687 UNION
3688 SELECT 1 AS RowNumber,
3689 @_CompanyID AS CompanyID,
3690 2766 AS SalespersonID,
3691 'R000918' AS Route,
3692 1194 AS SalesForceID,
3693 535 AS SalesAreaID,
3694 '2019-06-15 00:00:00.000' AS StartDatePre,
3695 '2019-06-30 00:00:00.000' AS EndDatePre
3696 WHERE @_Year = '2019'
3697 AND @_Month = '06'
3698 AND @_CompanyID = 3
3699 ) tmp
3700 JOIN #tmpSalesTerritory st
3701 ON st.CompanyID = tmp.CompanyID
3702 AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
3703 WHERE tmp.RowNumber = 1;
3704
3705 ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
3706 INSERT INTO #tmpSalesForce
3707 SELECT tmp.CompanyID,
3708 tmp.EmployeeID,
3709 tmp.SFHierachyID,
3710 tmp.TerritoryType,
3711 tmp.SalesOrgValueID,
3712 st.SalesOrg_0_ValueID,
3713 st.SalesOrg_1_ValueID,
3714 st.SalesOrg_2_ValueID,
3715 st.SalesOrg_3_ValueID,
3716 st.SalesOrg_4_ValueID,
3717 st.SalesOrg_5_ValueID,
3718 st.SalesOrg_6_ValueID,
3719 st.SalesOrg_7_ValueID,
3720 st.SalesOrg_8_ValueID,
3721 st.SalesOrg_9_ValueID
3722 FROM
3723 (
3724 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
3725 sf.EmployeeID
3726 ORDER BY sf.EffectiveDate DESC
3727 ),
3728 sf.CompanyID,
3729 sf.EmployeeID,
3730 sf.SFHierachyID,
3731 sf.TerritoryType,
3732 sf.SalesOrgValueID
3733 FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
3734 WHERE sf.CompanyID = @_CompanyID
3735 AND
3736 (
3737 @_FromDate
3738 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
3739 OR @_ToDate
3740 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
3741 OR sf.EffectiveDate
3742 BETWEEN @_FromDate AND @_ToDate
3743 )
3744 ) tmp
3745 LEFT JOIN #tmpSalesTerritory st
3746 ON st.CompanyID = tmp.CompanyID
3747 AND
3748 (
3749 st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
3750 OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
3751 OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
3752 OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
3753 OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
3754 OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
3755 OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
3756 OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
3757 OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
3758 OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
3759 )
3760 WHERE tmp.RowNumber = 1;
3761
3762 SET @_TempFromDate = @_FromDate;
3763 SET @_TempToDate = @_ToDate;
3764 END;
3765
3766 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
3767 ---- Tính KPI PC tạm
3768 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
3769 SELECT rs.CompanyID,
3770 rs.SalespersonID,
3771 r.SalesForceID,
3772 r.SalesAreaID,
3773 rs.ROUTE,
3774 rs.OrderDate,
3775 rs.SourceType,
3776 rs.CustomerID,
3777 rs.CustomerLocationID,
3778 rs.IsValidDate,
3779 rs.IsValidDistance,
3780 Multiply = CASE
3781 WHEN rs.IsRevenueDeduction = 1 THEN
3782 -1
3783 ELSE
3784 1
3785 END,
3786 PC = COUNT(DISTINCT rs.CustomerID),
3787 SKU = COUNT(DISTINCT rs.InventoryID),
3788 TranAmt = SUM(rs.TranAmt),
3789 ShippedQty = SUM(rs.ShippedQty),
3790 rs.SalesOrg_0_ValueID,
3791 rs.SalesOrg_1_ValueID,
3792 rs.SalesOrg_2_ValueID,
3793 rs.SalesOrg_3_ValueID,
3794 rs.SalesOrg_4_ValueID,
3795 rs.SalesOrg_5_ValueID,
3796 rs.SalesOrg_6_ValueID,
3797 rs.SalesOrg_7_ValueID,
3798 rs.SalesOrg_8_ValueID,
3799 rs.SalesOrg_9_ValueID,
3800 rs.SalesForce_0_ID,
3801 rs.SalesForce_1_ID,
3802 rs.SalesForce_2_ID,
3803 rs.SalesForce_3_ID,
3804 rs.SalesForce_4_ID,
3805 rs.SalesForce_5_ID,
3806 rs.SalesForce_6_ID,
3807 rs.SalesForce_7_ID,
3808 rs.SalesForce_8_ID,
3809 rs.SalesForce_9_ID,
3810 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
3811 FROM #tmpSORawSales rs
3812 LEFT JOIN MRCDMS..DMSViewInventoryItem inv
3813 ON rs.CompanyID = inv.CompanyID
3814 AND rs.InventoryID = inv.InventoryID
3815 LEFT JOIN #tmpRoute r
3816 ON r.CompanyID = rs.CompanyID
3817 AND r.SalespersonID = rs.SalespersonID
3818 AND r.ROUTE = rs.ROUTE
3819 AND rs.OrderDate
3820 BETWEEN r.EffectiveDate AND r.ExpirationDate
3821 WHERE rs.OrderDate
3822 BETWEEN @_TempFromDate AND @_TempToDate
3823 AND rs.OrderType IN ( (CASE
3824 WHEN @_ExcludeReturnOrder = 0 THEN
3825 'CM'
3826 ELSE
3827 ''
3828 END
3829 ), 'SO', 'IN'
3830 )
3831 AND
3832 (
3833 ISNULL(@_GroupID, '') = ''
3834 OR
3835 (
3836 EXISTS
3837 (
3838 SELECT TOP 1
3839 *
3840 FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
3841 WHERE igd.CompanyID = @_CompanyID
3842 AND igd.CodegGroupProd = @_GroupID
3843 AND igd.InventoryID = rs.InventoryID
3844 )
3845 OR EXISTS
3846 (
3847 SELECT *
3848 FROM MRCDMS..DMSKPIGroupProductSalesDetailGroup gr
3849 WHERE gr.CompanyID = @_CompanyID
3850 AND gr.CodegGroupProd = @_GroupID
3851 AND inv.[HierarchyID] = gr.[HierarchyID]
3852 OR gr.Attribute0 = inv.Attribute0ID
3853 OR gr.Attribute1 = inv.Attribute1ID
3854 OR gr.Attribute2 = inv.Attribute2ID
3855 OR gr.Attribute3 = inv.Attribute3ID
3856 OR gr.Attribute4 = inv.Attribute4ID
3857 OR gr.Attribute5 = inv.Attribute5ID
3858 OR gr.Attribute6 = inv.Attribute6ID
3859 OR gr.Attribute7 = inv.Attribute7ID
3860 OR gr.Attribute8 = inv.Attribute8ID
3861 OR gr.Attribute9 = inv.Attribute9ID
3862 )
3863 )
3864 )
3865 AND rs.ShippedQty >= @_MinSKUVolume
3866 AND ABS(rs.TranAmt) >= @_MinSKURevenue
3867 --AND rs.ReasonCode = ( CASE
3868 -- WHEN @_IncludePromotionItem = 1
3869 -- THEN 'ISSKM'
3870 -- WHEN @_IncludeWarrantyItem = 1
3871 -- THEN 'ISSBH'
3872 -- ELSE 'ISS'
3873 -- END )
3874 GROUP BY rs.CompanyID,
3875 rs.SalespersonID,
3876 r.SalesForceID,
3877 r.SalesAreaID,
3878 rs.ROUTE,
3879 rs.OrderDate,
3880 rs.SourceType,
3881 rs.CustomerID,
3882 rs.CustomerLocationID,
3883 rs.IsValidDate,
3884 rs.IsValidDistance,
3885 rs.IsRevenueDeduction,
3886 rs.SalesOrg_0_ValueID,
3887 rs.SalesOrg_1_ValueID,
3888 rs.SalesOrg_2_ValueID,
3889 rs.SalesOrg_3_ValueID,
3890 rs.SalesOrg_4_ValueID,
3891 rs.SalesOrg_5_ValueID,
3892 rs.SalesOrg_6_ValueID,
3893 rs.SalesOrg_7_ValueID,
3894 rs.SalesOrg_8_ValueID,
3895 rs.SalesOrg_9_ValueID,
3896 rs.SalesForce_0_ID,
3897 rs.SalesForce_1_ID,
3898 rs.SalesForce_2_ID,
3899 rs.SalesForce_3_ID,
3900 rs.SalesForce_4_ID,
3901 rs.SalesForce_5_ID,
3902 rs.SalesForce_6_ID,
3903 rs.SalesForce_7_ID,
3904 rs.SalesForce_8_ID,
3905 rs.SalesForce_9_ID;
3906
3907 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
3908 'PC',
3909 NULL,
3910 NULL,
3911 NULL,
3912 @_IsValiDateValidDistance,
3913 @_IsValidDateInvalidDistance,
3914 @_IsInvalidDate,
3915 @_IsDistributorSell;
3916
3917 SELECT rs.CompanyID,
3918 rs.SalespersonID,
3919 r.SalesForceID,
3920 r.SalesAreaID,
3921 rs.ROUTE,
3922 rs.OrderDate,
3923 --rs.CustomerID,
3924 --rs.CustomerLocationID,
3925 Multiply = CASE
3926 WHEN rs.IsRevenueDeduction = 1 THEN
3927 -1
3928 ELSE
3929 1
3930 END,
3931 PC = COUNT(DISTINCT rs.CustomerID),
3932 rs.SalesOrg_0_ValueID,
3933 rs.SalesOrg_1_ValueID,
3934 rs.SalesOrg_2_ValueID,
3935 rs.SalesOrg_3_ValueID,
3936 rs.SalesOrg_4_ValueID,
3937 rs.SalesOrg_5_ValueID,
3938 rs.SalesOrg_6_ValueID,
3939 rs.SalesOrg_7_ValueID,
3940 rs.SalesOrg_8_ValueID,
3941 rs.SalesOrg_9_ValueID,
3942 rs.SalesForce_0_ID,
3943 rs.SalesForce_1_ID,
3944 rs.SalesForce_2_ID,
3945 rs.SalesForce_3_ID,
3946 rs.SalesForce_4_ID,
3947 rs.SalesForce_5_ID,
3948 rs.SalesForce_6_ID,
3949 rs.SalesForce_7_ID,
3950 rs.SalesForce_8_ID,
3951 rs.SalesForce_9_ID
3952 INTO #tmpActual
3953 FROM #tmpSORawSales rs
3954 LEFT JOIN MRCDMS..DMSViewInventoryItem inv
3955 ON rs.CompanyID = inv.CompanyID
3956 AND rs.InventoryID = inv.InventoryID
3957 LEFT JOIN #tmpRoute r
3958 ON r.CompanyID = rs.CompanyID
3959 AND r.SalespersonID = rs.SalespersonID
3960 AND r.ROUTE = rs.ROUTE
3961 AND rs.OrderDate
3962 BETWEEN r.EffectiveDate AND r.ExpirationDate
3963 WHERE rs.OrderDate
3964 BETWEEN @_TempFromDate AND @_TempToDate
3965 AND rs.OrderType IN ( (CASE
3966 WHEN @_ExcludeReturnOrder = 0 THEN
3967 'CM'
3968 ELSE
3969 ''
3970 END
3971 ), 'SO', 'IN'
3972 )
3973 AND
3974 (
3975 ISNULL(@_GroupID, '') = ''
3976 OR
3977 (
3978 EXISTS
3979 (
3980 SELECT TOP 1
3981 *
3982 FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
3983 WHERE igd.CompanyID = @_CompanyID
3984 AND igd.CodegGroupProd = @_GroupID
3985 AND igd.InventoryID = rs.InventoryID
3986 )
3987 OR EXISTS
3988 (
3989 SELECT *
3990 FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
3991 WHERE gr.CompanyID = @_CompanyID
3992 AND gr.CodegGroupProd = @_GroupID
3993 AND inv.[HierarchyID] = gr.[HierarchyID]
3994 OR gr.Attribute0 = inv.Attribute0ID
3995 OR gr.Attribute1 = inv.Attribute1ID
3996 OR gr.Attribute2 = inv.Attribute2ID
3997 OR gr.Attribute3 = inv.Attribute3ID
3998 OR gr.Attribute4 = inv.Attribute4ID
3999 OR gr.Attribute5 = inv.Attribute5ID
4000 OR gr.Attribute6 = inv.Attribute6ID
4001 OR gr.Attribute7 = inv.Attribute7ID
4002 OR gr.Attribute8 = inv.Attribute8ID
4003 OR gr.Attribute9 = inv.Attribute9ID
4004 )
4005 )
4006 )
4007 GROUP BY rs.CompanyID,
4008 rs.SalespersonID,
4009 r.SalesForceID,
4010 r.SalesAreaID,
4011 rs.ROUTE,
4012 rs.OrderDate,
4013 --rs.CustomerID,
4014 --rs.CustomerLocationID,
4015 rs.IsRevenueDeduction,
4016 rs.SalesOrg_0_ValueID,
4017 rs.SalesOrg_1_ValueID,
4018 rs.SalesOrg_2_ValueID,
4019 rs.SalesOrg_3_ValueID,
4020 rs.SalesOrg_4_ValueID,
4021 rs.SalesOrg_5_ValueID,
4022 rs.SalesOrg_6_ValueID,
4023 rs.SalesOrg_7_ValueID,
4024 rs.SalesOrg_8_ValueID,
4025 rs.SalesOrg_9_ValueID,
4026 rs.SalesForce_0_ID,
4027 rs.SalesForce_1_ID,
4028 rs.SalesForce_2_ID,
4029 rs.SalesForce_3_ID,
4030 rs.SalesForce_4_ID,
4031 rs.SalesForce_5_ID,
4032 rs.SalesForce_6_ID,
4033 rs.SalesForce_7_ID,
4034 rs.SalesForce_8_ID,
4035 rs.SalesForce_9_ID
4036 HAVING SUM(rs.ShippedQty) >= @_MinSKUVolume
4037 AND SUM(ABS(rs.TranAmt)) >= @_MinSKURevenue;
4038
4039 SELECT rs.CompanyID,
4040 rs.SalespersonID,
4041 rs.ROUTE,
4042 Actual = SUM(PC * Multiply)
4043 INTO #tmpTotalActual
4044 FROM #tmpActual rs
4045 GROUP BY rs.CompanyID,
4046 rs.SalespersonID,
4047 rs.ROUTE;
4048
4049 DELETE FROM #tmpTotalPC;
4050 INSERT INTO #tmpTotalPC
4051 SELECT *
4052 FROM dbo.DMSBLObjectKPITemp;
4053
4054
4055 UPDATE pc
4056 SET pc.Actual = ac.Actual
4057 FROM #tmpTotalPC pc
4058 INNER JOIN #tmpTotalActual ac
4059 ON pc.CompanyID = ac.CompanyID
4060 AND pc.ObjectID = ac.SalespersonID
4061 AND pc.ObjectAssignment = ac.ROUTE;
4062 DROP TABLE #tmpTotalActual;
4063 DROP TABLE #tmpActual;
4064
4065 DELETE FROM dbo.DMSBLObjectKPITemp;
4066 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
4067
4068 -- Tính BPPC
4069 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
4070 SELECT rs.CompanyID,
4071 rs.SalespersonID,
4072 r.SalesForceID,
4073 r.SalesAreaID,
4074 rs.ROUTE,
4075 rs.OrderDate,
4076 rs.SourceType,
4077 rs.CustomerID,
4078 rs.CustomerLocationID,
4079 rs.IsValidDate,
4080 rs.IsValidDistance,
4081 Multiply = CASE
4082 WHEN rs.IsRevenueDeduction = 1 THEN
4083 -1
4084 ELSE
4085 1
4086 END,
4087 PC = COUNT(DISTINCT rs.CustomerID),
4088 SKU = COUNT(DISTINCT rs.InventoryID),
4089 TranAmt = SUM(rs.TranAmt),
4090 ShippedQty = SUM(rs.ShippedQty),
4091 rs.SalesOrg_0_ValueID,
4092 rs.SalesOrg_1_ValueID,
4093 rs.SalesOrg_2_ValueID,
4094 rs.SalesOrg_3_ValueID,
4095 rs.SalesOrg_4_ValueID,
4096 rs.SalesOrg_5_ValueID,
4097 rs.SalesOrg_6_ValueID,
4098 rs.SalesOrg_7_ValueID,
4099 rs.SalesOrg_8_ValueID,
4100 rs.SalesOrg_9_ValueID,
4101 rs.SalesForce_0_ID,
4102 rs.SalesForce_1_ID,
4103 rs.SalesForce_2_ID,
4104 rs.SalesForce_3_ID,
4105 rs.SalesForce_4_ID,
4106 rs.SalesForce_5_ID,
4107 rs.SalesForce_6_ID,
4108 rs.SalesForce_7_ID,
4109 rs.SalesForce_8_ID,
4110 rs.SalesForce_9_ID,
4111 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
4112 FROM #tmpSORawSales rs
4113 LEFT JOIN MRCDMS..DMSViewInventoryItem inv
4114 ON rs.CompanyID = inv.CompanyID
4115 AND rs.InventoryID = inv.InventoryID
4116 LEFT JOIN #tmpRoute r
4117 ON r.CompanyID = rs.CompanyID
4118 AND r.SalespersonID = rs.SalespersonID
4119 AND r.ROUTE = rs.ROUTE
4120 AND rs.OrderDate
4121 BETWEEN r.EffectiveDate AND r.ExpirationDate
4122 WHERE rs.OrderDate
4123 BETWEEN @_TempFromDate AND @_TempToDate
4124 AND rs.OrderType IN ( (CASE
4125 WHEN @_ExcludeReturnOrder = 0 THEN
4126 'CM'
4127 ELSE
4128 ''
4129 END
4130 ), 'SO', 'IN'
4131 )
4132 AND
4133 (
4134 ISNULL(@_GroupID, '') = ''
4135 OR
4136 (
4137 EXISTS
4138 (
4139 SELECT TOP 1
4140 *
4141 FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
4142 WHERE igd.CompanyID = @_CompanyID
4143 AND igd.CodegGroupProd = @_GroupID
4144 AND igd.InventoryID = rs.InventoryID
4145 )
4146 OR EXISTS
4147 (
4148 SELECT *
4149 FROM MRCDMS..DMSKPIGroupProductSalesDetailGroup gr
4150 WHERE gr.CompanyID = @_CompanyID
4151 AND gr.CodegGroupProd = @_GroupID
4152 AND inv.[HierarchyID] = gr.[HierarchyID]
4153 OR gr.Attribute0 = inv.Attribute0ID
4154 OR gr.Attribute1 = inv.Attribute1ID
4155 OR gr.Attribute2 = inv.Attribute2ID
4156 OR gr.Attribute3 = inv.Attribute3ID
4157 OR gr.Attribute4 = inv.Attribute4ID
4158 OR gr.Attribute5 = inv.Attribute5ID
4159 OR gr.Attribute6 = inv.Attribute6ID
4160 OR gr.Attribute7 = inv.Attribute7ID
4161 OR gr.Attribute8 = inv.Attribute8ID
4162 OR gr.Attribute9 = inv.Attribute9ID
4163 )
4164 )
4165 )
4166 AND rs.ShippedQty >= @_MinSKUVolume
4167 AND ABS(rs.TranAmt) >= @_MinSKURevenue
4168 --AND rs.ReasonCode = ( CASE
4169 -- WHEN @_IncludePromotionItem = 1
4170 -- THEN 'ISSKM'
4171 -- WHEN @_IncludeWarrantyItem = 1
4172 -- THEN 'ISSBH'
4173 -- ELSE 'ISS'
4174 -- END )
4175 GROUP BY rs.CompanyID,
4176 rs.SalespersonID,
4177 r.SalesForceID,
4178 r.SalesAreaID,
4179 rs.ROUTE,
4180 rs.OrderDate,
4181 rs.SourceType,
4182 rs.CustomerID,
4183 rs.CustomerLocationID,
4184 rs.IsValidDate,
4185 rs.IsValidDistance,
4186 rs.IsRevenueDeduction,
4187 rs.SalesOrg_0_ValueID,
4188 rs.SalesOrg_1_ValueID,
4189 rs.SalesOrg_2_ValueID,
4190 rs.SalesOrg_3_ValueID,
4191 rs.SalesOrg_4_ValueID,
4192 rs.SalesOrg_5_ValueID,
4193 rs.SalesOrg_6_ValueID,
4194 rs.SalesOrg_7_ValueID,
4195 rs.SalesOrg_8_ValueID,
4196 rs.SalesOrg_9_ValueID,
4197 rs.SalesForce_0_ID,
4198 rs.SalesForce_1_ID,
4199 rs.SalesForce_2_ID,
4200 rs.SalesForce_3_ID,
4201 rs.SalesForce_4_ID,
4202 rs.SalesForce_5_ID,
4203 rs.SalesForce_6_ID,
4204 rs.SalesForce_7_ID,
4205 rs.SalesForce_8_ID,
4206 rs.SalesForce_9_ID;
4207
4208 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
4209 'Hie3',
4210 NULL,
4211 NULL,
4212 NULL,
4213 @_IsValiDateValidDistance,
4214 @_IsValidDateInvalidDistance,
4215 @_IsInvalidDate,
4216 @_IsDistributorSell;
4217
4218 INSERT INTO #tmpTotalLine
4219 SELECT *
4220 FROM dbo.DMSBLObjectKPITemp;
4221
4222 --Step 1 for Sales Rep, step 2 for sales Sup
4223 IF @_step = 1
4224 BEGIN
4225 INSERT INTO #tmpKPI
4226 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
4227 CompanyID = tmp.CompanyID,
4228 @_KPIID,
4229 @_KPICD,
4230 @_KPIDescr,
4231 @_Source,
4232 @_Type,
4233 @_GroupBy,
4234 @_GroupID,
4235 @_Formula,
4236 @_Template,
4237 @_KPIPeriodNbr,
4238 ISNULL(
4239 @_RefNbr,
4240 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
4241 + CONVERT(VARCHAR(20), @_ToDate, 111)
4242 ),
4243 @_Period,
4244 @_FromDate,
4245 @_ToDate,
4246 ObjectID = tmp.EmployeeID,
4247 TempObjectID = ISNULL(kpid.ObjectID, 0),
4248 'S',
4249 tmp.ObjectAssignment,
4250 kpid.TargetsSuggest, ---- temp
4251 kpid.TargetsAssigned, ---- temp
4252 kpid.TargetsSuggest,
4253 kpid.TargetsAssigned,
4254 kpia.Actual1,
4255 kpia.Actual2,
4256 kpia.Actual3,
4257 kpia.Actual4,
4258 kpia.Actual
4259 FROM
4260 (
4261 SELECT sf.CompanyID,
4262 sf.EmployeeID,
4263 ObjectAssignment = CASE
4264 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
4265 r.ROUTE
4266 ELSE
4267 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
4268 END
4269 FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
4270 JOIN #tmpSalesForce sf WITH (NOLOCK)
4271 ON sf.CompanyID = objectPeriod.CompanyID
4272 AND sf.SFHierachyID = objectPeriod.KPIObjectID
4273 LEFT JOIN #tmpRoute r
4274 ON r.CompanyID = sf.CompanyID
4275 AND
4276 (
4277 r.SalespersonID = sf.EmployeeID
4278 OR r.SalesForceID = sf.EmployeeID
4279 )
4280 WHERE objectPeriod.CompanyID = @_CompanyID
4281 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
4282 AND
4283 (
4284 @_SalesAreaID IS NULL
4285 OR @_Level = -1
4286 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
4287 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
4288 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
4289 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
4290 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
4291 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
4292 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
4293 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
4294 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
4295 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
4296 )
4297 GROUP BY sf.CompanyID,
4298 sf.EmployeeID,
4299 CASE
4300 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
4301 r.ROUTE
4302 ELSE
4303 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
4304 END
4305 ) tmp
4306 LEFT JOIN
4307 (
4308 SELECT kpid.CompanyID,
4309 kpid.ObjectID,
4310 kpid.KPIPeriodNbr,
4311 kpid.FromDate,
4312 kpid.ToDate,
4313 kpid.TargetsSuggest,
4314 kpid.TargetsAssigned
4315 FROM #tmpKPIDetail kpid
4316 WHERE kpid.CodeListSalesID = @_KPIID
4317 AND
4318 (
4319 @_RefNbr LIKE 'Temp-%'
4320 OR kpid.RefNbr = @_RefNbr
4321 )
4322 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
4323 AND kpid.FromDate = @_FromDate
4324 AND kpid.ToDate = @_ToDate
4325 ) kpid
4326 ON kpid.CompanyID = tmp.CompanyID
4327 AND kpid.ObjectID = tmp.EmployeeID
4328 LEFT JOIN
4329 (
4330 SELECT kpipc.CompanyID,
4331 kpipc.ObjectID,
4332 kpipc.ObjectAssignment,
4333 Actual1 = CASE
4334 WHEN @_IsValiDateValidDistance = 1
4335 AND ISNULL(kpipc.Actual1, 0) > 0 THEN
4336 kpisku.Actual1 / kpipc.Actual1
4337 ELSE
4338 0
4339 END,
4340 Actual2 = CASE
4341 WHEN @_IsValidDateInvalidDistance = 1
4342 AND ISNULL(kpipc.Actual2, 0) > 0 THEN
4343 kpisku.Actual2 / kpipc.Actual2
4344 ELSE
4345 0
4346 END,
4347 Actual3 = CASE
4348 WHEN @_IsInvalidDate = 1
4349 AND ISNULL(kpipc.Actual3, 0) > 0 THEN
4350 kpisku.Actual3 / kpipc.Actual3
4351 ELSE
4352 0
4353 END,
4354 Actual4 = CASE
4355 WHEN @_IsDistributorSell = 1
4356 AND ISNULL(kpipc.Actual4, 0) > 0 THEN
4357 kpisku.Actual4 / kpipc.Actual4
4358 ELSE
4359 0
4360 END,
4361 Actual = CASE
4362 WHEN ISNULL(kpipc.Actual, 0) > 0 THEN
4363 kpisku.Actual / kpipc.Actual
4364 ELSE
4365 0
4366 END
4367 FROM #tmpTotalPC kpipc
4368 JOIN #tmpTotalLine kpisku
4369 ON kpisku.CompanyID = kpipc.CompanyID
4370 AND kpisku.ObjectID = kpipc.ObjectID
4371 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
4372 JOIN #tmpSalesForce sf
4373 ON sf.CompanyID = kpipc.CompanyID
4374 AND sf.EmployeeID = kpipc.ObjectID
4375 AND sf.TerritoryType = 'S'
4376 ) kpia
4377 ON kpia.CompanyID = tmp.CompanyID
4378 AND kpia.ObjectID = tmp.EmployeeID
4379 AND kpia.ObjectAssignment = tmp.ObjectAssignment
4380 WHERE kpid.ObjectID IS NOT NULL;
4381 END;
4382 ELSE IF @_step = 2
4383 BEGIN
4384 INSERT INTO #tmpKPI
4385 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
4386 tmp.CompanyID,
4387 @_KPIID,
4388 @_KPICD,
4389 @_KPIDescr,
4390 @_Source,
4391 @_Type,
4392 @_GroupBy,
4393 @_GroupID,
4394 @_Formula,
4395 @_Template,
4396 @_KPIPeriodNbr,
4397 ISNULL(
4398 @_RefNbr,
4399 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
4400 + CONVERT(VARCHAR(20), @_ToDate, 111)
4401 ),
4402 @_Period,
4403 @_FromDate,
4404 @_ToDate,
4405 ObjectID = tmp.EmployeeID,
4406 TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
4407 'S',
4408 tmp.ObjectAssignment,
4409 TargetsSuggest = MAX(kpid.TargetsSuggest), ---- temp
4410 TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
4411 TargetsSuggest = MAX(kpid.TargetsSuggest),
4412 TargetsAssigned = MAX(kpid.TargetsAssigned),
4413 Actual1 = CASE
4414 WHEN @_IsValiDateValidDistance = 1
4415 AND SUM(kpi.PC1) > 0 THEN
4416 SUM(kpi.SKU1) / SUM(kpi.PC1)
4417 ELSE
4418 0
4419 END,
4420 Actual2 = CASE
4421 WHEN @_IsValidDateInvalidDistance = 1
4422 AND SUM(kpi.PC2) > 0 THEN
4423 SUM(kpi.SKU2) / SUM(kpi.PC2)
4424 ELSE
4425 0
4426 END,
4427 Actual3 = CASE
4428 WHEN @_IsInvalidDate = 1
4429 AND SUM(kpi.PC3) > 0 THEN
4430 SUM(kpi.SKU3) / SUM(kpi.PC3)
4431 ELSE
4432 0
4433 END,
4434 Actual4 = CASE
4435 WHEN @_IsDistributorSell = 1
4436 AND SUM(kpi.PC4) > 0 THEN
4437 SUM(kpi.SKU4) / SUM(kpi.PC4)
4438 ELSE
4439 0
4440 END,
4441 Actual = CASE
4442 WHEN SUM(kpi.PC) > 0 THEN
4443 SUM(kpi.SKU) / SUM(kpi.PC)
4444 ELSE
4445 0
4446 END
4447 FROM
4448 (
4449 SELECT sf.CompanyID,
4450 sf.EmployeeID,
4451 ObjectAssignment = CASE
4452 WHEN sf.TerritoryType = 'D' THEN
4453 r.ROUTE
4454 ELSE
4455 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
4456 END,
4457 sf.SalesOrg_0_ValueID
4458 FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
4459 JOIN #tmpSalesForce sf WITH (NOLOCK)
4460 ON sf.CompanyID = objectPeriod.CompanyID
4461 AND sf.SFHierachyID = objectPeriod.KPIObjectID
4462 LEFT JOIN #tmpRoute r
4463 ON r.CompanyID = sf.CompanyID
4464 AND r.SalesForceID = sf.EmployeeID
4465 WHERE objectPeriod.CompanyID = @_CompanyID
4466 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
4467 AND sf.TerritoryType != 'S'
4468 AND
4469 (
4470 @_SalesAreaID IS NULL
4471 OR @_Level = -1
4472 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
4473 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
4474 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
4475 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
4476 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
4477 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
4478 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
4479 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
4480 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
4481 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
4482 )
4483 GROUP BY sf.CompanyID,
4484 sf.EmployeeID,
4485 CASE
4486 WHEN sf.TerritoryType = 'D' THEN
4487 r.ROUTE
4488 ELSE
4489 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
4490 END,
4491 sf.SalesOrg_0_ValueID
4492 ) tmp
4493 LEFT JOIN
4494 (
4495 SELECT kpid.CompanyID,
4496 kpid.ObjectID,
4497 kpid.KPIPeriodNbr,
4498 kpid.FromDate,
4499 kpid.ToDate,
4500 kpid.TargetsSuggest,
4501 kpid.TargetsAssigned
4502 FROM #tmpKPIDetail kpid
4503 WHERE kpid.CodeListSalesID = @_KPIID
4504 AND
4505 (
4506 @_RefNbr LIKE 'Temp-%'
4507 OR kpid.RefNbr = @_RefNbr
4508 )
4509 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
4510 AND kpid.FromDate = @_FromDate
4511 AND kpid.ToDate = @_ToDate
4512 ) kpid
4513 ON kpid.CompanyID = tmp.CompanyID
4514 AND kpid.ObjectID = tmp.EmployeeID
4515 LEFT JOIN
4516 (
4517 SELECT DISTINCT
4518 kpipc.CompanyID,
4519 kpipc.ObjectID,
4520 kpipc.ObjectAssignment,
4521 SKU1 = kpisku.Actual1,
4522 SKU2 = kpisku.Actual2,
4523 SKU3 = kpisku.Actual3,
4524 SKU4 = kpisku.Actual4,
4525 SKU = kpisku.Actual,
4526 PC1 = kpipc.Actual1,
4527 PC2 = kpipc.Actual2,
4528 PC3 = kpipc.Actual3,
4529 PC4 = kpipc.Actual4,
4530 PC = kpipc.Actual,
4531 r.ROUTE,
4532 r.SalesAreaID
4533 FROM #tmpTotalPC kpipc
4534 JOIN #tmpTotalLine kpisku
4535 ON kpisku.CompanyID = kpipc.CompanyID
4536 AND kpisku.ObjectID = kpipc.ObjectID
4537 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
4538 JOIN #tmpSalesForce sf
4539 ON sf.CompanyID = kpipc.CompanyID
4540 AND sf.EmployeeID = kpipc.ObjectID
4541 AND sf.TerritoryType = 'S'
4542 JOIN #tmpRoute r
4543 ON r.CompanyID = sf.CompanyID
4544 AND r.SalespersonID = sf.EmployeeID
4545 ) kpi
4546 ON kpi.CompanyID = tmp.CompanyID
4547 AND
4548 (
4549 kpi.ROUTE = tmp.ObjectAssignment
4550 OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
4551 )
4552 WHERE kpid.ObjectID IS NOT NULL
4553 GROUP BY tmp.CompanyID,
4554 tmp.EmployeeID,
4555 tmp.ObjectAssignment;
4556 END;
4557 SET @_Min = @_Min + 1;
4558 END;
4559
4560 SELECT @_step = @_step + 1;
4561 END;
4562 END; ---- KPI BPPC
4563
4564 BEGIN ---- Tính KPI DropSize by revnue or quantity
4565 ---- Formula: Total (Revenue or Volumn) / Quantity of PC
4566 ---- DROPSIZEV: drop size by qty
4567 ---- DROPSIZER: drop size by revenue
4568 DELETE FROM #tmpKPIList;
4569
4570 INSERT INTO #tmpKPIList
4571 SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
4572 *
4573 FROM
4574 (
4575 SELECT CompanyID,
4576 CodeListSalesID,
4577 CodeListSalesCD,
4578 RefNbr,
4579 KPIPeriodNbr,
4580 Descr = MAX(Descr),
4581 SOURCE = MAX(SOURCE),
4582 TYPE = MAX(TYPE),
4583 GroupBy = MAX(GroupBy),
4584 GroupID = MAX(GroupID),
4585 Formula = MAX(Formula),
4586 PERIOD = MAX(PERIOD),
4587 SalesOrgID = MAX(SalesOrgID),
4588 SalesAreaID = MAX(SalesAreaID),
4589 LEVEL = MAX(LEVEL),
4590 Template = MAX(Template),
4591 FromDate,
4592 ToDate,
4593 ObjectType = MAX(ObjectType),
4594 CorrectRouteAndCorrectCoordinates,
4595 CorrectRouteAndIncorrectCoordinates,
4596 IncorrectRoute,
4597 DistributorsSell,
4598 MinSKUVolume,
4599 MinSKURevenue,
4600 ExcludeReturnOrder
4601 FROM #tmpKPIDetail
4602 WHERE Template IN ( 'DROPSIZER', 'DROPSIZEV' )
4603 AND ObjectType = 'S'
4604 GROUP BY CompanyID,
4605 CodeListSalesID,
4606 CodeListSalesCD,
4607 RefNbr,
4608 KPIPeriodNbr,
4609 FromDate,
4610 ToDate,
4611 CorrectRouteAndCorrectCoordinates,
4612 CorrectRouteAndIncorrectCoordinates,
4613 IncorrectRoute,
4614 DistributorsSell,
4615 MinSKUVolume,
4616 MinSKURevenue,
4617 ExcludeReturnOrder
4618 ) tmp;
4619
4620 SELECT @_Max = MAX(RowNumber)
4621 FROM #tmpKPIList;
4622
4623 SELECT @_step = 1;
4624 WHILE @_step <= 2
4625 BEGIN
4626 SELECT @_Min = 1;
4627 WHILE @_Min <= @_Max
4628 BEGIN
4629 ---- Xóa dữ liệu SM để tính lại đối với KPI mới
4630 DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
4631 DELETE FROM dbo.DMSBLObjectKPITemp;
4632 DELETE FROM #tmpTotalPC;
4633 DELETE FROM #tmpTotalLine;
4634
4635 ---- Lấy thông tin công thức KPI
4636 SELECT @_KPIID = CodeListSalesID,
4637 @_KPICD = CodeListSalesCD,
4638 @_KPIDescr = Descr,
4639 @_RefNbr = RefNbr,
4640 @_KPIPeriodNbr = KPIPeriodNbr,
4641 @_Source = SOURCE,
4642 @_Type = TYPE,
4643 @_GroupBy = ISNULL(GroupBy, 'A'),
4644 @_GroupID = GroupID,
4645 @_Formula = Formula,
4646 @_Template = Template,
4647 @_Period = PERIOD,
4648 @_SalesAreaID = SalesAreaID,
4649 @_Level = LEVEL,
4650 @_FromDate = FromDate,
4651 @_ToDate = ToDate,
4652 @_ObjectType = ObjectType,
4653 @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
4654 @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
4655 @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
4656 @_IsDistributorSell = ISNULL(DistributorsSell, 0),
4657 @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
4658 @_MinSKURevenue = MinSKURevenue,
4659 @_MinSKUVolume = MinSKUVolume
4660 FROM #tmpKPIList
4661 WHERE RowNumber = @_Min;
4662
4663 IF @_TempFromDate IS NULL
4664 OR @_TempToDate IS NULL
4665 OR @_TempFromDate != @_FromDate
4666 OR @_TempToDate != @_ToDate
4667 BEGIN
4668 DELETE FROM #tmpSalesTerritory;
4669 DELETE FROM #tmpRoute;
4670 DELETE FROM #tmpSalesForce;
4671
4672 ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
4673 INSERT INTO #tmpSalesTerritory
4674 SELECT *
4675 FROM
4676 (
4677 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
4678 st.SalesOrg_0_ValueID
4679 ORDER BY st.EffectiveDate DESC
4680 ),
4681 st.CompanyID,
4682 st.SalesOrg_0_ValueID,
4683 st.SalesOrg_1_ValueID,
4684 st.SalesOrg_2_ValueID,
4685 st.SalesOrg_3_ValueID,
4686 st.SalesOrg_4_ValueID,
4687 st.SalesOrg_5_ValueID,
4688 st.SalesOrg_6_ValueID,
4689 st.SalesOrg_7_ValueID,
4690 st.SalesOrg_8_ValueID,
4691 st.SalesOrg_9_ValueID
4692 FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
4693 WHERE st.CompanyID = @_CompanyID
4694 AND
4695 (
4696 @_FromDate
4697 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
4698 OR @_ToDate
4699 BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
4700 OR st.EffectiveDate
4701 BETWEEN @_FromDate AND @_ToDate
4702 )
4703 ) tmp
4704 WHERE tmp.RowNumber = 1;
4705
4706 ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
4707 INSERT INTO #tmpRoute
4708 SELECT tmp.CompanyID,
4709 tmp.SalespersonID,
4710 tmp.ROUTE,
4711 tmp.SalesForceID,
4712 tmp.SalesAreaID,
4713 tmp.EffectiveDate,
4714 tmp.ExpirationDate,
4715 st.SalesOrg_0_ValueID,
4716 st.SalesOrg_1_ValueID,
4717 st.SalesOrg_2_ValueID,
4718 st.SalesOrg_3_ValueID,
4719 st.SalesOrg_4_ValueID,
4720 st.SalesOrg_5_ValueID,
4721 st.SalesOrg_6_ValueID,
4722 st.SalesOrg_7_ValueID,
4723 st.SalesOrg_8_ValueID,
4724 st.SalesOrg_9_ValueID
4725 FROM
4726 (
4727 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
4728 rs.SalespersonID
4729 ORDER BY rs.EffectiveDate DESC
4730 ),
4731 rs.CompanyID,
4732 rs.SalespersonID,
4733 ROUTE = rs.RouteCD,
4734 rs.SalesForceID,
4735 sph.SalesAreaID,
4736 rs.EffectiveDate,
4737 ExpirationDate = ISNULL(rs.EndDate, GETDATE())
4738 FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
4739 JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
4740 WHERE rs.CompanyID = @_CompanyID
4741 AND
4742 (
4743 @_FromDate
4744 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
4745 OR @_ToDate
4746 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
4747 OR rs.EffectiveDate
4748 BETWEEN @_FromDate AND @_ToDate
4749 )
4750 UNION ALL
4751 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
4752 rs.PreviousSalesMan
4753 ORDER BY rs.EffectiveDate DESC
4754 ),
4755 rs.CompanyID,
4756 SalespersonID = rs.PreviousSalesMan,
4757 ROUTE = rs.RouteCD,
4758 rs.SalesForceID,
4759 sph.SalesAreaID,
4760 rs.StartDatePre,
4761 rs.EndDatePre
4762 FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
4763 JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
4764 WHERE rs.CompanyID = @_CompanyID
4765 AND
4766 (
4767 @_FromDate
4768 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
4769 OR @_ToDate
4770 BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
4771 OR rs.EffectiveDate
4772 BETWEEN @_FromDate AND @_ToDate
4773 )
4774 AND rs.PreviousSalesMan IS NOT NULL
4775 ) tmp
4776 JOIN #tmpSalesTerritory st
4777 ON st.CompanyID = tmp.CompanyID
4778 AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
4779 WHERE tmp.RowNumber = 1;
4780
4781 ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
4782 INSERT INTO #tmpSalesForce
4783 SELECT tmp.CompanyID,
4784 tmp.EmployeeID,
4785 tmp.SFHierachyID,
4786 tmp.TerritoryType,
4787 tmp.SalesOrgValueID,
4788 st.SalesOrg_0_ValueID,
4789 st.SalesOrg_1_ValueID,
4790 st.SalesOrg_2_ValueID,
4791 st.SalesOrg_3_ValueID,
4792 st.SalesOrg_4_ValueID,
4793 st.SalesOrg_5_ValueID,
4794 st.SalesOrg_6_ValueID,
4795 st.SalesOrg_7_ValueID,
4796 st.SalesOrg_8_ValueID,
4797 st.SalesOrg_9_ValueID
4798 FROM
4799 (
4800 SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
4801 sf.EmployeeID
4802 ORDER BY sf.EffectiveDate DESC
4803 ),
4804 sf.CompanyID,
4805 sf.EmployeeID,
4806 sf.SFHierachyID,
4807 sf.TerritoryType,
4808 sf.SalesOrgValueID
4809 FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
4810 WHERE sf.CompanyID = @_CompanyID
4811 AND
4812 (
4813 @_FromDate
4814 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
4815 OR @_ToDate
4816 BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
4817 OR sf.EffectiveDate
4818 BETWEEN @_FromDate AND @_ToDate
4819 )
4820 ) tmp
4821 LEFT JOIN #tmpSalesTerritory st
4822 ON st.CompanyID = tmp.CompanyID
4823 AND
4824 (
4825 st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
4826 OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
4827 OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
4828 OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
4829 OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
4830 OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
4831 OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
4832 OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
4833 OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
4834 OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
4835 )
4836 WHERE tmp.RowNumber = 1;
4837
4838 SET @_TempFromDate = @_FromDate;
4839 SET @_TempToDate = @_ToDate;
4840 END;
4841
4842 ---- Tính KPI PC tạm để tính
4843 INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
4844 SELECT rs.CompanyID,
4845 rs.SalespersonID,
4846 r.SalesForceID,
4847 r.SalesAreaID,
4848 rs.ROUTE,
4849 rs.OrderDate,
4850 rs.SourceType,
4851 rs.CustomerID,
4852 rs.CustomerLocationID,
4853 rs.IsValidDate,
4854 rs.IsValidDistance,
4855 Multiply = CASE
4856 WHEN rs.IsRevenueDeduction = 1 THEN
4857 -1
4858 ELSE
4859 1
4860 END,
4861 PC = COUNT(DISTINCT rs.CustomerID),
4862 SKU = COUNT(DISTINCT rs.InventoryID),
4863 TranAmt = SUM(rs.TranAmt),
4864 ShippedQty = SUM(rs.ShippedQty),
4865 rs.SalesOrg_0_ValueID,
4866 rs.SalesOrg_1_ValueID,
4867 rs.SalesOrg_2_ValueID,
4868 rs.SalesOrg_3_ValueID,
4869 rs.SalesOrg_4_ValueID,
4870 rs.SalesOrg_5_ValueID,
4871 rs.SalesOrg_6_ValueID,
4872 rs.SalesOrg_7_ValueID,
4873 rs.SalesOrg_8_ValueID,
4874 rs.SalesOrg_9_ValueID,
4875 rs.SalesForce_0_ID,
4876 rs.SalesForce_1_ID,
4877 rs.SalesForce_2_ID,
4878 rs.SalesForce_3_ID,
4879 rs.SalesForce_4_ID,
4880 rs.SalesForce_5_ID,
4881 rs.SalesForce_6_ID,
4882 rs.SalesForce_7_ID,
4883 rs.SalesForce_8_ID,
4884 rs.SalesForce_9_ID,
4885 Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
4886 FROM #tmpSORawSales rs
4887 LEFT JOIN MRCDMS..DMSViewInventoryItem inv
4888 ON rs.CompanyID = inv.CompanyID
4889 AND rs.InventoryID = inv.InventoryID
4890 LEFT JOIN #tmpRoute r
4891 ON r.CompanyID = rs.CompanyID
4892 AND r.SalespersonID = rs.SalespersonID
4893 AND r.ROUTE = rs.ROUTE
4894 AND rs.OrderDate
4895 BETWEEN r.EffectiveDate AND r.ExpirationDate
4896 WHERE rs.OrderDate
4897 BETWEEN @_TempFromDate AND @_TempToDate
4898 AND rs.OrderType IN ( (CASE
4899 WHEN @_ExcludeReturnOrder = 0 THEN
4900 'CM'
4901 ELSE
4902 ''
4903 END
4904 ), 'SO'
4905 )
4906 AND
4907 (
4908 ISNULL(@_GroupID, '') = ''
4909 OR
4910 (
4911 EXISTS
4912 (
4913 SELECT TOP 1
4914 *
4915 FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
4916 WHERE igd.CompanyID = @_CompanyID
4917 AND igd.CodegGroupProd = @_GroupID
4918 AND igd.InventoryID = rs.InventoryID
4919 )
4920 OR EXISTS
4921 (
4922 SELECT *
4923 FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
4924 WHERE gr.CompanyID = @_CompanyID
4925 AND gr.CodegGroupProd = @_GroupID
4926 AND inv.[HierarchyID] = gr.[HierarchyID]
4927 OR gr.Attribute0 = inv.Attribute0ID
4928 OR gr.Attribute1 = inv.Attribute1ID
4929 OR gr.Attribute2 = inv.Attribute2ID
4930 OR gr.Attribute3 = inv.Attribute3ID
4931 OR gr.Attribute4 = inv.Attribute4ID
4932 OR gr.Attribute5 = inv.Attribute5ID
4933 OR gr.Attribute6 = inv.Attribute6ID
4934 OR gr.Attribute7 = inv.Attribute7ID
4935 OR gr.Attribute8 = inv.Attribute8ID
4936 OR gr.Attribute9 = inv.Attribute9ID
4937 )
4938 )
4939 )
4940 AND rs.ShippedQty >= @_MinSKUVolume
4941 AND ABS(rs.TranAmt) >= @_MinSKURevenue
4942 --AND rs.ReasonCode = ( CASE
4943 -- WHEN @_IncludePromotionItem = 1
4944 -- THEN 'ISSKM'
4945 -- WHEN @_IncludeWarrantyItem = 1
4946 -- THEN 'ISSBH'
4947 -- ELSE 'ISS'
4948 -- END )
4949 GROUP BY rs.CompanyID,
4950 rs.SalespersonID,
4951 r.SalesForceID,
4952 r.SalesAreaID,
4953 rs.ROUTE,
4954 rs.OrderDate,
4955 rs.SourceType,
4956 rs.CustomerID,
4957 rs.CustomerLocationID,
4958 rs.IsValidDate,
4959 rs.IsValidDistance,
4960 rs.IsRevenueDeduction,
4961 rs.SalesOrg_0_ValueID,
4962 rs.SalesOrg_1_ValueID,
4963 rs.SalesOrg_2_ValueID,
4964 rs.SalesOrg_3_ValueID,
4965 rs.SalesOrg_4_ValueID,
4966 rs.SalesOrg_5_ValueID,
4967 rs.SalesOrg_6_ValueID,
4968 rs.SalesOrg_7_ValueID,
4969 rs.SalesOrg_8_ValueID,
4970 rs.SalesOrg_9_ValueID,
4971 rs.SalesForce_0_ID,
4972 rs.SalesForce_1_ID,
4973 rs.SalesForce_2_ID,
4974 rs.SalesForce_3_ID,
4975 rs.SalesForce_4_ID,
4976 rs.SalesForce_5_ID,
4977 rs.SalesForce_6_ID,
4978 rs.SalesForce_7_ID,
4979 rs.SalesForce_8_ID,
4980 rs.SalesForce_9_ID;
4981
4982
4983
4984 DECLARE @tempTemplate nvarchar(50) = (
4985 SELECT CASE
4986 WHEN @_Template = 'DROPSIZER' THEN
4987 'REVENUE'
4988 WHEN @_Template = 'DROPSIZEV' THEN
4989 'QUANTITY'
4990 END
4991 );
4992 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
4993 @tempTemplate,
4994 NULL,
4995 NULL,
4996 NULL,
4997 @_IsValiDateValidDistance,
4998 @_IsValidDateInvalidDistance,
4999 @_IsInvalidDate,
5000 @_IsDistributorSell;
5001
5002 DELETE FROM dbo.DMSBLObjectKPITemp;
5003
5004 INSERT INTO #tmpTotalLine
5005 SELECT *
5006 FROM dbo.DMSBLObjectKPITemp;
5007 EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
5008 'PC',
5009 NULL,
5010 NULL,
5011 NULL,
5012 @_IsValiDateValidDistance,
5013 @_IsValidDateInvalidDistance,
5014 @_IsInvalidDate,
5015 @_IsDistributorSell;
5016
5017 INSERT INTO #tmpTotalPC
5018 SELECT *
5019 FROM dbo.DMSBLObjectKPITemp;
5020 ---- Kết thúc tính KPI ra các tham số để tính KPI
5021 ---- Bắt đầu tính data từ các cấp dự trên số liệu param đã execute ở 2 bảng dữ liệu tạm
5022
5023 IF @_step = 1
5024 BEGIN
5025 INSERT INTO #tmpKPI
5026 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
5027 CompanyID = tmp.CompanyID,
5028 @_KPIID,
5029 @_KPICD,
5030 @_KPIDescr,
5031 @_Source,
5032 @_Type,
5033 @_GroupBy,
5034 @_GroupID,
5035 @_Formula,
5036 @_Template,
5037 @_KPIPeriodNbr,
5038 ISNULL(
5039 @_RefNbr,
5040 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
5041 + CONVERT(VARCHAR(20), @_ToDate, 111)
5042 ),
5043 @_Period,
5044 @_FromDate,
5045 @_ToDate,
5046 ObjectID = tmp.EmployeeID,
5047 TempObjectID = ISNULL(kpid.ObjectID, 0),
5048 'S',
5049 tmp.ObjectAssignment,
5050 kpid.TargetsSuggest, ---- temp
5051 kpid.TargetsAssigned, ---- temp
5052 kpid.TargetsSuggest,
5053 kpid.TargetsAssigned,
5054 kpia.Actual1,
5055 kpia.Actual2,
5056 kpia.Actual3,
5057 kpia.Actual4,
5058 kpia.Actual
5059 FROM
5060 (
5061 SELECT sf.CompanyID,
5062 sf.EmployeeID,
5063 ObjectAssignment = CASE
5064 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
5065 r.ROUTE
5066 ELSE
5067 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
5068 END
5069 FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
5070 JOIN #tmpSalesForce sf WITH (NOLOCK)
5071 ON sf.CompanyID = objectPeriod.CompanyID
5072 AND sf.SFHierachyID = objectPeriod.KPIObjectID
5073 LEFT JOIN #tmpRoute r
5074 ON r.CompanyID = sf.CompanyID
5075 AND
5076 (
5077 r.SalespersonID = sf.EmployeeID
5078 OR r.SalesForceID = sf.EmployeeID
5079 )
5080 WHERE objectPeriod.CompanyID = @_CompanyID
5081 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
5082 AND
5083 (
5084 @_SalesAreaID IS NULL
5085 OR @_Level = -1
5086 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
5087 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
5088 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
5089 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
5090 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
5091 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
5092 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
5093 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
5094 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
5095 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
5096 )
5097 GROUP BY sf.CompanyID,
5098 sf.EmployeeID,
5099 CASE
5100 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
5101 r.ROUTE
5102 ELSE
5103 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
5104 END
5105 ) tmp
5106 LEFT JOIN
5107 (
5108 SELECT kpid.CompanyID,
5109 kpid.ObjectID,
5110 kpid.KPIPeriodNbr,
5111 kpid.FromDate,
5112 kpid.ToDate,
5113 kpid.TargetsSuggest,
5114 kpid.TargetsAssigned
5115 FROM #tmpKPIDetail kpid
5116 WHERE kpid.CodeListSalesID = @_KPIID
5117 AND
5118 (
5119 @_RefNbr LIKE 'Temp-%'
5120 OR kpid.RefNbr = @_RefNbr
5121 )
5122 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
5123 AND kpid.FromDate = @_FromDate
5124 AND kpid.ToDate = @_ToDate
5125 ) kpid
5126 ON kpid.CompanyID = tmp.CompanyID
5127 AND kpid.ObjectID = tmp.EmployeeID
5128 LEFT JOIN
5129 (
5130 SELECT kpipc.CompanyID,
5131 kpipc.ObjectID,
5132 kpipc.ObjectAssignment,
5133 Actual1 = CASE
5134 WHEN @_IsValiDateValidDistance = 1
5135 AND ISNULL(kpipc.Actual1, 0) > 0 THEN
5136 kpisku.Actual1 / kpipc.Actual1
5137 ELSE
5138 0
5139 END,
5140 Actual2 = CASE
5141 WHEN @_IsValidDateInvalidDistance = 1
5142 AND ISNULL(kpipc.Actual2, 0) > 0 THEN
5143 kpisku.Actual2 / kpipc.Actual2
5144 ELSE
5145 0
5146 END,
5147 Actual3 = CASE
5148 WHEN @_IsInvalidDate = 1
5149 AND ISNULL(kpipc.Actual3, 0) > 0 THEN
5150 kpisku.Actual3 / kpipc.Actual3
5151 ELSE
5152 0
5153 END,
5154 Actual4 = CASE
5155 WHEN @_IsDistributorSell = 1
5156 AND ISNULL(kpipc.Actual4, 0) > 0 THEN
5157 kpisku.Actual4 / kpipc.Actual4
5158 ELSE
5159 0
5160 END,
5161 Actual = CASE
5162 WHEN ISNULL(kpipc.Actual, 0) > 0 THEN
5163 kpisku.Actual / kpipc.Actual
5164 ELSE
5165 0
5166 END
5167 FROM #tmpTotalPC kpipc
5168 JOIN #tmpTotalLine kpisku
5169 ON kpisku.CompanyID = kpipc.CompanyID
5170 AND kpisku.ObjectID = kpipc.ObjectID
5171 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
5172 JOIN #tmpSalesForce sf
5173 ON sf.CompanyID = kpipc.CompanyID
5174 AND sf.EmployeeID = kpipc.ObjectID
5175 AND sf.TerritoryType = 'S'
5176 JOIN #tmpRoute r
5177 ON r.CompanyID = sf.CompanyID
5178 AND r.SalespersonID = sf.EmployeeID
5179 ) kpia
5180 ON kpia.CompanyID = tmp.CompanyID
5181 AND kpia.ObjectID = tmp.EmployeeID
5182 AND kpia.ObjectAssignment = tmp.ObjectAssignment
5183 WHERE kpid.ObjectID IS NOT NULL;
5184 END;
5185 ELSE IF @_step = 2
5186 BEGIN
5187 INSERT INTO #tmpKPI
5188 SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
5189 tmp.CompanyID,
5190 @_KPIID,
5191 @_KPICD,
5192 @_KPIDescr,
5193 @_Source,
5194 @_Type,
5195 @_GroupBy,
5196 @_GroupID,
5197 @_Formula,
5198 @_Template,
5199 @_KPIPeriodNbr,
5200 ISNULL(
5201 @_RefNbr,
5202 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
5203 + CONVERT(VARCHAR(20), @_ToDate, 111)
5204 ),
5205 @_Period,
5206 @_FromDate,
5207 @_ToDate,
5208 ObjectID = tmp.EmployeeID,
5209 TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
5210 'S',
5211 tmp.ObjectAssignment,
5212 TargetsSuggest = MAX(kpid.TargetsSuggest), ---- temp
5213 TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
5214 TargetsSuggest = MAX(kpid.TargetsSuggest),
5215 TargetsAssigned = MAX(kpid.TargetsAssigned),
5216 Actual1 = CASE
5217 WHEN @_IsValiDateValidDistance = 1
5218 AND SUM(kpi.PC1) > 0 THEN
5219 SUM(kpi.AC1) / SUM(kpi.PC1)
5220 ELSE
5221 0
5222 END,
5223 Actual2 = CASE
5224 WHEN @_IsValidDateInvalidDistance = 1
5225 AND SUM(kpi.PC2) > 0 THEN
5226 SUM(kpi.AC2) / SUM(kpi.PC2)
5227 ELSE
5228 0
5229 END,
5230 Actual3 = CASE
5231 WHEN @_IsInvalidDate = 1
5232 AND SUM(kpi.PC3) > 0 THEN
5233 SUM(kpi.AC3) / SUM(kpi.PC3)
5234 ELSE
5235 0
5236 END,
5237 Actual4 = CASE
5238 WHEN @_IsDistributorSell = 1
5239 AND SUM(kpi.PC4) > 0 THEN
5240 SUM(kpi.AC4) / SUM(kpi.PC4)
5241 ELSE
5242 0
5243 END,
5244 Actual = CASE
5245 WHEN SUM(kpi.PC) > 0 THEN
5246 SUM(kpi.AC) / SUM(kpi.PC)
5247 ELSE
5248 0
5249 END
5250 FROM
5251 (
5252 SELECT sf.CompanyID,
5253 sf.EmployeeID,
5254 ObjectAssignment = CASE
5255 WHEN sf.TerritoryType = 'D' THEN
5256 r.ROUTE
5257 ELSE
5258 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
5259 END,
5260 sf.SalesOrg_0_ValueID
5261 FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
5262 JOIN #tmpSalesForce sf WITH (NOLOCK)
5263 ON sf.CompanyID = objectPeriod.CompanyID
5264 AND sf.SFHierachyID = objectPeriod.KPIObjectID
5265 LEFT JOIN #tmpRoute r
5266 ON r.CompanyID = sf.CompanyID
5267 AND r.SalesForceID = sf.EmployeeID
5268 WHERE objectPeriod.CompanyID = @_CompanyID
5269 AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
5270 AND sf.TerritoryType != 'S'
5271 AND
5272 (
5273 @_SalesAreaID IS NULL
5274 OR @_Level = -1
5275 OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
5276 OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
5277 OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
5278 OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
5279 OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
5280 OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
5281 OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
5282 OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
5283 OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
5284 OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
5285 )
5286 GROUP BY sf.CompanyID,
5287 sf.EmployeeID,
5288 CASE
5289 WHEN sf.TerritoryType = 'D' THEN
5290 r.ROUTE
5291 ELSE
5292 CONVERT(VARCHAR(20), sf.SalesOrgValueID)
5293 END,
5294 sf.SalesOrg_0_ValueID
5295 ) tmp
5296 LEFT JOIN
5297 (
5298 SELECT kpid.CompanyID,
5299 kpid.ObjectID,
5300 kpid.KPIPeriodNbr,
5301 kpid.FromDate,
5302 kpid.ToDate,
5303 kpid.TargetsSuggest,
5304 kpid.TargetsAssigned
5305 FROM #tmpKPIDetail kpid
5306 WHERE kpid.CodeListSalesID = @_KPIID
5307 AND
5308 (
5309 @_RefNbr LIKE 'Temp-%'
5310 OR kpid.RefNbr = @_RefNbr
5311 )
5312 AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
5313 AND kpid.FromDate = @_FromDate
5314 AND kpid.ToDate = @_ToDate
5315 ) kpid
5316 ON kpid.CompanyID = tmp.CompanyID
5317 AND kpid.ObjectID = tmp.EmployeeID
5318 LEFT JOIN
5319 (
5320 SELECT DISTINCT
5321 kpipc.CompanyID,
5322 kpipc.ObjectID,
5323 kpipc.ObjectAssignment,
5324 AC1 = kpisku.Actual1,
5325 AC2 = kpisku.Actual2,
5326 AC3 = kpisku.Actual3,
5327 AC4 = kpisku.Actual4,
5328 AC = kpisku.Actual,
5329 PC1 = kpipc.Actual1,
5330 PC2 = kpipc.Actual2,
5331 PC3 = kpipc.Actual3,
5332 PC4 = kpipc.Actual4,
5333 PC = kpipc.Actual,
5334 r.ROUTE,
5335 r.SalesAreaID
5336 FROM #tmpTotalPC kpipc
5337 JOIN #tmpTotalLine kpisku
5338 ON kpisku.CompanyID = kpipc.CompanyID
5339 AND kpisku.ObjectID = kpipc.ObjectID
5340 AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
5341 JOIN #tmpSalesForce sf
5342 ON sf.CompanyID = kpipc.CompanyID
5343 AND sf.EmployeeID = kpipc.ObjectID
5344 AND sf.TerritoryType = 'S'
5345 JOIN #tmpRoute r
5346 ON r.CompanyID = sf.CompanyID
5347 AND r.SalespersonID = sf.EmployeeID
5348 ) kpi
5349 ON kpi.CompanyID = tmp.CompanyID
5350 AND
5351 (
5352 kpi.ROUTE = tmp.ObjectAssignment
5353 OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
5354 )
5355 WHERE kpid.ObjectID IS NOT NULL
5356 GROUP BY tmp.CompanyID,
5357 tmp.EmployeeID,
5358 tmp.ObjectAssignment;
5359 END;
5360 SET @_Min = @_Min + 1;
5361 END;
5362
5363 SELECT @_step = @_step + 1;
5364 END;
5365 END; ---- Tính KPI DropSize by revnue or quantity
5366
5367 UPDATE kpi
5368 SET kpi.TargetsSuggest = tmp.TargetsSuggest,
5369 kpi.TargetsAssigned = tmp.TargetsAssigned
5370 FROM #tmpKPI kpi
5371 JOIN
5372 (
5373 SELECT CompanyID,
5374 CodeListSalesID,
5375 KPIPeriodNbr,
5376 RefNbr,
5377 ObjectID,
5378 TargetsSuggest = SUM(TargetsSuggest),
5379 TargetsAssigned = MAX(TargetsAssigned)
5380 FROM #tmpKPI
5381 WHERE RowNumber > 1
5382 GROUP BY CompanyID,
5383 CodeListSalesID,
5384 KPIPeriodNbr,
5385 RefNbr,
5386 ObjectID
5387 ) tmp
5388 ON tmp.CompanyID = kpi.CompanyID
5389 AND tmp.CodeListSalesID = kpi.CodeListSalesID
5390 AND tmp.KPIPeriodNbr = kpi.KPIPeriodNbr
5391 AND tmp.RefNbr = kpi.RefNbr
5392 AND tmp.ObjectID = kpi.ObjectID
5393 WHERE kpi.RowNumber = 1;
5394
5395 INSERT INTO dbo.DMSBLKPI
5396 (
5397 BaselineDate,
5398 CompanyID,
5399 KPIID,
5400 KPICD,
5401 KPIDescr,
5402 SOURCE,
5403 TYPE,
5404 GroupBy,
5405 GroupID,
5406 Formula,
5407 Template,
5408 KPIPeriodNbr,
5409 RefNbr,
5410 PERIOD,
5411 FromDate,
5412 ToDate,
5413 ObjectID,
5414 ObjectType,
5415 ObjectAssignment,
5416 TargetsSuggest,
5417 TargetsAssigned,
5418 Actual1,
5419 Actual2,
5420 Actual3,
5421 Actual4,
5422 Actual
5423 )
5424 SELECT GETDATE(),
5425 CompanyID,
5426 CodeListSalesID,
5427 CodeListSalesCD,
5428 Descr,
5429 SOURCE,
5430 TYPE,
5431 GroupBy,
5432 GroupID,
5433 Formula,
5434 Template,
5435 KPIPeriodNbr,
5436 RefNbr,
5437 PERIOD,
5438 FromDate,
5439 ToDate,
5440 ObjectID,
5441 ObjectType,
5442 ObjectAssignment,
5443 TargetsSuggest,
5444 TargetsAssigned,
5445 Actual1,
5446 Actual2,
5447 Actual3,
5448 Actual4,
5449 Actual
5450 FROM
5451 (
5452 SELECT Temp = ROW_NUMBER() OVER (PARTITION BY CompanyID,
5453 CodeListSalesID,
5454 KPIPeriodNbr,
5455 ObjectID
5456 --, ObjectAssignment -- Thu.Nguyen 2019-03-12 bỏ Partition theo ObjectAssignment vì KPI chỉ assign cho SR, không assign theo ROUTE
5457 ORDER BY TempObjectID DESC,
5458 RefNbr DESC
5459 ),
5460 *
5461 FROM #tmpKPI
5462 WHERE ObjectAssignment IS NOT NULL
5463 ) tmp
5464 WHERE tmp.Temp = 1;
5465
5466 -- Update lại Actual cho chỉ tiêu BPPC
5467 UPDATE kpispcd
5468 SET kpispcd.Actual = ISNULL(kpi.Actual, 0),
5469 kpispcd.Actual1 = ISNULL(kpi.Actual1, 0),
5470 kpispcd.Actual2 = ISNULL(kpi.Actual2, 0),
5471 kpispcd.Actual3 = ISNULL(kpi.Actual3, 0),
5472 kpispcd.Actual4 = ISNULL(kpi.Actual4, 0)
5473 FROM DMSBLKPI kpispcd
5474 JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader kpispch
5475 ON kpispcd.CompanyID = kpispch.CompanyID
5476 AND kpispcd.RefNbr = kpispch.RefNbr
5477 JOIN
5478 (
5479 SELECT CompanyID,
5480 CodeListSalesID,
5481 KPIPeriodNbr,
5482 RefNbr,
5483 ObjectID,
5484 Actual = SUM(Actual),
5485 Actual1 = SUM(Actual1),
5486 Actual2 = SUM(Actual2),
5487 Actual3 = SUM(Actual3),
5488 Actual4 = SUM(Actual4)
5489 FROM #tmpKPI
5490 WHERE ObjectAssignment IS NOT NULL
5491 AND Template = 'BPPC'
5492 GROUP BY CompanyID,
5493 CodeListSalesID,
5494 KPIPeriodNbr,
5495 RefNbr,
5496 ObjectID
5497 ) kpi
5498 ON kpi.CompanyID = kpispcd.CompanyID
5499 AND kpi.RefNbr = kpispcd.RefNbr
5500 AND kpi.KPIPeriodNbr = kpispch.KPIPeriodNbr
5501 AND kpi.CodeListSalesID = kpispcd.KPIID
5502 AND kpi.ObjectID = kpispcd.ObjectID;
5503
5504 UPDATE kpispcd
5505 SET kpispcd.ValuesBaseline = ISNULL(kpi.Actual, 0)
5506 FROM MRCDMS..DMSKPISalesPeriodConfigurationDetail kpispcd
5507 JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader kpispch
5508 ON kpispcd.CompanyID = kpispch.CompanyID
5509 AND kpispcd.RefNbr = kpispch.RefNbr
5510 JOIN
5511 (
5512 SELECT CompanyID,
5513 CodeListSalesID,
5514 KPIPeriodNbr,
5515 RefNbr,
5516 ObjectID,
5517 Actual = SUM(Actual)
5518 FROM #tmpKPI
5519 GROUP BY CompanyID,
5520 CodeListSalesID,
5521 KPIPeriodNbr,
5522 RefNbr,
5523 ObjectID
5524 ) kpi
5525 ON kpi.CompanyID = kpispcd.CompanyID
5526 AND kpi.RefNbr = kpispcd.RefNbr
5527 AND kpi.KPIPeriodNbr = kpispch.KPIPeriodNbr
5528 AND kpi.CodeListSalesID = kpispcd.CodeListSalesID
5529 AND kpi.ObjectID = kpispcd.ThisObject;
5530
5531END;