USP_DATAFORMTEMPLATE_ADD_ADDRESS
The save procedure used by the add dataform template "Address Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | Type |
| @PRIMARY | bit | IN | Set as primary address |
| @DONOTMAIL | bit | IN | Do not send mail to this address |
| @STARTDATE | UDT_MONTHDAY | IN | Start date |
| @ENDDATE | UDT_MONTHDAY | IN | End date |
| @COUNTRYID | uniqueidentifier | IN | Country |
| @STATEID | uniqueidentifier | IN | State |
| @ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @CITY | nvarchar(50) | IN | City |
| @POSTCODE | nvarchar(12) | IN | ZIP |
| @CART | nvarchar(10) | IN | CART |
| @DPC | nvarchar(8) | IN | DPC |
| @LOT | nvarchar(5) | IN | LOT |
| @UPDATEMATCHINGSPOUSEADDRESSES | bit | IN | Update matching address information for spouse |
| @UPDATEMATCHINGHOUSEHOLDADDRESSES | bit | IN | Copy address information to household members |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ADDRESS
(
@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,
@UPDATEMATCHINGHOUSEHOLDADDRESSES bit = null
) as
set nocount on;
declare @CURRENTDATE datetime;
-- @UPDATEMATCHINGSPOUSEADDRESSES has been deprecated in favor of updating all of the members of a household
set @UPDATEMATCHINGHOUSEHOLDADDRESSES = case when @UPDATEMATCHINGSPOUSEADDRESSES = 1 then 1 else @UPDATEMATCHINGHOUSEHOLDADDRESSES end;
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;
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],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[CART],
[DPC],
[LOT],
[SEQUENCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@CONSTITUENTID,
@ADDRESSTYPECODEID,
@PRIMARY,
@DONOTMAIL,
@STARTDATE,
@ENDDATE,
@COUNTRYID,
@STATEID,
@ADDRESSBLOCK,
@CITY,
@POSTCODE,
@CART,
@DPC,
@LOT,
@SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if @UPDATEMATCHINGHOUSEHOLDADDRESSES = 1 begin
declare @EARLIESTTIMECURRENTDATE date;
set @EARLIESTTIMECURRENTDATE = getdate();
-- 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)
-- remove primary indicator if we're going to insert a new one
if @PRIMARY = 1
update dbo.ADDRESS
set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID in (select ID from @IDSTOUPDATE)
and
not exists (
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
and CONSTITUENTID in (select ID from @IDSTOUPDATE)
)
-- insert where there are no records with the new number
insert into dbo.ADDRESS
(CONSTITUENTID,ADDRESSTYPECODEID,DONOTMAIL,STARTDATE,ENDDATE,COUNTRYID,STATEID,ADDRESSBLOCK,CITY,POSTCODE,CART,DPC,LOT,ISPRIMARY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
select
ID,@ADDRESSTYPECODEID,@DONOTMAIL,@STARTDATE,@ENDDATE,@COUNTRYID,@STATEID,@ADDRESSBLOCK,@CITY,@POSTCODE,@CART,@DPC,@LOT,@PRIMARY,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
from
@IDSTOUPDATE
where
not exists (
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
and CONSTITUENTID in (select ID from @IDSTOUPDATE)
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;