USP_MERGETWOCONSTITUENTS_PHILANTHROPICGIFT

            create procedure dbo.USP_MERGETWOCONSTITUENTS_PHILANTHROPICGIFT
            (
                @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 TARGETPHILANTHROPICGIFT
                set
                    [CONFIRMED] = SOURCEPHILANTHROPICGIFT.CONFIRMED
                    ,[DATECONFIRMED] = SOURCEPHILANTHROPICGIFT.DATECONFIRMED
                    ,[CONFIRMEDBYAPPUSERID] = SOURCEPHILANTHROPICGIFT.CONFIRMEDBYAPPUSERID
                    ,[REJECTED] = SOURCEPHILANTHROPICGIFT.REJECTED
                    ,[DATEREJECTED] = SOURCEPHILANTHROPICGIFT.DATEREJECTED
                    ,[REJECTEDBYAPPUSERID] = SOURCEPHILANTHROPICGIFT.REJECTEDBYAPPUSERID
                    ,[SOURCE] = SOURCEPHILANTHROPICGIFT.SOURCE
                    ,[WMID] = SOURCEPHILANTHROPICGIFT.WMID
                    ,[REVISION] = SOURCEPHILANTHROPICGIFT.REVISION
                    ,[ORIGINALID] = SOURCEPHILANTHROPICGIFT.ORIGINALID
                    ,[NEWROW] = SOURCEPHILANTHROPICGIFT.NEWROW
                    ,[CDATE] = SOURCEPHILANTHROPICGIFT.CDATE
                    ,[CVALUE] = SOURCEPHILANTHROPICGIFT.CVALUE
                    ,[CNOTES] = case SOURCEPHILANTHROPICGIFT.CNOTES when '' then TARGETPHILANTHROPICGIFT.CNOTES else TARGETPHILANTHROPICGIFT.CNOTES + (case when TARGETPHILANTHROPICGIFT.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCEPHILANTHROPICGIFT.CNOTES end
                    ,[FULLHASH] = SOURCEPHILANTHROPICGIFT.FULLHASH
                    ,[PARTIALHASH] = SOURCEPHILANTHROPICGIFT.PARTIALHASH
                    ,[MC] = SOURCEPHILANTHROPICGIFT.MC
                    ,[ORGANIZATION] = SOURCEPHILANTHROPICGIFT.ORGANIZATION
                    ,[LOCATION] = SOURCEPHILANTHROPICGIFT.LOCATION
                    ,[GIFTYEAR] = SOURCEPHILANTHROPICGIFT.GIFTYEAR
                    ,[GIFTRANGE] = SOURCEPHILANTHROPICGIFT.GIFTRANGE
                    ,[TYPE] = SOURCEPHILANTHROPICGIFT.TYPE
                    ,[CATEGORY] = SOURCEPHILANTHROPICGIFT.CATEGORY
                    ,[LO] = SOURCEPHILANTHROPICGIFT.LO
                    ,[HI] = SOURCEPHILANTHROPICGIFT.HI
                    ,[NAME] = SOURCEPHILANTHROPICGIFT.NAME
                    ,[ORGANIZATIONWEBADDRESS] = SOURCEPHILANTHROPICGIFT.ORGANIZATIONWEBADDRESS
                    ,[SOURCEMATERIAL] = SOURCEPHILANTHROPICGIFT.SOURCEMATERIAL
                    ,[GIFTYEARHI] = SOURCEPHILANTHROPICGIFT.GIFTYEARHI
                    ,[GIFTYEARLO] = SOURCEPHILANTHROPICGIFT.GIFTYEARLO
                    ,[TYPECODE] = SOURCEPHILANTHROPICGIFT.TYPECODE
                    ,[EIN] = SOURCEPHILANTHROPICGIFT.EIN
                    ,[VIEWED] = SOURCEPHILANTHROPICGIFT.VIEWED
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                    ,[RECURSIVEMATCH] = SOURCEPHILANTHROPICGIFT.RECURSIVEMATCH
                    ,[MATCHHASH] = SOURCEPHILANTHROPICGIFT.MATCHHASH
                    ,[PHILANTHROPICGIFTID] = SOURCEPHILANTHROPICGIFT.PHILANTHROPICGIFTID
                    ,[HOUSEHOLDID] = SOURCEPHILANTHROPICGIFT.HOUSEHOLDID
                    ,[TASCORE] = SOURCEPHILANTHROPICGIFT.TASCORE
                    ,[MATCHADDRESS] = SOURCEPHILANTHROPICGIFT.MATCHADDRESS
                    ,[MATCHCITY] = SOURCEPHILANTHROPICGIFT.MATCHCITY
                    ,[MATCHSTATE] = SOURCEPHILANTHROPICGIFT.MATCHSTATE
                    ,[MATCHZIP] = SOURCEPHILANTHROPICGIFT.MATCHZIP
                from dbo.WPPHILANTHROPICGIFT TARGETPHILANTHROPICGIFT
                inner join dbo.WPPHILANTHROPICGIFT SOURCEPHILANTHROPICGIFT ON SOURCEPHILANTHROPICGIFT.PARTIALHASH = TARGETPHILANTHROPICGIFT.PARTIALHASH
                where SOURCEPHILANTHROPICGIFT.WEALTHID = @SOURCEID
                    and TARGETPHILANTHROPICGIFT.WEALTHID = @TARGETID
                    and not (TARGETPHILANTHROPICGIFT.CONFIRMED = 1 or TARGETPHILANTHROPICGIFT.REJECTED = 1)
                    and (SOURCEPHILANTHROPICGIFT.CONFIRMED = 1 or SOURCEPHILANTHROPICGIFT.REJECTED = 1)
                    and SOURCEPHILANTHROPICGIFT.PARTIALHASH <> ''

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

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

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

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

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

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

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