USP_MERGETASK_CONSTITUENTPROSPECTRESEARCH

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

    declare @CURRENTDATE datetime = getdate();

    -- merge giving capacity


    if not exists (select 1 from dbo.WEALTHCAPACITY where ID = @TARGETID)
    begin
        insert into dbo.WEALTHCAPACITY
        (
            [ID]
            ,[ESTIMATEDWEALTHID]
            ,[OVERALLRATINGCODEID]
            ,[MAJORGIVINGCAPACITYID]
            ,[MAJORGIVINGCAPACITYVALUE]
            ,[CONFIRMED]
            ,[SYSTEMCALCULATED]
            ,[ESTIMATEDWEALTHVALUE]
            ,[WEALTHCAPACITYFORMULAID]
            ,[ADDEDBYID]
            ,[CHANGEDBYID]
            ,[DATEADDED]
            ,[DATECHANGED]
        )
        select
            @TARGETID
            ,[ESTIMATEDWEALTHID]
            ,[OVERALLRATINGCODEID]
            ,[MAJORGIVINGCAPACITYID]
            ,[MAJORGIVINGCAPACITYVALUE]
            ,[CONFIRMED]
            ,[SYSTEMCALCULATED]
            ,[ESTIMATEDWEALTHVALUE]
            ,[WEALTHCAPACITYFORMULAID]
            ,@CHANGEAGENTID
            ,@CHANGEAGENTID
            ,@CURRENTDATE
            ,@CURRENTDATE
        from dbo.WEALTHCAPACITY
        where ID = @SOURCEID

        if exists (select 1 from dbo.WEALTHCAPACITY where ID = @TARGETID and CONFIRMED = 0)
        begin
            exec dbo.USP_WEALTHCAPACITY_UPDATE @TARGETID, @CHANGEAGENTID;
        end
    end;

    -- remove source prospect from research list having target prospect also

    delete SOURCEPROSPECT
    from dbo.RESEARCHGROUPMEMBER SOURCEPROSPECT
    inner join dbo.RESEARCHGROUPMEMBER TARGETPROSPECT ON TARGETPROSPECT.RESEARCHGROUPID = SOURCEPROSPECT.RESEARCHGROUPID
    where SOURCEPROSPECT.CONSTITUENTID = @SOURCEID
        and TARGETPROSPECT.CONSTITUENTID = @TARGETID;

    -- replace source prospect by target prospect if in different research lists

    update dbo.RESEARCHGROUPMEMBER
    set
        [CONSTITUENTID] = @TARGETID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
    where CONSTITUENTID = @SOURCEID;

    -- merge prospects which are in same prospect research requests

    update TARGETPROSPECT
    set
        [NOTES] = TARGETPROSPECT.NOTES + (case when TARGETPROSPECT.NOTES = '' then '' else (char(13) + char(10)) end) + SOURCEPROSPECT.NOTES,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
    from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT TARGETPROSPECT
    inner join dbo.PROSPECTRESEARCHREQUESTCONSTITUENT SOURCEPROSPECT ON SOURCEPROSPECT.PROSPECTRESEARCHREQUESTID = TARGETPROSPECT.PROSPECTRESEARCHREQUESTID
    where SOURCEPROSPECT.CONSTITUENTID = @SOURCEID
        and TARGETPROSPECT.CONSTITUENTID = @TARGETID
        and SOURCEPROSPECT.NOTES <> '';

    -- remove source prospect from prospect research request having target prospect also

    delete SOURCEPROSPECT
    from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT SOURCEPROSPECT
    inner join dbo.PROSPECTRESEARCHREQUESTCONSTITUENT TARGETPROSPECT ON TARGETPROSPECT.PROSPECTRESEARCHREQUESTID = SOURCEPROSPECT.PROSPECTRESEARCHREQUESTID
    where SOURCEPROSPECT.CONSTITUENTID = @SOURCEID
        and TARGETPROSPECT.CONSTITUENTID = @TARGETID;

    -- replace source prospect by target prospect if in different prospect research requests

    update dbo.PROSPECTRESEARCHREQUESTCONSTITUENT
    set
        [CONSTITUENTID] = @TARGETID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
    where CONSTITUENTID = @SOURCEID

    -- update requested by field for the prospect research request requested by source constituent

    update dbo.PROSPECTRESEARCHREQUEST
    set
        [REQUESTEDBYID] = @TARGETID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
    where REQUESTEDBYID = @SOURCEID;

    -- update submitted by field for the prospect research request submitted by source constituent

    update dbo.PROSPECTRESEARCHREQUEST
    set
        [SUBMITTEDBYID] = @TARGETID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
    where SUBMITTEDBYID = @SOURCEID;

    return 0;