USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTADDRESSUPDATEBATCHCOMMIT
The save procedure used by the edit dataform template "Constituent Address Update Batch Row Commit 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 | Address type |
| @COUNTRYID | uniqueidentifier | IN | Country |
| @ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @CITY | nvarchar(50) | IN | City |
| @STATEID | uniqueidentifier | IN | State |
| @POSTCODE | nvarchar(12) | IN | ZIP |
| @ISPRIMARY | bit | IN | Set as primary address |
| @DONOTMAIL | bit | IN | Do not send mail to this address |
| @UPDATEMATCHINGSPOUSEADDRESSES | bit | IN | Update spouse address |
| @VALIDATEONLY | bit | IN | Validate only |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTADDRESSUPDATEBATCHCOMMIT
(
@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
) as
set nocount on;
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;
begin try
if @VALIDATEONLY = 0
begin
declare @CONSTITUENTID uniqueidentifier;
declare @OLDCOUNTRYID uniqueidentifier;
declare @OLDADDRESSBLOCK nvarchar(150);
declare @OLDCITY nvarchar(50);
declare @OLDSTATEID uniqueidentifier;
declare @OLDPOSTCODE nvarchar(12);
select
@CONSTITUENTID = ADDRESS.[CONSTITUENTID],
@OLDCOUNTRYID = ADDRESS.[COUNTRYID],
@OLDADDRESSBLOCK = ADDRESS.[ADDRESSBLOCK],
@OLDCITY = ADDRESS.[CITY],
@OLDSTATEID = ADDRESS.[STATEID],
@OLDPOSTCODE = ADDRESS.[POSTCODE]
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
where
[ID] = @ID;
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,
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
);
end;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;