USE [MRCBaseline]
GO
/****** Object:  StoredProcedure [dbo].[sp_DMS_Baseline_KPI]    Script Date: 5/21/2019 10:30:44 AM ******/
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, '2018/09/25'
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;

    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)
    );

    DELETE FROM dbo.DMSBLKPI
    WHERE CompanyID = @_CompanyID
          AND @_Date
          BETWEEN FromDate AND ToDate;

    ---- 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
        LEFT 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
        LEFT 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
        LEFT 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;
    --1 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;
    ---- 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.SalespersonID
                                                      ORDER BY rs.EffectiveDate DESC
                                                     ),
                       rs.CompanyID,
                       SalespersonID = rs.PreviousSalesMan,
                       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
                      )
                      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 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,
                       sph.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
					LEFT JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = R.SalesAreaID
                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,
                         sph.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,
                       sph.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
					LEFT JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = R.SalesAreaID
                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,
                         sph.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;
        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;
        END;

        SET @_Min = @_Min + 1;
    END;
    ---- 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.SalespersonID
                                                          ORDER BY rs.EffectiveDate DESC
                                                         ),
                           rs.CompanyID,
                           SalespersonID = rs.PreviousSalesMan,
                           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
                          )
                          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,
                       sph.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.OrderNbr),
                       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
					LEFT JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = r.SalesAreaID
                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,
                         sph.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,
                       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
                FROM #tempDMSBLSalesObject
                GROUP BY CompanyID,
                         SalespersonID,
                         SalesForceID,
                         SalesAreaID,
                         ROUTE,
                         OrderDate,
                         SourceType,
                         CustomerID,
                         CustomerLocationID,
                         IsValidDate,
                         IsValidDistance,
                         Multiply,
                         PC,
                         SKU,
                         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;
                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;
            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,
                            sph.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
							JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = r.CompanyID AND sph.SellingProvinceHeaderID = r.SalesAreaID
                        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
                           )
                GROUP BY tmp.CompanyID,
                         tmp.EmployeeID,
                         tmp.ObjectAssignment;
            END;

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

    ---- 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.SalespersonID
                                                          ORDER BY rs.EffectiveDate DESC
                                                         ),
                           rs.CompanyID,
                           SalespersonID = rs.PreviousSalesMan,
                           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
                          )
                          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,
                   sph.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.OrderNbr),
                   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
				LEFT JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = r.SalesAreaID
            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,
                     sph.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;
            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,
                            sph.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
							JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = r.CompanyID AND sph.SellingProvinceHeaderID = r.SalesAreaID
                    ) kpi
                        ON kpi.CompanyID = tmp.CompanyID
                           AND
                           (
                               kpi.ROUTE = tmp.ObjectAssignment
                               OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
                           )
                GROUP BY tmp.CompanyID,
                         tmp.EmployeeID,
                         tmp.ObjectAssignment;
            END;
            SET @_Min = @_Min + 1;
        END;

        SELECT @_step = @_step + 1;
    END;


    ---- 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.SalespersonID
                                                          ORDER BY rs.EffectiveDate DESC
                                                         ),
                           rs.CompanyID,
                           SalespersonID = rs.PreviousSalesMan,
                           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
                          )
                          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;

            DELETE FROM dbo.DMSBLSalesObjectRawDataTemp;
            ---- Tính KPI PC tạm
            INSERT INTO dbo.DMSBLSalesObjectRawDataTemp
            SELECT rs.CompanyID,
                   rs.SalespersonID,
                   r.SalesForceID,
                   sph.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.OrderDate),
                   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
				LEFT JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = r.SalesAreaID
            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,
                     sph.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,
                   sph.SalesAreaID,
                   rs.ROUTE,
                   rs.OrderDate,
                   rs.CustomerID,
                   rs.CustomerLocationID,
                   Multiply = CASE
                                  WHEN rs.IsRevenueDeduction = 1 THEN
                                      -1
                                  ELSE
                                      1
                              END,
                   PC = COUNT(DISTINCT rs.OrderDate),
                   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
				LEFT JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = r.SalesAreaID
            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,
                     sph.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;

            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,
                   sph.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.OrderNbr),
                   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
				LEFT JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = r.SalesAreaID
            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,
                     sph.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'
                            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;
            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,
                            sph.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
						JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = r.CompanyID AND sph.SellingProvinceHeaderID = r.SalesAreaID
                    ) kpi
                        ON kpi.CompanyID = tmp.CompanyID
                           AND
                           (
                               kpi.ROUTE = tmp.ObjectAssignment
                               OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
                           )
                GROUP BY tmp.CompanyID,
                         tmp.EmployeeID,
                         tmp.ObjectAssignment;
            END;
            SET @_Min = @_Min + 1;
        END;

        SELECT @_step = @_step + 1;
    END;


    ---- 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.SalespersonID
                                                          ORDER BY rs.EffectiveDate DESC
                                                         ),
                           rs.CompanyID,
                           SalespersonID = rs.PreviousSalesMan,
                           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
                          )
                          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,
                   sph.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.OrderNbr),
                   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
				LEFT JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = rs.CompanyID AND sph.SellingProvinceHeaderID = r.SalesAreaID
            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,
                     sph.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;
            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,
                            sph.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
							JOIN MRCDMS..DMSSellingProvinceHeader sph ON sph.CompanyID = r.CompanyID AND sph.SellingProvinceHeaderID = r.SalesAreaID
                    ) kpi
                        ON kpi.CompanyID = tmp.CompanyID
                           AND
                           (
                               kpi.ROUTE = tmp.ObjectAssignment
                               OR kpi.SalesAreaID = tmp.SalesOrg_0_ValueID
                           )
                GROUP BY tmp.CompanyID,
                         tmp.EmployeeID,
                         tmp.ObjectAssignment;
            END;
            SET @_Min = @_Min + 1;
        END;

        SELECT @_step = @_step + 1;
    END;


    ----- Cập nhật lại các số target sau khi hoàn tất tính KPIs
    --    UPDATE  kpi
    --    SET     kpi.TargetsSuggest = ROUND(kpi.TargetsSuggest
    --                                       / tmp.TotalAssignment, 2) ,
    --            kpi.TargetsAssigned = ROUND(kpi.TargetsAssigned
    --                                        / tmp.TotalAssignment, 2)
    --    FROM    #tmpKPI kpi
    --            JOIN ( SELECT   CompanyID ,
    --                            CodeListSalesID ,
    --                            KPIPeriodNbr ,
    --                            RefNbr ,
    --                            ObjectID ,
    --                            TotalAssignment = COUNT(*)
    --                   FROM     #tmpKPI
    --                   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;

    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;

    --SELECT    Temp = ROW_NUMBER() OVER ( PARTITION BY CompanyID,
    --                                                            CodeListSalesID,
    --                                                            KPIPeriodNbr,
    --                                                            ObjectID,
    --                                                            ObjectAssignment ORDER BY TempObjectID DESC, RefNbr DESC ) ,
    --                                  *
    --                        FROM      #tmpKPI
    --                        WHERE     ObjectAssignment IS NOT NULL --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
                                         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;

    -- Tính KPI display
    --EXEC dbo.sp_DMS_Baseline_KPI_Display 3, @_Date

    -- 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) / 2,
    --               Actual1 = SUM(Actual1) / 2,
    --               Actual2 = SUM(Actual2) / 2,
    --               Actual3 = SUM(Actual3) / 2,
    --               Actual4 = SUM(Actual4) / 2
    --        FROM #tmpKPI
    --        WHERE ObjectAssignment IS NOT NULL
    --              AND Template = 'BPPC'
    --              AND ObjectID IN ( 1283, 1476, 2017, 2396 )
    --        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;
END;