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