USP_DATALIST_WPBIOGRAPHICAL2
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @WEALTHID | uniqueidentifier | IN | |
| @MATCH | nvarchar(100) | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @CONFIDENCE | int | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_WPBIOGRAPHICAL2
(
@WEALTHID uniqueidentifier = null,
@MATCH nvarchar(100) = null,
@SOURCE nvarchar(100) = null,
@CONFIDENCE int = 0
)
as
begin
declare @CHANGEAGENTID uniqueidentifier;
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
update
dbo.WPBIOGRAPHICAL
set
VIEWED = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
VIEWED = 0 and
WEALTHID = @WEALTHID;
update
dbo.WPBIOGRAPHICALDEMOGRAPHIC
set
VIEWED = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
VIEWED = 0 and
WEALTHID = @WEALTHID;
with RECORDCONFIDENCE as(
select
WP.ID,
case
when WP.CONFIRMED = 1 then '5'
when WP.REJECTED = 1 then '0'
else
isnull(coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
end as CONFIDENCE
from
dbo.WPBIOGRAPHICAL WP
left outer join
dbo.MATCHCODE MC on WP.MC = MC.MATCHCODE
left outer join
dbo.WEALTHSOURCE WS on WP.SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and WS.ID = CR.WEALTHSOURCEID
union
select
WP.ID,
case
when WP.CONFIRMED = 1 then '5'
when WP.REJECTED = 1 then '0'
else
isnull(coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
end as CONFIDENCE
from
dbo.WPBIOGRAPHICALDEMOGRAPHIC WP
left outer join
dbo.MATCHCODE MC on WP.MC = MC.MATCHCODE
left outer join
dbo.WEALTHSOURCE WS on WP.SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and WS.ID = CR.WEALTHSOURCEID
)
select
--Begin Generic Fields
WP.ID,
case
when WP.CONFIRMED = 1 and WP.RECURSIVEMATCH = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_star_check_16.png'
when WP.CONFIRMED = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_check_16.png'
when WP.REJECTED = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
else
case WP.RECURSIVEMATCH
when '1' then
case RC.CONFIDENCE
when '0' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
when '1' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_1_star_16.png'
when '2' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_2_star_16.png'
when '3' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_3_star_16.png'
when '4' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_4_star_16.png'
when '5' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_star_16.png'
end
when '0' then
case RC.CONFIDENCE
when '0' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
when '1' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_1_16.png'
when '2' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_2_16.png'
when '3' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_3_16.png'
when '4' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_4_16.png'
when '5' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_16.png'
end
end
end as STATUSICON,
RC.CONFIDENCE,
WP.DATECONFIRMED,
coalesce(C.NAME,U.USERNAME),
WP.SOURCE,
--End Generic Fields
WP.FULLNAME,
WP.BIRTHDATE,
WP.BIRTHPLACE,
WP.MC,
dbo.UFN_WPMATCHCODE_DESCRIPTION(WP.MC),
WP.CONFIRMED,
WP.REJECTED,
'4898198b-8db5-4381-bb4c-770ea97195c9' as VIEWFORMID,
case
when WP.BIRTHDATE is null then '00000000'
else right('0000' + cast(DATEPART(yyyy, WP.BIRTHDATE) as nvarchar(4)), 4) +
right('00' + cast(DATEPART(mm, WP.BIRTHDATE) as nvarchar(2)), 2) +
right('00' + cast(DATEPART(dd, WP.BIRTHDATE) as nvarchar(2)), 2)
end as BIRTHDATEFUZZY,
0 as ISDEMOGRAPHIC,
cast(WP.ID as varchar(36)) + '|' + '0'
from
dbo.WPBIOGRAPHICAL WP
left outer join
dbo.APPUSER U on U.ID=WP.CONFIRMEDBYAPPUSERID
left outer join
dbo.CONSTITUENT C on C.ID=U.CONSTITUENTID
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
where
WP.WEALTHID = @WEALTHID and
(nullif(@SOURCE, '') is null or WP.SOURCE = @SOURCE) and
(@MATCH is null or WP.MC like @MATCH + '%') and
(((0 <= @CONFIDENCE and @CONFIDENCE <=5) and RC.CONFIDENCE >= @CONFIDENCE) or
((@CONFIDENCE = 99) and WP.CONFIRMED = 1))
union
select
--Begin Generic Fields
WP.ID,
case
when WP.CONFIRMED = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_check_16.png'
when WP.REJECTED = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
else
case RC.CONFIDENCE
when '0' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
when '1' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_1_16.png'
when '2' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_2_16.png'
when '3' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_3_16.png'
when '4' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_4_16.png'
when '5' then
'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_16.png'
end
end as STATUSICON,
RC.CONFIDENCE,
WP.DATECONFIRMED,
coalesce(C.NAME,U.USERNAME),
WP.SOURCE,
--End Generic Fields
WP.FULLNAME,
null as BIRTHDATE,
null as BIRTHPLACE,
WP.MC,
dbo.UFN_WPMATCHCODE_DESCRIPTION(WP.MC),
WP.CONFIRMED,
WP.REJECTED,
'f7960777-d442-4308-9c98-61dfd15bada0' as VIEWFORMID,
WP.BIRTHDATE,
1 as ISDEMOGRAPHIC,
cast(WP.ID as varchar(36)) + '|' + '1'
from
dbo.WPBIOGRAPHICALDEMOGRAPHIC WP
left outer join
dbo.APPUSER U on U.ID=WP.CONFIRMEDBYAPPUSERID
left outer join
dbo.CONSTITUENT C on C.ID=U.CONSTITUENTID
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
where
WP.WEALTHID = @WEALTHID and
(nullif(@SOURCE, '') is null or WP.SOURCE = @SOURCE) and
(@MATCH is null or WP.MC like @MATCH + '%') and
(((0 <= @CONFIDENCE and @CONFIDENCE <=5) and RC.CONFIDENCE >= @CONFIDENCE) or
((@CONFIDENCE = 99) and WP.CONFIRMED = 1))
order by
CONFIRMED desc,
CONFIDENCE desc,
FULLNAME,
BIRTHDATEFUZZY;
end