pqa9h4Bh

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