USP_SIMPLEDATALIST_STEPPARTICIPANTCANDIDATE

                CREATE procedure dbo.USP_SIMPLEDATALIST_STEPPARTICIPANTCANDIDATE
                (
                    @PROSPECTPLANID uniqueidentifier,
                    @CONSTITUENTID uniqueidentifier = null
                )
                as
                    set nocount on;

                    -- Create a table variable to hold the participants.  This is used rather than 

                    -- a union to prevent the same constituent from being returned multiple times.

                    declare @participants table
                    (
                    CONSTITUENTID uniqueidentifier,
                    DESCRIPTION nvarchar(100),
                    KEYNAME nvarchar(100),
                    FIRSTNAME nvarchar(100)
                    );

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

                    if @CONSTITUENTID is null
                        select @CONSTITUENTID = PROSPECTID from dbo.PROSPECTPLAN where ID = @PROSPECTPLANID;

                    -- participants for the plan

                    insert into @participants (CONSTITUENTID, DESCRIPTION, KEYNAME, FIRSTNAME)
                    select
                    C.ID,
                    case 
                        when P.PLANPARTICIPANTROLECODEID is not null then NF.NAME + ' - ' + (select DESCRIPTION from dbo.PLANPARTICIPANTROLECODE where ID = P.PLANPARTICIPANTROLECODEID)
                        else NF.NAME + ' - Plan participant'
                    end,
                    C.KEYNAME,
                    C.FIRSTNAME
                    from
                    dbo.PLANPARTICIPANT P
                    inner join
                    dbo.CONSTITUENT C on P.CONSTITUENTID = C.ID
                                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                    where
                    P.PROSPECTPLANID = @PROSPECTPLANID and
                    -- Verify the owner of the plan isn't shown in case they were added as a 

                    -- as a plan participant.

                    C.ID <> @CONSTITUENTID
          option(recompile)

                    -- 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));

                    -- group members

                    insert into @participants (CONSTITUENTID, DESCRIPTION, KEYNAME, FIRSTNAME)
                    select
                    C.ID,
                    case 
                        when GM.ISPRIMARY = 1 then NF.NAME + ' - Member (Primary contact)'
                        else NF.NAME + ' - Member'
                    end,
                    C.KEYNAME,
                    C.FIRSTNAME
                    from
                    dbo.GROUPMEMBER GM
                    inner join
                    dbo.CONSTITUENT C on GM.MEMBERID = C.ID
                                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                    left join 
                    dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                    where
                    GM.GROUPID = @HOUSEHOLDID 
                    and
                    GM.MEMBERID <> @CONSTITUENTID
                    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)) 
                    and
                    not exists (select 1 from @participants where CONSTITUENTID = C.ID)

                    -- relationships for all

                    insert into @participants (CONSTITUENTID, DESCRIPTION, KEYNAME, FIRSTNAME)
                    select
                    C.ID,
                    (NF.NAME + ' - ' + (select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID=R.RECIPROCALTYPECODEID)),
                    C.KEYNAME,
                    C.FIRSTNAME
                    from
                    dbo.RELATIONSHIP R
                    inner join
                    dbo.CONSTITUENT C on R.RECIPROCALCONSTITUENTID = C.ID
                                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                    where
                    R.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                    not exists (select 1 from @participants where CONSTITUENTID = C.ID)

                    select
                    CONSTITUENTID as 'VALUE',
                    DESCRIPTION as 'LABEL'
                    from
                    @participants
                    order by
                    KEYNAME,
                    FIRSTNAME