USP_ADDRESS_CREATE
Creates an address record.
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 | |
| @CART | nvarchar(10) | IN | |
| @DPC | nvarchar(8) | IN | |
| @LOT | nvarchar(5) | IN | |
| @UPDATEMATCHINGSPOUSEADDRESSES | 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 | |
| @HISTORICALSTARTDATE | date | IN | |
| @INFOSOURCECOMMENTS | nvarchar(256) | IN | |
| @ISCONFIDENTIAL | bit | IN | |
| @ORIGINCODE | tinyint | IN | |
| @HISTORICALENDDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_ADDRESS_CREATE
(
@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) = '',
@CART nvarchar(10) = '',
@DPC nvarchar(8) = '',
@LOT nvarchar(5) = '',
@UPDATEMATCHINGSPOUSEADDRESSES bit = 0,
@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,
@HISTORICALSTARTDATE date = null,
@INFOSOURCECOMMENTS nvarchar(256) = '',
@ISCONFIDENTIAL bit = 0,
@ORIGINCODE tinyint = 0,
@HISTORICALENDDATE datetime = null
) as
set nocount on;
declare @CURRENTDATE datetime;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
declare @SEQUENCE int;
select
@SEQUENCE=coalesce(max(SEQUENCE),0) + 1
from
dbo.ADDRESS
where
CONSTITUENTID=@CONSTITUENTID;
if @CERTIFICATIONDATA is null
set @CERTIFICATIONDATA = 0;
if @ORIGINCODE is not null and @ORIGINCODE <> 0
set @INFOSOURCECODEID = null
else
set @ORIGINCODE = 0
if @DONOTMAIL = 0
set @DONOTMAILREASONCODEID = null
begin try
if @PRIMARY = 1
update
dbo.[ADDRESS]
set
ISPRIMARY = 0,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1;
insert into dbo.[ADDRESS]
(
[ID],
[CONSTITUENTID],
[ADDRESSTYPECODEID],
[ISPRIMARY],
[DONOTMAIL],
[STARTDATE],
[ENDDATE],
[HISTORICALSTARTDATE],
[HISTORICALENDDATE],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[CART],
[DPC],
[LOT],
[SEQUENCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[DONOTMAILREASONCODEID],
[ISCONFIDENTIAL]
)
values
(
@ID,
@CONSTITUENTID,
@ADDRESSTYPECODEID,
@PRIMARY,
@DONOTMAIL,
@STARTDATE,
@ENDDATE,
@HISTORICALSTARTDATE,
@HISTORICALENDDATE,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@CART,
@DPC,
@LOT,
@SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@DONOTMAILREASONCODEID,
@ISCONFIDENTIAL
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(
[ID],
[OMITFROMVALIDATION],
[COUNTYCODEID],
[CONGRESSIONALDISTRICTCODEID],
[STATEHOUSEDISTRICTCODEID],
[STATESENATEDISTRICTCODEID],
[LOCALPRECINCTCODEID],
[INFOSOURCECODEID],
[INFOSOURCECOMMENTS],
[REGIONCODEID],
[LASTVALIDATIONATTEMPTDATE],
[VALIDATIONMESSAGE],
[CERTIFICATIONDATA],
[ORIGINCODE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@OMITFROMVALIDATION,
@COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID,
@STATEHOUSEDISTRICTCODEID,
@STATESENATEDISTRICTCODEID,
@LOCALPRECINCTCODEID,
@INFOSOURCECODEID,
@INFOSOURCECOMMENTS,
@REGIONCODEID,
@LASTVALIDATIONATTEMPTDATE,
@VALIDATIONMESSAGE,
@CERTIFICATIONDATA,
@ORIGINCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if @UPDATEMATCHINGSPOUSEADDRESSES = 1 and exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP') begin
declare @SPOUSEID uniqueidentifier;
declare @EXISTINGID uniqueidentifier;
select @SPOUSEID = RECIPROCALCONSTITUENTID from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and ISSPOUSE = 1;
if @PRIMARY = 1
update dbo.ADDRESS
set
ISPRIMARY = 0
where
CONSTITUENTID = @SPOUSEID and
ISPRIMARY = 1
select
@EXISTINGID = ID
from
dbo.ADDRESS
where
CONSTITUENTID = @SPOUSEID and
COUNTRYID = @COUNTRYID and
(STATEID = @STATEID or (STATEID is null and @STATEID is null)) and
ADDRESSBLOCK = @ADDRESSBLOCK and
CITY = @CITY and
POSTCODE = @POSTCODE
if @EXISTINGID is null
begin
declare @SPOUSEADDRESSID uniqueidentifier;
set @SPOUSEADDRESSID = newID();
declare @SPOUSEHASPRIMARYADDRESS bit = 0;
if exists(select 1 from dbo.ADDRESS where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
begin
set @SPOUSEHASPRIMARYADDRESS = 1;
end
declare @INSERTSPOUSEADDRESSASPRIMARY bit = 0;
if @PRIMARY = 1 or @SPOUSEHASPRIMARYADDRESS = 0
begin
set @INSERTSPOUSEADDRESSASPRIMARY = 1;
end
insert into dbo.ADDRESS
(
ID,
CONSTITUENTID,
ADDRESSTYPECODEID,
DONOTMAIL,
STARTDATE,
ENDDATE,
HISTORICALSTARTDATE,
COUNTRYID,
STATEID,
ADDRESSBLOCK,
CITY,
POSTCODE,
CART,
DPC,
LOT,
ISPRIMARY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
DONOTMAILREASONCODEID,
ISCONFIDENTIAL
)
values
(
@SPOUSEADDRESSID,
@SPOUSEID,
@ADDRESSTYPECODEID,
@DONOTMAIL,
@STARTDATE,
@ENDDATE,
@HISTORICALSTARTDATE,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@CART,
@DPC,
@LOT,
@INSERTSPOUSEADDRESSASPRIMARY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@DONOTMAILREASONCODEID,
@ISCONFIDENTIAL
)
insert into dbo.ADDRESSVALIDATIONUPDATE
(
ID,
OMITFROMVALIDATION,
COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID,
STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID,
LOCALPRECINCTCODEID,
INFOSOURCECODEID,
INFOSOURCECOMMENTS,
REGIONCODEID,
LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE,
CERTIFICATIONDATA,
ORIGINCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@SPOUSEADDRESSID,
@OMITFROMVALIDATION,
@COUNTYCODEID,
@CONGRESSIONALDISTRICTCODEID,
@STATEHOUSEDISTRICTCODEID,
@STATESENATEDISTRICTCODEID,
@LOCALPRECINCTCODEID,
@INFOSOURCECODEID,
@INFOSOURCECOMMENTS,
@REGIONCODEID,
@LASTVALIDATIONATTEMPTDATE,
@VALIDATIONMESSAGE,
@CERTIFICATIONDATA,
@ORIGINCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
else
begin
update dbo.ADDRESS
set
ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
DONOTMAIL = @DONOTMAIL,
STARTDATE = @STARTDATE,
ENDDATE = @ENDDATE,
HISTORICALSTARTDATE = @HISTORICALSTARTDATE,
COUNTRYID = @COUNTRYID,
STATEID = @STATEID,
ADDRESSBLOCK = @ADDRESSBLOCK,
CITY = @CITY,
POSTCODE = @POSTCODE,
CART = @CART,
DPC = @DPC,
LOT = @LOT,
ISPRIMARY = @PRIMARY,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
ISCONFIDENTIAL = @ISCONFIDENTIAL
where
ID = @EXISTINGID;
if exists (select ID from dbo.ADDRESSVALIDATIONUPDATE where ID = @ID)
update dbo.ADDRESSVALIDATIONUPDATE
set OMITFROMVALIDATION = @OMITFROMVALIDATION,
COUNTYCODEID = @COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID,
STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID,
LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID,
INFOSOURCECODEID = @INFOSOURCECODEID,
INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
REGIONCODEID = @REGIONCODEID,
LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
CERTIFICATIONDATA = @CERTIFICATIONDATA,
ORIGINCODE = @ORIGINCODE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @EXISTINGID;
else
insert into dbo.ADDRESSVALIDATIONUPDATE
(ID, OMITFROMVALIDATION, COUNTYCODEID, CONGRESSIONALDISTRICTCODEID, STATEHOUSEDISTRICTCODEID, STATESENATEDISTRICTCODEID, LOCALPRECINCTCODEID, INFOSOURCECODEID, INFOSOURCECOMMENTS, REGIONCODEID, LASTVALIDATIONATTEMPTDATE, VALIDATIONMESSAGE, CERTIFICATIONDATA, ORIGINCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@EXISTINGID, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID, @INFOSOURCECOMMENTS, @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, coalesce(@CERTIFICATIONDATA, 0), @ORIGINCODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;