UFN_WEALTHPOINT_EXTENDEDRELATIONSHIP2
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @WEALTHID | uniqueidentifier | IN | |
| @NAME | nvarchar(100) | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @COMPANY | nvarchar(100) | IN |
Definition
Copy
CREATE function dbo.UFN_WEALTHPOINT_EXTENDEDRELATIONSHIP2
(
@WEALTHID uniqueidentifier,
@NAME nvarchar(100) = null,
@SOURCE nvarchar(100) = null,
@COMPANY nvarchar(100) = null
) returns @OUTPUT table (
ID uniqueidentifier,
PARENTID uniqueidentifier,
VIEWFORMID uniqueidentifier,
MERGEID nvarchar(100),
COMPANY nvarchar(100),
MATCHNAME nvarchar(308),
LASTNAME nvarchar(100),
RELATIONSHIPNAME nvarchar(412),
TITLE nvarchar(100),
LOOKUPID nvarchar(100),
[TYPE] nvarchar(100),
LINKEDCONSTITUENTID uniqueidentifier,
RELATIONSHIPEXISTS bit,
CONFIDENCE nvarchar(2)
)
as
begin
with CTE_UNIQUE_NPAS as(
select
WPRELATIONSHIP_NPA_ID, MAX(FORMYEAR) as MAXYEAR
from
dbo.WPNONPROFITAFFILIATION
where
WEALTHID = @WEALTHID 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 = @WEALTHID and CONFIRMED = 1
group by WPRELATIONSHIP_PF_ID
),
EXTENDEDRELATIONSHIPS_CTE as (
--Business ownership
select distinct
BO.ID,
null PARENTID,
'881824cc-5f88-4425-8f88-538927541545' VIEWFORMID,
cast(BO.ID as nvarchar(36)) MERGEID,
coalesce(BUSINESS.NAME, BO.COMPANY) as COMPANY,
case
when BO.SPOUSEIND = 0 then C.NAME
when BO.SPOUSEIND = 1 then coalesce(SPOUSE.NAME, C.NAME)
end MATCHNAME,
null LASTNAME,
null RELATIONSHIPNAME,
BO.TITLE TITLE,
BUSINESS.LOOKUPID,
'BO' as TYPE,
BUSINESS.ID LINKEDCONSTITUENTID,
case
when RELTOLINK.ID is null then 0
else 1
end RELATIONSHIPEXISTS,
case
when BO.CONFIRMED = 1 then '5'
when BO.REJECTED = 1 then '0'
else
isnull(coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
end as CONFIDENCE
from
dbo.WPBUSINESSOWNERSHIP BO
left outer join
dbo.MATCHCODE MC on BO.MC = MC.MATCHCODE
left outer join
dbo.WEALTHSOURCE WS on BO.SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and WS.ID = CR.WEALTHSOURCEID
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
left join dbo.RELATIONSHIP RELTOLINK
on RELTOLINK.RELATIONSHIPCONSTITUENTID = C.ID and RELTOLINK.RECIPROCALCONSTITUENTID = BUSINESS.ID
where
BO.WEALTHID = @WEALTHID and
BO.CONFIRMED = 1 and
(NULLIF(@SOURCE,'') is null or BO.SOURCE + ' (Businesses)' = @SOURCE) and
(NULLIF(@COMPANY,'') is null or coalesce(BUSINESS.NAME, BO.COMPANY) like '%' + @COMPANY + '%') 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 = @WEALTHID and
BO_EXISTS.ID = BO.ID and
((@NAME is null or @NAME = '') or coalesce(IND.NAME, BORI.FULLNAME) like '%' + @NAME + '%') )
union all
select distinct
BORI.ID,
BO.ID PARENTID,
'881824cc-5f88-4425-8f88-538927541545' VIEWFORMID,
coalesce(cast(BO.ID as nvarchar(36)), '') + coalesce(cast(BORI.ID as nvarchar(36)), '') MERGEID,
null COMPANY,
null MATCHNAME,
BORI.LASTNAME,
coalesce(IND.NAME, BORI.FULLNAME) RELATIONSHIPNAME,
BORI.TITLE,
IND.LOOKUPID,
'BO_IND' as TYPE,
IND.ID as LINKEDCONSTITUENTID,
case
when RELTOLINK.ID is null then 0
else 1
end,
case
when BO.CONFIRMED = 1 then '5'
when BO.REJECTED = 1 then '0'
else
isnull(coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
end as CONFIDENCE
from
dbo.WPBUSINESSOWNERSHIP BO
left outer join
dbo.MATCHCODE MC on BO.MC = MC.MATCHCODE
left outer join
dbo.WEALTHSOURCE WS on BO.SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and WS.ID = CR.WEALTHSOURCEID
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.CONSTITUENT BUSINESS
on BUSINESS.ID = BOR.CONSTITUENTID
left join dbo.RELATIONSHIP RELTOLINK
on RELTOLINK.RELATIONSHIPCONSTITUENTID = C.ID and RELTOLINK.RECIPROCALCONSTITUENTID = IND.ID
where
BORI.ID is not null and
BO.WEALTHID = @WEALTHID and
BO.CONFIRMED = 1 and
(NULLIF(@SOURCE,'') is null or BO.SOURCE + ' (Businesses)' = @SOURCE) and
((@NAME is null or @NAME = '') or coalesce(IND.NAME, BORI.FULLNAME) like '%' + @NAME + '%') and
(NULLIF(@COMPANY,'') is null or coalesce(BUSINESS.NAME, BO.COMPANY) like '%' + @COMPANY + '%')
union all
--Nonprofit Affiliations
select distinct
NPA.ID,
null PARENTID,
'4bf99f46-c8bb-42e6-96fa-f28abf3d3137' VIEWFORMID,
cast(NPA.ID as nvarchar(36)) MERGEID,
coalesce(ORG.NAME, NPA.DN_ORGANIZATION) as COMPANY,
case
when NPA.SPOUSEFLAG = 0 then C.NAME
when NPA.SPOUSEFLAG = 1 then coalesce(SPOUSE.NAME, C.NAME)
end MATCHNAME,
null LASTNAME,
null RELATIONSHIPNAME,
NPA.TITLE TITLE,
ORG.LOOKUPID,
'NPA' as TYPE,
ORG.ID as LINKEDCONSTITUENTID,
case
when RELTOLINK.ID is null then 0
else 1
end,
case
when NPA.CONFIRMED = 1 then '5'
when NPA.REJECTED = 1 then '0'
else
isnull(coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
end as CONFIDENCE
from
dbo.WPNONPROFITAFFILIATION NPA
left outer join
dbo.MATCHCODE MC on NPA.MC = MC.MATCHCODE
left outer join
dbo.WEALTHSOURCE WS on NPA.SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and WS.ID = CR.WEALTHSOURCEID
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
left join dbo.WPRELATIONSHIP_NPA RELNPA
on RELNPA.ID = NPA.WPRELATIONSHIP_NPA_ID
left join dbo.CONSTITUENT ORG
on ORG.ID = RELNPA.CONSTITUENTID
left join dbo.RELATIONSHIP RELTOLINK
on RELTOLINK.RELATIONSHIPCONSTITUENTID = C.ID and RELTOLINK.RECIPROCALCONSTITUENTID = ORG.ID
where
(NULLIF(@SOURCE,'') is null or NPA.SOURCE + ' (Nonprofit affiliation)' = @SOURCE) and
(NULLIF(@COMPANY,'') is null or coalesce(ORG.NAME, NPA.DN_ORGANIZATION) like '%' + @COMPANY + '%') and
NPA.CONFIRMED = 1 and
NPA.WEALTHID = @WEALTHID 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_EXISTS.ID = NPA.ID and
((@NAME is null or @NAME = '') or coalesce(IND.NAME, NPARI.FULLNAME) like '%' + @NAME + '%') )
union all
select distinct
NPARI.ID,
NPA.ID PARENTID,
'4bf99f46-c8bb-42e6-96fa-f28abf3d3137' VIEWFORMID,
coalesce(cast(NPA.ID as nvarchar(36)), '') + coalesce(cast(NPARI.ID as nvarchar(36)), '') MERGEID,
null COMPANY,
null MATCHNAME,
NPARI.LASTNAME,
coalesce(IND.NAME, NPARI.FULLNAME) RELATIONSHIPNAME,
NPARI.TITLE,
IND.LOOKUPID,
'NPA_IND' as TYPE,
IND.ID as LINKEDCONSTITUENTID,
case
when RELTOLINK.ID is null then 0
else 1
end,
case
when NPA.CONFIRMED = 1 then '5'
when NPA.REJECTED = 1 then '0'
else
isnull(coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
end as CONFIDENCE
from
dbo.WPNONPROFITAFFILIATION NPA
left outer join
dbo.MATCHCODE MC on NPA.MC = MC.MATCHCODE
left outer join
dbo.WEALTHSOURCE WS on NPA.SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and WS.ID = CR.WEALTHSOURCEID
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
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
left join dbo.CONSTITUENT IND
on NPARI.CONSTITUENTID = IND.ID
left join dbo.RELATIONSHIP RELTOLINK
on RELTOLINK.RELATIONSHIPCONSTITUENTID = C.ID and RELTOLINK.RECIPROCALCONSTITUENTID = IND.ID
left join dbo.CONSTITUENT ORG
on ORG.ID = NPAR.CONSTITUENTID
where
NPARI.ID is not null and
NPA.CONFIRMED = 1 and
NPA.WEALTHID = @WEALTHID and
(NULLIF(@SOURCE,'') is null or NPA.SOURCE + ' (Nonprofit affiliation)' = @SOURCE) and
((@NAME is null or @NAME = '') or coalesce(IND.NAME, NPARI.FULLNAME) like '%' + @NAME + '%') and
(NULLIF(@COMPANY,'') is null or coalesce(ORG.NAME, NPA.DN_ORGANIZATION) like '%' + @COMPANY + '%')
union all
--Private Foundations
select distinct
PF.ID,
null PARENTID,
'2b627bda-760a-4842-9468-2cc4c26ee74c' VIEWFORMID,
cast(PF.ID as nvarchar(36)) MERGEID,
coalesce(ORG.NAME, COMPANY),
case
when PF.SPOUSEFLAG = 0 then C.NAME
when PF.SPOUSEFLAG = 1 then coalesce(SPOUSE.NAME, C.NAME)
end MATCHNAME,
null LASTNAME,
null RELATIONSHIPNAME,
PF.TITLE TITLE,
ORG.LOOKUPID,
'PF' as TYPE,
ORG.ID as LINKEDCONSTITUENTID,
case
when RELTOLINK.ID is null then 0
else 1
end,
case
when PF.CONFIRMED = 1 then '5'
when PF.REJECTED = 1 then '0'
else
isnull(coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
end as CONFIDENCE
from
dbo.WPPRIVATEFOUNDATION PF
left outer join
dbo.MATCHCODE MC on PF.MC = MC.MATCHCODE
left outer join
dbo.WEALTHSOURCE WS on PF.SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and WS.ID = CR.WEALTHSOURCEID
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
left join dbo.WPRELATIONSHIP_PF RELPF
on RELPF.ID = PF.WPRELATIONSHIP_PF_ID
left join dbo.CONSTITUENT ORG
on ORG.ID = RELPF.CONSTITUENTID
left join dbo.RELATIONSHIP RELTOLINK
on RELTOLINK.RELATIONSHIPCONSTITUENTID = C.ID and RELTOLINK.RECIPROCALCONSTITUENTID = ORG.ID
where
(NULLIF(@SOURCE,'') is null or PF.SOURCE + ' (Foundation affiliation)' = @SOURCE) and
(NULLIF(@COMPANY,'') is null or coalesce(ORG.NAME, COMPANY) like '%' + @COMPANY + '%') and
PF.CONFIRMED = 1 and
PF.WEALTHID = @WEALTHID 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_EXISTS.ID = PF.ID and
((@NAME is null or @NAME = '') or coalesce(IND.NAME, PFRI.FULLNAME) like '%' + @NAME + '%') )
union all
select distinct
PFRI.ID,
PF.ID PARENTID,
'2b627bda-760a-4842-9468-2cc4c26ee74c' VIEWFORMID,
coalesce(cast(PF.ID as nvarchar(36)), '') + coalesce(cast(PFRI.ID as nvarchar(36)), '') MERGEID,
null COMPANY,
null MATCHNAME,
PFRI.LASTNAME,
coalesce(IND.NAME, PFRI.FULLNAME) RELATIONSHIPNAME,
PFRI.TITLE,
IND.LOOKUPID,
'PF_IND' as TYPE,
IND.ID as LINKEDCONSTITUENTID,
case
when RELTOLINK.ID is null then 0
else 1
end,
case
when PF.CONFIRMED = 1 then '5'
when PF.REJECTED = 1 then '0'
else
isnull(coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE),(select top(1) MANUALCONFIDENCE from dbo.WEALTHPOINTCONFIGURATION))
end as CONFIDENCE
from
dbo.WPPRIVATEFOUNDATION PF
left outer join
dbo.MATCHCODE MC on PF.MC = MC.MATCHCODE
left outer join
dbo.WEALTHSOURCE WS on PF.SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and WS.ID = CR.WEALTHSOURCEID
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
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
left join dbo.CONSTITUENT IND
on PFRI.CONSTITUENTID = IND.ID
left join dbo.RELATIONSHIP RELTOLINK
on RELTOLINK.RELATIONSHIPCONSTITUENTID = C.ID and RELTOLINK.RECIPROCALCONSTITUENTID = IND.ID
left join dbo.CONSTITUENT ORG
on ORG.ID = PFR.CONSTITUENTID
where
PFRI.ID is not null and
PF.WEALTHID = @WEALTHID and
PF.CONFIRMED = 1 and
(NULLIF(@SOURCE,'') is null or PF.SOURCE + ' (Foundation affiliation)' = @SOURCE) and
((@NAME is null or @NAME = '') or coalesce(IND.NAME, PFRI.FULLNAME) like '%' + @NAME + '%') and
(NULLIF(@COMPANY,'') is null or coalesce(ORG.NAME, COMPANY) like '%' + @COMPANY + '%')
)
insert into @OUTPUT
select
E.ID,
E.PARENTID,
E.VIEWFORMID,
E.MERGEID,
E.COMPANY,
E.MATCHNAME,
E.LASTNAME,
E.RELATIONSHIPNAME,
E.TITLE,
E.LOOKUPID,
E.[TYPE],
E.LINKEDCONSTITUENTID,
E.RELATIONSHIPEXISTS,
E.CONFIDENCE
from
EXTENDEDRELATIONSHIPS_CTE E with (nolock)
order by COMPANY, LASTNAME, RELATIONSHIPNAME asc
return;
end