USE [MRCBaseline]
GO
/****** Object:  StoredProcedure [dbo].[sp_DMS_Baseline_KPI]    Script Date: 7/17/2019 4:05:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author Creator:		Dinh Hoang Lam
-- Create date: 2015/04/02
-- Description:
-- =============================================

-- =============================================
-- Author Update:		Dang.Huynh
-- Create date: 2017-10-31
-- Description:
-- + 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)
-- + 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
-- + 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
-- + 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)
-- + Cập nhật cách tính KPIs mở mới outlet
-- =============================================

-- EXEC sp_DMS_Baseline_KPI 3, '2019-04-30'
ALTER PROCEDURE [dbo].[sp_DMS_Baseline_KPI]
    @CompanyID INT,
    @Date DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @_CompanyID INT = @CompanyID;
    DECLARE @_Date DATETIME = @Date;
    DECLARE @_FirstDayOfWeek DATETIME;
    DECLARE @_LastDayOfWeek DATETIME;
    DECLARE @_FirstDayOfMonth DATETIME
        = CONVERT(VARCHAR(4), YEAR(@_Date)) + '/' + CONVERT(VARCHAR(2), MONTH(@_Date)) + '/1';
    DECLARE @_LastDayOfMonth DATETIME = DATEADD(dd, -1, DATEADD(mm, 1, @_FirstDayOfMonth));
    DECLARE @_Week CHAR(2);
    DECLARE @_Month CHAR(2) = RIGHT('0' + CONVERT(VARCHAR(2), MONTH(@_Date)), 2);
    DECLARE @_Year CHAR(4) = CONVERT(CHAR(4), YEAR(@_Date));
    DECLARE @_FromDate DATETIME;
    DECLARE @_ToDate DATETIME;

    BEGIN -- Khai báo bảng tạm
        CREATE TABLE #tmpKPI
        (
            RowNumber INT,
            CompanyID INT,
            CodeListSalesID INT,
            CodeListSalesCD VARCHAR(30),
            Descr NVARCHAR(100),
            Source CHAR(2),
            Type CHAR(1),
            GroupBy CHAR(1),
            GroupID VARCHAR(30),
            Formula CHAR(1),
            Template VARCHAR(15),
            KPIPeriodNbr VARCHAR(30),
            RefNbr VARCHAR(30),
            PERIOD CHAR(1),
            FromDate DATETIME,
            ToDate DATETIME,
            ObjectID INT,
            TempObjectID INT,
            ObjectType CHAR(1),
            ObjectAssignment VARCHAR(50),
            TempTargetsSuggest DECIMAL(18, 2),
            TempTargetsAssigned DECIMAL(18, 2),
            TargetsSuggest DECIMAL(18, 2),
            TargetsAssigned DECIMAL(18, 2),
            Actual1 DECIMAL(18, 2),
            Actual2 DECIMAL(18, 2),
            Actual3 DECIMAL(18, 2),
            Actual4 DECIMAL(18, 2),
            Actual DECIMAL(18, 2)
        );

        CREATE TABLE #tmpSalesTerritory
        (
            RowNumber INT,
            CompanyID INT,
            SalesOrg_0_ValueID INT,
            SalesOrg_1_ValueID INT,
            SalesOrg_2_ValueID INT,
            SalesOrg_3_ValueID INT,
            SalesOrg_4_ValueID INT,
            SalesOrg_5_ValueID INT,
            SalesOrg_6_ValueID INT,
            SalesOrg_7_ValueID INT,
            SalesOrg_8_ValueID INT,
            SalesOrg_9_ValueID INT
        );

        CREATE TABLE #tmpRoute
        (
            CompanyID INT,
            SalespersonID INT,
            Route VARCHAR(30),
            SalesForceID INT,
            SalesAreaID INT,
            EffectiveDate DATETIME,
            ExpirationDate DATETIME,
            SalesOrg_0_ValueID INT,
            SalesOrg_1_ValueID INT,
            SalesOrg_2_ValueID INT,
            SalesOrg_3_ValueID INT,
            SalesOrg_4_ValueID INT,
            SalesOrg_5_ValueID INT,
            SalesOrg_6_ValueID INT,
            SalesOrg_7_ValueID INT,
            SalesOrg_8_ValueID INT,
            SalesOrg_9_ValueID INT
        );

        CREATE TABLE #tmpSalesForce
        (
            CompanyID INT,
            EmployeeID INT,
            SFHierachyID INT,
            TerritoryType CHAR(1),
            SalesOrgValueID INT,
            SalesOrg_0_ValueID INT,
            SalesOrg_1_ValueID INT,
            SalesOrg_2_ValueID INT,
            SalesOrg_3_ValueID INT,
            SalesOrg_4_ValueID INT,
            SalesOrg_5_ValueID INT,
            SalesOrg_6_ValueID INT,
            SalesOrg_7_ValueID INT,
            SalesOrg_8_ValueID INT,
            SalesOrg_9_ValueID INT
        );

        CREATE TABLE #tmpTotalPC
        (
            CompanyID INT,
            ObjectID INT,
            ObjectAssignment VARCHAR(50),
            Actual1 DECIMAL(18, 2),
            Actual2 DECIMAL(18, 2),
            Actual3 DECIMAL(18, 2),
            Actual4 DECIMAL(18, 2),
            Actual DECIMAL(18, 2)
        );

        CREATE TABLE #tmpTotalLine
        (
            CompanyID INT,
            ObjectID INT,
            ObjectAssignment VARCHAR(50),
            Actual1 DECIMAL(18, 2),
            Actual2 DECIMAL(18, 2),
            Actual3 DECIMAL(18, 2),
            Actual4 DECIMAL(18, 2),
            Actual DECIMAL(18, 2)
        );

        CREATE TABLE #tmpCM
        (
            OrigOrderNbr NVARCHAR(50),
            OrigOrderType NVARCHAR(5)
        );

    END; -- Khai báo bảng tạm

    BEGIN -- Xóa dữ liệu KPI cũ
        DELETE FROM dbo.DMSBLKPI
        WHERE CompanyID = @_CompanyID
              AND @_Date
              BETWEEN FromDate AND ToDate;
    END; -- Xóa dữ liệu KPI cũ
    ---- Lấy tuần trong năm
    SELECT @_Week = Week,
           @_FirstDayOfWeek = StartDate,
           @_LastDayOfWeek = EndDate
    FROM dbo.DMSBLSalesCalendarWeek WITH (NOLOCK)
    WHERE CompanyID = @_CompanyID
          AND @_Date
          BETWEEN StartDate AND EndDate;

    ---- Lấy KPI tháng có kỳ đánh giá hiệu lực trong khoảng thời gian baseline
    SELECT kpi.CompanyID,
           kpi.CodeListSalesID,
           kpi.CodeListSalesCD,
           kpi.Descr,
           kpi.[Source],
           kpi.Type,
           kpi.GroupBy,
           kpi.GroupID,
           kpi.Formula,
           Template = kpi.Template,
           periodHeader.KPIPeriodNbr,
           periodHeader.Period,
           sov.SalesOrgID,
           SalesAreaID = sov.ValueID,
           sov.Level,
           kpi.CorrectRouteAndCorrectCoordinates,
           kpi.CorrectRouteAndIncorrectCoordinates,
           kpi.IncorrectRoute,
           kpi.DistributorsSell,
           kpi.MinSKUVolume,
           kpi.MinSKURevenue,
           kpi.ExcludeReturnOrder,
           periodConfigHeader.RefNbr,
           FromDate = @_FirstDayOfMonth,
           ToDate = @_LastDayOfMonth,
           ObjectID = periodConfigDetail.ThisObject,
           ObjectType = CASE
                            WHEN cate.Distributor = 1 THEN
                                'D'
                            WHEN cate.Sales = 1 THEN
                                'S'
                        END,
           TargetsSuggest = periodConfigDetail.TargetsSuggest,
           TargetsAssigned = COALESCE(periodConfigDetail.TargetsAdjust, periodConfigDetail.TargetsAssigned)
    INTO #tmpKPIDetail
    FROM MRCDMS..DMSKPIListSales kpi WITH (NOLOCK)
        JOIN MRCDMS..DMSKPISalesPeriodDetail periodDetail WITH (NOLOCK)
            ON periodDetail.CompanyID = kpi.CompanyID
               AND periodDetail.CodeListSalesID = kpi.CodeListSalesID
        JOIN MRCDMS..DMSKPISalesPeriodHeader periodHeader WITH (NOLOCK)
            ON periodHeader.CompanyID = periodDetail.CompanyID
               AND periodHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
        JOIN MRCDMS..DMSKPICategory cate WITH (NOLOCK)
            ON cate.CompanyID = periodHeader.CompanyID
               AND cate.CategoryID = periodHeader.KPICategoryID
        LEFT JOIN MRCDMS..DMSSalesOrgValue sov WITH (NOLOCK)
            ON sov.CompanyID = periodHeader.CompanyID
               AND sov.ValueID = periodHeader.SalesArea
        LEFT JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader periodConfigHeader WITH (NOLOCK)
            ON periodConfigHeader.CompanyID = periodDetail.CompanyID
               AND periodConfigHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
               --AND periodConfigHeader.CodeListSalesID = periodDetail.CodeListSalesID
               AND periodConfigHeader.Year = @_Year
               AND periodConfigHeader.Month = @_Month
        JOIN MRCDMS..DMSKPISalesPeriodConfigurationDetail periodConfigDetail WITH (NOLOCK)
            ON periodConfigDetail.CompanyID = periodConfigHeader.CompanyID
               AND periodConfigDetail.RefNbr = periodConfigHeader.RefNbr
               AND periodConfigDetail.CodeListSalesID = periodDetail.CodeListSalesID
    WHERE kpi.CompanyID = @_CompanyID
          AND kpi.Active = 1
          AND periodHeader.Release = 1
          AND periodHeader.Period = 'M'
          AND periodConfigHeader.Status = 'R';

    ---- Lấy KPI tuần có kỳ đánh giá hiệu lực trong khoảng thời gian baseline
    INSERT INTO #tmpKPIDetail
    SELECT kpi.CompanyID,
           kpi.CodeListSalesID,
           kpi.CodeListSalesCD,
           kpi.Descr,
           kpi.[Source],
           kpi.Type,
           kpi.GroupBy,
           kpi.GroupID,
           kpi.Formula,
           Template = kpi.Template,
           periodHeader.KPIPeriodNbr,
           periodHeader.Period,
           sov.SalesOrgID,
           SalesAreaID = sov.ValueID,
           sov.Level,
           kpi.CorrectRouteAndCorrectCoordinates,
           kpi.CorrectRouteAndIncorrectCoordinates,
           kpi.IncorrectRoute,
           kpi.ExcludeReturnOrder,
           kpi.MinSKUVolume,
           kpi.MinSKURevenue,
           kpi.DistributorsSell,
           periodConfigHeader.RefNbr,
           FromDate = @_FirstDayOfWeek,
           ToDate = @_LastDayOfWeek,
           ObjectID = periodConfigDetail.ThisObject,
           ObjectType = CASE
                            WHEN cate.Distributor = 1 THEN
                                'D'
                            WHEN cate.Sales = 1 THEN
                                'S'
                        END,
           TargetsSuggest = periodConfigDetail.TargetsSuggest,
           TargetsAssigned = COALESCE(periodConfigDetail.TargetsAdjust, periodConfigDetail.TargetsAssigned)
    FROM MRCDMS..DMSKPIListSales kpi WITH (NOLOCK)
        JOIN MRCDMS..DMSKPISalesPeriodDetail periodDetail WITH (NOLOCK)
            ON periodDetail.CompanyID = kpi.CompanyID
               AND periodDetail.CodeListSalesID = kpi.CodeListSalesID
        JOIN MRCDMS..DMSKPISalesPeriodHeader periodHeader WITH (NOLOCK)
            ON periodHeader.CompanyID = periodDetail.CompanyID
               AND periodHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
        JOIN MRCDMS..DMSKPICategory cate WITH (NOLOCK)
            ON cate.CompanyID = periodHeader.CompanyID
               AND cate.CategoryID = periodHeader.KPICategoryID
        LEFT JOIN MRCDMS..DMSSalesOrgValue sov WITH (NOLOCK)
            ON sov.CompanyID = periodHeader.CompanyID
               AND sov.ValueID = periodHeader.SalesArea
        LEFT JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader periodConfigHeader WITH (NOLOCK)
            ON periodConfigHeader.CompanyID = periodHeader.CompanyID
               AND periodConfigHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
               --AND periodConfigHeader.CodeListSalesID = periodDetail.CodeListSalesID
               AND periodConfigHeader.Year = @_Year
               AND periodConfigHeader.Week = @_Week
        JOIN MRCDMS..DMSKPISalesPeriodConfigurationDetail periodConfigDetail WITH (NOLOCK)
            ON periodConfigDetail.CompanyID = periodConfigHeader.CompanyID
               AND periodConfigDetail.RefNbr = periodConfigHeader.RefNbr
               AND periodConfigDetail.CodeListSalesID = periodDetail.CodeListSalesID
    WHERE kpi.CompanyID = @_CompanyID
          AND kpi.Active = 1
          AND periodHeader.Release = 1
          AND periodHeader.Period = 'W'
          AND periodConfigHeader.Status = 'R';

    ---- Lấy KPI ngày có kỳ đánh giá hiệu lực trong khoảng thời gian baseline
    INSERT INTO #tmpKPIDetail
    SELECT kpi.CompanyID,
           kpi.CodeListSalesID,
           kpi.CodeListSalesCD,
           kpi.Descr,
           kpi.[Source],
           kpi.Type,
           kpi.GroupBy,
           kpi.GroupID,
           kpi.Formula,
           Template = kpi.Template,
           periodHeader.KPIPeriodNbr,
           periodHeader.Period,
           sov.SalesOrgID,
           SalesAreaID = sov.ValueID,
           sov.Level,
           kpi.CorrectRouteAndCorrectCoordinates,
           kpi.CorrectRouteAndIncorrectCoordinates,
           kpi.IncorrectRoute,
           kpi.DistributorsSell,
           kpi.MinSKUVolume,
           kpi.MinSKURevenue,
           kpi.ExcludeReturnOrder,
           periodConfigHeader.RefNbr,
           FromDate = periodConfigHeader.FromDate,
           ToDate = periodConfigHeader.ToDate,
           ObjectID = periodConfigDetail.ThisObject,
           ObjectType = CASE
                            WHEN cate.Distributor = 1 THEN
                                'D'
                            WHEN cate.Sales = 1 THEN
                                'S'
                        END,
           TargetsSuggest = periodConfigDetail.TargetsSuggest,
           TargetsAssigned = COALESCE(periodConfigDetail.TargetsAdjust, periodConfigDetail.TargetsAssigned)
    FROM MRCDMS..DMSKPIListSales kpi WITH (NOLOCK)
        JOIN MRCDMS..DMSKPISalesPeriodDetail periodDetail WITH (NOLOCK)
            ON periodDetail.CompanyID = kpi.CompanyID
               AND periodDetail.CodeListSalesID = kpi.CodeListSalesID
        JOIN MRCDMS..DMSKPISalesPeriodHeader periodHeader WITH (NOLOCK)
            ON periodHeader.CompanyID = periodDetail.CompanyID
               AND periodHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
        JOIN MRCDMS..DMSKPICategory cate WITH (NOLOCK)
            ON cate.CompanyID = periodHeader.CompanyID
               AND cate.CategoryID = periodHeader.KPICategoryID
        LEFT JOIN MRCDMS..DMSSalesOrgValue sov WITH (NOLOCK)
            ON sov.CompanyID = periodHeader.CompanyID
               AND sov.ValueID = periodHeader.SalesArea
        JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader periodConfigHeader WITH (NOLOCK)
            ON periodConfigHeader.CompanyID = periodDetail.CompanyID
               AND periodConfigHeader.KPIPeriodNbr = periodDetail.KPIPeriodNbr
        --AND periodConfigHeader.CodeListSalesID = periodDetail.CodeListSalesID
        JOIN MRCDMS..DMSKPISalesPeriodConfigurationDetail periodConfigDetail WITH (NOLOCK)
            ON periodConfigDetail.CompanyID = periodConfigHeader.CompanyID
               AND periodConfigDetail.RefNbr = periodConfigHeader.RefNbr
               AND periodConfigDetail.CodeListSalesID = periodDetail.CodeListSalesID
    WHERE kpi.CompanyID = @_CompanyID
          AND kpi.Active = 1
          AND periodHeader.Release = 1
          AND periodHeader.Period = 'D'
          AND @_Date
          BETWEEN periodConfigHeader.FromDate AND periodConfigHeader.ToDate
          AND periodConfigHeader.Status = 'R';

    ---- Xóa các KPI chưa có kì DK
    DELETE #tmpKPIDetail
    WHERE RefNbr IS NULL;
    --DELETE 		#tmpKPIDetail WHERE RefNbr not in ('00000936','00000937','00000938','00000939')--1
    ---- Lấy khoảng thời gian tính KPI lớn nhất
    SELECT @_FromDate = MIN(FromDate),
           @_ToDate = MAX(ToDate)
    FROM #tmpKPIDetail;

    ---- Lấy danh sách NPP
    SELECT DISTINCT
        bldl.CompanyID,
        bldl.ID,
        bldl.DistributorID,
        DistributorLocationID = bldl.LocationID,
        blst.SalesOrg_0_ValueID,
        blst.SalesOrg_1_ValueID,
        blst.SalesOrg_2_ValueID,
        blst.SalesOrg_3_ValueID,
        blst.SalesOrg_4_ValueID,
        blst.SalesOrg_5_ValueID,
        blst.SalesOrg_6_ValueID,
        blst.SalesOrg_7_ValueID,
        blst.SalesOrg_8_ValueID,
        blst.SalesOrg_9_ValueID,
        SalesForce_0_ID = blsf0.EmployeeID,
        SalesForce_1_ID = blsf1.EmployeeID,
        SalesForce_2_ID = blsf2.EmployeeID,
        SalesForce_3_ID = blsf3.EmployeeID,
        SalesForce_4_ID = blsf4.EmployeeID,
        SalesForce_5_ID = blsf5.EmployeeID,
        SalesForce_6_ID = blsf6.EmployeeID,
        SalesForce_7_ID = blsf7.EmployeeID,
        SalesForce_8_ID = blsf8.EmployeeID,
        SalesForce_9_ID = blsf9.EmployeeID,
        bldl.ExpirationDate
    INTO #tmpDistributorLocation
    FROM dbo.DMSBLDistributorLocation bldl WITH (NOLOCK)
        LEFT JOIN dbo.DMSBLSalesTerritory blst WITH (NOLOCK)
            ON blst.CompanyID = bldl.CompanyID
               AND blst.ID = bldl.BLSalesTerritoryID
        LEFT JOIN dbo.DMSBLSalesForce blsf0 WITH (NOLOCK)
            ON blsf0.CompanyID = blst.CompanyID
               AND blsf0.SalesOrgValueID = blst.SalesOrg_0_ValueID
               AND blsf0.ExpirationDate IS NULL
        LEFT JOIN dbo.DMSBLSalesForce blsf1 WITH (NOLOCK)
            ON blsf1.CompanyID = blst.CompanyID
               AND blsf1.SalesOrgValueID = blst.SalesOrg_1_ValueID
               AND blsf1.ExpirationDate IS NULL
        LEFT JOIN dbo.DMSBLSalesForce blsf2 WITH (NOLOCK)
            ON blsf2.CompanyID = blst.CompanyID
               AND blsf2.SalesOrgValueID = blst.SalesOrg_2_ValueID
               AND blsf2.ExpirationDate IS NULL
        LEFT JOIN dbo.DMSBLSalesForce blsf3 WITH (NOLOCK)
            ON blsf3.CompanyID = blst.CompanyID
               AND blsf3.SalesOrgValueID = blst.SalesOrg_3_ValueID
               AND blsf3.ExpirationDate IS NULL
        LEFT JOIN dbo.DMSBLSalesForce blsf4 WITH (NOLOCK)
            ON blsf4.CompanyID = blst.CompanyID
               AND blsf4.SalesOrgValueID = blst.SalesOrg_4_ValueID
               AND blsf4.ExpirationDate IS NULL
        LEFT JOIN dbo.DMSBLSalesForce blsf5 WITH (NOLOCK)
            ON blsf5.CompanyID = blst.CompanyID
               AND blsf5.SalesOrgValueID = blst.SalesOrg_5_ValueID
               AND blsf5.ExpirationDate IS NULL
        LEFT JOIN dbo.DMSBLSalesForce blsf6 WITH (NOLOCK)
            ON blsf6.CompanyID = blst.CompanyID
               AND blsf6.SalesOrgValueID = blst.SalesOrg_6_ValueID
               AND blsf6.ExpirationDate IS NULL
        LEFT JOIN dbo.DMSBLSalesForce blsf7 WITH (NOLOCK)
            ON blsf7.CompanyID = blst.CompanyID
               AND blsf7.SalesOrgValueID = blst.SalesOrg_7_ValueID
               AND blsf7.ExpirationDate IS NULL
        LEFT JOIN dbo.DMSBLSalesForce blsf8 WITH (NOLOCK)
            ON blsf8.CompanyID = blst.CompanyID
               AND blsf8.SalesOrgValueID = blst.SalesOrg_8_ValueID
               AND blsf8.ExpirationDate IS NULL
        LEFT JOIN dbo.DMSBLSalesForce blsf9 WITH (NOLOCK)
            ON blsf9.CompanyID = blst.CompanyID
               AND blsf9.SalesOrgValueID = blst.SalesOrg_9_ValueID
               AND blsf9.ExpirationDate IS NULL
    WHERE bldl.CompanyID = @_CompanyID;

    ---- Lấy dữ liệu RawData SalesOut để tính KPI
    SELECT tmp.*,
           dl.DistributorLocationID,
           dl.SalesOrg_0_ValueID,
           dl.SalesOrg_1_ValueID,
           dl.SalesOrg_2_ValueID,
           dl.SalesOrg_3_ValueID,
           dl.SalesOrg_4_ValueID,
           dl.SalesOrg_5_ValueID,
           dl.SalesOrg_6_ValueID,
           dl.SalesOrg_7_ValueID,
           dl.SalesOrg_8_ValueID,
           dl.SalesOrg_9_ValueID,
           dl.SalesForce_0_ID,
           dl.SalesForce_1_ID,
           dl.SalesForce_2_ID,
           dl.SalesForce_3_ID,
           dl.SalesForce_4_ID,
           dl.SalesForce_5_ID,
           dl.SalesForce_6_ID,
           dl.SalesForce_7_ID,
           dl.SalesForce_8_ID,
           dl.SalesForce_9_ID
    INTO #tmpSORawSales
    FROM
    (
        SELECT blrs.CompanyID,
               blrs.BLDistributorLocationID,
               blrs.SalespersonID,
               blrs.OrderType,
               blrs.OrderNbr,
               blrs.OrderDate,
               blrs.SourceType,
               blrs.Route,
               blrs.CustomerID,
               blrs.CustomerLocationID,
               blrs.InventoryID,
               TranAmt = CASE
                             WHEN blrs.OrderType = 'CM' THEN
                                 blrs.TranAmt * -1
                             ELSE
                                 blrs.TranAmt
                         END,
               blrs.ShippedQty,
               blrs.IsValidDate,
               blrs.IsValidDistance,
               blrs.IsRevenueDeduction,
               blrs.ReasonCode,
               blrs.OrigOrderNbr,
               blrs.OrigOrderType
        FROM dbo.DMSBLRawSales blrs WITH (NOLOCK)
        WHERE blrs.CompanyID = @_CompanyID
              AND blrs.OrderDate
              BETWEEN @_FromDate AND @_ToDate
              AND blrs.IsDispose = 0
              AND blrs.IsFree = 0
              AND blrs.SourceType != 'TMK'
              AND
              (
                  blrs.OrderType <> 'CM'
                  OR blrs.IsRevenueDeduction = 1
              )
        UNION ALL
        SELECT blrsh.CompanyID,
               blrsh.BLDistributorLocationID,
               blrsh.SalespersonID,
               blrsh.OrderType,
               blrsh.OrderNbr,
               blrsh.OrderDate,
               blrsh.SourceType,
               blrsh.Route,
               blrsh.CustomerID,
               blrsh.CustomerLocationID,
               blrsh.InventoryID,
               TranAmt = CASE
                             WHEN blrsh.OrderType = 'CM' THEN
                                 blrsh.TranAmt * -1
                             ELSE
                                 blrsh.TranAmt
                         END,
               blrsh.ShippedQty,
               blrsh.IsValidDate,
               blrsh.IsValidDistance,
               blrsh.IsRevenueDeduction,
               blrsh.ReasonCode,
               blrsh.OrigOrderNbr,
               blrsh.OrigOrderType
        FROM dbo.DMSBLRawSalesHistory blrsh WITH (NOLOCK)
            JOIN dbo.DMSBLDistributorLocation dl WITH (NOLOCK)
                ON dl.CompanyID = blrsh.CompanyID
                   AND dl.ID = blrsh.BLDistributorLocationID
        WHERE blrsh.CompanyID = @_CompanyID
              AND blrsh.OrderDate
              BETWEEN @_FromDate AND @_ToDate
              AND blrsh.IsDispose = 0
              AND blrsh.IsFree = 0
              AND blrsh.SourceType != 'TMK'
              -- đối với PNC có config trừ doanh số giao dịch trả hàng
              -- nên phải lấy tất cả các giao dịch và tùy config để lấy
              AND
              (
                  blrsh.OrderType <> 'CM'
                  OR blrsh.IsRevenueDeduction = 1
              )
        UNION ALL
        SELECT bi.CompanyID,
               bldl.ID,
               bi.SRCode,
               bi.OrderType,
               bi.OrderNbr,
               bi.OrderDate,
               SourceType = 'PDA',
               bi.Route,
               bi.CustomerCD,
               bi.CustomerLocationID,
               bi.InventoryID,
               TranAmt = bi.Amount,
               ShippedQty = bi.OrderQty,
               IsValidDate = 1,
               IsValidDistance = 1,
               IsRevenueDeduction = NULL,
               ReasonCode = NULL,
               OrigOrderNbr = NULL,
               OrigOrderType = NULL
        FROM GESO..DMSBiHoDetail bi
            JOIN dbo.DMSBLDistributorLocation bldl WITH (NOLOCK)
                ON bldl.CompanyID = bi.CompanyID
                   AND bldl.DistributorID = bi.DistributorID
                   AND bldl.LocationID = bi.DistributorLocationID
        WHERE bi.CompanyID = @_CompanyID
              AND bi.OrderDate
              BETWEEN @_FromDate AND @_ToDate
              AND bi.Route IS NOT NULL
              AND bi.CustomerCD IS NOT NULL
              AND bi.CustomerLocationID IS NOT NULL
              AND bi.Status = 'A'
              --AND bldl.EffectiveDate BETWEEN @_FromDate
              --                       AND     @_ToDate
              AND ISNULL(bldl.ExpirationDate, @_ToDate)
              BETWEEN @_FromDate AND @_ToDate
    ) tmp
        JOIN #tmpDistributorLocation dl WITH (NOLOCK)
            ON dl.CompanyID = tmp.CompanyID
               AND dl.ID = tmp.BLDistributorLocationID;

    -- Thu.Nguyen 2019-03-19 bỏ do không dùng tới
    -- them tinh doanh so route cu cho route moi
    --SELECT tmp.RowNumber,
    --       tmp.CompanyID,
    --       tmp.SalespersonID,
    --       tmp.ROUTE
    --INTO #tempSRMCP
    --FROM
    --(
    --    SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
    --                                                       rs.SalespersonID
    --                                          ORDER BY rs.EffectiveDate DESC
    --                                         ),
    --           rs.CompanyID,
    --           rs.SalespersonID,
    --           ROUTE = rs.RouteCD
    --    FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
    --    WHERE rs.CompanyID = @_CompanyID
    --          AND
    --          (
    --              @_FromDate
    --          BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
    --              OR @_ToDate
    --          BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
    --              OR rs.EffectiveDate
    --          BETWEEN @_FromDate AND @_ToDate
    --          )
    --) tmp
    --WHERE tmp.RowNumber = 1;

    --UPDATE rs
    --SET Route = mcp.ROUTE
    --FROM #tmpSORawSales rs
    --    JOIN #tempSRMCP mcp
    --        ON mcp.CompanyID = rs.CompanyID
    --           AND mcp.SalespersonID = rs.SalespersonID;


    ---- Lấy dữ liệu RawData SalesIn để tính KPI

    SELECT blsi.CompanyID,
           blsi.TranDate,
           dl.DistributorID,
           dl.DistributorLocationID,
           blsi.InventoryID,
           blsi.ReceivedAmt,
           blsi.ReceivedQty,
           dl.SalesOrg_0_ValueID,
           dl.SalesOrg_1_ValueID,
           dl.SalesOrg_2_ValueID,
           dl.SalesOrg_3_ValueID,
           dl.SalesOrg_4_ValueID,
           dl.SalesOrg_5_ValueID,
           dl.SalesOrg_6_ValueID,
           dl.SalesOrg_7_ValueID,
           dl.SalesOrg_8_ValueID,
           dl.SalesOrg_9_ValueID,
           dl.SalesForce_0_ID,
           dl.SalesForce_1_ID,
           dl.SalesForce_2_ID,
           dl.SalesForce_3_ID,
           dl.SalesForce_4_ID,
           dl.SalesForce_5_ID,
           dl.SalesForce_6_ID,
           dl.SalesForce_7_ID,
           dl.SalesForce_8_ID,
           dl.SalesForce_9_ID
    INTO #tmpSIRawSales
    FROM dbo.DMSBLSalesIn blsi WITH (NOLOCK)
        JOIN #tmpDistributorLocation dl
            ON dl.CompanyID = blsi.CompanyID
               AND dl.ID = blsi.BLDistributorLocationID
    WHERE blsi.CompanyID = @_CompanyID
          AND blsi.TranDate
          BETWEEN @_FromDate AND @_ToDate;

    ---- Lấy ra dữ liệu BL Route Sales Để tính các KPIs theo %
    SELECT *
    INTO #tmpRouteSalesData
    FROM
    (
        SELECT blrs.CompanyID,
               blrs.DistributorID,
               blrs.BLDistributorLocationID,
               blrs.TranDate,
               blrs.SalespersonID,
               blrs.Route,
               blrs.Revenue1,
               blrs.Revenue2,
               blrs.Revenue3,
               blrs.PromotionAmt,
               blrs.SalesOut1,
               blrs.SalesOut2,
               blrs.SalesOut3,
               blrs.PromotionQty,
               blrs.ASO,
               blrs.TotalASO,
               blrs.TotalMCPVisit,
               blrs.TotalActualVisit,
               blrs.PC1,
               blrs.PC2,
               blrs.PC3
        FROM dbo.DMSBLRouteSales blrs WITH (NOLOCK)
        WHERE blrs.CompanyID = @_CompanyID
              AND blrs.TranDate
              BETWEEN @_FromDate AND @_ToDate
        UNION ALL
        SELECT blrsh.CompanyID,
               blrsh.DistributorID,
               blrsh.BLDistributorLocationID,
               blrsh.TranDate,
               blrsh.SalespersonID,
               blrsh.Route,
               blrsh.Revenue1,
               blrsh.Revenue2,
               blrsh.Revenue3,
               blrsh.PromotionAmt,
               blrsh.SalesOut1,
               blrsh.SalesOut2,
               blrsh.SalesOut3,
               blrsh.PromotionQty,
               blrsh.ASO,
               blrsh.TotalASO,
               blrsh.TotalMCPVisit,
               blrsh.TotalActualVisit,
               blrsh.PC1,
               blrsh.PC2,
               blrsh.PC3
        FROM dbo.DMSBLRouteSalesHistory blrsh WITH (NOLOCK)
        WHERE blrsh.CompanyID = @_CompanyID
              AND blrsh.TranDate
              BETWEEN @_FromDate AND @_ToDate
    ) tmp;

    ---- Lấy danh sách KPI ngoại trừ các KPI đặc biệt
    SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
           *
    INTO #tmpKPIList
    FROM
    (
        SELECT CompanyID,
               CodeListSalesID,
               CodeListSalesCD,
               RefNbr,
               KPIPeriodNbr,
               Descr = MAX(Descr),
               Source = MAX(Source),
               Type = MAX(Type),
               GroupBy = MAX(GroupBy),
               GroupID = MAX(GroupID),
               Formula = MAX(Formula),
               Period = MAX(Period),
               SalesOrgID = MAX(SalesOrgID),
               SalesAreaID = MAX(SalesAreaID),
               Level = MAX(Level),
               Template = MAX(Template),
               FromDate,
               ToDate,
               ObjectType = MAX(ObjectType),
               CorrectRouteAndCorrectCoordinates,
               CorrectRouteAndIncorrectCoordinates,
               IncorrectRoute,
               DistributorsSell,
               MinSKUVolume,
               MinSKURevenue,
               ExcludeReturnOrder
        FROM #tmpKPIDetail
        WHERE Template IS NULL
              OR Template NOT IN ( 'LPPC', 'AVGPC', 'AVGPCAC', 'DROPSIZER', 'DROPSIZEV', 'DISPLAY%PASS', 'PCINDAY' )
        GROUP BY CompanyID,
                 CodeListSalesID,
                 CodeListSalesCD,
                 RefNbr,
                 KPIPeriodNbr,
                 FromDate,
                 ToDate,
                 CorrectRouteAndCorrectCoordinates,
                 CorrectRouteAndIncorrectCoordinates,
                 IncorrectRoute,
                 DistributorsSell,
                 MinSKUVolume,
                 MinSKURevenue,
                 ExcludeReturnOrder
    ) tmp;

    DECLARE @_Min INT;
    DECLARE @_Max INT;
    DECLARE @_KPIID INT;
    DECLARE @_KPICD VARCHAR(50);
    DECLARE @_KPIDescr NVARCHAR(100);
    DECLARE @_RefNbr VARCHAR(50);
    DECLARE @_KPIPeriodNbr VARCHAR(50);
    DECLARE @_Source CHAR(2);
    DECLARE @_Type CHAR(1);
    DECLARE @_GroupBy CHAR(1);
    DECLARE @_GroupID VARCHAR(50);
    DECLARE @_Formula CHAR(1);
    DECLARE @_Template VARCHAR(50);
    DECLARE @_Period CHAR(1);
    DECLARE @_SalesAreaID INT;
    DECLARE @_Level INT;
    DECLARE @_ObjectType CHAR(1);
    DECLARE @_IsValiDateValidDistance BIT;
    DECLARE @_IsValidDateInvalidDistance BIT;
    DECLARE @_ExcludeReturnOrder BIT;
    DECLARE @_IsInvalidDate BIT;
    DECLARE @_IsDistributorSell BIT;
    DECLARE @_IncludePromotionItem BIT;
    DECLARE @_IncludeWarrantyItem BIT;
    DECLARE @_MinSKUVolume DECIMAL(18, 6);
    DECLARE @_MinSKURevenue DECIMAL(18, 6);
    SELECT @_Min = 1;
    SELECT @_Max = MAX(RowNumber)
    FROM #tmpKPIList;
    DECLARE @_TempFromDate DATETIME = NULL;
    DECLARE @_TempToDate DATETIME = NULL;

    BEGIN ---- Tính các KPI bình thường
        ---- Lặp qua tất cả KPI trong danh sách để tính
        WHILE @_Min <= @_Max
        BEGIN
            ---- Xóa dữ liệu SM để tính lại đối với KPI mới
            DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
            DELETE FROM dbo.DMSBLObjectKPITemp;

            ---- Lấy thông tin công thức KPI
            SELECT @_KPIID = CodeListSalesID,
                   @_KPICD = CodeListSalesCD,
                   @_KPIDescr = Descr,
                   @_RefNbr = RefNbr,
                   @_KPIPeriodNbr = KPIPeriodNbr,
                   @_Source = Source,
                   @_Type = Type,
                   @_GroupBy = ISNULL(GroupBy, 'A'),
                   @_GroupID = GroupID,
                   @_Formula = Formula,
                   @_Template = Template,
                   @_Period = Period,
                   @_SalesAreaID = SalesAreaID,
                   @_Level = Level,
                   @_FromDate = FromDate,
                   @_ToDate = ToDate,
                   @_ObjectType = ObjectType,
                   @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
                   @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
                   @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
                   @_IsDistributorSell = ISNULL(DistributorsSell, 0),
                   @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
                   @_MinSKURevenue = MinSKURevenue,
                   @_MinSKUVolume = MinSKUVolume
            FROM #tmpKPIList
            WHERE RowNumber = @_Min;

            IF @_TempFromDate IS NULL
               OR @_TempToDate IS NULL
               OR @_TempFromDate != @_FromDate
               OR @_TempToDate != @_ToDate
            BEGIN
                DELETE FROM #tmpSalesTerritory;
                DELETE FROM #tmpRoute;
                DELETE FROM #tmpSalesForce;

                ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
                INSERT INTO #tmpSalesTerritory
                SELECT *
                FROM
                (
                    SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
                                                                       st.SalesOrg_0_ValueID
                                                          ORDER BY st.EffectiveDate DESC
                                                         ),
                           st.CompanyID,
                           st.SalesOrg_0_ValueID,
                           st.SalesOrg_1_ValueID,
                           st.SalesOrg_2_ValueID,
                           st.SalesOrg_3_ValueID,
                           st.SalesOrg_4_ValueID,
                           st.SalesOrg_5_ValueID,
                           st.SalesOrg_6_ValueID,
                           st.SalesOrg_7_ValueID,
                           st.SalesOrg_8_ValueID,
                           st.SalesOrg_9_ValueID
                    FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
                    WHERE st.CompanyID = @_CompanyID
                          AND
                          (
                              @_FromDate
                          BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
                              OR @_ToDate
                          BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
                              OR st.EffectiveDate
                          BETWEEN @_FromDate AND @_ToDate
                          )
                ) tmp
                WHERE tmp.RowNumber = 1;

                ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
                INSERT INTO #tmpRoute
                SELECT tmp.CompanyID,
                       tmp.SalespersonID,
                       tmp.Route,
                       tmp.SalesForceID,
                       tmp.SalesAreaID,
                       tmp.EffectiveDate,
                       tmp.ExpirationDate,
                       st.SalesOrg_0_ValueID,
                       st.SalesOrg_1_ValueID,
                       st.SalesOrg_2_ValueID,
                       st.SalesOrg_3_ValueID,
                       st.SalesOrg_4_ValueID,
                       st.SalesOrg_5_ValueID,
                       st.SalesOrg_6_ValueID,
                       st.SalesOrg_7_ValueID,
                       st.SalesOrg_8_ValueID,
                       st.SalesOrg_9_ValueID
                FROM
                (
                    SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
                                                                       rs.SalespersonID
                                                          ORDER BY rs.EffectiveDate DESC
                                                         ),
                           rs.CompanyID,
                           rs.SalespersonID,
                           Route = rs.RouteCD,
                           rs.SalesForceID,
                           sph.SalesAreaID,
                           rs.EffectiveDate,
                           ExpirationDate = ISNULL(rs.EndDate, GETDATE())
                    FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
					JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
                    WHERE rs.CompanyID = @_CompanyID
                          AND
                          (
                              @_FromDate
                          BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                              OR @_ToDate
                          BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                              OR rs.EffectiveDate
                          BETWEEN @_FromDate AND @_ToDate
                          )
                    UNION ALL
                    SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
                                                                       rs.PreviousSalesMan
                                                          ORDER BY rs.EffectiveDate DESC
                                                         ),
                           rs.CompanyID,
                           SalespersonID = rs.PreviousSalesMan,
                           Route = rs.RouteCD,
                           rs.SalesForceID,
                           sph.SalesAreaID,
                           rs.StartDatePre,
                           rs.EndDatePre
                    FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
					JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
                    WHERE rs.CompanyID = @_CompanyID
                          AND
                          (
                              @_FromDate
                          BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                              OR @_ToDate
                          BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                              OR rs.EffectiveDate
                          BETWEEN @_FromDate AND @_ToDate
                          )
                          AND rs.PreviousSalesMan IS NOT NULL
						   UNION
                           SELECT 1 AS RowNumber,
                                  @_CompanyID AS CompanyID,
                                  1231 AS SalespersonID,
                                  'R000918' AS Route,
                                  1194 AS SalesForceID,
                                  535 AS SalesAreaID,
                                  '2019-06-01 00:00:00.000' AS StartDatePre,
                                  '2019-06-14 00:00:00.000' AS EndDatePre
                            WHERE @_Year      = '2019'
                              AND @_Month     = '06'
                              AND @_CompanyID = 3
                           UNION
                           SELECT 1 AS RowNumber,
                                  @_CompanyID AS CompanyID,
                                  2766 AS SalespersonID,
                                  'R000918' AS Route,
                                  1194 AS SalesForceID,
                                  535 AS SalesAreaID,
                                  '2019-06-15 00:00:00.000' AS StartDatePre,
                                  '2019-06-30 00:00:00.000' AS EndDatePre
                            WHERE @_Year      = '2019'
                              AND @_Month     = '06'
                              AND @_CompanyID = 3
                ) tmp
                    JOIN #tmpSalesTerritory st
                        ON st.CompanyID = tmp.CompanyID
                           AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
                WHERE tmp.RowNumber = 1;

                ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
                INSERT INTO #tmpSalesForce
                SELECT tmp.CompanyID,
                       tmp.EmployeeID,
                       tmp.SFHierachyID,
                       tmp.TerritoryType,
                       tmp.SalesOrgValueID,
                       st.SalesOrg_0_ValueID,
                       st.SalesOrg_1_ValueID,
                       st.SalesOrg_2_ValueID,
                       st.SalesOrg_3_ValueID,
                       st.SalesOrg_4_ValueID,
                       st.SalesOrg_5_ValueID,
                       st.SalesOrg_6_ValueID,
                       st.SalesOrg_7_ValueID,
                       st.SalesOrg_8_ValueID,
                       st.SalesOrg_9_ValueID
                FROM
                (
                    SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
                                                                       sf.EmployeeID
                                                          ORDER BY sf.EffectiveDate DESC
                                                         ),
                           sf.CompanyID,
                           sf.EmployeeID,
                           sf.SFHierachyID,
                           sf.TerritoryType,
                           sf.SalesOrgValueID
                    FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
                    WHERE sf.CompanyID = @_CompanyID
                          AND
                          (
                              @_FromDate
                          BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
                              OR @_ToDate
                          BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
                              OR sf.EffectiveDate
                          BETWEEN @_FromDate AND @_ToDate
                          )
                ) tmp
                    LEFT JOIN #tmpSalesTerritory st
                        ON st.CompanyID = tmp.CompanyID
                           AND
                           (
                               st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
                               OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
                               OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
                               OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
                               OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
                               OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
                               OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
                               OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
                               OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
                               OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
                           )
                WHERE tmp.RowNumber = 1;

                SET @_TempFromDate = @_FromDate;
                SET @_TempToDate = @_ToDate;
            END;
            ---- Tính KPI của đối tượng Sales
            IF @_ObjectType = 'S'
            BEGIN

                ---- Tính KPI doanh số và số lượng, PC, SKU, ASO theo đơn hàng
                IF @_Template IN ( 'REVENUE', 'QUANTITY', 'PC', 'SKU', 'ASOROUTE' )
                   OR @_Source = 'SO'
                   OR @_Source = 'SD'
                   OR
                   (
                       @_Source = 'CU'
                       AND @_Type = 'Q'
                   )
                BEGIN
                    INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
                    SELECT rs.CompanyID,
                           rs.SalespersonID,
                           r.SalesForceID,
                           r.SalesAreaID,
                           rs.Route,
                           rs.OrderDate,
                           rs.SourceType,
                           rs.CustomerID,
                           rs.CustomerLocationID,
                           rs.IsValidDate,
                           rs.IsValidDistance,
                           Multiply = CASE
                                          WHEN rs.IsRevenueDeduction = 1 THEN
                                              -1
                                          ELSE
                                              1
                                      END,
                           PC = 1,
                           SKU = COUNT(DISTINCT rs.InventoryID),
                           TranAmt = SUM(rs.TranAmt),
                           ShippedQty = SUM(rs.ShippedQty),
                           rs.SalesOrg_0_ValueID,
                           rs.SalesOrg_1_ValueID,
                           rs.SalesOrg_2_ValueID,
                           rs.SalesOrg_3_ValueID,
                           rs.SalesOrg_4_ValueID,
                           rs.SalesOrg_5_ValueID,
                           rs.SalesOrg_6_ValueID,
                           rs.SalesOrg_7_ValueID,
                           rs.SalesOrg_8_ValueID,
                           rs.SalesOrg_9_ValueID,
                           rs.SalesForce_0_ID,
                           rs.SalesForce_1_ID,
                           rs.SalesForce_2_ID,
                           rs.SalesForce_3_ID,
                           rs.SalesForce_4_ID,
                           rs.SalesForce_5_ID,
                           rs.SalesForce_6_ID,
                           rs.SalesForce_7_ID,
                           rs.SalesForce_8_ID,
                           rs.SalesForce_9_ID,
                           Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
                    FROM #tmpSORawSales rs
                        LEFT JOIN MRCDMS..DMSViewInventoryItem inv
                            ON rs.CompanyID = inv.CompanyID
                               AND rs.InventoryID = inv.InventoryID
                        LEFT JOIN #tmpRoute r
                            ON r.CompanyID = rs.CompanyID
                               AND r.SalespersonID = rs.SalespersonID
                               AND r.Route = rs.Route
                               AND rs.OrderDate
                               BETWEEN r.EffectiveDate AND r.ExpirationDate
                    WHERE rs.OrderDate
                          BETWEEN @_TempFromDate AND @_TempToDate
                          AND rs.OrderType IN (   (CASE
                                                       WHEN @_ExcludeReturnOrder = 0 THEN
                                                           'CM'
                                                       ELSE
                                                           ''
                                                   END
                                                  ), 'SO', 'IN'
                                              )
                          AND
                          (
                              ISNULL(@_GroupID, '') = ''
                              OR
                              (
                                  EXISTS
                    (
                        SELECT TOP 1
                            *
                        FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
                        WHERE igd.CompanyID = @_CompanyID
                              AND igd.CodegGroupProd = @_GroupID
                              AND igd.InventoryID = rs.InventoryID
                    )
                                  OR EXISTS
                    (
                        SELECT *
                        FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
                        WHERE gr.CompanyID = @_CompanyID
                              AND gr.CodegGroupProd = @_GroupID
                              AND inv.[HierarchyID] = gr.[HierarchyID]
                              OR gr.Attribute0 = inv.Attribute0ID
                              OR gr.Attribute1 = inv.Attribute1ID
                              OR gr.Attribute2 = inv.Attribute2ID
                              OR gr.Attribute3 = inv.Attribute3ID
                              OR gr.Attribute4 = inv.Attribute4ID
                              OR gr.Attribute5 = inv.Attribute5ID
                              OR gr.Attribute6 = inv.Attribute6ID
                              OR gr.Attribute7 = inv.Attribute7ID
                              OR gr.Attribute8 = inv.Attribute8ID
                              OR gr.Attribute9 = inv.Attribute9ID
                    )
                              )
                          )
                          AND rs.ShippedQty >= @_MinSKUVolume
                          AND ABS(rs.TranAmt) >= @_MinSKURevenue
                    GROUP BY rs.CompanyID,
                             rs.SalespersonID,
                             r.SalesForceID,
                             r.SalesAreaID,
                             rs.ROUTE,
                             rs.OrderDate,
                             rs.SourceType,
                             rs.CustomerID,
                             rs.CustomerLocationID,
                             rs.IsValidDate,
                             rs.IsValidDistance,
                             rs.IsRevenueDeduction,
                             rs.SalesOrg_0_ValueID,
                             rs.SalesOrg_1_ValueID,
                             rs.SalesOrg_2_ValueID,
                             rs.SalesOrg_3_ValueID,
                             rs.SalesOrg_4_ValueID,
                             rs.SalesOrg_5_ValueID,
                             rs.SalesOrg_6_ValueID,
                             rs.SalesOrg_7_ValueID,
                             rs.SalesOrg_8_ValueID,
                             rs.SalesOrg_9_ValueID,
                             rs.SalesForce_0_ID,
                             rs.SalesForce_1_ID,
                             rs.SalesForce_2_ID,
                             rs.SalesForce_3_ID,
                             rs.SalesForce_4_ID,
                             rs.SalesForce_5_ID,
                             rs.SalesForce_6_ID,
                             rs.SalesForce_7_ID,
                             rs.SalesForce_8_ID,
                             rs.SalesForce_9_ID;

                    EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
                                                                             @_Template,
                                                                             @_Source,
                                                                             @_Type,
                                                                             @_Formula,
                                                                             @_IsValiDateValidDistance,
                                                                             @_IsValidDateInvalidDistance,
                                                                             @_IsInvalidDate,
                                                                             @_IsDistributorSell;

                    ---- KPI for PDA
                    EXEC dbo.sp_DMS_Baseline_KPI_For_SFA @_CompanyID,
                                                         @_Template,
                                                         @_Source,
                                                         @_Type,
                                                         @_Formula,
                                                         @_IsValiDateValidDistance,
                                                         @_IsValidDateInvalidDistance,
                                                         @_IsInvalidDate,
                                                         @_IsDistributorSell,
                                                         @_KPICD;

                END;

                ELSE IF @_Template = 'ASO' -- ASO theo Group SP MRC
                BEGIN
                    DELETE #tmpCM;
                    INSERT INTO #tmpCM
                    SELECT DISTINCT
                        OrigOrderNbr,
                        OrigOrderType
                    FROM #tmpSORawSales ss
                    WHERE ss.OrderDate
                          BETWEEN @_TempFromDate AND @_TempToDate
                          AND ss.OrderType = 'CM';

                    INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
                    SELECT rs.CompanyID,
                           rs.SalespersonID,
                           r.SalesForceID,
                           r.SalesAreaID,
                           rs.ROUTE,
                           NULL,
                           rs.SourceType,
                           rs.CustomerID,
                           rs.CustomerLocationID,
                           rs.IsValidDate,
                           rs.IsValidDistance,
                           Multiply = CASE
                                          WHEN rs.IsRevenueDeduction = 1 THEN
                                              -1
                                          ELSE
                                              1
                                      END,
                           PC = 1,
                           SKU = COUNT(DISTINCT rs.InventoryID),
                           TranAmt = SUM(rs.TranAmt),
                           ShippedQty = SUM(rs.ShippedQty),
                           rs.SalesOrg_0_ValueID,
                           rs.SalesOrg_1_ValueID,
                           rs.SalesOrg_2_ValueID,
                           rs.SalesOrg_3_ValueID,
                           rs.SalesOrg_4_ValueID,
                           rs.SalesOrg_5_ValueID,
                           rs.SalesOrg_6_ValueID,
                           rs.SalesOrg_7_ValueID,
                           rs.SalesOrg_8_ValueID,
                           rs.SalesOrg_9_ValueID,
                           rs.SalesForce_0_ID,
                           rs.SalesForce_1_ID,
                           rs.SalesForce_2_ID,
                           rs.SalesForce_3_ID,
                           rs.SalesForce_4_ID,
                           rs.SalesForce_5_ID,
                           rs.SalesForce_6_ID,
                           rs.SalesForce_7_ID,
                           rs.SalesForce_8_ID,
                           rs.SalesForce_9_ID,
                           Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
                    FROM #tmpSORawSales rs
                        LEFT JOIN MRCDMS..DMSViewInventoryItem inv
                            ON rs.CompanyID = inv.CompanyID
                               AND rs.InventoryID = inv.InventoryID
                        LEFT JOIN #tmpRoute r
                            ON r.CompanyID = rs.CompanyID
                               AND r.SalespersonID = rs.SalespersonID
                               AND r.ROUTE = rs.ROUTE
                               AND rs.OrderDate
                               BETWEEN r.EffectiveDate AND r.ExpirationDate
                    WHERE rs.OrderDate
                          BETWEEN @_TempFromDate AND @_TempToDate
                          AND
                          (
                              @_ExcludeReturnOrder = 1
                              OR NOT EXISTS
                    (
                        SELECT *
                        FROM #tmpCM cm
                        WHERE cm.OrigOrderNbr = rs.OrderNbr
                              AND cm.OrigOrderType = rs.OrderType
                    )
                          )
                          AND
                          (
                              ISNULL(@_GroupID, '') = ''
                              OR
                              (
                                  EXISTS
                    (
                        SELECT TOP 1
                            *
                        FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
                        WHERE igd.CompanyID = @_CompanyID
                              AND igd.CodegGroupProd = @_GroupID
                              AND igd.InventoryID = rs.InventoryID
                    )
                                  OR EXISTS
                    (
                        SELECT *
                        FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
                        WHERE gr.CompanyID = @_CompanyID
                              AND gr.CodegGroupProd = @_GroupID
                              AND inv.[HierarchyID] = gr.[HierarchyID]
                              OR gr.Attribute0 = inv.Attribute0ID
                              OR gr.Attribute1 = inv.Attribute1ID
                              OR gr.Attribute2 = inv.Attribute2ID
                              OR gr.Attribute3 = inv.Attribute3ID
                              OR gr.Attribute4 = inv.Attribute4ID
                              OR gr.Attribute5 = inv.Attribute5ID
                              OR gr.Attribute6 = inv.Attribute6ID
                              OR gr.Attribute7 = inv.Attribute7ID
                              OR gr.Attribute8 = inv.Attribute8ID
                              OR gr.Attribute9 = inv.Attribute9ID
                    )
                              )
                          )
                    GROUP BY rs.CompanyID,
                             rs.SalespersonID,
                             r.SalesForceID,
                             r.SalesAreaID,
                             rs.ROUTE,
                             rs.SourceType,
                             rs.CustomerID,
                             rs.CustomerLocationID,
                             rs.IsValidDate,
                             rs.IsValidDistance,
                             rs.IsRevenueDeduction,
                             rs.SalesOrg_0_ValueID,
                             rs.SalesOrg_1_ValueID,
                             rs.SalesOrg_2_ValueID,
                             rs.SalesOrg_3_ValueID,
                             rs.SalesOrg_4_ValueID,
                             rs.SalesOrg_5_ValueID,
                             rs.SalesOrg_6_ValueID,
                             rs.SalesOrg_7_ValueID,
                             rs.SalesOrg_8_ValueID,
                             rs.SalesOrg_9_ValueID,
                             rs.SalesForce_0_ID,
                             rs.SalesForce_1_ID,
                             rs.SalesForce_2_ID,
                             rs.SalesForce_3_ID,
                             rs.SalesForce_4_ID,
                             rs.SalesForce_5_ID,
                             rs.SalesForce_6_ID,
                             rs.SalesForce_7_ID,
                             rs.SalesForce_8_ID,
                             rs.SalesForce_9_ID
                    HAVING SUM(rs.ShippedQty) >= @_MinSKUVolume
                           AND SUM(rs.TranAmt) >= @_MinSKURevenue;

                    EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
                                                                             @_Template,
                                                                             @_Source,
                                                                             @_Type,
                                                                             @_Formula,
                                                                             @_IsValiDateValidDistance,
                                                                             @_IsValidDateInvalidDistance,
                                                                             @_IsInvalidDate,
                                                                             @_IsDistributorSell;
                    --select * from DMSBLObjectKPITemp--1
                    ---- KPI for PDA
                    EXEC dbo.sp_DMS_Baseline_KPI_For_SFA @_CompanyID,
                                                         @_Template,
                                                         @_Source,
                                                         @_Type,
                                                         @_Formula,
                                                         @_IsValiDateValidDistance,
                                                         @_IsValidDateInvalidDistance,
                                                         @_IsInvalidDate,
                                                         @_IsDistributorSell,
                                                         @_KPICD;

                END;
                ---- Tính KPI ASO theo hình
                ELSE IF @_Template = 'ASOIMG'
                        OR @_Source = 'CU'
                           AND @_Type = 'I'
                BEGIN
                    PRINT 'Lấy dữ liệu hình ảnh SM';
                END;
                ---- Tính KPI viếng thăm KH
                ELSE IF @_Template = 'VISIT'
                        OR @_Source = 'CU'
                           AND @_Type = 'V'
                BEGIN
                    INSERT INTO dbo.DMSBLObjectKPITemp
                    (
                        CompanyID,
                        ObjectID,
                        Actual
                    )
                    SELECT rsd.CompanyID,
                           rsd.SalespersonID,
                           SUM(rsd.TotalActualVisit)
                    FROM #tmpRouteSalesData rsd
                    WHERE rsd.TranDate
                    BETWEEN @_FromDate AND @_ToDate
                    GROUP BY rsd.CompanyID,
                             rsd.SalespersonID;
                END;
                ---- Tính KPI mua hàng
                ELSE IF @_Source = 'PO'
                BEGIN
                    INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
                    (
                        CompanyID,
                        TranAmt,
                        ShippedQty,
                        SalesOrg_0_ValueID,
                        SalesOrg_1_ValueID,
                        SalesOrg_2_ValueID,
                        SalesOrg_3_ValueID,
                        SalesOrg_4_ValueID,
                        SalesOrg_5_ValueID,
                        SalesOrg_6_ValueID,
                        SalesOrg_7_ValueID,
                        SalesOrg_8_ValueID,
                        SalesOrg_9_ValueID,
                        SalesForce_0_ID,
                        SalesForce_1_ID,
                        SalesForce_2_ID,
                        SalesForce_3_ID,
                        SalesForce_4_ID,
                        SalesForce_5_ID,
                        SalesForce_6_ID,
                        SalesForce_7_ID,
                        SalesForce_8_ID,
                        SalesForce_9_ID
                    )
                    SELECT rs.CompanyID,
                           rs.ReceivedAmt,
                           rs.ReceivedQty,
                           rs.SalesOrg_0_ValueID,
                           rs.SalesOrg_1_ValueID,
                           rs.SalesOrg_2_ValueID,
                           rs.SalesOrg_3_ValueID,
                           rs.SalesOrg_4_ValueID,
                           rs.SalesOrg_5_ValueID,
                           rs.SalesOrg_6_ValueID,
                           rs.SalesOrg_7_ValueID,
                           rs.SalesOrg_8_ValueID,
                           rs.SalesOrg_9_ValueID,
                           rs.SalesForce_0_ID,
                           rs.SalesForce_1_ID,
                           rs.SalesForce_2_ID,
                           rs.SalesForce_3_ID,
                           rs.SalesForce_4_ID,
                           rs.SalesForce_5_ID,
                           rs.SalesForce_6_ID,
                           rs.SalesForce_7_ID,
                           rs.SalesForce_8_ID,
                           rs.SalesForce_9_ID
                    FROM #tmpSIRawSales rs
                    WHERE rs.TranDate
                          BETWEEN @_FromDate AND @_ToDate
                          AND
                          (
                              @_GroupBy = 'A'
                              OR
                              (
                                  @_GroupBy = 'I'
                                  AND EXISTS
                    (
                        SELECT TOP 1
                            *
                        FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
                        WHERE igd.CompanyID = @_CompanyID
                              AND igd.CodegGroupProd = @_GroupID
                              AND igd.InventoryID = rs.InventoryID
                              AND ISNULL(igd.MinQty, 1) <= rs.ReceivedQty
                    )
                              )
                          );

                    EXEC dbo.sp_DMS_Baseline_KPI_SalesInObjectSales @_Type;
                END;
                ELSE IF @_Template = 'NEWCUSTOMER'
                BEGIN

                    INSERT INTO dbo.DMSBLObjectKPITemp
                    (
                        CompanyID,
                        ObjectID,
                        Actual
                    )
                    SELECT CompanyID,
                           Salesperson,
                           TotalOutlet = COUNT(1)
                    FROM MRCDMS.dbo.DMSCalculatorFrequencyPropose
                    WHERE CompanyID = @_CompanyID
                          AND createdDate
                          BETWEEN @_FromDate AND @_ToDate
                          AND ActionType = 'A'
                    GROUP BY CompanyID,
                             Salesperson;
                END;


            END;

            ELSE IF @_ObjectType = 'D'
            BEGIN
                ---- Tính KPI doanh số và số lượng
                IF @_Template IN ( 'REVENUE', 'QUANTITY' )
                   OR @_Source = 'SO'
                BEGIN
                    INSERT INTO dbo.DMSBLObjectKPITemp
                    (
                        CompanyID,
                        ObjectID,
                        ObjectAssignment,
                        Actual
                    )
                    SELECT rs.CompanyID,
                           rs.DistributorLocationID,
                           rs.SalesOrg_0_ValueID,
                           SUM(   CASE
                                      WHEN ISNULL(@_Type, 'A') = 'A' THEN
                                          rs.ReceivedAmt
                                      ELSE
                                          rs.ReceivedQty
                                  END
                              )
                    FROM #tmpSIRawSales rs
                    WHERE rs.TranDate
                          BETWEEN @_FromDate AND @_ToDate
                          AND
                          (
                              @_GroupBy = 'A'
                              OR
                              (
                                  @_GroupBy = 'I'
                                  AND EXISTS
                    (
                        SELECT TOP 1
                            *
                        FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
                        WHERE igd.CompanyID = @_CompanyID
                              AND igd.CodegGroupProd = @_GroupID
                              AND igd.InventoryID = rs.InventoryID
                              AND ISNULL(igd.MinQty, 1) <= rs.ReceivedQty
                    )
                              )
                          )
                    GROUP BY rs.CompanyID,
                             rs.DistributorLocationID,
                             rs.SalesOrg_0_ValueID;

                END;

            END;

            ---- Insert vào dữ liệu KPI

            IF @_ObjectType = 'S'
            BEGIN
                INSERT INTO #tmpKPI
                SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID,
                                                       tmp.EmployeeID
                                          ORDER BY tmp.EmployeeID ASC
                                         ),
                       CompanyID = tmp.CompanyID,
                       @_KPIID,
                       @_KPICD,
                       @_KPIDescr,
                       @_Source,
                       @_Type,
                       @_GroupBy,
                       @_GroupID,
                       @_Formula,
                       @_Template,
                       @_KPIPeriodNbr,
                       ISNULL(
                                 @_RefNbr,
                                 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
                                 + CONVERT(VARCHAR(20), @_ToDate, 111)
                             ),
                       @_Period,
                       @_FromDate,
                       @_ToDate,
                       ObjectID = tmp.EmployeeID,
                       TempObjectID = ISNULL(kpid.ObjectID, 0),
                       @_ObjectType,
                       tmp.ObjectAssignment,
                       kpid.TargetsSuggest,  ---- temp
                       kpid.TargetsAssigned, ---- temp
                       kpid.TargetsSuggest,
                       kpid.TargetsAssigned,
                       okpit.Actual1,
                       okpit.Actual2,
                       okpit.Actual3,
                       okpit.Actual4,
                       okpit.Actual
                FROM
                (
                    SELECT sf.CompanyID,
                           sf.EmployeeID,
                           ObjectAssignment = CASE
                                                  WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
                                                      r.ROUTE
                                                  ELSE
                                                      CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                              END
                    FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
                        JOIN #tmpSalesForce sf WITH (NOLOCK)
                            ON sf.CompanyID = objectPeriod.CompanyID
                               AND sf.SFHierachyID = objectPeriod.KPIObjectID
                        LEFT JOIN #tmpRoute r
                            ON r.CompanyID = sf.CompanyID
                               AND
                               (
                                   r.SalespersonID = sf.EmployeeID
                                   OR r.SalesForceID = sf.EmployeeID
                               )
                    WHERE objectPeriod.CompanyID = @_CompanyID
                          AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
                          AND
                          (
                              @_SalesAreaID IS NULL
                              OR @_Level = -1
                              OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
                              OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
                              OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
                              OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
                              OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
                              OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
                              OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
                              OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
                              OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
                              OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
                          )
                    GROUP BY sf.CompanyID,
                             sf.EmployeeID,
                             CASE
                                 WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
                                     r.ROUTE
                                 ELSE
                                     CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                             END
                ) tmp
                    LEFT JOIN
                    (
                        SELECT kpid.CompanyID,
                               kpid.ObjectID,
                               kpid.TargetsSuggest,
                               kpid.TargetsAssigned
                        FROM #tmpKPIDetail kpid
                        WHERE kpid.CodeListSalesID = @_KPIID
                              AND
                              (
                                  @_RefNbr LIKE 'Temp-%'
                                  OR kpid.RefNbr = @_RefNbr
                              )
                              AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
                              AND kpid.FromDate = @_FromDate
                              AND kpid.ToDate = @_ToDate
                    ) kpid
                        ON kpid.CompanyID = tmp.CompanyID
                           AND kpid.ObjectID = tmp.EmployeeID
                    LEFT JOIN dbo.DMSBLObjectKPITemp okpit WITH (NOLOCK)
                        ON okpit.CompanyID = tmp.CompanyID
                           AND okpit.ObjectID = tmp.EmployeeID
                           AND okpit.ObjectAssignment = tmp.ObjectAssignment
                WHERE kpid.ObjectID IS NOT NULL;
            END;
            ELSE IF @_ObjectType = 'D'
            BEGIN
                INSERT INTO #tmpKPI
                SELECT ROW_NUMBER() OVER (PARTITION BY dl.CompanyID,
                                                       dl.DistributorLocationID
                                          ORDER BY dl.DistributorLocationID ASC
                                         ),
                       CompanyID = dl.CompanyID,
                       @_KPIID,
                       @_KPICD,
                       @_KPIDescr,
                       @_Source,
                       @_Type,
                       @_GroupBy,
                       @_GroupID,
                       @_Formula,
                       @_Template,
                       @_KPIPeriodNbr,
                       ISNULL(
                                 @_RefNbr,
                                 'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
                                 + CONVERT(VARCHAR(20), @_ToDate, 111)
                             ),
                       @_Period,
                       @_FromDate,
                       @_ToDate,
                       ObjectID = dl.DistributorLocationID,
                       TempObjectID = ISNULL(kpid.ObjectID, 0),
                       @_ObjectType,
                       ObjectAssignment = dl.SalesOrg_0_ValueID,
                       kpid.TargetsSuggest,  ---- temp
                       kpid.TargetsAssigned, ---- temp
                       kpid.TargetsSuggest,
                       kpid.TargetsAssigned,
                       okpit.Actual1,
                       okpit.Actual2,
                       okpit.Actual3,
                       okpit.Actual4,
                       okpit.Actual
                FROM
                (
                    SELECT CompanyID,
                           DistributorLocationID,
                           SalesOrg_0_ValueID
                    FROM #tmpDistributorLocation
                    WHERE ExpirationDate IS NULL
                          AND
                          (
                              @_SalesAreaID IS NULL
                              OR @_Level = -1
                              OR SalesOrg_0_ValueID = @_SalesAreaID
                              OR SalesOrg_1_ValueID = @_SalesAreaID
                              OR SalesOrg_2_ValueID = @_SalesAreaID
                              OR SalesOrg_3_ValueID = @_SalesAreaID
                              OR SalesOrg_4_ValueID = @_SalesAreaID
                              OR SalesOrg_5_ValueID = @_SalesAreaID
                              OR SalesOrg_6_ValueID = @_SalesAreaID
                              OR SalesOrg_7_ValueID = @_SalesAreaID
                              OR SalesOrg_8_ValueID = @_SalesAreaID
                              OR SalesOrg_9_ValueID = @_SalesAreaID
                          )
                    GROUP BY CompanyID,
                             DistributorLocationID,
                             SalesOrg_0_ValueID
                ) dl
                    LEFT JOIN
                    (
                        SELECT kpid.CompanyID,
                               kpid.ObjectID,
                               kpid.TargetsSuggest,
                               kpid.TargetsAssigned
                        FROM #tmpKPIDetail kpid
                        WHERE kpid.CodeListSalesID = @_KPIID
                              AND
                              (
                                  @_RefNbr LIKE 'Temp-%'
                                  OR kpid.RefNbr = @_RefNbr
                              )
                              AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
                              AND kpid.FromDate = @_FromDate
                              AND kpid.ToDate = @_ToDate
                    ) kpid
                        ON kpid.CompanyID = dl.CompanyID
                           AND kpid.ObjectID = dl.DistributorLocationID
                    LEFT JOIN dbo.DMSBLObjectKPITemp okpit WITH (NOLOCK)
                        ON okpit.CompanyID = dl.CompanyID
                           AND okpit.ObjectID = dl.DistributorLocationID
                           AND okpit.ObjectAssignment = dl.SalesOrg_0_ValueID
                WHERE kpid.ObjectID IS NOT NULL;
            END;

            SET @_Min = @_Min + 1;
        END;
    END; ---- Tính các KPI bình thường


    BEGIN ---- Tính KPI AVGPC (%PC),
        ---- Formula: Total PC/ Visit ( Actual or MCP)
        DELETE FROM #tmpKPIList;

        INSERT INTO #tmpKPIList
        SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
               *
        FROM
        (
            SELECT CompanyID,
                   CodeListSalesID,
                   CodeListSalesCD,
                   RefNbr,
                   KPIPeriodNbr,
                   Descr = MAX(Descr),
                   SOURCE = MAX(SOURCE),
                   TYPE = MAX(TYPE),
                   GroupBy = MAX(GroupBy),
                   GroupID = MAX(GroupID),
                   Formula = MAX(Formula),
                   PERIOD = MAX(PERIOD),
                   SalesOrgID = MAX(SalesOrgID),
                   SalesAreaID = MAX(SalesAreaID),
                   LEVEL = MAX(LEVEL),
                   Template = MAX(Template),
                   FromDate,
                   ToDate,
                   ObjectType = MAX(ObjectType),
                   CorrectRouteAndCorrectCoordinates,
                   CorrectRouteAndIncorrectCoordinates,
                   IncorrectRoute,
                   DistributorsSell,
                   MinSKUVolume,
                   MinSKURevenue,
                   ExcludeReturnOrder
            FROM #tmpKPIDetail
            -- lấy AVG theo actual visit hoặc MCP
            WHERE Template IN ( 'AVGPC', 'AVGPCAC', 'PCINDAY' )
                  AND ObjectType = 'S'
            GROUP BY CompanyID,
                     CodeListSalesID,
                     CodeListSalesCD,
                     RefNbr,
                     KPIPeriodNbr,
                     FromDate,
                     ToDate,
                     CorrectRouteAndCorrectCoordinates,
                     CorrectRouteAndIncorrectCoordinates,
                     IncorrectRoute,
                     DistributorsSell,
                     MinSKUVolume,
                     MinSKURevenue,
                     ExcludeReturnOrder
        ) tmp;

        SELECT @_Max = MAX(RowNumber)
        FROM #tmpKPIList;
        DECLARE @_step INT = 1;

        WHILE @_step <= 2
        BEGIN
            SELECT @_Min = 1;
            WHILE @_Min <= @_Max
            BEGIN
                ---- Xóa dữ liệu SM để tính lại đối với KPI mới
                DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
                DELETE FROM dbo.DMSBLObjectKPITemp;
                DELETE FROM #tmpTotalPC;
                DELETE FROM #tmpTotalLine;

                ---- Lấy thông tin công thức KPI
                SELECT @_KPIID = CodeListSalesID,
                       @_KPICD = CodeListSalesCD,
                       @_KPIDescr = Descr,
                       @_RefNbr = RefNbr,
                       @_KPIPeriodNbr = KPIPeriodNbr,
                       @_Source = SOURCE,
                       @_Type = TYPE,
                       @_GroupBy = ISNULL(GroupBy, 'A'),
                       @_GroupID = GroupID,
                       @_Formula = Formula,
                       @_Template = Template,
                       @_Period = PERIOD,
                       @_SalesAreaID = SalesAreaID,
                       @_Level = LEVEL,
                       @_FromDate = FromDate,
                       @_ToDate = ToDate,
                       @_ObjectType = ObjectType,
                       @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
                       @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
                       @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
                       @_IsDistributorSell = ISNULL(DistributorsSell, 0),
                       @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
                       @_MinSKURevenue = MinSKURevenue,
                       @_MinSKUVolume = MinSKUVolume
                FROM #tmpKPIList
                WHERE RowNumber = @_Min;

                IF @_TempFromDate IS NULL
                   OR @_TempToDate IS NULL
                   OR @_TempFromDate != @_FromDate
                   OR @_TempToDate != @_ToDate
                BEGIN
                    DELETE FROM #tmpSalesTerritory;
                    DELETE FROM #tmpRoute;
                    DELETE FROM #tmpSalesForce;

                    ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
                    INSERT INTO #tmpSalesTerritory
                    SELECT *
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
                                                                           st.SalesOrg_0_ValueID
                                                              ORDER BY st.EffectiveDate DESC
                                                             ),
                               st.CompanyID,
                               st.SalesOrg_0_ValueID,
                               st.SalesOrg_1_ValueID,
                               st.SalesOrg_2_ValueID,
                               st.SalesOrg_3_ValueID,
                               st.SalesOrg_4_ValueID,
                               st.SalesOrg_5_ValueID,
                               st.SalesOrg_6_ValueID,
                               st.SalesOrg_7_ValueID,
                               st.SalesOrg_8_ValueID,
                               st.SalesOrg_9_ValueID
                        FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
                        WHERE st.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
                                  OR st.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                    ) tmp
                    WHERE tmp.RowNumber = 1;

                    ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
                    INSERT INTO #tmpRoute
                    SELECT tmp.CompanyID,
                           tmp.SalespersonID,
                           tmp.ROUTE,
                           tmp.SalesForceID,
                           tmp.SalesAreaID,
                           tmp.EffectiveDate,
                           tmp.ExpirationDate,
                           st.SalesOrg_0_ValueID,
                           st.SalesOrg_1_ValueID,
                           st.SalesOrg_2_ValueID,
                           st.SalesOrg_3_ValueID,
                           st.SalesOrg_4_ValueID,
                           st.SalesOrg_5_ValueID,
                           st.SalesOrg_6_ValueID,
                           st.SalesOrg_7_ValueID,
                           st.SalesOrg_8_ValueID,
                           st.SalesOrg_9_ValueID
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
                                                                           rs.SalespersonID
                                                              ORDER BY rs.EffectiveDate DESC
                                                             ),
                               rs.CompanyID,
                               rs.SalespersonID,
                               ROUTE = rs.RouteCD,
                               rs.SalesForceID,
                               sph.SalesAreaID,
                               rs.EffectiveDate,
                               ExpirationDate = ISNULL(rs.EndDate, GETDATE())
                        FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
						JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
                        WHERE rs.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR rs.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                        UNION ALL
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
                                                                           rs.PreviousSalesMan
                                                              ORDER BY rs.EffectiveDate DESC
                                                             ),
                               rs.CompanyID,
                               SalespersonID = rs.PreviousSalesMan,
                               ROUTE = rs.RouteCD,
                               rs.SalesForceID,
                               sph.SalesAreaID,
                               rs.StartDatePre,
                               rs.EndDatePre
                        FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
						JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
                        WHERE rs.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR rs.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                              AND rs.PreviousSalesMan IS NOT NULL
                    ) tmp
                        JOIN #tmpSalesTerritory st
                            ON st.CompanyID = tmp.CompanyID
                               AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
                    WHERE tmp.RowNumber = 1;

                    ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
                    INSERT INTO #tmpSalesForce
                    SELECT tmp.CompanyID,
                           tmp.EmployeeID,
                           tmp.SFHierachyID,
                           tmp.TerritoryType,
                           tmp.SalesOrgValueID,
                           st.SalesOrg_0_ValueID,
                           st.SalesOrg_1_ValueID,
                           st.SalesOrg_2_ValueID,
                           st.SalesOrg_3_ValueID,
                           st.SalesOrg_4_ValueID,
                           st.SalesOrg_5_ValueID,
                           st.SalesOrg_6_ValueID,
                           st.SalesOrg_7_ValueID,
                           st.SalesOrg_8_ValueID,
                           st.SalesOrg_9_ValueID
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
                                                                           sf.EmployeeID
                                                              ORDER BY sf.EffectiveDate DESC
                                                             ),
                               sf.CompanyID,
                               sf.EmployeeID,
                               sf.SFHierachyID,
                               sf.TerritoryType,
                               sf.SalesOrgValueID
                        FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
                        WHERE sf.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
                                  OR sf.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                    ) tmp
                        LEFT JOIN #tmpSalesTerritory st
                            ON st.CompanyID = tmp.CompanyID
                               AND
                               (
                                   st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
                               )
                    WHERE tmp.RowNumber = 1;

                    SET @_TempFromDate = @_FromDate;
                    SET @_TempToDate = @_ToDate;
                END;

                ---- Chỉ tính KPI cho SM
                IF @_step = 1
                BEGIN
                    ---- Tính KPI PC tạm để tính ra AVG PC
                    SELECT rs.CompanyID,
                           rs.SalespersonID,
                           r.SalesForceID,
                           r.SalesAreaID,
                           rs.ROUTE,
                           rs.OrderDate,
                           rs.OrderNbr,
                           rs.SourceType,
                           rs.CustomerID,
                           rs.CustomerLocationID,
                           rs.IsValidDate,
                           rs.IsValidDistance,
                           Multiply = CASE
                                          WHEN rs.IsRevenueDeduction = 1 THEN
                                              -1
                                          ELSE
                                              1
                                      END,
                           PC = COUNT(DISTINCT rs.CustomerID),
                           SKU = COUNT(DISTINCT rs.InventoryID),
                           TranAmt = SUM(rs.TranAmt),
                           ShippedQty = SUM(rs.ShippedQty),
                           rs.SalesOrg_0_ValueID,
                           rs.SalesOrg_1_ValueID,
                           rs.SalesOrg_2_ValueID,
                           rs.SalesOrg_3_ValueID,
                           rs.SalesOrg_4_ValueID,
                           rs.SalesOrg_5_ValueID,
                           rs.SalesOrg_6_ValueID,
                           rs.SalesOrg_7_ValueID,
                           rs.SalesOrg_8_ValueID,
                           rs.SalesOrg_9_ValueID,
                           rs.SalesForce_0_ID,
                           rs.SalesForce_1_ID,
                           rs.SalesForce_2_ID,
                           rs.SalesForce_3_ID,
                           rs.SalesForce_4_ID,
                           rs.SalesForce_5_ID,
                           rs.SalesForce_6_ID,
                           rs.SalesForce_7_ID,
                           rs.SalesForce_8_ID,
                           rs.SalesForce_9_ID,
                           Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
                    INTO #tempDMSBLSalesObject
                    FROM #tmpSORawSales rs
                        LEFT JOIN MRCDMS..DMSViewInventoryItem inv
                            ON rs.CompanyID = inv.CompanyID
                               AND rs.InventoryID = inv.InventoryID
                        LEFT JOIN #tmpRoute r
                            ON r.CompanyID = rs.CompanyID
                               AND r.SalespersonID = rs.SalespersonID
                               AND r.ROUTE = rs.ROUTE
                               AND rs.OrderDate
                               BETWEEN r.EffectiveDate AND r.ExpirationDate
                    WHERE rs.OrderDate
                          BETWEEN @_TempFromDate AND @_TempToDate
                          AND rs.OrderType IN (   (CASE
                                                       WHEN @_ExcludeReturnOrder = 0 THEN
                                                           'CM'
                                                       ELSE
                                                           ''
                                                   END
                                                  ), 'SO', 'IN'
                                              )
                          AND
                          (
                              ISNULL(@_GroupID, '') = ''
                              OR
                              (
                                  EXISTS
                    (
                        SELECT TOP 1
                            *
                        FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
                        WHERE igd.CompanyID = @_CompanyID
                              AND igd.CodegGroupProd = @_GroupID
                              AND igd.InventoryID = rs.InventoryID
                    )
                                  OR EXISTS
                    (
                        SELECT *
                        FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
                        WHERE gr.CompanyID = @_CompanyID
                              AND gr.CodegGroupProd = @_GroupID
                              AND inv.[HierarchyID] = gr.[HierarchyID]
                              OR gr.Attribute0 = inv.Attribute0ID
                              OR gr.Attribute1 = inv.Attribute1ID
                              OR gr.Attribute2 = inv.Attribute2ID
                              OR gr.Attribute3 = inv.Attribute3ID
                              OR gr.Attribute4 = inv.Attribute4ID
                              OR gr.Attribute5 = inv.Attribute5ID
                              OR gr.Attribute6 = inv.Attribute6ID
                              OR gr.Attribute7 = inv.Attribute7ID
                              OR gr.Attribute8 = inv.Attribute8ID
                              OR gr.Attribute9 = inv.Attribute9ID
                    )
                              )
                          )
                    GROUP BY rs.CompanyID,
                             rs.SalespersonID,
                             r.SalesForceID,
                             r.SalesAreaID,
                             rs.ROUTE,
                             rs.OrderDate,
                             rs.OrderNbr,
                             rs.SourceType,
                             rs.CustomerID,
                             rs.CustomerLocationID,
                             rs.IsValidDate,
                             rs.IsValidDistance,
                             rs.IsRevenueDeduction,
                             rs.SalesOrg_0_ValueID,
                             rs.SalesOrg_1_ValueID,
                             rs.SalesOrg_2_ValueID,
                             rs.SalesOrg_3_ValueID,
                             rs.SalesOrg_4_ValueID,
                             rs.SalesOrg_5_ValueID,
                             rs.SalesOrg_6_ValueID,
                             rs.SalesOrg_7_ValueID,
                             rs.SalesOrg_8_ValueID,
                             rs.SalesOrg_9_ValueID,
                             rs.SalesForce_0_ID,
                             rs.SalesForce_1_ID,
                             rs.SalesForce_2_ID,
                             rs.SalesForce_3_ID,
                             rs.SalesForce_4_ID,
                             rs.SalesForce_5_ID,
                             rs.SalesForce_6_ID,
                             rs.SalesForce_7_ID,
                             rs.SalesForce_8_ID,
                             rs.SalesForce_9_ID
                    HAVING SUM(rs.ShippedQty) >= @_MinSKUVolume
                           AND ABS(SUM(rs.TranAmt)) >= @_MinSKURevenue;

                    INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
                    SELECT CompanyID,
                           SalespersonID,
                           SalesForceID,
                           SalesAreaID,
                           ROUTE,
                           OrderDate,
                           SourceType,
                           CustomerID,
                           CustomerLocationID,
                           IsValidDate,
                           IsValidDistance,
                           Multiply,
                           PC,
                           SKU = SUM(SKU),
                           TranAmt = SUM(TranAmt),
                           ShippedQty = SUM(ShippedQty),
                           SalesOrg_0_ValueID,
                           SalesOrg_1_ValueID,
                           SalesOrg_2_ValueID,
                           SalesOrg_3_ValueID,
                           SalesOrg_4_ValueID,
                           SalesOrg_5_ValueID,
                           SalesOrg_6_ValueID,
                           SalesOrg_7_ValueID,
                           SalesOrg_8_ValueID,
                           SalesOrg_9_ValueID,
                           SalesForce_0_ID,
                           SalesForce_1_ID,
                           SalesForce_2_ID,
                           SalesForce_3_ID,
                           SalesForce_4_ID,
                           SalesForce_5_ID,
                           SalesForce_6_ID,
                           SalesForce_7_ID,
                           SalesForce_8_ID,
                           SalesForce_9_ID,
                           Hie3 = SUM(Hie3)
                    FROM #tempDMSBLSalesObject
                    GROUP BY CompanyID,
                             SalespersonID,
                             SalesForceID,
                             SalesAreaID,
                             ROUTE,
                             OrderDate,
                             SourceType,
                             CustomerID,
                             CustomerLocationID,
                             IsValidDate,
                             IsValidDistance,
                             Multiply,
                             PC,
                             SalesOrg_0_ValueID,
                             SalesOrg_1_ValueID,
                             SalesOrg_2_ValueID,
                             SalesOrg_3_ValueID,
                             SalesOrg_4_ValueID,
                             SalesOrg_5_ValueID,
                             SalesOrg_6_ValueID,
                             SalesOrg_7_ValueID,
                             SalesOrg_8_ValueID,
                             SalesOrg_9_ValueID,
                             SalesForce_0_ID,
                             SalesForce_1_ID,
                             SalesForce_2_ID,
                             SalesForce_3_ID,
                             SalesForce_4_ID,
                             SalesForce_5_ID,
                             SalesForce_6_ID,
                             SalesForce_7_ID,
                             SalesForce_8_ID,
                             SalesForce_9_ID;
                    DROP TABLE #tempDMSBLSalesObject;

                    EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
                                                                             'PC',
                                                                             NULL,
                                                                             NULL,
                                                                             NULL,
                                                                             @_IsValiDateValidDistance,
                                                                             @_IsValidDateInvalidDistance,
                                                                             @_IsInvalidDate,
                                                                             @_IsDistributorSell;

                    INSERT INTO #tmpTotalPC
                    SELECT *
                    FROM dbo.DMSBLObjectKPITemp;
                    ---- Kết thúc tính KPI PC tạm
                    --  TÍNH AVG PC theo lịch đi viếng thăm
                    CREATE TABLE #tmpSalesManDay
                    (
                        CompanyID INT NOT NULL,
                        SalespersonID INT NULL,
                        Minday DATETIME NULL
                    );
                    INSERT INTO #tmpSalesManDay
                    SELECT mcp.CompanyID,
                           mcp.SalespersonID,
                           Minday = MIN(VisitDate)
                    FROM DMSBLMCP mcp
                        LEFT JOIN MRCDMS..DMSBaselineClosedDate cl
                            ON mcp.CompanyID = cl.CompanyID
                               AND mcp.DistributorID = cl.BranchID
                    WHERE mcp.VisitDate
                          BETWEEN @_FromDate AND CASE
                                                     WHEN @_Template = 'PCINDAY'
                                                          AND @_ToDate > cl.ClosedDate THEN
                                                         cl.ClosedDate
                                                     ELSE
                                                         @_ToDate
                                                 END
                          AND mcp.VisitDate NOT IN (
                                                       SELECT ToDate FROM MRCDMS..DMSHoliday WHERE CompanyID = @_CompanyID
                                                   )
                    GROUP BY mcp.CompanyID,
                             mcp.SalespersonID;
                    ------------------------------
                    CREATE TABLE #tmpSubDay
                    (
                        CompanyID INT NOT NULL,
                        SRCode VARCHAR(20) NULL,
                        Mandays INT NULL
                    );
                    INSERT INTO #tmpSubDay
                    SELECT dt.CompanyID,
                           dt.SRCode,
                           Mandays = COUNT(DISTINCT OrderDate)
                    FROM GESO..DMSBiHoDetail dt
                        LEFT JOIN MRCDMS..DMSBaselineClosedDate cl
                            ON dt.CompanyID = cl.CompanyID
                               AND dt.DistributorID = cl.BranchID
                    WHERE dt.CompanyID = @_CompanyID
                          AND dt.STATUS = 'A'
                          AND dt.OrderDate
                          BETWEEN @_FromDate AND CASE
                                                     WHEN @_Template = 'PCINDAY'
                                                          AND @_ToDate > cl.ClosedDate THEN
                                                         cl.ClosedDate
                                                     ELSE
                                                         @_ToDate
                                                 END
                          AND dt.OrderDate <
                          (
                              SELECT Minday
                              FROM #tmpSalesManDay
                              WHERE dt.CompanyID = #tmpSalesManDay.CompanyID
                                    AND dt.SRCode = #tmpSalesManDay.SalespersonID
                          )
                          AND dt.OrderDate NOT IN (
                                                      SELECT ToDate FROM MRCDMS..DMSHoliday WHERE CompanyID = @_CompanyID
                                                  )
                    GROUP BY dt.CompanyID,
                             dt.SRCode;

                    DROP TABLE #tmpSalesManDay;
                    UPDATE kpipc
                    SET kpipc.Actual1 = CASE
                                            WHEN @_IsValiDateValidDistance = 1
                                                 AND ISNULL(pd.PlanDay, 0) > 0 THEN
                                                kpipc.Actual1 / (pd.PlanDay + ISNULL(sub.Mandays, 0))
                                            ELSE
                                                0
                                        END,
                        kpipc.Actual2 = CASE
                                            WHEN @_IsValidDateInvalidDistance = 1
                                                 AND ISNULL(pd.PlanDay, 0) > 0 THEN
                                                kpipc.Actual2 / (pd.PlanDay + ISNULL(sub.Mandays, 0))
                                            ELSE
                                                0
                                        END,
                        kpipc.Actual3 = CASE
                                            WHEN @_IsInvalidDate = 1
                                                 AND ISNULL(pd.PlanDay, 0) > 0 THEN
                                                kpipc.Actual3 / (pd.PlanDay + ISNULL(sub.Mandays, 0))
                                            ELSE
                                                0
                                        END,
                        kpipc.Actual4 = CASE
                                            WHEN @_IsDistributorSell = 1
                                                 AND ISNULL(pd.PlanDay, 0) > 0 THEN
                                                kpipc.Actual4 / (pd.PlanDay + ISNULL(sub.Mandays, 0))
                                            ELSE
                                                0
                                        END,
                        kpipc.Actual = CASE
                                           WHEN ISNULL(pd.PlanDay, 0) > 0 THEN
                                               kpipc.Actual / (pd.PlanDay + ISNULL(sub.Mandays, 0))
                                           ELSE
                                               0
                                       END
                    FROM #tmpTotalPC kpipc
                        LEFT JOIN
                        (
                            SELECT mcp.CompanyID,
                                   SalespersonID,
                                   PlanDay = CASE
                                                 WHEN @_Template = 'PCINDAY' THEN
                                                     COUNT(DISTINCT mcp.VisitDate)
                                             END
                            FROM MRCDMS..DMSVisitPlan mcp
                                LEFT JOIN MRCDMS..DMSBaselineClosedDate cl
                                    ON mcp.CompanyID = cl.CompanyID
                                       AND mcp.BranchID = cl.BranchID
                            WHERE mcp.VisitDate
                                  BETWEEN @_FromDate AND CASE
                                                             WHEN cl.ClosedDate < @_ToDate THEN
                                                                 cl.ClosedDate
                                                             ELSE
                                                                 @_ToDate
                                                         END
                                  AND mcp.VisitDate NOT IN (
                                                               SELECT ToDate FROM MRCDMS..DMSHoliday WHERE CompanyID = @_CompanyID
                                                           )
                            GROUP BY mcp.CompanyID,
                                     mcp.SalespersonID
                        ) pd
                            ON pd.CompanyID = kpipc.CompanyID
                               AND pd.SalespersonID = kpipc.ObjectID
                        LEFT JOIN #tmpSubDay sub
                            ON sub.CompanyID = kpipc.CompanyID
                               AND sub.SRCode = kpipc.ObjectID;


                    DROP TABLE #tmpSubDay;

                    INSERT INTO #tmpKPI
                    SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
                           tmp.CompanyID,
                           @_KPIID,
                           @_KPICD,
                           @_KPIDescr,
                           @_Source,
                           @_Type,
                           @_GroupBy,
                           @_GroupID,
                           @_Formula,
                           @_Template,
                           @_KPIPeriodNbr,
                           ISNULL(
                                     @_RefNbr,
                                     'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
                                     + CONVERT(VARCHAR(20), @_ToDate, 111)
                                 ),
                           @_Period,
                           @_FromDate,
                           @_ToDate,
                           ObjectID = tmp.EmployeeID,
                           TempObjectID = ISNULL(kpid.ObjectID, 0),
                           'S',
                           tmp.ObjectAssignment,
                           kpid.TargetsSuggest,  ---- temp
                           kpid.TargetsAssigned, ---- temp
                           kpid.TargetsSuggest,
                           kpid.TargetsAssigned,
                           kpia.Actual1,
                           kpia.Actual2,
                           kpia.Actual3,
                           kpia.Actual4,
                           kpia.Actual
                    FROM
                    (
                        SELECT sf.CompanyID,
                               sf.EmployeeID,
                               ObjectAssignment = r.ROUTE
                        FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
                            JOIN #tmpSalesForce sf WITH (NOLOCK)
                                ON sf.CompanyID = objectPeriod.CompanyID
                                   AND sf.SFHierachyID = objectPeriod.KPIObjectID
                            JOIN #tmpRoute r
                                ON r.CompanyID = sf.CompanyID
                                   AND r.SalespersonID = sf.EmployeeID
                        WHERE objectPeriod.CompanyID = @_CompanyID
                              AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
                              AND sf.TerritoryType = 'S'
                              AND
                              (
                                  @_SalesAreaID IS NULL
                                  OR @_Level = -1
                                  OR r.SalesOrg_1_ValueID = @_SalesAreaID
                                  OR r.SalesOrg_2_ValueID = @_SalesAreaID
                                  OR r.SalesOrg_3_ValueID = @_SalesAreaID
                                  OR r.SalesOrg_4_ValueID = @_SalesAreaID
                                  OR r.SalesOrg_5_ValueID = @_SalesAreaID
                                  OR r.SalesOrg_6_ValueID = @_SalesAreaID
                                  OR r.SalesOrg_7_ValueID = @_SalesAreaID
                                  OR r.SalesOrg_8_ValueID = @_SalesAreaID
                                  OR r.SalesOrg_9_ValueID = @_SalesAreaID
                              )
                        GROUP BY sf.CompanyID,
                                 sf.EmployeeID,
                                 r.ROUTE
                    ) tmp
                        LEFT JOIN
                        (
                            SELECT kpid.CompanyID,
                                   kpid.ObjectID,
                                   kpid.KPIPeriodNbr,
                                   kpid.FromDate,
                                   kpid.ToDate,
                                   kpid.TargetsSuggest,
                                   kpid.TargetsAssigned
                            FROM #tmpKPIDetail kpid
                            WHERE kpid.CodeListSalesID = @_KPIID
                                  AND
                                  (
                                      @_RefNbr LIKE 'Temp-%'
                                      OR kpid.RefNbr = @_RefNbr
                                  )
                                  AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
                                  AND kpid.FromDate = @_FromDate
                                  AND kpid.ToDate = @_ToDate
                        ) kpid
                            ON kpid.CompanyID = tmp.CompanyID
                               AND kpid.ObjectID = tmp.EmployeeID
                        LEFT JOIN #tmpTotalPC kpia
                            ON kpia.CompanyID = tmp.CompanyID
                               AND kpia.ObjectID = tmp.EmployeeID
                               AND kpia.ObjectAssignment = tmp.ObjectAssignment
                    WHERE kpid.ObjectID IS NOT NULL;
                END;
                ---- Tính KPI cho các đối tượng còn lại
                ELSE IF @_step = 2
                BEGIN
                    INSERT INTO #tmpKPI
                    SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
                           tmp.CompanyID,
                           @_KPIID,
                           @_KPICD,
                           @_KPIDescr,
                           @_Source,
                           @_Type,
                           @_GroupBy,
                           @_GroupID,
                           @_Formula,
                           @_Template,
                           @_KPIPeriodNbr,
                           ISNULL(
                                     @_RefNbr,
                                     'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
                                     + CONVERT(VARCHAR(20), @_ToDate, 111)
                                 ),
                           @_Period,
                           @_FromDate,
                           @_ToDate,
                           ObjectID = tmp.EmployeeID,
                           TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
                           'S',
                           tmp.ObjectAssignment,
                           TargetsSuggest = MAX(kpid.TargetsSuggest),   ---- temp
                           TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
                           TargetsSuggest = MAX(kpid.TargetsSuggest),
                           TargetsAssigned = MAX(kpid.TargetsAssigned),
                           Actual1 = SUM(kpi.Actual1),
                           Actual2 = SUM(kpi.Actual2),
                           Actual3 = SUM(kpi.Actual3),
                           Actual4 = SUM(kpi.Actual4),
                           Actual = SUM(kpi.Actual)
                    FROM
                    (
                        SELECT sf.CompanyID,
                               sf.EmployeeID,
                               ObjectAssignment = CASE
                                                      WHEN sf.TerritoryType = 'D' THEN
                                                          r.ROUTE
                                                      ELSE
                                                          CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                                  END,
                               sf.SalesOrg_0_ValueID
                        FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
                            JOIN #tmpSalesForce sf WITH (NOLOCK)
                                ON sf.CompanyID = objectPeriod.CompanyID
                                   AND sf.SFHierachyID = objectPeriod.KPIObjectID
                            LEFT JOIN #tmpRoute r
                                ON r.CompanyID = sf.CompanyID
                                   AND r.SalesForceID = sf.EmployeeID
                        WHERE objectPeriod.CompanyID = @_CompanyID
                              AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
                              AND sf.TerritoryType != 'S'
                              AND
                              (
                                  @_SalesAreaID IS NULL
                                  OR @_Level = -1
                                  OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
                              )
                        GROUP BY sf.CompanyID,
                                 sf.EmployeeID,
                                 CASE
                                     WHEN sf.TerritoryType = 'D' THEN
                                         r.ROUTE
                                     ELSE
                                         CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                 END,
                                 sf.SalesOrg_0_ValueID
                    ) tmp
                        LEFT JOIN
                        (
                            SELECT kpid.CompanyID,
                                   kpid.ObjectID,
                                   kpid.KPIPeriodNbr,
                                   kpid.FromDate,
                                   kpid.ToDate,
                                   kpid.TargetsSuggest,
                                   kpid.TargetsAssigned
                            FROM #tmpKPIDetail kpid
                            WHERE kpid.CodeListSalesID = @_KPIID
                                  AND
                                  (
                                      @_RefNbr LIKE 'Temp-%'
                                      OR kpid.RefNbr = @_RefNbr
                                  )
                                  AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
                                  AND kpid.FromDate = @_FromDate
                                  AND kpid.ToDate = @_ToDate
                        ) kpid
                            ON kpid.CompanyID = tmp.CompanyID
                               AND kpid.ObjectID = tmp.EmployeeID
                        LEFT JOIN
                        (
                            SELECT DISTINCT
                                kpi.CompanyID,
                                kpi.Actual1,
                                kpi.Actual2,
                                kpi.Actual3,
                                kpi.Actual4,
                                kpi.Actual,
                                r.ROUTE,
                                r.SalesAreaID
                            FROM #tmpKPI kpi
                                JOIN #tmpSalesForce sf
                                    ON sf.CompanyID = kpi.CompanyID
                                       AND sf.EmployeeID = kpi.ObjectID
                                       AND sf.TerritoryType = 'S'
                                JOIN #tmpRoute r
                                    ON r.CompanyID = sf.CompanyID
                                       AND r.SalespersonID = sf.EmployeeID
                            WHERE kpi.CodeListSalesID = @_KPIID
                                  AND kpi.FromDate = @_FromDate
                                  AND kpi.ToDate = @_ToDate
                        ) kpi
                            ON kpi.CompanyID = tmp.CompanyID
                               AND
                               (
                                   kpi.ROUTE = tmp.ObjectAssignment
                                   OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
                               )
                    WHERE kpid.ObjectID IS NOT NULL
                    GROUP BY tmp.CompanyID,
                             tmp.EmployeeID,
                             tmp.ObjectAssignment;
                END;

                SET @_Min = @_Min + 1;
            END;
            SET @_step = @_step + 1;
        END;

    END; ---- Tính KPI AVGPC (%PC),

    BEGIN ---- Tính KPI LPPC
        DELETE FROM #tmpKPIList;

        INSERT INTO #tmpKPIList
        SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
               *
        FROM
        (
            SELECT CompanyID,
                   CodeListSalesID,
                   CodeListSalesCD,
                   RefNbr,
                   KPIPeriodNbr,
                   Descr = MAX(Descr),
                   SOURCE = MAX(SOURCE),
                   TYPE = MAX(TYPE),
                   GroupBy = MAX(GroupBy),
                   GroupID = MAX(GroupID),
                   Formula = MAX(Formula),
                   PERIOD = MAX(PERIOD),
                   SalesOrgID = MAX(SalesOrgID),
                   SalesAreaID = MAX(SalesAreaID),
                   LEVEL = MAX(LEVEL),
                   Template = MAX(Template),
                   FromDate,
                   ToDate,
                   ObjectType = MAX(ObjectType),
                   CorrectRouteAndCorrectCoordinates,
                   CorrectRouteAndIncorrectCoordinates,
                   IncorrectRoute,
                   DistributorsSell,
                   MinSKUVolume,
                   MinSKURevenue,
                   ExcludeReturnOrder
            FROM #tmpKPIDetail
            WHERE Template = 'LPPC'
                  AND ObjectType = 'S'
            GROUP BY CompanyID,
                     CodeListSalesID,
                     CodeListSalesCD,
                     RefNbr,
                     KPIPeriodNbr,
                     FromDate,
                     ToDate,
                     CorrectRouteAndCorrectCoordinates,
                     CorrectRouteAndIncorrectCoordinates,
                     IncorrectRoute,
                     DistributorsSell,
                     MinSKUVolume,
                     MinSKURevenue,
                     ExcludeReturnOrder
        ) tmp;

        SELECT @_Max = MAX(RowNumber)
        FROM #tmpKPIList;

        SELECT @_step = 1;
        WHILE @_step <= 2
        BEGIN
            SELECT @_Min = 1;
            WHILE @_Min <= @_Max
            BEGIN
                ---- Xóa dữ liệu SM để tính lại đối với KPI mới
                DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
                DELETE FROM dbo.DMSBLObjectKPITemp;
                DELETE FROM #tmpTotalPC;
                DELETE FROM #tmpTotalLine;

                ---- Lấy thông tin công thức KPI
                SELECT @_KPIID = CodeListSalesID,
                       @_KPICD = CodeListSalesCD,
                       @_KPIDescr = Descr,
                       @_RefNbr = RefNbr,
                       @_KPIPeriodNbr = KPIPeriodNbr,
                       @_Source = SOURCE,
                       @_Type = TYPE,
                       @_GroupBy = ISNULL(GroupBy, 'A'),
                       @_GroupID = GroupID,
                       @_Formula = Formula,
                       @_Template = Template,
                       @_Period = PERIOD,
                       @_SalesAreaID = SalesAreaID,
                       @_Level = LEVEL,
                       @_FromDate = FromDate,
                       @_ToDate = ToDate,
                       @_ObjectType = ObjectType,
                       @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
                       @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
                       @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
                       @_IsDistributorSell = ISNULL(DistributorsSell, 0),
                       @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
                       @_MinSKURevenue = MinSKURevenue,
                       @_MinSKUVolume = MinSKUVolume
                FROM #tmpKPIList
                WHERE RowNumber = @_Min;

                IF @_TempFromDate IS NULL
                   OR @_TempToDate IS NULL
                   OR @_TempFromDate != @_FromDate
                   OR @_TempToDate != @_ToDate
                BEGIN
                    DELETE FROM #tmpSalesTerritory;
                    DELETE FROM #tmpRoute;
                    DELETE FROM #tmpSalesForce;

                    ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
                    INSERT INTO #tmpSalesTerritory
                    SELECT *
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
                                                                           st.SalesOrg_0_ValueID
                                                              ORDER BY st.EffectiveDate DESC
                                                             ),
                               st.CompanyID,
                               st.SalesOrg_0_ValueID,
                               st.SalesOrg_1_ValueID,
                               st.SalesOrg_2_ValueID,
                               st.SalesOrg_3_ValueID,
                               st.SalesOrg_4_ValueID,
                               st.SalesOrg_5_ValueID,
                               st.SalesOrg_6_ValueID,
                               st.SalesOrg_7_ValueID,
                               st.SalesOrg_8_ValueID,
                               st.SalesOrg_9_ValueID
                        FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
                        WHERE st.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
                                  OR st.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                    ) tmp
                    WHERE tmp.RowNumber = 1;

                    ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
                    INSERT INTO #tmpRoute
                    SELECT tmp.CompanyID,
                           tmp.SalespersonID,
                           tmp.ROUTE,
                           tmp.SalesForceID,
                           tmp.SalesAreaID,
                           tmp.EffectiveDate,
                           tmp.ExpirationDate,
                           st.SalesOrg_0_ValueID,
                           st.SalesOrg_1_ValueID,
                           st.SalesOrg_2_ValueID,
                           st.SalesOrg_3_ValueID,
                           st.SalesOrg_4_ValueID,
                           st.SalesOrg_5_ValueID,
                           st.SalesOrg_6_ValueID,
                           st.SalesOrg_7_ValueID,
                           st.SalesOrg_8_ValueID,
                           st.SalesOrg_9_ValueID
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
                                                                           rs.SalespersonID
                                                              ORDER BY rs.EffectiveDate DESC
                                                             ),
                               rs.CompanyID,
                               rs.SalespersonID,
                               ROUTE = rs.RouteCD,
                               rs.SalesForceID,
                               sph.SalesAreaID,
                               rs.EffectiveDate,
                               ExpirationDate = ISNULL(rs.EndDate, GETDATE())
                        FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
						JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
                        WHERE rs.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR rs.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                        UNION ALL
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
                                                                           rs.PreviousSalesMan
                                                              ORDER BY rs.EffectiveDate DESC
                                                             ),
                               rs.CompanyID,
                               SalespersonID = rs.PreviousSalesMan,
                               ROUTE = rs.RouteCD,
                               rs.SalesForceID,
                               sph.SalesAreaID,
                               rs.StartDatePre,
                               rs.EndDatePre
                        FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
						JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
                        WHERE rs.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR rs.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                              AND rs.PreviousSalesMan IS NOT NULL
                    ) tmp
                        JOIN #tmpSalesTerritory st
                            ON st.CompanyID = tmp.CompanyID
                               AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
                    WHERE tmp.RowNumber = 1;

                    ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
                    INSERT INTO #tmpSalesForce
                    SELECT tmp.CompanyID,
                           tmp.EmployeeID,
                           tmp.SFHierachyID,
                           tmp.TerritoryType,
                           tmp.SalesOrgValueID,
                           st.SalesOrg_0_ValueID,
                           st.SalesOrg_1_ValueID,
                           st.SalesOrg_2_ValueID,
                           st.SalesOrg_3_ValueID,
                           st.SalesOrg_4_ValueID,
                           st.SalesOrg_5_ValueID,
                           st.SalesOrg_6_ValueID,
                           st.SalesOrg_7_ValueID,
                           st.SalesOrg_8_ValueID,
                           st.SalesOrg_9_ValueID
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
                                                                           sf.EmployeeID
                                                              ORDER BY sf.EffectiveDate DESC
                                                             ),
                               sf.CompanyID,
                               sf.EmployeeID,
                               sf.SFHierachyID,
                               sf.TerritoryType,
                               sf.SalesOrgValueID
                        FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
                        WHERE sf.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
                                  OR sf.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                    ) tmp
                        LEFT JOIN #tmpSalesTerritory st
                            ON st.CompanyID = tmp.CompanyID
                               AND
                               (
                                   st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
                               )
                    WHERE tmp.RowNumber = 1;

                    SET @_TempFromDate = @_FromDate;
                    SET @_TempToDate = @_ToDate;
                END;

                ---- Tính KPI PC tạm để tính ra LPPC
                INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
                SELECT rs.CompanyID,
                       rs.SalespersonID,
                       r.SalesForceID,
                       r.SalesAreaID,
                       rs.ROUTE,
                       rs.OrderDate,
                       rs.SourceType,
                       rs.CustomerID,
                       rs.CustomerLocationID,
                       rs.IsValidDate,
                       rs.IsValidDistance,
                       Multiply = CASE
                                      WHEN rs.IsRevenueDeduction = 1 THEN
                                          -1
                                      ELSE
                                          1
                                  END,
                       PC = COUNT(DISTINCT rs.CustomerID),
                       SKU = COUNT(DISTINCT rs.InventoryID),
                       TranAmt = SUM(rs.TranAmt),
                       ShippedQty = SUM(rs.ShippedQty),
                       rs.SalesOrg_0_ValueID,
                       rs.SalesOrg_1_ValueID,
                       rs.SalesOrg_2_ValueID,
                       rs.SalesOrg_3_ValueID,
                       rs.SalesOrg_4_ValueID,
                       rs.SalesOrg_5_ValueID,
                       rs.SalesOrg_6_ValueID,
                       rs.SalesOrg_7_ValueID,
                       rs.SalesOrg_8_ValueID,
                       rs.SalesOrg_9_ValueID,
                       rs.SalesForce_0_ID,
                       rs.SalesForce_1_ID,
                       rs.SalesForce_2_ID,
                       rs.SalesForce_3_ID,
                       rs.SalesForce_4_ID,
                       rs.SalesForce_5_ID,
                       rs.SalesForce_6_ID,
                       rs.SalesForce_7_ID,
                       rs.SalesForce_8_ID,
                       rs.SalesForce_9_ID,
                       Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
                FROM #tmpSORawSales rs
                    LEFT JOIN MRCDMS..DMSViewInventoryItem inv
                        ON rs.CompanyID = inv.CompanyID
                           AND rs.InventoryID = inv.InventoryID
                    LEFT JOIN #tmpRoute r
                        ON r.CompanyID = rs.CompanyID
                           AND r.SalespersonID = rs.SalespersonID
                           AND r.ROUTE = rs.ROUTE
                           AND rs.OrderDate
                           BETWEEN r.EffectiveDate AND r.ExpirationDate
                WHERE rs.OrderDate
                      BETWEEN @_TempFromDate AND @_TempToDate
                      AND rs.OrderType IN (   (CASE
                                                   WHEN @_ExcludeReturnOrder = 0 THEN
                                                       'CM'
                                                   ELSE
                                                       ''
                                               END
                                              ), 'SO', 'IN'
                                          )
                      AND
                      (
                          ISNULL(@_GroupID, '') = ''
                          OR
                          (
                              EXISTS
                (
                    SELECT TOP 1
                        *
                    FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
                    WHERE igd.CompanyID = @_CompanyID
                          AND igd.CodegGroupProd = @_GroupID
                          AND igd.InventoryID = rs.InventoryID
                )
                              OR EXISTS
                (
                    SELECT *
                    FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
                    WHERE gr.CompanyID = @_CompanyID
                          AND gr.CodegGroupProd = @_GroupID
                          AND inv.[HierarchyID] = gr.[HierarchyID]
                          OR gr.Attribute0 = inv.Attribute0ID
                          OR gr.Attribute1 = inv.Attribute1ID
                          OR gr.Attribute2 = inv.Attribute2ID
                          OR gr.Attribute3 = inv.Attribute3ID
                          OR gr.Attribute4 = inv.Attribute4ID
                          OR gr.Attribute5 = inv.Attribute5ID
                          OR gr.Attribute6 = inv.Attribute6ID
                          OR gr.Attribute7 = inv.Attribute7ID
                          OR gr.Attribute8 = inv.Attribute8ID
                          OR gr.Attribute9 = inv.Attribute9ID
                )
                          )
                      )
                      AND rs.ShippedQty >= @_MinSKUVolume
                      AND ABS(rs.TranAmt) >= @_MinSKURevenue
                --AND rs.ReasonCode = ( CASE
                --                      WHEN @_IncludePromotionItem = 1
                --                      THEN 'ISSKM'
                --                      WHEN @_IncludeWarrantyItem = 1
                --                      THEN 'ISSBH'
                --                      ELSE 'ISS'
                --                      END )
                GROUP BY rs.CompanyID,
                         rs.SalespersonID,
                         r.SalesForceID,
                         r.SalesAreaID,
                         rs.ROUTE,
                         rs.OrderDate,
                         rs.SourceType,
                         rs.CustomerID,
                         rs.CustomerLocationID,
                         rs.IsValidDate,
                         rs.IsValidDistance,
                         rs.IsRevenueDeduction,
                         rs.SalesOrg_0_ValueID,
                         rs.SalesOrg_1_ValueID,
                         rs.SalesOrg_2_ValueID,
                         rs.SalesOrg_3_ValueID,
                         rs.SalesOrg_4_ValueID,
                         rs.SalesOrg_5_ValueID,
                         rs.SalesOrg_6_ValueID,
                         rs.SalesOrg_7_ValueID,
                         rs.SalesOrg_8_ValueID,
                         rs.SalesOrg_9_ValueID,
                         rs.SalesForce_0_ID,
                         rs.SalesForce_1_ID,
                         rs.SalesForce_2_ID,
                         rs.SalesForce_3_ID,
                         rs.SalesForce_4_ID,
                         rs.SalesForce_5_ID,
                         rs.SalesForce_6_ID,
                         rs.SalesForce_7_ID,
                         rs.SalesForce_8_ID,
                         rs.SalesForce_9_ID,
                         rs.OrderNbr;


                EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
                                                                         'PC',
                                                                         NULL,
                                                                         NULL,
                                                                         NULL,
                                                                         @_IsValiDateValidDistance,
                                                                         @_IsValidDateInvalidDistance,
                                                                         @_IsInvalidDate,
                                                                         @_IsDistributorSell;

                INSERT INTO #tmpTotalPC
                SELECT *
                FROM dbo.DMSBLObjectKPITemp;


                DELETE FROM dbo.DMSBLObjectKPITemp;

                EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
                                                                         'SKU',
                                                                         NULL,
                                                                         NULL,
                                                                         NULL,
                                                                         @_IsValiDateValidDistance,
                                                                         @_IsValidDateInvalidDistance,
                                                                         @_IsInvalidDate,
                                                                         @_IsDistributorSell;

                INSERT INTO #tmpTotalLine
                SELECT *
                FROM dbo.DMSBLObjectKPITemp;
                ---- Kết thúc tính KPI LPPC tạm

                IF @_step = 1
                BEGIN
                    INSERT INTO #tmpKPI
                    SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
                           CompanyID = tmp.CompanyID,
                           @_KPIID,
                           @_KPICD,
                           @_KPIDescr,
                           @_Source,
                           @_Type,
                           @_GroupBy,
                           @_GroupID,
                           @_Formula,
                           @_Template,
                           @_KPIPeriodNbr,
                           ISNULL(
                                     @_RefNbr,
                                     'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
                                     + CONVERT(VARCHAR(20), @_ToDate, 111)
                                 ),
                           @_Period,
                           @_FromDate,
                           @_ToDate,
                           ObjectID = tmp.EmployeeID,
                           TempObjectID = ISNULL(kpid.ObjectID, 0),
                           'S',
                           tmp.ObjectAssignment,
                           kpid.TargetsSuggest,  ---- temp
                           kpid.TargetsAssigned, ---- temp
                           kpid.TargetsSuggest,
                           kpid.TargetsAssigned,
                           kpia.Actual1,
                           kpia.Actual2,
                           kpia.Actual3,
                           kpia.Actual4,
                           kpia.Actual
                    FROM
                    (
                        SELECT sf.CompanyID,
                               sf.EmployeeID,
                               ObjectAssignment = CASE
                                                      WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
                                                          r.ROUTE
                                                      ELSE
                                                          CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                                  END
                        FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
                            JOIN #tmpSalesForce sf WITH (NOLOCK)
                                ON sf.CompanyID = objectPeriod.CompanyID
                                   AND sf.SFHierachyID = objectPeriod.KPIObjectID
                            LEFT JOIN #tmpRoute r
                                ON r.CompanyID = sf.CompanyID
                                   AND
                                   (
                                       r.SalespersonID = sf.EmployeeID
                                       OR r.SalesForceID = sf.EmployeeID
                                   )
                        WHERE objectPeriod.CompanyID = @_CompanyID
                              AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
                              AND
                              (
                                  @_SalesAreaID IS NULL
                                  OR @_Level = -1
                                  OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
                              )
                        GROUP BY sf.CompanyID,
                                 sf.EmployeeID,
                                 CASE
                                     WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
                                         r.ROUTE
                                     ELSE
                                         CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                 END
                    ) tmp
                        LEFT JOIN
                        (
                            SELECT kpid.CompanyID,
                                   kpid.ObjectID,
                                   kpid.KPIPeriodNbr,
                                   kpid.FromDate,
                                   kpid.ToDate,
                                   kpid.TargetsSuggest,
                                   kpid.TargetsAssigned
                            FROM #tmpKPIDetail kpid
                            WHERE kpid.CodeListSalesID = @_KPIID
                                  AND
                                  (
                                      @_RefNbr LIKE 'Temp-%'
                                      OR kpid.RefNbr = @_RefNbr
                                  )
                                  AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
                                  AND kpid.FromDate = @_FromDate
                                  AND kpid.ToDate = @_ToDate
                        ) kpid
                            ON kpid.CompanyID = tmp.CompanyID
                               AND kpid.ObjectID = tmp.EmployeeID
                        LEFT JOIN
                        (
                            SELECT kpipc.CompanyID,
                                   kpipc.ObjectID,
                                   kpipc.ObjectAssignment,
                                   Actual1 = CASE
                                                 WHEN @_IsValiDateValidDistance = 1
                                                      AND ISNULL(kpipc.Actual1, 0) > 0 THEN
                                                     kpisku.Actual1 / kpipc.Actual1
                                                 ELSE
                                                     0
                                             END,
                                   Actual2 = CASE
                                                 WHEN @_IsValidDateInvalidDistance = 1
                                                      AND ISNULL(kpipc.Actual2, 0) > 0 THEN
                                                     kpisku.Actual2 / kpipc.Actual2
                                                 ELSE
                                                     0
                                             END,
                                   Actual3 = CASE
                                                 WHEN @_IsInvalidDate = 1
                                                      AND ISNULL(kpipc.Actual3, 0) > 0 THEN
                                                     kpisku.Actual3 / kpipc.Actual3
                                                 ELSE
                                                     0
                                             END,
                                   Actual4 = CASE
                                                 WHEN @_IsDistributorSell = 1
                                                      AND ISNULL(kpipc.Actual4, 0) > 0 THEN
                                                     kpisku.Actual4 / kpipc.Actual4
                                                 ELSE
                                                     0
                                             END,
                                   Actual = CASE
                                                WHEN ISNULL(kpipc.Actual, 0) > 0 THEN
                                                    kpisku.Actual / kpipc.Actual
                                                ELSE
                                                    0
                                            END
                            FROM #tmpTotalPC kpipc
                                JOIN #tmpTotalLine kpisku
                                    ON kpisku.CompanyID = kpipc.CompanyID
                                       AND kpisku.ObjectID = kpipc.ObjectID
                                       AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
                                JOIN #tmpSalesForce sf
                                    ON sf.CompanyID = kpipc.CompanyID
                                       AND sf.EmployeeID = kpipc.ObjectID
                                       AND sf.TerritoryType = 'S'
                                JOIN #tmpRoute r
                                    ON r.CompanyID = sf.CompanyID
                                       AND r.SalespersonID = sf.EmployeeID
                        ) kpia
                            ON kpia.CompanyID = tmp.CompanyID
                               AND kpia.ObjectID = tmp.EmployeeID
                               AND kpia.ObjectAssignment = tmp.ObjectAssignment
                    WHERE kpid.ObjectID IS NOT NULL;
                END;
                ELSE IF @_step = 2
                BEGIN
                    INSERT INTO #tmpKPI
                    SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
                           tmp.CompanyID,
                           @_KPIID,
                           @_KPICD,
                           @_KPIDescr,
                           @_Source,
                           @_Type,
                           @_GroupBy,
                           @_GroupID,
                           @_Formula,
                           @_Template,
                           @_KPIPeriodNbr,
                           ISNULL(
                                     @_RefNbr,
                                     'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
                                     + CONVERT(VARCHAR(20), @_ToDate, 111)
                                 ),
                           @_Period,
                           @_FromDate,
                           @_ToDate,
                           ObjectID = tmp.EmployeeID,
                           TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
                           'S',
                           tmp.ObjectAssignment,
                           TargetsSuggest = MAX(kpid.TargetsSuggest),   ---- temp
                           TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
                           TargetsSuggest = MAX(kpid.TargetsSuggest),
                           TargetsAssigned = MAX(kpid.TargetsAssigned),
                           Actual1 = CASE
                                         WHEN @_IsValiDateValidDistance = 1
                                              AND SUM(kpi.PC1) > 0 THEN
                                             SUM(kpi.SKU1) / SUM(kpi.PC1)
                                         ELSE
                                             0
                                     END,
                           Actual2 = CASE
                                         WHEN @_IsValidDateInvalidDistance = 1
                                              AND SUM(kpi.PC2) > 0 THEN
                                             SUM(kpi.SKU2) / SUM(kpi.PC2)
                                         ELSE
                                             0
                                     END,
                           Actual3 = CASE
                                         WHEN @_IsInvalidDate = 1
                                              AND SUM(kpi.PC3) > 0 THEN
                                             SUM(kpi.SKU3) / SUM(kpi.PC3)
                                         ELSE
                                             0
                                     END,
                           Actual4 = CASE
                                         WHEN @_IsDistributorSell = 1
                                              AND SUM(kpi.PC4) > 0 THEN
                                             SUM(kpi.SKU4) / SUM(kpi.PC4)
                                         ELSE
                                             0
                                     END,
                           Actual = CASE
                                        WHEN SUM(kpi.PC) > 0 THEN
                                            SUM(kpi.SKU) / SUM(kpi.PC)
                                        ELSE
                                            0
                                    END
                    FROM
                    (
                        SELECT sf.CompanyID,
                               sf.EmployeeID,
                               ObjectAssignment = CASE
                                                      WHEN sf.TerritoryType = 'D' THEN
                                                          r.ROUTE
                                                      ELSE
                                                          CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                                  END,
                               sf.SalesOrg_0_ValueID
                        FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
                            JOIN #tmpSalesForce sf WITH (NOLOCK)
                                ON sf.CompanyID = objectPeriod.CompanyID
                                   AND sf.SFHierachyID = objectPeriod.KPIObjectID
                            LEFT JOIN #tmpRoute r
                                ON r.CompanyID = sf.CompanyID
                                   AND r.SalesForceID = sf.EmployeeID
                        WHERE objectPeriod.CompanyID = @_CompanyID
                              AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
                              AND sf.TerritoryType != 'S'
                              AND
                              (
                                  @_SalesAreaID IS NULL
                                  OR @_Level = -1
                                  OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
                              )
                        GROUP BY sf.CompanyID,
                                 sf.EmployeeID,
                                 CASE
                                     WHEN sf.TerritoryType = 'D' THEN
                                         r.ROUTE
                                     ELSE
                                         CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                 END,
                                 sf.SalesOrg_0_ValueID
                    ) tmp
                        LEFT JOIN
                        (
                            SELECT kpid.CompanyID,
                                   kpid.ObjectID,
                                   kpid.KPIPeriodNbr,
                                   kpid.FromDate,
                                   kpid.ToDate,
                                   kpid.TargetsSuggest,
                                   kpid.TargetsAssigned
                            FROM #tmpKPIDetail kpid
                            WHERE kpid.CodeListSalesID = @_KPIID
                                  AND
                                  (
                                      @_RefNbr LIKE 'Temp-%'
                                      OR kpid.RefNbr = @_RefNbr
                                  )
                                  AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
                                  AND kpid.FromDate = @_FromDate
                                  AND kpid.ToDate = @_ToDate
                        ) kpid
                            ON kpid.CompanyID = tmp.CompanyID
                               AND kpid.ObjectID = tmp.EmployeeID
                        LEFT JOIN
                        (
                            SELECT DISTINCT
                                kpipc.CompanyID,
                                kpipc.ObjectID,
                                kpipc.ObjectAssignment,
                                SKU1 = kpisku.Actual1,
                                SKU2 = kpisku.Actual2,
                                SKU3 = kpisku.Actual3,
                                SKU4 = kpisku.Actual4,
                                SKU = kpisku.Actual,
                                PC1 = kpipc.Actual1,
                                PC2 = kpipc.Actual2,
                                PC3 = kpipc.Actual3,
                                PC4 = kpipc.Actual4,
                                PC = kpipc.Actual,
                                r.ROUTE,
                                r.SalesAreaID
                            FROM #tmpTotalPC kpipc
                                JOIN #tmpTotalLine kpisku
                                    ON kpisku.CompanyID = kpipc.CompanyID
                                       AND kpisku.ObjectID = kpipc.ObjectID
                                       AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
                                JOIN #tmpSalesForce sf
                                    ON sf.CompanyID = kpipc.CompanyID
                                       AND sf.EmployeeID = kpipc.ObjectID
                                       AND sf.TerritoryType = 'S'
                                JOIN #tmpRoute r
                                    ON r.CompanyID = sf.CompanyID
                                       AND r.SalespersonID = sf.EmployeeID
                        ) kpi
                            ON kpi.CompanyID = tmp.CompanyID
                               AND
                               (
                                   kpi.ROUTE = tmp.ObjectAssignment
                                   OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
                               )
                    WHERE kpid.ObjectID IS NOT NULL
                    GROUP BY tmp.CompanyID,
                             tmp.EmployeeID,
                             tmp.ObjectAssignment;
                END;
                SET @_Min = @_Min + 1;
            END;

            SELECT @_step = @_step + 1;
        END;
    END; ---- Tính KPI LPPC

    BEGIN ---- KPI BPPC

        DELETE FROM #tmpKPIList;

        INSERT INTO #tmpKPIList
        SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
               *
        FROM
        (
            SELECT CompanyID,
                   CodeListSalesID,
                   CodeListSalesCD,
                   RefNbr,
                   KPIPeriodNbr,
                   Descr = MAX(Descr),
                   SOURCE = MAX(SOURCE),
                   TYPE = MAX(TYPE),
                   GroupBy = MAX(GroupBy),
                   GroupID = MAX(GroupID),
                   Formula = MAX(Formula),
                   PERIOD = MAX(PERIOD),
                   SalesOrgID = MAX(SalesOrgID),
                   SalesAreaID = MAX(SalesAreaID),
                   LEVEL = MAX(LEVEL),
                   Template = MAX(Template),
                   FromDate,
                   ToDate,
                   ObjectType = MAX(ObjectType),
                   CorrectRouteAndCorrectCoordinates,
                   CorrectRouteAndIncorrectCoordinates,
                   IncorrectRoute,
                   DistributorsSell,
                   MinSKUVolume,
                   MinSKURevenue,
                   ExcludeReturnOrder
            FROM #tmpKPIDetail
            WHERE Template = 'BPPC'
                  AND ObjectType = 'S'
            GROUP BY CompanyID,
                     CodeListSalesID,
                     CodeListSalesCD,
                     RefNbr,
                     KPIPeriodNbr,
                     FromDate,
                     ToDate,
                     CorrectRouteAndCorrectCoordinates,
                     CorrectRouteAndIncorrectCoordinates,
                     IncorrectRoute,
                     DistributorsSell,
                     MinSKUVolume,
                     MinSKURevenue,
                     ExcludeReturnOrder
        ) tmp;

        SELECT @_Max = MAX(RowNumber)
        FROM #tmpKPIList;

        SELECT @_step = 1;
        WHILE @_step <= 2
        BEGIN
            SELECT @_Min = 1;
            WHILE @_Min <= @_Max
            BEGIN
                ---- Xóa dữ liệu SM để tính lại đối với KPI mới
                DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
                DELETE FROM dbo.DMSBLObjectKPITemp;
                DELETE FROM #tmpTotalPC;
                DELETE FROM #tmpTotalLine;

                ---- Lấy thông tin công thức KPI
                SELECT @_KPIID = CodeListSalesID,
                       @_KPICD = CodeListSalesCD,
                       @_KPIDescr = Descr,
                       @_RefNbr = RefNbr,
                       @_KPIPeriodNbr = KPIPeriodNbr,
                       @_Source = SOURCE,
                       @_Type = TYPE,
                       @_GroupBy = ISNULL(GroupBy, 'A'),
                       @_GroupID = GroupID,
                       @_Formula = Formula,
                       @_Template = Template,
                       @_Period = PERIOD,
                       @_SalesAreaID = SalesAreaID,
                       @_Level = LEVEL,
                       @_FromDate = FromDate,
                       @_ToDate = ToDate,
                       @_ObjectType = ObjectType,
                       @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
                       @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
                       @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
                       @_IsDistributorSell = ISNULL(DistributorsSell, 0),
                       @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
                       @_MinSKURevenue = MinSKURevenue,
                       @_MinSKUVolume = MinSKUVolume
                FROM #tmpKPIList
                WHERE RowNumber = @_Min;

                IF @_TempFromDate IS NULL
                   OR @_TempToDate IS NULL
                   OR @_TempFromDate != @_FromDate
                   OR @_TempToDate != @_ToDate
                BEGIN
                    DELETE FROM #tmpSalesTerritory;
                    DELETE FROM #tmpRoute;
                    DELETE FROM #tmpSalesForce;

                    ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
                    INSERT INTO #tmpSalesTerritory
                    SELECT *
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
                                                                           st.SalesOrg_0_ValueID
                                                              ORDER BY st.EffectiveDate DESC
                                                             ),
                               st.CompanyID,
                               st.SalesOrg_0_ValueID,
                               st.SalesOrg_1_ValueID,
                               st.SalesOrg_2_ValueID,
                               st.SalesOrg_3_ValueID,
                               st.SalesOrg_4_ValueID,
                               st.SalesOrg_5_ValueID,
                               st.SalesOrg_6_ValueID,
                               st.SalesOrg_7_ValueID,
                               st.SalesOrg_8_ValueID,
                               st.SalesOrg_9_ValueID
                        FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
                        WHERE st.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
                                  OR st.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                    ) tmp
                    WHERE tmp.RowNumber = 1;

                    ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
                    INSERT INTO #tmpRoute
                    SELECT tmp.CompanyID,
                           tmp.SalespersonID,
                           tmp.ROUTE,
                           tmp.SalesForceID,
                           tmp.SalesAreaID,
                           tmp.EffectiveDate,
                           tmp.ExpirationDate,
                           st.SalesOrg_0_ValueID,
                           st.SalesOrg_1_ValueID,
                           st.SalesOrg_2_ValueID,
                           st.SalesOrg_3_ValueID,
                           st.SalesOrg_4_ValueID,
                           st.SalesOrg_5_ValueID,
                           st.SalesOrg_6_ValueID,
                           st.SalesOrg_7_ValueID,
                           st.SalesOrg_8_ValueID,
                           st.SalesOrg_9_ValueID
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
                                                                           rs.SalespersonID
                                                              ORDER BY rs.EffectiveDate DESC
                                                             ),
                               rs.CompanyID,
                               rs.SalespersonID,
                               ROUTE = rs.RouteCD,
                               rs.SalesForceID,
                               sph.SalesAreaID,
                               rs.EffectiveDate,
                               ExpirationDate = ISNULL(rs.EndDate, GETDATE())
                        FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
						JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
                        WHERE rs.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR rs.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                        UNION ALL
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
                                                                           rs.PreviousSalesMan
                                                              ORDER BY rs.EffectiveDate DESC
                                                             ),
                               rs.CompanyID,
                               SalespersonID = rs.PreviousSalesMan,
                               ROUTE = rs.RouteCD,
                               rs.SalesForceID,
                               sph.SalesAreaID,
                               rs.StartDatePre,
                               rs.EndDatePre
                        FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
						JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
                        WHERE rs.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR rs.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                              AND rs.PreviousSalesMan IS NOT NULL

						--Hieu.Bui : Them vao de xu ly case chuyen nhieu SM 1 route trong 1 thang cua SM 1218 - RedmineID : #62296
						 UNION
                           SELECT 1 AS RowNumber,
                                  @_CompanyID AS CompanyID,
                                  1231 AS SalespersonID,
                                  'R000918' AS Route,
                                  1194 AS SalesForceID,
                                  535 AS SalesAreaID,
                                  '2019-06-01 00:00:00.000' AS StartDatePre,
                                  '2019-06-14 00:00:00.000' AS EndDatePre
                            WHERE @_Year      = '2019'
                              AND @_Month     = '06'
                              AND @_CompanyID = 3
                           UNION
                           SELECT 1 AS RowNumber,
                                  @_CompanyID AS CompanyID,
                                  2766 AS SalespersonID,
                                  'R000918' AS Route,
                                  1194 AS SalesForceID,
                                  535 AS SalesAreaID,
                                  '2019-06-15 00:00:00.000' AS StartDatePre,
                                  '2019-06-30 00:00:00.000' AS EndDatePre
                            WHERE @_Year      = '2019'
                              AND @_Month     = '06'
                              AND @_CompanyID = 3
                    ) tmp
                        JOIN #tmpSalesTerritory st
                            ON st.CompanyID = tmp.CompanyID
                               AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
                    WHERE tmp.RowNumber = 1;

                    ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
                    INSERT INTO #tmpSalesForce
                    SELECT tmp.CompanyID,
                           tmp.EmployeeID,
                           tmp.SFHierachyID,
                           tmp.TerritoryType,
                           tmp.SalesOrgValueID,
                           st.SalesOrg_0_ValueID,
                           st.SalesOrg_1_ValueID,
                           st.SalesOrg_2_ValueID,
                           st.SalesOrg_3_ValueID,
                           st.SalesOrg_4_ValueID,
                           st.SalesOrg_5_ValueID,
                           st.SalesOrg_6_ValueID,
                           st.SalesOrg_7_ValueID,
                           st.SalesOrg_8_ValueID,
                           st.SalesOrg_9_ValueID
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
                                                                           sf.EmployeeID
                                                              ORDER BY sf.EffectiveDate DESC
                                                             ),
                               sf.CompanyID,
                               sf.EmployeeID,
                               sf.SFHierachyID,
                               sf.TerritoryType,
                               sf.SalesOrgValueID
                        FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
                        WHERE sf.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
                                  OR sf.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                    ) tmp
                        LEFT JOIN #tmpSalesTerritory st
                            ON st.CompanyID = tmp.CompanyID
                               AND
                               (
                                   st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
                               )
                    WHERE tmp.RowNumber = 1;

                    SET @_TempFromDate = @_FromDate;
                    SET @_TempToDate = @_ToDate;
                END;

                DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
                ---- Tính KPI PC tạm
                INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
                SELECT rs.CompanyID,
                       rs.SalespersonID,
                       r.SalesForceID,
                       r.SalesAreaID,
                       rs.ROUTE,
                       rs.OrderDate,
                       rs.SourceType,
                       rs.CustomerID,
                       rs.CustomerLocationID,
                       rs.IsValidDate,
                       rs.IsValidDistance,
                       Multiply = CASE
                                      WHEN rs.IsRevenueDeduction = 1 THEN
                                          -1
                                      ELSE
                                          1
                                  END,
                       PC = COUNT(DISTINCT rs.CustomerID),
                       SKU = COUNT(DISTINCT rs.InventoryID),
                       TranAmt = SUM(rs.TranAmt),
                       ShippedQty = SUM(rs.ShippedQty),
                       rs.SalesOrg_0_ValueID,
                       rs.SalesOrg_1_ValueID,
                       rs.SalesOrg_2_ValueID,
                       rs.SalesOrg_3_ValueID,
                       rs.SalesOrg_4_ValueID,
                       rs.SalesOrg_5_ValueID,
                       rs.SalesOrg_6_ValueID,
                       rs.SalesOrg_7_ValueID,
                       rs.SalesOrg_8_ValueID,
                       rs.SalesOrg_9_ValueID,
                       rs.SalesForce_0_ID,
                       rs.SalesForce_1_ID,
                       rs.SalesForce_2_ID,
                       rs.SalesForce_3_ID,
                       rs.SalesForce_4_ID,
                       rs.SalesForce_5_ID,
                       rs.SalesForce_6_ID,
                       rs.SalesForce_7_ID,
                       rs.SalesForce_8_ID,
                       rs.SalesForce_9_ID,
                       Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
                FROM #tmpSORawSales rs
                    LEFT JOIN MRCDMS..DMSViewInventoryItem inv
                        ON rs.CompanyID = inv.CompanyID
                           AND rs.InventoryID = inv.InventoryID
                    LEFT JOIN #tmpRoute r
                        ON r.CompanyID = rs.CompanyID
                           AND r.SalespersonID = rs.SalespersonID
                           AND r.ROUTE = rs.ROUTE
                           AND rs.OrderDate
                           BETWEEN r.EffectiveDate AND r.ExpirationDate
                WHERE rs.OrderDate
                      BETWEEN @_TempFromDate AND @_TempToDate
                      AND rs.OrderType IN (   (CASE
                                                   WHEN @_ExcludeReturnOrder = 0 THEN
                                                       'CM'
                                                   ELSE
                                                       ''
                                               END
                                              ), 'SO', 'IN'
                                          )
                      AND
                      (
                          ISNULL(@_GroupID, '') = ''
                          OR
                          (
                              EXISTS
                (
                    SELECT TOP 1
                        *
                    FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
                    WHERE igd.CompanyID = @_CompanyID
                          AND igd.CodegGroupProd = @_GroupID
                          AND igd.InventoryID = rs.InventoryID
                )
                              OR EXISTS
                (
                    SELECT *
                    FROM MRCDMS..DMSKPIGroupProductSalesDetailGroup gr
                    WHERE gr.CompanyID = @_CompanyID
                          AND gr.CodegGroupProd = @_GroupID
                          AND inv.[HierarchyID] = gr.[HierarchyID]
                          OR gr.Attribute0 = inv.Attribute0ID
                          OR gr.Attribute1 = inv.Attribute1ID
                          OR gr.Attribute2 = inv.Attribute2ID
                          OR gr.Attribute3 = inv.Attribute3ID
                          OR gr.Attribute4 = inv.Attribute4ID
                          OR gr.Attribute5 = inv.Attribute5ID
                          OR gr.Attribute6 = inv.Attribute6ID
                          OR gr.Attribute7 = inv.Attribute7ID
                          OR gr.Attribute8 = inv.Attribute8ID
                          OR gr.Attribute9 = inv.Attribute9ID
                )
                          )
                      )
                      AND rs.ShippedQty >= @_MinSKUVolume
                      AND ABS(rs.TranAmt) >= @_MinSKURevenue
                --AND rs.ReasonCode = ( CASE
                --                      WHEN @_IncludePromotionItem = 1
                --                      THEN 'ISSKM'
                --                      WHEN @_IncludeWarrantyItem = 1
                --                      THEN 'ISSBH'
                --                      ELSE 'ISS'
                --                      END )
                GROUP BY rs.CompanyID,
                         rs.SalespersonID,
                         r.SalesForceID,
                         r.SalesAreaID,
                         rs.ROUTE,
                         rs.OrderDate,
                         rs.SourceType,
                         rs.CustomerID,
                         rs.CustomerLocationID,
                         rs.IsValidDate,
                         rs.IsValidDistance,
                         rs.IsRevenueDeduction,
                         rs.SalesOrg_0_ValueID,
                         rs.SalesOrg_1_ValueID,
                         rs.SalesOrg_2_ValueID,
                         rs.SalesOrg_3_ValueID,
                         rs.SalesOrg_4_ValueID,
                         rs.SalesOrg_5_ValueID,
                         rs.SalesOrg_6_ValueID,
                         rs.SalesOrg_7_ValueID,
                         rs.SalesOrg_8_ValueID,
                         rs.SalesOrg_9_ValueID,
                         rs.SalesForce_0_ID,
                         rs.SalesForce_1_ID,
                         rs.SalesForce_2_ID,
                         rs.SalesForce_3_ID,
                         rs.SalesForce_4_ID,
                         rs.SalesForce_5_ID,
                         rs.SalesForce_6_ID,
                         rs.SalesForce_7_ID,
                         rs.SalesForce_8_ID,
                         rs.SalesForce_9_ID;

                EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
                                                                         'PC',
                                                                         NULL,
                                                                         NULL,
                                                                         NULL,
                                                                         @_IsValiDateValidDistance,
                                                                         @_IsValidDateInvalidDistance,
                                                                         @_IsInvalidDate,
                                                                         @_IsDistributorSell;

                SELECT rs.CompanyID,
                       rs.SalespersonID,
                       r.SalesForceID,
                       r.SalesAreaID,
                       rs.ROUTE,
                       rs.OrderDate,
                       --rs.CustomerID,
                       --rs.CustomerLocationID,
                       Multiply = CASE
                                      WHEN rs.IsRevenueDeduction = 1 THEN
                                          -1
                                      ELSE
                                          1
                                  END,
                       PC = COUNT(DISTINCT rs.CustomerID),
                       rs.SalesOrg_0_ValueID,
                       rs.SalesOrg_1_ValueID,
                       rs.SalesOrg_2_ValueID,
                       rs.SalesOrg_3_ValueID,
                       rs.SalesOrg_4_ValueID,
                       rs.SalesOrg_5_ValueID,
                       rs.SalesOrg_6_ValueID,
                       rs.SalesOrg_7_ValueID,
                       rs.SalesOrg_8_ValueID,
                       rs.SalesOrg_9_ValueID,
                       rs.SalesForce_0_ID,
                       rs.SalesForce_1_ID,
                       rs.SalesForce_2_ID,
                       rs.SalesForce_3_ID,
                       rs.SalesForce_4_ID,
                       rs.SalesForce_5_ID,
                       rs.SalesForce_6_ID,
                       rs.SalesForce_7_ID,
                       rs.SalesForce_8_ID,
                       rs.SalesForce_9_ID
                INTO #tmpActual
                FROM #tmpSORawSales rs
                    LEFT JOIN MRCDMS..DMSViewInventoryItem inv
                        ON rs.CompanyID = inv.CompanyID
                           AND rs.InventoryID = inv.InventoryID
                    LEFT JOIN #tmpRoute r
                        ON r.CompanyID = rs.CompanyID
                           AND r.SalespersonID = rs.SalespersonID
                           AND r.ROUTE = rs.ROUTE
                           AND rs.OrderDate
                           BETWEEN r.EffectiveDate AND r.ExpirationDate
                WHERE rs.OrderDate
                      BETWEEN @_TempFromDate AND @_TempToDate
                      AND rs.OrderType IN (   (CASE
                                                   WHEN @_ExcludeReturnOrder = 0 THEN
                                                       'CM'
                                                   ELSE
                                                       ''
                                               END
                                              ), 'SO', 'IN'
                                          )
                      AND
                      (
                          ISNULL(@_GroupID, '') = ''
                          OR
                          (
                              EXISTS
                (
                    SELECT TOP 1
                        *
                    FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
                    WHERE igd.CompanyID = @_CompanyID
                          AND igd.CodegGroupProd = @_GroupID
                          AND igd.InventoryID = rs.InventoryID
                )
                              OR EXISTS
                (
                    SELECT *
                    FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
                    WHERE gr.CompanyID = @_CompanyID
                          AND gr.CodegGroupProd = @_GroupID
                          AND inv.[HierarchyID] = gr.[HierarchyID]
                          OR gr.Attribute0 = inv.Attribute0ID
                          OR gr.Attribute1 = inv.Attribute1ID
                          OR gr.Attribute2 = inv.Attribute2ID
                          OR gr.Attribute3 = inv.Attribute3ID
                          OR gr.Attribute4 = inv.Attribute4ID
                          OR gr.Attribute5 = inv.Attribute5ID
                          OR gr.Attribute6 = inv.Attribute6ID
                          OR gr.Attribute7 = inv.Attribute7ID
                          OR gr.Attribute8 = inv.Attribute8ID
                          OR gr.Attribute9 = inv.Attribute9ID
                )
                          )
                      )
                GROUP BY rs.CompanyID,
                         rs.SalespersonID,
                         r.SalesForceID,
                         r.SalesAreaID,
                         rs.ROUTE,
                         rs.OrderDate,
                         --rs.CustomerID,
                         --rs.CustomerLocationID,
                         rs.IsRevenueDeduction,
                         rs.SalesOrg_0_ValueID,
                         rs.SalesOrg_1_ValueID,
                         rs.SalesOrg_2_ValueID,
                         rs.SalesOrg_3_ValueID,
                         rs.SalesOrg_4_ValueID,
                         rs.SalesOrg_5_ValueID,
                         rs.SalesOrg_6_ValueID,
                         rs.SalesOrg_7_ValueID,
                         rs.SalesOrg_8_ValueID,
                         rs.SalesOrg_9_ValueID,
                         rs.SalesForce_0_ID,
                         rs.SalesForce_1_ID,
                         rs.SalesForce_2_ID,
                         rs.SalesForce_3_ID,
                         rs.SalesForce_4_ID,
                         rs.SalesForce_5_ID,
                         rs.SalesForce_6_ID,
                         rs.SalesForce_7_ID,
                         rs.SalesForce_8_ID,
                         rs.SalesForce_9_ID
                HAVING SUM(rs.ShippedQty) >= @_MinSKUVolume
                       AND SUM(ABS(rs.TranAmt)) >= @_MinSKURevenue;

                SELECT rs.CompanyID,
                       rs.SalespersonID,
                       rs.ROUTE,
                       Actual = SUM(PC * Multiply)
                INTO #tmpTotalActual
                FROM #tmpActual rs
                GROUP BY rs.CompanyID,
                         rs.SalespersonID,
                         rs.ROUTE;

                DELETE FROM #tmpTotalPC;
                INSERT INTO #tmpTotalPC
                SELECT *
                FROM dbo.DMSBLObjectKPITemp;


                UPDATE pc
                SET pc.Actual = ac.Actual
                FROM #tmpTotalPC pc
                    INNER JOIN #tmpTotalActual ac
                        ON pc.CompanyID = ac.CompanyID
                           AND pc.ObjectID = ac.SalespersonID
                           AND pc.ObjectAssignment = ac.ROUTE;
                DROP TABLE #tmpTotalActual;
                DROP TABLE #tmpActual;

                DELETE FROM dbo.DMSBLObjectKPITemp;
                DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;

                -- Tính BPPC
                INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
                SELECT rs.CompanyID,
                       rs.SalespersonID,
                       r.SalesForceID,
                       r.SalesAreaID,
                       rs.ROUTE,
                       rs.OrderDate,
                       rs.SourceType,
                       rs.CustomerID,
                       rs.CustomerLocationID,
                       rs.IsValidDate,
                       rs.IsValidDistance,
                       Multiply = CASE
                                      WHEN rs.IsRevenueDeduction = 1 THEN
                                          -1
                                      ELSE
                                          1
                                  END,
                       PC = COUNT(DISTINCT rs.CustomerID),
                       SKU = COUNT(DISTINCT rs.InventoryID),
                       TranAmt = SUM(rs.TranAmt),
                       ShippedQty = SUM(rs.ShippedQty),
                       rs.SalesOrg_0_ValueID,
                       rs.SalesOrg_1_ValueID,
                       rs.SalesOrg_2_ValueID,
                       rs.SalesOrg_3_ValueID,
                       rs.SalesOrg_4_ValueID,
                       rs.SalesOrg_5_ValueID,
                       rs.SalesOrg_6_ValueID,
                       rs.SalesOrg_7_ValueID,
                       rs.SalesOrg_8_ValueID,
                       rs.SalesOrg_9_ValueID,
                       rs.SalesForce_0_ID,
                       rs.SalesForce_1_ID,
                       rs.SalesForce_2_ID,
                       rs.SalesForce_3_ID,
                       rs.SalesForce_4_ID,
                       rs.SalesForce_5_ID,
                       rs.SalesForce_6_ID,
                       rs.SalesForce_7_ID,
                       rs.SalesForce_8_ID,
                       rs.SalesForce_9_ID,
                       Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
                FROM #tmpSORawSales rs
                    LEFT JOIN MRCDMS..DMSViewInventoryItem inv
                        ON rs.CompanyID = inv.CompanyID
                           AND rs.InventoryID = inv.InventoryID
                    LEFT JOIN #tmpRoute r
                        ON r.CompanyID = rs.CompanyID
                           AND r.SalespersonID = rs.SalespersonID
                           AND r.ROUTE = rs.ROUTE
                           AND rs.OrderDate
                           BETWEEN r.EffectiveDate AND r.ExpirationDate
                WHERE rs.OrderDate
                      BETWEEN @_TempFromDate AND @_TempToDate
                      AND rs.OrderType IN (   (CASE
                                                   WHEN @_ExcludeReturnOrder = 0 THEN
                                                       'CM'
                                                   ELSE
                                                       ''
                                               END
                                              ), 'SO', 'IN'
                                          )
                      AND
                      (
                          ISNULL(@_GroupID, '') = ''
                          OR
                          (
                              EXISTS
                (
                    SELECT TOP 1
                        *
                    FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
                    WHERE igd.CompanyID = @_CompanyID
                          AND igd.CodegGroupProd = @_GroupID
                          AND igd.InventoryID = rs.InventoryID
                )
                              OR EXISTS
                (
                    SELECT *
                    FROM MRCDMS..DMSKPIGroupProductSalesDetailGroup gr
                    WHERE gr.CompanyID = @_CompanyID
                          AND gr.CodegGroupProd = @_GroupID
                          AND inv.[HierarchyID] = gr.[HierarchyID]
                          OR gr.Attribute0 = inv.Attribute0ID
                          OR gr.Attribute1 = inv.Attribute1ID
                          OR gr.Attribute2 = inv.Attribute2ID
                          OR gr.Attribute3 = inv.Attribute3ID
                          OR gr.Attribute4 = inv.Attribute4ID
                          OR gr.Attribute5 = inv.Attribute5ID
                          OR gr.Attribute6 = inv.Attribute6ID
                          OR gr.Attribute7 = inv.Attribute7ID
                          OR gr.Attribute8 = inv.Attribute8ID
                          OR gr.Attribute9 = inv.Attribute9ID
                )
                          )
                      )
                      AND rs.ShippedQty >= @_MinSKUVolume
                      AND ABS(rs.TranAmt) >= @_MinSKURevenue
                --AND rs.ReasonCode = ( CASE
                --                      WHEN @_IncludePromotionItem = 1
                --                      THEN 'ISSKM'
                --                      WHEN @_IncludeWarrantyItem = 1
                --                      THEN 'ISSBH'
                --                      ELSE 'ISS'
                --                      END )
                GROUP BY rs.CompanyID,
                         rs.SalespersonID,
                         r.SalesForceID,
                         r.SalesAreaID,
                         rs.ROUTE,
                         rs.OrderDate,
                         rs.SourceType,
                         rs.CustomerID,
                         rs.CustomerLocationID,
                         rs.IsValidDate,
                         rs.IsValidDistance,
                         rs.IsRevenueDeduction,
                         rs.SalesOrg_0_ValueID,
                         rs.SalesOrg_1_ValueID,
                         rs.SalesOrg_2_ValueID,
                         rs.SalesOrg_3_ValueID,
                         rs.SalesOrg_4_ValueID,
                         rs.SalesOrg_5_ValueID,
                         rs.SalesOrg_6_ValueID,
                         rs.SalesOrg_7_ValueID,
                         rs.SalesOrg_8_ValueID,
                         rs.SalesOrg_9_ValueID,
                         rs.SalesForce_0_ID,
                         rs.SalesForce_1_ID,
                         rs.SalesForce_2_ID,
                         rs.SalesForce_3_ID,
                         rs.SalesForce_4_ID,
                         rs.SalesForce_5_ID,
                         rs.SalesForce_6_ID,
                         rs.SalesForce_7_ID,
                         rs.SalesForce_8_ID,
                         rs.SalesForce_9_ID;

                EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
                                                                         'Hie3',
                                                                         NULL,
                                                                         NULL,
                                                                         NULL,
                                                                         @_IsValiDateValidDistance,
                                                                         @_IsValidDateInvalidDistance,
                                                                         @_IsInvalidDate,
                                                                         @_IsDistributorSell;

                INSERT INTO #tmpTotalLine
                SELECT *
                FROM dbo.DMSBLObjectKPITemp;

                --Step 1 for Sales Rep, step 2 for sales Sup
                IF @_step = 1
                BEGIN
                    INSERT INTO #tmpKPI
                    SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
                           CompanyID = tmp.CompanyID,
                           @_KPIID,
                           @_KPICD,
                           @_KPIDescr,
                           @_Source,
                           @_Type,
                           @_GroupBy,
                           @_GroupID,
                           @_Formula,
                           @_Template,
                           @_KPIPeriodNbr,
                           ISNULL(
                                     @_RefNbr,
                                     'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
                                     + CONVERT(VARCHAR(20), @_ToDate, 111)
                                 ),
                           @_Period,
                           @_FromDate,
                           @_ToDate,
                           ObjectID = tmp.EmployeeID,
                           TempObjectID = ISNULL(kpid.ObjectID, 0),
                           'S',
                           tmp.ObjectAssignment,
                           kpid.TargetsSuggest,  ---- temp
                           kpid.TargetsAssigned, ---- temp
                           kpid.TargetsSuggest,
                           kpid.TargetsAssigned,
                           kpia.Actual1,
                           kpia.Actual2,
                           kpia.Actual3,
                           kpia.Actual4,
                           kpia.Actual
                    FROM
                    (
                        SELECT sf.CompanyID,
                               sf.EmployeeID,
                               ObjectAssignment = CASE
                                                      WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
                                                          r.ROUTE
                                                      ELSE
                                                          CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                                  END
                        FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
                            JOIN #tmpSalesForce sf WITH (NOLOCK)
                                ON sf.CompanyID = objectPeriod.CompanyID
                                   AND sf.SFHierachyID = objectPeriod.KPIObjectID
                            LEFT JOIN #tmpRoute r
                                ON r.CompanyID = sf.CompanyID
                                   AND
                                   (
                                       r.SalespersonID = sf.EmployeeID
                                       OR r.SalesForceID = sf.EmployeeID
                                   )
                        WHERE objectPeriod.CompanyID = @_CompanyID
                              AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
                              AND
                              (
                                  @_SalesAreaID IS NULL
                                  OR @_Level = -1
                                  OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
                              )
                        GROUP BY sf.CompanyID,
                                 sf.EmployeeID,
                                 CASE
                                     WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
                                         r.ROUTE
                                     ELSE
                                         CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                 END
                    ) tmp
                        LEFT JOIN
                        (
                            SELECT kpid.CompanyID,
                                   kpid.ObjectID,
                                   kpid.KPIPeriodNbr,
                                   kpid.FromDate,
                                   kpid.ToDate,
                                   kpid.TargetsSuggest,
                                   kpid.TargetsAssigned
                            FROM #tmpKPIDetail kpid
                            WHERE kpid.CodeListSalesID = @_KPIID
                                  AND
                                  (
                                      @_RefNbr LIKE 'Temp-%'
                                      OR kpid.RefNbr = @_RefNbr
                                  )
                                  AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
                                  AND kpid.FromDate = @_FromDate
                                  AND kpid.ToDate = @_ToDate
                        ) kpid
                            ON kpid.CompanyID = tmp.CompanyID
                               AND kpid.ObjectID = tmp.EmployeeID
                        LEFT JOIN
                        (
                            SELECT kpipc.CompanyID,
                                   kpipc.ObjectID,
                                   kpipc.ObjectAssignment,
                                   Actual1 = CASE
                                                 WHEN @_IsValiDateValidDistance = 1
                                                      AND ISNULL(kpipc.Actual1, 0) > 0 THEN
                                                     kpisku.Actual1 / kpipc.Actual1
                                                 ELSE
                                                     0
                                             END,
                                   Actual2 = CASE
                                                 WHEN @_IsValidDateInvalidDistance = 1
                                                      AND ISNULL(kpipc.Actual2, 0) > 0 THEN
                                                     kpisku.Actual2 / kpipc.Actual2
                                                 ELSE
                                                     0
                                             END,
                                   Actual3 = CASE
                                                 WHEN @_IsInvalidDate = 1
                                                      AND ISNULL(kpipc.Actual3, 0) > 0 THEN
                                                     kpisku.Actual3 / kpipc.Actual3
                                                 ELSE
                                                     0
                                             END,
                                   Actual4 = CASE
                                                 WHEN @_IsDistributorSell = 1
                                                      AND ISNULL(kpipc.Actual4, 0) > 0 THEN
                                                     kpisku.Actual4 / kpipc.Actual4
                                                 ELSE
                                                     0
                                             END,
                                   Actual = CASE
                                                WHEN ISNULL(kpipc.Actual, 0) > 0 THEN
                                                    kpisku.Actual / kpipc.Actual
                                                ELSE
                                                    0
                                            END
                            FROM #tmpTotalPC kpipc
                                JOIN #tmpTotalLine kpisku
                                    ON kpisku.CompanyID = kpipc.CompanyID
                                       AND kpisku.ObjectID = kpipc.ObjectID
                                       AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
                                JOIN #tmpSalesForce sf
                                    ON sf.CompanyID = kpipc.CompanyID
                                       AND sf.EmployeeID = kpipc.ObjectID
                                       AND sf.TerritoryType = 'S'
                        ) kpia
                            ON kpia.CompanyID = tmp.CompanyID
                               AND kpia.ObjectID = tmp.EmployeeID
                               AND kpia.ObjectAssignment = tmp.ObjectAssignment
                    WHERE kpid.ObjectID IS NOT NULL;
                END;
                ELSE IF @_step = 2
                BEGIN
                    INSERT INTO #tmpKPI
                    SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
                           tmp.CompanyID,
                           @_KPIID,
                           @_KPICD,
                           @_KPIDescr,
                           @_Source,
                           @_Type,
                           @_GroupBy,
                           @_GroupID,
                           @_Formula,
                           @_Template,
                           @_KPIPeriodNbr,
                           ISNULL(
                                     @_RefNbr,
                                     'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
                                     + CONVERT(VARCHAR(20), @_ToDate, 111)
                                 ),
                           @_Period,
                           @_FromDate,
                           @_ToDate,
                           ObjectID = tmp.EmployeeID,
                           TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
                           'S',
                           tmp.ObjectAssignment,
                           TargetsSuggest = MAX(kpid.TargetsSuggest),   ---- temp
                           TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
                           TargetsSuggest = MAX(kpid.TargetsSuggest),
                           TargetsAssigned = MAX(kpid.TargetsAssigned),
                           Actual1 = CASE
                                         WHEN @_IsValiDateValidDistance = 1
                                              AND SUM(kpi.PC1) > 0 THEN
                                             SUM(kpi.SKU1) / SUM(kpi.PC1)
                                         ELSE
                                             0
                                     END,
                           Actual2 = CASE
                                         WHEN @_IsValidDateInvalidDistance = 1
                                              AND SUM(kpi.PC2) > 0 THEN
                                             SUM(kpi.SKU2) / SUM(kpi.PC2)
                                         ELSE
                                             0
                                     END,
                           Actual3 = CASE
                                         WHEN @_IsInvalidDate = 1
                                              AND SUM(kpi.PC3) > 0 THEN
                                             SUM(kpi.SKU3) / SUM(kpi.PC3)
                                         ELSE
                                             0
                                     END,
                           Actual4 = CASE
                                         WHEN @_IsDistributorSell = 1
                                              AND SUM(kpi.PC4) > 0 THEN
                                             SUM(kpi.SKU4) / SUM(kpi.PC4)
                                         ELSE
                                             0
                                     END,
                           Actual = CASE
                                        WHEN SUM(kpi.PC) > 0 THEN
                                            SUM(kpi.SKU) / SUM(kpi.PC)
                                        ELSE
                                            0
                                    END
                    FROM
                    (
                        SELECT sf.CompanyID,
                               sf.EmployeeID,
                               ObjectAssignment = CASE
                                                      WHEN sf.TerritoryType = 'D' THEN
                                                          r.ROUTE
                                                      ELSE
                                                          CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                                  END,
                               sf.SalesOrg_0_ValueID
                        FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
                            JOIN #tmpSalesForce sf WITH (NOLOCK)
                                ON sf.CompanyID = objectPeriod.CompanyID
                                   AND sf.SFHierachyID = objectPeriod.KPIObjectID
                            LEFT JOIN #tmpRoute r
                                ON r.CompanyID = sf.CompanyID
                                   AND r.SalesForceID = sf.EmployeeID
                        WHERE objectPeriod.CompanyID = @_CompanyID
                              AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
                              AND sf.TerritoryType != 'S'
                              AND
                              (
                                  @_SalesAreaID IS NULL
                                  OR @_Level = -1
                                  OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
                              )
                        GROUP BY sf.CompanyID,
                                 sf.EmployeeID,
                                 CASE
                                     WHEN sf.TerritoryType = 'D' THEN
                                         r.ROUTE
                                     ELSE
                                         CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                 END,
                                 sf.SalesOrg_0_ValueID
                    ) tmp
                        LEFT JOIN
                        (
                            SELECT kpid.CompanyID,
                                   kpid.ObjectID,
                                   kpid.KPIPeriodNbr,
                                   kpid.FromDate,
                                   kpid.ToDate,
                                   kpid.TargetsSuggest,
                                   kpid.TargetsAssigned
                            FROM #tmpKPIDetail kpid
                            WHERE kpid.CodeListSalesID = @_KPIID
                                  AND
                                  (
                                      @_RefNbr LIKE 'Temp-%'
                                      OR kpid.RefNbr = @_RefNbr
                                  )
                                  AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
                                  AND kpid.FromDate = @_FromDate
                                  AND kpid.ToDate = @_ToDate
                        ) kpid
                            ON kpid.CompanyID = tmp.CompanyID
                               AND kpid.ObjectID = tmp.EmployeeID
                        LEFT JOIN
                        (
                            SELECT DISTINCT
                                kpipc.CompanyID,
                                kpipc.ObjectID,
                                kpipc.ObjectAssignment,
                                SKU1 = kpisku.Actual1,
                                SKU2 = kpisku.Actual2,
                                SKU3 = kpisku.Actual3,
                                SKU4 = kpisku.Actual4,
                                SKU = kpisku.Actual,
                                PC1 = kpipc.Actual1,
                                PC2 = kpipc.Actual2,
                                PC3 = kpipc.Actual3,
                                PC4 = kpipc.Actual4,
                                PC = kpipc.Actual,
                                r.ROUTE,
                                r.SalesAreaID
                            FROM #tmpTotalPC kpipc
                                JOIN #tmpTotalLine kpisku
                                    ON kpisku.CompanyID = kpipc.CompanyID
                                       AND kpisku.ObjectID = kpipc.ObjectID
                                       AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
                                JOIN #tmpSalesForce sf
                                    ON sf.CompanyID = kpipc.CompanyID
                                       AND sf.EmployeeID = kpipc.ObjectID
                                       AND sf.TerritoryType = 'S'
                                JOIN #tmpRoute r
                                    ON r.CompanyID = sf.CompanyID
                                       AND r.SalespersonID = sf.EmployeeID
                        ) kpi
                            ON kpi.CompanyID = tmp.CompanyID
                               AND
                               (
                                   kpi.ROUTE = tmp.ObjectAssignment
                                   OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
                               )
                    WHERE kpid.ObjectID IS NOT NULL
                    GROUP BY tmp.CompanyID,
                             tmp.EmployeeID,
                             tmp.ObjectAssignment;
                END;
                SET @_Min = @_Min + 1;
            END;

            SELECT @_step = @_step + 1;
        END;
    END; ---- KPI BPPC

    BEGIN ---- Tính KPI DropSize by revnue or quantity
        ---- Formula: Total (Revenue or Volumn) / Quantity of PC
        ---- DROPSIZEV: drop size by qty
        ---- DROPSIZER: drop size by revenue
        DELETE FROM #tmpKPIList;

        INSERT INTO #tmpKPIList
        SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY tmp.CodeListSalesID ASC),
               *
        FROM
        (
            SELECT CompanyID,
                   CodeListSalesID,
                   CodeListSalesCD,
                   RefNbr,
                   KPIPeriodNbr,
                   Descr = MAX(Descr),
                   SOURCE = MAX(SOURCE),
                   TYPE = MAX(TYPE),
                   GroupBy = MAX(GroupBy),
                   GroupID = MAX(GroupID),
                   Formula = MAX(Formula),
                   PERIOD = MAX(PERIOD),
                   SalesOrgID = MAX(SalesOrgID),
                   SalesAreaID = MAX(SalesAreaID),
                   LEVEL = MAX(LEVEL),
                   Template = MAX(Template),
                   FromDate,
                   ToDate,
                   ObjectType = MAX(ObjectType),
                   CorrectRouteAndCorrectCoordinates,
                   CorrectRouteAndIncorrectCoordinates,
                   IncorrectRoute,
                   DistributorsSell,
                   MinSKUVolume,
                   MinSKURevenue,
                   ExcludeReturnOrder
            FROM #tmpKPIDetail
            WHERE Template IN ( 'DROPSIZER', 'DROPSIZEV' )
                  AND ObjectType = 'S'
            GROUP BY CompanyID,
                     CodeListSalesID,
                     CodeListSalesCD,
                     RefNbr,
                     KPIPeriodNbr,
                     FromDate,
                     ToDate,
                     CorrectRouteAndCorrectCoordinates,
                     CorrectRouteAndIncorrectCoordinates,
                     IncorrectRoute,
                     DistributorsSell,
                     MinSKUVolume,
                     MinSKURevenue,
                     ExcludeReturnOrder
        ) tmp;

        SELECT @_Max = MAX(RowNumber)
        FROM #tmpKPIList;

        SELECT @_step = 1;
        WHILE @_step <= 2
        BEGIN
            SELECT @_Min = 1;
            WHILE @_Min <= @_Max
            BEGIN
                ---- Xóa dữ liệu SM để tính lại đối với KPI mới
                DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
                DELETE FROM dbo.DMSBLObjectKPITemp;
                DELETE FROM #tmpTotalPC;
                DELETE FROM #tmpTotalLine;

                ---- Lấy thông tin công thức KPI
                SELECT @_KPIID = CodeListSalesID,
                       @_KPICD = CodeListSalesCD,
                       @_KPIDescr = Descr,
                       @_RefNbr = RefNbr,
                       @_KPIPeriodNbr = KPIPeriodNbr,
                       @_Source = SOURCE,
                       @_Type = TYPE,
                       @_GroupBy = ISNULL(GroupBy, 'A'),
                       @_GroupID = GroupID,
                       @_Formula = Formula,
                       @_Template = Template,
                       @_Period = PERIOD,
                       @_SalesAreaID = SalesAreaID,
                       @_Level = LEVEL,
                       @_FromDate = FromDate,
                       @_ToDate = ToDate,
                       @_ObjectType = ObjectType,
                       @_IsValiDateValidDistance = ISNULL(CorrectRouteAndCorrectCoordinates, 0),
                       @_IsValidDateInvalidDistance = ISNULL(CorrectRouteAndIncorrectCoordinates, 0),
                       @_IsInvalidDate = ISNULL(IncorrectRoute, 0),
                       @_IsDistributorSell = ISNULL(DistributorsSell, 0),
                       @_ExcludeReturnOrder = ISNULL(ExcludeReturnOrder, 0),
                       @_MinSKURevenue = MinSKURevenue,
                       @_MinSKUVolume = MinSKUVolume
                FROM #tmpKPIList
                WHERE RowNumber = @_Min;

                IF @_TempFromDate IS NULL
                   OR @_TempToDate IS NULL
                   OR @_TempFromDate != @_FromDate
                   OR @_TempToDate != @_ToDate
                BEGIN
                    DELETE FROM #tmpSalesTerritory;
                    DELETE FROM #tmpRoute;
                    DELETE FROM #tmpSalesForce;

                    ---- Lấy danh sách sales territory trong khoảng thời gian KPI hiệu lực
                    INSERT INTO #tmpSalesTerritory
                    SELECT *
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY st.CompanyID,
                                                                           st.SalesOrg_0_ValueID
                                                              ORDER BY st.EffectiveDate DESC
                                                             ),
                               st.CompanyID,
                               st.SalesOrg_0_ValueID,
                               st.SalesOrg_1_ValueID,
                               st.SalesOrg_2_ValueID,
                               st.SalesOrg_3_ValueID,
                               st.SalesOrg_4_ValueID,
                               st.SalesOrg_5_ValueID,
                               st.SalesOrg_6_ValueID,
                               st.SalesOrg_7_ValueID,
                               st.SalesOrg_8_ValueID,
                               st.SalesOrg_9_ValueID
                        FROM dbo.DMSBLSalesTerritory st WITH (NOLOCK)
                        WHERE st.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN st.EffectiveDate AND ISNULL(st.ExpirationDate, GETDATE())
                                  OR st.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                    ) tmp
                    WHERE tmp.RowNumber = 1;

                    ---- Lấy danh sách salesman và route trong khoảng thời gian KPI hiệu lực
                    INSERT INTO #tmpRoute
                    SELECT tmp.CompanyID,
                           tmp.SalespersonID,
                           tmp.ROUTE,
                           tmp.SalesForceID,
                           tmp.SalesAreaID,
                           tmp.EffectiveDate,
                           tmp.ExpirationDate,
                           st.SalesOrg_0_ValueID,
                           st.SalesOrg_1_ValueID,
                           st.SalesOrg_2_ValueID,
                           st.SalesOrg_3_ValueID,
                           st.SalesOrg_4_ValueID,
                           st.SalesOrg_5_ValueID,
                           st.SalesOrg_6_ValueID,
                           st.SalesOrg_7_ValueID,
                           st.SalesOrg_8_ValueID,
                           st.SalesOrg_9_ValueID
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
                                                                           rs.SalespersonID
                                                              ORDER BY rs.EffectiveDate DESC
                                                             ),
                               rs.CompanyID,
                               rs.SalespersonID,
                               ROUTE = rs.RouteCD,
                               rs.SalesForceID,
                               sph.SalesAreaID,
                               rs.EffectiveDate,
                               ExpirationDate = ISNULL(rs.EndDate, GETDATE())
                        FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
						JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
                        WHERE rs.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR rs.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                        UNION ALL
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY rs.CompanyID,
                                                                           rs.PreviousSalesMan
                                                              ORDER BY rs.EffectiveDate DESC
                                                             ),
                               rs.CompanyID,
                               SalespersonID = rs.PreviousSalesMan,
                               ROUTE = rs.RouteCD,
                               rs.SalesForceID,
                               sph.SalesAreaID,
                               rs.StartDatePre,
                               rs.EndDatePre
                        FROM MRCDMS..DMSRouteSetting rs WITH (NOLOCK)
						JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = rs.SalesAreaID
                        WHERE rs.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN rs.EffectiveDate AND ISNULL(rs.EndDate, GETDATE())
                                  OR rs.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                              AND rs.PreviousSalesMan IS NOT NULL
                    ) tmp
                        JOIN #tmpSalesTerritory st
                            ON st.CompanyID = tmp.CompanyID
                               AND st.SalesOrg_0_ValueID = tmp.SalesAreaID
                    WHERE tmp.RowNumber = 1;

                    ---- Lấy danh sách sales force trong thời gian KPI hiệu lực
                    INSERT INTO #tmpSalesForce
                    SELECT tmp.CompanyID,
                           tmp.EmployeeID,
                           tmp.SFHierachyID,
                           tmp.TerritoryType,
                           tmp.SalesOrgValueID,
                           st.SalesOrg_0_ValueID,
                           st.SalesOrg_1_ValueID,
                           st.SalesOrg_2_ValueID,
                           st.SalesOrg_3_ValueID,
                           st.SalesOrg_4_ValueID,
                           st.SalesOrg_5_ValueID,
                           st.SalesOrg_6_ValueID,
                           st.SalesOrg_7_ValueID,
                           st.SalesOrg_8_ValueID,
                           st.SalesOrg_9_ValueID
                    FROM
                    (
                        SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY sf.CompanyID,
                                                                           sf.EmployeeID
                                                              ORDER BY sf.EffectiveDate DESC
                                                             ),
                               sf.CompanyID,
                               sf.EmployeeID,
                               sf.SFHierachyID,
                               sf.TerritoryType,
                               sf.SalesOrgValueID
                        FROM dbo.DMSBLSalesForce sf WITH (NOLOCK)
                        WHERE sf.CompanyID = @_CompanyID
                              AND
                              (
                                  @_FromDate
                              BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
                                  OR @_ToDate
                              BETWEEN sf.EffectiveDate AND ISNULL(sf.ExpirationDate, GETDATE())
                                  OR sf.EffectiveDate
                              BETWEEN @_FromDate AND @_ToDate
                              )
                    ) tmp
                        LEFT JOIN #tmpSalesTerritory st
                            ON st.CompanyID = tmp.CompanyID
                               AND
                               (
                                   st.SalesOrg_0_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_1_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_2_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_3_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_4_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_5_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_6_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_7_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_8_ValueID = tmp.SalesOrgValueID
                                   OR st.SalesOrg_9_ValueID = tmp.SalesOrgValueID
                               )
                    WHERE tmp.RowNumber = 1;

                    SET @_TempFromDate = @_FromDate;
                    SET @_TempToDate = @_ToDate;
                END;

                ---- Tính KPI PC tạm để tính
                INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
                SELECT rs.CompanyID,
                       rs.SalespersonID,
                       r.SalesForceID,
                       r.SalesAreaID,
                       rs.ROUTE,
                       rs.OrderDate,
                       rs.SourceType,
                       rs.CustomerID,
                       rs.CustomerLocationID,
                       rs.IsValidDate,
                       rs.IsValidDistance,
                       Multiply = CASE
                                      WHEN rs.IsRevenueDeduction = 1 THEN
                                          -1
                                      ELSE
                                          1
                                  END,
                       PC = COUNT(DISTINCT rs.CustomerID),
                       SKU = COUNT(DISTINCT rs.InventoryID),
                       TranAmt = SUM(rs.TranAmt),
                       ShippedQty = SUM(rs.ShippedQty),
                       rs.SalesOrg_0_ValueID,
                       rs.SalesOrg_1_ValueID,
                       rs.SalesOrg_2_ValueID,
                       rs.SalesOrg_3_ValueID,
                       rs.SalesOrg_4_ValueID,
                       rs.SalesOrg_5_ValueID,
                       rs.SalesOrg_6_ValueID,
                       rs.SalesOrg_7_ValueID,
                       rs.SalesOrg_8_ValueID,
                       rs.SalesOrg_9_ValueID,
                       rs.SalesForce_0_ID,
                       rs.SalesForce_1_ID,
                       rs.SalesForce_2_ID,
                       rs.SalesForce_3_ID,
                       rs.SalesForce_4_ID,
                       rs.SalesForce_5_ID,
                       rs.SalesForce_6_ID,
                       rs.SalesForce_7_ID,
                       rs.SalesForce_8_ID,
                       rs.SalesForce_9_ID,
                       Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
                FROM #tmpSORawSales rs
                    LEFT JOIN MRCDMS..DMSViewInventoryItem inv
                        ON rs.CompanyID = inv.CompanyID
                           AND rs.InventoryID = inv.InventoryID
                    LEFT JOIN #tmpRoute r
                        ON r.CompanyID = rs.CompanyID
                           AND r.SalespersonID = rs.SalespersonID
                           AND r.ROUTE = rs.ROUTE
                           AND rs.OrderDate
                           BETWEEN r.EffectiveDate AND r.ExpirationDate
                WHERE rs.OrderDate
                      BETWEEN @_TempFromDate AND @_TempToDate
                      AND rs.OrderType IN (   (CASE
                                                   WHEN @_ExcludeReturnOrder = 0 THEN
                                                       'CM'
                                                   ELSE
                                                       ''
                                               END
                                              ), 'SO'
                                          )
                      AND
                      (
                          ISNULL(@_GroupID, '') = ''
                          OR
                          (
                              EXISTS
                (
                    SELECT TOP 1
                        *
                    FROM MRCDMS..DMSKPIGroupProductSalesDetail igd
                    WHERE igd.CompanyID = @_CompanyID
                          AND igd.CodegGroupProd = @_GroupID
                          AND igd.InventoryID = rs.InventoryID
                )
                              OR EXISTS
                (
                    SELECT *
                    FROM MRCDMS.dbo.DMSKPIGroupProductSalesDetailGroup gr
                    WHERE gr.CompanyID = @_CompanyID
                          AND gr.CodegGroupProd = @_GroupID
                          AND inv.[HierarchyID] = gr.[HierarchyID]
                          OR gr.Attribute0 = inv.Attribute0ID
                          OR gr.Attribute1 = inv.Attribute1ID
                          OR gr.Attribute2 = inv.Attribute2ID
                          OR gr.Attribute3 = inv.Attribute3ID
                          OR gr.Attribute4 = inv.Attribute4ID
                          OR gr.Attribute5 = inv.Attribute5ID
                          OR gr.Attribute6 = inv.Attribute6ID
                          OR gr.Attribute7 = inv.Attribute7ID
                          OR gr.Attribute8 = inv.Attribute8ID
                          OR gr.Attribute9 = inv.Attribute9ID
                )
                          )
                      )
                      AND rs.ShippedQty >= @_MinSKUVolume
                      AND ABS(rs.TranAmt) >= @_MinSKURevenue
                --AND rs.ReasonCode = ( CASE
                --                      WHEN @_IncludePromotionItem = 1
                --                      THEN 'ISSKM'
                --                      WHEN @_IncludeWarrantyItem = 1
                --                      THEN 'ISSBH'
                --                      ELSE 'ISS'
                --                      END )
                GROUP BY rs.CompanyID,
                         rs.SalespersonID,
                         r.SalesForceID,
                         r.SalesAreaID,
                         rs.ROUTE,
                         rs.OrderDate,
                         rs.SourceType,
                         rs.CustomerID,
                         rs.CustomerLocationID,
                         rs.IsValidDate,
                         rs.IsValidDistance,
                         rs.IsRevenueDeduction,
                         rs.SalesOrg_0_ValueID,
                         rs.SalesOrg_1_ValueID,
                         rs.SalesOrg_2_ValueID,
                         rs.SalesOrg_3_ValueID,
                         rs.SalesOrg_4_ValueID,
                         rs.SalesOrg_5_ValueID,
                         rs.SalesOrg_6_ValueID,
                         rs.SalesOrg_7_ValueID,
                         rs.SalesOrg_8_ValueID,
                         rs.SalesOrg_9_ValueID,
                         rs.SalesForce_0_ID,
                         rs.SalesForce_1_ID,
                         rs.SalesForce_2_ID,
                         rs.SalesForce_3_ID,
                         rs.SalesForce_4_ID,
                         rs.SalesForce_5_ID,
                         rs.SalesForce_6_ID,
                         rs.SalesForce_7_ID,
                         rs.SalesForce_8_ID,
                         rs.SalesForce_9_ID;



                DECLARE @tempTemplate nvarchar(50) = (
                                                         SELECT CASE
                                                                    WHEN @_Template = 'DROPSIZER' THEN
                                                                        'REVENUE'
                                                                    WHEN @_Template = 'DROPSIZEV' THEN
                                                                        'QUANTITY'
                                                                END
                                                     );
                EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
                                                                         @tempTemplate,
                                                                         NULL,
                                                                         NULL,
                                                                         NULL,
                                                                         @_IsValiDateValidDistance,
                                                                         @_IsValidDateInvalidDistance,
                                                                         @_IsInvalidDate,
                                                                         @_IsDistributorSell;

                DELETE FROM dbo.DMSBLObjectKPITemp;

                INSERT INTO #tmpTotalLine
                SELECT *
                FROM dbo.DMSBLObjectKPITemp;
                EXEC dbo.sp_DMS_Baseline_KPI_RevenueAndVolumeObjectSales @_CompanyID,
                                                                         'PC',
                                                                         NULL,
                                                                         NULL,
                                                                         NULL,
                                                                         @_IsValiDateValidDistance,
                                                                         @_IsValidDateInvalidDistance,
                                                                         @_IsInvalidDate,
                                                                         @_IsDistributorSell;

                INSERT INTO #tmpTotalPC
                SELECT *
                FROM dbo.DMSBLObjectKPITemp;
                ---- Kết thúc tính KPI ra các tham số để tính KPI
                ---- 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

                IF @_step = 1
                BEGIN
                    INSERT INTO #tmpKPI
                    SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
                           CompanyID = tmp.CompanyID,
                           @_KPIID,
                           @_KPICD,
                           @_KPIDescr,
                           @_Source,
                           @_Type,
                           @_GroupBy,
                           @_GroupID,
                           @_Formula,
                           @_Template,
                           @_KPIPeriodNbr,
                           ISNULL(
                                     @_RefNbr,
                                     'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
                                     + CONVERT(VARCHAR(20), @_ToDate, 111)
                                 ),
                           @_Period,
                           @_FromDate,
                           @_ToDate,
                           ObjectID = tmp.EmployeeID,
                           TempObjectID = ISNULL(kpid.ObjectID, 0),
                           'S',
                           tmp.ObjectAssignment,
                           kpid.TargetsSuggest,  ---- temp
                           kpid.TargetsAssigned, ---- temp
                           kpid.TargetsSuggest,
                           kpid.TargetsAssigned,
                           kpia.Actual1,
                           kpia.Actual2,
                           kpia.Actual3,
                           kpia.Actual4,
                           kpia.Actual
                    FROM
                    (
                        SELECT sf.CompanyID,
                               sf.EmployeeID,
                               ObjectAssignment = CASE
                                                      WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
                                                          r.ROUTE
                                                      ELSE
                                                          CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                                  END
                        FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
                            JOIN #tmpSalesForce sf WITH (NOLOCK)
                                ON sf.CompanyID = objectPeriod.CompanyID
                                   AND sf.SFHierachyID = objectPeriod.KPIObjectID
                            LEFT JOIN #tmpRoute r
                                ON r.CompanyID = sf.CompanyID
                                   AND
                                   (
                                       r.SalespersonID = sf.EmployeeID
                                       OR r.SalesForceID = sf.EmployeeID
                                   )
                        WHERE objectPeriod.CompanyID = @_CompanyID
                              AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
                              AND
                              (
                                  @_SalesAreaID IS NULL
                                  OR @_Level = -1
                                  OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
                              )
                        GROUP BY sf.CompanyID,
                                 sf.EmployeeID,
                                 CASE
                                     WHEN sf.TerritoryType IN ( 'S', 'D' ) THEN
                                         r.ROUTE
                                     ELSE
                                         CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                 END
                    ) tmp
                        LEFT JOIN
                        (
                            SELECT kpid.CompanyID,
                                   kpid.ObjectID,
                                   kpid.KPIPeriodNbr,
                                   kpid.FromDate,
                                   kpid.ToDate,
                                   kpid.TargetsSuggest,
                                   kpid.TargetsAssigned
                            FROM #tmpKPIDetail kpid
                            WHERE kpid.CodeListSalesID = @_KPIID
                                  AND
                                  (
                                      @_RefNbr LIKE 'Temp-%'
                                      OR kpid.RefNbr = @_RefNbr
                                  )
                                  AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
                                  AND kpid.FromDate = @_FromDate
                                  AND kpid.ToDate = @_ToDate
                        ) kpid
                            ON kpid.CompanyID = tmp.CompanyID
                               AND kpid.ObjectID = tmp.EmployeeID
                        LEFT JOIN
                        (
                            SELECT kpipc.CompanyID,
                                   kpipc.ObjectID,
                                   kpipc.ObjectAssignment,
                                   Actual1 = CASE
                                                 WHEN @_IsValiDateValidDistance = 1
                                                      AND ISNULL(kpipc.Actual1, 0) > 0 THEN
                                                     kpisku.Actual1 / kpipc.Actual1
                                                 ELSE
                                                     0
                                             END,
                                   Actual2 = CASE
                                                 WHEN @_IsValidDateInvalidDistance = 1
                                                      AND ISNULL(kpipc.Actual2, 0) > 0 THEN
                                                     kpisku.Actual2 / kpipc.Actual2
                                                 ELSE
                                                     0
                                             END,
                                   Actual3 = CASE
                                                 WHEN @_IsInvalidDate = 1
                                                      AND ISNULL(kpipc.Actual3, 0) > 0 THEN
                                                     kpisku.Actual3 / kpipc.Actual3
                                                 ELSE
                                                     0
                                             END,
                                   Actual4 = CASE
                                                 WHEN @_IsDistributorSell = 1
                                                      AND ISNULL(kpipc.Actual4, 0) > 0 THEN
                                                     kpisku.Actual4 / kpipc.Actual4
                                                 ELSE
                                                     0
                                             END,
                                   Actual = CASE
                                                WHEN ISNULL(kpipc.Actual, 0) > 0 THEN
                                                    kpisku.Actual / kpipc.Actual
                                                ELSE
                                                    0
                                            END
                            FROM #tmpTotalPC kpipc
                                JOIN #tmpTotalLine kpisku
                                    ON kpisku.CompanyID = kpipc.CompanyID
                                       AND kpisku.ObjectID = kpipc.ObjectID
                                       AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
                                JOIN #tmpSalesForce sf
                                    ON sf.CompanyID = kpipc.CompanyID
                                       AND sf.EmployeeID = kpipc.ObjectID
                                       AND sf.TerritoryType = 'S'
                                JOIN #tmpRoute r
                                    ON r.CompanyID = sf.CompanyID
                                       AND r.SalespersonID = sf.EmployeeID
                        ) kpia
                            ON kpia.CompanyID = tmp.CompanyID
                               AND kpia.ObjectID = tmp.EmployeeID
                               AND kpia.ObjectAssignment = tmp.ObjectAssignment
                    WHERE kpid.ObjectID IS NOT NULL;
                END;
                ELSE IF @_step = 2
                BEGIN
                    INSERT INTO #tmpKPI
                    SELECT ROW_NUMBER() OVER (PARTITION BY tmp.CompanyID, tmp.EmployeeID ORDER BY tmp.EmployeeID),
                           tmp.CompanyID,
                           @_KPIID,
                           @_KPICD,
                           @_KPIDescr,
                           @_Source,
                           @_Type,
                           @_GroupBy,
                           @_GroupID,
                           @_Formula,
                           @_Template,
                           @_KPIPeriodNbr,
                           ISNULL(
                                     @_RefNbr,
                                     'Temp-' + CONVERT(VARCHAR(20), @_FromDate, 111) + '-'
                                     + CONVERT(VARCHAR(20), @_ToDate, 111)
                                 ),
                           @_Period,
                           @_FromDate,
                           @_ToDate,
                           ObjectID = tmp.EmployeeID,
                           TempObjectID = MAX(ISNULL(kpid.ObjectID, 0)),
                           'S',
                           tmp.ObjectAssignment,
                           TargetsSuggest = MAX(kpid.TargetsSuggest),   ---- temp
                           TargetsAssigned = MAX(kpid.TargetsAssigned), ---- temp
                           TargetsSuggest = MAX(kpid.TargetsSuggest),
                           TargetsAssigned = MAX(kpid.TargetsAssigned),
                           Actual1 = CASE
                                         WHEN @_IsValiDateValidDistance = 1
                                              AND SUM(kpi.PC1) > 0 THEN
                                             SUM(kpi.AC1) / SUM(kpi.PC1)
                                         ELSE
                                             0
                                     END,
                           Actual2 = CASE
                                         WHEN @_IsValidDateInvalidDistance = 1
                                              AND SUM(kpi.PC2) > 0 THEN
                                             SUM(kpi.AC2) / SUM(kpi.PC2)
                                         ELSE
                                             0
                                     END,
                           Actual3 = CASE
                                         WHEN @_IsInvalidDate = 1
                                              AND SUM(kpi.PC3) > 0 THEN
                                             SUM(kpi.AC3) / SUM(kpi.PC3)
                                         ELSE
                                             0
                                     END,
                           Actual4 = CASE
                                         WHEN @_IsDistributorSell = 1
                                              AND SUM(kpi.PC4) > 0 THEN
                                             SUM(kpi.AC4) / SUM(kpi.PC4)
                                         ELSE
                                             0
                                     END,
                           Actual = CASE
                                        WHEN SUM(kpi.PC) > 0 THEN
                                            SUM(kpi.AC) / SUM(kpi.PC)
                                        ELSE
                                            0
                                    END
                    FROM
                    (
                        SELECT sf.CompanyID,
                               sf.EmployeeID,
                               ObjectAssignment = CASE
                                                      WHEN sf.TerritoryType = 'D' THEN
                                                          r.ROUTE
                                                      ELSE
                                                          CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                                  END,
                               sf.SalesOrg_0_ValueID
                        FROM MRCDMS..DMSKPIListSalesObjectPeriod objectPeriod WITH (NOLOCK)
                            JOIN #tmpSalesForce sf WITH (NOLOCK)
                                ON sf.CompanyID = objectPeriod.CompanyID
                                   AND sf.SFHierachyID = objectPeriod.KPIObjectID
                            LEFT JOIN #tmpRoute r
                                ON r.CompanyID = sf.CompanyID
                                   AND r.SalesForceID = sf.EmployeeID
                        WHERE objectPeriod.CompanyID = @_CompanyID
                              AND objectPeriod.KPIPeriodNbr = @_KPIPeriodNbr
                              AND sf.TerritoryType != 'S'
                              AND
                              (
                                  @_SalesAreaID IS NULL
                                  OR @_Level = -1
                                  OR COALESCE(sf.SalesOrg_0_ValueID, r.SalesOrg_0_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_1_ValueID, r.SalesOrg_1_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_2_ValueID, r.SalesOrg_2_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_3_ValueID, r.SalesOrg_3_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_4_ValueID, r.SalesOrg_4_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_5_ValueID, r.SalesOrg_5_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_6_ValueID, r.SalesOrg_6_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_7_ValueID, r.SalesOrg_7_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_8_ValueID, r.SalesOrg_8_ValueID) = @_SalesAreaID
                                  OR COALESCE(sf.SalesOrg_9_ValueID, r.SalesOrg_9_ValueID) = @_SalesAreaID
                              )
                        GROUP BY sf.CompanyID,
                                 sf.EmployeeID,
                                 CASE
                                     WHEN sf.TerritoryType = 'D' THEN
                                         r.ROUTE
                                     ELSE
                                         CONVERT(VARCHAR(20), sf.SalesOrgValueID)
                                 END,
                                 sf.SalesOrg_0_ValueID
                    ) tmp
                        LEFT JOIN
                        (
                            SELECT kpid.CompanyID,
                                   kpid.ObjectID,
                                   kpid.KPIPeriodNbr,
                                   kpid.FromDate,
                                   kpid.ToDate,
                                   kpid.TargetsSuggest,
                                   kpid.TargetsAssigned
                            FROM #tmpKPIDetail kpid
                            WHERE kpid.CodeListSalesID = @_KPIID
                                  AND
                                  (
                                      @_RefNbr LIKE 'Temp-%'
                                      OR kpid.RefNbr = @_RefNbr
                                  )
                                  AND kpid.KPIPeriodNbr = @_KPIPeriodNbr
                                  AND kpid.FromDate = @_FromDate
                                  AND kpid.ToDate = @_ToDate
                        ) kpid
                            ON kpid.CompanyID = tmp.CompanyID
                               AND kpid.ObjectID = tmp.EmployeeID
                        LEFT JOIN
                        (
                            SELECT DISTINCT
                                kpipc.CompanyID,
                                kpipc.ObjectID,
                                kpipc.ObjectAssignment,
                                AC1 = kpisku.Actual1,
                                AC2 = kpisku.Actual2,
                                AC3 = kpisku.Actual3,
                                AC4 = kpisku.Actual4,
                                AC = kpisku.Actual,
                                PC1 = kpipc.Actual1,
                                PC2 = kpipc.Actual2,
                                PC3 = kpipc.Actual3,
                                PC4 = kpipc.Actual4,
                                PC = kpipc.Actual,
                                r.ROUTE,
                                r.SalesAreaID
                            FROM #tmpTotalPC kpipc
                                JOIN #tmpTotalLine kpisku
                                    ON kpisku.CompanyID = kpipc.CompanyID
                                       AND kpisku.ObjectID = kpipc.ObjectID
                                       AND kpisku.ObjectAssignment = kpipc.ObjectAssignment
                                JOIN #tmpSalesForce sf
                                    ON sf.CompanyID = kpipc.CompanyID
                                       AND sf.EmployeeID = kpipc.ObjectID
                                       AND sf.TerritoryType = 'S'
                                JOIN #tmpRoute r
                                    ON r.CompanyID = sf.CompanyID
                                       AND r.SalespersonID = sf.EmployeeID
                        ) kpi
                            ON kpi.CompanyID = tmp.CompanyID
                               AND
                               (
                                   kpi.ROUTE = tmp.ObjectAssignment
                                   OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
                               )
                    WHERE kpid.ObjectID IS NOT NULL
                    GROUP BY tmp.CompanyID,
                             tmp.EmployeeID,
                             tmp.ObjectAssignment;
                END;
                SET @_Min = @_Min + 1;
            END;

            SELECT @_step = @_step + 1;
        END;
    END; ---- Tính KPI DropSize by revnue or quantity

    UPDATE kpi
    SET kpi.TargetsSuggest = tmp.TargetsSuggest,
        kpi.TargetsAssigned = tmp.TargetsAssigned
    FROM #tmpKPI kpi
        JOIN
        (
            SELECT CompanyID,
                   CodeListSalesID,
                   KPIPeriodNbr,
                   RefNbr,
                   ObjectID,
                   TargetsSuggest = SUM(TargetsSuggest),
                   TargetsAssigned = MAX(TargetsAssigned)
            FROM #tmpKPI
            WHERE RowNumber > 1
            GROUP BY CompanyID,
                     CodeListSalesID,
                     KPIPeriodNbr,
                     RefNbr,
                     ObjectID
        ) tmp
            ON tmp.CompanyID = kpi.CompanyID
               AND tmp.CodeListSalesID = kpi.CodeListSalesID
               AND tmp.KPIPeriodNbr = kpi.KPIPeriodNbr
               AND tmp.RefNbr = kpi.RefNbr
               AND tmp.ObjectID = kpi.ObjectID
    WHERE kpi.RowNumber = 1;

    INSERT INTO dbo.DMSBLKPI
    (
        BaselineDate,
        CompanyID,
        KPIID,
        KPICD,
        KPIDescr,
        SOURCE,
        TYPE,
        GroupBy,
        GroupID,
        Formula,
        Template,
        KPIPeriodNbr,
        RefNbr,
        PERIOD,
        FromDate,
        ToDate,
        ObjectID,
        ObjectType,
        ObjectAssignment,
        TargetsSuggest,
        TargetsAssigned,
        Actual1,
        Actual2,
        Actual3,
        Actual4,
        Actual
    )
    SELECT GETDATE(),
           CompanyID,
           CodeListSalesID,
           CodeListSalesCD,
           Descr,
           SOURCE,
           TYPE,
           GroupBy,
           GroupID,
           Formula,
           Template,
           KPIPeriodNbr,
           RefNbr,
           PERIOD,
           FromDate,
           ToDate,
           ObjectID,
           ObjectType,
           ObjectAssignment,
           TargetsSuggest,
           TargetsAssigned,
           Actual1,
           Actual2,
           Actual3,
           Actual4,
           Actual
    FROM
    (
        SELECT Temp = ROW_NUMBER() OVER (PARTITION BY CompanyID,
                                                      CodeListSalesID,
                                                      KPIPeriodNbr,
                                                      ObjectID
                                         --, ObjectAssignment -- Thu.Nguyen 2019-03-12 bỏ Partition theo ObjectAssignment vì KPI chỉ assign cho SR, không assign theo ROUTE
                                         ORDER BY TempObjectID DESC,
                                                  RefNbr DESC
                                        ),
               *
        FROM #tmpKPI
        WHERE ObjectAssignment IS NOT NULL
    ) tmp
    WHERE tmp.Temp = 1;

    -- Update lại Actual cho chỉ tiêu BPPC
    UPDATE kpispcd
    SET kpispcd.Actual = ISNULL(kpi.Actual, 0),
        kpispcd.Actual1 = ISNULL(kpi.Actual1, 0),
        kpispcd.Actual2 = ISNULL(kpi.Actual2, 0),
        kpispcd.Actual3 = ISNULL(kpi.Actual3, 0),
        kpispcd.Actual4 = ISNULL(kpi.Actual4, 0)
    FROM DMSBLKPI kpispcd
        JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader kpispch
            ON kpispcd.CompanyID = kpispch.CompanyID
               AND kpispcd.RefNbr = kpispch.RefNbr
        JOIN
        (
            SELECT CompanyID,
                   CodeListSalesID,
                   KPIPeriodNbr,
                   RefNbr,
                   ObjectID,
                   Actual = SUM(Actual),
                   Actual1 = SUM(Actual1),
                   Actual2 = SUM(Actual2),
                   Actual3 = SUM(Actual3),
                   Actual4 = SUM(Actual4)
            FROM #tmpKPI
            WHERE ObjectAssignment IS NOT NULL
                  AND Template = 'BPPC'
            GROUP BY CompanyID,
                     CodeListSalesID,
                     KPIPeriodNbr,
                     RefNbr,
                     ObjectID
        ) kpi
            ON kpi.CompanyID = kpispcd.CompanyID
               AND kpi.RefNbr = kpispcd.RefNbr
               AND kpi.KPIPeriodNbr = kpispch.KPIPeriodNbr
               AND kpi.CodeListSalesID = kpispcd.KPIID
               AND kpi.ObjectID = kpispcd.ObjectID;

    UPDATE kpispcd
    SET kpispcd.ValuesBaseline = ISNULL(kpi.Actual, 0)
    FROM MRCDMS..DMSKPISalesPeriodConfigurationDetail kpispcd
        JOIN MRCDMS..DMSKPISalesPeriodConfigurationHeader kpispch
            ON kpispcd.CompanyID = kpispch.CompanyID
               AND kpispcd.RefNbr = kpispch.RefNbr
        JOIN
        (
            SELECT CompanyID,
                   CodeListSalesID,
                   KPIPeriodNbr,
                   RefNbr,
                   ObjectID,
                   Actual = SUM(Actual)
            FROM #tmpKPI
            GROUP BY CompanyID,
                     CodeListSalesID,
                     KPIPeriodNbr,
                     RefNbr,
                     ObjectID
        ) kpi
            ON kpi.CompanyID = kpispcd.CompanyID
               AND kpi.RefNbr = kpispcd.RefNbr
               AND kpi.KPIPeriodNbr = kpispch.KPIPeriodNbr
               AND kpi.CodeListSalesID = kpispcd.CodeListSalesID
               AND kpi.ObjectID = kpispcd.ThisObject;

END;