USP_MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL_SAVEFIELD_FROMXML

create procedure dbo.[USP_MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL_SAVEFIELD_FROMXML]
(
  @COMMUNICATIONTEMPLATEID uniqueidentifier,    
  @APPEALINFORMATION xml,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @APPEALDEFAULTID uniqueidentifier;
  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    --Update any existing appeal information, making sure not to overwrite the gift IDSet IDs...

    update dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL] set
      [APPEALSYSTEMID] = isnull(T.c.value('(APPEALSYSTEMID)[1]','nvarchar(36)'), ''),
      [APPEALID] = T.c.value('(APPEALID)[1]','nvarchar(100)'),
      [APPEALDESCRIPTION] = T.c.value('(APPEALDESCRIPTION)[1]','nvarchar(255)'),
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL]
    inner join @APPEALINFORMATION.nodes('/APPEALINFORMATION/ITEM') T(c) on T.c.value('(RECORDSOURCEID)[1]','uniqueidentifier') = [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[RECORDSOURCEID]
    where [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[COMMUNICATIONTEMPLATEID] = @COMMUNICATIONTEMPLATEID;


    --Insert any new appeal information...

    insert into dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL] (
      [ID],
      [COMMUNICATIONTEMPLATEID],
      [RECORDSOURCEID],
      [APPEALSYSTEMID],
      [APPEALID],
      [APPEALDESCRIPTION],
      [ADDEDBYID],
      [DATEADDED],
      [CHANGEDBYID],
      [DATECHANGED]
    )
    select
      newid(),
      @COMMUNICATIONTEMPLATEID,
      T.c.value('(RECORDSOURCEID)[1]','uniqueidentifier') as [RECORDSOURCEID],
      isnull(T.c.value('(APPEALSYSTEMID)[1]','nvarchar(36)'), '') as [APPEALSYSTEMID],
      T.c.value('(APPEALID)[1]','nvarchar(100)') as [APPEALID],
      T.c.value('(APPEALDESCRIPTION)[1]','nvarchar(255)') as [APPEALDESCRIPTION],
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CHANGEAGENTID,
      @CURRENTDATE
    from @APPEALINFORMATION.nodes('/APPEALINFORMATION/ITEM') T(c)
    left join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL] on [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[COMMUNICATIONTEMPLATEID] = @COMMUNICATIONTEMPLATEID 
      and [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[RECORDSOURCEID] = T.c.value('(RECORDSOURCEID)[1]','uniqueidentifier')
    where [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[ID] is null;


    --Delete any rows that are in the table but not in the XML variable...

    declare DELETECURSOR cursor local fast_forward for
      select [ID]
      from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL]
      where [COMMUNICATIONTEMPLATEID] = @COMMUNICATIONTEMPLATEID
      and [RECORDSOURCEID] not in (select T.c.value('(RECORDSOURCEID)[1]','uniqueidentifier') from @APPEALINFORMATION.nodes('/APPEALINFORMATION/ITEM') T(c));

    open DELETECURSOR;
    fetch next from DELETECURSOR into @APPEALDEFAULTID;

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

    close DELETECURSOR;
    deallocate DELETECURSOR;
  end try

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

  return 0;