USP_MERGETWOCONSTITUENTS_INCOMECOMPENSATION

            create procedure dbo.USP_MERGETWOCONSTITUENTS_INCOMECOMPENSATION
            (
                @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 TARGETINCOMECOMPENSATION
                set
                    [CONFIRMED] = SOURCEINCOMECOMPENSATION.CONFIRMED
                    ,[DATECONFIRMED] = SOURCEINCOMECOMPENSATION.DATECONFIRMED
                    ,[CONFIRMEDBYAPPUSERID] = SOURCEINCOMECOMPENSATION.CONFIRMEDBYAPPUSERID
                    ,[REJECTED] = SOURCEINCOMECOMPENSATION.REJECTED
                    ,[DATEREJECTED] = SOURCEINCOMECOMPENSATION.DATEREJECTED
                    ,[REJECTEDBYAPPUSERID] = SOURCEINCOMECOMPENSATION.REJECTEDBYAPPUSERID
                    ,[SOURCE] = SOURCEINCOMECOMPENSATION.SOURCE
                    ,[MGID] = SOURCEINCOMECOMPENSATION.MGID
                    ,[REVISION] = SOURCEINCOMECOMPENSATION.REVISION
                    ,[ORIGINALID] = SOURCEINCOMECOMPENSATION.ORIGINALID
                    ,[NEWROW] = SOURCEINCOMECOMPENSATION.NEWROW
                    ,[CDATE] = SOURCEINCOMECOMPENSATION.CDATE
                    ,[CVALUE] = SOURCEINCOMECOMPENSATION.CVALUE
                    ,[CNOTES] = case SOURCEINCOMECOMPENSATION.CNOTES when '' then TARGETINCOMECOMPENSATION.CNOTES else TARGETINCOMECOMPENSATION.CNOTES + (case when TARGETINCOMECOMPENSATION.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCEINCOMECOMPENSATION.CNOTES end
                    ,[FULLHASH] = SOURCEINCOMECOMPENSATION.FULLHASH
                    ,[PARTIALHASH] = SOURCEINCOMECOMPENSATION.PARTIALHASH
                    ,[MC] = SOURCEINCOMECOMPENSATION.MC
                    ,[BB_FULLNAME] = SOURCEINCOMECOMPENSATION.BB_FULLNAME
                    ,[COMPANY] = SOURCEINCOMECOMPENSATION.COMPANY
                    ,[LONGTITLE] = SOURCEINCOMECOMPENSATION.LONGTITLE
                    ,[TICKER] = SOURCEINCOMECOMPENSATION.TICKER
                    ,[AGE] = SOURCEINCOMECOMPENSATION.AGE
                    ,[BIOGRAPHY] = SOURCEINCOMECOMPENSATION.BIOGRAPHY
                    ,[OFFICER_DIRECTORFLAG] = SOURCEINCOMECOMPENSATION.OFFICER_DIRECTORFLAG
                    ,[OFFSTARTY] = SOURCEINCOMECOMPENSATION.OFFSTARTY
                    ,[DIRSTARTY] = SOURCEINCOMECOMPENSATION.DIRSTARTY
                    ,[LATESTCOMPENSATIONYEAR] = SOURCEINCOMECOMPENSATION.LATESTCOMPENSATIONYEAR
                    ,[LATESTFISCALYEARSALARY] = SOURCEINCOMECOMPENSATION.LATESTFISCALYEARSALARY
                    ,[LATESTFISCALYEARBONUS] = SOURCEINCOMECOMPENSATION.LATESTFISCALYEARBONUS
                    ,[LATESTFISCALYEAROTHERSHORTTERMCOMPENSATION] = SOURCEINCOMECOMPENSATION.LATESTFISCALYEAROTHERSHORTTERMCOMPENSATION
                    ,[VALUEOFOPTIONSEXERCISED] = SOURCEINCOMECOMPENSATION.VALUEOFOPTIONSEXERCISED
                    ,[VALUEOFOPTIONSUNEXERCISED_EXERCISABLE] = SOURCEINCOMECOMPENSATION.VALUEOFOPTIONSUNEXERCISED_EXERCISABLE
                    ,[VALUEOFOPTIONSUNEXERCISED_UNEXERCISABLE] = SOURCEINCOMECOMPENSATION.VALUEOFOPTIONSUNEXERCISED_UNEXERCISABLE
                    ,[LATESTFISCALYEAROTHERLONGTERMCOMPENSATION] = SOURCEINCOMECOMPENSATION.LATESTFISCALYEAROTHERLONGTERMCOMPENSATION
                    ,[VIEWED] = SOURCEINCOMECOMPENSATION.VIEWED
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                    ,[RECURSIVEMATCH] = SOURCEINCOMECOMPENSATION.RECURSIVEMATCH
                    ,[HISTORICCODE] = SOURCEINCOMECOMPENSATION.HISTORICCODE
                    ,[HISTORICSET] = SOURCEINCOMECOMPENSATION.HISTORICSET
                from dbo.WPINCOMECOMPENSATION TARGETINCOMECOMPENSATION
                inner join dbo.WPINCOMECOMPENSATION SOURCEINCOMECOMPENSATION ON SOURCEINCOMECOMPENSATION.PARTIALHASH = TARGETINCOMECOMPENSATION.PARTIALHASH
                where SOURCEINCOMECOMPENSATION.WEALTHID = @SOURCEID
                    and TARGETINCOMECOMPENSATION.WEALTHID = @TARGETID
                    and not (TARGETINCOMECOMPENSATION.CONFIRMED = 1 or TARGETINCOMECOMPENSATION.REJECTED = 1)
                    and (SOURCEINCOMECOMPENSATION.CONFIRMED = 1 or SOURCEINCOMECOMPENSATION.REJECTED = 1)
                    and SOURCEINCOMECOMPENSATION.PARTIALHASH <> ''

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

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

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

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

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

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

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