USP_MERGETWOCONSTITUENTS_SECURITIES

            create procedure dbo.USP_MERGETWOCONSTITUENTS_SECURITIES
            (
                @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 TARGETSECURITIES
                set
                    [CONFIRMED] = SOURCESECURITIES.CONFIRMED
                    ,[DATECONFIRMED] = SOURCESECURITIES.DATECONFIRMED
                    ,[CONFIRMEDBYAPPUSERID] = SOURCESECURITIES.CONFIRMEDBYAPPUSERID
                    ,[SOURCE] = SOURCESECURITIES.SOURCE
                    ,[VIID] = SOURCESECURITIES.VIID
                    ,[REVISION] = SOURCESECURITIES.REVISION
                    ,[ORIGINALID] = SOURCESECURITIES.ORIGINALID
                    ,[NEWROW] = SOURCESECURITIES.NEWROW
                    ,[CDATE] = SOURCESECURITIES.CDATE
                    ,[CVALUE] = SOURCESECURITIES.CVALUE
                    ,[CNOTES] = case SOURCESECURITIES.CNOTES when '' then TARGETSECURITIES.CNOTES else TARGETSECURITIES.CNOTES + (case when TARGETSECURITIES.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCESECURITIES.CNOTES end
                    ,[FULLHASH] = SOURCESECURITIES.FULLHASH
                    ,[PARTIALHASH] = SOURCESECURITIES.PARTIALHASH
                    ,[MC] = SOURCESECURITIES.MC
                    ,[FULLNAME] = SOURCESECURITIES.FULLNAME
                    ,[CUSIP] = SOURCESECURITIES.CUSIP
                    ,[FILERID] = SOURCESECURITIES.FILERID
                    ,[RELATIONSHIP] = SOURCESECURITIES.RELATIONSHIP
                    ,[TICKER] = SOURCESECURITIES.TICKER
                    ,[ISSUERNAME] = SOURCESECURITIES.ISSUERNAME
                    ,[REPORTDATEDIRECT] = SOURCESECURITIES.REPORTDATEDIRECT
                    ,[REPORTDATEINDIRECT] = SOURCESECURITIES.REPORTDATEINDIRECT
                    ,[AMOUNTOWNEDDIRECT] = SOURCESECURITIES.AMOUNTOWNEDDIRECT
                    ,[AMOUNTOWNEDINDIRECT] = SOURCESECURITIES.AMOUNTOWNEDINDIRECT
                    ,[PRICEDIRECT] = SOURCESECURITIES.PRICEDIRECT
                    ,[PRICEINDIRECT] = SOURCESECURITIES.PRICEINDIRECT
                    ,[VALUEDIRECT] = SOURCESECURITIES.VALUEDIRECT
                    ,[VALUEINDIRECT] = SOURCESECURITIES.VALUEINDIRECT
                    ,[UPDATEDVALUEDIRECT] = SOURCESECURITIES.UPDATEDVALUEDIRECT
                    ,[UPDATEDVALUEINDIRECT] = SOURCESECURITIES.UPDATEDVALUEINDIRECT
                    ,[UPDATEDDATE] = SOURCESECURITIES.UPDATEDDATE
                    ,[UPDATEDPRICE] = SOURCESECURITIES.UPDATEDPRICE
                    ,[ACTIVE] = SOURCESECURITIES.ACTIVE
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                    ,[REJECTED] = SOURCESECURITIES.REJECTED
                    ,[DATEREJECTED] = SOURCESECURITIES.DATEREJECTED
                    ,[REJECTEDBYAPPUSERID] = SOURCESECURITIES.REJECTEDBYAPPUSERID
                    ,[VIEWED] = SOURCESECURITIES.VIEWED
                    ,[RECURSIVEMATCH] = SOURCESECURITIES.RECURSIVEMATCH
                    ,[SECURITYID] = SOURCESECURITIES.SECURITYID
                    ,[SHARESHELDDATE] = SOURCESECURITIES.SHARESHELDDATE
                    ,[ISHISTORICAL] = SOURCESECURITIES.ISHISTORICAL
                from dbo.WPSECURITIES TARGETSECURITIES
                inner join dbo.WPSECURITIES SOURCESECURITIES ON SOURCESECURITIES.PARTIALHASH = TARGETSECURITIES.PARTIALHASH
                where SOURCESECURITIES.WEALTHID = @SOURCEID
                    and TARGETSECURITIES.WEALTHID = @TARGETID
                    and not (TARGETSECURITIES.CONFIRMED = 1 or TARGETSECURITIES.REJECTED = 1)
                    and (SOURCESECURITIES.CONFIRMED = 1 or SOURCESECURITIES.REJECTED = 1)
                    and SOURCESECURITIES.PARTIALHASH <> ''

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

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

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

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

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

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

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