USP_MERGETASK_CONSTITUENTPHONE

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

                    declare @CHANGEDATE datetime = getdate();

                    if exists(
                        select ID
                        from dbo.PHONE
                        where CONSTITUENTID = @SOURCEID
                    )
                    begin

                        --Store the primary phone indicators for later access
                        declare @SOURCEPRIMARYID as uniqueidentifier;
                        select @SOURCEPRIMARYID = ID
                        from dbo.PHONE
                        where CONSTITUENTID = @SOURCEID and ISPRIMARY = 1;

                        declare @TARGETPRIMARYID as uniqueidentifier;
                        select @TARGETPRIMARYID = ID
                        from dbo.PHONE
                        where CONSTITUENTID = @TARGETID and ISPRIMARY = 1;

                        --Wipe the source primary phone indicators before the merge
                        --to keep from violating any constraints
                        update dbo.PHONE
                        set ISPRIMARY = 0, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        where ID in (@SOURCEPRIMARYID, @TARGETPRIMARYID)
                        and RELATIONSHIPID is null;

                        declare @DUPLICATEPHONES table (SOURCE_PHONEID uniqueidentifier, TARGET_PHONEID uniqueidentifier,
                            SOURCE_DONOTCALL bit, SOURCE_DONOTCALLREASONCODEID uniqueidentifier, SOURCE_ISCONFIDENTIAL bit, SOURCE_DONOTTEXT bit);

                        insert into @DUPLICATEPHONES
                        select
                            SOURCE_PHONE.ID,
                            TARGET_PHONE.ID,
                            SOURCE_PHONE.DONOTCALL,
                            SOURCE_PHONE.DONOTCALLREASONCODEID,
                            SOURCE_PHONE.ISCONFIDENTIAL,
                            SOURCE_PHONE.DONOTTEXT
                        from
                            dbo.PHONE SOURCE_PHONE
                        inner join
                            dbo.PHONE TARGET_PHONE on TARGET_PHONE.CONSTITUENTID = @TARGETID
                        where
                            SOURCE_PHONE.CONSTITUENTID = @SOURCEID and
                            SOURCE_PHONE.RELATIONSHIPID is null and
                            SOURCE_PHONE.NUMBER = TARGET_PHONE.NUMBER and
                            ((SOURCE_PHONE.PHONETYPECODEID = TARGET_PHONE.PHONETYPECODEID) or (SOURCE_PHONE.PHONETYPECODEID is null and TARGET_PHONE.PHONETYPECODEID is null));

                        --move all of the source constit's phones over to
                        --the target constit
                        update dbo.PHONE
                        set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                        where CONSTITUENTID = @SOURCEID
                            and RELATIONSHIPID is null
                            and ID not in (select SOURCE_PHONEID from @DUPLICATEPHONES);

                        --Update some specific items that wouldn't otherwise be carried over on duplicate phones
                        update TARGET set
                            DONOTCALL =
                                case
                                    when TARGET.DONOTCALL = 1
                                        then 1
                                    when exists(select 1 from @DUPLICATEPHONES DUPE where DUPE.TARGET_PHONEID = TARGET.ID and DUPE.SOURCE_DONOTCALL = 1)
                                        then 1
                                    else
                                        0
                                end,
                            DONOTCALLREASONCODEID =
                                case
                                    when TARGET.DONOTCALLREASONCODEID is not null
                                        then TARGET.DONOTCALLREASONCODEID
                                    else
                                        (select top 1 DUPE.SOURCE_DONOTCALLREASONCODEID from @DUPLICATEPHONES DUPE where TARGET_PHONEID = TARGET.ID order by DUPE.SOURCE_DONOTCALLREASONCODEID desc)
                                end,
                            ISCONFIDENTIAL = 
                                case
                                    when TARGET.ISCONFIDENTIAL = 1
                                        then 1
                                    when exists(select 1 from @DUPLICATEPHONES DUPE where TARGET_PHONEID = TARGET.ID and DUPE.SOURCE_ISCONFIDENTIAL = 1)
                                        then 1
                                    else
                                        0
                                end,
                            DONOTTEXT =
                                case
                                    when TARGET.DONOTTEXT = 1
                                        then 1
                                    when exists(select 1 from @DUPLICATEPHONES DUPE where DUPE.TARGET_PHONEID = TARGET.ID and DUPE.SOURCE_DONOTTEXT = 1)
                                        then 1
                                    else
                                        0
                                end,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                        from
                            dbo.PHONE TARGET
                        where
                            TARGET.CONSTITUENTID = @TARGETID;

                        --Select the primary phone
                        if @PRIMARYCRITERIA = 0 and @TARGETPRIMARYID is not null
                        begin
                            -- The target's original primary phone record
                            -- is assured of being associated with the target
                            -- so simply reset it's primary indicator.
                            update dbo.PHONE
                            set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                            where ID = @TARGETPRIMARYID    
                            and RELATIONSHIPID is null;


                            declare @TEMPUID uniqueidentifier;
                                select @TEMPUID = CONSTITUENTID
                                from dbo.PHONE
                                where ID = @SOURCEPRIMARYID;

                            if @TEMPUID = @SOURCEID
                            begin
                                -- If the source's primary address is still associated to
                                -- the source, then reset the primary indicator
                                update dbo.PHONE
                                set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                                where ID = @SOURCEPRIMARYID
                                and RELATIONSHIPID is null;
                            end
                        end
                        else
                        begin
                            -- The target had no primary phone record,
                            -- or we are preserving the source's primary
                            -- phone record
                            if @SOURCEPRIMARYID is null
                            begin
                                -- If the source had no primary phone record then
                                -- attempt to reset the target's primary record indicator
                                update dbo.PHONE
                                set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                                where ID = @TARGETPRIMARYID
                                and RELATIONSHIPID is null
                            end
                            else
                            begin
                                -- Otherwise, the source had a primary educational record
                                declare @TEMPID uniqueidentifier;
                                select @TEMPID = CONSTITUENTID
                                from dbo.PHONE
                                where ID = @SOURCEPRIMARYID;

                                if @TEMPID = @TARGETID
                                begin
                                    -- If the source primary phone is now associated
                                    -- with the target, then reset its primary indicator
                                    update dbo.PHONE
                                    set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                                    where ID = @SOURCEPRIMARYID
                                    and RELATIONSHIPID is null;
                                end
                                else
                                begin
                                    -- Otherwise, the source's primary phone was
                                    -- not associated with the target b/c it is a
                                    -- duplicate of one of the target's phone records.
                                    -- In this scenario, the target's phone record that
                                    -- is a duplicate of the source's primary phone
                                    -- should be set as the target's primary phone.
                                    set @TEMPID = null;

                                    select @TEMPID = TARGET_PHONEID
                                    from @DUPLICATEPHONES DP
                                    where DP.SOURCE_PHONEID = @SOURCEPRIMARYID;

                                    if @TEMPID is not null
                                    begin
                                        update dbo.PHONE
                                        set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                                        where ID = @TEMPID
                                        and RELATIONSHIPID is null;
                                    end
                                    else
                                    begin
                                        -- If no match was found then reset the primary
                                        -- indicator on the target's original primary
                                        -- record
                                        update dbo.PHONE
                                        set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                                        where ID = @TARGETPRIMARYID
                                        and RELATIONSHIPID is null;
                                    end    
                                end
                            end    
                        end

                        --update sales order if deleting duplicates
                        update dbo.SALESORDER
                        set
                            PHONEID = coalesce((
                                select top 1 target.ID 
                                from dbo.PHONE target
                                inner join dbo.PHONE source
                                on target.NUMBER = source.NUMBER
                                and (target.PHONETYPECODEID = source.PHONETYPECODEID or (target.PHONETYPECODEID is null and source.PHONETYPECODEID is null))
                                where target.CONSTITUENTID = @TARGETID
                                and source.ID = SALESORDER.PHONEID                                
                            ), SALESORDER.PHONEID),
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                        where                            
                            SALESORDER.RECIPIENTID = @SOURCEID                                                            
                    end

                    update dbo.BATCHPHONEFINDER
                    set
                        CONSTITUENTID = @TARGETID
                    where
                        CONSTITUENTID = @SOURCEID

                    return 0;