USP_MERGETWOCONSTITUENTS_BIOGRAPHICAL

            create procedure dbo.USP_MERGETWOCONSTITUENTS_BIOGRAPHICAL
            (
                @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 TARGETBIOGRAPHICAL
                set
                    [CONFIRMED] = SOURCEBIOGRAPHICAL.CONFIRMED
                    ,[DATECONFIRMED] = SOURCEBIOGRAPHICAL.DATECONFIRMED
                    ,[CONFIRMEDBYAPPUSERID] = SOURCEBIOGRAPHICAL.CONFIRMEDBYAPPUSERID
                    ,[REJECTED] = SOURCEBIOGRAPHICAL.REJECTED
                    ,[DATEREJECTED] = SOURCEBIOGRAPHICAL.DATEREJECTED
                    ,[REJECTEDBYAPPUSERID] = SOURCEBIOGRAPHICAL.REJECTEDBYAPPUSERID
                    ,[SOURCE] = SOURCEBIOGRAPHICAL.SOURCE
                    ,[WWID] = SOURCEBIOGRAPHICAL.WWID
                    ,[REVISION] = SOURCEBIOGRAPHICAL.REVISION
                    ,[ORIGINALID] = SOURCEBIOGRAPHICAL.ORIGINALID
                    ,[NEWROW] = SOURCEBIOGRAPHICAL.NEWROW
                    ,[FULLHASH] = SOURCEBIOGRAPHICAL.FULLHASH
                    ,[PARTIALHASH] = SOURCEBIOGRAPHICAL.PARTIALHASH
                    ,[MC] = SOURCEBIOGRAPHICAL.MC
                    ,[FULLNAME] = SOURCEBIOGRAPHICAL.FULLNAME
                    ,[LINE1] = SOURCEBIOGRAPHICAL.LINE1
                    ,[CITY] = SOURCEBIOGRAPHICAL.CITY
                    ,[STATE] = SOURCEBIOGRAPHICAL.STATE
                    ,[ZIP] = SOURCEBIOGRAPHICAL.ZIP
                    ,[A2LINE1] = SOURCEBIOGRAPHICAL.A2LINE1
                    ,[A2CITY] = SOURCEBIOGRAPHICAL.A2CITY
                    ,[A2STATE] = SOURCEBIOGRAPHICAL.A2STATE
                    ,[A2ZIP] = SOURCEBIOGRAPHICAL.A2ZIP
                    ,[GENDERCODE] = SOURCEBIOGRAPHICAL.GENDERCODE
                    ,[BIRTHDATE] = SOURCEBIOGRAPHICAL.BIRTHDATE
                    ,[CNOTES] = case SOURCEBIOGRAPHICAL.CNOTES when '' then TARGETBIOGRAPHICAL.CNOTES else TARGETBIOGRAPHICAL.CNOTES + (case when TARGETBIOGRAPHICAL.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCEBIOGRAPHICAL.CNOTES end
                    ,[BIRTHPLACE] = SOURCEBIOGRAPHICAL.BIRTHPLACE
                    ,[DECEASEDDATE] = SOURCEBIOGRAPHICAL.DECEASEDDATE
                    ,[OCCUPATION] = SOURCEBIOGRAPHICAL.OCCUPATION
                    ,[EDUCATION] = SOURCEBIOGRAPHICAL.EDUCATION
                    ,[FAMILY] = SOURCEBIOGRAPHICAL.FAMILY
                    ,[POSITIONSHELD] = SOURCEBIOGRAPHICAL.POSITIONSHELD
                    ,[CERTIFICATIONS] = SOURCEBIOGRAPHICAL.CERTIFICATIONS
                    ,[CAREER] = SOURCEBIOGRAPHICAL.CAREER
                    ,[AWARDS] = SOURCEBIOGRAPHICAL.AWARDS
                    ,[MEMBERSHIPS] = SOURCEBIOGRAPHICAL.MEMBERSHIPS
                    ,[RESEARCHINTERESTS] = SOURCEBIOGRAPHICAL.RESEARCHINTERESTS
                    ,[POLITICALRELIGIOUSAFFILIATIONS] = SOURCEBIOGRAPHICAL.POLITICALRELIGIOUSAFFILIATIONS
                    ,[CIVICMILITARYSERVICE] = SOURCEBIOGRAPHICAL.CIVICMILITARYSERVICE
                    ,[THOUGHTSONLIFE] = SOURCEBIOGRAPHICAL.THOUGHTSONLIFE
                    ,[LAW] = SOURCEBIOGRAPHICAL.LAW
                    ,[PERSONAL] = SOURCEBIOGRAPHICAL.PERSONAL
                    ,[VIEWED] = SOURCEBIOGRAPHICAL.VIEWED
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                    ,[RECURSIVEMATCH] = SOURCEBIOGRAPHICAL.RECURSIVEMATCH
                from dbo.WPBIOGRAPHICAL TARGETBIOGRAPHICAL
                inner join dbo.WPBIOGRAPHICAL SOURCEBIOGRAPHICAL ON SOURCEBIOGRAPHICAL.PARTIALHASH = TARGETBIOGRAPHICAL.PARTIALHASH
                where SOURCEBIOGRAPHICAL.WEALTHID = @SOURCEID
                    and TARGETBIOGRAPHICAL.WEALTHID = @TARGETID
                    and not (TARGETBIOGRAPHICAL.CONFIRMED = 1 or TARGETBIOGRAPHICAL.REJECTED = 1)
                    and (SOURCEBIOGRAPHICAL.CONFIRMED = 1 or SOURCEBIOGRAPHICAL.REJECTED = 1)
                    and SOURCEBIOGRAPHICAL.PARTIALHASH <> ''

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

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

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

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

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

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

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