USP_DATALIST_WEALTHINFORMATIONDASHBOARDBIOGRAPHICAL
This datalist returns WealthPiont biographical 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_WEALTHINFORMATIONDASHBOARDBIOGRAPHICAL
(
@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.WPBIOGRAPHICAL 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,
MC,
FULLNAME,
LINE1,
CITY,
STATE,
ZIP,
A2LINE1,
A2CITY,
A2STATE,
A2ZIP,
GENDER,
BIRTHDATE,
CNOTES,
BIRTHPLACE,
DECEASEDDATE,
OCCUPATION,
EDUCATION,
FAMILY,
CAREER,
DATEADDED,
DATECHANGED,
PERSONAL,
AWARDS,
MEMBERSHIPS,
RESEARCHINTERESTS,
POLITICALRELIGIOUSAFFILIATIONS,
CIVICMILITARYSERVICE,
LAW,
THOUGHTSONLIFE,
POSITIONSHELD,
CERTIFICATIONS,
dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(LINE1, '', CITY, STATE, ZIP) as HOMEADDRESS,
dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(A2LINE1, '', A2CITY, A2STATE, A2ZIP) as BUSINESSADDRESS
from
dbo.WPBIOGRAPHICAL WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.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);
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.WPBIOGRAPHICAL 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,
MC,
FULLNAME,
LINE1,
CITY,
STATE,
ZIP,
A2LINE1,
A2CITY,
A2STATE,
A2ZIP,
GENDER,
BIRTHDATE,
CNOTES,
BIRTHPLACE,
DECEASEDDATE,
OCCUPATION,
EDUCATION,
FAMILY,
CAREER,
DATEADDED,
DATECHANGED,
PERSONAL,
AWARDS,
MEMBERSHIPS,
RESEARCHINTERESTS,
POLITICALRELIGIOUSAFFILIATIONS,
CIVICMILITARYSERVICE,
LAW,
THOUGHTSONLIFE,
POSITIONSHELD,
CERTIFICATIONS,
dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(LINE1, '', CITY, STATE, ZIP) as HOMEADDRESS,
dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(A2LINE1, '', A2CITY, A2STATE, A2ZIP) as BUSINESSADDRESS
from
dbo.WPBIOGRAPHICAL WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.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);
end