USP_DATALIST_WEALTHINFORMATIONDASHBOARDBUSINESSOWNERSHIP
This datalist returns WealthPiont business ownership 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_WEALTHINFORMATIONDASHBOARDBUSINESSOWNERSHIP
(
@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.WPBUSINESSOWNERSHIP 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
WP.ID,
SOURCE,
COMPANY,
SALES_VOLUME,
FULLNAME,
TITLE,
OWNERSHIPVALUE,
LINE_OF_BUSINESS_DESCRIPTION,
LINE1,
CITY,
STATE,
ZIP,
PHONE,
EMPLOYEES_TOTAL,
YEAR_STARTED,
HELD,
SIC_CODE,
VALUATION,
PERCENTAGE,
CNOTES,
SICNAME,
MAILINGLINE1,
MAILINGCITY,
MAILINGSTATE,
MAILINGZIP,
BO.DUNS,
SIC_CODE1,
SIC_CODE2,
SIC_CODE3,
SIC_CODE4,
SIC_CODE5,
SIC_CODE6,
dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(LINE1, '', CITY, STATE, ZIP) as ADDRESS,
dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(MAILINGLINE1, '', MAILINGCITY, MAILINGSTATE, MAILINGZIP) as MAILINGADDRESS
from
dbo.WPBUSINESSOWNERSHIP WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
left outer join
dbo.WPRELATIONSHIP_BO BO on BO.ID = WP.WPRELATIONSHIP_BO_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.WPBUSINESSOWNERSHIP 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
WP.ID,
SOURCE,
COMPANY,
SALES_VOLUME,
FULLNAME,
TITLE,
OWNERSHIPVALUE,
LINE_OF_BUSINESS_DESCRIPTION,
LINE1,
CITY,
STATE,
ZIP,
PHONE,
EMPLOYEES_TOTAL,
YEAR_STARTED,
HELD,
SIC_CODE,
VALUATION,
PERCENTAGE,
CNOTES,
SICNAME,
MAILINGLINE1,
MAILINGCITY,
MAILINGSTATE,
MAILINGZIP,
BO.DUNS,
SIC_CODE1,
SIC_CODE2,
SIC_CODE3,
SIC_CODE4,
SIC_CODE5,
SIC_CODE6,
dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(LINE1, '', CITY, STATE, ZIP) as ADDRESS,
dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(MAILINGLINE1, '', MAILINGCITY, MAILINGSTATE, MAILINGZIP) as MAILINGADDRESS
from
dbo.WPBUSINESSOWNERSHIP WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
left outer join
dbo.WPRELATIONSHIP_BO BO on BO.ID = WP.WPRELATIONSHIP_BO_ID
where
WEALTHID = @CONSTITUENTID and REJECTED = 0 and
(((0 <= @CONFIDENCE and @CONFIDENCE <=5) and RC.CONFIDENCE >= @CONFIDENCE) or
((@CONFIDENCE = 99) and WP.CONFIRMED = 1))
end