UFN_QUERY_BUSINESSOWNERSHIPSBYCONSTITUENT
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN |
Definition
Copy
CREATE function [dbo].[UFN_QUERY_BUSINESSOWNERSHIPSBYCONSTITUENT]
(
@ID uniqueidentifier
)
returns table
as
return
with WEALTH_CTE as(
select
WPBUSINESSOWNERSHIP.ID,
WPBUSINESSOWNERSHIP.WEALTHID,
WPBUSINESSOWNERSHIP.CONFIRMED,
WPBUSINESSOWNERSHIP.CONFIRMEDBYAPPUSERID, --Confirmed By
WPBUSINESSOWNERSHIP.REJECTED,
WPBUSINESSOWNERSHIP.REJECTEDBYAPPUSERID, --Rejected By
WPBUSINESSOWNERSHIP.SOURCE, --Source
WPBUSINESSOWNERSHIP.MC, --Match code
WPBUSINESSOWNERSHIP.LINE1, --Company address
WPBUSINESSOWNERSHIP.CITY, --Company city
WPBUSINESSOWNERSHIP.STATE, --Company state
WPBUSINESSOWNERSHIP.ZIP, --Company ZIP
WPBUSINESSOWNERSHIP.COMPANY, --Company name
WPBUSINESSOWNERSHIP.VALUATION, --Company valuation
WPBUSINESSOWNERSHIP.LINE_OF_BUSINESS_DESCRIPTION, --Description
coalesce(WPRELATIONSHIP_BO.DUNS, '') as DUNS, --DUNS
WPBUSINESSOWNERSHIP.HELD, --Held
WPBUSINESSOWNERSHIP.TITLE, --Job title
WPBUSINESSOWNERSHIP.MAILINGLINE1, --Mailing address
WPBUSINESSOWNERSHIP.MAILINGCITY, --Mailing city
WPBUSINESSOWNERSHIP.MAILINGSTATE, --Mailing state
WPBUSINESSOWNERSHIP.MAILINGZIP, --Mailing ZIP
WPBUSINESSOWNERSHIP.FULLNAME, --Name
CAST(WPBUSINESSOWNERSHIP.OWNERSHIPVALUE as money) OWNERSHIPVALUE,--Ownership value
WPBUSINESSOWNERSHIP.SALES_VOLUME, --Sales
WPBUSINESSOWNERSHIP.SICNAME, --Trade name
WPBUSINESSOWNERSHIP.RECURSIVEMATCH,
case
when WPBUSINESSOWNERSHIP.CONFIRMED = 1 then '5'
when WPBUSINESSOWNERSHIP.REJECTED = 1 then '0'
else
isnull(coalesce(CONFIDENCERATING.CONFIDENCE,MATCHCODE.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
end as CONFIDENCE --Confidence
from
dbo.WPBUSINESSOWNERSHIP
left outer join
dbo.MATCHCODE on WPBUSINESSOWNERSHIP.MC = MATCHCODE.MATCHCODE
left outer join
dbo.WEALTHSOURCE on WPBUSINESSOWNERSHIP.SOURCE = WEALTHSOURCE.SOURCE
left outer join
dbo.CONFIDENCERATING on CONFIDENCERATING.MATCHCODEID = MATCHCODE.ID and WEALTHSOURCE.ID = CONFIDENCERATING.WEALTHSOURCEID
left join dbo.WPRELATIONSHIP_BO
on WPBUSINESSOWNERSHIP.WPRELATIONSHIP_BO_ID = WPRELATIONSHIP_BO.ID
where
WPBUSINESSOWNERSHIP.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.LINE1, --Company address
WEALTH_CTE.CITY, --Company city
WEALTH_CTE.STATE, --Company state
WEALTH_CTE.ZIP, --Company ZIP
WEALTH_CTE.COMPANY, --Company name
WEALTH_CTE.VALUATION, --Company valuation
WEALTH_CTE.LINE_OF_BUSINESS_DESCRIPTION, --Description
WEALTH_CTE.DUNS, --DUNS
WEALTH_CTE.HELD, --Held
WEALTH_CTE.TITLE, --Job title
WEALTH_CTE.MAILINGLINE1, --Mailing address
WEALTH_CTE.MAILINGCITY, --Mailing city
WEALTH_CTE.MAILINGSTATE, --Mailing state
WEALTH_CTE.MAILINGZIP, --Mailing ZIP
WEALTH_CTE.FULLNAME, --Name
WEALTH_CTE.OWNERSHIPVALUE, --Ownership value
WEALTH_CTE.SALES_VOLUME, --Sales
WEALTH_CTE.SICNAME, --Trade name
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