ihCb0JY5

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