USP_MERGETASK_CONSTITUENTLOOKUPID

                CREATE procedure dbo.USP_MERGETASK_CONSTITUENTLOOKUPID
                (
                    @SOURCEID uniqueidentifier,
                    @TARGETID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier,
                    @ALTERNATELOOKUPIDTYPECODEID uniqueidentifier = null,
                    @OVERWRITEALTERNATETYPE bit = 0
                )
                as
                    set nocount on;

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

                    if @ALTERNATELOOKUPIDTYPECODEID is null
                        raiserror('The "Lookup ID" merge task cannot be run because an alternate lookup ID type was not provided by the merge configuration.', 13, 1)

                    declare @SOURCELOOKUPID nvarchar(100);
                    select
                        @SOURCELOOKUPID = LOOKUPID
                    from dbo.CONSTITUENT
                    where ID = @SOURCEID

                    --Make sure the target does not already have the exact alternate lookup ID
                    if not exists (select ID from dbo.ALTERNATELOOKUPID where CONSTITUENTID = @TARGETID and ALTERNATELOOKUPIDTYPECODEID = @ALTERNATELOOKUPIDTYPECODEID and ALTERNATELOOKUPID = @SOURCELOOKUPID)
                    begin
                        if exists (
                          select top 1 ID
                          from dbo.ALTERNATELOOKUPID
                          where ALTERNATELOOKUPID = @SOURCELOOKUPID
                          and ALTERNATELOOKUPIDTYPECODEID = @ALTERNATELOOKUPIDTYPECODEID
                          and CONSTITUENTID <> @TARGETID
                        )
                          raiserror('Cannot merge to an alternate lookup ID of this type when it already exists on another constituent.', 13, 1)

                        insert into dbo.ALTERNATELOOKUPID
                        (
                            ID,
                            CONSTITUENTID,
                            ALTERNATELOOKUPIDTYPECODEID,
                            ALTERNATELOOKUPID,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED
                        )
                        values
                        (
                            newid(),
                            @TARGETID,
                            @ALTERNATELOOKUPIDTYPECODEID,
                            @SOURCELOOKUPID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        )
                    end

                return 0;