USP_MERGETASK_CONSTITUENTADDRESS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SOURCEID | uniqueidentifier | IN | |
| @TARGETID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @PRIMARYCRITERIA | int | IN | |
| @DELETEDUPES | bit | IN | |
| @INCLUDEPREFS | bit | IN | |
| @PREFCRITERIA | int | IN |
Definition
Copy
CREATE procedure dbo.USP_MERGETASK_CONSTITUENTADDRESS
(
@SOURCEID uniqueidentifier,
@TARGETID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@PRIMARYCRITERIA int = 0,
@DELETEDUPES bit = 0,
@INCLUDEPREFS bit = 1,
@PREFCRITERIA int = 0
)
as
set nocount on;
begin try
declare @CHANGEDATE datetime = getdate();
--Store the primary address indicators for later access
declare @TARGETPRIMARYID as uniqueidentifier;
select @TARGETPRIMARYID = ID
from dbo.ADDRESS
where CONSTITUENTID = @TARGETID and ISPRIMARY = 1;
declare @SOURCEPRIMARYID as uniqueidentifier;
select @SOURCEPRIMARYID = ID
from dbo.ADDRESS
where CONSTITUENTID = @SOURCEID and ISPRIMARY = 1;
--Wipe the primary address indicators before the merge
--to keep from violating any constraints
update dbo.ADDRESS
set ISPRIMARY = 0, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID in (@SOURCEPRIMARYID, @TARGETPRIMARYID)
and RELATIONSHIPID is null;
--build a table of all the addresses to change
declare @CHANGEDADDRESSES table (ID uniqueidentifier);
declare @DUPLICATEADDRESSES table (SOURCE_ADDRESSID uniqueidentifier, TARGET_ADDRESSID uniqueidentifier,
SOURCE_DONOTMAIL bit, SOURCE_DONOTMAILREASONCODEID uniqueidentifier, SOURCE_ISCONFIDENTIAL bit);
if @DELETEDUPES = 0
begin
insert @CHANGEDADDRESSES
select ID from dbo.ADDRESS
where CONSTITUENTID = @SOURCEID
and RELATIONSHIPID is null;
end
else
begin
insert into @DUPLICATEADDRESSES
select
SOURCE_ADDRESS.ID,
TARGET_ADDRESS.ID,
SOURCE_ADDRESS.DONOTMAIL,
SOURCE_ADDRESS.DONOTMAILREASONCODEID,
SOURCE_ADDRESS.ISCONFIDENTIAL
from
dbo.ADDRESS SOURCE_ADDRESS
inner join
dbo.ADDRESS TARGET_ADDRESS on TARGET_ADDRESS.CONSTITUENTID = @TARGETID
where
SOURCE_ADDRESS.CONSTITUENTID = @SOURCEID and
SOURCE_ADDRESS.RELATIONSHIPID is null and
SOURCE_ADDRESS.COUNTRYID = TARGET_ADDRESS.COUNTRYID and
((SOURCE_ADDRESS.STATEID = TARGET_ADDRESS.STATEID) or (SOURCE_ADDRESS.STATEID is null and TARGET_ADDRESS.STATEID is null)) and
SOURCE_ADDRESS.ADDRESSBLOCK = TARGET_ADDRESS.ADDRESSBLOCK and
SOURCE_ADDRESS.CITY = TARGET_ADDRESS.CITY and
SOURCE_ADDRESS.POSTCODE = TARGET_ADDRESS.POSTCODE and
((SOURCE_ADDRESS.ADDRESSTYPECODEID = TARGET_ADDRESS.ADDRESSTYPECODEID) or (SOURCE_ADDRESS.ADDRESSTYPECODEID is null and TARGET_ADDRESS.ADDRESSTYPECODEID is null)) and
SOURCE_ADDRESS.CART = TARGET_ADDRESS.CART and
SOURCE_ADDRESS.DPC = TARGET_ADDRESS.DPC and
SOURCE_ADDRESS.LOT = TARGET_ADDRESS.LOT
-- Omit redundant addresses
insert @CHANGEDADDRESSES
select ID from dbo.ADDRESS
where CONSTITUENTID = @SOURCEID
and RELATIONSHIPID is null
and ID not in (select SOURCE_ADDRESSID from @DUPLICATEADDRESSES);
--Update some specific items that wouldn't otherwise be carried over on duplicate addresses
update TARGET set
DONOTMAIL =
case
when TARGET.DONOTMAIL = 1
then 1
when exists(select 1 from @DUPLICATEADDRESSES DUPE where DUPE.TARGET_ADDRESSID = TARGET.ID and DUPE.SOURCE_DONOTMAIL = 1)
then 1
else
0
end,
DONOTMAILREASONCODEID =
case
when TARGET.DONOTMAILREASONCODEID is not null
then TARGET.DONOTMAILREASONCODEID
else
(select top 1 DUPE.SOURCE_DONOTMAILREASONCODEID from @DUPLICATEADDRESSES DUPE where DUPE.TARGET_ADDRESSID = TARGET.ID order by DUPE.SOURCE_DONOTMAILREASONCODEID desc)
end,
ISCONFIDENTIAL =
case
when TARGET.ISCONFIDENTIAL = 1
then 1
when exists(select 1 from @DUPLICATEADDRESSES DUPE where DUPE.TARGET_ADDRESSID = TARGET.ID and DUPE.SOURCE_ISCONFIDENTIAL = 1)
then 1
else
0
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
dbo.ADDRESS TARGET
where
TARGET.CONSTITUENTID = @TARGETID;
-- if omitted addresses are in a committed Address Update batch, we can just delete that row from the batch
delete dbo.BATCHCONSTITUENTADDRESSUPDATE
from
dbo.BATCHCONSTITUENTADDRESSUPDATE
inner join dbo.ADDRESS
on ADDRESS.ID = BATCHCONSTITUENTADDRESSUPDATE.ADDRESSID
where
ADDRESS.CONSTITUENTID = @SOURCEID
and ADDRESS.RELATIONSHIPID is null
and ADDRESS.ID not in (select ID from @CHANGEDADDRESSES)
end
--move all of the source constit's addresses over to
--the target constit
update dbo.ADDRESS
set CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ADDRESS.ID in (select ID from @CHANGEDADDRESSES);
--Select the primary address
if @PRIMARYCRITERIA = 0 and @TARGETPRIMARYID is not null
begin
-- The target's original primary address record
-- is assured of being associated with the target
-- so simply reset it's primary indicator.
update dbo.ADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @TARGETPRIMARYID
and RELATIONSHIPID is null;
-- If delete duplicates is set We may have to reset the
-- primary indicator on the source address if it was a duplicate
if @DELETEDUPES <> 0
begin
declare @TEMPUID uniqueidentifier;
select @TEMPUID = CONSTITUENTID
from dbo.ADDRESS
where ID = @SOURCEPRIMARYID;
if @TEMPUID = @SOURCEID
begin
-- If the source's primary address is still associated to
-- the source, then reset the primary indicator
update dbo.ADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @SOURCEPRIMARYID
and RELATIONSHIPID is null;
end
end
end
else
begin
-- Things are more complicated if we are preserving
-- the source's primary indicator since there is
-- no guarantee it was associated with the target.
if @DELETEDUPES = 0
begin
-- If we are not deleting duplicate addresses
-- then we can be assured the source's primary
-- address is associated with the target, so
-- simply reset its primary indicator
update dbo.ADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @SOURCEPRIMARYID
and RELATIONSHIPID is null;
end
else
begin
declare @TEMPID uniqueidentifier;
select @TEMPID = CONSTITUENTID
from dbo.ADDRESS
where ID = @SOURCEPRIMARYID;
if @TEMPID = @TARGETID
begin
-- If the source primary address is now associated
-- with the target, then reset its primary indicator
update dbo.ADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @SOURCEPRIMARYID
and RELATIONSHIPID is null;
end
else
begin
-- Otherwise, the source's primary address was
-- not associated with the target b/c it is a
-- duplicate of one of the target's addresses.
-- In this scenario, the target's address that
-- is a duplicate of the source's primary address
-- should be set as the target's primary address.
set @TEMPID = null;
select @TEMPID = TARGET_ADDRESSID
from @DUPLICATEADDRESSES DA
where DA.SOURCE_ADDRESSID = @SOURCEPRIMARYID;
if @TEMPID is not null
begin
update dbo.ADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @TEMPID
and RELATIONSHIPID is null;
end
else
begin
-- If no duplicate address was found then the source's
-- primary address was not associated with the target
-- for another unknown reason. In this case, reset
-- the primary indicator on the target's original
-- primary address
update dbo.ADDRESS
set ISPRIMARY = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where ID = @TARGETPRIMARYID
and RELATIONSHIPID is null;
end
end
end
end
--Reaim any mail prefs that might be on a group the source might belong to.
--This function will always remove prefs that will be invalidated by the merge,
--but it won't actually update the mail prefs unless the Personal Information
--Merge Task is also run
exec dbo.USP_CONSTITUENTMERGE_UPDATEGROUPADDRESSMAILPREFS @TARGETID, @SOURCEID, @CHANGEAGENTID, @DELETEDUPES
if @INCLUDEPREFS = 1
begin
--Merge address 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
CONSTITUENTID=@SOURCEID and
(USEPRIMARYADDRESS = 1 or
SENDMAIL = 0 or
(ADDRESSID is not null and
ADDRESSID in (
select ID
from ADDRESS
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.USEPRIMARYADDRESS = 1 or
sourcemp.SENDMAIL = 0 or
(sourcemp.ADDRESSID is not null and
sourcemp.ADDRESSID in (
select ID
from ADDRESS
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 address also moved,
--send to the primary address, or are marked "Do not send"
update MAILPREFERENCE
set
CONSTITUENTID=@TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where
CONSTITUENTID=@SOURCEID and
(USEPRIMARYADDRESS = 1 or
SENDMAIL = 0 or
(ADDRESSID is not null and
ADDRESSID in (
select ID
from ADDRESS
where CONSTITUENTID=@TARGETID
)
)
)
end
--If source had any changed addresses in a change of address batch,
--update the batch constituent to point to the target
update dbo.BATCHCOAUPDATE
set
CONSTITUENTID = @TARGETID, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CHANGEDATE
where
CONSTITUENTID = @SOURCEID and
ADDRESSID in (select ID from @CHANGEDADDRESSES);
-- If deleting duplicates, remove the relationship addresses from committed COA update batches
delete dbo.BATCHCOAUPDATE
from
dbo.BATCHCOAUPDATE
inner join dbo.BATCH
on BATCH.ID = BATCHCOAUPDATE.BATCHID
inner join dbo.ADDRESS
on ADDRESS.ID = BATCHCOAUPDATE.ADDRESSID
where
BATCHCOAUPDATE.CONSTITUENTID = @SOURCEID
and ADDRESS.RELATIONSHIPID is not null
and
(
BATCH.STATUSCODE = 1
or BATCH.STATUSCODE = 2
)
-- If there are any relationship addresses in uncommitted COA update batches, display an error
if exists
(
select 1
from
dbo.BATCHCOAUPDATE
inner join dbo.BATCH
on BATCH.ID = BATCHCOAUPDATE.BATCHID
inner join dbo.ADDRESS
on ADDRESS.ID = BATCHCOAUPDATE.ADDRESSID
where
BATCHCOAUPDATE.CONSTITUENTID = @SOURCEID
and ADDRESS.RELATIONSHIPID is not null
)
begin
raiserror('You cannot complete this merge because at least one constituent has an address that is included in an uncommitted AddressFinder batch. To continue the merge, you must first commit the batch.', 16, 1);
end
--update sales order if deleting duplicates
if @DELETEDUPES <> 0
begin
update dbo.SALESORDER
set
ADDRESSID = coalesce((
select top 1 target.ID
from dbo.ADDRESS source
inner join dbo.ADDRESS target
on source.COUNTRYID = target.COUNTRYID
and (source.STATEID = target.STATEID or (source.STATEID is null and target.STATEID is null))
and source.ADDRESSBLOCK = target.ADDRESSBLOCK
and source.CITY = target.CITY
and source.POSTCODE = target.POSTCODE
and (source.ADDRESSTYPECODEID = target.ADDRESSTYPECODEID or (source.ADDRESSTYPECODEID is null and target.ADDRESSTYPECODEID is null))
and source.CART = target.CART
and source.DPC = target.DPC
and source.LOT = target.LOT
where source.ID = SALESORDER.ADDRESSID
and target.CONSTITUENTID = @TARGETID
order by target.ISPRIMARY desc), SALESORDER.ADDRESSID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
SALESORDER.RECIPIENTID = @SOURCEID
and SALESORDER.CONTACTRELATIONSHIPID is null
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;