USP_DATALIST_WPGROUPPRIVATEFOUNDATIONS
Displays a list of WealthPoint Private Foundation 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_WPGROUPPRIVATEFOUNDATIONS (
@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.WPPRIVATEFOUNDATION 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.COMPANY,
WP.TOTALASSETS_FMV,
WP.MC,
dbo.UFN_WPMATCHCODE_DESCRIPTION(WP.MC),
WP.FORMYEAR,
WP.FILEDDATE,
WP.FULLNAME as NAME,
WP.FAIRMARKETVALUEASSETS,
WP.CONFIRMED,
WP.REJECTED
from
dbo.WPPRIVATEFOUNDATION 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))
order by
WP.COMPANY,
WP.SOURCE;
end