USP_DATALIST_WEALTHINFORMATIONDASHBOARDPRIVATEFOUNDATION
This datalist returns WealthPiont private foundation information that is used by the wealth information dashboard.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Constituent |
| @ISVISIBLE | bit | IN | Visible |
| @CONFIDENCE | int | IN | Confidence |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_WEALTHINFORMATIONDASHBOARDPRIVATEFOUNDATION
(
@CONSTITUENTID uniqueidentifier,
@ISVISIBLE bit = 1,
@CONFIDENCE int = 0,
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;
declare @ISADMIN bit;
declare @APPUSER_IN_NONRACROLE bit;
declare @APPUSER_IN_NOSECGROUPROLE bit;
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
if @ISVISIBLE = 1
begin
with RECORDCONFIDENCE as(
select
WP.ID,
case
when WP.CONFIRMED = 1 then '5'
when WP.REJECTED = 1 then '0'
else
isnull(CR.CONFIDENCE,(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.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID
left outer join
dbo.WEALTHSOURCE WS on CR.WEALTHSOURCEID = WS.ID
where
WS.SOURCE like WP.SOURCE or WS.SOURCE is null
)
select top 1000
SOURCE,
COMPANY,
TOTALASSETS_FMV,
CITY,
STATE,
TITLE,
FILEDDATE,
case FORMYEAR
when 0 then NULL
else FORMYEAR
end as FORMYEAR,
FULLNAME,
COMP,
DESCRIPTION,
dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(LINE1, '', CITY, STATE, ZIP) as ADDRESS,
PF.EIN,
FAIRMARKETVALUEASSETS,
case
when HCITY <> '' then
HCITY +
case when HSTATE <> '' or HZIP <> '' then ', ' else '' end
else
''
end +
case
when HSTATE <> '' then
HSTATE + ' '
else
''
end + HZIP as LOCATION,
CNOTES
from
dbo.WPPRIVATEFOUNDATION WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
left outer join
dbo.WPRELATIONSHIP_PF PF on PF.ID = WP.WPRELATIONSHIP_PF_ID
where
WEALTHID = @CONSTITUENTID and REJECTED = 0 and
(((0 <= @CONFIDENCE and @CONFIDENCE <=5) and RC.CONFIDENCE >= @CONFIDENCE) or
((@CONFIDENCE = 99) and WP.CONFIRMED = 1))
and (@ISADMIN = 1 or
@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, WEALTHID, @APPUSER_IN_NOSECGROUPROLE) = 1)
order by COMPANY asc, FILEDDATE desc, TOTALASSETS_FMV desc
end
else
begin
with RECORDCONFIDENCE as(
select
WP.ID,
case
when WP.CONFIRMED = 1 then '5'
when WP.REJECTED = 1 then '0'
else
isnull(CR.CONFIDENCE,(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.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID
left outer join
dbo.WEALTHSOURCE WS on CR.WEALTHSOURCEID = WS.ID
where
WS.SOURCE like WP.SOURCE or WS.SOURCE is null
)
select top 1
SOURCE,
COMPANY,
TOTALASSETS_FMV,
CITY,
STATE,
TITLE,
FILEDDATE,
case FORMYEAR
when 0 then NULL
else FORMYEAR
end as FORMYEAR,
FULLNAME,
COMP,
DESCRIPTION,
dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(LINE1, '', CITY, STATE, ZIP) as ADDRESS,
PF.EIN,
FAIRMARKETVALUEASSETS,
case
when HCITY <> '' then
HCITY +
case when HSTATE <> '' or HZIP <> '' then ', ' else '' end
else
''
end +
case
when HSTATE <> '' then
HSTATE + ' '
else
''
end + HZIP as LOCATION,
CNOTES
from
dbo.WPPRIVATEFOUNDATION WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
left outer join
dbo.WPRELATIONSHIP_PF PF on PF.ID = WP.WPRELATIONSHIP_PF_ID
where
WEALTHID = @CONSTITUENTID and REJECTED = 0 and
(((0 <= @CONFIDENCE and @CONFIDENCE <=5) and RC.CONFIDENCE >= @CONFIDENCE) or
((@CONFIDENCE = 99) and WP.CONFIRMED = 1))
order by COMPANY asc, FILEDDATE desc, TOTALASSETS_FMV desc
end