USP_WEALTHPOINT_GETRESEARCHGROUPHASHDATAFORSEARCH

            create procedure dbo.USP_WEALTHPOINT_GETRESEARCHGROUPHASHDATAFORSEARCH(
                @ID uniqueidentifier
            ) as begin
                set nocount on;

                select
                    'LS' as SOURCE,
                    C.ID as CID,
                    WP.FULLHASH as FULLHASH,
                    WP.MC
                from
                    dbo.RESEARCHGROUP RG
                    left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                    left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                    left outer join dbo.WPAFFLUENCEINDICATOR WP on WP.WEALTHID = C.ID
                where
                    RG.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.RESEARCHGROUP RG
                    left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                    left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                    left outer join dbo.WPBIOGRAPHICAL WP on WP.WEALTHID = C.ID
                where
                    RG.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.RESEARCHGROUP RG
                    left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                    left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                    left outer join dbo.WPBUSINESSOWNERSHIP WP on WP.WEALTHID = C.ID
                where
                    RG.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.RESEARCHGROUP RG
                    left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                    left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                    left outer join dbo.WPINCOMECOMPENSATION WP on WP.WEALTHID = C.ID
                where
                    RG.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.RESEARCHGROUP RG
                    left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                    left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                    left outer join dbo.WPNONPROFITAFFILIATION WP on WP.WEALTHID = C.ID
                where
                    RG.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.RESEARCHGROUP RG
                    left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                    left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                    left outer join dbo.WPPHILANTHROPICGIFT WP on WP.WEALTHID = C.ID
                where
                    RG.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.RESEARCHGROUP RG
                    left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                    left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                    left outer join dbo.WPPOLITICALDONATION WP on WP.WEALTHID = C.ID
                where
                    RG.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.RESEARCHGROUP RG
                    left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                    left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                    left outer join dbo.WPPRIVATEFOUNDATION WP on WP.WEALTHID = C.ID
                where
                    RG.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.RESEARCHGROUP RG
                    left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                    left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                    left outer join dbo.WPREALESTATE WP on WP.WEALTHID = C.ID
                where
                    RG.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.RESEARCHGROUP RG
                    left outer join dbo.RESEARCHGROUPMEMBER RGM on RGM.RESEARCHGROUPID = RG.ID
                    left outer join dbo.CONSTITUENT C on C.ID = RGM.CONSTITUENTID
                    left outer join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                    left outer join dbo.WPSECURITIES WP on WP.WEALTHID = C.ID
                where
                    RG.ID = @ID and
                    C.ISORGANIZATION = 0 and
                    DWU.ID is null and
                    len(WP.FULLHASH) > 0;

                return 0;

            end;