USP_CONSTITUENTUPDATEBATCH_APPLYADDRESSRULES
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @PRIMARYRECORDID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @ADDALLADDRESSES | bit | IN | |
| @SIMILARADDRESSCODE | tinyint | IN | |
| @UNSIMILARADDRESSCODE | tinyint | IN | |
| @NEWADDRESSPRIMARYCODE | tinyint | IN | |
| @EXCLUDEADDRESSID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_CONSTITUENTUPDATEBATCH_APPLYADDRESSRULES(
@ID uniqueidentifier, -- Incoming Constituent ID
@PRIMARYRECORDID uniqueidentifier, -- Existing Constituent ID
@CHANGEAGENTID uniqueidentifier,
@ADDALLADDRESSES bit = 1,
@SIMILARADDRESSCODE tinyint = 3,
@UNSIMILARADDRESSCODE tinyint = 3,
@NEWADDRESSPRIMARYCODE tinyint = 1,
@EXCLUDEADDRESSID uniqueidentifier = null --this is used by manual reconciliation to not include in the cursor the incoming address shown on the resolution screen;
)
as
begin
declare @CURRENTDATE datetime;
declare @INCOMINGADDRESSROWID uniqueidentifier;
declare @INCOMINGADDRESSTYPECODEID uniqueidentifier;
declare @INCOMINGCOUNTRYID uniqueidentifier;
declare @INCOMINGADDRESSBLOCK nvarchar(150);
declare @INCOMINGCITY nvarchar(50);
declare @INCOMINGSTATEID uniqueidentifier;
declare @INCOMINGPOSTCODE nvarchar(12);
declare @INCOMINGISPRIMARY bit;
declare @INCOMINGHISTORICALSTARTDATE date;
declare @INCOMINGHISTORICALENDDATE date;
declare @INCOMINGDONOTMAIL bit;
declare @INCOMINGDONOTMAILREASONCODEID uniqueidentifier;
declare @INCOMINGSTARTDATE dbo.UDT_MONTHDAY;
declare @INCOMINGENDDATE dbo.UDT_MONTHDAY;
declare @INCOMINGDPC nvarchar(max);
declare @INCOMINGCART nvarchar(max);
declare @INCOMINGLOT nvarchar(5);
declare @SIMILARADDRESSID uniqueidentifier;
declare @SIMILARADDRESSTYPECODEID uniqueidentifier;
declare @SIMILARCOUNTRYID uniqueidentifier;
declare @SIMILARADDRESSBLOCK nvarchar(150);
declare @SIMILARCITY nvarchar(50);
declare @SIMILARSTATEID uniqueidentifier;
declare @SIMILARPOSTCODE nvarchar(12);
declare @SIMILARISPRIMARY bit;
declare @SIMILARHISTORICALSTARTDATE date;
declare @SIMILARHISTORICALENDDATE date;
declare @SIMILARDONOTMAIL bit;
declare @SIMILARDONOTMAILREASONCODEID uniqueidentifier;
declare @SIMILARSTARTDATE dbo.UDT_MONTHDAY;
declare @SIMILARENDDATE dbo.UDT_MONTHDAY;
declare @SIMILARDPC nvarchar(max);
declare @SIMILARCART nvarchar(max);
declare @SIMILARLOT nvarchar(5);
declare @SIMILARINFOSOURCECODEID uniqueidentifier;
declare @SIMILARINFOSOURCECOMMENTS nvarchar(256);
declare @SIMILARCOUNTYCODEID uniqueidentifier;
declare @SIMILARREGIONCODEID uniqueidentifier;
declare @SIMILARCONGRESSIONALDISTRICTCODEID uniqueidentifier;
declare @SIMILARSTATEHOUSEDISTRICTCODEID uniqueidentifier;
declare @SIMILARSTATESENATEDISTRICTCODEID uniqueidentifier;
declare @SIMILARLOCALPRECINCTCODEID uniqueidentifier;
declare @SIMILARCERTIFICATIONDATA int;
declare @SIMILARLASTVALIDATIONATTEMPTDATE date;
declare @SIMILAROMITFROMVALIDATION bit;
declare @SIMILARVALIDATIONMESSAGE nvarchar(200);
declare @ADDRESSMATCHTHRESHOLD tinyint;
declare @NOCONFLICTS bit;
declare @MAKENEWPRIMARYROWID uniqueidentifier;
declare @EXISTINGISPRIMARYADDRESSID uniqueidentifier;
declare @EXISTINGPRIMARYADDRESSTYPECODEID uniqueidentifier;
declare @INCOMINGINFOSOURCECODEID uniqueidentifier;
declare @INCOMINGINFOSOURCECOMMENTS nvarchar(256);
declare @INCOMINGCOUNTYCODEID uniqueidentifier;
declare @INCOMINGREGIONCODEID uniqueidentifier;
declare @INCOMINGCONGRESSIONALDISTRICTCODEID uniqueidentifier;
declare @INCOMINGSTATEHOUSEDISTRICTCODEID uniqueidentifier;
declare @INCOMINGSTATESENATEDISTRICTCODEID uniqueidentifier;
declare @INCOMINGLOCALPRECINCTCODEID uniqueidentifier;
declare @INCOMINGCERTIFICATIONDATA int;
declare @INCOMINGLASTVALIDATIONATTEMPTDATE date;
declare @INCOMINGOMITFROMVALIDATION bit;
declare @INCOMINGVALIDATIONMESSAGE nvarchar(200);
declare @UPDATESECONDARYDATA bit = 0; -- flag for non core fields
declare @PRIMARYCOUNT int = dbo.UFN_BATCHCONSTITUENTUPDATEADDRESSES_PRIMARYCOUNT(@ID);
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
set @CURRENTDATE = getdate();
-- Get the address match threshold
select top 1
@ADDRESSMATCHTHRESHOLD = ADDRESSMATCHTHRESHOLD
from dbo.CONSTITUENTDUPLICATESEARCHSETTINGS
--Get the existing is primary row
select top 1
@EXISTINGISPRIMARYADDRESSID = ID,
@EXISTINGPRIMARYADDRESSTYPECODEID = case when ISPRIMARY = 1 then ADDRESSTYPECODEID else null end
from dbo.ADDRESS where CONSTITUENTID = @PRIMARYRECORDID
order by ISPRIMARY desc;
declare @NULLVALUE uniqueidentifier = newid();
if @EXISTINGISPRIMARYADDRESSID is not null -- only apply the rules if the constituent has at least one address
begin
declare INCOMINGADDRESS_CURSOR cursor local fast_forward for
select
BATCHCONSTITUENTUPDATEADDRESSES.ID,
ADDRESSTYPECODEID,
COUNTRYID,
ADDRESSBLOCK,
CITY,
STATEID,
POSTCODE,
ISPRIMARY,
HISTORICALSTARTDATE,
HISTORICALENDDATE,
DONOTMAIL,
DONOTMAILREASONCODEID,
STARTDATE,
ENDDATE,
DPC,
CART,
LOT,
INFOSOURCECODEID,
INFOSOURCECOMMENTS,
COUNTYCODEID,
REGIONCODEID,
CONGRESSIONALDISTRICTCODEID,
STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID,
LOCALPRECINCTCODEID,
CERTIFICATIONDATA,
LASTVALIDATIONATTEMPTDATE,
OMITFROMVALIDATION,
VALIDATIONMESSAGE
from dbo.BATCHCONSTITUENTUPDATEADDRESSES
where BATCHCONSTITUENTUPDATEID = @ID and ADDRESSID is null and BATCHCONSTITUENTUPDATEADDRESSES.ID <> coalesce(@EXCLUDEADDRESSID, @NULLVALUE); -- exclude from the cursor the ID coming from the resolutions screen
open INCOMINGADDRESS_CURSOR;
begin try
fetch next from INCOMINGADDRESS_CURSOR into
@INCOMINGADDRESSROWID,
@INCOMINGADDRESSTYPECODEID,
@INCOMINGCOUNTRYID,
@INCOMINGADDRESSBLOCK,
@INCOMINGCITY,
@INCOMINGSTATEID,
@INCOMINGPOSTCODE,
@INCOMINGISPRIMARY,
@INCOMINGHISTORICALSTARTDATE,
@INCOMINGHISTORICALENDDATE,
@INCOMINGDONOTMAIL,
@INCOMINGDONOTMAILREASONCODEID,
@INCOMINGSTARTDATE,
@INCOMINGENDDATE,
@INCOMINGDPC,
@INCOMINGCART,
@INCOMINGLOT,
@INCOMINGINFOSOURCECODEID,
@INCOMINGINFOSOURCECOMMENTS,
@INCOMINGCOUNTYCODEID,
@INCOMINGREGIONCODEID,
@INCOMINGCONGRESSIONALDISTRICTCODEID,
@INCOMINGSTATEHOUSEDISTRICTCODEID,
@INCOMINGSTATESENATEDISTRICTCODEID,
@INCOMINGLOCALPRECINCTCODEID,
@INCOMINGCERTIFICATIONDATA,
@INCOMINGLASTVALIDATIONATTEMPTDATE,
@INCOMINGOMITFROMVALIDATION,
@INCOMINGVALIDATIONMESSAGE;
while (@@FETCH_STATUS = 0)
begin
set @SIMILARADDRESSID = null;
set @NOCONFLICTS = 0;
select top 1
@SIMILARADDRESSID = ADDRESSID,
@NOCONFLICTS = NOCONFLICTS
from dbo.UFN_FINDADDRESSMATCH_CONSTITUENTUPDATEBATCH2(@PRIMARYRECORDID, @INCOMINGADDRESSBLOCK, @INCOMINGPOSTCODE, @INCOMINGCOUNTRYID, @INCOMINGCITY, @INCOMINGSTATEID, @INCOMINGADDRESSTYPECODEID, @ADDRESSMATCHTHRESHOLD, @ID, @INCOMINGHISTORICALENDDATE);
if @SIMILARADDRESSID is not null -- If address is similar
begin
select
@SIMILARADDRESSTYPECODEID = ADDRESSTYPECODEID,
@SIMILARCOUNTRYID = COUNTRYID,
@SIMILARADDRESSBLOCK= ADDRESSBLOCK,
@SIMILARCITY = CITY,
@SIMILARSTATEID = STATEID,
@SIMILARPOSTCODE = POSTCODE,
@SIMILARISPRIMARY = ISPRIMARY,
@SIMILARHISTORICALSTARTDATE = HISTORICALSTARTDATE,
@SIMILARHISTORICALENDDATE= HISTORICALENDDATE,
@SIMILARDONOTMAIL= DONOTMAIL,
@SIMILARDONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
@SIMILARSTARTDATE= STARTDATE,
@SIMILARENDDATE = ENDDATE,
@SIMILARDPC = DPC,
@SIMILARCART= CART,
@SIMILARLOT= LOT,
@SIMILARINFOSOURCECODEID = AVU.[INFOSOURCECODEID],
@SIMILARINFOSOURCECOMMENTS = coalesce(AVU.INFOSOURCECOMMENTS, ''),
@SIMILARCOUNTYCODEID = AVU.[COUNTYCODEID],
@SIMILARREGIONCODEID = AVU.[REGIONCODEID],
@SIMILARCONGRESSIONALDISTRICTCODEID = AVU.[CONGRESSIONALDISTRICTCODEID],
@SIMILARSTATEHOUSEDISTRICTCODEID = AVU.[STATEHOUSEDISTRICTCODEID],
@SIMILARSTATESENATEDISTRICTCODEID = AVU.[STATESENATEDISTRICTCODEID],
@SIMILARCERTIFICATIONDATA = coalesce(AVU.[CERTIFICATIONDATA], '0'),
@SIMILARLASTVALIDATIONATTEMPTDATE = AVU.[LASTVALIDATIONATTEMPTDATE],
@SIMILAROMITFROMVALIDATION = coalesce(AVU.[OMITFROMVALIDATION], cast(0 as bit)),
@SIMILARVALIDATIONMESSAGE = coalesce(AVU.[VALIDATIONMESSAGE], '')
from dbo.ADDRESS
left join dbo.[ADDRESSVALIDATIONUPDATE] AVU on ADDRESS.[ID] = AVU.[ID]
where ADDRESS.ID = @SIMILARADDRESSID;
if @SIMILARADDRESSCODE = 1 or @NOCONFLICTS = 1 -- Update
begin
if @SIMILARADDRESSCODE <> 0
set @UPDATESECONDARYDATA = 1
-- If address has no conflicts in the core fields then check secondary fields for conflicts and apply rules accordingly.
else if (@SIMILARHISTORICALSTARTDATE is null or @SIMILARHISTORICALSTARTDATE = @INCOMINGHISTORICALSTARTDATE or @INCOMINGHISTORICALSTARTDATE is null) and
(@SIMILARHISTORICALENDDATE is null or @SIMILARHISTORICALENDDATE = @INCOMINGHISTORICALENDDATE) and
(@SIMILARDONOTMAIL = @INCOMINGDONOTMAIL or @INCOMINGDONOTMAIL = 0) and
(@SIMILARDONOTMAILREASONCODEID is null or @INCOMINGDONOTMAILREASONCODEID is null or @SIMILARDONOTMAILREASONCODEID = @INCOMINGDONOTMAILREASONCODEID) and
(@SIMILARSTARTDATE = '0000' or @SIMILARSTARTDATE = @INCOMINGSTARTDATE or @INCOMINGSTARTDATE = '0000') and
(@SIMILARENDDATE = '0000' or @SIMILARENDDATE = @INCOMINGENDDATE or @INCOMINGENDDATE = '0000') and
(@SIMILARDPC = '' or @SIMILARDPC = @INCOMINGDPC or @INCOMINGDPC = '') and
(@SIMILARCART = '' or @SIMILARCART = @INCOMINGCART or @INCOMINGCART = '') and
(@SIMILARLOT = '' or @SIMILARLOT = @INCOMINGLOT or @INCOMINGLOT = '') and
(@SIMILARINFOSOURCECODEID is null or @INCOMINGINFOSOURCECODEID is null or @SIMILARINFOSOURCECODEID = @INCOMINGINFOSOURCECODEID ) and
(@SIMILARINFOSOURCECOMMENTS = '' or @SIMILARINFOSOURCECOMMENTS = @INCOMINGINFOSOURCECOMMENTS or @INCOMINGINFOSOURCECOMMENTS = '') and
(@SIMILARCOUNTYCODEID is null or @INCOMINGCOUNTYCODEID is null or @SIMILARCOUNTYCODEID = @INCOMINGCOUNTYCODEID) and
(@SIMILARREGIONCODEID is null or @INCOMINGREGIONCODEID is null or @SIMILARREGIONCODEID = @INCOMINGREGIONCODEID) and
(@SIMILARCONGRESSIONALDISTRICTCODEID is null or @INCOMINGCONGRESSIONALDISTRICTCODEID is null or @SIMILARCONGRESSIONALDISTRICTCODEID = @INCOMINGCONGRESSIONALDISTRICTCODEID) and
(@SIMILARSTATEHOUSEDISTRICTCODEID is null or @INCOMINGSTATEHOUSEDISTRICTCODEID is null or @SIMILARSTATEHOUSEDISTRICTCODEID = @INCOMINGSTATEHOUSEDISTRICTCODEID) and
(@SIMILARSTATESENATEDISTRICTCODEID is null or @INCOMINGSTATESENATEDISTRICTCODEID is null or @SIMILARSTATESENATEDISTRICTCODEID = @INCOMINGSTATESENATEDISTRICTCODEID) and
(@SIMILARLOCALPRECINCTCODEID is null or @INCOMINGLOCALPRECINCTCODEID is null or @SIMILARLOCALPRECINCTCODEID = @INCOMINGLOCALPRECINCTCODEID) and
(@SIMILARCERTIFICATIONDATA = @INCOMINGCERTIFICATIONDATA or @INCOMINGCERTIFICATIONDATA = '0') and
(@SIMILARLASTVALIDATIONATTEMPTDATE is null or @SIMILARLASTVALIDATIONATTEMPTDATE = @INCOMINGLASTVALIDATIONATTEMPTDATE or @INCOMINGLASTVALIDATIONATTEMPTDATE = '') and
(@SIMILAROMITFROMVALIDATION = @INCOMINGOMITFROMVALIDATION or @INCOMINGOMITFROMVALIDATION = 0) and
(@SIMILARVALIDATIONMESSAGE = '' or @SIMILARVALIDATIONMESSAGE = @INCOMINGVALIDATIONMESSAGE or @INCOMINGVALIDATIONMESSAGE = '')
set @UPDATESECONDARYDATA = 1
else
set @UPDATESECONDARYDATA = 0
if @UPDATESECONDARYDATA = 1 -- Update all fields
begin
update dbo.BATCHCONSTITUENTUPDATEADDRESSES set
ADDRESSID = @SIMILARADDRESSID,
ADDRESSTYPECODEID = coalesce(ADDRESSTYPECODEID,@SIMILARADDRESSTYPECODEID),
COUNTRYID = coalesce(COUNTRYID,@SIMILARCOUNTRYID),
ADDRESSBLOCK = coalesce(NULLIF(ADDRESSBLOCK,''),@SIMILARADDRESSBLOCK),
CITY = coalesce(NULLIF(CITY,''),@SIMILARCITY),
STATEID = coalesce(STATEID,@SIMILARSTATEID),
POSTCODE = coalesce(NULLIF(POSTCODE,''),@SIMILARPOSTCODE),
HISTORICALSTARTDATE = coalesce(HISTORICALSTARTDATE,@SIMILARHISTORICALSTARTDATE),
--In this case blank has meaning so we don't want to coalesce, just keep what's on the incoming
--HISTORICALENDDATE = coalesce(HISTORICALENDDATE,@SIMILARHISTORICALENDDATE),
--DONOTMAIL = coalesce(NULLIF(DONOTMAIL,0),@SIMILARDONOTMAIL),
--DONOTMAILREASONCODEID = coalesce(DONOTMAILREASONCODEID,@SIMILARDONOTMAILREASONCODEID),
STARTDATE = coalesce(NULLIF(STARTDATE,'0000'),@SIMILARSTARTDATE),
ENDDATE = coalesce(NULLIF(ENDDATE,'0000'),@SIMILARENDDATE),
DPC = coalesce(NULLIF(DPC,''),@SIMILARDPC),
CART = coalesce(NULLIF(CART,''),@SIMILARCART),
LOT = coalesce(NULLIF(LOT,''),@SIMILARLOT),
INFOSOURCECODEID = coalesce(INFOSOURCECODEID,@SIMILARINFOSOURCECODEID),
INFOSOURCECOMMENTS = coalesce(NULLIF(INFOSOURCECOMMENTS,''), @SIMILARINFOSOURCECOMMENTS),
COUNTYCODEID = coalesce(COUNTYCODEID,@SIMILARCOUNTYCODEID),
REGIONCODEID = coalesce(REGIONCODEID,@SIMILARREGIONCODEID),
CONGRESSIONALDISTRICTCODEID = coalesce(CONGRESSIONALDISTRICTCODEID,@SIMILARCONGRESSIONALDISTRICTCODEID),
STATEHOUSEDISTRICTCODEID = coalesce(STATEHOUSEDISTRICTCODEID,@SIMILARSTATEHOUSEDISTRICTCODEID),
STATESENATEDISTRICTCODEID = coalesce(STATESENATEDISTRICTCODEID,@SIMILARSTATESENATEDISTRICTCODEID),
LOCALPRECINCTCODEID = coalesce(LOCALPRECINCTCODEID,@SIMILARLOCALPRECINCTCODEID),
CERTIFICATIONDATA = coalesce(NULLIF(CERTIFICATIONDATA,0),@SIMILARCERTIFICATIONDATA),
LASTVALIDATIONATTEMPTDATE = coalesce(LASTVALIDATIONATTEMPTDATE,@SIMILARLASTVALIDATIONATTEMPTDATE),
OMITFROMVALIDATION = coalesce(NULLIF(OMITFROMVALIDATION,0),@SIMILAROMITFROMVALIDATION),
VALIDATIONMESSAGE = coalesce(NULLIF(VALIDATIONMESSAGE,''), @SIMILARVALIDATIONMESSAGE),
ISPRIMARY = case when @PRIMARYCOUNT = 1 then ISPRIMARY else @SIMILARISPRIMARY end, -- Do not set ISPRIMARY = 1 if a primary already exists. May be changed by rules below.
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @INCOMINGADDRESSROWID;
end
else -- update only core fields and ignore secondary fields
begin
update dbo.BATCHCONSTITUENTUPDATEADDRESSES set
ADDRESSID = @SIMILARADDRESSID,
ADDRESSTYPECODEID = coalesce(ADDRESSTYPECODEID,@SIMILARADDRESSTYPECODEID),
COUNTRYID = coalesce(COUNTRYID,@SIMILARCOUNTRYID),
ADDRESSBLOCK = coalesce(NULLIF(ADDRESSBLOCK,''),@SIMILARADDRESSBLOCK),
CITY = coalesce(NULLIF(CITY,''),@SIMILARCITY),
STATEID = coalesce(STATEID,@SIMILARSTATEID),
POSTCODE = coalesce(NULLIF(POSTCODE,''),@SIMILARPOSTCODE),
HISTORICALSTARTDATE = @SIMILARHISTORICALSTARTDATE,
HISTORICALENDDATE = @SIMILARHISTORICALENDDATE,
DONOTMAIL = @SIMILARDONOTMAIL,
DONOTMAILREASONCODEID = @SIMILARDONOTMAILREASONCODEID,
STARTDATE = @SIMILARSTARTDATE,
ENDDATE = @SIMILARENDDATE,
DPC = @SIMILARDPC,
CART = @SIMILARCART,
LOT = @SIMILARLOT,
INFOSOURCECODEID = @SIMILARINFOSOURCECODEID,
INFOSOURCECOMMENTS = @SIMILARINFOSOURCECOMMENTS,
COUNTYCODEID = @SIMILARCOUNTYCODEID,
REGIONCODEID = @SIMILARREGIONCODEID,
CONGRESSIONALDISTRICTCODEID = @SIMILARCONGRESSIONALDISTRICTCODEID,
STATEHOUSEDISTRICTCODEID = @SIMILARSTATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID = @SIMILARSTATESENATEDISTRICTCODEID,
LOCALPRECINCTCODEID = @SIMILARLOCALPRECINCTCODEID,
CERTIFICATIONDATA = @SIMILARCERTIFICATIONDATA,
LASTVALIDATIONATTEMPTDATE = @SIMILARLASTVALIDATIONATTEMPTDATE,
OMITFROMVALIDATION = @SIMILAROMITFROMVALIDATION,
VALIDATIONMESSAGE = @SIMILARVALIDATIONMESSAGE,
ISPRIMARY = case when @PRIMARYCOUNT = 1 then ISPRIMARY else @SIMILARISPRIMARY end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @INCOMINGADDRESSROWID;
end
end
else if @SIMILARADDRESSCODE = 0 -- Ignore all fields
begin
update dbo.BATCHCONSTITUENTUPDATEADDRESSES set
ADDRESSID = @SIMILARADDRESSID,
ADDRESSTYPECODEID = @SIMILARADDRESSTYPECODEID,
COUNTRYID = @SIMILARCOUNTRYID,
ADDRESSBLOCK = @SIMILARADDRESSBLOCK,
CITY = @SIMILARCITY,
STATEID = @SIMILARSTATEID,
POSTCODE = @SIMILARPOSTCODE,
HISTORICALSTARTDATE = @SIMILARHISTORICALSTARTDATE,
HISTORICALENDDATE = @SIMILARHISTORICALENDDATE,
DONOTMAIL = @SIMILARDONOTMAIL,
DONOTMAILREASONCODEID = @SIMILARDONOTMAILREASONCODEID,
STARTDATE = @SIMILARSTARTDATE,
ENDDATE = @SIMILARENDDATE,
DPC = @SIMILARDPC,
CART = @SIMILARCART,
LOT = @SIMILARLOT,
INFOSOURCECODEID = @SIMILARINFOSOURCECODEID,
INFOSOURCECOMMENTS = @SIMILARINFOSOURCECOMMENTS,
COUNTYCODEID = @SIMILARCOUNTYCODEID,
REGIONCODEID = @SIMILARREGIONCODEID,
CONGRESSIONALDISTRICTCODEID = @SIMILARCONGRESSIONALDISTRICTCODEID,
STATEHOUSEDISTRICTCODEID = @SIMILARSTATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID = @SIMILARSTATESENATEDISTRICTCODEID,
LOCALPRECINCTCODEID = @SIMILARLOCALPRECINCTCODEID,
CERTIFICATIONDATA = @SIMILARCERTIFICATIONDATA,
LASTVALIDATIONATTEMPTDATE = @SIMILARLASTVALIDATIONATTEMPTDATE,
OMITFROMVALIDATION = @SIMILAROMITFROMVALIDATION,
VALIDATIONMESSAGE = @SIMILARVALIDATIONMESSAGE,
ISPRIMARY = @SIMILARISPRIMARY,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @INCOMINGADDRESSROWID;
end
-- If the address is being updated/ignored and address type is being updated or the address is being added then store the row ID for this address type
if @SIMILARADDRESSCODE in (0,1,3)
begin
set @SIMILARADDRESSTYPECODEID = case when @SIMILARADDRESSCODE = 1 or @NOCONFLICTS = 1 -- Update
then coalesce(@INCOMINGADDRESSTYPECODEID,@SIMILARADDRESSTYPECODEID)
when @SIMILARADDRESSCODE = 0 -- Ignore
then @SIMILARADDRESSTYPECODEID
when @SIMILARADDRESSCODE = 3 -- Add
then @INCOMINGADDRESSTYPECODEID
end;
if @SIMILARADDRESSCODE = 3 and @NOCONFLICTS = 0 -- Add
begin
if @INCOMINGISPRIMARY = 1 or (@MAKENEWPRIMARYROWID is null and (@NEWADDRESSPRIMARYCODE = 2 or (@NEWADDRESSPRIMARYCODE = 1 and @INCOMINGADDRESSTYPECODEID = @EXISTINGPRIMARYADDRESSTYPECODEID)))
-- Always make new ADDRESS primary/Old ADDRESS has the same type as the new ADDRESS
set @MAKENEWPRIMARYROWID = @INCOMINGADDRESSROWID;
end
end
end
else -- Incoming address is not similar
begin
if @UNSIMILARADDRESSCODE = 0 -- Ignore
delete from dbo.BATCHCONSTITUENTUPDATEADDRESSES
where ID = @INCOMINGADDRESSROWID;
else if @UNSIMILARADDRESSCODE = 3 -- Add
begin
if (@NEWADDRESSPRIMARYCODE = 2
or (
@NEWADDRESSPRIMARYCODE = 1
and @INCOMINGADDRESSTYPECODEID = @EXISTINGPRIMARYADDRESSTYPECODEID
and @EXISTINGPRIMARYADDRESSTYPECODEID is not null
)
)
and @INCOMINGHISTORICALENDDATE is null
and @INCOMINGDONOTMAIL = 0 -- Cannot make address with end dates primary under any condition
-- Always make new ADDRESS primary/Old ADDRESS has the same type as the new ADDRESS
set @MAKENEWPRIMARYROWID = @INCOMINGADDRESSROWID;
end
end
fetch next from INCOMINGADDRESS_CURSOR into
@INCOMINGADDRESSROWID,
@INCOMINGADDRESSTYPECODEID,
@INCOMINGCOUNTRYID,
@INCOMINGADDRESSBLOCK,
@INCOMINGCITY,
@INCOMINGSTATEID,
@INCOMINGPOSTCODE,
@INCOMINGISPRIMARY,
@INCOMINGHISTORICALSTARTDATE,
@INCOMINGHISTORICALENDDATE,
@INCOMINGDONOTMAIL,
@INCOMINGDONOTMAILREASONCODEID,
@INCOMINGSTARTDATE,
@INCOMINGENDDATE,
@INCOMINGDPC,
@INCOMINGCART,
@INCOMINGLOT,
@INCOMINGINFOSOURCECODEID,
@INCOMINGINFOSOURCECOMMENTS,
@INCOMINGCOUNTYCODEID,
@INCOMINGREGIONCODEID,
@INCOMINGCONGRESSIONALDISTRICTCODEID,
@INCOMINGSTATEHOUSEDISTRICTCODEID,
@INCOMINGSTATESENATEDISTRICTCODEID,
@INCOMINGLOCALPRECINCTCODEID,
@INCOMINGCERTIFICATIONDATA,
@INCOMINGLASTVALIDATIONATTEMPTDATE,
@INCOMINGOMITFROMVALIDATION,
@INCOMINGVALIDATIONMESSAGE;
end
close INCOMINGADDRESS_CURSOR;
deallocate INCOMINGADDRESS_CURSOR;
end try
begin catch
close INCOMINGADDRESS_CURSOR;
deallocate INCOMINGADDRESS_CURSOR;
exec dbo.USP_RAISE_ERROR;
end catch
-- set all potential primary addresses to non-primary, except for the MAKENEWPRIMARYROWID address
if @MAKENEWPRIMARYROWID is not null
begin
update dbo.BATCHCONSTITUENTUPDATEADDRESSES
set ISPRIMARY = 0
where ID <> @MAKENEWPRIMARYROWID and ISPRIMARY = 1
and BATCHCONSTITUENTUPDATEID = @ID;
end
if @ADDALLADDRESSES = 1
begin
insert into dbo.BATCHCONSTITUENTUPDATEADDRESSES(
ID,
ADDRESSID,
BATCHCONSTITUENTUPDATEID,
ADDRESSBLOCK,
ENDDATE,
STARTDATE,
HISTORICALSTARTDATE,
ADDRESSTYPECODEID,
CART,
CERTIFICATIONDATA,
CITY,
CONGRESSIONALDISTRICTCODEID,
COUNTRYID,
COUNTYCODEID,
DONOTMAIL,
DONOTMAILREASONCODEID,
DPC,
INFOSOURCECODEID,
ISPRIMARY,
LASTVALIDATIONATTEMPTDATE,
LOCALPRECINCTCODEID,
LOT,
OMITFROMVALIDATION,
REGIONCODEID,
STATEID,
STATEHOUSEDISTRICTCODEID,
STATESENATEDISTRICTCODEID,
VALIDATIONMESSAGE,
POSTCODE,
SEQUENCE,
UPDATEHOUSEHOLD,
INFOSOURCECOMMENTS,
HISTORICALENDDATE,
ADDEDBYID,
CHANGEDBYID,
ROWORIGINCODE
)
select
newid(),
A.ID as ADDRESSID,
@ID,
coalesce(A.[ADDRESSBLOCK], '')as ADDRESSBLOCK,
coalesce(A.[ENDDATE], '0000') as ENDDATE,
coalesce(A.[STARTDATE], '0000') as STARTDATE,
A.[HISTORICALSTARTDATE],
A.[ADDRESSTYPECODEID],
coalesce(A.[CART], '') as CART,
coalesce(AVU.[CERTIFICATIONDATA], '0') as CERTIFICATIONDATA,
coalesce(A.[CITY], '') as CITY,
AVU.[CONGRESSIONALDISTRICTCODEID],
A.[COUNTRYID],
AVU.[COUNTYCODEID],
coalesce(A.[DONOTMAIL], cast(0 as bit)) as DONOTMAIL,
A.[DONOTMAILREASONCODEID],
coalesce(A.[DPC], '') as DPC,
AVU.[INFOSOURCECODEID],
case when exists(select ISPRIMARY from dbo.BATCHCONSTITUENTUPDATEADDRESSES where BATCHCONSTITUENTUPDATEID = @ID and ISPRIMARY = 1) or @MAKENEWPRIMARYROWID is not null then 0 else ISPRIMARY end,
AVU.[LASTVALIDATIONATTEMPTDATE],
AVU.[LOCALPRECINCTCODEID],
coalesce(A.[LOT], '') as LOT,
coalesce(AVU.[OMITFROMVALIDATION], cast(0 as bit)) as OMITFROMVALIDATION,
AVU.[REGIONCODEID],
A.[STATEID],
AVU.[STATEHOUSEDISTRICTCODEID],
AVU.[STATESENATEDISTRICTCODEID],
coalesce(AVU.[VALIDATIONMESSAGE], '') as VALIDATIONMESSAGE,
coalesce(A.[POSTCODE], '') as POSTCODE,
A.[SEQUENCE],
cast(0 as bit) as UPDATEHOUSEHOLD,
coalesce(AVU.INFOSOURCECOMMENTS, '') as INFOSOURCECOMMENTS,
A.HISTORICALENDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
2 -- Existing record, automatically populated by the system during import
from dbo.[ADDRESS] A
left join dbo.[ADDRESSVALIDATIONUPDATE] AVU on A.[ID] = AVU.[ID]
where A.CONSTITUENTID = @PRIMARYRECORDID and A.ID not in (select ADDRESSID from dbo.BATCHCONSTITUENTUPDATEADDRESSES where BATCHCONSTITUENTUPDATEID = @ID and ADDRESSID is not null);
end
-- set the MAKENEWPRIMARYROWID address as primary
if @MAKENEWPRIMARYROWID is not null
begin
update dbo.BATCHCONSTITUENTUPDATEADDRESSES
set ISPRIMARY = 1
where ID = @MAKENEWPRIMARYROWID;
end
end
end