USP_DATALIST_WPPRIVATEFOUNDATIONS
Displays a list of WealthPoint Private Foundation data.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @WEALTHID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @MATCH | nvarchar(100) | IN | Match code |
| @SOURCE | nvarchar(100) | IN | Source |
| @CONFIDENCE | int | IN | Confidence |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_WPPRIVATEFOUNDATIONS (
@WEALTHID uniqueidentifier = null,
@MATCH nvarchar(100) = null,
@SOURCE nvarchar(100) = null,
@CONFIDENCE int = 0
) as
begin
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,
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.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
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
WP.COMPANY,
WP.SOURCE;
end