USP_ADDRESS_ADD
Adds a new address for a constituent.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @PRIMARY | bit | IN | |
| @DONOTMAIL | bit | IN | |
| @STARTDATE | UDT_MONTHDAY | IN | |
| @ENDDATE | UDT_MONTHDAY | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @STATEID | uniqueidentifier | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @HISTORICALSTARTDATE | datetime | IN | |
| @RECENTMOVE | bit | IN | |
| @OLDADDRESSID | uniqueidentifier | IN | |
| @CART | nvarchar(10) | IN | |
| @DPC | nvarchar(8) | IN | |
| @LOT | nvarchar(5) | IN | |
| @UPDATEMATCHINGSPOUSEADDRESSES | bit | IN | |
| @UPDATEMATCHINGHOUSEHOLDADDRESSES | bit | IN | |
| @OMITFROMVALIDATION | bit | IN | |
| @COUNTYCODEID | uniqueidentifier | IN | |
| @CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | |
| @STATEHOUSEDISTRICTCODEID | uniqueidentifier | IN | |
| @STATESENATEDISTRICTCODEID | uniqueidentifier | IN | |
| @LOCALPRECINCTCODEID | uniqueidentifier | IN | |
| @INFOSOURCECODEID | uniqueidentifier | IN | |
| @REGIONCODEID | uniqueidentifier | IN | |
| @LASTVALIDATIONATTEMPTDATE | datetime | IN | |
| @VALIDATIONMESSAGE | nvarchar(200) | IN | |
| @CERTIFICATIONDATA | int | IN | |
| @DONOTMAILREASONCODEID | uniqueidentifier | IN | |
| @INFOSOURCECOMMENTS | nvarchar(256) | IN | |
| @ISCONFIDENTIAL | bit | IN | |
| @CONSTITUENTDATAREVIEWROLLBACKREASONID | uniqueidentifier | IN | |
| @ORIGINCODE | tinyint | IN | |
| @HISTORICALENDDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_ADDRESS_ADD
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@CONSTITUENTID uniqueidentifier,
@ADDRESSTYPECODEID uniqueidentifier = null,
@PRIMARY bit = 0,
@DONOTMAIL bit = 0,
@STARTDATE dbo.UDT_MONTHDAY = '0000',
@ENDDATE dbo.UDT_MONTHDAY = '0000',
@COUNTRYID uniqueidentifier,
@STATEID uniqueidentifier = null,
@ADDRESSBLOCK nvarchar(150) = '',
@CITY nvarchar(50) = '',
@POSTCODE nvarchar(12) = '',
@HISTORICALSTARTDATE datetime = null,
@RECENTMOVE bit = 0,
@OLDADDRESSID uniqueidentifier = null,
@CART nvarchar(10) = '',
@DPC nvarchar(8) = '',
@LOT nvarchar(5) = '',
@UPDATEMATCHINGSPOUSEADDRESSES bit = 0,
@UPDATEMATCHINGHOUSEHOLDADDRESSES bit = null,
@OMITFROMVALIDATION bit = 0,
@COUNTYCODEID uniqueidentifier = null,
@CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
@STATEHOUSEDISTRICTCODEID uniqueidentifier = null,
@STATESENATEDISTRICTCODEID uniqueidentifier = null,
@LOCALPRECINCTCODEID uniqueidentifier = null,
@INFOSOURCECODEID uniqueidentifier = null,
@REGIONCODEID uniqueidentifier = null,
@LASTVALIDATIONATTEMPTDATE datetime = null,
@VALIDATIONMESSAGE nvarchar(200) = '',
@CERTIFICATIONDATA integer = 0,
@DONOTMAILREASONCODEID uniqueidentifier = null,
@INFOSOURCECOMMENTS nvarchar(256) = '',
@ISCONFIDENTIAL bit = 0,
@CONSTITUENTDATAREVIEWROLLBACKREASONID uniqueidentifier = null, -- used by constituent data review
@ORIGINCODE tinyint = 0,
@HISTORICALENDDATE datetime = null
) as
set nocount on;
if @PRIMARY = 0 and (select count(*) from dbo.ADDRESS where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1) = 0
raiserror('ERR_ADDRESS_MUSTHAVEPRIMARY', 13, 1);
exec dbo.USP_ADDRESS_CREATE @ID output, @CHANGEAGENTID, @CONSTITUENTID, @ADDRESSTYPECODEID, @PRIMARY, @DONOTMAIL,
@STARTDATE, @ENDDATE, @COUNTRYID, @STATEID, @ADDRESSBLOCK, @CITY, @POSTCODE, @CART, @DPC, @LOT,
@UPDATEMATCHINGSPOUSEADDRESSES, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID,
@STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID,
@REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @DONOTMAILREASONCODEID,
@HISTORICALSTARTDATE, @INFOSOURCECOMMENTS, @ISCONFIDENTIAL, @ORIGINCODE, @HISTORICALENDDATE
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @EARLIESTTIMECURRENTDATE date;
set @EARLIESTTIMECURRENTDATE = @CURRENTDATE;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @RECENTMOVE = 1
begin
if @OLDADDRESSID is null
raiserror('BBERR_OLDADDRESSIDREQUIRED', 13, 1);
else
begin
declare @HISTORICALENDDATEVALUE datetime;
if @HISTORICALSTARTDATE is null
begin
set @HISTORICALENDDATEVALUE = @EARLIESTTIMECURRENTDATE
end
if @HISTORICALSTARTDATE is not null
begin
declare @OLDHISTORICALSTARTDATE datetime;
select @OLDHISTORICALSTARTDATE = HISTORICALSTARTDATE from dbo.ADDRESS where ADDRESS.ID = @OLDADDRESSID;
if @HISTORICALSTARTDATE < @OLDHISTORICALSTARTDATE
set @HISTORICALENDDATEVALUE = @EARLIESTTIMECURRENTDATE
else
set @HISTORICALENDDATEVALUE = @HISTORICALSTARTDATE
end
update dbo.ADDRESS
set
ISPRIMARY = 0,
DONOTMAIL = 1,
HISTORICALENDDATE = @HISTORICALENDDATEVALUE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ADDRESS.ID = @OLDADDRESSID
end
end
begin try
if @UPDATEMATCHINGHOUSEHOLDADDRESSES = 1
begin
-- if the constituent is an individual, householdid will be the household they are a member of
-- if the constituent is a household, householdid will be that household's id
declare @HOUSEHOLDID uniqueidentifier;
if dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1
set @HOUSEHOLDID = @CONSTITUENTID;
else
select
@HOUSEHOLDID = GM.GROUPID
from
dbo.GROUPMEMBER GM
left outer join
dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
left outer join
dbo.GROUPDATA GD on GD.ID = GM.GROUPID
where
GM.MEMBERID = @CONSTITUENTID
and
GD.GROUPTYPECODE = 0
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @EARLIESTTIMECURRENTDATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE))
or (GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE and GMDR.DATETO > @EARLIESTTIMECURRENTDATE));
-- create a table of all of the members of the household previously identified
declare @IDSTOUPDATE table(ID uniqueidentifier)
insert into @IDSTOUPDATE
select
GM.MEMBERID
from
dbo.GROUPMEMBER GM
left outer join
dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
where
GM.GROUPID = @HOUSEHOLDID
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @EARLIESTTIMECURRENTDATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE))
or (GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE and GMDR.DATETO > @EARLIESTTIMECURRENTDATE))
union all
select
@HOUSEHOLDID; -- include the household itself (for the case where the constituent is an individual)
delete from @IDSTOUPDATE where ID = @CONSTITUENTID; -- (if the constituent was an individual, they'll show as a member - if they were a household it got union'd in)
-- create table of matching addresses
declare @MATCHING table(ID uniqueidentifier);
insert into @MATCHING
select
ID
from
dbo.ADDRESS
where
COUNTRYID = @COUNTRYID
and ( (STATEID = @STATEID) or (STATEID is null and @STATEID is null) )
and ADDRESSBLOCK = @ADDRESSBLOCK
and CITY = @CITY
and POSTCODE = @POSTCODE
and ( (ADDRESSTYPECODEID = @ADDRESSTYPECODEID) or (ADDRESSTYPECODEID is null and @ADDRESSTYPECODEID is null) )
and CONSTITUENTID in (select ID from @IDSTOUPDATE)
and HISTORICALENDDATE is null;
-- remove primary indicator if we're going to insert a new one
if @PRIMARY = 1
begin
update dbo.ADDRESS
set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID in (select ID from @IDSTOUPDATE)
and not exists (
select ID
from @MATCHING
where ADDRESS.ID = ID
)
-- update existing records with primary bit
update dbo.ADDRESS
set
ISPRIMARY = @PRIMARY,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID in (select ID from @IDSTOUPDATE)
and exists (
select ID
from @MATCHING
where ADDRESS.ID = ID
)
end
if @RECENTMOVE = 1
begin
--Find all addresses that match the old address for household constituents
declare @MATCHING_OLDADDRESS table (ID uniqueidentifier);
insert into @MATCHING_OLDADDRESS
select
ADDRESS.ID
from
dbo.ADDRESS
inner join
dbo.ADDRESS OLDADDRESS on OLDADDRESS.ID = @OLDADDRESSID
where
ADDRESS.CONSTITUENTID in (select ID from @IDSTOUPDATE) and
ADDRESS.COUNTRYID = OLDADDRESS.COUNTRYID and
ADDRESS.ADDRESSBLOCK = OLDADDRESS.ADDRESSBLOCK and
ADDRESS.CITY = OLDADDRESS.CITY and
ADDRESS.POSTCODE = OLDADDRESS.POSTCODE and
(
(ADDRESS.STATEID = OLDADDRESS.STATEID) or
(ADDRESS.STATEID is null and OLDADDRESS.STATEID is null)
) and
(
(ADDRESS.ADDRESSTYPECODEID = OLDADDRESS.ADDRESSTYPECODEID) or
(ADDRESS.ADDRESSTYPECODEID is null and OLDADDRESS.ADDRESSTYPECODEID is null)
);
--Update any household constituent's old matching addresses. Only set the PRIMARY flag
--and HISTORICALENDDATE when this is not the household constituent's primary address.
update
dbo.ADDRESS
set
ISPRIMARY = 0,
DONOTMAIL = 1,
HISTORICALENDDATE =
case
when @HISTORICALSTARTDATE is null
then @EARLIESTTIMECURRENTDATE
when @HISTORICALSTARTDATE is not null
then
case
when @HISTORICALSTARTDATE < HISTORICALSTARTDATE
then @EARLIESTTIMECURRENTDATE
else
@HISTORICALSTARTDATE
end
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID in (select ID from @MATCHING_OLDADDRESS)
and (@PRIMARY = 1 or ISPRIMARY = 0);
end
-- insert where there are no records with the new number
insert into dbo.ADDRESS
(CONSTITUENTID,ADDRESSTYPECODEID,DONOTMAIL,DONOTMAILREASONCODEID,STARTDATE,ENDDATE,COUNTRYID,STATEID,ADDRESSBLOCK,CITY,POSTCODE,CART,DPC,LOT,ISPRIMARY,HISTORICALSTARTDATE,ISCONFIDENTIAL,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
select
IDSTOUPDATE.ID,@ADDRESSTYPECODEID,@DONOTMAIL,@DONOTMAILREASONCODEID,@STARTDATE,@ENDDATE,@COUNTRYID,@STATEID,@ADDRESSBLOCK,@CITY,@POSTCODE,@CART,@DPC,@LOT,case when ISPRIMARY = 1 then 0 else 1 end,@HISTORICALSTARTDATE,@ISCONFIDENTIAL,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
from
@IDSTOUPDATE IDSTOUPDATE
left join
dbo.ADDRESS on ADDRESS.CONSTITUENTID = IDSTOUPDATE.ID and ISPRIMARY = 1
where
not exists (
select ADDRESS.ID
from dbo.ADDRESS
inner join @MATCHING MA on MA.ID = ADDRESS.ID
where IDSTOUPDATE.ID = CONSTITUENTID
);
-- insert address validation update data where there are no records with the new number
insert into dbo.ADDRESSVALIDATIONUPDATE
(ID, OMITFROMVALIDATION, COUNTYCODEID, CONGRESSIONALDISTRICTCODEID, STATEHOUSEDISTRICTCODEID, STATESENATEDISTRICTCODEID, LOCALPRECINCTCODEID, INFOSOURCECODEID, INFOSOURCECOMMENTS, REGIONCODEID, LASTVALIDATIONATTEMPTDATE, VALIDATIONMESSAGE, CERTIFICATIONDATA, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ID, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID, @INFOSOURCECOMMENTS, @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from
dbo.ADDRESS
where
COUNTRYID = @COUNTRYID
and ((STATEID = @STATEID) or (STATEID is null and @STATEID is null) )
and ADDRESSBLOCK = @ADDRESSBLOCK
and CITY = @CITY
and POSTCODE = @POSTCODE
and ( (ADDRESSTYPECODEID = @ADDRESSTYPECODEID) or (ADDRESSTYPECODEID is null and @ADDRESSTYPECODEID is null) )
and CONSTITUENTID in (select ID from @IDSTOUPDATE)
and ID not in (select ID from dbo.ADDRESSVALIDATIONUPDATE union select ID from @MATCHING);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;