USP_WEALTHPOINT_GETCONSTITUENTHASHDATAFORSEARCH
Gets WealthPoint hash data for an constituent.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN |
Definition
Copy
create procedure dbo.USP_WEALTHPOINT_GETCONSTITUENTHASHDATAFORSEARCH(
@ID uniqueidentifier
) as begin
set nocount on;
select
'LS' as SOURCE,
C.ID as CID,
WP.FULLHASH as FULLHASH,
WP.MC
from
dbo.CONSTITUENT C
left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
left outer join dbo.WPAFFLUENCEINDICATOR WP on WP.WEALTHID = C.ID
where
C.ID = @ID and
C.ISORGANIZATION = 0 and
DWU.ID is null and
len(WP.FULLHASH) > 0
union all
select
'WW' as SOURCE,
C.ID as CID,
WP.FULLHASH as FULLHASH,
WP.MC
from
dbo.CONSTITUENT C
left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
left outer join dbo.WPBIOGRAPHICAL WP on WP.WEALTHID = C.ID
where
C.ID = @ID and
C.ISORGANIZATION = 0 and
DWU.ID is null and
len(WP.FULLHASH) > 0
union all
select
'DB' as SOURCE,
C.ID as CID,
WP.FULLHASH as FULLHASH,
WP.MC
from
dbo.CONSTITUENT C
left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
left outer join dbo.WPBUSINESSOWNERSHIP WP on WP.WEALTHID = C.ID
where
C.ID = @ID and
C.ISORGANIZATION = 0 and
DWU.ID is null and
len(WP.FULLHASH) > 0
union all
select
'MG' as SOURCE,
C.ID as CID,
WP.FULLHASH as FULLHASH,
WP.MC
from
dbo.CONSTITUENT C
left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
left outer join dbo.WPINCOMECOMPENSATION WP on WP.WEALTHID = C.ID
where
C.ID = @ID and
C.ISORGANIZATION = 0 and
DWU.ID is null and
len(WP.FULLHASH) > 0
union all
select
'GS' as SOURCE,
C.ID as CID,
WP.FULLHASH as FULLHASH,
WP.MC
from
dbo.CONSTITUENT C
left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
left outer join dbo.WPNONPROFITAFFILIATION WP on WP.WEALTHID = C.ID
where
C.ID = @ID and
C.ISORGANIZATION = 0 and
DWU.ID is null and
len(WP.FULLHASH) > 0
union all
select
'WM' as SOURCE,
C.ID as CID,
WP.FULLHASH as FULLHASH,
WP.MC
from
dbo.CONSTITUENT C
left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
left outer join dbo.WPPHILANTHROPICGIFT WP on WP.WEALTHID = C.ID
where
C.ID = @ID and
C.ISORGANIZATION = 0 and
DWU.ID is null and
len(WP.FULLHASH) > 0
union all
select
'FEC' as SOURCE,
C.ID as CID,
WP.FULLHASH as FULLHASH,
WP.MC
from
dbo.CONSTITUENT C
left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
left outer join dbo.WPPOLITICALDONATION WP on WP.WEALTHID = C.ID
where
C.ID = @ID and
C.ISORGANIZATION = 0 and
DWU.ID is null and
len(WP.FULLHASH) > 0
union all
select
'FA' as SOURCE,
C.ID as CID,
WP.FULLHASH as FULLHASH,
WP.MC
from
dbo.CONSTITUENT C
left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
left outer join dbo.WPPRIVATEFOUNDATION WP on WP.WEALTHID = C.ID
where
C.ID = @ID and
C.ISORGANIZATION = 0 and
DWU.ID is null and
len(WP.FULLHASH) > 0
union all
select
'LX' as SOURCE,
C.ID as CID,
WP.FULLHASH as FULLHASH,
WP.MC
from
dbo.CONSTITUENT C
left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
left outer join dbo.WPREALESTATE WP on WP.WEALTHID = C.ID
where
C.ID = @ID and
C.ISORGANIZATION = 0 and
DWU.ID is null and
len(WP.FULLHASH) > 0
union all
select
'VI' as SOURCE,
C.ID as CID,
WP.FULLHASH as FULLHASH,
WP.MC
from
dbo.CONSTITUENT C
left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
left outer join dbo.WPSECURITIES WP on WP.WEALTHID = C.ID
where
C.ID = @ID and
C.ISORGANIZATION = 0 and
DWU.ID is null and
len(WP.FULLHASH) > 0;
return 0;
end;