USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTADDRESSUPDATEBATCHCOMMIT_3
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @STATEID | uniqueidentifier | IN | |
| @POSTCODE | nvarchar(12) | IN | |
| @ISPRIMARY | bit | IN | |
| @DONOTMAIL | bit | IN | |
| @UPDATEMATCHINGSPOUSEADDRESSES | bit | IN | |
| @VALIDATEONLY | bit | IN | |
| @CART | nvarchar(10) | IN | |
| @DPC | nvarchar(8) | IN | |
| @LOT | nvarchar(5) | IN | |
| @COUNTYCODEID | uniqueidentifier | IN | |
| @OMITFROMVALIDATION | bit | 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 | |
| @STARTDATE | UDT_MONTHDAY | IN | |
| @ENDDATE | UDT_MONTHDAY | IN | |
| @DONOTMAILREASONCODEID | uniqueidentifier | IN | |
| @UPDATEMATCHINGHOUSEHOLDADDRESSES | bit | IN | |
| @INFOSOURCECOMMENTS | nvarchar(256) | IN |
Definition
Copy
create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTADDRESSUPDATEBATCHCOMMIT_3
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@ADDRESSTYPECODEID uniqueidentifier,
@COUNTRYID uniqueidentifier,
@ADDRESSBLOCK nvarchar(150),
@CITY nvarchar(50),
@STATEID uniqueidentifier,
@POSTCODE nvarchar(12),
@ISPRIMARY bit,
@DONOTMAIL bit,
@UPDATEMATCHINGSPOUSEADDRESSES bit,
@VALIDATEONLY bit,
@CART nvarchar(10),
@DPC nvarchar(8),
@LOT nvarchar(5),
@COUNTYCODEID uniqueidentifier,
@OMITFROMVALIDATION bit,
@CONGRESSIONALDISTRICTCODEID uniqueidentifier,
@STATEHOUSEDISTRICTCODEID uniqueidentifier,
@STATESENATEDISTRICTCODEID uniqueidentifier,
@LOCALPRECINCTCODEID uniqueidentifier,
@INFOSOURCECODEID uniqueidentifier,
@REGIONCODEID uniqueidentifier,
@LASTVALIDATIONATTEMPTDATE datetime,
@VALIDATIONMESSAGE nvarchar(200),
@CERTIFICATIONDATA int,
@STARTDATE dbo.UDT_MONTHDAY,
@ENDDATE dbo.UDT_MONTHDAY,
@DONOTMAILREASONCODEID uniqueidentifier,
@UPDATEMATCHINGHOUSEHOLDADDRESSES bit,
@INFOSOURCECOMMENTS nvarchar(256)
) as
set nocount on;
begin try
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @SEQUENCE int;
-- THM 03/07/2008 CR295315-030708 & CR295291-030608 set default values if null
if @CERTIFICATIONDATA is null
set @CERTIFICATIONDATA = 0
if @OMITFROMVALIDATION is null
set @OMITFROMVALIDATION = 0
if @DONOTMAIL = 0
set @DONOTMAILREASONCODEID = null
declare @CONSTITUENTID uniqueidentifier;
select @CONSTITUENTID = ADDRESS.[CONSTITUENTID]
from dbo.[ADDRESS]
where [ID] = @ID;
declare @NULLVALUE uniqueidentifier = newid();
if exists (select 'X'
from dbo.ADDRESS
where HISTORICALENDDATE is null and DONOTMAIL = 0 and CONSTITUENTID = @CONSTITUENTID
and coalesce(ADDRESSBLOCK,'') = coalesce(@ADDRESSBLOCK,'') and coalesce(CITY,'') = coalesce(@CITY,'')
and coalesce(STATEID,@NULLVALUE) = coalesce(@STATEID,@NULLVALUE)
and coalesce(ADDRESSTYPECODEID,@NULLVALUE) = coalesce(@ADDRESSTYPECODEID,@NULLVALUE)
and coalesce(COUNTRYID,@NULLVALUE) = coalesce(@COUNTRYID,@NULLVALUE)
and coalesce(POSTCODE,'') =coalesce(@POSTCODE,'')
and ADDRESS.ID <> @ID)
raiserror('BBERR_ADDRESS_DUPLICATENOTALLOWED', 13, 1);
declare @ADDRESSISPRIMARY bit = 0;
select
@ADDRESSISPRIMARY = ISPRIMARY
from
dbo.ADDRESS
where
ID = @ID;
if @ISPRIMARY = 0 and @ADDRESSISPRIMARY = 1
begin
raiserror('BBERR_ADDRESS_PRIMARYREMOVED : An address cannot be unmarked as primary in Constituent Address Update Batch.', 13, 1);
end
if @VALIDATEONLY = 0
begin
declare @OLDCOUNTRYID uniqueidentifier;
declare @OLDADDRESSBLOCK nvarchar(150);
declare @OLDCITY nvarchar(50);
declare @OLDSTATEID uniqueidentifier;
declare @OLDPOSTCODE nvarchar(12);
declare @OLDADDRESSTYPECODEID uniqueidentifier;
select
@OLDCOUNTRYID = ADDRESS.[COUNTRYID],
@OLDADDRESSBLOCK = ADDRESS.[ADDRESSBLOCK],
@OLDCITY = ADDRESS.[CITY],
@OLDSTATEID = ADDRESS.[STATEID],
@OLDPOSTCODE = ADDRESS.[POSTCODE],
@OLDADDRESSTYPECODEID = ADDRESS.[ADDRESSTYPECODEID]
from
dbo.[ADDRESS]
where
[ID] = @ID;
if @ISPRIMARY = 1
update
dbo.ADDRESS
set
[ISPRIMARY] = 0,
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = @CHANGEAGENTID
where
[CONSTITUENTID] = @CONSTITUENTID and [ISPRIMARY] = 1;
update
dbo.ADDRESS
set
[ADDRESSTYPECODEID] = @ADDRESSTYPECODEID,
[ISPRIMARY] = @ISPRIMARY,
[DONOTMAIL] = @DONOTMAIL,
[COUNTRYID] = @COUNTRYID,
[STATEID] = @STATEID,
[ADDRESSBLOCK] = @ADDRESSBLOCK,
[CITY] = @CITY,
[POSTCODE] = @POSTCODE,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE,
[CART] = @CART,
[DPC] = @DPC,
[LOT] = @LOT,
[STARTDATE] = @STARTDATE,
[ENDDATE] = @ENDDATE,
[DONOTMAILREASONCODEID] = @DONOTMAILREASONCODEID
where
[ID] = @ID;
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,
REGIONCODEID = @REGIONCODEID,
LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
CERTIFICATIONDATA = @CERTIFICATIONDATA,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS
where ID = @ID;
else
insert into dbo.ADDRESSVALIDATIONUPDATE
(ID, OMITFROMVALIDATION, COUNTYCODEID, CONGRESSIONALDISTRICTCODEID, STATEHOUSEDISTRICTCODEID, STATESENATEDISTRICTCODEID, LOCALPRECINCTCODEID, INFOSOURCECODEID, REGIONCODEID, LASTVALIDATIONATTEMPTDATE, VALIDATIONMESSAGE, CERTIFICATIONDATA,INFOSOURCECOMMENTS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID, @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, coalesce(@CERTIFICATIONDATA, 0), @INFOSOURCECOMMENTS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
if @UPDATEMATCHINGSPOUSEADDRESSES = 1 and exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP')
update
dbo.ADDRESS
set
COUNTRYID = @COUNTRYID,
STATEID = @STATEID,
ADDRESSBLOCK = @ADDRESSBLOCK,
CITY = @CITY,
POSTCODE = @POSTCODE,
STARTDATE = @STARTDATE,
ENDDATE = @ENDDATE,
DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ADDRESS.ID in (
select
SPOUSEADDRESS.ID
from
dbo.RELATIONSHIP
left join dbo.ADDRESS as SPOUSEADDRESS on SPOUSEADDRESS.CONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
RELATIONSHIP.ISSPOUSE = 1 and
SPOUSEADDRESS.COUNTRYID = @OLDCOUNTRYID and
SPOUSEADDRESS.ADDRESSBLOCK = @OLDADDRESSBLOCK and
SPOUSEADDRESS.CITY = @OLDCITY and
(SPOUSEADDRESS.STATEID = @OLDSTATEID or (SPOUSEADDRESS.STATEID is null and @OLDSTATEID is null)) and
SPOUSEADDRESS.POSTCODE = @OLDPOSTCODE
);
if @UPDATEMATCHINGHOUSEHOLDADDRESSES = 1
begin
declare @EARLIESTTIMECURRENTDATE date;
set @EARLIESTTIMECURRENTDATE = getdate();
declare @MATCHINGCONSTITUENTS table(ID uniqueidentifier);
insert into @MATCHINGCONSTITUENTS select CONSTITUENTID from dbo.UFN_ADDRESS_MATCHINGHOUSEHOLDRECORDS(@CONSTITUENTID, @OLDCOUNTRYID, @OLDSTATEID, @OLDADDRESSBLOCK, @OLDCITY, @OLDPOSTCODE, @OLDADDRESSTYPECODEID);
-- create table of matching addresses
declare @MATCHING table(ID uniqueidentifier);
insert into @MATCHING
select
ID
from
dbo.ADDRESS
where
COUNTRYID = @OLDCOUNTRYID
and ADDRESSBLOCK = @OLDADDRESSBLOCK
and CITY = @OLDCITY
and ( (STATEID = @OLDSTATEID) or (STATEID is null and @OLDSTATEID is null) )
and POSTCODE = @OLDPOSTCODE
and ( (ADDRESSTYPECODEID = @OLDADDRESSTYPECODEID) or (ADDRESSTYPECODEID is null and @OLDADDRESSTYPECODEID is null) )
and CONSTITUENTID in (select ID from @MATCHINGCONSTITUENTS);
-- update the existing records
-- Per 271319, do *not* update ISPRIMARY on the matching addresses.
update dbo.ADDRESS
set
ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
DONOTMAIL = @DONOTMAIL,
DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
STARTDATE = @STARTDATE,
ENDDATE = @ENDDATE,
COUNTRYID = @COUNTRYID,
STATEID = @STATEID,
ADDRESSBLOCK = @ADDRESSBLOCK,
CITY = @CITY,
POSTCODE = @POSTCODE,
CART = @CART,
DPC = @DPC,
LOT = @LOT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
exists (
select ID
from @MATCHING
where ID = ADDRESS.ID
);
-- insert address validation update data where there are no records with the new number
update dbo.ADDRESSVALIDATIONUPDATE
set
OMITFROMVALIDATION = @OMITFROMVALIDATION,
COUNTYCODEID = @COUNTYCODEID,
CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID,
STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID,
LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID,
INFOSOURCECODEID = @INFOSOURCECODEID,
REGIONCODEID = @REGIONCODEID,
LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
CERTIFICATIONDATA = @CERTIFICATIONDATA,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS
where
exists (
select ID
from @MATCHING
where ID = ADDRESSVALIDATIONUPDATE.ID
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(ID, OMITFROMVALIDATION, COUNTYCODEID, CONGRESSIONALDISTRICTCODEID, STATEHOUSEDISTRICTCODEID, STATESENATEDISTRICTCODEID, LOCALPRECINCTCODEID, INFOSOURCECODEID, REGIONCODEID, LASTVALIDATIONATTEMPTDATE, VALIDATIONMESSAGE, CERTIFICATIONDATA, INFOSOURCECOMMENTS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
ID, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID, @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @INFOSOURCECOMMENTS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from dbo.ADDRESS
where
exists (
select ID
from @MATCHING
where ID = ADDRESS.ID
and ID not in (select ID from dbo.ADDRESSVALIDATIONUPDATE)
);
end
end;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;