USP_CONSTITUENTMERGE_UPDATEGROUPADDRESSMAILPREFS

            CREATE procedure USP_CONSTITUENTMERGE_UPDATEGROUPADDRESSMAILPREFS
            (
                @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 address

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

            --If there are no candidate prefs, do nothing

            if @GROUPMAILPREFCOUNT <> 0
            begin

                --Try to update prefs.  This won't work if both the address 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 ADDRESS a on a.CONSTITUENTID=gm.MEMBERID and a.ID=mpgc.ADDRESSID
                        where gm.MEMBERID=@TARGETID
                            and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(gm.ID) = 1
                            and mpgc.CONSTITUENTID=@SOURCEID
                            and ((not a.ID is null) or (a.ID is null and mpgc.USEPRIMARYADDRESS = 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 address 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 addresses are to be deleted, delete mail prefs 

                    --that would send to those dupe addresses.

                    if @DELETEDUPES = 1
                    begin
                        delete MAILPREFERENCE
                        where 
                            ID in (
                                select mpgc.MAILPREFERENCEID
                                from MAILPREFERENCEGROUPCONTACT mpgc
                                    join ADDRESS addr on addr.ID=mpgc.ADDRESSID
                                where mpgc.CONSTITUENTID=@SOURCEID
                                    and addr.CONSTITUENTID=@SOURCEID
                                    and addr.ID in (
                                        select a.ID
                                        from dbo.ADDRESS a
                                            inner join dbo.ADDRESS b on a.COUNTRYID = b.COUNTRYID
                                                and (a.STATEID = b.STATEID or (a.STATEID is null and b.STATEID is null))
                                                and a.ADDRESSBLOCK = b.ADDRESSBLOCK
                                                and a.CITY = b.CITY
                                                and a.POSTCODE = b.POSTCODE
                                                and (a.ADDRESSTYPECODEID = b.ADDRESSTYPECODEID or (a.ADDRESSTYPECODEID is null and b.ADDRESSTYPECODEID is null))
                                                and a.CART = b.CART
                                                and a.DPC = b.DPC
                                                and a.LOT = b.LOT
                                        where a.CONSTITUENTID = @SOURCEID
                                            and b.CONSTITUENTID = @TARGETID
                                    )
                            )
                    end
                end
            end

            return 0;