USP_MKTCOMMUNICATIONTEMPLATEDEFAULTKPI_SAVEFIELD_FROMXML

CREATE procedure dbo.[USP_MKTCOMMUNICATIONTEMPLATEDEFAULTKPI_SAVEFIELD_FROMXML]
(
  @COMMUNICATIONTEMPLATEID uniqueidentifier,
  @KPICATALOGIDS xml,
  @CHANGEAGENTID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @TEMPLATETYPECODE tinyint;
  declare @KPIS table ([KPICATALOGID] uniqueidentifier, [LOCKED] bit);
  declare @ID uniqueidentifier;
  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    select @TEMPLATETYPECODE = [TEMPLATETYPECODE] from dbo.[MKTCOMMUNICATIONTEMPLATE] where [ID] = @COMMUNICATIONTEMPLATEID;

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

    if @TEMPLATETYPECODE <> 2
      and exists (select top 1 1
                  from @KPIS as [KPIS]
                  inner join dbo.[KPICATALOG] on [KPICATALOG].[ID] = [KPIS].[KPICATALOGID]
                  where isnull([KPICATALOG].[SPECXML].value(
                    'declare namespace bbafx="bb_appfx_kpi";
                    declare namespace c="bb_appfx_commontypes";
                    (bbafx:KpiSpec/c:MetaTags/@MarketingMembershipMailingKPI)[1]', 'tinyint')
                  , 0) = 1)
      begin
        raiserror('BBERR_MKTCOMMUNICATIONTEMPLATEDEFAULTKPI_INVALIDKPI', 13, 1);
        return 1;
      end

    -- update existing KPIs

    update dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTKPI] set
      [LOCKED] = [KPIS].[LOCKED],
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTKPI]
    inner join @KPIS as [KPIS] on [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[KPICATALOGID] = [KPIS].[KPICATALOGID] 
                              and [MKTCOMMUNICATIONTEMPLATEDEFAULTKPI].[COMMUNICATIONTEMPLATEID] = @COMMUNICATIONTEMPLATEID;

    -- insert KPIs that do not already exist

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

    -- delete KPIs that no longer exist

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

    open DELETECURSOR;
    fetch next from DELETECURSOR into @ID;

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

    close DELETECURSOR;
    deallocate DELETECURSOR;
  end try

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

  return 0;