USP_BBNC_COMMITPROFILEUPDATEBIOGRAPHICAL_1
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @BIRTHDATE | UDT_FUZZYDATE | IN | |
| @FIRSTNAME | nvarchar(50) | IN | |
| @GENDERCODE | tinyint | IN | |
| @KEYNAME | nvarchar(100) | IN | |
| @MAIDENNAME | nvarchar(100) | IN | |
| @MARITALSTATUSCODEID | uniqueidentifier | IN | |
| @MIDDLENAME | nvarchar(50) | IN | |
| @NICKNAME | nvarchar(50) | IN | |
| @TITLECODEID | uniqueidentifier | IN | |
| @SUFFIXCODEID | uniqueidentifier | IN | |
| @REQUESTNOEMAIL | bit | IN | |
| @CUSTOMIDENTIFIER | nvarchar(36) | IN | |
| @WEBADDRESS | UDT_WEBADDRESS | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @TITLE2CODEID | uniqueidentifier | IN | |
| @SUFFIX2CODEID | uniqueidentifier | IN | |
| @CONSTITUENCYCODEID | uniqueidentifier | IN | |
| @GENDERCODEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEBIOGRAPHICAL_1
(
@ID uniqueidentifier = null,
@BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@FIRSTNAME nvarchar(50) = '',
@GENDERCODE tinyint = 0,
@KEYNAME nvarchar(100),
@MAIDENNAME nvarchar(100) = '',
@MARITALSTATUSCODEID uniqueidentifier = null,
@MIDDLENAME nvarchar(50) = '',
@NICKNAME nvarchar(50) = '',
@TITLECODEID uniqueidentifier = null,
@SUFFIXCODEID uniqueidentifier = null,
@REQUESTNOEMAIL bit = 0,
@CUSTOMIDENTIFIER nvarchar(36) = '',
@WEBADDRESS dbo.UDT_WEBADDRESS = '',
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@TITLE2CODEID uniqueidentifier = null,
@SUFFIX2CODEID uniqueidentifier = null,
@CONSTITUENCYCODEID uniqueidentifier = null,
@GENDERCODEID uniqueidentifier = null
) as
set nocount on;
if @ID is null
begin
raiserror('The constituent ID is required',16,1);
return -2;
end
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
--JamesWill 02/26/2008 CR294475-022508
if @NICKNAME is null
set @NICKNAME = '';
begin try
update dbo.CONSTITUENT set
[BIRTHDATE] = @BIRTHDATE,
[FIRSTNAME] = @FIRSTNAME,
[KEYNAME] = @KEYNAME,
[MAIDENNAME] = @MAIDENNAME,
[MARITALSTATUSCODEID] = @MARITALSTATUSCODEID,
[MIDDLENAME] = @MIDDLENAME,
-- TMV 10/11/2007 CR285339-101107 Non-members can submit profile update if they received an
-- email, only set the NETCOMMUNITYMEMBER flag during signups
-- [NETCOMMUNITYMEMBER] = 1,
[NICKNAME] = @NICKNAME,
[TITLECODEID] = @TITLECODEID,
[TITLE2CODEID] = @TITLE2CODEID,
[SUFFIXCODEID] = @SUFFIXCODEID,
[SUFFIX2CODEID] = @SUFFIX2CODEID,
-- SHL 3/2/2013 Bug 256850 Fix; Gender should now change to unknown when the user desires it
-- [GENDERCODE] =
-- case
-- when (@GENDERCODE is not null) and (@GENDERCODE > 0) then
-- @GENDERCODE --Only update the gender if it's not "Unknown"
-- else
-- [GENDERCODE]
-- end,
[GENDERCODE] = @GENDERCODE,
[WEBADDRESS] = @WEBADDRESS,
[CUSTOMIDENTIFIER] = @CUSTOMIDENTIFIER,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE,
[GENDERCODEID] = @GENDERCODEID
where
[ID] = @ID;
--Retrieve the ID of the solicit code being used to flag do not email.
declare @NOEMAILSOLICITCODEID uniqueidentifier
select top 1 @NOEMAILSOLICITCODEID=DONOTEMAILSOLICITCODEID
from NETCOMMUNITYDEFAULTCODEMAP
--Does the constituent already have that solicit code assigned?
declare @CONSTITUENTSOLICITCODEID uniqueidentifier
select @CONSTITUENTSOLICITCODEID=ID
from CONSTITUENTSOLICITCODE
where CONSTITUENTID=@ID and
SOLICITCODEID=@NOEMAILSOLICITCODEID
--If they request to not receive email...
if @REQUESTNOEMAIL = 1
begin
--And if they don't have the appropriate solicit code, add it.
if @CONSTITUENTSOLICITCODEID is null
begin
exec dbo.USP_DATAFORM_ADD_CONSTITUENTSOLICITCODE null, @NOEMAILSOLICITCODEID, @ID, null, null, '', @CHANGEAGENTID
end
--Otherwise, if they do have the solicit code and the dates on the code do not include today,
-- unbound its date range.
else
begin
update CONSTITUENTSOLICITCODE
set
STARTDATE=null,
ENDDATE=null,
CHANGEDBYID=@CHANGEAGENTID,
DATECHANGED=@CHANGEDATE
where
ID=@CONSTITUENTSOLICITCODEID and
(((not STARTDATE is null) and (datediff(day, STARTDATE, getdate())<0)) or
((not ENDDATE is null) and (datediff(day, getdate(), ENDDATE)<0)))
end
end
--If they request to receive email and have the solicit code, remove it.
if @REQUESTNOEMAIL = 0 and not @CONSTITUENTSOLICITCODEID is null
begin
exec dbo.USP_CONSTITUENTSOLICITCODE_DELETE @CONSTITUENTSOLICITCODEID, @CHANGEAGENTID
end
-- CONSTITUENCY UPDATE
declare @EARLIESTTIME as datetime =dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE)
if @CONSTITUENCYCODEID is not null
and not exists
(
select
1
from
dbo.CONSTITUENCY
where
[CONSTITUENTID] = @ID
and [CONSTITUENCYCODEID] = @CONSTITUENCYCODEID
and
(
([DATEFROM] is null and [DATETO] is null)
or ([DATEFROM] <= @EARLIESTTIME and [DATETO] is null)
or ([DATEFROM] is null and @EARLIESTTIME <= [DATETO])
or (@EARLIESTTIME between [DATEFROM] and [DATETO])
)
)
begin
declare @CONSTITUENCYDATETO datetime;
select top (1)
@CONSTITUENCYDATETO = dateadd(day, -1, [CONSTITUENCY].[DATEFROM])
from
dbo.CONSTITUENCY
where
[CONSTITUENCY].[CONSTITUENTID] = @ID
and [CONSTITUENCY].[CONSTITUENCYCODEID] = @CONSTITUENCYCODEID
and [CONSTITUENCY].[DATEFROM] > @EARLIESTTIME
order by
[DATETO];
insert into dbo.CONSTITUENCY
(
[CONSTITUENTID],
[CONSTITUENCYCODEID],
[DATEFROM],
[DATETO],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@CONSTITUENCYCODEID,
@EARLIESTTIME,
dbo.UFN_DATE_GETEARLIESTTIME(@CONSTITUENCYDATETO),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;