USP_MKTSEGMENTATIONACTIVATE_SAVEFIELD

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_SAVEFIELD]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,    
  @RECORDSOURCEID uniqueidentifier,
  @NORMALGIFTIDSETREGISTERID uniqueidentifier,
  @UNRESOLVEDGIFTIDSETREGISTERID uniqueidentifier,
  @APPEALSYSTEMID nvarchar(36),
  @APPEALID nvarchar(100),
  @APPEALDESCRIPTION nvarchar(255)
)
as
  set nocount on;

  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    if @APPEALSYSTEMID is null
      set @APPEALSYSTEMID = '';
    if @APPEALID is null
      set @APPEALID = '';
    if @APPEALDESCRIPTION is null
      set @APPEALDESCRIPTION = '';

    declare @APPEALLOCKED bit = 0;

    /* Get template locked appeal settings */  
    if exists(select top 1 1 from dbo.[MKTCOMMUNICATIONTEMPLATE] where [MKTSEGMENTATIONID] = @SEGMENTATIONID)
      begin

        select
          @APPEALLOCKED = [MKTCOMMUNICATIONTEMPLATEDEFAULT].[APPEALLOCKED]
        from dbo.[MKTCOMMUNICATIONTEMPLATE]
        inner join dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT] on [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]
        where [MKTSEGMENTATIONID] = @SEGMENTATIONID;

      end

    /* Insert or update the appeal values for the record source */
    if exists(select [ID] from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = @SEGMENTATIONID and [RECORDSOURCEID] = @RECORDSOURCEID)
      update dbo.[MKTSEGMENTATIONACTIVATE] set
        [NORMALGIFTIDSETREGISTERID] = @NORMALGIFTIDSETREGISTERID,
        [UNRESOLVEDGIFTIDSETREGISTERID] = @UNRESOLVEDGIFTIDSETREGISTERID,
        [APPEALSYSTEMID] = case when @APPEALLOCKED = 1 then [APPEALSYSTEMID] else @APPEALSYSTEMID end,
        [APPEALID] = case when @APPEALLOCKED = 1 then [APPEALID] else @APPEALID end,
        [APPEALDESCRIPTION] = case when @APPEALLOCKED = 1 then [APPEALDESCRIPTION] else @APPEALDESCRIPTION end,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [SEGMENTATIONID] = @SEGMENTATIONID 
      and [RECORDSOURCEID] = @RECORDSOURCEID;
    else
      insert into dbo.[MKTSEGMENTATIONACTIVATE] (
        [ID],
        [SEGMENTATIONID],
        [RECORDSOURCEID],
        [NORMALGIFTIDSETREGISTERID],
        [UNRESOLVEDGIFTIDSETREGISTERID],
        [APPEALSYSTEMID],
        [APPEALID],
        [APPEALDESCRIPTION],
        [ADDEDBYID],
        [DATEADDED],
        [CHANGEDBYID],
        [DATECHANGED]
      ) values (
        newid(),
        @SEGMENTATIONID,
        @RECORDSOURCEID,
        @NORMALGIFTIDSETREGISTERID,
        @UNRESOLVEDGIFTIDSETREGISTERID,
        @APPEALSYSTEMID,
        @APPEALID,
        @APPEALDESCRIPTION,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CHANGEAGENTID,
        @CURRENTDATE
      );
  end try

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

  return 0;