USP_DATALIST_PROSPECTRESEARCHDASHBOARDEXTENDEDRELATIONSHIPS
This datalist returns a constituent's extended relationships that are used by the prospect research dashboard.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Constituent ID |
| @ISVISIBLE | bit | IN | Is visible |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_PROSPECTRESEARCHDASHBOARDEXTENDEDRELATIONSHIPS
(
@CONSTITUENTID uniqueidentifier,
@ISVISIBLE bit = 1,
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;
--If you update the MAXSIZE value, you will need to update the related message in the report client component(WealthInformation_TooManyRelationships).
declare @MAXSIZE int = 100
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 CTE_UNIQUE_BOS as(
select
BO.WPRELATIONSHIP_BO_ID, COUNT(BORI.ID) as COUNT
from WPBUSINESSOWNERSHIP BO
left join dbo.WPRELATIONSHIP_BO BOR
on BO.WPRELATIONSHIP_BO_ID = BOR.ID
left join dbo.WPRELATIONSHIP_BO_IND BORI
on BOR.ID = BORI.WPRELATIONSHIP_BO_ID
where
BO.WEALTHID = @CONSTITUENTID and CONFIRMED = 1
group by BO.WPRELATIONSHIP_BO_ID
),
CTE_UNIQUE_NPAS as(
select
NPA.WPRELATIONSHIP_NPA_ID, MAX(NPA.FORMYEAR) as MAXYEAR, COUNT(NPARI.ID) as COUNT
from
dbo.WPNONPROFITAFFILIATION NPA
left join dbo.WPRELATIONSHIP_NPA NPAR
on NPA.WPRELATIONSHIP_NPA_ID = NPAR.ID
left join dbo.WPRELATIONSHIP_NPA_IND NPARI
on NPAR.ID = NPARI.WPRELATIONSHIP_NPA_ID
where
NPA.WEALTHID = @CONSTITUENTID and CONFIRMED = 1
group by NPA.WPRELATIONSHIP_NPA_ID
),
CTE_UNIQUE_PFS as(
select
PF.WPRELATIONSHIP_PF_ID, MAX(FORMYEAR) as MAXYEAR, COUNT(PFRI.ID) as COUNT
from
dbo.WPPRIVATEFOUNDATION PF
left join dbo.WPRELATIONSHIP_PF PFR
on PF.WPRELATIONSHIP_PF_ID = PFR.ID
left join dbo.WPRELATIONSHIP_PF_IND PFRI
on PFR.ID = PFRI.WPRELATIONSHIP_PF_ID
where
WEALTHID = @CONSTITUENTID and CONFIRMED = 1
group by PF.WPRELATIONSHIP_PF_ID
)
--Business ownership
select
BO.ID,
coalesce(BUSINESS.NAME, BO.COMPANY) COMPANY,
BO.SOURCE,
case
when BO.SPOUSEIND = 0 then NF_C.NAME
when BO.SPOUSEIND = 1 then coalesce(NF_SPOUSE.NAME, NF_C.NAME)
end MATCHNAME,
BORI.LASTNAME,
coalesce(NF_IND.NAME, BORI.FULLNAME) RELATIONSHIPNAME,
BORI.TITLE,
BUSINESS.LOOKUPID ORGLOOKUPID,
IND.LOOKUPID,
BO.TITLE MATCHTITLE,
case
when CTE_UNIQUE_BOS.COUNT <= @MAXSIZE then convert(bit, 0)
else convert(bit,1)
end TOOMANYORGS
from
dbo.WPBUSINESSOWNERSHIP BO
left join CTE_UNIQUE_BOS
on CTE_UNIQUE_BOS.WPRELATIONSHIP_BO_ID = BO.WPRELATIONSHIP_BO_ID and CTE_UNIQUE_BOS.COUNT <= @MAXSIZE
left join dbo.CONSTITUENT C
on BO.WEALTHID = C.ID
left join dbo.RELATIONSHIP REL
on C.ID = REL.RELATIONSHIPCONSTITUENTID and ISSPOUSE = 1
left join dbo.CONSTITUENT SPOUSE
on REL.RECIPROCALCONSTITUENTID = SPOUSE.ID
left join dbo.WPRELATIONSHIP_BO BOR
on BO.WPRELATIONSHIP_BO_ID = BOR.ID
left join dbo.WPRELATIONSHIP_BO_IND BORI
on BOR.ID = BORI.WPRELATIONSHIP_BO_ID
left join dbo.CONSTITUENT IND
on BORI.CONSTITUENTID = IND.ID
left join dbo.WPRELATIONSHIP_BO RELBO
on RELBO.ID = BO.WPRELATIONSHIP_BO_ID
left join dbo.CONSTITUENT BUSINESS
on BUSINESS.ID = RELBO.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF_C
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPOUSE.ID) NF_SPOUSE
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(IND.ID) NF_IND
where
BORI.ID is not null and
BO.WEALTHID = @CONSTITUENTID and
BO.CONFIRMED = 1
and (@ISADMIN = 1 or
@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, BO.WEALTHID, @APPUSER_IN_NOSECGROUPROLE) = 1)
union all
--Nonprofit Affiliations
select
NPA.ID,
coalesce(ORG.NAME, NPA.DN_ORGANIZATION) COMPANY,
NPA.SOURCE,
case
when NPA.SPOUSEFLAG = 0 then NF_C.NAME
when NPA.SPOUSEFLAG = 1 then coalesce(NF_SPOUSE.NAME, NF_C.NAME)
end MATCHNAME,
NPARI.LASTNAME,
coalesce(NF_IND.NAME, NPARI.FULLNAME) RELATIONSHIPNAME,
NPARI.TITLE,
ORG.LOOKUPID ORGLOOKUPID,
IND.LOOKUPID,
NPA.TITLE MATCHTITLE,
case
when CTE_UNIQUE_NPAS.COUNT <= @MAXSIZE then convert(bit, 0)
else convert(bit, 1)
end TOOMANYORGS
from
dbo.WPNONPROFITAFFILIATION NPA
inner join CTE_UNIQUE_NPAS NPAFILTER
on NPAFILTER.WPRELATIONSHIP_NPA_ID = NPA.WPRELATIONSHIP_NPA_ID and NPAFILTER.MAXYEAR = NPA.FORMYEAR
left join CTE_UNIQUE_NPAS
on CTE_UNIQUE_NPAS.WPRELATIONSHIP_NPA_ID = NPA.WPRELATIONSHIP_NPA_ID and CTE_UNIQUE_NPAS.MAXYEAR = NPA.FORMYEAR and CTE_UNIQUE_NPAS.COUNT <= @MAXSIZE
left join dbo.CONSTITUENT C
on NPA.WEALTHID = C.ID
left join dbo.RELATIONSHIP REL
on C.ID = REL.RELATIONSHIPCONSTITUENTID and ISSPOUSE = 1
left join dbo.CONSTITUENT SPOUSE
on REL.RECIPROCALCONSTITUENTID = SPOUSE.ID
left join dbo.WPRELATIONSHIP_NPA NPAR
on NPA.WPRELATIONSHIP_NPA_ID = NPAR.ID and CTE_UNIQUE_NPAS.WPRELATIONSHIP_NPA_ID is not null
left join dbo.WPRELATIONSHIP_NPA_IND NPARI
on NPAR.ID = NPARI.WPRELATIONSHIP_NPA_ID
left join dbo.CONSTITUENT IND
on NPARI.CONSTITUENTID = IND.ID
left join dbo.WPRELATIONSHIP_NPA RELNPA
on RELNPA.ID = NPA.WPRELATIONSHIP_NPA_ID and CTE_UNIQUE_NPAS.WPRELATIONSHIP_NPA_ID is not null
left join dbo.CONSTITUENT ORG
on ORG.ID = RELNPA.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF_C
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPOUSE.ID) NF_SPOUSE
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(IND.ID) NF_IND
where
(NPARI.ID is not null or NPAFILTER.COUNT > @MAXSIZE) and
NPA.WEALTHID = @CONSTITUENTID and
NPA.CONFIRMED = 1
and (@ISADMIN = 1 or
@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, NPA.WEALTHID, @APPUSER_IN_NOSECGROUPROLE) = 1)
union all
--Private Foundations
select
PF.ID,
coalesce(ORG.NAME, COMPANY),
PF.SOURCE,
case
when PF.SPOUSEFLAG = 0 then NF_C.NAME
when PF.SPOUSEFLAG = 1 then coalesce(NF_SPOUSE.NAME, NF_C.NAME)
end MATCHNAME,
PFRI.LASTNAME,
coalesce(NF_IND.NAME, PFRI.FULLNAME) RELATIONSHIPNAME,
PFRI.TITLE,
ORG.LOOKUPID ORGLOOKUPID,
IND.LOOKUPID,
PF.TITLE MATCHTITLE,
case
when CTE_UNIQUE_PFS.COUNT <= @MAXSIZE then convert(bit, 0)
else convert(bit, 1)
end TOOMANYORGS
from
dbo.WPPRIVATEFOUNDATION PF
inner join CTE_UNIQUE_PFS PFFILTER
on PFFILTER.WPRELATIONSHIP_PF_ID = PF.WPRELATIONSHIP_PF_ID and PFFILTER.MAXYEAR = PF.FORMYEAR
left join CTE_UNIQUE_PFS
on CTE_UNIQUE_PFS.WPRELATIONSHIP_PF_ID = PF.WPRELATIONSHIP_PF_ID and CTE_UNIQUE_PFS.MAXYEAR = PF.FORMYEAR and CTE_UNIQUE_PFS.COUNT <= @MAXSIZE
left join dbo.CONSTITUENT C
on PF.WEALTHID = C.ID
left join dbo.RELATIONSHIP REL
on C.ID = REL.RELATIONSHIPCONSTITUENTID and ISSPOUSE = 1
left join dbo.CONSTITUENT SPOUSE
on REL.RECIPROCALCONSTITUENTID = SPOUSE.ID
left join dbo.WPRELATIONSHIP_PF PFR
on PF.WPRELATIONSHIP_PF_ID = PFR.ID and CTE_UNIQUE_PFS.WPRELATIONSHIP_PF_ID is not null
left join dbo.WPRELATIONSHIP_PF_IND PFRI
on PFR.ID = PFRI.WPRELATIONSHIP_PF_ID
left join dbo.CONSTITUENT IND
on PFRI.CONSTITUENTID = IND.ID
left join dbo.WPRELATIONSHIP_PF RELPF
on RELPF.ID = PF.WPRELATIONSHIP_PF_ID and CTE_UNIQUE_PFS.WPRELATIONSHIP_PF_ID is not null
left join dbo.CONSTITUENT ORG
on ORG.ID = RELPF.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF_C
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPOUSE.ID) NF_SPOUSE
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(IND.ID) NF_IND
where
(PFRI.ID is not null or PFFILTER.COUNT > @MAXSIZE) and
PF.WEALTHID = @CONSTITUENTID and
PF.CONFIRMED = 1
and (@ISADMIN = 1 or
@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, PF.WEALTHID, @APPUSER_IN_NOSECGROUPROLE) = 1)
order by
COMPANY asc, ID, LASTNAME, RELATIONSHIPNAME asc
end
else
begin
with CTE_UNIQUE_NPAS as(
select
WPRELATIONSHIP_NPA_ID, MAX(FORMYEAR) as MAXYEAR
from
dbo.WPNONPROFITAFFILIATION
where
WEALTHID = @CONSTITUENTID and CONFIRMED = 1
group by WPRELATIONSHIP_NPA_ID
),
CTE_UNIQUE_PFS as(
select
WPRELATIONSHIP_PF_ID, MAX(FORMYEAR) as MAXYEAR
from
dbo.WPPRIVATEFOUNDATION
where
WEALTHID = @CONSTITUENTID and CONFIRMED = 1
group by WPRELATIONSHIP_PF_ID
)
--Business ownership
select top 1
BO.ID,
coalesce(BUSINESS.NAME, BO.COMPANY) COMPANY,
coalesce(BUSINESS.NAME, BO.COMPANY) SORTCOMPANY,
BO.SOURCE,
case
when BO.SPOUSEIND = 0 then NF_C.NAME
when BO.SPOUSEIND = 1 then coalesce(NF_SPOUSE.NAME, NF_C.NAME)
end MATCHNAME,
null LASTNAME,
null RELATIONSHIPNAME,
null TITLE,
null ORGLOOKUPID,
null LOOKUPID,
null TOOMANYORGS
from
dbo.WPBUSINESSOWNERSHIP BO
left join dbo.CONSTITUENT C
on BO.WEALTHID = C.ID
left join dbo.RELATIONSHIP REL
on C.ID = REL.RELATIONSHIPCONSTITUENTID and ISSPOUSE = 1
left join dbo.CONSTITUENT SPOUSE
on REL.RECIPROCALCONSTITUENTID = SPOUSE.ID
left join dbo.WPRELATIONSHIP_BO RELBO
on RELBO.ID = BO.WPRELATIONSHIP_BO_ID
left join dbo.CONSTITUENT BUSINESS
on BUSINESS.ID = RELBO.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF_C
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPOUSE.ID) NF_SPOUSE
where
BO.WEALTHID = @CONSTITUENTID and
BO.CONFIRMED = 1 and
exists(select top 1 BO_EXISTS.ID
from dbo.WPBUSINESSOWNERSHIP BO_EXISTS
inner join dbo.WPRELATIONSHIP_BO BOR_EXISTS
on BO.WPRELATIONSHIP_BO_ID = BOR_EXISTS.ID
inner join dbo.WPRELATIONSHIP_BO_IND BORI
on BOR_EXISTS.ID = BORI.WPRELATIONSHIP_BO_ID
left join dbo.CONSTITUENT IND
on BORI.CONSTITUENTID = IND.ID
where
BO.WEALTHID = @CONSTITUENTID and
BO_EXISTS.ID = BO.ID)
union all
--Nonprofit Affiliations
select top 1
NPA.ID,
NPA.DN_ORGANIZATION COMPANY,
NPA.DN_ORGANIZATION SORTCOMPANY,
NPA.SOURCE,
case
when NPA.SPOUSEFLAG = 0 then NF_C.NAME
when NPA.SPOUSEFLAG = 1 then coalesce(NF_SPOUSE.NAME, NF_C.NAME)
end MATCHNAME,
null LASTNAME,
null RELATIONSHIPNAME,
null TITLE,
null ORGLOOKUPID,
null LOOKUPID,
null TOOMANYORGS
from
dbo.WPNONPROFITAFFILIATION NPA
inner join CTE_UNIQUE_NPAS
on CTE_UNIQUE_NPAS.WPRELATIONSHIP_NPA_ID = NPA.WPRELATIONSHIP_NPA_ID and CTE_UNIQUE_NPAS.MAXYEAR = NPA.FORMYEAR
left join dbo.CONSTITUENT C
on NPA.WEALTHID = C.ID
left join dbo.RELATIONSHIP REL
on C.ID = REL.RELATIONSHIPCONSTITUENTID and ISSPOUSE = 1
left join dbo.CONSTITUENT SPOUSE
on REL.RECIPROCALCONSTITUENTID = SPOUSE.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF_C
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPOUSE.ID) NF_SPOUSE
where
NPA.WEALTHID = @CONSTITUENTID and
NPA.CONFIRMED = 1 and
exists(select top 1 NPA_EXISTS.ID
from dbo.WPNONPROFITAFFILIATION NPA_EXISTS
inner join dbo.WPRELATIONSHIP_NPA NPAR_EXISTS
on NPA.WPRELATIONSHIP_NPA_ID = NPAR_EXISTS.ID
inner join dbo.WPRELATIONSHIP_NPA_IND NPARI
on NPAR_EXISTS.ID = NPARI.WPRELATIONSHIP_NPA_ID
left join dbo.CONSTITUENT IND
on NPARI.CONSTITUENTID = IND.ID
where
NPA.WEALTHID = @CONSTITUENTID and
NPA_EXISTS.ID = NPA.ID)
union all
--Private Foundations
select top 1
PF.ID,
COMPANY,
COMPANY SORTCOMPANY,
PF.SOURCE,
case
when PF.SPOUSEFLAG = 0 then NF_C.NAME
when PF.SPOUSEFLAG = 1 then coalesce(NF_SPOUSE.NAME, NF_C.NAME)
end MATCHNAME,
null LASTNAME,
null RELATIONSHIPNAME,
null TITLE,
null ORGLOOKUPID,
null LOOKUPID,
null TOOMANYORGS
from
dbo.WPPRIVATEFOUNDATION PF
inner join CTE_UNIQUE_PFS
on CTE_UNIQUE_PFS.WPRELATIONSHIP_PF_ID = PF.WPRELATIONSHIP_PF_ID and CTE_UNIQUE_PFS.MAXYEAR = PF.FORMYEAR
left join dbo.CONSTITUENT C
on PF.WEALTHID = C.ID
left join dbo.RELATIONSHIP REL
on C.ID = REL.RELATIONSHIPCONSTITUENTID and ISSPOUSE = 1
left join dbo.CONSTITUENT SPOUSE
on REL.RECIPROCALCONSTITUENTID = SPOUSE.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF_C
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPOUSE.ID) NF_SPOUSE
where
PF.WEALTHID = @CONSTITUENTID and
PF.CONFIRMED = 1 and
exists(select top 1 PF_EXISTS.ID
from dbo.WPPRIVATEFOUNDATION PF_EXISTS
inner join dbo.WPRELATIONSHIP_PF PFR_EXISTS
on PF.WPRELATIONSHIP_PF_ID = PFR_EXISTS.ID
inner join dbo.WPRELATIONSHIP_PF_IND PFRI
on PFR_EXISTS.ID = PFRI.WPRELATIONSHIP_PF_ID
left join dbo.CONSTITUENT IND
on PFRI.CONSTITUENTID = IND.ID
where
PF.WEALTHID = @CONSTITUENTID and
PF_EXISTS.ID = PF.ID)
order by COMPANY asc, ID, LASTNAME, RELATIONSHIPNAME asc
end