USP_DATAFORMTEMPLATE_EDIT_MKTCOMMUNICATIONEFFORTACTIVATIONSETTINGS

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTCOMMUNICATIONEFFORTACTIVATIONSETTINGS]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @RUNSEGMENTATIONSEGMENTREFRESHPROCESS bit,
  @RUNMARKETINGEXCLUSIONSREPORT bit,
  @CACHESOURCEANALYSISRULEDATA bit,
  @RUNSEGMENTATIONSEGMENTCALCULATEPROCESS bit,
  @RUNSEGMENTATIONSEGMENTREFRESHPROCESSACTIVATE bit,
  @RUNMARKETINGEXCLUSIONSREPORTACTIVATE bit,
  @CACHESOURCEANALYSISRULEDATAACTIVATE bit,
  @EXPORTAFTERACTIVATE bit
)
as

  set nocount on;

  if @CHANGEAGENTID is null  
    exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

  declare @CURRENTDATE datetime = getdate();
  declare @SEGMENTATIONSEGMENTCALCULATEPROCESSID uniqueidentifier;
  declare @SEGMENTATIONACTIVATEPROCESSID uniqueidentifier;
  declare @RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED bit = 0;
  declare @RUNMARKETINGEXCLUSIONSREPORTLOCKED bit = 0;
  declare @CACHESOURCEANALYSISRULEDATALOCKED bit = 0;
  declare @ACTIVATEOPTIONSLOCKED bit = 0;
  declare @EXPORTAFTERACTIVATELOCKED bit = 0;
  declare @CACHESOURCEANALYSISRULEDATAACTIVATELOCKED bit = 0;

  begin try

    /* Get template locked settings */  
    if exists(select top 1 1 from dbo.[MKTCOMMUNICATIONTEMPLATE] where [MKTSEGMENTATIONID] = @ID)
      begin

        select
          @RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED],
          @RUNMARKETINGEXCLUSIONSREPORTLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[RUNMARKETINGEXCLUSIONSREPORTLOCKED],
          @CACHESOURCEANALYSISRULEDATALOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATALOCKED],
          @ACTIVATEOPTIONSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ACTIVATEOPTIONSLOCKED],
          @EXPORTAFTERACTIVATELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXPORTAFTERACTIVATELOCKED],
          @CACHESOURCEANALYSISRULEDATAACTIVATELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CACHESOURCEANALYSISRULEDATAACTIVATELOCKED]
        from dbo.[MKTCOMMUNICATIONTEMPLATE]
        inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
        where [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = @ID;

      end

    /* Get process IDs */
    select 
      @SEGMENTATIONSEGMENTCALCULATEPROCESSID = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID],
      @SEGMENTATIONACTIVATEPROCESSID = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
    from dbo.[MKTSEGMENTATION]
    inner join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    where [MKTSEGMENTATION].[ID] = @ID;

    /* Update counts settings */
    update [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
    set
      [RUNSEGMENTATIONSEGMENTREFRESHPROCESS] = case when @RUNSEGMENTATIONSEGMENTREFRESHPROCESSLOCKED = 1 then [RUNSEGMENTATIONSEGMENTREFRESHPROCESS] else @RUNSEGMENTATIONSEGMENTREFRESHPROCESS end,
      [RUNMARKETINGEXCLUSIONSREPORT] = case when @RUNMARKETINGEXCLUSIONSREPORTLOCKED = 1 then [RUNMARKETINGEXCLUSIONSREPORT] else @RUNMARKETINGEXCLUSIONSREPORT end,
      [CACHESOURCEANALYSISRULEDATA] = case when @CACHESOURCEANALYSISRULEDATALOCKED = 1 then [CACHESOURCEANALYSISRULEDATA] else @CACHESOURCEANALYSISRULEDATA end,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @SEGMENTATIONSEGMENTCALCULATEPROCESSID;

    /* Update activation settings */
    update [MKTSEGMENTATIONACTIVATEPROCESS]
    set
      [RUNSEGMENTATIONSEGMENTCALCULATEPROCESS] = case when @ACTIVATEOPTIONSLOCKED = 1 then [RUNSEGMENTATIONSEGMENTCALCULATEPROCESS] else @RUNSEGMENTATIONSEGMENTCALCULATEPROCESS end,
      [RUNSEGMENTATIONSEGMENTREFRESHPROCESS] = case when @ACTIVATEOPTIONSLOCKED = 1 then [RUNSEGMENTATIONSEGMENTREFRESHPROCESS] else @RUNSEGMENTATIONSEGMENTREFRESHPROCESSACTIVATE end,
      [RUNMARKETINGEXCLUSIONSREPORT] = case when @ACTIVATEOPTIONSLOCKED = 1 then [RUNMARKETINGEXCLUSIONSREPORT] else @RUNMARKETINGEXCLUSIONSREPORTACTIVATE end,
      [CACHESOURCEANALYSISRULEDATA] = case when @CACHESOURCEANALYSISRULEDATAACTIVATELOCKED = 1 then [CACHESOURCEANALYSISRULEDATA] else @CACHESOURCEANALYSISRULEDATAACTIVATE end,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @SEGMENTATIONACTIVATEPROCESSID;    

    /* Update export settings */
    update [MKTSEGMENTATION]
    set
      [RUNACTIVATEANDEXPORT] = case when @EXPORTAFTERACTIVATELOCKED = 1 then [RUNACTIVATEANDEXPORT] else @EXPORTAFTERACTIVATE end,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;   

  end try
  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

return 0;