UFN_QUERY_FOUNDATIONAFFILIATIONSBYCONSTITUENT
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN |
Definition
Copy
CREATE function [dbo].[UFN_QUERY_FOUNDATIONAFFILIATIONSBYCONSTITUENT]
(
@ID uniqueidentifier
)
returns table
as
return
with WEALTH_CTE as(
select
WPPRIVATEFOUNDATION.ID,
WPPRIVATEFOUNDATION.WEALTHID,
WPPRIVATEFOUNDATION.CONFIRMED,
WPPRIVATEFOUNDATION.CONFIRMEDBYAPPUSERID, --Confirmed By
WPPRIVATEFOUNDATION.REJECTED,
WPPRIVATEFOUNDATION.REJECTEDBYAPPUSERID, --Rejected By
WPPRIVATEFOUNDATION.SOURCE, --Source
WPPRIVATEFOUNDATION.MC, --Match code
WPPRIVATEFOUNDATION.COMP, --Compensation
WPPRIVATEFOUNDATION.FAIRMARKETVALUEASSETS, --Fair market value assets
WPPRIVATEFOUNDATION.HCITY, --Individual city
WPPRIVATEFOUNDATION.FORMYEAR, --Individual form year
WPPRIVATEFOUNDATION.HSTATE, --Individual state
WPPRIVATEFOUNDATION.HZIP, --Individual ZIP
WPPRIVATEFOUNDATION.TITLE, --Job title
WPPRIVATEFOUNDATION.FULLNAME, --Name
WPPRIVATEFOUNDATION.COMPANY, --Organization
WPPRIVATEFOUNDATION.CITY, --Organization city
WPPRIVATEFOUNDATION.ORGFORMYEAR, --Organization form year
WPPRIVATEFOUNDATION.STATE, --Organization state
WPPRIVATEFOUNDATION.ZIP, --Organization ZIP
WPPRIVATEFOUNDATION.RULING_YEAR, --Ruling year
WPPRIVATEFOUNDATION.TOTALASSETS_FMV, --Total assets
WPPRIVATEFOUNDATION.RECURSIVEMATCH,
case
when WPPRIVATEFOUNDATION.CONFIRMED = 1 then '5'
when WPPRIVATEFOUNDATION.REJECTED = 1 then '0'
else
isnull(coalesce(CONFIDENCERATING.CONFIDENCE,MATCHCODE.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
end as CONFIDENCE --Confidence
from
dbo.WPPRIVATEFOUNDATION
left outer join
dbo.MATCHCODE on WPPRIVATEFOUNDATION.MC = MATCHCODE.MATCHCODE
left outer join
dbo.WEALTHSOURCE on WPPRIVATEFOUNDATION.SOURCE = WEALTHSOURCE.SOURCE
left outer join
dbo.CONFIDENCERATING on CONFIDENCERATING.MATCHCODEID = MATCHCODE.ID and WEALTHSOURCE.ID = CONFIDENCERATING.WEALTHSOURCEID
where
WPPRIVATEFOUNDATION.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.COMP, --Compensation
WEALTH_CTE.FAIRMARKETVALUEASSETS, --Fair market value assets
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.COMPANY, --Organization
WEALTH_CTE.CITY, --Organization city
WEALTH_CTE.ORGFORMYEAR, --Organization form year
WEALTH_CTE.STATE, --Organization state
WEALTH_CTE.ZIP, --Organization ZIP
WEALTH_CTE.RULING_YEAR, --Ruling year
WEALTH_CTE.TOTALASSETS_FMV, --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