USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGHYBRIDACTIVATE_2

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGHYBRIDACTIVATE_2]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @EMAILPROCESSINGTYPE tinyint,
  @CREATEOUTPUTIDSET bit,
  @OUTPUTIDSETNAME nvarchar(100),
  @OVERWRITEOUTPUTIDSET bit,
  @EMAILDATETIME datetime,
  @LETTEROPTIONS xml
)
as
  set nocount on;

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

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate();

  declare @SEGMENTATIONID uniqueidentifier;
  declare @HASEMAIL bit = 0;

  select
    @SEGMENTATIONID = [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID],
    @HASEMAIL = case when exists(select ID from dbo.COMMUNICATIONLETTER where COMMUNICATIONLETTER.SEGMENTATIONID = MKTSEGMENTATIONACTIVATEPROCESS.SEGMENTATIONID and COMMUNICATIONLETTER.CHANNELCODE > 0) then 1 else 0 end
  from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
  where [MKTSEGMENTATIONACTIVATEPROCESS].[ID] = @ID;

  begin try

    exec dbo.USP_SELECTIVECOMMUNICATIONLETTERS_GETLETTEROPTIONS_UPDATEFROMXML @SEGMENTATIONID, @LETTEROPTIONS, @CHANGEAGENTID, @CURRENTDATE;

    update dbo.[APPEALMAILING] set
      [CREATEOUTPUTIDSET] = @CREATEOUTPUTIDSET,
      [OUTPUTIDSETNAME] = @OUTPUTIDSETNAME,
      [OVERWRITEOUTPUTIDSET] = @OVERWRITEOUTPUTIDSET,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @SEGMENTATIONID;

    -- OutputIDSet information is now stored on the segmentation table, but keep both tables up-to-date for backwards compatibility...
    update dbo.[MKTSEGMENTATION] set
      [CREATEOUTPUTIDSET] = @CREATEOUTPUTIDSET,
      [OUTPUTIDSETNAME] = @OUTPUTIDSETNAME,
      [OVERWRITEOUTPUTIDSET] = @OVERWRITEOUTPUTIDSET,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @SEGMENTATIONID;

    if @HASEMAIL = 1
        begin
            if exists (select [ID] from dbo.[APPEALMAILINGACTIVATEEMAILJOBSCHEDULE] where [ID] = @ID)                
                update dbo.[APPEALMAILINGACTIVATEEMAILJOBSCHEDULE] set
                    [SCHEDULETYPECODE] = @EMAILPROCESSINGTYPE,
                    [EMAILJOBSTART] = @EMAILDATETIME,
                    [EMAILJOBSTARTWITHOFFSET] = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@EMAILDATETIME, 0),
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                where ID = @ID;                

            else
                insert into dbo.[APPEALMAILINGACTIVATEEMAILJOBSCHEDULE]
                (
                    [ID],
                    [SCHEDULETYPECODE],
                    [EMAILJOBSTART],
                    [EMAILJOBSTARTWITHOFFSET],
                    [ADDEDBYID],
                    [CHANGEDBYID],
                    [DATEADDED],
                    [DATECHANGED]
                ) values (
                    @ID,
                    @EMAILPROCESSINGTYPE,
                    @EMAILDATETIME,
                    dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@EMAILDATETIME, 0),
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                );
        end
    else
        delete from dbo.[APPEALMAILINGACTIVATEEMAILJOBSCHEDULE] where ID = @ID;                

  end try

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

  return 0;