USP_SIMPLEDATALIST_CONSTITUENTOPPORTUNITY

        CREATE procedure dbo.USP_SIMPLEDATALIST_CONSTITUENTOPPORTUNITY
        (
          @CONSTITUENTID uniqueidentifier
        )
        as
            set nocount on;

            declare @CURRENTDATE datetime;
            set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

            -- the record if it's a household, or the household an individual belongs to    

            declare @HOUSEHOLDID uniqueidentifier; 
            if dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1
                set @HOUSEHOLDID = @CONSTITUENTID;
            else
                select
                    @HOUSEHOLDID = GM.GROUPID
                from
                    dbo.GROUPMEMBER GM
                left outer join
                    dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                left outer join
                    dbo.GROUPDATA GD on GD.ID = GM.GROUPID
                where
                    GM.MEMBERID = @CONSTITUENTID
                and
                    GD.GROUPTYPECODE = 0
                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                    or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE));

            with HOUSEHOLDANDMEMBERS(ID) as
            (
                select
                    GM.MEMBERID as ID
                from
                    dbo.GROUPMEMBER GM
                left outer join
                    dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                where
                    GM.GROUPID = @HOUSEHOLDID
                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                    or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
                union
                select @HOUSEHOLDID
                union
                select @CONSTITUENTID
            )
            select
                OPPORTUNITY.ID as 'VALUE',
                dbo.UFN_OPPORTUNITY_GETLONGDESCRIPTION(OPPORTUNITY.ID) as 'LABEL'

            from
                dbo.OPPORTUNITY 
                inner join dbo.PROSPECTPLAN on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
            where 
                PROSPECTPLAN.PROSPECTID in (select HOUSEHOLDANDMEMBERS.ID from HOUSEHOLDANDMEMBERS) and
                OPPORTUNITY.STATUSCODE in (1,2,3)