USP_MERGETASK_CONSTITUENTALIASES

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

                    declare @CHANGEDATE datetime = getdate();

                    if @PRESERVECRITERIA = 0
                        -- Move the source's aliases to the 
                        -- target unless the target already has
                        -- a name format of that type.
                        update dbo.ALIAS
                        set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        where CONSTITUENTID = @SOURCEID
                        and ID not in
                        (
                            select b.ID
                            from dbo.ALIAS a
                            inner join dbo.ALIAS b
                            on a.KEYNAME = b.KEYNAME
                            and a.FIRSTNAME = b.FIRSTNAME
                            and a.MIDDLENAME = b.MIDDLENAME
                            and (a.TITLECODEID = b.TITLECODEID or (a.TITLECODEID is null and b.TITLECODEID is null))
                            and (a.TITLE2CODEID = b.TITLE2CODEID or (a.TITLE2CODEID is null and b.TITLE2CODEID is null))
                            and (a.SUFFIXCODEID = b.SUFFIXCODEID or (a.SUFFIXCODEID is null and b.SUFFIXCODEID is null))
                            and (a.SUFFIX2CODEID = b.SUFFIX2CODEID or (a.SUFFIX2CODEID is null and b.SUFFIX2CODEID is null))
                            where a.CONSTITUENTID = @TARGETID
                            and b.CONSTITUENTID = @SOURCEID
                        );
                    else
                    begin
                        -- Delete the target's aliases where the target
                        -- and source have aliases of the same type
                        delete from dbo.ALIAS
                        where ID in
                        (
                            select a.ID
                            from dbo.ALIAS a
                            inner join dbo.ALIAS b
                            on a.KEYNAME = b.KEYNAME
                            and a.FIRSTNAME = b.FIRSTNAME
                            and a.MIDDLENAME = b.MIDDLENAME
                            and (a.TITLECODEID = b.TITLECODEID or (a.TITLECODEID is null and b.TITLECODEID is null))
                            and (a.TITLE2CODEID = b.TITLE2CODEID or (a.TITLE2CODEID is null and b.TITLE2CODEID is null))
                            and (a.SUFFIXCODEID = b.SUFFIXCODEID or (a.SUFFIXCODEID is null and b.SUFFIXCODEID is null))
                            and (a.SUFFIX2CODEID = b.SUFFIX2CODEID or (a.SUFFIX2CODEID is null and b.SUFFIX2CODEID is null))
                            where a.CONSTITUENTID = @TARGETID
                            and b.CONSTITUENTID = @SOURCEID
                        );

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

                    return 0;