USP_MKTSEGMENTATIONACTIVATEKPI_SAVEFIELD_FROMXML

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_SAVEFIELD_FROMXML]
(
  @SEGMENTATIONID uniqueidentifier,
  @KPICATALOGIDS xml,
  @DEFAULT bit,
  @CHANGEAGENTID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @MAILINGTYPECODE tinyint;
  declare @KPIS table ([KPICATALOGID] uniqueidentifier);
  declare @ID uniqueidentifier;
  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    insert into @KPIS
    select
      T.c.value('(KPICATALOGID)[1]', 'uniqueidentifier') as [KPICATALOGID]
    from @KPICATALOGIDS.nodes('/ACTIVATIONKPIS/ITEM') T(c);

    -- insert selected KPIs that aren't already selected

    insert into dbo.[MKTSEGMENTATIONACTIVATEKPI]
    (
      [ID],
      [SEGMENTATIONID],
      [KPICATALOGID],
      [ADDEDBYID],
      [DATEADDED],
      [CHANGEDBYID],
      [DATECHANGED]
    )
    select
      newid(),
      @SEGMENTATIONID,
      [I].[KPICATALOGID],
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CHANGEAGENTID,
      @CURRENTDATE
    from @KPIS as [I]
    where not exists (select top 1 1 from dbo.[MKTSEGMENTATIONACTIVATEKPI] as [K]
                      where [K].[SEGMENTATIONID] = @SEGMENTATIONID
                      and [K].[KPICATALOGID] = [I].[KPICATALOGID]);

    -- delete KPIs that are no longer selected

    declare DELETECURSOR cursor local fast_forward for
      select [K].[ID]
      from dbo.[MKTSEGMENTATIONACTIVATEKPI] as [K]
      where [K].[SEGMENTATIONID] = @SEGMENTATIONID
      and not exists (select top 1 1 from @KPIS as [D]
                      where [K].[KPICATALOGID] = [D].[KPICATALOGID])
      and (@CURRENTAPPUSERID is null or dbo.[UFN_SECURITY_APPUSER_GRANTED_KPI](@CURRENTAPPUSERID, [K].[KPICATALOGID]) = 1);

    open DELETECURSOR;
    fetch next from DELETECURSOR into @ID;

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

    close DELETECURSOR;
    deallocate DELETECURSOR;

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

        -- insert selected KPIs that aren't already defaults

        insert into dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI]
        (
          [ID],
          [MAILINGTYPECODE],
          [KPICATALOGID],
          [ADDEDBYID],
          [DATEADDED],
          [CHANGEDBYID],
          [DATECHANGED]
        )
        select
          newid(),
          @MAILINGTYPECODE,
          [I].[KPICATALOGID],
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CHANGEAGENTID,
          @CURRENTDATE
        from @KPIS as [I]
        where not exists (select top 1 1 from dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI] as [K]
                          where [K].[MAILINGTYPECODE] = @MAILINGTYPECODE
                          and [K].[KPICATALOGID] = [I].[KPICATALOGID])
        and (@CURRENTAPPUSERID is null or dbo.[UFN_SECURITY_APPUSER_GRANTED_KPI](@CURRENTAPPUSERID, [I].[KPICATALOGID]) = 1);

        -- delete KPIs that are no longer defaults

        declare DELETECURSOR cursor local fast_forward for
          select [K].[ID]
          from dbo.[MKTSEGMENTATIONACTIVATEDEFAULTKPI] as [K]
          where [K].[MAILINGTYPECODE] = @MAILINGTYPECODE
          and not exists (select top 1 1 from @KPIS as [D]
                          where [K].[KPICATALOGID] = [D].[KPICATALOGID])
          and (@CURRENTAPPUSERID is null or dbo.[UFN_SECURITY_APPUSER_GRANTED_KPI](@CURRENTAPPUSERID, [K].[KPICATALOGID]) = 1);

        open DELETECURSOR;
        fetch next from DELETECURSOR into @ID;

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

        close DELETECURSOR;
        deallocate DELETECURSOR;
      end
  end try

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

  return 0;