USP_MERGETASK_CONSTITUENTRELATIONSHIPS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEID | uniqueidentifier | IN | |
| @TARGETID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @PRIMARYBUSINESSCRITERIA | int | IN | |
| @SPOUSECRITERIA | int | IN | |
| @INCLUDEPREFS | bit | IN | |
| @PREFCRITERIA | int | IN | |
| @PARENTORGANIZATIONRELATIONSHIPCRITERIA | int | IN | |
| @MERGEEMPLOYMENTINFORMATION | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_MERGETASK_CONSTITUENTRELATIONSHIPS
(
@SOURCEID uniqueidentifier,
@TARGETID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@PRIMARYBUSINESSCRITERIA int = 0,
@SPOUSECRITERIA int = 0,
@INCLUDEPREFS bit = 1,
@PREFCRITERIA int = 0,
@PARENTORGANIZATIONRELATIONSHIPCRITERIA int = 0,
@MERGEEMPLOYMENTINFORMATION bit = 1
)
as
set nocount on;
begin try
declare @DATECHANGED datetime = getdate();
--Store the indicators for later access
declare @SOURCEPRIMARYBUSINESSID as uniqueidentifier;
select @SOURCEPRIMARYBUSINESSID = ID
from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @SOURCEID and ISPRIMARYBUSINESS = 1;
declare @TARGETPRIMARYBUSINESSID as uniqueidentifier;
select @TARGETPRIMARYBUSINESSID = ID
from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @TARGETID and ISPRIMARYBUSINESS = 1;
declare @SOURCESPOUSEID as uniqueidentifier;
select @SOURCESPOUSEID = ID
from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @SOURCEID and ISSPOUSE = 1;
declare @TARGETSPOUSEID as uniqueidentifier;
select @TARGETSPOUSEID = ID
from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @TARGETID and ISSPOUSE = 1;
declare @SOURCEPRIMARYCONTACTID as uniqueidentifier;
select @SOURCEPRIMARYCONTACTID = ID
from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @SOURCEID and ISPRIMARYCONTACT = 1;
declare @TARGETPRIMARYCONTACTID as uniqueidentifier;
select @TARGETPRIMARYCONTACTID = ID
from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @TARGETID and ISPRIMARYCONTACT = 1;
--Wipe the indicators before the merge
--to keep from violating any constraints
if (select ISORGANIZATION from dbo.CONSTITUENT where CONSTITUENT.ID = @TARGETID) = 0
begin
update dbo.RELATIONSHIP
set ISPRIMARYBUSINESS = 0,
ISSPOUSE = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where (RELATIONSHIPCONSTITUENTID = @SOURCEID or RELATIONSHIPCONSTITUENTID = @TARGETID) and
(ISPRIMARYBUSINESS <> 0 or ISSPOUSE <> 0);
end
-- Matching relationships that exist on both the source and target will not be moved over
-- so they will require special handling
declare @MATCHINGRELATIONSHIPS table (SOURCERELATIONSHIPID uniqueidentifier, TARGETRELATIONSHIPID uniqueidentifier);
insert into @MATCHINGRELATIONSHIPS
select
SOURCERELATIONSHIP.ID, TARGETRELATIONSHIP.ID
from
dbo.RELATIONSHIP as SOURCERELATIONSHIP
inner join
dbo.RELATIONSHIP as TARGETRELATIONSHIP
on TARGETRELATIONSHIP.RECIPROCALCONSTITUENTID = SOURCERELATIONSHIP.RECIPROCALCONSTITUENTID
and TARGETRELATIONSHIP.RELATIONSHIPTYPECODEID = SOURCERELATIONSHIP.RELATIONSHIPTYPECODEID
and TARGETRELATIONSHIP.RECIPROCALTYPECODEID = SOURCERELATIONSHIP.RECIPROCALTYPECODEID
where
SOURCERELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SOURCEID
and TARGETRELATIONSHIP.RELATIONSHIPCONSTITUENTID = @TARGETID
union all
select
SOURCERELATIONSHIP.ID, TARGETRELATIONSHIP.ID
from
dbo.RELATIONSHIP as SOURCERELATIONSHIP
inner join
dbo.RELATIONSHIP as TARGETRELATIONSHIP
on TARGETRELATIONSHIP.RELATIONSHIPCONSTITUENTID = SOURCERELATIONSHIP.RELATIONSHIPCONSTITUENTID
and TARGETRELATIONSHIP.RELATIONSHIPTYPECODEID = SOURCERELATIONSHIP.RELATIONSHIPTYPECODEID
and TARGETRELATIONSHIP.RECIPROCALTYPECODEID = SOURCERELATIONSHIP.RECIPROCALTYPECODEID
where
SOURCERELATIONSHIP.RECIPROCALCONSTITUENTID = @SOURCEID
and TARGETRELATIONSHIP.RECIPROCALCONSTITUENTID = @TARGETID;
--Build table of relationships to change
declare @CHANGEDRELATIONSHIPS table (RELATIONSHIPID uniqueidentifier);
insert into @CHANGEDRELATIONSHIPS
select ID
from dbo.RELATIONSHIP
where @SOURCEID in (RELATIONSHIPCONSTITUENTID, RECIPROCALCONSTITUENTID)
and ID not in (select SOURCERELATIONSHIPID from @MATCHINGRELATIONSHIPS);
-- Store the relationship IDs of those SOURCE relationship records in which the matching gift flag will be cleared.
declare @CONFLICTINGMATCHINGGIFTRELATIONSHIPS as table (
ID uniqueidentifier not null
);
-- @CHANGEDRELATIONSHIPS contains SOURCE relationships to be converted into TARGET relationships.
-- Use it to select those relationships with the matching gift flag enabled.
with SOURCEMATCHINGGIFTRELATIONSHIPS_CTE as (
select
ID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID
from dbo.RELATIONSHIP
inner join @CHANGEDRELATIONSHIPS CR on CR.RELATIONSHIPID = RELATIONSHIP.ID
and ISMATCHINGGIFTRELATIONSHIP = 1
),
-- Select all the TARGET relationships with matching gift flag enabled.
TARGETMATCHINGGIFTRELATIONSHIPS_CTE as (
select
ID,
RELATIONSHIPCONSTITUENTID,
RECIPROCALCONSTITUENTID
from dbo.RELATIONSHIP
where @TARGETID in (RELATIONSHIPCONSTITUENTID, RECIPROCALCONSTITUENTID)
and ISMATCHINGGIFTRELATIONSHIP = 1
)
insert into @CONFLICTINGMATCHINGGIFTRELATIONSHIPS
select
-- Only one of the matched relationships should have the matching flag set (source or target relationship)
-- to avoid the conflict and it would be the target relationship, that is already having the flag enabled.
-- So just store the SOURCE relationship IDs and remove the flag from them before their conversion.
SOURCEMATCHINGGIFTRELATIONSHIPS_CTE.ID
from SOURCEMATCHINGGIFTRELATIONSHIPS_CTE
inner join TARGETMATCHINGGIFTRELATIONSHIPS_CTE
on (TARGETMATCHINGGIFTRELATIONSHIPS_CTE.RECIPROCALCONSTITUENTID = SOURCEMATCHINGGIFTRELATIONSHIPS_CTE.RECIPROCALCONSTITUENTID)
or (TARGETMATCHINGGIFTRELATIONSHIPS_CTE.RELATIONSHIPCONSTITUENTID = SOURCEMATCHINGGIFTRELATIONSHIPS_CTE.RELATIONSHIPCONSTITUENTID);
--wipe this(ISMATCHINGGIFTRELATIONSHIP) indicator before the merge to avoid violating constraint(CK_RELATIONSHIP_MATCHINGGIFTRELATIONSHIPCOUNT)
update RELATIONSHIP
set
ISMATCHINGGIFTRELATIONSHIP = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from dbo.RELATIONSHIP
inner join @CONFLICTINGMATCHINGGIFTRELATIONSHIPS CMR on CMR.ID = RELATIONSHIP.ID;
--Remove emergency contact flag from relationship if target is the reciprocal constituent but is not a student
--Or if the target is the relationship constituent but is a student to avoid violating constraint
update dbo.RELATIONSHIP
set ISEMERGENCYCONTACT = 0, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ISEMERGENCYCONTACT = 1 and
ID in (select RELATIONSHIPID from @CHANGEDRELATIONSHIPS) and
((dbo.UFN_CONSTITUENT_ISSTUDENT(@TARGETID) = 1 and RELATIONSHIPCONSTITUENTID = @SOURCEID) or
(dbo.UFN_CONSTITUENT_ISSTUDENT(@TARGETID) = 0 and RECIPROCALCONSTITUENTID = @SOURCEID))
update dbo.RELATIONSHIP
set RELATIONSHIPCONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where (RELATIONSHIPCONSTITUENTID = @SOURCEID
and ID in (select RELATIONSHIPID from @CHANGEDRELATIONSHIPS))
update dbo.RELATIONSHIP
set RECIPROCALCONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where (RECIPROCALCONSTITUENTID = @SOURCEID
and ID in (select RELATIONSHIPID from @CHANGEDRELATIONSHIPS))
-- Update contact relationship links to sales orders for relationships where the
-- constituent IDs are not being swapped because they are duplicate on the source and target
update dbo.SALESORDER set
CONTACTRELATIONSHIPID = MATCHINGRELATIONSHIPS.TARGETRELATIONSHIPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
from
@MATCHINGRELATIONSHIPS as MATCHINGRELATIONSHIPS
where
MATCHINGRELATIONSHIPS.SOURCERELATIONSHIPID = SALESORDER.CONTACTRELATIONSHIPID;
-- Copy contact fields for duplicate relationships if the source is a contact and the primary isn't
declare @CONTACTCHANGEDRELATIONSHIPS table (SOURCERELATIONSHIPID uniqueidentifier, TARGETRELATIONSHIPID uniqueidentifier);
insert into @CONTACTCHANGEDRELATIONSHIPS (SOURCERELATIONSHIPID, TARGETRELATIONSHIPID)
select
SOURCERELATIONSHIP.ID,
TARGETRELATIONSHIP.ID
from dbo.RELATIONSHIP SOURCERELATIONSHIP
inner join dbo.RELATIONSHIP TARGETRELATIONSHIP on
SOURCERELATIONSHIP.RELATIONSHIPTYPECODEID = TARGETRELATIONSHIP.RELATIONSHIPTYPECODEID and
SOURCERELATIONSHIP.RECIPROCALTYPECODEID = TARGETRELATIONSHIP.RECIPROCALTYPECODEID
where
(
(
SOURCERELATIONSHIP.RELATIONSHIPCONSTITUENTID = @SOURCEID and
TARGETRELATIONSHIP.RELATIONSHIPCONSTITUENTID = @TARGETID and
SOURCERELATIONSHIP.RECIPROCALCONSTITUENTID = TARGETRELATIONSHIP.RECIPROCALCONSTITUENTID
) or
(
SOURCERELATIONSHIP.RECIPROCALCONSTITUENTID = @SOURCEID and
TARGETRELATIONSHIP.RECIPROCALCONSTITUENTID = @TARGETID and
SOURCERELATIONSHIP.RELATIONSHIPCONSTITUENTID = TARGETRELATIONSHIP.RELATIONSHIPCONSTITUENTID
)
) and
SOURCERELATIONSHIP.ISCONTACT = 1 and
TARGETRELATIONSHIP.ISCONTACT = 0;
update dbo.RELATIONSHIP set
ISCONTACT = 1,
CONTACTTYPECODEID = SOURCERELATIONSHIP.CONTACTTYPECODEID,
ISPRIMARYCONTACT = SOURCERELATIONSHIP.ISPRIMARYCONTACT,
DATECHANGED = @DATECHANGED,
CHANGEDBYID = @CHANGEAGENTID
from dbo.RELATIONSHIP
inner join @CONTACTCHANGEDRELATIONSHIPS CONTACTCHANGEDRELATIONSHIPS on RELATIONSHIP.ID = CONTACTCHANGEDRELATIONSHIPS.TARGETRELATIONSHIPID
inner join dbo.RELATIONSHIP SOURCERELATIONSHIP on CONTACTCHANGEDRELATIONSHIPS.SOURCERELATIONSHIPID = SOURCERELATIONSHIP.ID
declare @ADDRESSESCHANGED int;
--Move contact addresses to the target if we moved the relationship
update dbo.ADDRESS
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED,
--clear the primary flag if the target has a primary address
ISPRIMARY = case when exists(select ID from dbo.ADDRESS where CONSTITUENTID = @TARGETID and ISPRIMARY = 1) then 0
else ISPRIMARY end,
RELATIONSHIPID = case when RELATIONSHIPID in (select SOURCERELATIONSHIPID from @CONTACTCHANGEDRELATIONSHIPS) then (select TARGETRELATIONSHIPID from @CONTACTCHANGEDRELATIONSHIPS where SOURCERELATIONSHIPID = ADDRESS.RELATIONSHIPID)
else RELATIONSHIPID end
where CONSTITUENTID = @SOURCEID
and
(
RELATIONSHIPID in (select RELATIONSHIPID from @CHANGEDRELATIONSHIPS) or
RELATIONSHIPID in (select SOURCERELATIONSHIPID from @CONTACTCHANGEDRELATIONSHIPS)
)
set @ADDRESSESCHANGED = @@rowcount;
declare @EMAILADDRESSESCHANGED int;
--Move contact email addresses to the target if we moved the relationship
update dbo.EMAILADDRESS
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED,
--clear the primary flag if the target has a primary address
ISPRIMARY = case when exists(select ID from dbo.EMAILADDRESS where CONSTITUENTID = @TARGETID and ISPRIMARY = 1) then 0
else ISPRIMARY end,
RELATIONSHIPID = case when RELATIONSHIPID in (select SOURCERELATIONSHIPID from @CONTACTCHANGEDRELATIONSHIPS) then (select TARGETRELATIONSHIPID from @CONTACTCHANGEDRELATIONSHIPS where SOURCERELATIONSHIPID = EMAILADDRESS.RELATIONSHIPID)
else RELATIONSHIPID end
where CONSTITUENTID = @SOURCEID
and
(
RELATIONSHIPID in (select RELATIONSHIPID from @CHANGEDRELATIONSHIPS) or
RELATIONSHIPID in (select SOURCERELATIONSHIPID from @CONTACTCHANGEDRELATIONSHIPS)
)
and not exists
(
select ESUB.ID
from dbo.EMAILADDRESS ESUB
where ESUB.CONSTITUENTID = @TARGETID
and ESUB.EMAILADDRESS = EMAILADDRESS.EMAILADDRESS
and (ESUB.EMAILADDRESSTYPECODEID = EMAILADDRESS.EMAILADDRESSTYPECODEID or (ESUB.EMAILADDRESSTYPECODEID is null and EMAILADDRESS.EMAILADDRESSTYPECODEID is null))
);
set @EMAILADDRESSESCHANGED = @@rowcount;
--Remove the relationship ID for remaining relationship email addresses
update dbo.EMAILADDRESS
set RELATIONSHIPID = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where CONSTITUENTID = @SOURCEID
and RELATIONSHIPID in (select RELATIONSHIPID from @CHANGEDRELATIONSHIPS);
--Move contact phones to the target if we moved the relationship
update dbo.PHONE
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED,
--clear the primary flag if the target has a primary address
ISPRIMARY = case when exists(select ID from dbo.PHONE where CONSTITUENTID = @TARGETID and ISPRIMARY = 1) then 0
else ISPRIMARY end,
RELATIONSHIPID = case when RELATIONSHIPID in (select SOURCERELATIONSHIPID from @CONTACTCHANGEDRELATIONSHIPS) then (select TARGETRELATIONSHIPID from @CONTACTCHANGEDRELATIONSHIPS where SOURCERELATIONSHIPID = PHONE.RELATIONSHIPID)
else RELATIONSHIPID end
where CONSTITUENTID = @SOURCEID
and
(
RELATIONSHIPID in (select RELATIONSHIPID from @CHANGEDRELATIONSHIPS) or
RELATIONSHIPID in (select SOURCERELATIONSHIPID from @CONTACTCHANGEDRELATIONSHIPS)
)
and not exists
(
select PSUB.ID
from dbo.PHONE PSUB
where PSUB.CONSTITUENTID = @TARGETID
and PSUB.NUMBER = PHONE.NUMBER
and (PSUB.PHONETYPECODEID = PHONE.PHONETYPECODEID or (PSUB.PHONETYPECODEID is null and PHONE.PHONETYPECODEID is null))
);
--Remove the relationship ID for remaining relationship phone numbers
update dbo.PHONE
set RELATIONSHIPID = null, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where CONSTITUENTID = @SOURCEID
and RELATIONSHIPID in (select RELATIONSHIPID from @CHANGEDRELATIONSHIPS);
--Set the primary business indicator
if @PRIMARYBUSINESSCRITERIA = 0
begin
if @TARGETPRIMARYBUSINESSID is not null
begin
update dbo.RELATIONSHIP
set ISPRIMARYBUSINESS = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @TARGETPRIMARYBUSINESSID
end
else
begin
if @SOURCEPRIMARYBUSINESSID is not null
begin
update dbo.RELATIONSHIP
set ISPRIMARYBUSINESS = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @SOURCEPRIMARYBUSINESSID
end
-- Account for the scenario where the source's
-- primary business relationship was not merged
-- b/c it is a duplicate of one of the target's
-- existing relationships. In this scenario, reset
-- the target's original primary business indicator.
if not exists
(
select top(1) ID
from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @TARGETID
and ISPRIMARYBUSINESS = 1
)
begin
update dbo.RELATIONSHIP
set ISPRIMARYBUSINESS = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @TARGETPRIMARYBUSINESSID;
end
end
end
else
begin
if @SOURCEPRIMARYBUSINESSID is not null
begin
update dbo.RELATIONSHIP
set ISPRIMARYBUSINESS = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @SOURCEPRIMARYBUSINESSID
-- Account for the scenario where the source's
-- primary business relationship was not merged
-- b/c it is a duplicate of one of the target's
-- existing relationships. In this scenario, reset
-- the target's original primary business indicator.
if not exists
(
select top(1) ID
from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @TARGETID
and ISPRIMARYBUSINESS = 1
)
begin
update dbo.RELATIONSHIP
set ISPRIMARYBUSINESS = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @TARGETPRIMARYBUSINESSID;
end
end
else
begin
if @TARGETPRIMARYBUSINESSID is not null
begin
update dbo.RELATIONSHIP
set ISPRIMARYBUSINESS = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @TARGETPRIMARYBUSINESSID
end
end
end
--Set the spouse indicator
if @SPOUSECRITERIA = 0
begin
if @TARGETSPOUSEID is not null
begin
update dbo.RELATIONSHIP
set ISSPOUSE = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @TARGETSPOUSEID
end
else
begin
if @SOURCESPOUSEID is not null
begin
update dbo.RELATIONSHIP
set ISSPOUSE = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @SOURCESPOUSEID
end
-- Account for the scenario where the source's
-- spouse relationship was not merged
-- b/c it is a duplicate of one of the target's
-- existing relationships. In this scenario, reset
-- the target's original spouse indicator.
if not exists
(
select top(1) ID
from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @TARGETID
and ISSPOUSE = 1
)
begin
update dbo.RELATIONSHIP
set ISSPOUSE = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @TARGETSPOUSEID;
end
end
end
else
begin
if @SOURCESPOUSEID is not null
begin
update dbo.RELATIONSHIP
set ISSPOUSE = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @SOURCESPOUSEID
-- Account for the scenario where the source's
-- spouse relationship was not merged
-- b/c it is a duplicate of one of the target's
-- existing relationships. In this scenario, reset
-- the target's original spouse indicator.
if not exists
(
select top(1) ID
from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @TARGETID
and ISSPOUSE = 1
)
begin
update dbo.RELATIONSHIP
set ISSPOUSE = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @TARGETSPOUSEID;
end
end
else
begin
if @TARGETSPOUSEID is not null
begin
update dbo.RELATIONSHIP
set ISSPOUSE = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @TARGETSPOUSEID
end
end
end
-- If the target constituent is in a household, the household name
-- will need to be updated to reflect the new spouse.
exec dbo.USP_HOUSEHOLD_REFRESHNAME @TARGETID, @CHANGEAGENTID
if @INCLUDEPREFS=1
begin
--Make sure the constituents involved are organizations.
--If not, they have no contacts and there is no need to merge mail preferences.
declare @TARGETISORG bit
select @TARGETISORG=ISORGANIZATION
from CONSTITUENT
where id=@TARGETID
if @TARGETISORG=1
begin
declare @SOURCEISORG bit
select @SOURCEISORG=ISORGANIZATION
from CONSTITUENT
where id=@SOURCEID
if @SOURCEISORG=1
begin
--Merge mail preferences
--Due to table constraints, we have to remove
--potential dupes before merging the preferences.
--If the target's preferences supersede,
--delete those the source has that would
--come over, but have a dupe on the target.
if @PREFCRITERIA=0
begin
delete from MAILPREFERENCE
where
ID in (
select mp.ID
from MAILPREFERENCE mp
join MAILPREFERENCEORGCONTACTTYPE mpct on mp.ID=mpct.MAILPREFERENCEID
join RELATIONSHIP r on mpct.CONTACTTYPECODEID=r.CONTACTTYPECODEID or (mpct.USEPRIMARYCONTACT=1 and r.ISPRIMARYCONTACT=1)
where
mp.CONSTITUENTID=@SOURCEID and
r.RELATIONSHIPCONSTITUENTID=@TARGETID
) and
dbo.UFN_MAILPREFERENCE_VALIDATEUNIQUE(ID,@TARGETID,MAILTYPECODE,BUSINESSUNITCODEID,CATEGORYCODEID,
EVENTCATEGORYCODEID,SITEID,ACKNOWLEDGEMENTID,CORRESPONDENCEID,PLEDGEREMINDERID,CORRESPONDENCECODEID,PURPOSEID)=0
end
--If the source's preferences supersede,
--delete those the target has that dupe
--a preference that is going to come over
if @PREFCRITERIA=1
begin
delete targetmp
from MAILPREFERENCE targetmp, MAILPREFERENCE sourcemp
where
targetmp.CONSTITUENTID=@TARGETID and
sourcemp.ID in (
select mp.ID
from MAILPREFERENCE mp
join MAILPREFERENCEORGCONTACTTYPE mpct on mp.ID=mpct.MAILPREFERENCEID
join RELATIONSHIP r on mpct.CONTACTTYPECODEID=r.CONTACTTYPECODEID or (mpct.USEPRIMARYCONTACT=1 and r.ISPRIMARYCONTACT=1)
where
mp.CONSTITUENTID=@SOURCEID and
r.RELATIONSHIPCONSTITUENTID=@TARGETID
) and
((targetmp.MAILTYPECODE = sourcemp.MAILTYPECODE) or (targetmp.MAILTYPECODE is null and sourcemp.MAILTYPECODE is null)) and
((targetmp.BUSINESSUNITCODEID = sourcemp.BUSINESSUNITCODEID) or (targetmp.BUSINESSUNITCODEID is null and sourcemp.BUSINESSUNITCODEID is null)) and
((targetmp.CATEGORYCODEID = sourcemp.CATEGORYCODEID) or (targetmp.CATEGORYCODEID is null and sourcemp.CATEGORYCODEID is null)) and
((targetmp.EVENTCATEGORYCODEID = sourcemp.EVENTCATEGORYCODEID) or (targetmp.EVENTCATEGORYCODEID is null and sourcemp.EVENTCATEGORYCODEID is null)) and
((targetmp.SITEID = sourcemp.SITEID) or (targetmp.SITEID is null and sourcemp.SITEID is null)) and
((targetmp.ACKNOWLEDGEMENTID = sourcemp.ACKNOWLEDGEMENTID) or (targetmp.ACKNOWLEDGEMENTID is null and sourcemp.ACKNOWLEDGEMENTID is null)) and
((targetmp.CORRESPONDENCEID = sourcemp.CORRESPONDENCEID) or (targetmp.CORRESPONDENCEID is null and sourcemp.CORRESPONDENCEID is null)) and
((targetmp.PLEDGEREMINDERID = sourcemp.PLEDGEREMINDERID) or (targetmp.PLEDGEREMINDERID is null and sourcemp.PLEDGEREMINDERID is null)) and
((targetmp.CORRESPONDENCECODEID = sourcemp.CORRESPONDENCECODEID) or (targetmp.CORRESPONDENCECODEID is null and sourcemp.CORRESPONDENCECODEID is null)) and
((targetmp.PURPOSEID = sourcemp.PURPOSEID) or (targetmp.PURPOSEID is null and sourcemp.PURPOSEID is null))
end
--Merge mail preferences that match a contact type on the target organization
update MAILPREFERENCE
set
CONSTITUENTID=@TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where
ID in (
select mp.ID
from MAILPREFERENCE mp
join MAILPREFERENCEORGCONTACTTYPE mpct on mp.ID=mpct.MAILPREFERENCEID
join RELATIONSHIP r on mpct.CONTACTTYPECODEID=r.CONTACTTYPECODEID or (mpct.USEPRIMARYCONTACT=1 and r.ISPRIMARYCONTACT=1)
where
mp.CONSTITUENTID=@SOURCEID and
r.RELATIONSHIPCONSTITUENTID=@TARGETID
) and
dbo.UFN_MAILPREFERENCE_VALIDATEUNIQUE(ID,@TARGETID,MAILTYPECODE,BUSINESSUNITCODEID,CATEGORYCODEID,
EVENTCATEGORYCODEID,SITEID,ACKNOWLEDGEMENTID,CORRESPONDENCEID,PLEDGEREMINDERID,CORRESPONDENCECODEID,PURPOSEID)=1
end
end
else
begin
if @ADDRESSESCHANGED > 0 or @EMAILADDRESSESCHANGED > 0
begin
--Due to table constraints, we have to remove
--potential dupes before merging the preferences.
--If the target's preferences supersede,
--delete those the source has that would
--come over, but have a dupe on the target.
if @PREFCRITERIA = 0
begin
delete from MAILPREFERENCE
where
CONSTITUENTID=@SOURCEID and
(
(
ADDRESSID is not null and
ADDRESSID in (
select ID
from ADDRESS
where CONSTITUENTID=@TARGETID
)
)
or (
EMAILADDRESSID is not null and
EMAILADDRESSID in (
select ID
from dbo.EMAILADDRESS
where CONSTITUENTID=@TARGETID
)
)
)
and dbo.UFN_MAILPREFERENCE_VALIDATEUNIQUE(ID,@TARGETID,MAILTYPECODE,BUSINESSUNITCODEID,CATEGORYCODEID,
EVENTCATEGORYCODEID,SITEID,ACKNOWLEDGEMENTID,CORRESPONDENCEID,PLEDGEREMINDERID,CORRESPONDENCECODEID,PURPOSEID)=0
end
--If the source's preferences supersede,
--delete those the target has that dupe
--a preference that is going to come over
--from the source.
if @PREFCRITERIA = 1
begin
delete targetmp
from MAILPREFERENCE targetmp, MAILPREFERENCE sourcemp
where
targetmp.CONSTITUENTID=@TARGETID and
sourcemp.CONSTITUENTID=@SOURCEID and
(
(
sourcemp.ADDRESSID is not null and
sourcemp.ADDRESSID in (
select ID
from ADDRESS
where CONSTITUENTID=@TARGETID
)
)
or (
sourcemp.EMAILADDRESSID is not null and
sourcemp.EMAILADDRESSID in (
select ID
from dbo.EMAILADDRESS
where CONSTITUENTID=@TARGETID
)
)
) and
((targetmp.MAILTYPECODE = sourcemp.MAILTYPECODE) or (targetmp.MAILTYPECODE is null and sourcemp.MAILTYPECODE is null)) and
((targetmp.BUSINESSUNITCODEID = sourcemp.BUSINESSUNITCODEID) or (targetmp.BUSINESSUNITCODEID is null and sourcemp.BUSINESSUNITCODEID is null)) and
((targetmp.CATEGORYCODEID = sourcemp.CATEGORYCODEID) or (targetmp.CATEGORYCODEID is null and sourcemp.CATEGORYCODEID is null)) and
((targetmp.EVENTCATEGORYCODEID = sourcemp.EVENTCATEGORYCODEID) or (targetmp.EVENTCATEGORYCODEID is null and sourcemp.EVENTCATEGORYCODEID is null)) and
((targetmp.SITEID = sourcemp.SITEID) or (targetmp.SITEID is null and sourcemp.SITEID is null)) and
((targetmp.ACKNOWLEDGEMENTID = sourcemp.ACKNOWLEDGEMENTID) or (targetmp.ACKNOWLEDGEMENTID is null and sourcemp.ACKNOWLEDGEMENTID is null)) and
((targetmp.CORRESPONDENCEID = sourcemp.CORRESPONDENCEID) or (targetmp.CORRESPONDENCEID is null and sourcemp.CORRESPONDENCEID is null)) and
((targetmp.PLEDGEREMINDERID = sourcemp.PLEDGEREMINDERID) or (targetmp.PLEDGEREMINDERID is null and sourcemp.PLEDGEREMINDERID is null)) and
((targetmp.CORRESPONDENCECODEID = sourcemp.CORRESPONDENCECODEID) or (targetmp.CORRESPONDENCECODEID is null and sourcemp.CORRESPONDENCECODEID is null)) and
((targetmp.PURPOSEID = sourcemp.PURPOSEID) or (targetmp.PURPOSEID is null and sourcemp.PURPOSEID is null))
end
--Move preferences whose contact address moved
update MAILPREFERENCE
set
CONSTITUENTID=@TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where
CONSTITUENTID=@SOURCEID and
(
(
ADDRESSID is not null and
ADDRESSID in (
select ID
from ADDRESS
where CONSTITUENTID=@TARGETID
)
)
or (
EMAILADDRESSID is not null and
EMAILADDRESSID in (
select ID
from dbo.EMAILADDRESS
where CONSTITUENTID=@TARGETID
)
)
)
end
end
end
-------------------------
-- Parent Organization --
-------------------------
declare @CONTEXTCACHE varbinary(128);
set @CONTEXTCACHE = CONTEXT_INFO();
declare @MERGEWELLKNOWNGUID uniqueidentifier = '3DB5F72B-2BA0-45A0-890F-24359E3F42A8';
set CONTEXT_INFO @MERGEWELLKNOWNGUID;
declare @SOURCEPARENTCORPID uniqueidentifier;
select @SOURCEPARENTCORPID = PARENTCORPID
from dbo.ORGANIZATIONDATA
where ID = @SOURCEID;
declare @TARGETPARENTCORPID uniqueidentifier;
select @TARGETPARENTCORPID = PARENTCORPID
from dbo.ORGANIZATIONDATA
where ID = @TARGETID;
-- if the source's parent is a child of the target, get rid of the source's parent
if @PARENTORGANIZATIONRELATIONSHIPCRITERIA = 0 and
exists (select 1
from
dbo.ORGANIZATIONDATA
inner join dbo.ORGANIZATIONDATA as PARENTORGANIZATIONDATA on PARENTORGANIZATIONDATA.ID = ORGANIZATIONDATA.PARENTCORPID
where
ORGANIZATIONDATA.ID = @SOURCEID
and PARENTORGANIZATIONDATA.PARENTCORPID = @TARGETID)
begin
update dbo.ORGANIZATIONDATA
set PARENTCORPID = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where ID = @SOURCEID;
set @SOURCEPARENTCORPID = null;
end
if (@PARENTORGANIZATIONRELATIONSHIPCRITERIA = 1 and @SOURCEPARENTCORPID is not null) or @TARGETPARENTCORPID is null
begin
-- if the source's parent has the target as its parent
-- get rid of that organization's parent to avoid a circular reference
update dbo.ORGANIZATIONDATA
set PARENTCORPID = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where PARENTCORPID = @TARGETID and ID = @SOURCEPARENTCORPID;
-- put the source's parent on the target
update dbo.ORGANIZATIONDATA
set PARENTCORPID = @SOURCEPARENTCORPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where ID = @TARGETID;
set @TARGETPARENTCORPID = @SOURCEPARENTCORPID;
end
-- if one of the source's children is the parent of the target, avoid a circular reference
if exists (select 1 from dbo.ORGANIZATIONDATA where PARENTCORPID = @SOURCEID and ID = @TARGETPARENTCORPID)
begin
if @PARENTORGANIZATIONRELATIONSHIPCRITERIA = 1
begin
-- keep source selected, keep source's child relation and get rid of target's parent
update dbo.ORGANIZATIONDATA
set PARENTCORPID = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
ID = @TARGETID;
set @TARGETPARENTCORPID = null;
end
else
-- keep target selected, get rid of source's child relation
update dbo.ORGANIZATIONDATA
set PARENTCORPID = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
PARENTCORPID = @SOURCEID and ID = @TARGETPARENTCORPID;
end
-- anything with the source as a parent should now have target as a parent
update dbo.ORGANIZATIONDATA
set PARENTCORPID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where PARENTCORPID = @SOURCEID and ID <> @TARGETID and (@TARGETPARENTCORPID is null or ID <> @TARGETPARENTCORPID);
---------------------------------
-- Organization Parent History --
---------------------------------
update dbo.ORGANIZATIONPARENTHISTORY
set CHILDCORPID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where CHILDCORPID = @SOURCEID;
update dbo.ORGANIZATIONPARENTHISTORY
set PARENTCORPID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where PARENTCORPID = @SOURCEID;
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
------------------------
-- Employment History --
------------------------
if coalesce(@MERGEEMPLOYMENTINFORMATION, 1) = 1
begin
declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
if exists(
select
1
from
@MATCHINGRELATIONSHIPS MATCHINGRELATIONSHIPS
inner join dbo.RELATIONSHIP SOURCERELATIONSHIP
on SOURCERELATIONSHIP.ID = MATCHINGRELATIONSHIPS.SOURCERELATIONSHIPID
inner join dbo.RELATIONSHIPJOBINFO SOURCERELATIONSHIPJOBINFO
on SOURCERELATIONSHIPJOBINFO.RELATIONSHIPSETID = SOURCERELATIONSHIP.RELATIONSHIPSETID
inner join dbo.RELATIONSHIP TARGETRELATIONSHIP
on TARGETRELATIONSHIP.ID = MATCHINGRELATIONSHIPS.TARGETRELATIONSHIPID
inner join dbo.RELATIONSHIPJOBINFO TARGETRELATIONSHIPJOBINFO
on TARGETRELATIONSHIPJOBINFO.RELATIONSHIPSETID = TARGETRELATIONSHIP.RELATIONSHIPSETID
where
(SOURCERELATIONSHIPJOBINFO.ENDDATE between TARGETRELATIONSHIPJOBINFO.STARTDATE and TARGETRELATIONSHIPJOBINFO.ENDDATE)
or (TARGETRELATIONSHIPJOBINFO.ENDDATE between SOURCERELATIONSHIPJOBINFO.STARTDATE and SOURCERELATIONSHIPJOBINFO.ENDDATE)
or (SOURCERELATIONSHIPJOBINFO.STARTDATE between TARGETRELATIONSHIPJOBINFO.STARTDATE and TARGETRELATIONSHIPJOBINFO.ENDDATE)
or (TARGETRELATIONSHIPJOBINFO.STARTDATE between SOURCERELATIONSHIPJOBINFO.STARTDATE and SOURCERELATIONSHIPJOBINFO.ENDDATE)
or (SOURCERELATIONSHIPJOBINFO.STARTDATE is null and TARGETRELATIONSHIPJOBINFO.STARTDATE <= SOURCERELATIONSHIPJOBINFO.ENDDATE)
or (TARGETRELATIONSHIPJOBINFO.STARTDATE is null and SOURCERELATIONSHIPJOBINFO.STARTDATE <= TARGETRELATIONSHIPJOBINFO.ENDDATE)
or (SOURCERELATIONSHIPJOBINFO.ENDDATE is null and TARGETRELATIONSHIPJOBINFO.ENDDATE >= SOURCERELATIONSHIPJOBINFO.STARTDATE)
or (TARGETRELATIONSHIPJOBINFO.ENDDATE is null and SOURCERELATIONSHIPJOBINFO.ENDDATE >= TARGETRELATIONSHIPJOBINFO.STARTDATE)
or (TARGETRELATIONSHIPJOBINFO.STARTDATE is null and SOURCERELATIONSHIPJOBINFO.STARTDATE is null)
or (TARGETRELATIONSHIPJOBINFO.ENDDATE is null and SOURCERELATIONSHIPJOBINFO.ENDDATE is null)
or (TARGETRELATIONSHIPJOBINFO.STARTDATE is null and TARGETRELATIONSHIPJOBINFO.ENDDATE is null)
or (SOURCERELATIONSHIPJOBINFO.STARTDATE is null and SOURCERELATIONSHIPJOBINFO.ENDDATE is null)
)
begin
raiserror('BBERR_JOBINFO_OVERLAPPINGDATERANGE', 16, 1);
end
-- the relationships on TARGET may not have a relationship set
declare NULLRELATIONSHIPSETIDCURSOR cursor for
select distinct
TARGETRELATIONSHIP.ID
from
@MATCHINGRELATIONSHIPS MATCHINGRELATIONSHIPS
inner join dbo.RELATIONSHIP TARGETRELATIONSHIP
on TARGETRELATIONSHIP.ID = MATCHINGRELATIONSHIPS.TARGETRELATIONSHIPID
-- only update target relationships if their matching source relationship has any relationship job info records
inner join dbo.RELATIONSHIP SOURCERELATIONSHIP
on SOURCERELATIONSHIP.ID = MATCHINGRELATIONSHIPS.SOURCERELATIONSHIPID
left join dbo.RELATIONSHIPJOBINFO SOURCERELATIONSHIPJOBINFO
on SOURCERELATIONSHIPJOBINFO.RELATIONSHIPSETID = SOURCERELATIONSHIP.RELATIONSHIPSETID
where
TARGETRELATIONSHIP.RELATIONSHIPSETID is null
and SOURCERELATIONSHIPJOBINFO.ID is not null;
declare @RELATIONSHIPID uniqueidentifier = null;
open NULLRELATIONSHIPSETIDCURSOR;
fetch next from NULLRELATIONSHIPSETIDCURSOR into @RELATIONSHIPID;
while @@FETCH_STATUS = 0
begin
exec dbo.USP_RELATIONSHIPSET_RETROCREATE
@RELATIONSHIPID = @RELATIONSHIPID,
@CHANGEAGENTID = @CHANGEAGENTID;
fetch next from NULLRELATIONSHIPSETIDCURSOR into @RELATIONSHIPID;
end
close NULLRELATIONSHIPSETIDCURSOR;
deallocate NULLRELATIONSHIPSETIDCURSOR;
-- move the job info from the source relationship set to the target relationship set
update dbo.RELATIONSHIPJOBINFO
set
RELATIONSHIPSETID = CHANGINGJOBINFO.TARGETRELATIONSHIPSETID,
DATECHANGED = @DATECHANGED,
CHANGEDBYID = @CHANGEAGENTID
from
dbo.RELATIONSHIPJOBINFO
inner join (
select distinct
SOURCERELATIONSHIPJOBINFO.ID SOURCERELATIONSHIPJOBINFOID,
TARGETRELATIONSHIP.RELATIONSHIPSETID TARGETRELATIONSHIPSETID
from
@MATCHINGRELATIONSHIPS MATCHINGRELATIONSHIPS
inner join dbo.RELATIONSHIP SOURCERELATIONSHIP
on SOURCERELATIONSHIP.ID = MATCHINGRELATIONSHIPS.SOURCERELATIONSHIPID
inner join dbo.RELATIONSHIPJOBINFO SOURCERELATIONSHIPJOBINFO
on SOURCERELATIONSHIPJOBINFO.RELATIONSHIPSETID = SOURCERELATIONSHIP.RELATIONSHIPSETID
inner join dbo.RELATIONSHIP TARGETRELATIONSHIP
on TARGETRELATIONSHIP.ID = MATCHINGRELATIONSHIPS.TARGETRELATIONSHIPID
group by
SOURCERELATIONSHIPJOBINFO.ID,
TARGETRELATIONSHIP.RELATIONSHIPSETID
) CHANGINGJOBINFO
on RELATIONSHIPJOBINFO.ID = CHANGINGJOBINFO.SOURCERELATIONSHIPJOBINFOID;
end
-----------------
-- Final check --
-----------------
--make sure there is only one primary contact after merge.
if @SOURCEPRIMARYCONTACTID is not null and @TARGETPRIMARYCONTACTID is not null
begin
update dbo.RELATIONSHIP
set ISPRIMARYCONTACT = 0, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where ID = @SOURCEPRIMARYCONTACTID
and RELATIONSHIPCONSTITUENTID = @TARGETID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;