USP_DATALIST_WEALTHINFORMATIONDASHBOARDREALESTATE
This datalist returns WealthPiont real estate 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_WEALTHINFORMATIONDASHBOARDREALESTATE
(
@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.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
)
select top 1000
WP.ID,
SOURCE,
OWNER,
PROPADDRESS,
PROPCITY,
PROPSTATE,
PROPZIP,
TOTALMARKETVALUE,
ASSESSEDTOTALVALUE,
coalesce(MARKETVALUEYEAR, '') as MARKETVALUEYEAR,
coalesce(ASSESSMENTYEAR, '') as ASSESSMENTYEAR,
coalesce(BB_COUNTY, '') as BB_COUNTY,
coalesce(LANDUSE, '') as LANDUSE,
coalesce(LEGALDESCRIPTION, '') as LEGALDESCRIPTION,
SALEDATE,
coalesce(SALEPRICE, '') as SALEPRICE,
coalesce(LOANAMOUNT, '') as LOANAMOUNT,
coalesce(BUYER, '') as BUYER,
coalesce(SELLER, '') as SELLER,
coalesce(CNOTES, '') as CNOTES,
PROPERTYVALUATION,
ASSESSORSPARCELNUMBER,
OWNERCAREOFNAME,
MAILINGADDRESS,
ESTIMATEDVALUE,
ESTIMATEDVALUEDATE,
nullif(CONFIDENCESCORE,-1) as CONFIDENCESCORE,
CONFIDENCESCOREDATE,
ASSESSEDTOTALVALUE,
ASSESSMENTYEAR,
TOTALMARKETVALUE,
MARKETVALUEYEAR,
SUMMARYSALESVALUE,
SUMMARYSALESRECORDINGDATE,
LOANAMOUNT,
MORTRECORDINGDATE,
MORT2_LOANAMOUNT,
MORT2_RECORDINGDATE
from
dbo.WPREALESTATE WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
where
WEALTHID = @CONSTITUENTID and REJECTED = 0 and 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, 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.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
)
select top 1
WP.ID,
SOURCE,
OWNER,
PROPADDRESS,
PROPCITY,
PROPSTATE,
PROPZIP,
TOTALMARKETVALUE,
ASSESSEDTOTALVALUE,
coalesce(MARKETVALUEYEAR, '') as MARKETVALUEYEAR,
coalesce(ASSESSMENTYEAR, '') as ASSESSMENTYEAR,
coalesce(BB_COUNTY, '') as BB_COUNTY,
coalesce(LANDUSE, '') as LANDUSE,
coalesce(LEGALDESCRIPTION, '') as LEGALDESCRIPTION,
SALEDATE,
coalesce(SALEPRICE, '') as SALEPRICE,
coalesce(LOANAMOUNT, '') as LOANAMOUNT,
coalesce(BUYER, '') as BUYER,
coalesce(SELLER, '') as SELLER,
coalesce(CNOTES, '') as CNOTES,
PROPERTYVALUATION,
ASSESSORSPARCELNUMBER,
OWNERCAREOFNAME,
MAILINGADDRESS,
ESTIMATEDVALUE,
ESTIMATEDVALUEDATE,
nullif(CONFIDENCESCORE,-1),
CONFIDENCESCOREDATE,
ASSESSEDTOTALVALUE,
ASSESSMENTYEAR,
TOTALMARKETVALUE,
MARKETVALUEYEAR,
SUMMARYSALESVALUE,
SUMMARYSALESRECORDINGDATE,
LOANAMOUNT,
MORTRECORDINGDATE,
MORT2_LOANAMOUNT,
MORT2_RECORDINGDATE
from
dbo.WPREALESTATE WP
left outer join
RECORDCONFIDENCE RC on RC.ID = WP.ID
where
WEALTHID = @CONSTITUENTID and REJECTED = 0 and HISTORICCODE <> 1 and
(((0 <= @CONFIDENCE and @CONFIDENCE <=5) and RC.CONFIDENCE >= @CONFIDENCE) or
((@CONFIDENCE = 99) and WP.CONFIRMED = 1))
end