USP_MERGETWOCONSTITUENTS_PRIVATEFOUNDATION

            create procedure dbo.USP_MERGETWOCONSTITUENTS_PRIVATEFOUNDATION
            (
                @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 TARGETPRIVATEFOUNDATION
                set
                    [CONFIRMED] = SOURCEPRIVATEFOUNDATION.CONFIRMED
                    ,[DATECONFIRMED] = SOURCEPRIVATEFOUNDATION.DATECONFIRMED
                    ,[CONFIRMEDBYAPPUSERID] = SOURCEPRIVATEFOUNDATION.CONFIRMEDBYAPPUSERID
                    ,[REJECTED] = SOURCEPRIVATEFOUNDATION.REJECTED
                    ,[DATEREJECTED] = SOURCEPRIVATEFOUNDATION.DATEREJECTED
                    ,[REJECTEDBYAPPUSERID] = SOURCEPRIVATEFOUNDATION.REJECTEDBYAPPUSERID
                    ,[SOURCE] = SOURCEPRIVATEFOUNDATION.SOURCE
                    ,[FAID] = SOURCEPRIVATEFOUNDATION.FAID
                    ,[REVISION] = SOURCEPRIVATEFOUNDATION.REVISION
                    ,[ORIGINALID] = SOURCEPRIVATEFOUNDATION.ORIGINALID
                    ,[NEWROW] = SOURCEPRIVATEFOUNDATION.NEWROW
                    ,[CDATE] = SOURCEPRIVATEFOUNDATION.CDATE
                    ,[CVALUE] = SOURCEPRIVATEFOUNDATION.CVALUE
                    ,[CNOTES] = case SOURCEPRIVATEFOUNDATION.CNOTES when '' then TARGETPRIVATEFOUNDATION.CNOTES else TARGETPRIVATEFOUNDATION.CNOTES + (case when TARGETPRIVATEFOUNDATION.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCEPRIVATEFOUNDATION.CNOTES end
                    ,[FULLHASH] = SOURCEPRIVATEFOUNDATION.FULLHASH
                    ,[PARTIALHASH] = SOURCEPRIVATEFOUNDATION.PARTIALHASH
                    ,[MC] = SOURCEPRIVATEFOUNDATION.MC
                    ,[RULING_YEAR] = SOURCEPRIVATEFOUNDATION.RULING_YEAR
                    ,[YEAR_ENDING] = SOURCEPRIVATEFOUNDATION.YEAR_ENDING
                    ,[EIN] = SOURCEPRIVATEFOUNDATION.EIN
                    ,[COMPANY] = SOURCEPRIVATEFOUNDATION.COMPANY
                    ,[LINE1] = SOURCEPRIVATEFOUNDATION.LINE1
                    ,[CITY] = SOURCEPRIVATEFOUNDATION.CITY
                    ,[STATE] = SOURCEPRIVATEFOUNDATION.STATE
                    ,[ZIP] = SOURCEPRIVATEFOUNDATION.ZIP
                    ,[PHONE] = SOURCEPRIVATEFOUNDATION.PHONE
                    ,[DESCRIPTION] = SOURCEPRIVATEFOUNDATION.DESCRIPTION
                    ,[TOTALASSETS_FMV] = SOURCEPRIVATEFOUNDATION.TOTALASSETS_FMV
                    ,[FULLNAME] = SOURCEPRIVATEFOUNDATION.FULLNAME
                    ,[TITLE] = SOURCEPRIVATEFOUNDATION.TITLE
                    ,[COMP] = SOURCEPRIVATEFOUNDATION.COMP
                    ,[VIEWED] = SOURCEPRIVATEFOUNDATION.VIEWED
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                    ,[RECURSIVEMATCH] = SOURCEPRIVATEFOUNDATION.RECURSIVEMATCH
                    ,[FORMYEAR] = SOURCEPRIVATEFOUNDATION.FORMYEAR
                    ,[FILEDDATE] = SOURCEPRIVATEFOUNDATION.FILEDDATE
                    ,[WPRELATIONSHIP_PF_ID] = SOURCEPRIVATEFOUNDATION.WPRELATIONSHIP_PF_ID
                    ,[FAIRMARKETVALUEASSETS] = SOURCEPRIVATEFOUNDATION.FAIRMARKETVALUEASSETS
                    ,[LOCATION] = SOURCEPRIVATEFOUNDATION.LOCATION
                    ,[REVENUE] = SOURCEPRIVATEFOUNDATION.REVENUE
                    ,[ORGFORMYEAR] = SOURCEPRIVATEFOUNDATION.ORGFORMYEAR
                    ,[WEBSITE] = SOURCEPRIVATEFOUNDATION.WEBSITE
                    ,[HCITY] = SOURCEPRIVATEFOUNDATION.HCITY
                    ,[HSTATE] = SOURCEPRIVATEFOUNDATION.HSTATE
                    ,[HZIP] = SOURCEPRIVATEFOUNDATION.HZIP
                    ,[SPOUSEFLAG] = SOURCEPRIVATEFOUNDATION.SPOUSEFLAG
                from dbo.WPPRIVATEFOUNDATION TARGETPRIVATEFOUNDATION
                inner join dbo.WPPRIVATEFOUNDATION SOURCEPRIVATEFOUNDATION ON SOURCEPRIVATEFOUNDATION.PARTIALHASH = TARGETPRIVATEFOUNDATION.PARTIALHASH
                where SOURCEPRIVATEFOUNDATION.WEALTHID = @SOURCEID
                    and TARGETPRIVATEFOUNDATION.WEALTHID = @TARGETID
                    and not (TARGETPRIVATEFOUNDATION.CONFIRMED = 1 or TARGETPRIVATEFOUNDATION.REJECTED = 1)
                    and (SOURCEPRIVATEFOUNDATION.CONFIRMED = 1 or SOURCEPRIVATEFOUNDATION.REJECTED = 1)
                    and SOURCEPRIVATEFOUNDATION.PARTIALHASH <> ''

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

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

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

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

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

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

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