USP_MERGETWOCONSTITUENTS_AFFLUENCEINDICATOR

            create procedure dbo.USP_MERGETWOCONSTITUENTS_AFFLUENCEINDICATOR
            (
                @SOURCEID uniqueidentifier,
                @TARGETID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as
            begin
                set nocount on;

                declare @CURRENTDATE datetime = getdate();

                -- If Source and Target match on hash code and Source is Rejected/Confirmed, but Target is not Rejected/Confirmed, update the Target record values with Source record values. Notes will always be appended.

                update TARGETAFFLUENCEINDICATOR
                set
                    [CONFIRMED] = SOURCEAFFLUENCEINDICATOR.CONFIRMED
                    ,[DATECONFIRMED] = SOURCEAFFLUENCEINDICATOR.DATECONFIRMED
                    ,[CONFIRMEDBYAPPUSERID] = SOURCEAFFLUENCEINDICATOR.CONFIRMEDBYAPPUSERID
                    ,[REJECTED] = SOURCEAFFLUENCEINDICATOR.REJECTED
                    ,[DATEREJECTED] = SOURCEAFFLUENCEINDICATOR.DATEREJECTED
                    ,[REJECTEDBYAPPUSERID] = SOURCEAFFLUENCEINDICATOR.REJECTEDBYAPPUSERID
                    ,[SOURCE] = SOURCEAFFLUENCEINDICATOR.SOURCE
                    ,[LSID] = SOURCEAFFLUENCEINDICATOR.LSID
                    ,[REVISION] = SOURCEAFFLUENCEINDICATOR.REVISION
                    ,[ORIGINALID] = SOURCEAFFLUENCEINDICATOR.ORIGINALID
                    ,[NEWROW] = SOURCEAFFLUENCEINDICATOR.NEWROW
                    ,[FULLHASH] = SOURCEAFFLUENCEINDICATOR.FULLHASH
                    ,[PARTIALHASH] = SOURCEAFFLUENCEINDICATOR.PARTIALHASH
                    ,[MC] = SOURCEAFFLUENCEINDICATOR.MC
                    ,[PHONE] = SOURCEAFFLUENCEINDICATOR.PHONE
                    ,[GENDERCODE] = SOURCEAFFLUENCEINDICATOR.GENDERCODE
                    ,[MULTIMILLN] = SOURCEAFFLUENCEINDICATOR.MULTIMILLN
                    ,[WEALTHIND] = SOURCEAFFLUENCEINDICATOR.WEALTHIND
                    ,[RPASSETS] = SOURCEAFFLUENCEINDICATOR.RPASSETS
                    ,[FULLNAME] = SOURCEAFFLUENCEINDICATOR.FULLNAME
                    ,[CNOTES] = case SOURCEAFFLUENCEINDICATOR.CNOTES when '' then TARGETAFFLUENCEINDICATOR.CNOTES else TARGETAFFLUENCEINDICATOR.CNOTES + (case when TARGETAFFLUENCEINDICATOR.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCEAFFLUENCEINDICATOR.CNOTES end
                    ,[VIEWED] = SOURCEAFFLUENCEINDICATOR.VIEWED
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                    ,[RECURSIVEMATCH] = SOURCEAFFLUENCEINDICATOR.RECURSIVEMATCH
                    ,[ADDRESSTYPE] = SOURCEAFFLUENCEINDICATOR.ADDRESSTYPE
                    ,[LINE1] = SOURCEAFFLUENCEINDICATOR.LINE1
                    ,[CITY] = SOURCEAFFLUENCEINDICATOR.CITY
                    ,[STATE] = SOURCEAFFLUENCEINDICATOR.STATE
                    ,[ZIP] = SOURCEAFFLUENCEINDICATOR.ZIP
                    ,[ZIP4] = SOURCEAFFLUENCEINDICATOR.ZIP4
                    ,[COUNTY] = SOURCEAFFLUENCEINDICATOR.COUNTY
                    ,[TITLE] = SOURCEAFFLUENCEINDICATOR.TITLE
                    ,[COMPANY] = SOURCEAFFLUENCEINDICATOR.COMPANY
                    ,[WEBSITE] = SOURCEAFFLUENCEINDICATOR.WEBSITE
                    ,[SALES] = SOURCEAFFLUENCEINDICATOR.SALES
                    ,[EMPLOYMENT] = SOURCEAFFLUENCEINDICATOR.EMPLOYMENT
                    ,[NAICS] = SOURCEAFFLUENCEINDICATOR.NAICS
                    ,[AGE] = SOURCEAFFLUENCEINDICATOR.AGE
                    ,[DONOR] = SOURCEAFFLUENCEINDICATOR.DONOR
                    ,[HOMEOWNER] = SOURCEAFFLUENCEINDICATOR.HOMEOWNER
                    ,[MARITAL] = SOURCEAFFLUENCEINDICATOR.MARITAL
                    ,[DWELLING] = SOURCEAFFLUENCEINDICATOR.DWELLING
                    ,[LENGTHOFRESIDENCE] = SOURCEAFFLUENCEINDICATOR.LENGTHOFRESIDENCE
                    ,[CHILDRENINDICATOR] = SOURCEAFFLUENCEINDICATOR.CHILDRENINDICATOR
                    ,[INCOME] = SOURCEAFFLUENCEINDICATOR.INCOME
                    ,[NEWPROSPECT] = SOURCEAFFLUENCEINDICATOR.NEWPROSPECT
                    ,[DONOTCALL] = SOURCEAFFLUENCEINDICATOR.DONOTCALL
                    ,[METROPOLITANSTATISTICALAREA] = SOURCEAFFLUENCEINDICATOR.METROPOLITANSTATISTICALAREA
                    ,[CONTRIBUTOR] = SOURCEAFFLUENCEINDICATOR.CONTRIBUTOR
                    ,[POLITICALAFFILIATION] = SOURCEAFFLUENCEINDICATOR.POLITICALAFFILIATION
                    ,[RELIGIOUSAFFILIATION] = SOURCEAFFLUENCEINDICATOR.RELIGIOUSAFFILIATION
                    ,[ETHNIC] = SOURCEAFFLUENCEINDICATOR.ETHNIC
                from dbo.WPAFFLUENCEINDICATOR TARGETAFFLUENCEINDICATOR
                inner join dbo.WPAFFLUENCEINDICATOR SOURCEAFFLUENCEINDICATOR ON SOURCEAFFLUENCEINDICATOR.PARTIALHASH = TARGETAFFLUENCEINDICATOR.PARTIALHASH
                where SOURCEAFFLUENCEINDICATOR.WEALTHID = @SOURCEID
                    and TARGETAFFLUENCEINDICATOR.WEALTHID = @TARGETID
                    and not (TARGETAFFLUENCEINDICATOR.CONFIRMED = 1 or TARGETAFFLUENCEINDICATOR.REJECTED = 1)
                    and (SOURCEAFFLUENCEINDICATOR.CONFIRMED = 1 or SOURCEAFFLUENCEINDICATOR.REJECTED = 1)
                    and SOURCEAFFLUENCEINDICATOR.PARTIALHASH <> ''

                -- For all other cases when Source and Target match on hash codes, only notes will be appended.

                update TARGETAFFLUENCEINDICATOR
                set
                    [CNOTES] = TARGETAFFLUENCEINDICATOR.CNOTES + (case when TARGETAFFLUENCEINDICATOR.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCEAFFLUENCEINDICATOR.CNOTES
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                from dbo.WPAFFLUENCEINDICATOR TARGETAFFLUENCEINDICATOR
                inner join dbo.WPAFFLUENCEINDICATOR SOURCEAFFLUENCEINDICATOR ON SOURCEAFFLUENCEINDICATOR.PARTIALHASH = TARGETAFFLUENCEINDICATOR.PARTIALHASH
                where SOURCEAFFLUENCEINDICATOR.WEALTHID = @SOURCEID
                    and TARGETAFFLUENCEINDICATOR.WEALTHID = @TARGETID
                    and ((TARGETAFFLUENCEINDICATOR.CONFIRMED = 1 or TARGETAFFLUENCEINDICATOR.REJECTED = 1)
                        or (SOURCEAFFLUENCEINDICATOR.CONFIRMED = 0 and SOURCEAFFLUENCEINDICATOR.REJECTED = 0))
                    and SOURCEAFFLUENCEINDICATOR.PARTIALHASH <> ''
                    and SOURCEAFFLUENCEINDICATOR.CNOTES <> ''

                -- If unique record in Source that came from wealth screening (will have hash code), move it to Target.

                update SOURCEAFFLUENCEINDICATOR
                set
                    [WEALTHID] = @TARGETID,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                from dbo.WPAFFLUENCEINDICATOR SOURCEAFFLUENCEINDICATOR
                left join dbo.WPAFFLUENCEINDICATOR TARGETAFFLUENCEINDICATOR ON TARGETAFFLUENCEINDICATOR.PARTIALHASH = SOURCEAFFLUENCEINDICATOR.PARTIALHASH and TARGETAFFLUENCEINDICATOR.WEALTHID = @TARGETID
                where SOURCEAFFLUENCEINDICATOR.WEALTHID = @SOURCEID
                    and (SOURCEAFFLUENCEINDICATOR.PARTIALHASH is not null and SOURCEAFFLUENCEINDICATOR.PARTIALHASH <> '')
                    and TARGETAFFLUENCEINDICATOR.ID is null

                -- If unique record in Source which is entered manually, move it to Target.

                update dbo.WPAFFLUENCEINDICATOR
                set
                    [WEALTHID] = @TARGETID,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                where WEALTHID = @SOURCEID
                    and (PARTIALHASH is null or PARTIALHASH = '')

                exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_AFFLUENCEINDICATOR @TARGETID, @CHANGEAGENTID;
                exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_AFFLUENCEINDICATOR @SOURCEID, @CHANGEAGENTID;
            end