UFN_QUERY_NONPROFITAFFILIATIONSBYCONSTITUENT
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN |
Definition
Copy
CREATE function [dbo].[UFN_QUERY_NONPROFITAFFILIATIONSBYCONSTITUENT]
(
@ID uniqueidentifier
)
returns table
as
return
with WEALTH_CTE as(
select
WPNONPROFITAFFILIATION.ID,
WPNONPROFITAFFILIATION.WEALTHID,
WPNONPROFITAFFILIATION.CONFIRMED,
WPNONPROFITAFFILIATION.CONFIRMEDBYAPPUSERID, --Confirmed By
WPNONPROFITAFFILIATION.REJECTED,
WPNONPROFITAFFILIATION.REJECTEDBYAPPUSERID, --Rejected By
WPNONPROFITAFFILIATION.SOURCE, --Source
WPNONPROFITAFFILIATION.MC, --Match code
WPNONPROFITAFFILIATION.HCITY, --Individual city
WPNONPROFITAFFILIATION.FORMYEAR, --Individual form year
WPNONPROFITAFFILIATION.HSTATE, --Individual state
WPNONPROFITAFFILIATION.HZIP, --Individual ZIP
WPNONPROFITAFFILIATION.TITLE, --Job title
WPNONPROFITAFFILIATION.FULLNAME, --Name
WPNONPROFITAFFILIATION.DN_ORGANIZATION, --Organization
WPNONPROFITAFFILIATION.CITY, --Organization city
WPNONPROFITAFFILIATION.ORGFORMYEAR, --Organization form year
WPNONPROFITAFFILIATION.STATE, --Organization state
WPNONPROFITAFFILIATION.ZIP, --Organization ZIP
WPNONPROFITAFFILIATION.REVENUE, --Revenue
WPNONPROFITAFFILIATION.RULINGYEAR, --Ruling year
WPNONPROFITAFFILIATION.SALARY, --Salary
WPNONPROFITAFFILIATION.TOTALASSETS, --Total assets
WPNONPROFITAFFILIATION.RECURSIVEMATCH,
case
when WPNONPROFITAFFILIATION.CONFIRMED = 1 then '5'
when WPNONPROFITAFFILIATION.REJECTED = 1 then '0'
else
isnull(coalesce(CONFIDENCERATING.CONFIDENCE,MATCHCODE.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
end as CONFIDENCE --Confidence
from
dbo.WPNONPROFITAFFILIATION
left outer join
dbo.MATCHCODE on WPNONPROFITAFFILIATION.MC = MATCHCODE.MATCHCODE
left outer join
dbo.WEALTHSOURCE on WPNONPROFITAFFILIATION.SOURCE = WEALTHSOURCE.SOURCE
left outer join
dbo.CONFIDENCERATING on CONFIDENCERATING.MATCHCODEID = MATCHCODE.ID and WEALTHSOURCE.ID = CONFIDENCERATING.WEALTHSOURCEID
where
WPNONPROFITAFFILIATION.WEALTHID=@ID
)
select
WEALTH_CTE.ID,
WEALTH_CTE.CONFIRMED,
coalesce(nullif(CONFIRMEDAPPUSER.DISPLAYNAME, ''), CONFIRMEDAPPUSER.USERNAME) CONFIRMEDBYUSER, --Confirmed by
WEALTH_CTE.REJECTED,
coalesce(nullif(REJECTEDAPPUSER.DISPLAYNAME, ''), REJECTEDAPPUSER.USERNAME) REJECTEDBYUSER, --Rejected by
WEALTH_CTE.SOURCE, --Source
WEALTH_CTE.MC, --Match code
MATCHCODE.DESCRIPTION MCDESCRIPTION, --Match code description
WEALTH_CTE.HCITY, --Individual city
WEALTH_CTE.FORMYEAR, --Individual form year
WEALTH_CTE.HSTATE, --Individual state
WEALTH_CTE.HZIP, --Individual ZIP
WEALTH_CTE.TITLE, --Job title
WEALTH_CTE.FULLNAME, --Name
WEALTH_CTE.DN_ORGANIZATION, --Organization
WEALTH_CTE.CITY, --Organization city
WEALTH_CTE.ORGFORMYEAR, --Organization form year
WEALTH_CTE.STATE, --Organization state
WEALTH_CTE.ZIP, --Organization ZIP
WEALTH_CTE.REVENUE, --Revenue
WEALTH_CTE.RULINGYEAR, --Ruling year
WEALTH_CTE.SALARY, --Salary
WEALTH_CTE.TOTALASSETS, --Total assets
WEALTH_CTE.CONFIDENCE,
case
when WEALTH_CTE.CONFIRMED = 1 and WEALTH_CTE.RECURSIVEMATCH = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_star_check_16.png'
when WEALTH_CTE.CONFIRMED = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_5_check_16.png'
when WEALTH_CTE.REJECTED = 1 then 'catalog:Blackbaud.AppFx.WealthPoint.Catalog.dll,Blackbaud.AppFx.WealthPoint.Catalog.level_0_16.png'
else
case WEALTH_CTE.RECURSIVEMATCH
when '1' then
case WEALTH_CTE.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 WEALTH_CTE.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
/*#EXTENSION*/
from
WEALTH_CTE
left join dbo.APPUSER CONFIRMEDAPPUSER on CONFIRMEDAPPUSER.ID = WEALTH_CTE.CONFIRMEDBYAPPUSERID
left join dbo.APPUSER REJECTEDAPPUSER on REJECTEDAPPUSER.ID = WEALTH_CTE.REJECTEDBYAPPUSERID
left join dbo.MATCHCODE on MATCHCODE.MATCHCODE = WEALTH_CTE.MC