USP_CONSTITUENT_COPYPRIMARYCONTACTINFO
Copies the primary contact information from one constituent to another.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @SOURCECONSTITUENTID | uniqueidentifier | IN | |
| @DESTINATIONCONSTITUENTID | uniqueidentifier | IN | |
| @COPYEMAILADDRESS | bit | IN | |
| @COPYPHONENUMBER | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO
(
@CHANGEAGENTID uniqueidentifier = null,
@SOURCECONSTITUENTID uniqueidentifier,
@DESTINATIONCONSTITUENTID uniqueidentifier,
@COPYEMAILADDRESS bit = 1,
@COPYPHONENUMBER bit = 1
)
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
select @CURRENTDATE = getdate();
declare @HASPRIMARY bit
declare @EXISTINGID uniqueidentifier
-------------------- Address --------------------
set @HASPRIMARY = 0
set @EXISTINGID = null
declare @ADDRESSTYPECODEID uniqueidentifier
declare @DONOTMAIL bit
declare @DONOTMAILREASONCODEID uniqueidentifier
declare @ADDRESSSTARTDATE dbo.UDT_MONTHDAY
declare @ADDRESSENDDATE dbo.UDT_MONTHDAY
declare @COUNTRYID uniqueidentifier
declare @STATEID uniqueidentifier
declare @ADDRESSBLOCK nvarchar(150)
declare @CITY nvarchar(50)
declare @POSTCODE nvarchar(12)
declare @CART nvarchar(10)
declare @DPC nvarchar(8)
declare @LOT nvarchar(5)
declare @COUNTYCODEID uniqueidentifier
declare @CONGRESSIONALDISTRICTCODEID uniqueidentifier
declare @STATEHOUSEDISTRICTCODEID uniqueidentifier
declare @STATESENATEDISTRICTCODEID uniqueidentifier
declare @LOCALPRECINCTCODEID uniqueidentifier
declare @INFOSOURCECODEID uniqueidentifier
declare @REGIONCODEID uniqueidentifier
declare @ISCONFIDENTIAL bit
declare @PHONECOUNTRYID uniqueidentifier
declare @ADDRESSHISTORICALSTARTDATE datetime
declare @ADDRESSHISTORICALENDDATE datetime
select
@ADDRESSTYPECODEID = ADDRESSTYPECODEID,
@DONOTMAIL = DONOTMAIL,
@DONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
@ADDRESSSTARTDATE = STARTDATE,
@ADDRESSENDDATE = ENDDATE,
@COUNTRYID = COUNTRYID,
@STATEID = STATEID,
@ADDRESSBLOCK = ADDRESSBLOCK,
@CITY = CITY,
@POSTCODE = POSTCODE,
@CART = CART,
@DPC = DPC,
@LOT = LOT,
@HASPRIMARY = 1,
@ISCONFIDENTIAL = ISCONFIDENTIAL,
@ADDRESSHISTORICALSTARTDATE = ADDRESS.HISTORICALSTARTDATE,
@ADDRESSHISTORICALENDDATE = ADDRESS.HISTORICALENDDATE,
@COUNTYCODEID = ADDRESSVALIDATIONUPDATE.COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID = ADDRESSVALIDATIONUPDATE.CONGRESSIONALDISTRICTCODEID,
@STATEHOUSEDISTRICTCODEID = ADDRESSVALIDATIONUPDATE.STATEHOUSEDISTRICTCODEID,
@STATESENATEDISTRICTCODEID = ADDRESSVALIDATIONUPDATE.STATESENATEDISTRICTCODEID,
@LOCALPRECINCTCODEID = ADDRESSVALIDATIONUPDATE.LOCALPRECINCTCODEID,
@INFOSOURCECODEID = ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID,
@REGIONCODEID = ADDRESSVALIDATIONUPDATE.REGIONCODEID
from
dbo.ADDRESS
left join
dbo.ADDRESSVALIDATIONUPDATE on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
where
CONSTITUENTID = @SOURCECONSTITUENTID and
ISPRIMARY = 1
if @HASPRIMARY = 1
begin
if exists (select ID from dbo.ADDRESS where CONSTITUENTID = @DESTINATIONCONSTITUENTID and ISPRIMARY = 1)
update dbo.ADDRESS
set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID = @DESTINATIONCONSTITUENTID and
ISPRIMARY = 1
select
@EXISTINGID = ADDRESS.ID
from
dbo.ADDRESS
left join
dbo.ADDRESSVALIDATIONUPDATE on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
where
CONSTITUENTID = @DESTINATIONCONSTITUENTID and
(ADDRESSTYPECODEID = @ADDRESSTYPECODEID or (ADDRESSTYPECODEID is null and @ADDRESSTYPECODEID is null)) and
DONOTMAIL = @DONOTMAIL and
STARTDATE = @ADDRESSSTARTDATE and
ENDDATE = @ADDRESSENDDATE and
COUNTRYID = @COUNTRYID and
(STATEID = @STATEID or (STATEID is null and @STATEID is null)) and
(ADDRESSBLOCK = @ADDRESSBLOCK or (ADDRESSBLOCK is null and @ADDRESSBLOCK is null)) and
(CITY = @CITY or (CITY is null and @CITY is null)) and
(POSTCODE = @POSTCODE or (POSTCODE is null and @POSTCODE is null)) and
CART = @CART and
DPC = @DPC and
LOT = @LOT and
(COUNTYCODEID = @COUNTYCODEID or (COUNTYCODEID is null and @COUNTYCODEID is null)) and
(CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID or (CONGRESSIONALDISTRICTCODEID is null and @CONGRESSIONALDISTRICTCODEID is null)) and
(STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID or (STATEHOUSEDISTRICTCODEID is null and @STATEHOUSEDISTRICTCODEID is null)) and
(STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID or (STATESENATEDISTRICTCODEID is null and @STATESENATEDISTRICTCODEID is null)) and
(LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID or (LOCALPRECINCTCODEID is null and @LOCALPRECINCTCODEID is null)) and
(INFOSOURCECODEID = @INFOSOURCECODEID or (INFOSOURCECODEID is null and @INFOSOURCECODEID is null)) and
(REGIONCODEID = @REGIONCODEID or (REGIONCODEID is null and @REGIONCODEID is null))
if @EXISTINGID is null
begin
declare @NEWADDRESSID uniqueidentifier
set @NEWADDRESSID = newid()
insert into dbo.ADDRESS (
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
DONOTMAIL,
DONOTMAILREASONCODEID,
STARTDATE,
ENDDATE,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
ISPRIMARY,
ISCONFIDENTIAL,
HISTORICALSTARTDATE,
HISTORICALENDDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
) values (
@NEWADDRESSID,
@DESTINATIONCONSTITUENTID,
@ADDRESSTYPECODEID,
@DONOTMAIL,
@DONOTMAILREASONCODEID,
@ADDRESSSTARTDATE,
@ADDRESSENDDATE,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@CART,
@DPC,
@LOT,
1,
@ISCONFIDENTIAL,
@ADDRESSHISTORICALSTARTDATE,
@ADDRESSHISTORICALENDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
insert into dbo.ADDRESSVALIDATIONUPDATE
(ID,COUNTYCODEID,CONGRESSIONALDISTRICTCODEID,STATEHOUSEDISTRICTCODEID,STATESENATEDISTRICTCODEID,LOCALPRECINCTCODEID,INFOSOURCECODEID,REGIONCODEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
values
(@NEWADDRESSID,@COUNTYCODEID,@CONGRESSIONALDISTRICTCODEID,@STATEHOUSEDISTRICTCODEID,@STATESENATEDISTRICTCODEID,@LOCALPRECINCTCODEID,@INFOSOURCECODEID,@REGIONCODEID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)
end
else
update dbo.ADDRESS
set
ISPRIMARY = 1,
ISCONFIDENTIAL = @ISCONFIDENTIAL,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @EXISTINGID
-- Remove blank address if one was created when adding an individual (no contact info entered creates blank address)
declare @BLANKADDRESSID uniqueidentifier
select
@BLANKADDRESSID = ADDRESS.ID
from
dbo.ADDRESS
left join
dbo.ADDRESSVALIDATIONUPDATE on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
where
CONSTITUENTID = @DESTINATIONCONSTITUENTID and
ADDRESSTYPECODEID is null and
DONOTMAIL = 0 and
STARTDATE = 0000 and
ENDDATE = 0000 and
STATEID is null and
ADDRESSBLOCK = '' and
CITY = '' and
POSTCODE = '' and
CART = '' and
DPC = '' and
LOT = '' and
ISPRIMARY = 0 and
COUNTYCODEID is null and
CONGRESSIONALDISTRICTCODEID is null and
STATEHOUSEDISTRICTCODEID is null and
STATESENATEDISTRICTCODEID is null and
LOCALPRECINCTCODEID is null and
INFOSOURCECODEID is null and
REGIONCODEID is null
if @BLANKADDRESSID is not null
begin
--Cache CONTEXT INFO
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from dbo.ADDRESS
where ID = @BLANKADDRESSID
--Restore CONTEXT_INFO
if not @contextCache is null
set CONTEXT_INFO @contextCache;
end
end
-------------------- Phone --------------------
if @COPYPHONENUMBER = 1
begin
set @HASPRIMARY = 0
set @EXISTINGID = null
declare @PHONETYPECODEID uniqueidentifier
declare @NUMBER nvarchar(100)
declare @DONOTCALL bit
declare @DONOTCALLREASONCODEID uniqueidentifier
declare @STARTTIME dbo.UDT_HOURMINUTE
declare @ENDTIME dbo.UDT_HOURMINUTE
declare @STARTDATE datetime
declare @ENDDATE datetime
declare @PHONEISCONFIDENTIAL bit
declare @DONOTTEXT bit
set @STARTTIME = ''
set @ENDTIME = ''
set @INFOSOURCECODEID = null
select
@PHONETYPECODEID = PHONETYPECODEID,
@NUMBER = NUMBER,
@HASPRIMARY = 1,
@DONOTCALL = DONOTCALL,
@DONOTCALLREASONCODEID = DONOTCALLREASONCODEID,
@STARTTIME = STARTTIME,
@ENDTIME = ENDTIME,
@STARTDATE = STARTDATE,
@ENDDATE = ENDDATE,
@INFOSOURCECODEID = INFOSOURCECODEID,
@PHONECOUNTRYID = COUNTRYID,
@PHONEISCONFIDENTIAL = ISCONFIDENTIAL,
@DONOTTEXT = DONOTTEXT
from
dbo.PHONE
where
CONSTITUENTID = @SOURCECONSTITUENTID and
ISPRIMARY = 1
if @HASPRIMARY = 1
begin
if exists (select ID from dbo.PHONE where CONSTITUENTID = @DESTINATIONCONSTITUENTID and ISPRIMARY = 1)
update dbo.PHONE
set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID = @DESTINATIONCONSTITUENTID and
ISPRIMARY = 1
select
@EXISTINGID = ID
from
dbo.PHONE
where
CONSTITUENTID = @DESTINATIONCONSTITUENTID and
(PHONETYPECODEID = @PHONETYPECODEID or (PHONETYPECODEID is null and @PHONETYPECODEID is null)) and
NUMBER = @NUMBER
if @EXISTINGID is null
insert into dbo.PHONE
(CONSTITUENTID,PHONETYPECODEID,NUMBER,ISPRIMARY,INFOSOURCECODEID,COUNTRYID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,DONOTCALL,DONOTCALLREASONCODEID,STARTTIME,ENDTIME,STARTDATE,ENDDATE,ISCONFIDENTIAL,DONOTTEXT)
values
(@DESTINATIONCONSTITUENTID,@PHONETYPECODEID,@NUMBER,1,@INFOSOURCECODEID,@PHONECOUNTRYID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@DONOTCALL,@DONOTCALLREASONCODEID,@STARTTIME,@ENDTIME,@STARTDATE,@ENDDATE,@PHONEISCONFIDENTIAL,@DONOTTEXT)
else
update dbo.PHONE
set
ISPRIMARY = 1,
DONOTCALL = @DONOTCALL,
DONOTCALLREASONCODEID = @DONOTCALLREASONCODEID,
STARTTIME = @STARTTIME,
ENDTIME = @ENDTIME,
STARTDATE = @STARTDATE,
ENDDATE = @ENDDATE,
INFOSOURCECODEID = @INFOSOURCECODEID,
ISCONFIDENTIAL = @PHONEISCONFIDENTIAL,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
DONOTTEXT = @DONOTTEXT
where
ID = @EXISTINGID
end
end
-------------------- Email address --------------------
if @COPYEMAILADDRESS = 1
begin
set @HASPRIMARY = 0
set @EXISTINGID = null
declare @EMAILADDRESSTYPECODEID uniqueidentifier
declare @EMAILADDRESS dbo.UDT_EMAILADDRESS
declare @DONOTEMAIL bit
declare @DONOTEMAILREASONCODEID uniqueidentifier
set @INFOSOURCECODEID = null
declare @EMAILADDRESS_STARTDATE date = null
declare @EMAILADDRESS_ENDDATE date = null
declare @EMAILISCONFIDENTIAL bit
select
@EMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODEID,
@EMAILADDRESS = EMAILADDRESS,
@HASPRIMARY = 1,
@DONOTEMAIL = DONOTEMAIL,
@INFOSOURCECODEID = INFOSOURCECODEID,
@EMAILADDRESS_STARTDATE = STARTDATE,
@EMAILADDRESS_ENDDATE = ENDDATE,
@EMAILISCONFIDENTIAL = ISCONFIDENTIAL,
@DONOTEMAILREASONCODEID = DONOTEMAILREASONCODEID
from
dbo.EMAILADDRESS
where
CONSTITUENTID = @SOURCECONSTITUENTID and
ISPRIMARY = 1
if @HASPRIMARY = 1
begin
if exists (select ID from dbo.EMAILADDRESS where CONSTITUENTID = @DESTINATIONCONSTITUENTID and ISPRIMARY = 1)
update dbo.EMAILADDRESS
set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID = @DESTINATIONCONSTITUENTID and
ISPRIMARY = 1
select
@EXISTINGID = ID
from
dbo.EMAILADDRESS
where
CONSTITUENTID = @DESTINATIONCONSTITUENTID and
(EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID or (EMAILADDRESSTYPECODEID is null and @EMAILADDRESSTYPECODEID is null)) and
EMAILADDRESS = @EMAILADDRESS
if @EXISTINGID is null
insert into dbo.EMAILADDRESS
(CONSTITUENTID,EMAILADDRESSTYPECODEID,EMAILADDRESS,ISPRIMARY,INFOSOURCECODEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,DONOTEMAIL,STARTDATE,ENDDATE,ISCONFIDENTIAL,DONOTEMAILREASONCODEID)
values
(@DESTINATIONCONSTITUENTID,@EMAILADDRESSTYPECODEID,@EMAILADDRESS,1,@INFOSOURCECODEID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@DONOTEMAIL,@EMAILADDRESS_STARTDATE,@EMAILADDRESS_ENDDATE,@EMAILISCONFIDENTIAL,@DONOTEMAILREASONCODEID)
else
update dbo.EMAILADDRESS
set
ISPRIMARY = 1,
DONOTEMAIL = @DONOTEMAIL,
INFOSOURCECODEID = @INFOSOURCECODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
STARTDATE = @EMAILADDRESS_STARTDATE,
ENDDATE = @EMAILADDRESS_ENDDATE,
ISCONFIDENTIAL = @EMAILISCONFIDENTIAL,
DONOTEMAILREASONCODEID = @DONOTEMAILREASONCODEID
where
ID = @EXISTINGID
end
end