USP_MERGETASK_CONSTITUENTALTERNATELOOKUPIDS

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

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

                    if @PRESERVECRITERIA = 0
                        /* Move the source's alternate lookup IDs to the target unless the target already has a lookup ID of that type. */
                        update 
                            dbo.ALTERNATELOOKUPID
                        set 
                            ALTERNATELOOKUPID.CONSTITUENTID = @TARGETID, 
                            ALTERNATELOOKUPID.CHANGEDBYID = @CHANGEAGENTID,
                            ALTERNATELOOKUPID.DATECHANGED = @CURRENTDATE
                        where 
                            ALTERNATELOOKUPID.CONSTITUENTID = @SOURCEID and 
                            ALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID not in
                            (
                                select 
                                    TARGETLOOKUPIDS.ALTERNATELOOKUPIDTYPECODEID
                                from 
                                    dbo.ALTERNATELOOKUPID as TARGETLOOKUPIDS
                                where 
                                    TARGETLOOKUPIDS.CONSTITUENTID = @TARGETID
                            );
                    else if @PRESERVECRITERIA = 1
                    begin
                        /* Delete the target's alternate lookup IDs where the target and source have alternate lookup IDs of the same type */
                        delete 
                            from dbo.ALTERNATELOOKUPID
                        where 
                            ALTERNATELOOKUPID.CONSTITUENTID = @TARGETID and
                            ALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID in
                            (
                                select 
                                    SOURCELOOKUPIDS.ALTERNATELOOKUPIDTYPECODEID
                                from 
                                    dbo.ALTERNATELOOKUPID as SOURCELOOKUPIDS
                                where 
                                    SOURCELOOKUPIDS.CONSTITUENTID = @SOURCEID
                            );

                        /* Then move all of the source's alternate lookup IDs to the target */
                        update 
                            dbo.ALTERNATELOOKUPID
                        set 
                            ALTERNATELOOKUPID.CONSTITUENTID = @TARGETID, 
                            ALTERNATELOOKUPID.CHANGEDBYID = @CHANGEAGENTID,
                            ALTERNATELOOKUPID.DATECHANGED = @CURRENTDATE
                        where 
                            ALTERNATELOOKUPID.CONSTITUENTID = @SOURCEID;
                    end
                    else
                        update 
                            dbo.ALTERNATELOOKUPID
                        set 
                            ALTERNATELOOKUPID.CONSTITUENTID = @TARGETID, 
                            ALTERNATELOOKUPID.CHANGEDBYID = @CHANGEAGENTID,
                            ALTERNATELOOKUPID.DATECHANGED = @CURRENTDATE
                        where 
                            ALTERNATELOOKUPID.CONSTITUENTID = @SOURCEID;

                    return 0;