USP_SIMPLEDATALIST_PLANPARTICIPANTCANDIDATE

                CREATE procedure dbo.USP_SIMPLEDATALIST_PLANPARTICIPANTCANDIDATE
                (
                  @CONSTITUENTID uniqueidentifier
                )
                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());

                    -- 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
                    left join 
                        dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                    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))

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