USP_MKTSEGMENTATIONACTIVATEKPI_CLEARFIELDS

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

  declare @MAILINGTYPECODE tinyint;
  declare @ID uniqueidentifier;
  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 clearing of locked KPIs */
    if @KPISLOCKED = 0
      begin
        --Deleting by cursor so the audit tables show correctly...

        declare KPICURSOR cursor local fast_forward for
          select [ID]
          from dbo.[MKTSEGMENTATIONACTIVATEKPI]
          where [SEGMENTATIONID] = @SEGMENTATIONID;

        open KPICURSOR;
        fetch next from KPICURSOR into @ID;

        while (@@FETCH_STATUS = 0)
          begin
            exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
            fetch next from KPICURSOR into @ID;
          end

        close KPICURSOR;
        deallocate KPICURSOR;

        --Reset all defaults if needed...

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

            declare DEFAULTCURSOR cursor local fast_forward for
              select [ID]
              from dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI]
              where [MAILINGTYPECODE] = @MAILINGTYPECODE;

            open DEFAULTCURSOR;
            fetch next from DEFAULTCURSOR into @ID;

            while (@@FETCH_STATUS = 0)
              begin
                exec dbo.[USP_MKTSEGMENTATIONACTIVATEDEFAULTKPI_DELETEBYID_WITHCHANGEAGENTID] @ID, @CHANGEAGENTID;
                fetch next from DEFAULTCURSOR into @ID;
              end

            close DEFAULTCURSOR;
            deallocate DEFAULTCURSOR;
          end
      end

  end try

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

  return 0;