USP_DATAFORMTEMPLATE_ADD_BATCHPEOPLEFINDERBATCHCOMMIT
The save procedure used by the add dataform template "PeopleFinder Batch Row Commit Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @VALIDATEONLY | bit | IN | Validate only |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @PEOPLEFINDERID | uniqueidentifier | IN | PeopleFinder ID |
| @CONSTITUENTID | uniqueidentifier | IN | Constituent |
| @OLDADDRESSID | uniqueidentifier | IN | Old address |
| @NEWADDRESSBLOCK | nvarchar(150) | IN | New address |
| @NEWCITY | nvarchar(50) | IN | New city |
| @NEWPOSTCODE | nvarchar(12) | IN | New zip |
| @OTHERLASTNAME | nvarchar(100) | IN | Other last name |
| @NEWPHONENUMBER | nvarchar(100) | IN | New phone number |
| @NEWADDRESSINFOSOURCE | nvarchar(20) | IN | New address info source |
| @DECEASEDYEAR | UDT_FUZZYDATE | IN | Year deceased |
| @CONFIDENCELEVEL | smallint | IN | Confidence level |
| @SENDMAIL | bit | IN | Send mail |
| @NEWSTATEID | uniqueidentifier | IN | New state |
| @ISDECEASED | bit | IN | Deceased |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHPEOPLEFINDERBATCHCOMMIT
(
@ID uniqueidentifier = null output,
@VALIDATEONLY bit = 0,
@CHANGEAGENTID uniqueidentifier,
@PEOPLEFINDERID uniqueidentifier,
@CONSTITUENTID uniqueidentifier = null,
@OLDADDRESSID uniqueidentifier = null,
@NEWADDRESSBLOCK nvarchar(150) = null,
@NEWCITY nvarchar(50) = null,
@NEWPOSTCODE nvarchar(12) = null,
@OTHERLASTNAME nvarchar(100) = null,
@NEWPHONENUMBER nvarchar(100) = null,
@NEWADDRESSINFOSOURCE nvarchar(20) = null,
@DECEASEDYEAR dbo.UDT_FUZZYDATE = null,
@CONFIDENCELEVEL smallint = null,
@SENDMAIL bit = null,
@NEWSTATEID uniqueidentifier = null,
@ISDECEASED bit = null
)
as
set nocount on;
declare @CURRENTDATE datetime;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();
update PEOPLEFINDER
set
STEPCODE = 3
where
ID = @PEOPLEFINDERID -- Set the step code to committed
begin try
declare @INFOSOURCECODEID uniqueidentifier;
declare @MINIMUMRANKCODE tinyint;
declare @MARKASDECEASED bit;
declare @UPDATELASTNAMECODE tinyint;
declare @UPDATEPHONENUMBER bit;
declare @PHONETYPECODEID uniqueidentifier;
declare @ADDFORMERLASTNAMEASALIAS bit;
declare @PEOPLEFINDERDESCRIPTION nvarchar(150);
declare @MINIMUMRANKVALUE int;
declare @DECEASEDSOURCECODEID uniqueidentifier;
declare @CONSTITUENTNOTEID uniqueidentifier;
declare @TEXTNOTE nvarchar(max);
declare @NOTETYPECODEID uniqueidentifier;
declare @NEWPHONEID uniqueidentifier;
declare @OLDADDRESSISPRIMARY bit;
declare @OLDADDRESSTYPECODE uniqueidentifier;
declare @OLDADDRESSBLOCK nvarchar(150);
declare @ADDRESSID uniqueidentifier;
declare @COUNTRYID uniqueidentifier;
declare @OLDADDRESSTYPECODEID uniqueidentifier;
declare @NEWADDRESSINFOSOURCECODEID uniqueidentifier;
declare @INFOSOURCECOMMENTS nvarchar(256);
declare @ALIASTYPECODEID uniqueidentifier;
declare @INDIVIDUALALIASID uniqueidentifier;
declare @KEYNAME nvarchar(100);
declare @FIRSTNAME nvarchar(50);
declare @MIDDLENAME nvarchar(50);
declare @TITLECODEID uniqueidentifier;
declare @TITLE2CODEID uniqueidentifier;
declare @SUFFIXCODEID uniqueidentifier;
declare @SUFFIX2CODEID uniqueidentifier;
declare @PRIMARYADDRESSCOUNT int;
declare @MARKPHONEASPRIMARY bit;
declare @PHONESCOUNT int = 0;
declare @PHONEISPRIMARY bit = 0;
declare @UPDATEADDRESS bit;
declare @NEWADDRESSTYPECODEID uniqueidentifier;
declare @MARKADDRESSASPRIMARY bit;
select @INFOSOURCECODEID = PEOPLEFINDER.INFOSOURCECODEID,
@MINIMUMRANKCODE = PEOPLEFINDER.MINIMUMRANKCODE,
@MARKASDECEASED = PEOPLEFINDER.MARKASDECEASED,
@UPDATELASTNAMECODE = PEOPLEFINDER.UPDATELASTNAMECODE,
@UPDATEPHONENUMBER = PEOPLEFINDER.UPDATEPHONENUMBER,
@PHONETYPECODEID = PEOPLEFINDER.PHONETYPECODEID,
@ADDFORMERLASTNAMEASALIAS = PEOPLEFINDER.ADDFORMERLASTNAMEASALIAS,
@PEOPLEFINDERDESCRIPTION = PEOPLEFINDER.DESCRIPTION,
@MARKPHONEASPRIMARY = PEOPLEFINDER.MARKPHONEASPRIMARY,
@UPDATEADDRESS = PEOPLEFINDER.UPDATEADDRESS,
@OLDADDRESSTYPECODEID = PEOPLEFINDER.OLDADDRESSTYPECODEID,
@NEWADDRESSTYPECODEID = PEOPLEFINDER.NEWADDRESSTYPECODEID,
@NEWADDRESSINFOSOURCECODEID = PEOPLEFINDER.NEWADDRESSINFOSOURCECODEID,
@MARKADDRESSASPRIMARY = PEOPLEFINDER.MARKADDRESSASPRIMARY
from
dbo.PEOPLEFINDER
where
PEOPLEFINDER.ID = @PEOPLEFINDERID;
if @PEOPLEFINDERDESCRIPTION is null
begin
raiserror('BBERR_PEOPLEFINDERIDREQUIRED', 13, 1)
return
end
--Set the minimum rank value to process
if @MINIMUMRANKCODE = 0 set @MINIMUMRANKVALUE = 530
else if @MINIMUMRANKCODE = 1 set @MINIMUMRANKVALUE = 454
else if @MINIMUMRANKCODE = 2 set @MINIMUMRANKVALUE = 335
else if @MINIMUMRANKCODE = 3 set @MINIMUMRANKVALUE = 0;
if @OLDADDRESSID is null
begin
set @ID = newid()
end
else
begin
set @ID = @OLDADDRESSID
end
if @CONFIDENCELEVEL > @MINIMUMRANKVALUE --Ensure confidence level meets minimum
begin
--Update deceased data
if @ISDECEASED = 1 --Constituent is deceased
begin
if @MARKASDECEASED = 1 --User wants to update deceased data
begin
select @DECEASEDSOURCECODEID = dbo.UFN_DECEASEDSOURCECODE_GETID('PeopleFinder')
select @NOTETYPECODEID = dbo.UFN_CONSTITUENTNOTETYPECODE_GETID('PeopleFinder')
if @DECEASEDSOURCECODEID is null --Check/add deceased source code
begin
exec dbo.USP_DECEASEDSOURCECODE_CREATEENTRY
'PeopleFinder',
1,
null,
@CHANGEAGENTID,
@DECEASEDSOURCECODEID output;
end
if @NOTETYPECODEID is null --Check/add note type code
begin
exec dbo.USP_CONSTITUENTNOTETYPECODE_CREATEENTRY
'PeopleFinder',
1,
null,
@CHANGEAGENTID,
@NOTETYPECODEID output;
end
set @TEXTNOTE = 'This constituent was marked deceased by PeopleFinder'
if len(@DECEASEDYEAR) <> 8
begin
set @DECEASEDYEAR = LEFT(@DECEASEDYEAR, 4) + '0000'
end
exec dbo.USP_DATAFORMTEMPLATE_EDIT_MARKINDIVIDUALDECEASED_2
@CONSTITUENTID,
@CHANGEAGENTID,
@DECEASEDYEAR,
0,
@DECEASEDSOURCECODEID
exec dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENTNOTE
@CONSTITUENTNOTEID output,
@CHANGEAGENTID,
@CURRENTDATE,
'',
null,
@TEXTNOTE,
@NOTETYPECODEID,
@CONSTITUENTID,
''
end
end
else -- Constituent is not deceased
begin
--Update last name
if @UPDATELASTNAMECODE = 0 --User wants to add new last name as alias
begin
if LEN(@OTHERLASTNAME) > 0 --Other last name returned from DES
begin
select @NOTETYPECODEID = dbo.UFN_CONSTITUENTNOTETYPECODE_GETID('PeopleFinder')
if @NOTETYPECODEID is null --Check/add note type code
begin
exec dbo.USP_CONSTITUENTNOTETYPECODE_CREATEENTRY
'PeopleFinder',
1,
null,
@CHANGEAGENTID,
@NOTETYPECODEID output;
end
set @TEXTNOTE = 'This constituent had an alias added by PeopleFinder';
exec dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENTNOTE
@CONSTITUENTNOTEID output,
@CHANGEAGENTID,
@CURRENTDATE,
'',
null,
@TEXTNOTE,
@NOTETYPECODEID,
@CONSTITUENTID,
''
select @ALIASTYPECODEID = dbo.UFN_ALIASTYPECODE_GETID('PeopleFinder')
if @ALIASTYPECODEID is null --Check/add alias type code
begin
exec dbo.USP_ALIASTYPECODE_CREATEENTRY
'PeopleFinder',
1,
null,
@CHANGEAGENTID,
@ALIASTYPECODEID output;
end
select
@FIRSTNAME = FIRSTNAME,
@MIDDLENAME = MIDDLENAME,
@TITLECODEID = TITLECODEID,
@TITLE2CODEID = TITLE2CODEID,
@SUFFIXCODEID = SUFFIXCODEID,
@SUFFIX2CODEID = SUFFIX2CODEID
from
CONSTITUENT
where
ID = @CONSTITUENTID
exec dbo.USP_DATAFORMTEMPLATE_ADD_INDIVIDUALALIAS
@INDIVIDUALALIASID output,
@CHANGEAGENTID,
@CONSTITUENTID,
@ALIASTYPECODEID,
@OTHERLASTNAME,
@FIRSTNAME,
@MIDDLENAME,
@TITLECODEID,
@TITLE2CODEID,
@SUFFIXCODEID,
@SUFFIX2CODEID
end
end
else if @UPDATELASTNAMECODE = 1 --User wants to change current last name
begin
if LEN(@OTHERLASTNAME) > 0 --Other last name returned from DES
begin
select @NOTETYPECODEID = dbo.UFN_CONSTITUENTNOTETYPECODE_GETID('PeopleFinder')
if @NOTETYPECODEID is null --Check/add note type code
begin
exec dbo.USP_CONSTITUENTNOTETYPECODE_CREATEENTRY
'PeopleFinder',
1,
null,
@CHANGEAGENTID,
@NOTETYPECODEID output;
end
set @TEXTNOTE = 'This constituent''s last name was updated by PeopleFinder';
exec dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENTNOTE
@CONSTITUENTNOTEID output,
@CHANGEAGENTID,
@CURRENTDATE,
'',
null,
@TEXTNOTE,
@NOTETYPECODEID,
@CONSTITUENTID,
''
if @ADDFORMERLASTNAMEASALIAS = 1 --User wants to add the former last name as an alias
begin
select @ALIASTYPECODEID = dbo.UFN_ALIASTYPECODE_GETID('PeopleFinder')
if @ALIASTYPECODEID is null --Check/add alias type code
begin
exec dbo.USP_ALIASTYPECODE_CREATEENTRY
'PeopleFinder',
1,
null,
@CHANGEAGENTID,
@ALIASTYPECODEID output;
end
select
@FIRSTNAME = FIRSTNAME,
@KEYNAME = KEYNAME,
@MIDDLENAME = MIDDLENAME,
@TITLECODEID = TITLECODEID,
@TITLE2CODEID = TITLE2CODEID,
@SUFFIXCODEID = SUFFIXCODEID,
@SUFFIX2CODEID = SUFFIX2CODEID
from
CONSTITUENT
where
ID = @CONSTITUENTID
exec dbo.USP_DATAFORMTEMPLATE_ADD_INDIVIDUALALIAS
@INDIVIDUALALIASID output,
@CHANGEAGENTID,
@CONSTITUENTID,
@ALIASTYPECODEID,
@KEYNAME,
@FIRSTNAME,
@MIDDLENAME,
@TITLECODEID,
@TITLE2CODEID,
@SUFFIXCODEID,
@SUFFIX2CODEID
end
update CONSTITUENT
set
KEYNAME = @OTHERLASTNAME
where
ID = @CONSTITUENTID
end
end
--Add new phone number
if @UPDATEPHONENUMBER = 1 --User wants to add phone number from DES
begin
if LEN(@NEWPHONENUMBER) > 0
begin
select @NEWPHONEID = [ID]
from dbo.[PHONE]
where (
[NUMBERNOFORMAT] = @NEWPHONENUMBER
or [NUMBER] = @NEWPHONENUMBER
)
and [CONSTITUENTID] = @CONSTITUENTID;
select @PHONESCOUNT = count(1)
from dbo.[PHONE]
where [CONSTITUENTID] = @CONSTITUENTID;
if @MARKPHONEASPRIMARY = 1 or @PHONESCOUNT = 0
select @PHONEISPRIMARY = 1
if @NEWPHONEID is null -- Phone number does not already exist
begin
if @MARKPHONEASPRIMARY = 1
begin
update dbo.[PHONE]
set
[ISPRIMARY] = 0,
[DATECHANGED] = @CURRENTDATE,
[CHANGEDBYID] = @CHANGEAGENTID
where CONSTITUENTID = @CONSTITUENTID;
end
set @INFOSOURCECOMMENTS = 'Phone number added by PeopleFinder'
exec dbo.USP_DATAFORMTEMPLATE_ADD_PHONE
@NEWPHONEID output,
@CHANGEAGENTID,
@CONSTITUENTID,
@PHONETYPECODEID,
@NEWPHONENUMBER,
@PHONEISPRIMARY,
0,
0,
0,
'',
'',
@INFOSOURCECODEID,
@INFOSOURCECOMMENTS,
null,
null,
null,
0;
end
end
end
--Add new address
select
@ADDRESSID = ID
from
ADDRESS
where
ADDRESSBLOCK = @NEWADDRESSBLOCK
and CONSTITUENTID = @CONSTITUENTID;
if @ADDRESSID is null and @UPDATEADDRESS = 1 --Address being added does not exist
begin
select @COUNTRYID = dbo.UFN_STATE_GETCOUNTRY(@NEWSTATEID, null)
if @COUNTRYID is null
begin
raiserror('BBERR_COUNTRYREQUIRED', 13, 1)
return
end
if @OLDADDRESSID is not null and @OLDADDRESSTYPECODEID is not null
begin
update dbo.ADDRESS
set
ADDRESSTYPECODEID = @OLDADDRESSTYPECODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @OLDADDRESSID;
end
set @PRIMARYADDRESSCOUNT = (select count(1) from dbo.ADDRESS where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1);
declare @NEWADDRESSISPRIMARY bit = 0;
if @PRIMARYADDRESSCOUNT = 0 or @MARKADDRESSASPRIMARY = 1
begin
update dbo.ADDRESS
set
ISPRIMARY = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTID = @CONSTITUENTID
and ISPRIMARY = 1;
set @NEWADDRESSISPRIMARY = 1;
end
set @ID = null;
exec dbo.USP_DATAFORMTEMPLATE_ADD_ADDRESS_2
@ID output,
@CHANGEAGENTID,
@CONSTITUENTID,
@NEWADDRESSTYPECODEID,
@NEWADDRESSISPRIMARY,
0,
'0000',
'0000',
@COUNTRYID,
@NEWSTATEID,
@NEWADDRESSBLOCK,
@NEWCITY,
@NEWPOSTCODE,
null,
0,
@OLDADDRESSID,
'',
'',
'',
0,
null,
0,
null,
null,
null,
null,
null,
@NEWADDRESSINFOSOURCECODEID,
null,
null,
'',
0,
null,
'',
0
end
else if @ADDRESSID is null
begin
set @ID = '00000000-0000-0000-0000-000000000000';
end
else
begin
set @ID = @ADDRESSID
end
end
end
end try
begin catch
exec.dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;