USP_MERGETWOCONSTITUENTS_NONPROFITAFFILIATION

            create procedure dbo.USP_MERGETWOCONSTITUENTS_NONPROFITAFFILIATION
            (
                @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 TARGETNONPROFITAFFILIATION
                set
                    [CONFIRMED] = SOURCENONPROFITAFFILIATION.CONFIRMED
                    ,[DATECONFIRMED] = SOURCENONPROFITAFFILIATION.DATECONFIRMED
                    ,[CONFIRMEDBYAPPUSERID] = SOURCENONPROFITAFFILIATION.CONFIRMEDBYAPPUSERID
                    ,[REJECTED] = SOURCENONPROFITAFFILIATION.REJECTED
                    ,[DATEREJECTED] = SOURCENONPROFITAFFILIATION.DATEREJECTED
                    ,[REJECTEDBYAPPUSERID] = SOURCENONPROFITAFFILIATION.REJECTEDBYAPPUSERID
                    ,[SOURCE] = SOURCENONPROFITAFFILIATION.SOURCE
                    ,[GSID] = SOURCENONPROFITAFFILIATION.GSID
                    ,[REVISION] = SOURCENONPROFITAFFILIATION.REVISION
                    ,[ORIGINALID] = SOURCENONPROFITAFFILIATION.ORIGINALID
                    ,[NEWROW] = SOURCENONPROFITAFFILIATION.NEWROW
                    ,[FULLHASH] = SOURCENONPROFITAFFILIATION.FULLHASH
                    ,[PARTIALHASH] = SOURCENONPROFITAFFILIATION.PARTIALHASH
                    ,[MC] = SOURCENONPROFITAFFILIATION.MC
                    ,[FULLNAME] = SOURCENONPROFITAFFILIATION.FULLNAME
                    ,[TITLE] = SOURCENONPROFITAFFILIATION.TITLE
                    ,[SALARY] = SOURCENONPROFITAFFILIATION.SALARY
                    ,[DN_ORGANIZATION] = SOURCENONPROFITAFFILIATION.DN_ORGANIZATION
                    ,[DN_ZIP5] = SOURCENONPROFITAFFILIATION.DN_ZIP5
                    ,[EIN] = SOURCENONPROFITAFFILIATION.EIN
                    ,[LINE1] = SOURCENONPROFITAFFILIATION.LINE1
                    ,[CITY] = SOURCENONPROFITAFFILIATION.CITY
                    ,[ZIP] = SOURCENONPROFITAFFILIATION.ZIP
                    ,[REVENUE] = SOURCENONPROFITAFFILIATION.REVENUE
                    ,[STATE] = SOURCENONPROFITAFFILIATION.STATE
                    ,[PHONE] = SOURCENONPROFITAFFILIATION.PHONE
                    ,[CNOTES] = case SOURCENONPROFITAFFILIATION.CNOTES when '' then TARGETNONPROFITAFFILIATION.CNOTES else TARGETNONPROFITAFFILIATION.CNOTES + (case when TARGETNONPROFITAFFILIATION.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCENONPROFITAFFILIATION.CNOTES end
                    ,[VIEWED] = SOURCENONPROFITAFFILIATION.VIEWED
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                    ,[RECURSIVEMATCH] = SOURCENONPROFITAFFILIATION.RECURSIVEMATCH
                    ,[FORMYEAR] = SOURCENONPROFITAFFILIATION.FORMYEAR
                    ,[FILEDDATE] = SOURCENONPROFITAFFILIATION.FILEDDATE
                    ,[WPRELATIONSHIP_NPA_ID] = SOURCENONPROFITAFFILIATION.WPRELATIONSHIP_NPA_ID
                    ,[DESCRIPTION] = SOURCENONPROFITAFFILIATION.DESCRIPTION
                    ,[TOTALASSETS] = SOURCENONPROFITAFFILIATION.TOTALASSETS
                    ,[WEBSITE] = SOURCENONPROFITAFFILIATION.WEBSITE
                    ,[ORGFORMYEAR] = SOURCENONPROFITAFFILIATION.ORGFORMYEAR
                    ,[RULINGYEAR] = SOURCENONPROFITAFFILIATION.RULINGYEAR
                    ,[LOCATION] = SOURCENONPROFITAFFILIATION.LOCATION
                    ,[HCITY] = SOURCENONPROFITAFFILIATION.HCITY
                    ,[HSTATE] = SOURCENONPROFITAFFILIATION.HSTATE
                    ,[HZIP] = SOURCENONPROFITAFFILIATION.HZIP
                    ,[SPOUSEFLAG] = SOURCENONPROFITAFFILIATION.SPOUSEFLAG
                from dbo.WPNONPROFITAFFILIATION TARGETNONPROFITAFFILIATION
                inner join dbo.WPNONPROFITAFFILIATION SOURCENONPROFITAFFILIATION ON SOURCENONPROFITAFFILIATION.PARTIALHASH = TARGETNONPROFITAFFILIATION.PARTIALHASH
                where SOURCENONPROFITAFFILIATION.WEALTHID = @SOURCEID
                    and TARGETNONPROFITAFFILIATION.WEALTHID = @TARGETID
                    and not (TARGETNONPROFITAFFILIATION.CONFIRMED = 1 or TARGETNONPROFITAFFILIATION.REJECTED = 1)
                    and (SOURCENONPROFITAFFILIATION.CONFIRMED = 1 or SOURCENONPROFITAFFILIATION.REJECTED = 1)
                    and SOURCENONPROFITAFFILIATION.PARTIALHASH <> ''

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

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

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

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

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

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

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