USP_MERGETASK_CONSTITUENTPERSONALINFORMATION
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEID | uniqueidentifier | IN | |
| @TARGETID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @NAMECRITERIA | int | IN | |
| @INCLUDEPREFS | bit | IN | |
| @PREFCRITERIA | int | IN | |
| @CONTACTCRITERIA | int | IN | |
| @PRESERVENAMEFIELDS | bit | IN | |
| @DONOTMERGEDECEASEDINFOIFONLYSOURCEISDECEASED | bit | IN | |
| @ORGANIZATIONDETAILSCRITERIA | tinyint | IN | |
| @PRESERVEORGANIZATIONDETAILS | bit | IN | |
| @GROUPDETAILSCRITERIA | tinyint | IN | |
| @PRESERVEGROUPDETAILS | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_MERGETASK_CONSTITUENTPERSONALINFORMATION
(
@SOURCEID uniqueidentifier,
@TARGETID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@NAMECRITERIA int = 0,
@INCLUDEPREFS bit = 1,
@PREFCRITERIA int = 0,
@CONTACTCRITERIA int = 0,
@PRESERVENAMEFIELDS bit = 0,
@DONOTMERGEDECEASEDINFOIFONLYSOURCEISDECEASED bit = 0,
@ORGANIZATIONDETAILSCRITERIA tinyint = 0,
@PRESERVEORGANIZATIONDETAILS bit = 1,
@GROUPDETAILSCRITERIA tinyint = 0,
@PRESERVEGROUPDETAILS bit = 1
)
as
set nocount on;
declare @DATECHANGED datetime = getdate();
declare @TARGETNAME nvarchar(154);
declare @SOURCEISINACTIVE bit;
declare @SOURCEKEYNAME nvarchar(100);
declare @SOURCEKEYNAMEPREFIX nvarchar(50);
declare @SOURCEFIRSTNAME nvarchar(50);
declare @SOURCEMIDDLENAME nvarchar(50);
declare @SOURCEMAIDENNAME nvarchar(100);
declare @SOURCENICKNAME nvarchar(50);
declare @SOURCETITLECODEID uniqueidentifier;
declare @SOURCETITLE2CODEID uniqueidentifier;
declare @SOURCESUFFIXCODEID uniqueidentifier;
declare @SOURCESUFFIX2CODEID uniqueidentifier;
declare @SOURCEGENDERCODE tinyint;
declare @SOURCEBIRTHDATE dbo.UDT_FUZZYDATE;
declare @SOURCEGIVESANONYMOUSLY bit;
declare @SOURCENAME nvarchar(154);
declare @SOURCEWEBADDRESS dbo.UDT_WEBADDRESS;
declare @SOURCEPICTURE varbinary(max);
declare @SOURCEPICTURETHUMBNAIL varbinary(max);
declare @SOURCEMARITALSTATUSCODEID uniqueidentifier;
declare @SOURCEDONOTMAIL bit;
declare @SOURCEDONOTEMAIL bit;
declare @SOURCEDONOTPHONE bit;
declare @SOURCEINDUSTRYCODEID uniqueidentifier;
declare @SOURCENUMEMPLOYEES int;
declare @SOURCENUMSUBSIDIARIES int;
declare @SOURCEISPRIMARY bit;
declare @SOURCEGROUPINCEPTIONDATE datetime;
declare @SOURCEGROUPSTARTDATE datetime;
declare @SOURCEGROUPTYPECODE tinyint;
declare @SOURCEGROUPDESCRIPTION nvarchar(300);
declare @SOURCEGROUPNAMEFORMATFUNCTIONID uniqueidentifier;
declare @SOURCEGROUPTYPEID uniqueidentifier;
declare @SOURCEGENDERCODEID uniqueidentifier;
--Cache the Target record's NAME field
select
@TARGETNAME = NAME
from dbo.CONSTITUENT
where ID = @TARGETID
--Cache the Source record's field values
select
@SOURCEKEYNAME = CONSTITUENT.KEYNAME,
@SOURCEKEYNAMEPREFIX = CONSTITUENT.KEYNAMEPREFIX,
@SOURCEFIRSTNAME = CONSTITUENT.FIRSTNAME,
@SOURCEMIDDLENAME = CONSTITUENT.MIDDLENAME,
@SOURCEMAIDENNAME = CONSTITUENT.MAIDENNAME,
@SOURCENICKNAME = CONSTITUENT.NICKNAME,
@SOURCETITLECODEID = CONSTITUENT.TITLECODEID,
@SOURCETITLE2CODEID = CONSTITUENT.TITLE2CODEID,
@SOURCESUFFIXCODEID = CONSTITUENT.SUFFIXCODEID,
@SOURCESUFFIX2CODEID = CONSTITUENT.SUFFIX2CODEID,
@SOURCEGENDERCODE = CONSTITUENT.GENDERCODE,
@SOURCEBIRTHDATE = CONSTITUENT.BIRTHDATE,
@SOURCEISINACTIVE = CONSTITUENT.ISINACTIVE,
@SOURCEGIVESANONYMOUSLY = CONSTITUENT.GIVESANONYMOUSLY,
@SOURCENAME = CONSTITUENT.NAME,
@SOURCEWEBADDRESS = CONSTITUENT.WEBADDRESS,
@SOURCEPICTURE = CONSTITUENT.PICTURE,
@SOURCEPICTURETHUMBNAIL = CONSTITUENT.PICTURETHUMBNAIL,
@SOURCEMARITALSTATUSCODEID = CONSTITUENT.MARITALSTATUSCODEID,
@SOURCEDONOTMAIL = CONSTITUENT.DONOTMAIL,
@SOURCEDONOTEMAIL = CONSTITUENT.DONOTEMAIL,
@SOURCEDONOTPHONE = CONSTITUENT.DONOTPHONE,
@SOURCEINDUSTRYCODEID = ORGANIZATIONDATA.INDUSTRYCODEID,
@SOURCENUMEMPLOYEES = coalesce(ORGANIZATIONDATA.NUMEMPLOYEES, 0),
@SOURCENUMSUBSIDIARIES = coalesce(ORGANIZATIONDATA.NUMSUBSIDIARIES, 0),
@SOURCEISPRIMARY = coalesce(ORGANIZATIONDATA.ISPRIMARY, 0),
@SOURCEGROUPINCEPTIONDATE = coalesce(GROUPDATA.STARTDATE, GROUPDATA.DATEADDED),
@SOURCEGROUPSTARTDATE = GROUPDATA.STARTDATE,
@SOURCEGROUPTYPECODE = GROUPDATA.GROUPTYPECODE,
@SOURCEGROUPDESCRIPTION = GROUPDATA.DESCRIPTION,
@SOURCEGROUPNAMEFORMATFUNCTIONID = GROUPDATA.NAMEFORMATFUNCTIONID,
@SOURCEGROUPTYPEID = GROUPDATA.GROUPTYPEID,
@SOURCEGENDERCODEID = CONSTITUENT.GENDERCODEID
from dbo.CONSTITUENT
left join dbo.ORGANIZATIONDATA on CONSTITUENT.ID = ORGANIZATIONDATA.ID
left join dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID
where CONSTITUENT.ID = @SOURCEID;
-- Handle Name
declare @nameID uniqueidentifier;
if @NAMECRITERIA = 0
set @nameID = @TARGETID
else if @NAMECRITERIA = 1
set @nameID = @SOURCEID
else
set @nameID = dbo.UFN_CONSTITUENTMERGEPROCESS_DETERMINEMOSTCOMPLETENAME(@SOURCEID, @TARGETID);
declare @ORGANIZATIONID uniqueidentifier;
if @ORGANIZATIONDETAILSCRITERIA = 0
set @ORGANIZATIONID = @TARGETID;
else if @ORGANIZATIONDETAILSCRITERIA = 1
set @ORGANIZATIONID = @SOURCEID;
else
begin
declare @NUMFIELDS_SOURCE integer;
declare @NUMFIELDS_TARGET integer;
select
@NUMFIELDS_SOURCE =
case when INDUSTRYCODEID is null then 0 else 1 end
+ case when NUMEMPLOYEES = 0 then 0 else 1 end
+ case when NUMSUBSIDIARIES = 0 then 0 else 1 end
+ ISPRIMARY
from dbo.ORGANIZATIONDATA
where ID = @SOURCEID;
select
@NUMFIELDS_TARGET =
case when INDUSTRYCODEID is null then 0 else 1 end
+ case when NUMEMPLOYEES = 0 then 0 else 1 end
+ case when NUMSUBSIDIARIES = 0 then 0 else 1 end
+ ISPRIMARY
from dbo.ORGANIZATIONDATA
where ID = @TARGETID;
if coalesce(@NUMFIELDS_TARGET, 0) >= coalesce(@NUMFIELDS_SOURCE, 0)
set @ORGANIZATIONID = @TARGETID;
else
set @ORGANIZATIONID = @SOURCEID;
end
declare @GROUPID uniqueidentifier;
if @GROUPDETAILSCRITERIA = 0 -- Target
set @GROUPID = @TARGETID;
else if @GROUPDETAILSCRITERIA = 1 -- Source
set @GROUPID = @SOURCEID;
else -- Most recent start date
begin
declare @TARGETGROUPSTARTDATE datetime;
select
@TARGETGROUPSTARTDATE = coalesce(STARTDATE, DATEADDED)
from
dbo.GROUPDATA
where
ID = @TARGETID;
if @TARGETGROUPSTARTDATE < @SOURCEGROUPINCEPTIONDATE
set @GROUPID = @SOURCEID;
else
set @GROUPID = @TARGETID;
end
-- Change the name format function for households based on the name criteria. Do it before the name is changed so that the triggers will not overwrite the custom keyname.
update dbo.GROUPDATA
set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED,
NAMEFORMATFUNCTIONID = case when (@nameid = @SOURCEID) then @SOURCEGROUPNAMEFORMATFUNCTIONID else NAMEFORMATFUNCTIONID end
where
ID = @TARGETID;
if @PRESERVENAMEFIELDS = 0 or @PRESERVENAMEFIELDS is null
update CONSTITUENT
set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED,
FIRSTNAME = case when (@nameid = @SOURCEID) then @SOURCEFIRSTNAME else FIRSTNAME end,
KEYNAME = case when (@nameid = @SOURCEID) then @SOURCEKEYNAME else KEYNAME end,
KEYNAMEPREFIX = case when (@nameid = @SOURCEID) then @SOURCEKEYNAMEPREFIX else KEYNAMEPREFIX end,
MIDDLENAME = case when (@nameid = @SOURCEID) then @SOURCEMIDDLENAME else MIDDLENAME end,
MAIDENNAME = case when (@nameid = @SOURCEID) then @SOURCEMAIDENNAME else MAIDENNAME end,
NICKNAME = case when (@nameid = @SOURCEID) then @SOURCENICKNAME else NICKNAME end,
TITLECODEID = case when (@nameid = @SOURCEID) then @SOURCETITLECODEID else TITLECODEID end,
TITLE2CODEID = case when (@nameid = @SOURCEID) then @SOURCETITLE2CODEID else TITLE2CODEID end,
SUFFIXCODEID = case when (@nameid = @SOURCEID) then @SOURCESUFFIXCODEID else SUFFIXCODEID end,
SUFFIX2CODEID = case when (@nameid = @SOURCEID) then @SOURCESUFFIX2CODEID else SUFFIX2CODEID end,
GENDERCODE = case when (GENDERCODE = 0) then @SOURCEGENDERCODE else GENDERCODE end,
BIRTHDATE = case when (BIRTHDATE = '00000000') then @SOURCEBIRTHDATE else BIRTHDATE end,
ISINACTIVE = case when (ISINACTIVE = 0) then @SOURCEISINACTIVE else ISINACTIVE end,
GIVESANONYMOUSLY = case when (GIVESANONYMOUSLY = 0) then @SOURCEGIVESANONYMOUSLY else GIVESANONYMOUSLY end,
WEBADDRESS = case when (WEBADDRESS = '') then @SOURCEWEBADDRESS else WEBADDRESS end,
PICTURE = case when (PICTURE is null) then @SOURCEPICTURE else PICTURE end,
PICTURETHUMBNAIL = case when (PICTURETHUMBNAIL is null) then @SOURCEPICTURETHUMBNAIL else PICTURETHUMBNAIL end,
MARITALSTATUSCODEID = case when (MARITALSTATUSCODEID is null) then @SOURCEMARITALSTATUSCODEID else MARITALSTATUSCODEID end,
DONOTMAIL = case when (DONOTMAIL = 0) then @SOURCEDONOTMAIL else DONOTMAIL end,
DONOTEMAIL = case when (DONOTEMAIL = 0) then @SOURCEDONOTEMAIL else DONOTEMAIL end,
DONOTPHONE = case when (DONOTPHONE = 0) then @SOURCEDONOTPHONE else DONOTPHONE end,
GENDERCODEID = case when GENDERCODEID IS NULL or (GENDERCODEID = (select dbo.UFN_GENDERCODEDEFAULTMAPPING_GETGENDERCODEID(0)) and @SOURCEGENDERCODEID IS NOT NULL) then @SOURCEGENDERCODEID else GENDERCODEID end
where
ID = @TARGETID;
else
update CONSTITUENT
set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED,
FIRSTNAME = case when (@nameid = @SOURCEID) then coalesce(nullif(@SOURCEFIRSTNAME, ''), FIRSTNAME) else coalesce(nullif(FIRSTNAME, ''), @SOURCEFIRSTNAME) end,
KEYNAME = case when (@nameid = @SOURCEID) then coalesce(nullif(@SOURCEKEYNAME, ''), KEYNAME) else coalesce(nullif(KEYNAME, ''), @SOURCEKEYNAME) end,
KEYNAMEPREFIX = case when (@nameid = @SOURCEID) then coalesce(nullif(@SOURCEKEYNAMEPREFIX, ''), KEYNAMEPREFIX) else coalesce(nullif(KEYNAMEPREFIX, ''), @SOURCEKEYNAMEPREFIX) end,
MIDDLENAME = case when (@nameid = @SOURCEID) then coalesce(nullif(@SOURCEMIDDLENAME, ''), MIDDLENAME) else coalesce(nullif(MIDDLENAME, ''), @SOURCEMIDDLENAME) end,
MAIDENNAME = case when (@nameid = @SOURCEID) then coalesce(nullif(@SOURCEMAIDENNAME, ''), MAIDENNAME) else coalesce(nullif(MAIDENNAME, ''), @SOURCEMAIDENNAME) end,
NICKNAME = case when (@nameid = @SOURCEID) then coalesce(nullif(@SOURCENICKNAME, ''), NICKNAME) else coalesce(nullif(NICKNAME, ''), @SOURCENICKNAME) end,
TITLECODEID = case when (@nameid = @SOURCEID) then coalesce(@SOURCETITLECODEID, TITLECODEID) else coalesce(TITLECODEID, @SOURCETITLECODEID) end,
TITLE2CODEID = case when (@nameid = @SOURCEID) then coalesce(@SOURCETITLE2CODEID, TITLE2CODEID) else coalesce(TITLE2CODEID, @SOURCETITLE2CODEID) end,
SUFFIXCODEID = case when (@nameid = @SOURCEID) then coalesce(@SOURCESUFFIXCODEID, SUFFIXCODEID) else coalesce(SUFFIXCODEID, @SOURCESUFFIXCODEID) end,
SUFFIX2CODEID = case when (@nameid = @SOURCEID) then coalesce(@SOURCESUFFIX2CODEID, SUFFIX2CODEID) else coalesce(SUFFIX2CODEID, @SOURCESUFFIX2CODEID) end,
GENDERCODE = case when (GENDERCODE = 0) then @SOURCEGENDERCODE else GENDERCODE end,
BIRTHDATE = case when (BIRTHDATE = '00000000') then @SOURCEBIRTHDATE else BIRTHDATE end,
ISINACTIVE = case when (ISINACTIVE = 0) then @SOURCEISINACTIVE else ISINACTIVE end,
GIVESANONYMOUSLY = case when (GIVESANONYMOUSLY = 0) then @SOURCEGIVESANONYMOUSLY else GIVESANONYMOUSLY end,
WEBADDRESS = case when (WEBADDRESS = '') then @SOURCEWEBADDRESS else WEBADDRESS end,
PICTURE = case when (PICTURE is null) then @SOURCEPICTURE else PICTURE end,
PICTURETHUMBNAIL = case when (PICTURETHUMBNAIL is null) then @SOURCEPICTURETHUMBNAIL else PICTURETHUMBNAIL end,
MARITALSTATUSCODEID = case when (MARITALSTATUSCODEID is null) then @SOURCEMARITALSTATUSCODEID else MARITALSTATUSCODEID end,
DONOTMAIL = case when (DONOTMAIL = 0) then @SOURCEDONOTMAIL else DONOTMAIL end,
DONOTEMAIL = case when (DONOTEMAIL = 0) then @SOURCEDONOTEMAIL else DONOTEMAIL end,
DONOTPHONE = case when (DONOTPHONE = 0) then @SOURCEDONOTPHONE else DONOTPHONE end,
GENDERCODEID = case when GENDERCODEID IS NULL or (GENDERCODEID = (select dbo.UFN_GENDERCODEDEFAULTMAPPING_GETGENDERCODEID(0)) and @SOURCEGENDERCODEID IS NOT NULL) then @SOURCEGENDERCODEID else GENDERCODEID end
where
ID = @TARGETID;
declare @TARGETISORGANIZATION bit;
select @TARGETISORGANIZATION = ISORGANIZATION from dbo.CONSTITUENT where ID = @TARGETID;
if @TARGETISORGANIZATION = 1
begin
update dbo.BATCHCONSTITUENT
set
PRIMARYBUSINESSID = @TARGETID
where
PRIMARYBUSINESSID = @SOURCEID
update dbo.BATCHCONSTITUENTUPDATE
set
BUSINESS_ID = @TARGETID
where
BUSINESS_ID = @SOURCEID
end
update dbo.BATCHPEOPLEFINDER
set
CONSTITUENTID = @TARGETID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where
CONSTITUENTID = @SOURCEID
-- If we were missing an org data row for the target, create it before trying to update it.
if @TARGETISORGANIZATION = 1 and not exists (select 1 from dbo.ORGANIZATIONDATA where ID = @TARGETID)
begin
insert into dbo.ORGANIZATIONDATA
(
[ID],
[INDUSTRYCODEID],
[NUMEMPLOYEES],
[NUMSUBSIDIARIES],
[PARENTCORPID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
) values (
@TARGETID,
null,
0,
0,
null,
@CHANGEAGENTID,
@CHANGEAGENTID,
@DATECHANGED,
@DATECHANGED
);
end
if isnull(@PRESERVEORGANIZATIONDETAILS, 0) = 0
update dbo.ORGANIZATIONDATA
set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED,
INDUSTRYCODEID = case when (@ORGANIZATIONID = @SOURCEID) then @SOURCEINDUSTRYCODEID else INDUSTRYCODEID end,
NUMEMPLOYEES = case when (@ORGANIZATIONID = @SOURCEID) then @SOURCENUMEMPLOYEES else NUMEMPLOYEES end,
NUMSUBSIDIARIES = case when (@ORGANIZATIONID = @SOURCEID) then @SOURCENUMSUBSIDIARIES else NUMSUBSIDIARIES end,
ISPRIMARY = case when (@ORGANIZATIONID = @SOURCEID) then @SOURCEISPRIMARY else ISPRIMARY end
where
ID = @TARGETID;
else
update dbo.ORGANIZATIONDATA
set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED,
INDUSTRYCODEID = case when (@ORGANIZATIONID = @SOURCEID) then coalesce(@SOURCEINDUSTRYCODEID, INDUSTRYCODEID) else coalesce(INDUSTRYCODEID, @SOURCEINDUSTRYCODEID) end,
NUMEMPLOYEES = case when (@ORGANIZATIONID = @SOURCEID) then coalesce(nullif(@SOURCENUMEMPLOYEES, 0), NUMEMPLOYEES) else coalesce(nullif(NUMEMPLOYEES, 0), @SOURCENUMEMPLOYEES) end,
NUMSUBSIDIARIES = case when (@ORGANIZATIONID = @SOURCEID) then coalesce(nullif(@SOURCENUMSUBSIDIARIES, 0), NUMSUBSIDIARIES) else coalesce(nullif(NUMSUBSIDIARIES, 0), @SOURCENUMSUBSIDIARIES) end,
ISPRIMARY = case when (@ORGANIZATIONID = @SOURCEID) then coalesce(nullif(@SOURCEISPRIMARY, 0), ISPRIMARY) else coalesce(nullif(ISPRIMARY, 0), @SOURCEISPRIMARY) end
where
ID = @TARGETID;
--Merge matching gift condition settings as these are part of organization info.
exec dbo.USP_MERGETASK_MATCHINGGIFTCONDITIONS @SOURCEID, @TARGETID, @CHANGEAGENTID, @PRESERVEORGANIZATIONDETAILS;
if isnull(@PRESERVEGROUPDETAILS, 0) = 0
begin
if @GROUPID = @SOURCEID
update dbo.GROUPDATA
set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED,
STARTDATE = @SOURCEGROUPSTARTDATE,
DESCRIPTION = @SOURCEGROUPDESCRIPTION,
GROUPTYPECODE = @SOURCEGROUPTYPECODE,
GROUPTYPEID = @SOURCEGROUPTYPEID
where
ID = @TARGETID;
end
else
begin
update dbo.GROUPDATA
set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED,
STARTDATE = case when (@GROUPID = @SOURCEID) then coalesce(@SOURCEGROUPSTARTDATE, STARTDATE) else coalesce(STARTDATE, @SOURCEGROUPSTARTDATE) end,
DESCRIPTION = case when (@GROUPID = @SOURCEID) then coalesce(nullif(@SOURCEGROUPDESCRIPTION, ''), DESCRIPTION) else coalesce(nullif(DESCRIPTION, ''), @SOURCEGROUPDESCRIPTION) end,
GROUPTYPECODE = case when (@GROUPID = @SOURCEID) then @SOURCEGROUPTYPECODE else GROUPTYPECODE end,
GROUPTYPEID = case when (@GROUPID = @SOURCEID) then @SOURCEGROUPTYPEID else GROUPTYPEID end
where
ID = @TARGETID;
end
--For the personal information merge task, since we don't know if revenue is being merged:
--only merge the origination data if the source has no REVENUEID and the target has no data
declare @SOURCEORIGINATIONSOURCEID uniqueidentifier = null;
declare @SOURCEORIGINCODE tinyint = null;
select
@SOURCEORIGINATIONSOURCEID = INFOSOURCECODEID,
@SOURCEORIGINCODE = ORIGINCODE
from
dbo.CONSTITUENTORIGINATION
where
ID = @SOURCEID
and REVENUEID is null
if @SOURCEORIGINATIONSOURCEID is not null or @SOURCEORIGINCODE <> 0
begin
declare @TARGETORIGINATIONSOURCEID uniqueidentifier = null;
declare @TARGETORIGINATIONREVENUEID uniqueidentifier = null;
declare @TARGETORIGINCODE tinyint = null;
declare @TARGETHASDATA bit = 0;
select
@TARGETHASDATA = 1,
@TARGETORIGINATIONSOURCEID = INFOSOURCECODEID,
@TARGETORIGINCODE = ORIGINCODE,
@TARGETORIGINATIONREVENUEID = REVENUEID
from
dbo.CONSTITUENTORIGINATION
where
ID = @TARGETID
if (@TARGETORIGINATIONSOURCEID is null) and (@TARGETORIGINATIONREVENUEID is null) and (@TARGETORIGINCODE is null or @TARGETORIGINCODE = 0)
begin
if @TARGETHASDATA = 1
update dbo.CONSTITUENTORIGINATION set
INFOSOURCECODEID = @SOURCEORIGINATIONSOURCEID,
ORIGINCODE = coalesce(@SOURCEORIGINCODE,0),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where ID = @TARGETID
else
insert into dbo.CONSTITUENTORIGINATION(ID, INFOSOURCECODEID, ORIGINCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(@TARGETID, @SOURCEORIGINATIONSOURCEID, coalesce(@SOURCEORIGINCODE,0), @CHANGEAGENTID, @CHANGEAGENTID, @DATECHANGED, @DATECHANGED)
end
end
-- Merge volunteer records where the source is the sponsor
-- so that the target will now be the sponsor
update dbo.VOLUNTEER set SPONSORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where SPONSORID = @SOURCEID;
--Update documentation records that were authored by the source
--so that their AuthorID's now point to the target.
update dbo.CONSTITUENTNOTE
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.CONSTITUENTMEDIALINK
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.CONSTITUENTATTACHMENT
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.INTERACTIONATTACHMENT
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.INTERACTIONNOTE
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.INTERACTIONMEDIALINK
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.JOBATTACHMENT
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.JOBMEDIALINK
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.JOBNOTE
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.REVENUEATTACHMENT
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.REVENUEMEDIALINK
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.REVENUENOTE
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.VOLUNTEERATTACHMENT
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.VOLUNTEERMEDIALINK
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.VOLUNTEERNOTE
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.PLANNEDGIFTATTACHMENT
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.PLANNEDGIFTMEDIALINK
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
update dbo.PLANNEDGIFTNOTE
set AUTHORID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where AUTHORID = @SOURCEID
--Move application user link
if not exists(select 1 from dbo.APPUSER where CONSTITUENTID = @TARGETID)
begin
update dbo.APPUSER
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where CONSTITUENTID = @SOURCEID;
end
-- Update attribute records that have a "Constituent" data type
-- that reference the source so that they reference the target
declare @TABLENAME nvarchar(50);
declare @VALUEFIELD nvarchar(50);
declare @SQL nvarchar(1000);
declare @SOURCEIDSTRING nvarchar(36);
declare @TARGETIDSTRING nvarchar(36);
declare @SOURCECONSTITIDSTRING nvarchar(36);
declare @TARGETCONSTITIDSTRING nvarchar(36);
declare @CHANGEAGENTIDSTRING nvarchar(36);
set @CHANGEAGENTIDSTRING = convert(nvarchar(36), @CHANGEAGENTID);
set @SOURCECONSTITIDSTRING = convert(nvarchar(36), @SOURCEID);
set @TARGETCONSTITIDSTRING = convert(nvarchar(36), @TARGETID);
declare ATTRTABLECURSOR cursor local fast_forward for
select
TABLECATALOG.TABLENAME
from ATTRIBUTECATEGORY
inner join TABLECATALOG on TABLECATALOG.ID = ATTRIBUTECATEGORY.TABLECATALOGID
inner join ATTRIBUTERECORDTYPE on ATTRIBUTERECORDTYPE.ID = ATTRIBUTECATEGORY.ATTRIBUTERECORDTYPEID
inner join RECORDTYPE on RECORDTYPE.ID = ATTRIBUTERECORDTYPE.RECORDTYPEID
where
ATTRIBUTECATEGORY.VALUECOLUMNNAME = 'CONSTITUENTVALUEID'
open ATTRTABLECURSOR;
fetch next from ATTRTABLECURSOR into @TABLENAME;
while @@fetch_status = 0
begin
set @SQL = '
update dbo.' + @TABLENAME +'
set CONSTITUENTVALUEID = ''' + @TARGETCONSTITIDSTRING + ''', CHANGEDBYID = ''' + @CHANGEAGENTIDSTRING + ''', DATECHANGED = getdate()
where CONSTITUENTVALUEID = ''' + @SOURCECONSTITIDSTRING + ''';'
exec(@SQL);
fetch next from ATTRTABLECURSOR into @TABLENAME;
end
close ATTRTABLECURSOR;
deallocate ATTRTABLECURSOR;
declare @SOURCEISGROUP bit;
declare @TARGETISGROUP bit;
-- Merge group membership info - if this constituent is a group member (merge members of this group in USP_MERGE_GROUPMEMBERS)
exec dbo.USP_MERGE_GROUPMEMBERSHIP @SOURCEID, @TARGETID, @CHANGEAGENTID, @DATECHANGED;
set @SOURCEISGROUP = dbo.UFN_CONSTITUENT_ISGROUP(@SOURCEID);
set @TARGETISGROUP = dbo.UFN_CONSTITUENT_ISGROUP(@TARGETID);
if (@SOURCEISGROUP <> @TARGETISGROUP)
begin
raiserror('These constituents were not merged because one is a group and the other is not a group.', 16, 1);
end
else if (@SOURCEISGROUP = 1 and @TARGETISGROUP = 1)
begin
exec dbo.USP_MERGE_GROUPMEMBERS @SOURCEID, @TARGETID, @CONTACTCRITERIA, @CHANGEAGENTID, @DATECHANGED;
end
--If both source and target are groups and we are merge mail preferences...
if @SOURCEISGROUP = 1 and @TARGETISGROUP = 1 and @INCLUDEPREFS = 1
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 MAILPREFERENCE
where
ID in(
select mp.ID
from dbo.MAILPREFERENCE mp
inner join dbo.MAILPREFERENCEGROUPCONTACT mpgc on mpgc.MAILPREFERENCEID=mp.ID
inner join dbo.GROUPMEMBER gm on gm.MEMBERID=mpgc.CONSTITUENTID
where mp.CONSTITUENTID=@SOURCEID
and gm.GROUPID=@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 dbo.MAILPREFERENCE mp
inner join dbo.MAILPREFERENCEGROUPCONTACT mpgc on mpgc.MAILPREFERENCEID=mp.ID
inner join dbo.GROUPMEMBER gm on gm.MEMBERID=mpgc.CONSTITUENTID
where mp.CONSTITUENTID=@SOURCEID
and gm.GROUPID=@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 mail preferences on the source that refer to members of the target.
update dbo.MAILPREFERENCE
set
CONSTITUENTID=@TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @DATECHANGED
where
ID in (
select MAILPREFERENCE.ID
from dbo.MAILPREFERENCE
inner join dbo.MAILPREFERENCEGROUPCONTACT on MAILPREFERENCEGROUPCONTACT.MAILPREFERENCEID=MAILPREFERENCE.ID
inner join dbo.GROUPMEMBER on GROUPMEMBER.MEMBERID=MAILPREFERENCEGROUPCONTACT.CONSTITUENTID or (MAILPREFERENCEGROUPCONTACT.USEPRIMARYCONTACT=1 and GROUPMEMBER.ISPRIMARY=1)
where MAILPREFERENCE.CONSTITUENTID=@SOURCEID
and GROUPMEMBER.GROUPID=@TARGETID
) and
dbo.UFN_MAILPREFERENCE_VALIDATEUNIQUE(ID,@TARGETID,MAILTYPECODE,BUSINESSUNITCODEID,CATEGORYCODEID,
EVENTCATEGORYCODEID,SITEID,ACKNOWLEDGEMENTID,CORRESPONDENCEID,PLEDGEREMINDERID,CORRESPONDENCECODEID,PURPOSEID)=1
end
-- Bring over source's deceased information
if exists (select 1 from dbo.DECEASEDCONSTITUENT where ID = @SOURCEID)
begin
if not exists (select 1 from dbo.DECEASEDCONSTITUENT where ID = @TARGETID)
begin
if @DONOTMERGEDECEASEDINFOIFONLYSOURCEISDECEASED = 0
begin
-- Mark the target as deceased using the source's information
insert into dbo.DECEASEDCONSTITUENT
(
ID,
DECEASEDDATE,
DECEASEDSOURCECODEID,
DECEASEDCONFIRMATIONCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@TARGETID,
DECEASEDDATE,
DECEASEDSOURCECODEID,
DECEASEDCONFIRMATIONCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@DATECHANGED,
@DATECHANGED
from dbo.DECEASEDCONSTITUENT
where ID = @SOURCEID
exec dbo.USP_CONSTITUENT_DECEASEFROMRULES @TARGETID, @CHANGEAGENTID
end
end
else
begin
declare
@SOURCEDECEASEDDATE dbo.UDT_FUZZYDATE,
@SOURCEDECEASEDSOURCECODEID uniqueidentifier,
@SOURCEDECEASEDCONFIRMATIONCODE tinyint
select
@SOURCEDECEASEDDATE = DECEASEDDATE,
@SOURCEDECEASEDCONFIRMATIONCODE = DECEASEDCONFIRMATIONCODE,
@SOURCEDECEASEDSOURCECODEID = DECEASEDSOURCECODEID
from dbo.DECEASEDCONSTITUENT
where ID = @SOURCEID
-- Copy any fields that are set on the source to the target when the target's value is blank or less specific
update dbo.DECEASEDCONSTITUENT set
DECEASEDDATE =
case
when DECEASEDCONSTITUENT.DECEASEDDATE = '00000000' then @SOURCEDECEASEDDATE
else DECEASEDCONSTITUENT.DECEASEDDATE
end,
DECEASEDSOURCECODEID =
case
when DECEASEDCONSTITUENT.DECEASEDSOURCECODEID is null then @SOURCEDECEASEDSOURCECODEID
else DECEASEDCONSTITUENT.DECEASEDSOURCECODEID
end,
DECEASEDCONFIRMATIONCODE =
case
when DECEASEDCONSTITUENT.DECEASEDCONFIRMATIONCODE = 0 then @SOURCEDECEASEDCONFIRMATIONCODE
else DECEASEDCONSTITUENT.DECEASEDCONFIRMATIONCODE
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @DATECHANGED
where DECEASEDCONSTITUENT.ID = @TARGETID
end
end
return 0;