USP_MERGETWOCONSTITUENTS_BUSINESSOWNERSHIP

            create procedure dbo.USP_MERGETWOCONSTITUENTS_BUSINESSOWNERSHIP
            (
                @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 TARGETBUSINESSOWNERSHIP
                set
                    [CONFIRMED] = SOURCEBUSINESSOWNERSHIP.CONFIRMED
                    ,[DATECONFIRMED] = SOURCEBUSINESSOWNERSHIP.DATECONFIRMED
                    ,[CONFIRMEDBYAPPUSERID] = SOURCEBUSINESSOWNERSHIP.CONFIRMEDBYAPPUSERID
                    ,[REJECTED] = SOURCEBUSINESSOWNERSHIP.REJECTED
                    ,[DATEREJECTED] = SOURCEBUSINESSOWNERSHIP.DATEREJECTED
                    ,[REJECTEDBYAPPUSERID] = SOURCEBUSINESSOWNERSHIP.REJECTEDBYAPPUSERID
                    ,[SOURCE] = SOURCEBUSINESSOWNERSHIP.SOURCE
                    ,[DBID] = SOURCEBUSINESSOWNERSHIP.DBID
                    ,[REVISION] = SOURCEBUSINESSOWNERSHIP.REVISION
                    ,[ORIGINALID] = SOURCEBUSINESSOWNERSHIP.ORIGINALID
                    ,[NEWROW] = SOURCEBUSINESSOWNERSHIP.NEWROW
                    ,[CDATE] = SOURCEBUSINESSOWNERSHIP.CDATE
                    ,[CVALUE] = SOURCEBUSINESSOWNERSHIP.CVALUE
                    ,[CNOTES] = case SOURCEBUSINESSOWNERSHIP.CNOTES when '' then TARGETBUSINESSOWNERSHIP.CNOTES else TARGETBUSINESSOWNERSHIP.CNOTES + (case when TARGETBUSINESSOWNERSHIP.CNOTES = '' then '' else (char(13) + char(10)) end) + SOURCEBUSINESSOWNERSHIP.CNOTES end
                    ,[FULLHASH] = SOURCEBUSINESSOWNERSHIP.FULLHASH
                    ,[PARTIALHASH] = SOURCEBUSINESSOWNERSHIP.PARTIALHASH
                    ,[MC] = SOURCEBUSINESSOWNERSHIP.MC
                    ,[FULLNAME] = SOURCEBUSINESSOWNERSHIP.FULLNAME
                    ,[TITLE] = SOURCEBUSINESSOWNERSHIP.TITLE
                    ,[LINE1] = SOURCEBUSINESSOWNERSHIP.LINE1
                    ,[CITY] = SOURCEBUSINESSOWNERSHIP.CITY
                    ,[STATE] = SOURCEBUSINESSOWNERSHIP.STATE
                    ,[ZIP] = SOURCEBUSINESSOWNERSHIP.ZIP
                    ,[BIO] = SOURCEBUSINESSOWNERSHIP.BIO
                    ,[COMPANY] = SOURCEBUSINESSOWNERSHIP.COMPANY
                    ,[PHONE] = SOURCEBUSINESSOWNERSHIP.PHONE
                    ,[LINE_OF_BUSINESS_DESCRIPTION] = SOURCEBUSINESSOWNERSHIP.LINE_OF_BUSINESS_DESCRIPTION
                    ,[SALES_VOLUME] = SOURCEBUSINESSOWNERSHIP.SALES_VOLUME
                    ,[YEAR_STARTED] = SOURCEBUSINESSOWNERSHIP.YEAR_STARTED
                    ,[EMPLOYEES_TOTAL] = SOURCEBUSINESSOWNERSHIP.EMPLOYEES_TOTAL
                    ,[SIC_CODE] = SOURCEBUSINESSOWNERSHIP.SIC_CODE
                    ,[DUNS] = SOURCEBUSINESSOWNERSHIP.DUNS
                    ,[PERCENTAGE] = SOURCEBUSINESSOWNERSHIP.PERCENTAGE
                    ,[VALUATION] = SOURCEBUSINESSOWNERSHIP.VALUATION
                    ,[SPOUSEIND] = SOURCEBUSINESSOWNERSHIP.SPOUSEIND
                    ,[HELDCODE] = SOURCEBUSINESSOWNERSHIP.HELDCODE
                    ,[VIEWED] = SOURCEBUSINESSOWNERSHIP.VIEWED
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                    ,[RECURSIVEMATCH] = SOURCEBUSINESSOWNERSHIP.RECURSIVEMATCH
                    ,[MAILINGLINE1] = SOURCEBUSINESSOWNERSHIP.MAILINGLINE1
                    ,[MAILINGCITY] = SOURCEBUSINESSOWNERSHIP.MAILINGCITY
                    ,[MAILINGSTATE] = SOURCEBUSINESSOWNERSHIP.MAILINGSTATE
                    ,[MAILINGZIP] = SOURCEBUSINESSOWNERSHIP.MAILINGZIP
                    ,[HOMELINE1] = SOURCEBUSINESSOWNERSHIP.HOMELINE1
                    ,[HOMECITY] = SOURCEBUSINESSOWNERSHIP.HOMECITY
                    ,[HOMESTATE] = SOURCEBUSINESSOWNERSHIP.HOMESTATE
                    ,[HOMEZIP] = SOURCEBUSINESSOWNERSHIP.HOMEZIP
                    ,[SICNAME] = SOURCEBUSINESSOWNERSHIP.SICNAME
                    ,[YEAROFBIRTH] = SOURCEBUSINESSOWNERSHIP.YEAROFBIRTH
                    ,[GENDERCODE] = SOURCEBUSINESSOWNERSHIP.GENDERCODE
                    ,[RATIO] = SOURCEBUSINESSOWNERSHIP.RATIO
                    ,[WPRELATIONSHIP_BO_ID] = SOURCEBUSINESSOWNERSHIP.WPRELATIONSHIP_BO_ID
                    ,[OWNERSHIPVALUEOVERRIDE] = SOURCEBUSINESSOWNERSHIP.OWNERSHIPVALUEOVERRIDE
                    ,[OWNERSHIPVALUEOVERRIDDEN] = SOURCEBUSINESSOWNERSHIP.OWNERSHIPVALUEOVERRIDDEN
                    ,[SIC_CODE1] = SOURCEBUSINESSOWNERSHIP.SIC_CODE1
                    ,[SIC_CODE2] = SOURCEBUSINESSOWNERSHIP.SIC_CODE2
                    ,[SIC_CODE3] = SOURCEBUSINESSOWNERSHIP.SIC_CODE3
                    ,[SIC_CODE4] = SOURCEBUSINESSOWNERSHIP.SIC_CODE4
                    ,[SIC_CODE5] = SOURCEBUSINESSOWNERSHIP.SIC_CODE5
                    ,[SIC_CODE6] = SOURCEBUSINESSOWNERSHIP.SIC_CODE6
                    ,[RATIO1] = SOURCEBUSINESSOWNERSHIP.RATIO1
                    ,[RATIO2] = SOURCEBUSINESSOWNERSHIP.RATIO2
                    ,[RATIO3] = SOURCEBUSINESSOWNERSHIP.RATIO3
                    ,[RATIO4] = SOURCEBUSINESSOWNERSHIP.RATIO4
                    ,[RATIO5] = SOURCEBUSINESSOWNERSHIP.RATIO5
                    ,[RATIO6] = SOURCEBUSINESSOWNERSHIP.RATIO6
                from dbo.WPBUSINESSOWNERSHIP TARGETBUSINESSOWNERSHIP
                inner join dbo.WPBUSINESSOWNERSHIP SOURCEBUSINESSOWNERSHIP ON SOURCEBUSINESSOWNERSHIP.PARTIALHASH = TARGETBUSINESSOWNERSHIP.PARTIALHASH
                where SOURCEBUSINESSOWNERSHIP.WEALTHID = @SOURCEID
                    and TARGETBUSINESSOWNERSHIP.WEALTHID = @TARGETID
                    and not (TARGETBUSINESSOWNERSHIP.CONFIRMED = 1 or TARGETBUSINESSOWNERSHIP.REJECTED = 1)
                    and (SOURCEBUSINESSOWNERSHIP.CONFIRMED = 1 or SOURCEBUSINESSOWNERSHIP.REJECTED = 1)
                    and SOURCEBUSINESSOWNERSHIP.PARTIALHASH <> ''

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

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

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

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

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

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

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