USP_DATALIST_WEALTHINFORMATIONDASHBOARDASSETDISTRIBUTION
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @CONFIDENCE | int | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_WEALTHINFORMATIONDASHBOARDASSETDISTRIBUTION
(
@CONSTITUENTID uniqueidentifier,
@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);
declare @ASSETTABLE TABLE
(
Name nvarchar(256),
Value money
)
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.WPREALESTATE 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
)
insert into @ASSETTABLE (Name, Value)
select 'Real Estate', SUM(PROPERTYVALUATION)
from
dbo.WPREALESTATE WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
where
WP.WEALTHID = @CONSTITUENTID
and REJECTED = 0 and
WP.HISTORICCODE <> 1 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, WP.ID, @APPUSER_IN_NOSECGROUPROLE) = 1);
end
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
)
insert into @ASSETTABLE (Name, Value)
select 'Businesses', SUM(OWNERSHIPVALUE)
from
dbo.WPBUSINESSOWNERSHIP WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
where
WP.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, WP.ID, @APPUSER_IN_NOSECGROUPROLE) = 1);
end
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.WPSECURITIES 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
)
insert into @ASSETTABLE (Name, Value)
select 'Securities', SUM(VALUEDIRECT)
from
dbo.WPSECURITIES WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
where
WP.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, WP.ID, @APPUSER_IN_NOSECGROUPROLE) = 1);
end
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.WPINCOMECOMPENSATION 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
)
insert into @ASSETTABLE (Name, Value)
select 'Income/Compensation', SUM(TOTALCOMPENSATION)
from
dbo.WPINCOMECOMPENSATION WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
where
WP.WEALTHID = @CONSTITUENTID
and REJECTED = 0 and
WP.HISTORICCODE <> 1 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, WP.ID, @APPUSER_IN_NOSECGROUPROLE) = 1);
end
begin
with RECORDCONFIDENCE as(
select
WP.ID,
case
when WP.CONFIRMED = 1 then '5'
when WP.REJECTED = 1 then '0'
else
(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION)
end as CONFIDENCE
from
dbo.WPOTHERASSET WP
)
insert into @ASSETTABLE (Name, Value)
select 'Other Assets', SUM(VALUE)
from
dbo.WPOTHERASSET WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
where
WP.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
--Remove empty assets
delete from @ASSETTABLE where VALUE = 0 or VALUE is NULL;
select NAME, VALUE from @ASSETTABLE;