USP_DATALIST_WPGROUPBIOGRAPHICAL
Displays a list of WealthPoint Biographical data for a group.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @GROUPID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @MEMBERID | uniqueidentifier | IN | Member |
| @MATCH | nvarchar(100) | IN | Match code |
| @SOURCE | nvarchar(100) | IN | Source |
| @CONFIDENCE | int | IN | Confidence |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_WPGROUPBIOGRAPHICAL
(
@GROUPID uniqueidentifier = null,
@MEMBERID uniqueidentifier = null,
@MATCH nvarchar(100) = null,
@SOURCE nvarchar(100) = null,
@CONFIDENCE int = 0
)
as
begin
declare @MEMBERSTOINCLUDE table (MEMBERID uniqueidentifier)
if @MEMBERID is not null
insert into @MEMBERSTOINCLUDE (MEMBERID) values (@MEMBERID)
else
begin
declare @CURRENTDATEEARLIESTTIME datetime;
set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
insert into @MEMBERSTOINCLUDE (MEMBERID)
select
GM.MEMBERID
from dbo.GROUPMEMBER GM
left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GM.GROUPID = @GROUPID
-- the GMDR.DATETO is set to the earliest time on that day, so use a strictly greater than in checks using current day
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME))
or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
end;
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,
MEMBER.ID as MEMBERID,
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,
MEMBER.NAME as MEMBERNAME,
WP.DATECONFIRMED,
coalesce(C.NAME,U.USERNAME),
WP.SOURCE,
--End Generic Fields
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
from
dbo.WPBIOGRAPHICAL WP
inner join dbo.CONSTITUENT MEMBER on WP.WEALTHID = MEMBER.ID
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 in (select MEMBERID from @MEMBERSTOINCLUDE) and
(nullif(@SOURCE, '') is null or WP.SOURCE like @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,
MEMBER.ID as MEMBERID,
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,
MEMBER.NAME as MEMBERNAME ,
WP.DATECONFIRMED,
coalesce(C.NAME,U.USERNAME),
WP.SOURCE,
--End Generic Fields
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 as BIRTHDATEFUZZY,
1 as ISDEMOGRAPHIC
from
dbo.WPBIOGRAPHICALDEMOGRAPHIC WP
inner join dbo.CONSTITUENT MEMBER on WP.WEALTHID = MEMBER.ID
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 in (select MEMBERID from @MEMBERSTOINCLUDE) 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
SOURCE;
end