USP_WPAFFLUENCEINDICATOR_ADD
Adds WealthPoint Affluence Indicator information for a prospect.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @WEALTHID | uniqueidentifier | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @LSID | int | IN | |
| @REVISION | int | IN | |
| @ORIGINALID | int | IN | |
| @NEWROW | bit | IN | |
| @FULLHASH | nvarchar(32) | IN | |
| @PARTIALHASH | nvarchar(32) | IN | |
| @MC | nvarchar(3) | IN | |
| @PHONE | nvarchar(20) | IN | |
| @GENDERCODE | tinyint | IN | |
| @MULTIMILLN | nvarchar(25) | IN | |
| @WEALTHIND | nvarchar(1000) | IN | |
| @RPASSETS | money | IN | |
| @FULLNAME | nvarchar(100) | IN | |
| @CNOTES | nvarchar(1024) | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @RECURSIVEMATCH | bit | IN | |
| @ADDRESSTYPE | nvarchar(25) | IN | |
| @LINE1 | nvarchar(100) | IN | |
| @CITY | nvarchar(25) | IN | |
| @STATE | nvarchar(2) | IN | |
| @ZIP | nvarchar(5) | IN | |
| @ZIP4 | nvarchar(4) | IN | |
| @COUNTY | nvarchar(20) | IN | |
| @TITLE | nvarchar(50) | IN | |
| @COMPANY | nvarchar(50) | IN | |
| @WEBSITE | nvarchar(200) | IN | |
| @SALES | money | IN | |
| @EMPLOYMENT | int | IN | |
| @NAICS | nvarchar(100) | IN | |
| @AGE | nvarchar(7) | IN | |
| @DONOR | nvarchar(250) | IN | |
| @HOMEOWNER | nvarchar(25) | IN | |
| @MARITAL | nvarchar(25) | IN | |
| @DWELLING | nvarchar(35) | IN | |
| @LENGTHOFRESIDENCE | nvarchar(35) | IN | |
| @CHILDRENINDICATOR | nvarchar(200) | IN | |
| @INCOME | nvarchar(25) | IN | |
| @NEWPROSPECT | nvarchar(1) | IN | |
| @DONOTCALL | nvarchar(25) | IN | |
| @METROPOLITANSTATISTICALAREA | nvarchar(8) | IN | |
| @CONTRIBUTOR | nvarchar(35) | IN | |
| @POLITICALAFFILIATION | nvarchar(25) | IN | |
| @RELIGIOUSAFFILIATION | nvarchar(25) | IN | |
| @ETHNIC | nvarchar(50) | IN |
Definition
Copy
CREATE procedure dbo.USP_WPAFFLUENCEINDICATOR_ADD (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@WEALTHID uniqueidentifier,
@SOURCE nvarchar(100),
@LSID int = 0,
@REVISION int = 0,
@ORIGINALID int = 0,
@NEWROW bit = 0,
@FULLHASH nvarchar(32) = '',
@PARTIALHASH nvarchar(32) = '',
@MC nvarchar(3) = '',
@PHONE nvarchar(20) = '',
@GENDERCODE tinyint = 0,
@MULTIMILLN nvarchar(25) = '',
@WEALTHIND nvarchar(1000) = '',
@RPASSETS money = 0,
@FULLNAME nvarchar(100) = '',
@CNOTES nvarchar(1024) = '',
@CURRENTAPPUSERID uniqueidentifier = null,
@RECURSIVEMATCH bit = 0,
@ADDRESSTYPE nvarchar(25) = '',
@LINE1 nvarchar(100) = '',
@CITY nvarchar(25) = '',
@STATE nvarchar(2) = '',
@ZIP nvarchar(5) = '',
@ZIP4 nvarchar(4) = '',
@COUNTY nvarchar(20) = '',
@TITLE nvarchar(50) = '',
@COMPANY nvarchar(50) = '',
@WEBSITE nvarchar(200) = '',
@SALES money = 0,
@EMPLOYMENT int = 0,
@NAICS nvarchar(100) = '',
@AGE nvarchar(7) = '',
@DONOR nvarchar(250) = '',
@HOMEOWNER nvarchar(25) = '',
@MARITAL nvarchar(25) = '',
@DWELLING nvarchar(35) = '',
@LENGTHOFRESIDENCE nvarchar(35) = '',
@CHILDRENINDICATOR nvarchar(200) = '',
@INCOME nvarchar(25) = '',
@NEWPROSPECT nvarchar(1) = '',
@DONOTCALL nvarchar(25) = '',
@METROPOLITANSTATISTICALAREA nvarchar(8) = '',
@CONTRIBUTOR nvarchar(35) = '',
@POLITICALAFFILIATION nvarchar(25) = '',
@RELIGIOUSAFFILIATION nvarchar(25) = '',
@ETHNIC nvarchar(50) = ''
) as begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
/* With Larkspur update, old hashing is being deprecated, so we will delete
all previous records returned from wealth searches.
*/
declare @DELETEMAX datetime = '2016-09-29T00:00:00.000';
delete from dbo.WPAFFLUENCEINDICATOR
where WEALTHID=@WEALTHID and
SOURCE = N'Larkspur Data' and
DATEADDED < @DELETEMAX and
CONFIRMED = 0 and
MC <> '';
--Get any existing ID
select
@ID = ID
from
dbo.WPAFFLUENCEINDICATOR
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.WPAFFLUENCEINDICATOR where WEALTHID=@WEALTHID and FULLHASH=@FULLHASH)
begin
update
dbo.WPAFFLUENCEINDICATOR
set
FULLHASH = @FULLHASH,
MC = @MC,
RECURSIVEMATCH = @RECURSIVEMATCH,
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
if @ID is null
set @ID = newid();
insert into dbo.WPAFFLUENCEINDICATOR (
ID,
WEALTHID,
SOURCE,
LSID,
REVISION,
ORIGINALID,
NEWROW,
FULLHASH,
PARTIALHASH,
MC,
PHONE,
GENDERCODE,
MULTIMILLN,
WEALTHIND,
RPASSETS,
FULLNAME,
ADDRESSTYPE,
LINE1,
CITY,
STATE,
ZIP,
ZIP4,
COUNTY,
TITLE,
COMPANY,
WEBSITE,
SALES,
EMPLOYMENT,
NAICS,
AGE,
DONOR,
HOMEOWNER,
MARITAL,
DWELLING,
LENGTHOFRESIDENCE,
CHILDRENINDICATOR,
INCOME,
NEWPROSPECT,
DONOTCALL,
METROPOLITANSTATISTICALAREA,
CONTRIBUTOR,
POLITICALAFFILIATION,
RELIGIOUSAFFILIATION,
ETHNIC,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
RECURSIVEMATCH
) values (
@ID,
@WEALTHID,
coalesce(@SOURCE, ''),
coalesce(@LSID, 0),
coalesce(@REVISION, 0),
coalesce(@ORIGINALID, 0),
coalesce(@NEWROW, 0),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@MC, ''),
coalesce(@PHONE, ''),
coalesce(@GENDERCODE, 0),
coalesce(@MULTIMILLN, ''),
coalesce(@WEALTHIND, ''),
coalesce(@RPASSETS, 0),
coalesce(@FULLNAME, ''),
coalesce(@ADDRESSTYPE, ''),
coalesce(@LINE1, ''),
coalesce(@CITY, ''),
coalesce(@STATE, ''),
coalesce(@ZIP, ''),
coalesce(@ZIP4, ''),
coalesce(@COUNTY, ''),
coalesce(@TITLE, ''),
coalesce(@COMPANY, ''),
coalesce(@WEBSITE, ''),
coalesce(@SALES, 0),
coalesce(@EMPLOYMENT, 0),
coalesce(@NAICS, ''),
coalesce(@AGE, ''),
coalesce(@DONOR, ''),
coalesce(@HOMEOWNER, ''),
coalesce(@MARITAL, ''),
coalesce(@DWELLING, ''),
coalesce(@LENGTHOFRESIDENCE, ''),
coalesce(@CHILDRENINDICATOR, ''),
coalesce(@INCOME, ''),
coalesce(@NEWPROSPECT, ''),
coalesce(@DONOTCALL, ''),
coalesce(@METROPOLITANSTATISTICALAREA, ''),
coalesce(@CONTRIBUTOR, ''),
coalesce(@POLITICALAFFILIATION, ''),
coalesce(@RELIGIOUSAFFILIATION, ''),
coalesce(@ETHNIC, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@RECURSIVEMATCH
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;
end;