USP_DATALIST_WPNONPROFITAFFILIATION_2
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @WEALTHID | uniqueidentifier | IN | |
| @MATCH | nvarchar(100) | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @CONFIDENCE | int | IN | |
| @CITY | nvarchar(50) | IN | |
| @STATE | nvarchar(100) | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_WPNONPROFITAFFILIATION_2
(
@WEALTHID uniqueidentifier = null,
@MATCH nvarchar(100) = null,
@SOURCE nvarchar(100) = null,
@CONFIDENCE int = 0,
@CITY nvarchar(50) = null,
@STATE nvarchar(100) = null
)
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.WPNONPROFITAFFILIATION 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,
--End Generic Fields
WP.DN_ORGANIZATION,
WP.TITLE,
WP.FORMYEAR,
WP.FULLNAME as NAME,
WP.CONFIRMED,
WP.REJECTED
from
dbo.WPNONPROFITAFFILIATION 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(@CITY, '') is null or WP.CITY = @CITY) and
(nullif(@STATE, '') is null or WP.STATE = @STATE) 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
RC.CONFIDENCE DESC,
WP.DN_ORGANIZATION,
WP.FORMYEAR;
end