USP_DATAFORMTEMPLATE_EDIT_SIMPLEACKNOWLEDGEMENT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SIMPLEACKNOWLEDGEMENT
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier,
    @NAMEFORMATPARAMETERID nvarchar(100),
    @ADDRESSPROCESSINGOPTIONID nvarchar(100),
    @EXCLUSIONS xml,
    @FREQUENCY int,
    @INCLUDERECIPIENTS bit,
    @LETTERS xml = null
)
as
    begin
        declare @CURRENTDATE datetime = getdate();

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

            if @LETTERS is not null        
                exec dbo.USP_COMMUNICATIONLETTER_GETSCHEDULEINFO_UPDATEFROMXML @ID, @LETTERS, @CHANGEAGENTID, @CURRENTDATE;

            update dbo.MKTSEGMENTATION set
                NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID,
                ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @ID;

            if exists(select ID from dbo.SIMPLEACKNOWLEDGEMENT where ID = @ID)                 
                update dbo.SIMPLEACKNOWLEDGEMENT set
                    INCLUDERECIPIENTS = @INCLUDERECIPIENTS,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @ID;
            else        
                insert into dbo.SIMPLEACKNOWLEDGEMENT (ID, INCLUDERECIPIENTS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values
                    (@ID, @INCLUDERECIPIENTS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

            exec dbo.USP_SIMPLEACKNOWLEDGEMENT_GETSOLICITCODES_UPDATEFROMXML @ID, @EXCLUSIONS, @CHANGEAGENTID, @CURRENTDATE;

            declare @SEGMENTATIONACTIVATEPROCESSID uniqueidentifier = (select top 1 ID from dbo.MKTSEGMENTATIONACTIVATEPROCESS where SEGMENTATIONID = @ID);

            exec dbo.[USP_BUSINESSPROCESSCOMMPREF_UPDATE]
                @CHANGEAGENTID = @CHANGEAGENTID,
                @BUSINESSPROCESSCATALOGID = '116332AF-BB79-4608-9709-4203BD2BA318',
                @BUSINESSPROCESSPARAMETERSETID = @SEGMENTATIONACTIVATEPROCESSID,
                @EXCLUSIONDATETYPECODE = 0,
                @EXCLUSIONASOFDATE = null,
                @EXCLUDEDECEASED = 1,
                @EXCLUDEINACTIVE = 1,
                @EXCLUSIONS = @EXCLUSIONS,
                @CURRENTAPPUSERID = @CURRENTAPPUSERID;
        end try

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

        return 0;
    end