USP_MERGETASK_CONSTITUENTMAJORGIVING
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEID | uniqueidentifier | IN | |
| @TARGETID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @DUPLICATEPROSPECTPLANRESOLUTION | tinyint | IN | |
| @RESEARCHDETAILS | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_MERGETASK_CONSTITUENTMAJORGIVING
(
@SOURCEID uniqueidentifier,
@TARGETID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@DUPLICATEPROSPECTPLANRESOLUTION tinyint = 0,
@RESEARCHDETAILS bit =0
)
as
set nocount on;
declare @CHANGEDATE datetime = getdate();
-- If the source does not have a prospect record, then
-- there is no need to continue
if exists
(
select top(1) ID
from dbo.PROSPECT
where ID = @SOURCEID
)
begin
-- Next we must determine if the target already has a prospect
-- record
if exists
(
select top(1) ID
from dbo.PROSPECT
where ID = @TARGETID
)
begin
-- If the Target has a prospect record, then we will
-- merge the fields of the source and target records
-- using the rule that a target field's value will
-- be kept unless it is null or holds a default value,
-- in which case the source field's value will be copied
-- to the target's field.
declare @PROSPECTMANAGERFUNDRAISERID uniqueidentifier;
declare @SPROSPECTMANAGERSTARTDATE date;
declare @SPROSPECTMANAGERENDDATE date;
declare @PROSPECTSTATUSCODEID uniqueidentifier;
declare @SRESEARCHSTATUSCONFIRMED bit;
declare @SRESEARCHSUMMARY nvarchar(max);
-- Cache the source prospect record's field values
select
@PROSPECTMANAGERFUNDRAISERID = PROSPECTMANAGERFUNDRAISERID,
@SPROSPECTMANAGERSTARTDATE = PROSPECTMANAGERSTARTDATE,
@SPROSPECTMANAGERENDDATE = PROSPECTMANAGERENDDATE,
@PROSPECTSTATUSCODEID = PROSPECTSTATUSCODEID,
@SRESEARCHSTATUSCONFIRMED = RESEARCHSTATUSCONFIRMED,
@SRESEARCHSUMMARY = RESEARCHSUMMARY
from
dbo.PROSPECT
where
ID = @SOURCEID;
if exists(select 1 from dbo.PROSPECT where ID = @TARGETID and PROSPECTMANAGERFUNDRAISERID is null)
begin
if(@SPROSPECTMANAGERSTARTDATE is not null and @PROSPECTMANAGERFUNDRAISERID is not null)
begin
update dbo.PROSPECTMANAGERHISTORY
set
DATETO = case when (DATEFROM is null or (DATEFROM <= @SPROSPECTMANAGERSTARTDATE)) then @SPROSPECTMANAGERSTARTDATE else DATETO end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
PROSPECTID = @TARGETID
and FUNDRAISERID <> @PROSPECTMANAGERFUNDRAISERID
and (DATETO > @SPROSPECTMANAGERSTARTDATE);
end
end
--No need to proceed if there are no prospect manager history in the source.
if exists(select 1 from dbo.PROSPECTMANAGERHISTORY where PROSPECTID = @SOURCEID)
begin
if exists(select 1 from dbo.PROSPECTMANAGERHISTORY where PROSPECTID = @TARGETID)
begin
--We will only add managers when their is no date overlap else we will keep the target.
update SOURCE
set
PROSPECTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from dbo.PROSPECTMANAGERHISTORY SOURCE
cross apply dbo.PROSPECTMANAGERHISTORY TARGET
left join dbo.PROSPECT on PROSPECT.ID = SOURCE.PROSPECTID
where
SOURCE.PROSPECTID = @SOURCEID
and TARGET.PROSPECTID = @TARGETID
and dbo.UFN_DATES_AREDATESOVERLAPPING(SOURCE.DATEFROM, SOURCE.DATETO, TARGET.DATEFROM, TARGET.DATETO) = 0
and dbo.UFN_PROSPECTMANAGERHISTORY_VALIDENDDATE_2(@TARGETID, SOURCE.FUNDRAISERID, SOURCE.DATETO) = 1;
end
end
-- Update the target's prospect record
update dbo.PROSPECT
set
PROSPECTMANAGERFUNDRAISERID = case when(PROSPECTMANAGERFUNDRAISERID is null) then @PROSPECTMANAGERFUNDRAISERID else PROSPECTMANAGERFUNDRAISERID end,
PROSPECTMANAGERSTARTDATE = case when(PROSPECTMANAGERSTARTDATE is null) then @SPROSPECTMANAGERSTARTDATE else PROSPECTMANAGERSTARTDATE end,
PROSPECTMANAGERENDDATE = case when(PROSPECTMANAGERENDDATE is null and (@SPROSPECTMANAGERENDDATE >= PROSPECTMANAGERSTARTDATE or PROSPECTMANAGERSTARTDATE is null)) then @SPROSPECTMANAGERENDDATE else PROSPECTMANAGERENDDATE end,
PROSPECTSTATUSCODEID = case when(PROSPECTSTATUSCODEID is null) then @PROSPECTSTATUSCODEID else PROSPECTSTATUSCODEID end,
RESEARCHSTATUSCONFIRMED = case when(@RESEARCHDETAILS = 1) then
(case when(RESEARCHSTATUSCONFIRMED = 1) then 1 else @SRESEARCHSTATUSCONFIRMED end)
else RESEARCHSTATUSCONFIRMED end,
RESEARCHSUMMARY = case when(@RESEARCHDETAILS = 1) then
(case when @SRESEARCHSUMMARY = '' then RESEARCHSUMMARY else RESEARCHSUMMARY + (case when RESEARCHSUMMARY = '' then '' else (char(13) + char(10)) end) + @SRESEARCHSUMMARY end)
else RESEARCHSUMMARY end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
ID = @TARGETID;
-- Merge prospect date range records for the target and source constituents
--Bring over any source prospect date range records that don't have date ranges
--that overlap with existing prospect date range records on the target
update dbo.PROSPECTDATERANGE
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where CONSTITUENTID = @SOURCEID
and ID not in
(
select source.ID
from dbo.PROSPECTDATERANGE source
cross apply dbo.PROSPECTDATERANGE target
where target.CONSTITUENTID = @TARGETID
and source.CONSTITUENTID = @SOURCEID
and
(
(target.DATETO between source.DATEFROM and source.DATETO) or
(source.DATETO between target.DATEFROM and target.DATETO) or
(target.DATEFROM between source.DATEFROM and source.DATETO) or
(source.DATEFROM between target.DATEFROM and target.DATETO) or
(target.DATEFROM is null and source.DATEFROM <= target.DATETO) or
(source.DATEFROM is null and target.DATEFROM <= source.DATETO) or
(target.DATETO is null and source.DATETO >= target.DATEFROM) or
(source.DATETO is null and target.DATETO >= source.DATEFROM) or
(source.DATEFROM is null and target.DATEFROM is null) or
(source.DATETO is null and target.DATETO is null) or
(source.DATEFROM is null and source.DATETO is null) or
(target.DATEFROM is null and target.DATETO is null)
)
)
--If there is still a prospect date range record on the source that is "open"
--(i.e. it's DATETO field is null) then make sure the most
--recent prospect date range record on the target is open. This is done
--to prevent "open" prospect date range status of being lost due to
--overlapping date ranges.
if exists
(
select top(1) ID
from dbo.PROSPECTDATERANGE
where CONSTITUENTID = @SOURCEID
and DATETO is null
)
begin
-- Order By clause evaluates NULL as less than any value.
-- Since we prefer NULL to any actual date when looking for
-- the most recent record, we have to look for NULL as a
-- separate search.
declare @openPDRID uniqueidentifier;
select @openPDRID = ID
from dbo.PROSPECTDATERANGE
where CONSTITUENTID = @TARGETID
and DATETO is null;
if @openPDRID is null
begin
-- If no open prospect was found for the Target,
-- then "open" the record with the most recent DATETO field
update dbo.PROSPECTDATERANGE
set DATETO = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID in
(
select top(1) ID
from dbo.PROSPECTDATERANGE
where CONSTITUENTID = @TARGETID
order by DATEFROM desc
)
end
end
end
else
begin
-- Otherwise, the target does not have a prospect record
-- so we will create one by cloning the source's record.
if (@RESEARCHDETAILS = 1)
insert into dbo.PROSPECT
(ID, PROSPECTMANAGERFUNDRAISERID, PROSPECTSTATUSCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, PROSPECTMANAGERSTARTDATE, PROSPECTMANAGERENDDATE,RESEARCHSTATUSCONFIRMED,RESEARCHSUMMARY)
select
@TARGETID, PROSPECTMANAGERFUNDRAISERID, PROSPECTSTATUSCODEID, ADDEDBYID, @CHANGEAGENTID, DATEADDED, @CHANGEDATE, PROSPECTMANAGERSTARTDATE, PROSPECTMANAGERENDDATE,RESEARCHSTATUSCONFIRMED,RESEARCHSUMMARY
from
dbo.PROSPECT
where
ID = @SOURCEID;
else
insert into dbo.PROSPECT
(ID, PROSPECTMANAGERFUNDRAISERID, PROSPECTSTATUSCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, PROSPECTMANAGERSTARTDATE, PROSPECTMANAGERENDDATE)
select
@TARGETID, PROSPECTMANAGERFUNDRAISERID, PROSPECTSTATUSCODEID, ADDEDBYID, @CHANGEAGENTID, DATEADDED, @CHANGEDATE, PROSPECTMANAGERSTARTDATE, PROSPECTMANAGERENDDATE
from
dbo.PROSPECT
where
ID = @SOURCEID;
--Update the source's prospect date range records to point to
-- the target as well
update dbo.PROSPECTDATERANGE
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where CONSTITUENTID = @SOURCEID;
end
--No need to proceed if there are no prospect manager history in the source.
if exists(select 1 from dbo.PROSPECTMANAGERHISTORY where PROSPECTID = @SOURCEID)
begin
--Directly update prospect manager history from source to target.
update HISTORY
set
HISTORY.PROSPECTID = @TARGETID,
HISTORY.CHANGEDBYID = @CHANGEAGENTID,
HISTORY.DATECHANGED = @CHANGEDATE
from dbo.PROSPECTMANAGERHISTORY HISTORY
left join dbo.PROSPECT on PROSPECT.ID = HISTORY.PROSPECTID
where
HISTORY.PROSPECTID = @SOURCEID
and dbo.UFN_PROSPECTMANAGERHISTORY_VALIDENDDATE_2(@TARGETID, HISTORY.FUNDRAISERID, HISTORY.DATETO) = 1;
end
-- Merge source prospect's prospect team members onto target's prospect team
-- unless the target already has an identical team member (same constit id, role code,
-- datefrom, dateto). Don't worry about overlapping date ranges.
update dbo.PROSPECTTEAM
set PROSPECTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where PROSPECTID = @SOURCEID
and ID not in
(
select source.ID
from dbo.PROSPECTTEAM source
cross apply dbo.PROSPECTTEAM target
where source.PROSPECTID = @SOURCEID
and target.PROSPECTID = @TARGETID
and source.MEMBERID = target.MEMBERID
and source.PROSPECTTEAMROLECODEID = target.PROSPECTTEAMROLECODEID
and source.DATEFROM = target.DATEFROM
and (source.DATETO = target.DATETO or (source.DATETO is null and target.DATETO is null))
)
-- Delete any prospect team records that were orphaned when we checked for dupes above
delete from dbo.PROSPECTTEAM where PROSPECTID = @SOURCEID
-- Migrate prospect plans from the source prospect to the target
-- prospect. However, currently a prospect can only be
-- associated with a unique plan name/plan type pair.
if @DUPLICATEPROSPECTPLANRESOLUTION = 0 -- Merge identical plans
begin
-- If the source and target each have plans with both the same name and type,
-- then a suffix will be added to the name to prevent the unique constraint
-- violation.
declare @NEWPLANNAME table
(
ID uniqueidentifier,
NEWNAME nvarchar(100)
);
if exists
(
select SOURCESPROSPECTPLAN.ID
from dbo.PROSPECTPLAN SOURCESPROSPECTPLAN
cross apply dbo.PROSPECTPLAN TARGETSPROSPECTPLAN
where
SOURCESPROSPECTPLAN.PROSPECTID = @SOURCEID
and TARGETSPROSPECTPLAN.PROSPECTID = @TARGETID
and SOURCESPROSPECTPLAN.PROSPECTPLANTYPECODEID = TARGETSPROSPECTPLAN.PROSPECTPLANTYPECODEID
and SOURCESPROSPECTPLAN.NAME = TARGETSPROSPECTPLAN.NAME
)
begin
-- Pull back duplicate plans
declare DUPLICATEPLANCURSOR cursor local fast_forward for
select
SOURCESPROSPECTPLAN.ID,
SOURCESPROSPECTPLAN.NAME,
SOURCESPROSPECTPLAN.PROSPECTPLANTYPECODEID
from dbo.PROSPECTPLAN SOURCESPROSPECTPLAN
cross apply dbo.PROSPECTPLAN TARGETSPROSPECTPLAN
where
SOURCESPROSPECTPLAN.PROSPECTID = @SOURCEID
and TARGETSPROSPECTPLAN.PROSPECTID = @TARGETID
and SOURCESPROSPECTPLAN.PROSPECTPLANTYPECODEID = TARGETSPROSPECTPLAN.PROSPECTPLANTYPECODEID
and SOURCESPROSPECTPLAN.NAME = TARGETSPROSPECTPLAN.NAME
declare
@DUPLICATEPLANID uniqueidentifier,
@DUPLICATEPLANNAME nvarchar(100),
@DUPLICATEPLANPROSPECTPLANTYPECODEID uniqueidentifier;
open DUPLICATEPLANCURSOR;
fetch next from DUPLICATEPLANCURSOR into @DUPLICATEPLANID, @DUPLICATEPLANNAME, @DUPLICATEPLANPROSPECTPLANTYPECODEID;
while @@FETCH_STATUS = 0
begin
declare @SUFFIXCOUNTER int = 0, @PLANNAMEVALID bit = 0;
-- Build a name unique to the prospect/plan-type. Using a do-while loop to avoid duplicating the name building code.
while 1 = 1
begin
set @SUFFIXCOUNTER = @SUFFIXCOUNTER + 1;
declare @SUFFIX nvarchar(15), @NEWNAME nvarchar(100);
set @SUFFIX = ' (' + cast(@SUFFIXCOUNTER as nvarchar(10)) + ')';
set @NEWNAME = substring(@DUPLICATEPLANNAME, 1, 100 - len(@SUFFIX)) + @SUFFIX;
if not exists ( select 1
from dbo.PROSPECTPLAN
where
PROSPECTID = @TARGETID and
PROSPECTPLANTYPECODEID = @DUPLICATEPLANPROSPECTPLANTYPECODEID and
NAME = @NEWNAME
union all
-- Check source plans to make sure we don't choose a new name that is already in use on the source
select 1
from dbo.PROSPECTPLAN
where
PROSPECTID = @SOURCEID and
PROSPECTPLANTYPECODEID = @DUPLICATEPLANPROSPECTPLANTYPECODEID and
NAME = @NEWNAME)
begin
set @PLANNAMEVALID = 1;
break;
end
end
insert into @NEWPLANNAME (ID, NEWNAME)
values (@DUPLICATEPLANID, @NEWNAME);
fetch next from DUPLICATEPLANCURSOR into @DUPLICATEPLANID, @DUPLICATEPLANNAME, @DUPLICATEPLANPROSPECTPLANTYPECODEID;
end
close DUPLICATEPLANCURSOR
deallocate DUPLICATEPLANCURSOR
end
update dbo.PROSPECTPLAN
set
NAME = case
when NEWPLANNAME.ID is null then PROSPECTPLAN.NAME
else NEWPLANNAME.NEWNAME
end,
PROSPECTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from dbo.PROSPECTPLAN
left join @NEWPLANNAME NEWPLANNAME on PROSPECTPLAN.ID = NEWPLANNAME.ID
where PROSPECTID = @SOURCEID;
end
else if @DUPLICATEPROSPECTPLANRESOLUTION = 1 -- Do not merge identical prospect plans
begin
update dbo.PROSPECTPLAN
set PROSPECTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where
PROSPECTID = @SOURCEID and
not exists
(
select 1
from dbo.PROSPECTPLAN SUBPROSPECTPLAN
where
SUBPROSPECTPLAN.PROSPECTID = @TARGETID and
SUBPROSPECTPLAN.NAME = PROSPECTPLAN.NAME and
SUBPROSPECTPLAN.PROSPECTPLANTYPECODEID = PROSPECTPLAN.PROSPECTPLANTYPECODEID
);
end
else -- Do not merge constituents
begin
if exists
(
select top(1) a.ID
from dbo.PROSPECTPLAN a
cross apply dbo.PROSPECTPLAN b
where a.PROSPECTID = @SOURCEID
and b.PROSPECTID = @TARGETID
and a.PROSPECTPLANTYPECODEID = b.PROSPECTPLANTYPECODEID
and a.NAME = b.NAME
)
RAISERROR('These constituents were not merged because they both have prospect plans with the same name and type. Please resolve this conflict and then run the merge again.', 16, 1);
else
update dbo.PROSPECTPLAN
set PROSPECTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where
PROSPECTID = @SOURCEID
end
-- Migrate any planned-gift relationships from the source to
-- the target
update dbo.RELATIONSHIP
set RELATIONSHIPCONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where RELATIONSHIPCONSTITUENTID = @SOURCEID
and ID not in
(
select a.ID
from dbo.RELATIONSHIP a
inner join dbo.RELATIONSHIP b
on a.RECIPROCALCONSTITUENTID = b.RECIPROCALCONSTITUENTID
and a.RELATIONSHIPTYPECODEID = b.RELATIONSHIPTYPECODEID
and a.RECIPROCALTYPECODEID = b.RECIPROCALTYPECODEID
where a.RELATIONSHIPCONSTITUENTID = @SOURCEID
and b.RELATIONSHIPCONSTITUENTID = @TARGETID
)
and ID in
(
select RELATIONSHIPID
from dbo.PLANNEDGIFTRELATIONSHIP
inner join dbo.PLANNEDGIFT
on PLANNEDGIFT.ID = PLANNEDGIFTRELATIONSHIP.PLANNEDGIFTID
where PLANNEDGIFT.CONSTITUENTID = @SOURCEID
)
-- migrate any planned gifts from the source prospect to the target prospect
update dbo.PLANNEDGIFT
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where CONSTITUENTID = @SOURCEID;
--Migrate any beneficiary status from the source prospect to the target prospect
update dbo.PLANNEDGIFTBENEFICIARY
set
BENEFICIARYCONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
BENEFICIARYCONSTITUENTID = @SOURCEID
-- migrate any fundingtype from the source prospect to the target prospect
declare FUNDINGTYPECURSOR cursor local fast_forward for
select
PFI.FUNDINGTYPECODEID,
PFI.INTERESTLEVELCODE,
PFI.COMMENT,
PFI.ID
from dbo.PROSPECTFUNDINGINTEREST PFI
where PROSPECTID = @SOURCEID
declare
@FUNDINGTYPECODEID uniqueidentifier,
@SOURCEPROSPECTFUNDINGINTERESTID uniqueidentifier,
@TARGETPROSPECTFUNDINGINTERESTID uniqueidentifier,
@INTERESTLEVELCODE tinyint,
@COMMENT nvarchar(max);
open FUNDINGTYPECURSOR;
fetch next from FUNDINGTYPECURSOR into @FUNDINGTYPECODEID, @INTERESTLEVELCODE, @COMMENT, @SOURCEPROSPECTFUNDINGINTERESTID;
while @@FETCH_STATUS = 0
begin
select @TARGETPROSPECTFUNDINGINTERESTID = ID
from dbo.PROSPECTFUNDINGINTEREST
where PROSPECTID = @TARGETID and FUNDINGTYPECODEID = @FUNDINGTYPECODEID;
if @@ROWCOUNT > 0
--if fundingtype already exists for target in table PROSPECTFUNDINGINTEREST then update OPPORTUNITYFUNDINGTYPE's source PROSPECTFUNDINGINTERESTID to target one
--and delete the source from first table. As PROSPECTFUNDINGINTEREST can only have unique PROSPECTID+FUNDINGTYPECODEID.
begin
update dbo.OPPORTUNITYFUNDINGTYPE
set
PROSPECTFUNDINGINTERESTID = @TARGETPROSPECTFUNDINGINTERESTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
OPPORTUNITYFUNDINGTYPE.PROSPECTFUNDINGINTERESTID = @SOURCEPROSPECTFUNDINGINTERESTID;
end
else
--if PROSPECTFUNDINGINTEREST does not have FUNDINGTYPECODEID+PROSPECTID for target as for source then migrate fundingtype from source to target.
begin
update dbo.PROSPECTFUNDINGINTEREST
set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE,
PROSPECTID = @TARGETID
where
PROSPECTID = @SOURCEID and FUNDINGTYPECODEID = @FUNDINGTYPECODEID;
end
fetch next from FUNDINGTYPECURSOR into @FUNDINGTYPECODEID, @INTERESTLEVELCODE, @COMMENT, @SOURCEPROSPECTFUNDINGINTERESTID;
end
close FUNDINGTYPECURSOR
deallocate FUNDINGTYPECURSOR
-- Now that all the prospect data has been merged, delete the source
-- prospect
exec dbo.USP_PROSPECT_DELETEBYID_WITHCHANGEAGENTID @SOURCEID, @CHANGEAGENTID;
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.PROSPECTDATERANGE
where CONSTITUENTID = @SOURCEID;
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
-- MODELINGANDPROPENSITY scores are merged in the Constituent wealth and ratings (model scores and ratings) merge task
-- Merge fundraiser records for the target and source constituents
--Bring over any source fundraiser records that don't have date ranges
--that overlap with existing fundraiser records on the target
update dbo.FUNDRAISERDATERANGE
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where CONSTITUENTID = @SOURCEID
and ID not in
(
select source.ID
from dbo.FUNDRAISERDATERANGE source
cross apply dbo.FUNDRAISERDATERANGE target
where target.CONSTITUENTID = @TARGETID
and source.CONSTITUENTID = @SOURCEID
and
(
(target.DATETO between source.DATEFROM and source.DATETO) or
(source.DATETO between target.DATEFROM and target.DATETO) or
(target.DATEFROM between source.DATEFROM and source.DATETO) or
(source.DATEFROM between target.DATEFROM and target.DATETO) or
(target.DATEFROM is null and source.DATEFROM <= target.DATETO) or
(source.DATEFROM is null and target.DATEFROM <= source.DATETO) or
(target.DATETO is null and source.DATETO >= target.DATEFROM) or
(source.DATETO is null and target.DATETO >= source.DATEFROM) or
(source.DATEFROM is null and target.DATEFROM is null) or
(source.DATETO is null and target.DATETO is null) or
(source.DATEFROM is null and source.DATETO is null) or
(target.DATEFROM is null and target.DATETO is null)
)
)
--If there is still a fundraiser record on the source that is "open"
--(i.e. it's DATETO field is null) then make sure the most
--recent fundraiser record on the target is open. This is done
--to prevent "open" fundraiser status of being lost due to
--overlapping date ranges.
if exists
(
select top(1) ID
from dbo.FUNDRAISERDATERANGE
where CONSTITUENTID = @SOURCEID
and DATETO is null
)
begin
-- Order By clause evaluates NULL as less than any value.
-- Since we prefer NULL to any actual date when looking for
-- the most recent record, we have to look for NULL as a
-- separate search.
declare @openFundRaiserID uniqueidentifier;
select @openFundRaiserID = ID
from dbo.FUNDRAISERDATERANGE
where CONSTITUENTID = @TARGETID
and DATETO is null;
if @openFundRaiserID is null
begin
-- If no open fundraiser was found for the Target,
-- then "open" the record with the most recent DATETO field
update dbo.FUNDRAISERDATERANGE
set DATETO = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID in
(
select top(1) ID
from dbo.FUNDRAISERDATERANGE
where CONSTITUENTID = @TARGETID
order by DATEFROM desc
)
end
end
-- Make the target the fundraiser of any plans of which
-- the source is the fundraiser.
update dbo.PROSPECTPLAN
set PRIMARYMANAGERFUNDRAISERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where PRIMARYMANAGERFUNDRAISERID = @SOURCEID;
update dbo.PROSPECTPLAN
set SECONDARYMANAGERFUNDRAISERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where SECONDARYMANAGERFUNDRAISERID = @SOURCEID;
update
SF
set
FUNDRAISERID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
dbo.SECONDARYFUNDRAISER SF
where
FUNDRAISERID = @SOURCEID and
not exists
(
select top 1 1
from
dbo.SECONDARYFUNDRAISER EXISTING
where
EXISTING.FUNDRAISERID = @TARGETID and
EXISTING.PROSPECTPLANID = SF.PROSPECTPLANID and
(
(EXISTING.DATETO between SF.DATEFROM and SF.DATETO) or
(SF.DATETO between EXISTING.DATEFROM and EXISTING.DATETO) or
(EXISTING.DATEFROM between SF.DATEFROM and SF.DATETO) or
(SF.DATEFROM between EXISTING.DATEFROM and EXISTING.DATETO) or
(EXISTING.DATEFROM is null and SF.DATEFROM <= EXISTING.DATETO) or
(SF.DATEFROM is null and EXISTING.DATEFROM <= SF.DATETO) or
(EXISTING.DATETO is null and SF.DATETO >= EXISTING.DATEFROM) or
(SF.DATETO is null and EXISTING.DATETO >= SF.DATEFROM) or
(SF.DATEFROM is null and EXISTING.DATEFROM is null)or
(SF.DATETO is null and EXISTING.DATETO is null) or
(SF.DATEFROM is null and SF.DATETO is null) or
(EXISTING.DATEFROM is null and EXISTING.DATETO is null)
)
);
-- Make the target the manager of any prospects of which the source is the manager
-- 723838: save dates, set to null, set dates again after update
if exists
(
select top(1) ID
from dbo.PROSPECT
where PROSPECTMANAGERFUNDRAISERID = @SOURCEID
)
begin
declare @SAVEDDATE table
(
PROSPECTID uniqueidentifier,
SAVEDSTARTDATE datetime,
SAVEDENDDATE datetime
);
insert into @SAVEDDATE (PROSPECTID, SAVEDSTARTDATE, SAVEDENDDATE)
select ID, PROSPECTMANAGERSTARTDATE, PROSPECTMANAGERENDDATE from dbo.PROSPECT where PROSPECTMANAGERFUNDRAISERID = @SOURCEID;
declare @NEWDATE table
(
PROSPECTID uniqueidentifier,
NEWDATE datetime
);
insert into @NEWDATE (PROSPECTID, NEWDATE)
select PROSPECTID, dateadd(day,1,DATETO) from dbo.PROSPECTMANAGERHISTORY where FUNDRAISERID = @SOURCEID;
if exists
(
select top(1) PROSPECTID from @NEWDATE
)
begin
update P
set P.PROSPECTMANAGERFUNDRAISERID = @TARGETID, P.CHANGEDBYID = @CHANGEAGENTID, P.DATECHANGED = @CHANGEDATE, P.PROSPECTMANAGERSTARTDATE = ND.NEWDATE, P.PROSPECTMANAGERENDDATE = ND.NEWDATE
from dbo.PROSPECT P
left join @NEWDATE ND
on P.ID = ND.PROSPECTID
where P.PROSPECTMANAGERFUNDRAISERID = @SOURCEID;
end
else
begin
update P
set P.PROSPECTMANAGERFUNDRAISERID = @TARGETID, P.CHANGEDBYID = @CHANGEAGENTID, P.DATECHANGED = @CHANGEDATE
from dbo.PROSPECT P
where P.PROSPECTMANAGERFUNDRAISERID = @SOURCEID;
end
update dbo.PROSPECTMANAGERHISTORY
set FUNDRAISERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where FUNDRAISERID = @SOURCEID;
--replace saved dates
update P
set P.PROSPECTMANAGERSTARTDATE = SD.SAVEDSTARTDATE, P.PROSPECTMANAGERENDDATE = SD.SAVEDENDDATE
from dbo.PROSPECT P
left join @SAVEDDATE SD
on P.ID = SD.PROSPECTID
where P.PROSPECTMANAGERFUNDRAISERID = @TARGETID;
end
-- Make the target the fundraiser for any interactions that are
-- associated with a prospect plan (aka steps)
-- THM 02/25/2008 CR294011-021908 Remove target as additional fundraiser
-- before making the target the owner.
delete from dbo.INTERACTIONADDITIONALFUNDRAISER
where INTERACTIONID in (select ID from dbo.INTERACTION where FUNDRAISERID = @SOURCEID and PROSPECTPLANID is not null)
and FUNDRAISERID = @TARGETID;
update dbo.INTERACTION
set FUNDRAISERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where FUNDRAISERID = @SOURCEID and PROSPECTPLANID is not null;
-- Move prospect plan interactions from source to target.
-- Only move interactions associated with plans that have been moved.
update dbo.INTERACTION
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
from dbo.INTERACTION
inner join dbo.PROSPECTPLAN on INTERACTION.PROSPECTPLANID = PROSPECTPLAN.ID
where INTERACTION.CONSTITUENTID = @SOURCEID and PROSPECTPLAN.PROSPECTID = @TARGETID;
-- Make the target the author of any plan step documentation
-- that the source is the author of
update dbo.INTERACTIONATTACHMENT
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where AUTHORID = @SOURCEID
and INTERACTIONID not in
(
select ID from INTERACTION where PROSPECTPLANID is null
)
update dbo.INTERACTIONNOTE
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where AUTHORID = @SOURCEID
and INTERACTIONID not in
(
select ID from INTERACTION where PROSPECTPLANID is null
)
update dbo.INTERACTIONMEDIALINK
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where AUTHORID = @SOURCEID
and INTERACTIONID not in
(
select ID from INTERACTION where PROSPECTPLANID is null
)
-- Make the target the author of any planned gift documentation
-- of which the source is currently the author.
update dbo.PLANNEDGIFTATTACHMENT
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where AUTHORID = @SOURCEID
update dbo.PLANNEDGIFTMEDIALINK
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where AUTHORID = @SOURCEID
update dbo.PLANNEDGIFTNOTE
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where AUTHORID = @SOURCEID
-- Also handle the scenario where the source is related to a
-- planned gift of another prospect. In this case, move these
-- reciprocal relationship id's to the target.
update dbo.RELATIONSHIP
set RECIPROCALCONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where RECIPROCALCONSTITUENTID = @SOURCEID
and ID not in
(
select a.ID
from dbo.RELATIONSHIP a
inner join dbo.RELATIONSHIP b
on a.RELATIONSHIPCONSTITUENTID = b.RELATIONSHIPCONSTITUENTID
and a.RELATIONSHIPTYPECODEID = b.RELATIONSHIPTYPECODEID
and a.RECIPROCALTYPECODEID = b.RECIPROCALTYPECODEID
where a.RECIPROCALCONSTITUENTID = @SOURCEID
and b.RECIPROCALCONSTITUENTID = @TARGETID
)
and ID in
(
select RELATIONSHIPID
from dbo.PLANNEDGIFTRELATIONSHIP
)
-- Migrate any records that have the source as a prospect plan participant
update dbo.PLANPARTICIPANT
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where CONSTITUENTID = @SOURCEID
and ID not in
(
select a.ID
from dbo.PLANPARTICIPANT a
inner join dbo.PLANPARTICIPANT b
on a.PROSPECTPLANID = b.PROSPECTPLANID
where a.CONSTITUENTID = @SOURCEID
and b.CONSTITUENTID = @TARGETID
);
-- Change CONSTITUENTID on opportunity designations
update dbo.OPPORTUNITYDESIGNATION
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where CONSTITUENTID = @SOURCEID
and ID not in
(
select a.ID
from dbo.OPPORTUNITYDESIGNATION a
inner join dbo.OPPORTUNITYDESIGNATION b
on a.OPPORTUNITYID = b.OPPORTUNITYID and a.DESIGNATIONID = b.DESIGNATIONID
where a.CONSTITUENTID = @SOURCEID
and b.CONSTITUENTID = @TARGETID
);
-- Merge source fundraiser's prospect team assignments to target's
-- unless the target already has an identical team assignment (same prospect id, role code,
-- datefrom, dateto). Don't worry about overlapping date ranges.
update dbo.PROSPECTTEAM
set MEMBERID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where MEMBERID = @SOURCEID
and ID not in
(
select source.ID
from dbo.PROSPECTTEAM source
cross apply dbo.PROSPECTTEAM target
where source.MEMBERID = @SOURCEID
and target.MEMBERID = @TARGETID
and source.PROSPECTID = target.PROSPECTID
and source.PROSPECTTEAMROLECODEID = target.PROSPECTTEAMROLECODEID
and source.DATEFROM = target.DATEFROM
and (source.DATETO = target.DATETO or (source.DATETO is null and target.DATETO is null))
)
-- Delete any prospect team records that were orphaned when we checked for dupes above
delete from dbo.PROSPECTTEAM where MEMBERID = @SOURCEID;
return 0;