USP_MERGETASK_CONSTITUENTNAMEFORMATS

                CREATE procedure dbo.USP_MERGETASK_CONSTITUENTNAMEFORMATS
                (
                    @SOURCEID uniqueidentifier,
                    @TARGETID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier,
                    @PRESERVECRITERIA int = 0
                )
                as
                    set nocount on;

                    declare @CHANGEDATE datetime = getdate();

                    --Cache out primary addressee and salutation
                    declare @TARGETPRIMARYADDRESSEE uniqueidentifier;
                    declare @TARGETPRIMARYSALUTATION uniqueidentifier;
                    declare @NEWCONSTITID uniqueidentifier;

                    select @TARGETPRIMARYADDRESSEE = ID 
                    from dbo.NAMEFORMAT
                    where CONSTITUENTID = @SOURCEID and PRIMARYADDRESSEE = 1;

                    select @TARGETPRIMARYSALUTATION = ID 
                    from dbo.NAMEFORMAT
                    where CONSTITUENTID = @SOURCEID and PRIMARYSALUTATION = 1;

                    update dbo.NAMEFORMAT
                    set PRIMARYADDRESSEE = 0, 
                        PRIMARYSALUTATION = 0, 
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CHANGEDATE
                    where CONSTITUENTID = @SOURCEID
                        and (PRIMARYADDRESSEE = 1
                            or PRIMARYSALUTATION = 1);

                    --Perform merge

                    if @PRESERVECRITERIA = 0
                        -- Move the source's name formats to the 
                        -- target unless the target already has
                        -- a name format of that type.
                        update dbo.NAMEFORMAT
                        set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        where CONSTITUENTID = @SOURCEID
                        and ID not in
                        (
                            select b.ID
                            from dbo.NAMEFORMAT a
                            inner join dbo.NAMEFORMAT b
                            on a.NAMEFORMATTYPECODEID = b.NAMEFORMATTYPECODEID
                            where a.CONSTITUENTID = @TARGETID
                            and b.CONSTITUENTID = @SOURCEID
                        );
                    else
                    begin
                        -- Delete the target's name formats where the target
                        -- and source have name formats of the same type
                        delete from dbo.NAMEFORMAT
                        where ID in
                        (
                            select a.ID
                            from dbo.NAMEFORMAT a
                            inner join dbo.NAMEFORMAT b
                            on a.NAMEFORMATTYPECODEID = b.NAMEFORMATTYPECODEID
                            where a.CONSTITUENTID = @TARGETID
                            and b.CONSTITUENTID = @SOURCEID
                        );

                        -- Then move all of the source's name formats
                        -- to the target
                        update dbo.NAMEFORMAT
                        set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        where CONSTITUENTID = @SOURCEID;
                    end

                    --check if we need to reset primary addressee
                    if @TARGETPRIMARYADDRESSEE is not null
                    begin
                        --see if name format moved
                        select @NEWCONSTITID = CONSTITUENTID 
                        from dbo.NAMEFORMAT
                        where ID = @TARGETPRIMARYADDRESSEE;

                        update dbo.NAMEFORMAT
                        set PRIMARYADDRESSEE = 1, 
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                        where ID = @TARGETPRIMARYADDRESSEE
                            and not exists 
                                    (select top 1 ID 
                                        from dbo.NAMEFORMAT
                                        where CONSTITUENTID = @NEWCONSTITID and PRIMARYADDRESSEE = 1)

                    end


                    --check if we need to reset primary salutation
                    if @TARGETPRIMARYSALUTATION is not null
                    begin
                        --see if name format moved
                        select @NEWCONSTITID = CONSTITUENTID 
                        from dbo.NAMEFORMAT
                        where ID = @TARGETPRIMARYSALUTATION;

                        update dbo.NAMEFORMAT
                        set PRIMARYSALUTATION = 1, 
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                        where ID = @TARGETPRIMARYSALUTATION
                            and not exists 
                                    (select top 1 ID 
                                        from dbo.NAMEFORMAT
                                        where CONSTITUENTID = @NEWCONSTITID and PRIMARYSALUTATION = 1)

                    end

                    return 0;