USP_MKTSEGMENTATIONACTIVATEKPI_SAVEFIELD

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_SAVEFIELD]
(
  @SEGMENTATIONID uniqueidentifier,
  @KPICATALOGID uniqueidentifier,
  @DEFAULT bit,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @MAILINGTYPECODE tinyint;
  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    declare @KPISLOCKED bit = 0;

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

        select
          @KPISLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[KPISLOCKED]
        from dbo.[MKTCOMMUNICATIONTEMPLATE]
        inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
        where [MKTSEGMENTATIONID] = @SEGMENTATIONID;

      end

    /* Don't allow overwriting of locked KPIs */
    if @KPISLOCKED = 0
      begin

        /* Insert the kpi type for the mailing */
        if not exists (select top 1 1 from dbo.[MKTSEGMENTATIONACTIVATEKPI] where [SEGMENTATIONID] = @SEGMENTATIONID and [KPICATALOGID] = @KPICATALOGID)
          insert into dbo.[MKTSEGMENTATIONACTIVATEKPI] (
            [ID],
            [SEGMENTATIONID],
            [KPICATALOGID],
            [ADDEDBYID],
            [DATEADDED],
            [CHANGEDBYID],
            [DATECHANGED]
          ) values (
            newid(),
            @SEGMENTATIONID,
            @KPICATALOGID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CHANGEAGENTID,
            @CURRENTDATE
          );

        if @DEFAULT = 1
          begin
            select @MAILINGTYPECODE = [MAILINGTYPECODE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;

            if not exists (select top 1 1 from dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI] where [MAILINGTYPECODE] = @MAILINGTYPECODE and [KPICATALOGID] = @KPICATALOGID)
              insert into dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI] (
                [ID],
                [MAILINGTYPECODE],
                [KPICATALOGID],
                [ADDEDBYID],
                [DATEADDED],
                [CHANGEDBYID],
                [DATECHANGED]
              ) values (
                newid(),
                @MAILINGTYPECODE,
                @KPICATALOGID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CHANGEAGENTID,
                @CURRENTDATE
              );
          end

      end

  end try

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

  return 0;