Untitled

USE [BELBaseline]
GO
/****** Object:  StoredProcedure [dbo].[sp_DMS_BaselineDaily]    Script Date: 10/22/2019 2:20:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Dinh Hoang Lam
-- Create date: 2015/04/02
-- Description:
-- =============================================
-- EXEC sp_DMS_BaselineDaily 3, '2018/04/01', '2018/04/26', null
ALTER PROCEDURE [dbo].[sp_DMS_BaselineDaily]
	@CompanyID INT
	, @FromDate DATETIME
	, @ToDate DATETIME
	, @DistributorID VARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @_CompanyID INT = @CompanyID
    DECLARE @_FromDate DATETIME = @FromDate
    DECLARE @_ToDate DATETIME = @ToDate
    DECLARE @_DistributorID VARCHAR(MAX) = NULLIF(LTRIM(RTRIM(@DistributorID)), '')
    DECLARE @_Month INT = MONTH(@_FromDate)
    DECLARE @_Year INT = YEAR(@_FromDate)
    DECLARE @_MonthID CHAR(6) = RIGHT('0' + CONVERT(VARCHAR(2), @_Month), 2) + CONVERT(VARCHAR(4), @_Year)
    DECLARE @_FirstDayOfMonth DATETIME = CONVERT(DATETIME, CONVERT(VARCHAR(4), @_Year) + '/' + CONVERT(VARCHAR(2), @_Month) + '/1')
    DECLARE @_LastDayOfMonth DATETIME = DATEADD(dd, -1, DATEADD(mm, 1, @_FirstDayOfMonth))
    DECLARE @_FirstDayOfPreMonth DATETIME = DATEADD(mm, -1, @_FirstDayOfMonth)
    DECLARE @_Weekend CHAR(3)
    DECLARE @_LeadDate INT
    DECLARE @_ClosedDate DATETIME
    DECLARE @_Date DATETIME
    DECLARE @_Holiday DATETIME
    DECLARE @_CheckDate datetime = getdate()

    SELECT * INTO #tmpDistributor FROM dbo.fn_DMS_ConvertIDListToTable(@_DistributorID)
    IF NOT EXISTS (SELECT TOP 1 * FROM #tmpDistributor)
        INSERT INTO #tmpDistributor
        SELECT DistributorID FROM BELDMS..DMSViewDistributor WHERE CompanyID = @_CompanyID

    SELECT @_Weekend = (SELECT Weekend FROM BELDMS..DMSSalesCalendar WHERE CompanyID = @_CompanyID AND YEAR = YEAR(@_ToDate))
    SELECT @_LeadDate = (SELECT LeadDate FROM BELDMS..DMSBaselineConfig WHERE CompanyID = @_CompanyID)

    IF @_LeadDate IS NOT NULL AND UPPER(SUBSTRING(DATENAME(dw, @_ToDate), 1, 3)) != @_Weekend
        BEGIN
            ---- Chốt thời gian đóng ngày
            ---- Xóa thời gian đóng ngày trước đó
            DELETE FROM BELDMS..DMSBaselineClosedDate
            WHERE
                CompanyID = @_CompanyID
                AND EXISTS (SELECT * FROM #tmpDistributor WHERE ID = BranchID)

            SET @_ClosedDate = DATEADD(dd, @_LeadDate * -1, @_ToDate)
            SET @_Date = DATEADD(dd, -1, @_ToDate)

            WHILE @_Date > @_ClosedDate
                BEGIN
                    ---- Neu ngay lead date roi vao chu nhat thi lui ve 1 ngay
                    IF UPPER(SUBSTRING(DATENAME(dw, @_Date), 1, 3)) = @_Weekend
                        BEGIN
                            SET @_ClosedDate = DATEADD(dd, -1, @_ClosedDate)
                            SET @_Date = DATEADD(dd, -1, @_Date)
                        END

                    SET @_Holiday = (SELECT TOP 1 FromDate FROM BELDMS..DMSHoliday WHERE CompanyID = @_CompanyID AND YEAR = YEAR(@_Date) AND @_Date BETWEEN FromDate AND ToDate)
                    IF @_Holiday IS NOT NULL
                        BEGIN
                            SET @_ClosedDate = DATEADD(dd, -1 * DATEDIFF(dd, @_Holiday, @_Date) - 1, @_ClosedDate)
                            SET @_Date = @_Holiday
                        END

                    SET @_Date = DATEADD(dd, -1, @_Date)
                END

            ---- Thêm mới thời gian đóng ngày để chặn user nhập giao dịch
            INSERT INTO BELDMS..DMSBaselineClosedDate
            (
                CompanyID
                , BranchID
                , ClosedDate
                , CreatedByID
                , CreatedByScreenID
                , CreatedDateTime
                , LastModifiedByID
                , LastModifiedByScreenID
                , LastModifiedDateTime
            )
            SELECT
                @_CompanyID
                , ID
                , @_ClosedDate
                , NULL
                , 'BASELINE'
                , GETDATE()
                , NULL
                , 'BASELINE'
                , GETDATE()
            FROM
                #tmpDistributor
        END

    ---- Baseline MDM
    EXEC dbo.sp_DMS_BaselineMDM @_CompanyID, @_ToDate


    ---- Baseline Transaction Data
    BEGIN

        DECLARE @StartTime datetime , @EndTime datetime;
        DECLARE @StartRowCount FLOAT , @EndRowCount FLOAT , @RowCountNumber FLOAT;
        DECLARE @Message NVARCHAR(MAX) , @Parameter nvarchar(MAX);
        SET @Message='';
        DECLARE @Error NVARCHAR(MAX);

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FromDate=' + CONVERT(nvarchar(20),@_FromDate) + ',@_LastDayOfMonth=' + CONVERT(nvarchar(20),@_LastDayOfMonth) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLMCP)
            EXEC dbo.sp_DMS_Baseline_MCP @_CompanyID, @_FromDate, @_LastDayOfMonth, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLMCP)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_MCP' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_MCP failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_MCP' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FromDate=' + CONVERT(nvarchar(20),@_FromDate) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLRawSales)
            EXEC dbo.sp_DMS_Baseline_RawSales @_CompanyID, @_FromDate, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLRawSales)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_RawSales' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_RawSales failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_RawSales' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

    --  EXEC dbo.sp_DMS_Baseline_IncompleteRawSales @_CompanyID, @_FromDate, @_ToDate, @_DistributorID

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FromDate=' + CONVERT(nvarchar(20),@_FromDate) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLSalesIn)
            EXEC dbo.sp_DMS_Baseline_SalesIn @_CompanyID, @_FromDate, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLSalesIn)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_SalesIn' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_SalesIn failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_SalesIn' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FromDate=' + CONVERT(nvarchar(20),@_FromDate) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLSalesInNoRequisition)
            EXEC dbo.sp_DMS_Baseline_SalesInNoRequisition @_CompanyID, @_FromDate, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLSalesInNoRequisition)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_SalesInNoRequisition' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_SalesInNoRequisition failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_SalesInNoRequisition' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FirstDayOfMonth=' + CONVERT(nvarchar(20),@_FirstDayOfMonth) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLPromotionDetail)
            EXEC dbo.sp_DMS_Baseline_PromotionDetail @_CompanyID, @_FirstDayOfMonth, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLPromotionDetail)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_PromotionDetail' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_PromotionDetail failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_PromotionDetail' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FirstDayOfMonth=' + CONVERT(nvarchar(20),@_FirstDayOfMonth) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLPromotionDetailFull)
            EXEC dbo.sp_DMS_Baseline_PromotionDetailFull @_CompanyID, @_FirstDayOfMonth, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLPromotionDetailFull)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_PromotionDetailFull' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_PromotionDetailFull failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_PromotionDetailFull' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch


        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FromDate=' + CONVERT(nvarchar(20),@_FromDate) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLAllocationDetail)
            EXEC dbo.sp_DMS_Baseline_AllocationDetail @_CompanyID, @_FromDate, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLAllocationDetail)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_AllocationDetail' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_AllocationDetail failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_AllocationDetail' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FromDate=' + CONVERT(nvarchar(20),@_FromDate) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLEvaluation)
            EXEC dbo.sp_DMS_Baseline_Evaluation @_CompanyID, @_FromDate, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLEvaluation)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_Evaluation' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_Evaluation failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_Evaluation' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FirstDayOfMonth=' + CONVERT(nvarchar(20),@_FirstDayOfMonth) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLInventoryStock)
            EXEC dbo.sp_DMS_Baseline_InventoryStock @_CompanyID, @_FirstDayOfMonth, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLInventoryStock)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_InventoryStock' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_InventoryStock failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_InventoryStock' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch


        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FirstDayOfMonth=' + CONVERT(nvarchar(20),@_FirstDayOfMonth) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLInventoryStockByOrderDate)
            EXEC dbo.sp_DMS_Baseline_InventoryStockByOrderDate @_CompanyID, @_FirstDayOfMonth, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLInventoryStockByOrderDate)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_InventoryStockByOrderDate' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_InventoryStockByOrderDate failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_InventoryStockByOrderDate' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FromDate=' + CONVERT(nvarchar(20),@_FromDate) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLSales)
            EXEC dbo.sp_DMS_Baseline_Sales @_CompanyID, @_FromDate, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLSales)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_Sales' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_Sales failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_Sales' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch


        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FromDate=' + CONVERT(nvarchar(20),@_FromDate) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLRouteSales)
            EXEC dbo.sp_DMS_Baseline_RouteSales @_CompanyID, @_FromDate, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLRouteSales)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_RouteSales' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_RouteSales failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_RouteSales' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch


        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FromDate=' + CONVERT(nvarchar(20),@_FromDate) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLASO)
            EXEC dbo.sp_DMS_Baseline_ASO @_CompanyID, @_FromDate, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLASO)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_ASO' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_ASO failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_ASO' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FromDate=' + CONVERT(nvarchar(20),@_FirstDayOfMonth) + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLProgramTracking)
            EXEC dbo.sp_DMS_Baseline_ProgramTracking @_CompanyID, @_FirstDayOfMonth, @_ToDate, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLProgramTracking)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_ProgramTracking' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_ProgramTracking failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_ProgramTracking' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch


        BEGIN try
            SET @Parameter = '@_CompanyID = 3' + ',@_ToDate=' + CONVERT(nvarchar(20),@_ToDate)
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLKPI)
            EXEC dbo.sp_DMS_Baseline_KPI @_CompanyID, @_ToDate
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLKPI)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_KPI' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_KPI failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_KPI' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch


        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_Month=' + CONVERT(nvarchar(20),@_Month) + ',@_Year=' + CONVERT(nvarchar(20),@_Year) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM BELDMS..DMSDistributorAVGSalesOut3LM)
            EXEC dbo.sp_DMS_Baseline_DistributorSellInTarget @_CompanyID, @_Month, @_Year, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM BELDMS..DMSDistributorAVGSalesOut3LM)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_DistributorSellInTarget' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_DistributorSellInTarget failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_DistributorSellInTarget' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_FromDate=' + CONVERT(nvarchar(20),@_FromDate) + ',@_LastDayOfMonth=' + CONVERT(nvarchar(20),@_LastDayOfMonth) + ',@_DistributorID='+Isnull(@_DistributorID,'')
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLDistributorSales)
            EXEC dbo.sp_DMS_Baseline_DistributorSales @_CompanyID, @_FromDate, @_LastDayOfMonth, @_DistributorID
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLDistributorSales)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_DistributorSales' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_DistributorSales failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_DistributorSales' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_Month=' + CONVERT(nvarchar(20),@_Month) + ',@_Year=' + CONVERT(nvarchar(20),@_Year)
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLDistributorSales)
            EXEC dbo.sp_DMS_Baseline_DistributorSystem @_CompanyID, @_Month, @_Year
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLDistributorSales)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_DistributorSystem' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_DistributorSystem failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_DistributorSystem' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_Month=' + CONVERT(nvarchar(20),@_Month) + ',@_Year=' + CONVERT(nvarchar(20),@_Year)
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLDistributorSystem)
            EXEC dbo.sp_DMS_Baseline_DistributorSystem @_CompanyID, @_Month, @_Year
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLDistributorSystem)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_DistributorSystem' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_DistributorSystem failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_DistributorSystem' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch


        BEGIN try
            SET @Parameter = '@_CompanyID = 3,@_Month=' + CONVERT(nvarchar(20),@_Month) + ',@_Year=' + CONVERT(nvarchar(20),@_Year)
            SET @StartTime = getdate();
            SET @StartRowCount = (SELECT COUNT(1) FROM DMSBLDistributorSystemByCustomer)
            EXEC dbo.sp_DMS_Baseline_DistributorSystemByCustomer @_CompanyID, @_Month, @_Year
            SET @EndTime = GETDATE();
            SET @EndRowCount = (SELECT COUNT(1) FROM DMSBLDistributorSystemByCustomer)
            SET @RowCountNumber = @EndRowCount - @StartRowCount
            EXEC dbo.spInsertHistory 'sp_DMS_Baseline_DistributorSystemByCustomer' , @Parameter,  1,'' ,@StartTime , @EndTime,@RowCountNumber
        END try
        BEGIN catch
            IF(ERROR_MESSAGE() IS NOT NULL)
            BEGIN
                SET @Error=ERROR_MESSAGE();
                SET @Message=  @Message+' - store sp_DMS_Baseline_DistributorSystemByCustomer failed: ' +@Error + '<br/>';
                EXEC dbo.spInsertHistory 'sp_DMS_Baseline_DistributorSystemByCustomer' , @Parameter, 0,@Message, @StartTime , @EndTime,@RowCountNumber;
            END
        END catch

        EXEC BELDMS..pp_DMS_L2_SendInforAfterBaseline @CompanyID,@_CheckDate,@_FromDate,@_ToDate


	END
END