USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTWINDOW_2
The save procedure used by the edit dataform template "Revenue Batch Constituent Window Edit Form 2".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @LASTNAME | nvarchar(100) | IN | Last name |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @FIRSTNAME | nvarchar(50) | IN | First name |
| @MIDDLENAME | nvarchar(50) | IN | Middle name |
| @MAIDENNAME | nvarchar(100) | IN | Maiden name |
| @NICKNAME | nvarchar(50) | IN | Nickname |
| @TITLECODEID | uniqueidentifier | IN | Title |
| @SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @GENDERCODE | tinyint | IN | Gender |
| @BIRTHDATE | UDT_FUZZYDATE | IN | Birth date |
| @GIVESANONYMOUSLY | bit | IN | Gives anonymously |
| @ADDRESS_STATEID | uniqueidentifier | IN | State |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @ADDRESS_CITY | nvarchar(50) | IN | City |
| @ADDRESS_POSTCODE | nvarchar(12) | IN | ZIP |
| @PHONE_PHONETYPECODEID | uniqueidentifier | IN | Phone type |
| @PHONE_NUMBER | nvarchar(100) | IN | Phone number |
| @UPDATEMATCHINGSPOUSECONTACT | bit | IN | Update matching contact information for spouse |
| @ADDRESS_COUNTRYID | uniqueidentifier | IN | Country |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTWINDOW_2(
@ID uniqueidentifier,
@LASTNAME nvarchar(100),
@CHANGEAGENTID uniqueidentifier = null,
@FIRSTNAME nvarchar(50),
@MIDDLENAME nvarchar(50),
@MAIDENNAME nvarchar(100),
@NICKNAME nvarchar(50),
@TITLECODEID uniqueidentifier,
@SUFFIXCODEID uniqueidentifier,
@GENDERCODE tinyint,
@BIRTHDATE dbo.UDT_FUZZYDATE,
@GIVESANONYMOUSLY bit,
@ADDRESS_STATEID uniqueidentifier,
@ADDRESS_ADDRESSBLOCK nvarchar(150),
@ADDRESS_CITY nvarchar(50),
@ADDRESS_POSTCODE nvarchar(12),
@PHONE_PHONETYPECODEID uniqueidentifier,
@PHONE_NUMBER nvarchar(100),
@UPDATEMATCHINGSPOUSECONTACT bit,
@ADDRESS_COUNTRYID uniqueidentifier
)
as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
update
dbo.[CONSTITUENT]
set
[KEYNAME] = @LASTNAME,
[FIRSTNAME] = @FIRSTNAME,
[MIDDLENAME] = @MIDDLENAME,
[MAIDENNAME] = @MAIDENNAME,
[NICKNAME] = @NICKNAME,
[TITLECODEID] = @TITLECODEID,
[SUFFIXCODEID] = @SUFFIXCODEID,
[GENDERCODE] = @GENDERCODE,
[BIRTHDATE] = @BIRTHDATE,
[GIVESANONYMOUSLY] = @GIVESANONYMOUSLY,
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = @CHANGEAGENTID
where
[CONSTITUENT].ID = @ID;
if len(@ADDRESS_ADDRESSBLOCK) > 0 or len(@ADDRESS_CITY) > 0 or @ADDRESS_STATEID is not null or len(@ADDRESS_POSTCODE) > 0 or @ADDRESS_COUNTRYID is not null
begin
declare @ADDRESS_OLDCOUNTRYID uniqueidentifier;
declare @ADDRESS_OLDSTATEID uniqueidentifier;
declare @ADDRESS_OLDADDRESSBLOCK nvarchar(150);
declare @ADDRESS_OLDCITY nvarchar(50);
declare @ADDRESS_OLDPOSTCODE nvarchar(12);
select
@ADDRESS_OLDCOUNTRYID = ADDRESS.COUNTRYID,
@ADDRESS_OLDSTATEID = ADDRESS.STATEID,
@ADDRESS_OLDADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
@ADDRESS_OLDCITY = ADDRESS.CITY,
@ADDRESS_OLDPOSTCODE = ADDRESS.POSTCODE
from dbo.[ADDRESS]
where ADDRESS.CONSTITUENTID = @ID and ADDRESS.ISPRIMARY = 1;
update dbo.ADDRESS set
COUNTRYID = @ADDRESS_COUNTRYID,
STATEID = @ADDRESS_STATEID,
ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK,
CITY = @ADDRESS_CITY,
POSTCODE = @ADDRESS_POSTCODE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where CONSTITUENTID = @ID and ISPRIMARY = 1;
if @@ROWCOUNT = 0
insert into dbo.ADDRESS (CONSTITUENTID, ADDRESSBLOCK, CITY, COUNTRYID, STATEID, POSTCODE, ISPRIMARY, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values (@ID, @ADDRESS_ADDRESSBLOCK, @ADDRESS_CITY, @ADDRESS_COUNTRYID, @ADDRESS_STATEID, @ADDRESS_POSTCODE, 1, @CURRENTDATE, @CURRENTDATE,@CHANGEAGENTID, @CHANGEAGENTID);
if @UPDATEMATCHINGSPOUSECONTACT = 1 and exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP')
update
dbo.ADDRESS
set
COUNTRYID = @ADDRESS_COUNTRYID,
STATEID = @ADDRESS_STATEID,
ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK,
CITY = @ADDRESS_CITY,
POSTCODE = @ADDRESS_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 = @ID and
RELATIONSHIP.ISSPOUSE = 1 and
SPOUSEADDRESS.COUNTRYID = @ADDRESS_OLDCOUNTRYID and
SPOUSEADDRESS.ADDRESSBLOCK = @ADDRESS_OLDADDRESSBLOCK and
SPOUSEADDRESS.CITY = @ADDRESS_OLDCITY and
(SPOUSEADDRESS.STATEID = @ADDRESS_OLDSTATEID or (SPOUSEADDRESS.STATEID is null and @ADDRESS_OLDSTATEID is null)) and
SPOUSEADDRESS.POSTCODE = @ADDRESS_OLDPOSTCODE
);
end;
if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER,'') <> '')
begin
declare @PHONE_OLDNUMBER nvarchar(100);
select
@PHONE_OLDNUMBER = PHONE.NUMBER
from dbo.[PHONE]
where PHONE.CONSTITUENTID = @ID and PHONE.ISPRIMARY = 1;
update dbo.PHONE set
PHONETYPECODEID = @PHONE_PHONETYPECODEID,
NUMBER = @PHONE_NUMBER,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where CONSTITUENTID = @ID and ISPRIMARY = 1;
if @@ROWCOUNT = 0
insert into dbo.PHONE (CONSTITUENTID, PHONETYPECODEID, NUMBER, ISPRIMARY, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values (@ID, @PHONE_PHONETYPECODEID, @PHONE_NUMBER, 1, @CURRENTDATE, @CURRENTDATE,@CHANGEAGENTID, @CHANGEAGENTID);
if @UPDATEMATCHINGSPOUSECONTACT = 1 and exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP')
update
dbo.PHONE
set
NUMBER = @PHONE_NUMBER,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
PHONE.ID in (
select
SPOUSEPHONE.ID
from
dbo.RELATIONSHIP
left join dbo.PHONE as SPOUSEPHONE on SPOUSEPHONE.CONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and
RELATIONSHIP.ISSPOUSE = 1 and
SPOUSEPHONE.NUMBER = @PHONE_OLDNUMBER
);
end;
else
if exists(select ID from dbo.PHONE where CONSTITUENTID = @ID and ISPRIMARY = 1)
begin
delete from dbo.PHONE where CONSTITUENTID = @ID and ISPRIMARY = 1;
end;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;