USP_SIMPLEDATALIST_PLANNEDGIFTBENEFICIARYCANDIDATE

                CREATE procedure USP_SIMPLEDATALIST_PLANNEDGIFTBENEFICIARYCANDIDATE
                (
                    @CONSTITUENTID uniqueidentifier
                ) 
                as
                    set nocount on;

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

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

                  declare @BENEFICIARIES table
                  (
                    CONSTITUENTID uniqueidentifier,
                    DESCRIPTION nvarchar(100),
                    ISDONOR bit,
                    PRIMARYCONSTITUENT bit,
                    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 @BENEFICIARIES (CONSTITUENTID, DESCRIPTION, PRIMARYCONSTITUENT, KEYNAME, FIRSTNAME, ISDONOR)
                      select
                        C.ID,
                        case 
                          when GM.ISPRIMARY = 1 then NF.NAME + ' - Member (Primary contact)'
                          else NF.NAME + ' - Member'
                        end,
                        GM.ISPRIMARY,
                        C.KEYNAME,
                        C.FIRSTNAME,
                        0
                      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 @BENEFICIARIES (CONSTITUENTID, DESCRIPTION, PRIMARYCONSTITUENT, KEYNAME, FIRSTNAME, ISDONOR)
                  select
                    C.ID,
                    (NF.NAME + ' - ' + (select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID=R.RECIPROCALTYPECODEID)),
                    ISPRIMARYCONTACT,
                    C.KEYNAME,
                    C.FIRSTNAME,
                    0
                  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 top(1) 1 from @BENEFICIARIES where CONSTITUENTID = C.ID)

                  -- donor, if not a household                  

                  declare @ISHOUSEHOLD bit;
                  set @ISHOUSEHOLD = 0;
                  select @ISHOUSEHOLD = case when GROUPTYPECODE = 0 then '1' else '0' end from dbo.GROUPDATA where GROUPDATA.ID = @CONSTITUENTID;

                  insert into @BENEFICIARIES (CONSTITUENTID, DESCRIPTION, PRIMARYCONSTITUENT, KEYNAME, FIRSTNAME, ISDONOR)
                  select
                    C.ID,
                    NF.NAME + ' - Donor',
                    0,
                    C.KEYNAME,
                    C.FIRSTNAME,
                    1
                  from
                    dbo.CONSTITUENT C
                  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                  where
                    @ISHOUSEHOLD = 0
                  and
                    C.ID = @CONSTITUENTID        

                  select
                    CONSTITUENTID as 'VALUE',
                    DESCRIPTION as 'LABEL'
                  from
                    @BENEFICIARIES
                  order by
                    ISDONOR desc,
                    PRIMARYCONSTITUENT desc,
                    KEYNAME,
                    FIRSTNAME