USP_MERGETASK_CONSTITUENTWEALTH

                create procedure dbo.USP_MERGETASK_CONSTITUENTWEALTH
                (
                    @SOURCEID uniqueidentifier,
                    @TARGETID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier
                )
                as
                    set nocount on;

                    begin try
                        declare @CURRENTDATE datetime = getdate();

                        if not exists (select 1 from dbo.WEALTH where ID = @TARGETID)
                        begin
                            insert into [dbo].[WEALTH]
                            (
                                [ID]
                                ,[WEALTHPROFILEPDF]
                                ,[WEALTHPOINTDATE]
                                ,[ADDEDBYID]
                                ,[CHANGEDBYID]
                                ,[DATEADDED]
                                ,[DATECHANGED]
                                ,[PENDINGSEARCH]
                                ,[REALESTATEASSETS]
                                ,[REALESTATEASSETSCONFIRMED]
                                ,[REALESTATENUMBER]
                                ,[REALESTATENUMBERCONFIRMED]
                                ,[BUSINESSOWNERSHIPASSETS]
                                ,[BUSINESSOWNERSHIPASSETSCONFIRMED]
                                ,[BUSINESSOWNERSHIPNUMBER]
                                ,[BUSINESSOWNERSHIPNUMBERCONFIRMED]
                                ,[SECURITIESASSETS]
                                ,[SECURITIESASSETSCONFIRMED]
                                ,[SECURITIESNUMBER]
                                ,[SECURITIESNUMBERCONFIRMED]
                                ,[AFFLUENCEINDICATORASSETS]
                                ,[AFFLUENCEINDICATORASSETSCONFIRMED]
                                ,[AFFLUENCEINDICATORNUMBER]
                                ,[AFFLUENCEINDICATORNUMBERCONFIRMED]
                                ,[INCOMECOMPENSATIONASSETS]
                                ,[INCOMECOMPENSATIONASSETSCONFIRMED]
                                ,[INCOMECOMPENSATIONNUMBER]
                                ,[INCOMECOMPENSATIONNUMBERCONFIRMED]
                                ,[OTHERASSETS]
                                ,[OTHERASSETSCONFIRMED]
                                ,[OTHERASSETSNUMBER]
                                ,[OTHERASSETSNUMBERCONFIRMED]
                                ,[BIOGRAPHICALNUMBER]
                                ,[BIOGRAPHICALNUMBERCONFIRMED]
                                ,[POLITICALDONATIONNUMBER]
                                ,[POLITICALDONATIONNUMBERCONFIRMED]
                                ,[PHILANTHROPICGIFTNUMBER]
                                ,[PHILANTHROPICGIFTNUMBERCONFIRMED]
                                ,[NONPROFITAFFILIATIONNUMBER]
                                ,[NONPROFITAFFILIATIONNUMBERCONFIRMED]
                                ,[PRIVATEFOUNDATIONNUMBER]
                                ,[PRIVATEFOUNDATIONNUMBERCONFIRMED]
                                ,[SOCIALMEDIADATE])
                            select
                                @TARGETID
                                ,[WEALTHPROFILEPDF]
                                ,[WEALTHPOINTDATE]
                                ,@CHANGEAGENTID
                                ,@CHANGEAGENTID
                                ,@CURRENTDATE
                                ,@CURRENTDATE
                                ,[PENDINGSEARCH]
                                ,[REALESTATEASSETS]
                                ,[REALESTATEASSETSCONFIRMED]
                                ,[REALESTATENUMBER]
                                ,[REALESTATENUMBERCONFIRMED]
                                ,[BUSINESSOWNERSHIPASSETS]
                                ,[BUSINESSOWNERSHIPASSETSCONFIRMED]
                                ,[BUSINESSOWNERSHIPNUMBER]
                                ,[BUSINESSOWNERSHIPNUMBERCONFIRMED]
                                ,[SECURITIESASSETS]
                                ,[SECURITIESASSETSCONFIRMED]
                                ,[SECURITIESNUMBER]
                                ,[SECURITIESNUMBERCONFIRMED]
                                ,[AFFLUENCEINDICATORASSETS]
                                ,[AFFLUENCEINDICATORASSETSCONFIRMED]
                                ,[AFFLUENCEINDICATORNUMBER]
                                ,[AFFLUENCEINDICATORNUMBERCONFIRMED]
                                ,[INCOMECOMPENSATIONASSETS]
                                ,[INCOMECOMPENSATIONASSETSCONFIRMED]
                                ,[INCOMECOMPENSATIONNUMBER]
                                ,[INCOMECOMPENSATIONNUMBERCONFIRMED]
                                ,[OTHERASSETS]
                                ,[OTHERASSETSCONFIRMED]
                                ,[OTHERASSETSNUMBER]
                                ,[OTHERASSETSNUMBERCONFIRMED]
                                ,[BIOGRAPHICALNUMBER]
                                ,[BIOGRAPHICALNUMBERCONFIRMED]
                                ,[POLITICALDONATIONNUMBER]
                                ,[POLITICALDONATIONNUMBERCONFIRMED]
                                ,[PHILANTHROPICGIFTNUMBER]
                                ,[PHILANTHROPICGIFTNUMBERCONFIRMED]
                                ,[NONPROFITAFFILIATIONNUMBER]
                                ,[NONPROFITAFFILIATIONNUMBERCONFIRMED]
                                ,[PRIVATEFOUNDATIONNUMBER]
                                ,[PRIVATEFOUNDATIONNUMBERCONFIRMED]
                                ,[SOCIALMEDIADATE]
                            from [dbo].[WEALTH]
                            where [ID] = @SOURCEID
                        end

                        -- merge real estates

                        exec dbo.USP_MERGETWOCONSTITUENTS_REALESTATE @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        -- merge business ownerships

                        exec dbo.USP_MERGETWOCONSTITUENTS_BUSINESSOWNERSHIP @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        -- merge securities

                        exec dbo.USP_MERGETWOCONSTITUENTS_SECURITIES @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        -- merge affluence indicators

                        exec dbo.USP_MERGETWOCONSTITUENTS_AFFLUENCEINDICATOR @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        -- merge income compensations

                        exec dbo.USP_MERGETWOCONSTITUENTS_INCOMECOMPENSATION @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        -- merge other assets

                        if exists (select 1 from dbo.WPOTHERASSET where WEALTHID = @SOURCEID)
                        begin
                            update dbo.WPOTHERASSET
                            set WEALTHID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
                            where WEALTHID = @SOURCEID;

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

                        -- merge giving summary

                        if not exists (select 1 from dbo.RE7INTEGRATIONGIVINGSUMMARY where ID = @TARGETID)
                        begin
                            insert into dbo.RE7INTEGRATIONGIVINGSUMMARY
                            (
                                [ID]
                                ,[FIRSTGIFTAMOUNT]
                                ,[FIRSTGIFTDATE]
                                ,[LARGESTGIFTAMOUNT]
                                ,[LARGESTGIFTDATE]
                                ,[LATESTGIFTAMOUNT]
                                ,[LATESTGIFTDATE]
                                ,[TOTALGIFTSGIVEN]
                                ,[TOTALGIFTAMOUNT]
                                ,[FIRSTGIFTTYPE]
                                ,[FIRSTGIFTDESIGNATION]
                                ,[LARGESTGIFTTYPE]
                                ,[LARGESTGIFTDESIGNATION]
                                ,[LATESTGIFTTYPE]
                                ,[LATESTGIFTDESIGNATION]
                                ,[ADDEDBYID]
                                ,[CHANGEDBYID]
                                ,[DATEADDED]
                                ,[DATECHANGED]
                            )
                            select
                                @TARGETID
                                ,[FIRSTGIFTAMOUNT]
                                ,[FIRSTGIFTDATE]
                                ,[LARGESTGIFTAMOUNT]
                                ,[LARGESTGIFTDATE]
                                ,[LATESTGIFTAMOUNT]
                                ,[LATESTGIFTDATE]
                                ,[TOTALGIFTSGIVEN]
                                ,[TOTALGIFTAMOUNT]
                                ,[FIRSTGIFTTYPE]
                                ,[FIRSTGIFTDESIGNATION]
                                ,[LARGESTGIFTTYPE]
                                ,[LARGESTGIFTDESIGNATION]
                                ,[LATESTGIFTTYPE]
                                ,[LATESTGIFTDESIGNATION]
                                ,@CHANGEAGENTID
                                ,@CHANGEAGENTID
                                ,@CURRENTDATE
                                ,@CURRENTDATE
                            from dbo.RE7INTEGRATIONGIVINGSUMMARY
                            where ID = @SOURCEID;
                        end

                        -- merge philanthropic gifts

                        exec dbo.USP_MERGETWOCONSTITUENTS_PHILANTHROPICGIFT @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        -- merge political donations

                        exec dbo.USP_MERGETWOCONSTITUENTS_POLITICALDONATION @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        -- merge biographical demographics

                        exec dbo.USP_MERGETWOCONSTITUENTS_BIOGRAPHICALDEMOGRAPHIC @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        -- merge biographicals

                        exec dbo.USP_MERGETWOCONSTITUENTS_BIOGRAPHICAL @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        -- merge non profit affiliations

                        exec dbo.USP_MERGETWOCONSTITUENTS_NONPROFITAFFILIATION @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        -- merge private foundations

                        exec dbo.USP_MERGETWOCONSTITUENTS_PRIVATEFOUNDATION @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        -- merge social summary

                        if not exists (select 1 from dbo.WPSOCIAL where ID = @TARGETID)
                        begin
                            insert into dbo.WPSOCIAL
                            (
                                [ID]
                                ,[ADDEDBYID]
                                ,[CHANGEDBYID]
                                ,[DATEADDED]
                                ,[DATECHANGED]
                                ,[SOURCE]
                                ,[FULLNAME]
                                ,[GENDERCODE]
                                ,[LOCATION]
                                ,[OCCUPATION]
                                ,[COMPANY]
                                ,[SOCIALMEDIARATING]
                                ,[INFLUENCERATING]
                                ,[OUTREACHRATING]
                            )
                            select
                                @TARGETID
                                ,@CHANGEAGENTID
                                ,@CHANGEAGENTID
                                ,@CURRENTDATE
                                ,@CURRENTDATE
                                ,[SOURCE]
                                ,[FULLNAME]
                                ,[GENDERCODE]
                                ,[LOCATION]
                                ,[OCCUPATION]
                                ,[COMPANY]
                                ,[SOCIALMEDIARATING]
                                ,[INFLUENCERATING]
                                ,[OUTREACHRATING]
                            from dbo.WPSOCIAL
                            where ID = @SOURCEID;

                            insert into dbo.WPSOCIALIMAGE
                            (
                                [ID]
                                ,[WPSOCIALID]
                                ,[IMAGEURL]
                                ,[EMAIL]
                                ,[SEQUENCE]
                                ,[ADDEDBYID]
                                ,[CHANGEDBYID]
                                ,[DATEADDED]
                                ,[DATECHANGED]
                                ,[IMAGE]
                                ,[IMAGETHUMBNAIL]
                            )
                            select
                                newid()
                                ,@TARGETID
                                ,[IMAGEURL]
                                ,[EMAIL]
                                ,[SEQUENCE]
                                ,@CHANGEAGENTID
                                ,@CHANGEAGENTID
                                ,@CURRENTDATE
                                ,@CURRENTDATE
                                ,[IMAGE]
                                ,[IMAGETHUMBNAIL]
                            from dbo.WPSOCIALIMAGE
                            where WPSOCIALID = @SOURCEID;

                            insert into dbo.WPSOCIALPROFILE
                            (
                                [ID]
                                ,[WPSOCIALID]
                                ,[LOCATION]
                                ,[OCCUPATION]
                                ,[COMPANY]
                                ,[EMAIL]
                                ,[SEQUENCE]
                                ,[ADDEDBYID]
                                ,[CHANGEDBYID]
                                ,[DATEADDED]
                                ,[DATECHANGED]
                                ,[ISNEW]
                            )
                            select
                                newid()
                                ,@TARGETID
                                ,[LOCATION]
                                ,[OCCUPATION]
                                ,[COMPANY]
                                ,[EMAIL]
                                ,[SEQUENCE]
                                ,@CHANGEAGENTID
                                ,@CHANGEAGENTID
                                ,@CURRENTDATE
                                ,@CURRENTDATE
                                ,[ISNEW]
                            from dbo.WPSOCIALPROFILE
                            where WPSOCIALID = @SOURCEID;
                        end

                        -- merge social media accounts

                        exec dbo.USP_MERGETWOCONSTITUENTS_SOCIALMEDIAACCOUNT @SOURCEID = @SOURCEID, @TARGETID = @TARGETID, @CHANGEAGENTID = @CHANGEAGENTID

                        exec dbo.USP_WEALTHCAPACITY_UPDATE @TARGETID, @CHANGEAGENTID;
                        exec dbo.USP_WEALTHCAPACITY_UPDATE @SOURCEID, @CHANGEAGENTID;
                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                        return 1;
                    end catch

                    return 0;