USP_CONSTITUENTMERGE_UPDATEGROUPEMAILMAILPREFS

            CREATE procedure USP_CONSTITUENTMERGE_UPDATEGROUPEMAILMAILPREFS
            (
                @TARGETID uniqueidentifier,
                @SOURCEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @DELETEDUPES bit = 0
            )
            as

            declare @CHANGEDATE datetime = getdate();

            --Count the number of mail prefs that use the source as a group contact and send to an email

            declare @GROUPMAILPREFCOUNT int
            select @GROUPMAILPREFCOUNT=COUNT(ID)
            from MAILPREFERENCEGROUPCONTACT
            where CONSTITUENTID=@SOURCEID
                and ((not EMAILADDRESSID is null) 
                    or (USEPRIMARYEMAIL = 1)
                )

            --If there are no candidate prefs, do nothing

            if @GROUPMAILPREFCOUNT <> 0
            begin

                --Try to update prefs.  This won't work if both the email and personal information merge tasks haven't run.

                update MAILPREFERENCEGROUPCONTACT
                set
                    CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
                where 
                    ID in(
                        select mpgc.ID
                        from GROUPMEMBER gm
                            join MAILPREFERENCE mp on mp.CONSTITUENTID=gm.GROUPID
                            join MAILPREFERENCEGROUPCONTACT mpgc on mpgc.MAILPREFERENCEID=mp.ID
                            left join EMAILADDRESS e on e.CONSTITUENTID=gm.MEMBERID and e.ID=mpgc.EMAILADDRESSID
                        where gm.MEMBERID=@TARGETID
                            and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(gm.ID) = 1
                            and mpgc.CONSTITUENTID=@SOURCEID
                            and ((not e.ID is null) or (e.ID is null and mpgc.USEPRIMARYEMAIL = 1))
                    )

                --Check to see if all candidate prefs were updated.  If they weren't it means one of

                --two things:  1) some of the mail prefs will be invalidated by the merge, and can

                --be removed or 2) both the email and personal information merge tasks haven't

                --been run yet.  If it is the latter, the prefs will be updated the next time this

                --procedure is called.

                if @@ROWCOUNT <> @GROUPMAILPREFCOUNT
                begin

                    --If the target and the source are members of different households, 

                    --prefs on the source's household will be invalidated and can be deleted.


                    --Get target's current household, if any.

                    declare @TARGETHOUSEHOLDID uniqueidentifier
                    select @TARGETHOUSEHOLDID = gm.GROUPID
                    from GROUPMEMBER gm
                    where gm.MEMBERID=@TARGETID
                        and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(gm.ID) = 1
                        and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(gm.GROUPID) = 1

                    if (not @TARGETHOUSEHOLDID is null)
                    begin
                        --If the target is in a household and there are mail preferences on 

                        --a different household that send to the source (meaning we can assume

                        --that the source was a current member of that household before the merge 

                        --started), delete those prefs.

                        delete MAILPREFERENCE
                        where 
                            ID in (
                                select mp.ID
                                from MAILPREFERENCE mp
                                    join MAILPREFERENCEGROUPCONTACT mpgc on mpgc.MAILPREFERENCEID=mp.ID
                                where mpgc.CONSTITUENTID=@SOURCEID
                                    and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(mp.CONSTITUENTID) = 1
                                    and mp.CONSTITUENTID<>@TARGETHOUSEHOLDID
                            )
                    end

                    --If duplicate emails are to be deleted, delete mail prefs 

                    --that would send to those dupe emails.

                    if @DELETEDUPES = 1
                    begin
                        delete MAILPREFERENCE
                        where 
                            ID in (
                                select mpgc.MAILPREFERENCEID
                                from MAILPREFERENCEGROUPCONTACT mpgc
                                    join EMAILADDRESS email on email.ID=mpgc.EMAILADDRESSID
                                where mpgc.CONSTITUENTID=@SOURCEID
                                    and email.CONSTITUENTID=@SOURCEID
                                    and email.ID in (
                                        select a.ID 
                                        from dbo.EMAILADDRESS a
                                            inner join dbo.EMAILADDRESS b on a.EMAILADDRESS = b.EMAILADDRESS
                                                and (a.EMAILADDRESSTYPECODEID = b.EMAILADDRESSTYPECODEID 
                                                    or (a.EMAILADDRESSTYPECODEID is null and b.EMAILADDRESSTYPECODEID is null)
                                                )
                                        where a.CONSTITUENTID = @SOURCEID
                                            and b.CONSTITUENTID = @TARGETID
                                    )
                            )
                    end
                end
            end

            return 0;