USP_DATAFORMTEMPLATE_EDIT_SELECTIVECOMMUNICATIONLETTERPREPROCESS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SELECTIVECOMMUNICATIONLETTERPREPROCESS (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @LETTEROPTIONS xml,
    @EMAILPROCESSINGTYPE tinyint,
    @EMAILDATETIME datetime
)
as
    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime = getdate();

    begin try

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

        select
            @SEGMENTATIONID = [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID]
        from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
        where [MKTSEGMENTATIONACTIVATEPROCESS].[ID] = @ID;

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

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

        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;