USP_MKTSEGMENTATIONACTIVATE_SAVEFIELD_FROMXML

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_SAVEFIELD_FROMXML]
(
  @SEGMENTATIONID uniqueidentifier,    
  @APPEALINFORMATION xml,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @ACTIVATEID 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.[MKTSEGMENTATIONACTIVATE] 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.[MKTSEGMENTATIONACTIVATE] as [SA]
    inner join @APPEALINFORMATION.nodes('/APPEALINFORMATION/ITEM') T(c) on T.c.value('(RECORDSOURCEID)[1]','uniqueidentifier') = [SA].[RECORDSOURCEID]
    where [SA].[SEGMENTATIONID] = @SEGMENTATIONID;


    --Insert any new appeal information...

    insert into dbo.[MKTSEGMENTATIONACTIVATE] (
      [ID],
      [SEGMENTATIONID],
      [RECORDSOURCEID],
      [NORMALGIFTIDSETREGISTERID],
      [UNRESOLVEDGIFTIDSETREGISTERID],
      [APPEALSYSTEMID],
      [APPEALID],
      [APPEALDESCRIPTION],
      [ADDEDBYID],
      [DATEADDED],
      [CHANGEDBYID],
      [DATECHANGED]
    )
    select
      newid(),
      @SEGMENTATIONID,
      T.c.value('(RECORDSOURCEID)[1]','uniqueidentifier') as [RECORDSOURCEID],
      null,
      null,
      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.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = T.c.value('(RECORDSOURCEID)[1]','uniqueidentifier')
    where [MKTSEGMENTATIONACTIVATE].[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.[MKTSEGMENTATIONACTIVATE]
      where [SEGMENTATIONID] = @SEGMENTATIONID
      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 @ACTIVATEID;

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

    close DELETECURSOR;
    deallocate DELETECURSOR;
  end try

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

  return 0;