USP_DATAFORMTEMPLATE_EDIT_ADDRESS
The save procedure used by the edit dataform template "Address Edit Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @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 | Update matching addresses in household |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ADDRESS
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@ADDRESSTYPECODEID uniqueidentifier,
@PRIMARY bit,
@DONOTMAIL bit,
@STARTDATE dbo.UDT_MONTHDAY,
@ENDDATE dbo.UDT_MONTHDAY,
@COUNTRYID uniqueidentifier,
@STATEID uniqueidentifier,
@ADDRESSBLOCK nvarchar(150),
@CITY nvarchar(50),
@POSTCODE nvarchar(12),
@CART nvarchar(10),
@DPC nvarchar(8),
@LOT nvarchar(5),
@UPDATEMATCHINGSPOUSEADDRESSES bit,
@UPDATEMATCHINGHOUSEHOLDADDRESSES bit
)
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 @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
declare @CONSTITUENTID uniqueidentifier;
declare @OLDCOUNTRYID uniqueidentifier;
declare @OLDSTATEID uniqueidentifier;
declare @OLDADDRESSBLOCK nvarchar(150);
declare @OLDCITY nvarchar(50);
declare @OLDPOSTCODE nvarchar(12);
declare @OLDADDRESSTYPECODEID uniqueidentifier;
select
@CONSTITUENTID = ADDRESS.[CONSTITUENTID],
@OLDCOUNTRYID = ADDRESS.[COUNTRYID],
@OLDADDRESSBLOCK = ADDRESS.[ADDRESSBLOCK],
@OLDCITY = ADDRESS.[CITY],
@OLDSTATEID = ADDRESS.[STATEID],
@OLDPOSTCODE = ADDRESS.[POSTCODE],
@OLDADDRESSTYPECODEID = ADDRESS.[ADDRESSTYPECODEID]
from
dbo.ADDRESS
where
ADDRESS.[ID] = @ID;
begin try
if @PRIMARY = 1
update
dbo.[ADDRESS]
set
ISPRIMARY = 0,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1 and ID <> @ID;
update
dbo.ADDRESS
set
ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
ISPRIMARY = @PRIMARY,
DONOTMAIL = @DONOTMAIL,
STARTDATE = @STARTDATE,
ENDDATE = @ENDDATE,
COUNTRYID = @COUNTRYID,
STATEID = @STATEID,
ADDRESSBLOCK = @ADDRESSBLOCK,
CITY = @CITY,
POSTCODE = @POSTCODE,
CART = @CART,
DPC = @DPC,
LOT = @LOT,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @ID;
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);
-- remove primary indicator if we're going to update with a new one
if @PRIMARY = 1
update dbo.ADDRESS
set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID in (select ID from @MATCHINGCONSTITUENTS);
-- update the existing records
update dbo.ADDRESS
set
ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
DONOTMAIL = @DONOTMAIL,
STARTDATE = @STARTDATE,
ENDDATE = @ENDDATE,
COUNTRYID = @COUNTRYID,
STATEID = @STATEID,
ADDRESSBLOCK = @ADDRESSBLOCK,
CITY = @CITY,
POSTCODE = @POSTCODE,
CART = @CART,
DPC = @DPC,
LOT = @LOT,
ISPRIMARY = @PRIMARY,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
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 = @ADDRESSTYPECODEID
and CONSTITUENTID in (select ID from @MATCHINGCONSTITUENTS);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;