USP_DATAFORMTEMPLATE_EDIT_MKTCOMMUNICATIONEFFORTCONTACTRULEEXCLUSIONS

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTCOMMUNICATIONEFFORTCONTACTRULEEXCLUSIONS]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @EXCLUDEDECEASED bit,
  @EXCLUDEINACTIVE bit,
  @EXCLUSIONS xml,
  @EXCLUSIONDATETYPECODE tinyint,
  @EXCLUSIONASOFDATE datetime
)
as

  set nocount on;

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

  declare @CURRENTDATE datetime = getdate();
  declare @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier;
  declare @EXCLUDEDECEASEDLOCKED bit = 0;
  declare @EXCLUDEINACTIVELOCKED bit = 0;
  declare @EXCLUSIONSLOCKED bit = 0;
  declare @EXCLUSIONASOFDATELOCKED bit = 0;

  begin try

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

        select
          @EXCLUDEDECEASEDLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEDECEASEDLOCKED],
          @EXCLUDEINACTIVELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUDEINACTIVELOCKED],
          @EXCLUSIONSLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONSLOCKED],
          @EXCLUSIONASOFDATELOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[EXCLUSIONASOFDATELOCKED]
        from dbo.[MKTCOMMUNICATIONTEMPLATE]
        inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
        where [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] = @ID;

      end

    /* Get mailing activate process ID */
    select 
      @MKTSEGMENTATIONACTIVATEPROCESSID = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
    from dbo.[MKTSEGMENTATION]
    inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    where [MKTSEGMENTATION].[ID] = @ID;

    update 
      dbo.[BUSINESSPROCESSCOMMPREF]
    set
      [EXCLUDEDECEASED] = case when @EXCLUDEDECEASEDLOCKED = 1 then [EXCLUDEDECEASED] else @EXCLUDEDECEASED end,
      [EXCLUDEINACTIVE] = case when @EXCLUDEINACTIVELOCKED = 1 then [EXCLUDEINACTIVE] else @EXCLUDEINACTIVE end,
      [DATETYPECODE] = case when @EXCLUSIONASOFDATELOCKED = 1 then [DATETYPECODE] else @EXCLUSIONDATETYPECODE end,
      [ASOFDATE] = case when @EXCLUSIONASOFDATELOCKED = 1 then [ASOFDATE] else @EXCLUSIONASOFDATE end,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [BUSINESSPROCESSPARAMETERSETID] = @MKTSEGMENTATIONACTIVATEPROCESSID;

    if @EXCLUSIONSLOCKED = 0
    begin
      -- Bug 543524: Remove existing duplicate solicit code exclusions to avoid duplicates from required codes

      delete from
        dbo.[BUSINESSPROCESSCOMMPREFEXCLUSIONS]
      where
        [BUSINESSPROCESSCOMMPREFEXCLUSIONS].[SOLICITCODEID]
          in (select
                X.Q.value('@SOLICITCODEID', 'uniqueidentifier')
              from
                @EXCLUSIONS.nodes('/EXCLUSIONS/ITEM') X(Q))
      and
        [BUSINESSPROCESSPARAMETERSETID] = @MKTSEGMENTATIONACTIVATEPROCESSID;

      exec dbo.[USP_MKTSEGMENTATION_GETREQUIREDANDSELECTEDSOLICITCODEEXCLUSIONS_FROMTEMPLATE_UPDATEFROMXML] @MKTSEGMENTATIONACTIVATEPROCESSID, @EXCLUSIONS, @CHANGEAGENTID, @CURRENTDATE;
    end

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

return 0;