USP_WPBIOGRAPHICAL_ADD
Adds WealthPoint Biographical information for a prospect.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @WEALTHID | uniqueidentifier | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @WWID | int | IN | |
| @REVISION | int | IN | |
| @ORIGINALID | int | IN | |
| @NEWROW | bit | IN | |
| @FULLHASH | nvarchar(32) | IN | |
| @PARTIALHASH | nvarchar(32) | IN | |
| @MC | nvarchar(3) | IN | |
| @FULLNAME | nvarchar(100) | IN | |
| @LINE1 | nvarchar(4000) | IN | |
| @CITY | nvarchar(4000) | IN | |
| @STATE | nvarchar(4000) | IN | |
| @ZIP | nvarchar(50) | IN | |
| @A2LINE1 | nvarchar(4000) | IN | |
| @A2CITY | nvarchar(4000) | IN | |
| @A2STATE | nvarchar(4000) | IN | |
| @A2ZIP | nvarchar(50) | IN | |
| @GENDERCODE | tinyint | IN | |
| @BIRTHDATE | datetime | IN | |
| @BIRTHPLACE | nvarchar(120) | IN | |
| @DECEASEDDATE | UDT_FUZZYDATE | IN | |
| @OCCUPATION | nvarchar(4000) | IN | |
| @EDUCATION | nvarchar(max) | IN | |
| @FAMILY | nvarchar(max) | IN | |
| @POSITIONSHELD | nvarchar(max) | IN | |
| @CERTIFICATIONS | nvarchar(max) | IN | |
| @CAREER | nvarchar(max) | IN | |
| @AWARDS | nvarchar(max) | IN | |
| @MEMBERSHIPS | nvarchar(max) | IN | |
| @RESEARCHINTERESTS | nvarchar(max) | IN | |
| @POLITICALRELIGIOUSAFFILIATIONS | nvarchar(max) | IN | |
| @CIVICMILITARYSERVICE | nvarchar(max) | IN | |
| @THOUGHTSONLIFE | nvarchar(max) | IN | |
| @LAW | nvarchar(max) | IN | |
| @PERSONAL | nvarchar(max) | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @RECURSIVEMATCH | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_WPBIOGRAPHICAL_ADD (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@WEALTHID uniqueidentifier,
@SOURCE nvarchar(100),
@WWID int = 0,
@REVISION int = 0,
@ORIGINALID int = 0,
@NEWROW bit = 0,
@FULLHASH nvarchar(32) = '',
@PARTIALHASH nvarchar(32) = '',
@MC nvarchar(3) = '',
@FULLNAME nvarchar(100) = '',
@LINE1 nvarchar(4000) = '',
@CITY nvarchar(4000) = '',
@STATE nvarchar(4000) = '',
@ZIP nvarchar(50) = '',
@A2LINE1 nvarchar(4000) = '',
@A2CITY nvarchar(4000) = '',
@A2STATE nvarchar(4000) = '',
@A2ZIP nvarchar(50) = '',
@GENDERCODE tinyint = 0,
@BIRTHDATE datetime = null,
@BIRTHPLACE nvarchar(120) = '',
@DECEASEDDATE dbo.UDT_FUZZYDATE = '00000000',
@OCCUPATION nvarchar(4000) = '',
@EDUCATION nvarchar(max) = '',
@FAMILY nvarchar(max) = '',
@POSITIONSHELD nvarchar(max) = '',
@CERTIFICATIONS nvarchar(max) = '',
@CAREER nvarchar(max) = '',
@AWARDS nvarchar(max) = '',
@MEMBERSHIPS nvarchar(max) = '',
@RESEARCHINTERESTS nvarchar(max) = '',
@POLITICALRELIGIOUSAFFILIATIONS nvarchar(max) = '',
@CIVICMILITARYSERVICE nvarchar(max) = '',
@THOUGHTSONLIFE nvarchar(max) = '',
@LAW nvarchar(max) = '',
@PERSONAL nvarchar(max) = '',
@CURRENTAPPUSERID uniqueidentifier = null,
@RECURSIVEMATCH bit = 0
) as begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
declare @NEWRECORD bit;
declare @UPDATEDRECORD bit;
declare @CURRENTDATE datetime;
set @NEWRECORD = 0;
set @UPDATEDRECORD = 0;
set @CURRENTDATE = getdate();
--Get any existing ID
select
@ID = ID
from
dbo.WPBIOGRAPHICAL
where
WEALTHID=@WEALTHID and PARTIALHASH=@PARTIALHASH;
declare @CONFIRMED bit;
declare @CONFIRMEDBYAPPUSERID uniqueidentifier;
declare @DATECONFIRMED datetime;
declare @REJECTED bit;
declare @REJECTEDBYAPPUSERID uniqueidentifier;
declare @DATEREJECTED datetime;
set @CONFIRMED = 0;
set @REJECTED = 0;
if @CURRENTAPPUSERID is not null
begin
declare @CONFIDENCE int
select
@CONFIDENCE = coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE)
from
dbo.MATCHCODE MC
left outer join
dbo.WEALTHSOURCE WS on @SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and CR.WEALTHSOURCEID = WS.ID
where
MC.MATCHCODE = @MC
select
@REJECTED = case @CONFIDENCE
when 5 then 0
when 0 then 1
end,
@DATEREJECTED = case @CONFIDENCE
when 5 then NULL
when 0 then @CURRENTDATE
end,
@REJECTEDBYAPPUSERID = case @CONFIDENCE
when 5 then NULL
when 0 then @CURRENTAPPUSERID
end,
@CONFIRMED = case @CONFIDENCE
when 5 then 1
when 0 then 0
end,
@DATECONFIRMED = case @CONFIDENCE
when 5 then @CURRENTDATE
when 0 then NULL
end,
@CONFIRMEDBYAPPUSERID = case @CONFIDENCE
when 5 then @CURRENTAPPUSERID
when 0 then NULL
end
where
@CONFIDENCE in (0,5);
end
-- Record exists
if @ID is not null
begin
if not exists(select 1 from dbo.WPBIOGRAPHICAL where WEALTHID=@WEALTHID and FULLHASH=@FULLHASH)
begin
--Record exists but needs to be updated
set @UPDATEDRECORD = 1
update
dbo.WPBIOGRAPHICAL
set
MC = coalesce(nullif(@MC, ''), MC),
RECURSIVEMATCH = @RECURSIVEMATCH,
FULLNAME = coalesce(nullif(@FULLNAME, ''), FULLNAME),
LINE1 = coalesce(nullif(@LINE1, ''), LINE1),
CITY = coalesce(nullif(@CITY, ''), CITY),
STATE = coalesce(nullif(@STATE, ''), STATE),
ZIP = coalesce(nullif(@ZIP, ''), ZIP),
A2LINE1 = coalesce(nullif(@A2LINE1, ''), A2LINE1),
A2CITY = coalesce(nullif(@A2CITY, ''), A2CITY),
A2STATE = coalesce(nullif(@A2STATE, ''), A2STATE),
A2ZIP = coalesce(nullif(@A2ZIP, ''), A2ZIP),
GENDERCODE = coalesce(nullif(@GENDERCODE, 0), GENDERCODE),
BIRTHDATE = coalesce(@BIRTHDATE, BIRTHDATE),
BIRTHPLACE = coalesce(nullif(@BIRTHPLACE, ''), BIRTHPLACE),
DECEASEDDATE = coalesce(nullif(@DECEASEDDATE, '00000000'), DECEASEDDATE),
OCCUPATION = coalesce(nullif(@OCCUPATION, ''), OCCUPATION),
EDUCATION = coalesce(nullif(@EDUCATION, ''), EDUCATION),
FAMILY = coalesce(nullif(@FAMILY, ''), FAMILY),
POSITIONSHELD = coalesce(nullif(@POSITIONSHELD, ''), POSITIONSHELD),
CERTIFICATIONS = coalesce(nullif(@CERTIFICATIONS, ''), CERTIFICATIONS),
CAREER = coalesce(nullif(@CAREER, ''), CAREER),
AWARDS = coalesce(nullif(@AWARDS, ''), AWARDS),
MEMBERSHIPS = coalesce(nullif(@MEMBERSHIPS, ''), MEMBERSHIPS),
RESEARCHINTERESTS = coalesce(nullif(@RESEARCHINTERESTS, ''), RESEARCHINTERESTS),
POLITICALRELIGIOUSAFFILIATIONS = coalesce(nullif(@POLITICALRELIGIOUSAFFILIATIONS, ''), POLITICALRELIGIOUSAFFILIATIONS),
CIVICMILITARYSERVICE = coalesce(nullif(@CIVICMILITARYSERVICE, ''), CIVICMILITARYSERVICE),
THOUGHTSONLIFE = coalesce(nullif(@THOUGHTSONLIFE, ''), THOUGHTSONLIFE),
LAW = coalesce(nullif(@LAW, ''), LAW),
PERSONAL = coalesce(nullif(@PERSONAL, ''), PERSONAL),
FULLHASH = @FULLHASH,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
CONFIRMED = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then 1 else CONFIRMED end,
CONFIRMEDBYAPPUSERID = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then @CONFIRMEDBYAPPUSERID else CONFIRMEDBYAPPUSERID end,
DATECONFIRMED = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then @DATECONFIRMED else DATECONFIRMED end
where
WEALTHID=@WEALTHID and PARTIALHASH=@PARTIALHASH;
end
end
else
begin
--Record is new and needs to be Added
set @NEWRECORD = 1
if @ID is null
set @ID = newid();
insert into dbo.WPBIOGRAPHICAL (
ID,
WEALTHID,
SOURCE,
WWID,
REVISION,
ORIGINALID,
NEWROW,
FULLHASH,
PARTIALHASH,
MC,
FULLNAME,
LINE1,
CITY,
STATE,
ZIP,
A2LINE1,
A2CITY,
A2STATE,
A2ZIP,
GENDERCODE,
BIRTHDATE,
BIRTHPLACE,
DECEASEDDATE,
OCCUPATION,
EDUCATION,
FAMILY,
POSITIONSHELD,
CERTIFICATIONS,
CAREER,
AWARDS,
MEMBERSHIPS,
RESEARCHINTERESTS,
POLITICALRELIGIOUSAFFILIATIONS,
CIVICMILITARYSERVICE,
THOUGHTSONLIFE,
LAW,
PERSONAL,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
RECURSIVEMATCH
) values (
@ID,
@WEALTHID,
coalesce(@SOURCE, ''),
coalesce(@WWID, 0),
coalesce(@REVISION, 0),
coalesce(@ORIGINALID, 0),
coalesce(@NEWROW, 0),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@MC, ''),
coalesce(replace(@FULLNAME, char(182), char(10)), ''),
coalesce(replace(@LINE1, char(182), char(10)), ''),
coalesce(replace(@CITY, char(182), char(10)), ''),
coalesce(replace(@STATE, char(182), char(10)), ''),
coalesce(@ZIP, ''),
coalesce(replace(@A2LINE1, char(182), char(10)), ''),
coalesce(replace(@A2CITY, char(182), char(10)), ''),
coalesce(replace(@A2STATE, char(182), char(10)), ''),
coalesce(@A2ZIP, ''),
coalesce(@GENDERCODE, 0),
@BIRTHDATE,
coalesce(replace(@BIRTHPLACE, char(182), char(10)), ''),
coalesce(@DECEASEDDATE, '00000000'),
coalesce(replace(@OCCUPATION, char(182), char(10)), ''),
coalesce(replace(@EDUCATION, char(182), char(10)), ''),
coalesce(replace(@FAMILY, char(182), char(10)), ''),
coalesce(replace(@POSITIONSHELD, char(182), char(10)), ''),
coalesce(replace(@CERTIFICATIONS, char(182), char(10)), ''),
coalesce(replace(@CAREER, char(182), char(10)), ''),
coalesce(replace(@AWARDS, char(182), char(10)), ''),
coalesce(replace(@MEMBERSHIPS, char(182), char(10)), ''),
coalesce(replace(@RESEARCHINTERESTS, char(182), char(10)), ''),
coalesce(replace(@POLITICALRELIGIOUSAFFILIATIONS, char(182), char(10)), ''),
coalesce(replace(@CIVICMILITARYSERVICE, char(182), char(10)), ''),
coalesce(replace(@THOUGHTSONLIFE, char(182), char(10)), ''),
coalesce(replace(@LAW, char(182), char(10)), ''),
coalesce(replace(@PERSONAL, char(182), char(10)), ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@RECURSIVEMATCH
);
end
if @NEWRECORD = 1 or @UPDATEDRECORD = 1
begin
--Log record into the history table
insert into dbo.WPBIOGRAPHICALHISTORY (
WPBIOGRAPHICALID,
SOURCE,
FULLHASH,
PARTIALHASH,
FULLNAME,
LINE1,
CITY,
STATE,
ZIP,
A2LINE1,
A2CITY,
A2STATE,
A2ZIP,
GENDERCODE,
BIRTHDATE,
BIRTHPLACE,
DECEASEDDATE,
OCCUPATION,
EDUCATION,
FAMILY,
POSITIONSHELD,
CERTIFICATIONS,
CAREER,
AWARDS,
MEMBERSHIPS,
RESEARCHINTERESTS,
POLITICALRELIGIOUSAFFILIATIONS,
CIVICMILITARYSERVICE,
THOUGHTSONLIFE,
LAW,
PERSONAL,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED
) values (
@ID,
coalesce(@SOURCE, ''),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(replace(@FULLNAME, char(182), char(10)), ''),
coalesce(replace(@LINE1, char(182), char(10)), ''),
coalesce(replace(@CITY, char(182), char(10)), ''),
coalesce(replace(@STATE, char(182), char(10)), ''),
coalesce(@ZIP, ''),
coalesce(replace(@A2LINE1, char(182), char(10)), ''),
coalesce(replace(@A2CITY, char(182), char(10)), ''),
coalesce(replace(@A2STATE, char(182), char(10)), ''),
coalesce(@A2ZIP, ''),
coalesce(@GENDERCODE, 0),
@BIRTHDATE,
coalesce(replace(@BIRTHPLACE, char(182), char(10)), ''),
coalesce(@DECEASEDDATE, '00000000'),
coalesce(replace(@OCCUPATION, char(182), char(10)), ''),
coalesce(replace(@EDUCATION, char(182), char(10)), ''),
coalesce(replace(@FAMILY, char(182), char(10)), ''),
coalesce(replace(@POSITIONSHELD, char(182), char(10)), ''),
coalesce(replace(@CERTIFICATIONS, char(182), char(10)), ''),
coalesce(replace(@CAREER, char(182), char(10)), ''),
coalesce(replace(@AWARDS, char(182), char(10)), ''),
coalesce(replace(@MEMBERSHIPS, char(182), char(10)), ''),
coalesce(replace(@RESEARCHINTERESTS, char(182), char(10)), ''),
coalesce(replace(@POLITICALRELIGIOUSAFFILIATIONS, char(182), char(10)), ''),
coalesce(replace(@CIVICMILITARYSERVICE, char(182), char(10)), ''),
coalesce(replace(@THOUGHTSONLIFE, char(182), char(10)), ''),
coalesce(replace(@LAW, char(182), char(10)), ''),
coalesce(replace(@PERSONAL, char(182), char(10)), ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;
end;