USP_DATALIST_RELATIONSHIPTREECONSTITUENTGROUPMEMBER

                create procedure dbo.USP_DATALIST_RELATIONSHIPTREECONSTITUENTGROUPMEMBER
                (
                    @CONSTITUENTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier
                 ) as
                    set nocount on;

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

                    declare @PRIMARYMEMBERID uniqueidentifier;
                    select @PRIMARYMEMBERID = GM.MEMBERID from dbo.GROUPMEMBER GM where GM.GROUPID = @CONSTITUENTID and GM.ISPRIMARY = 1;

                    select
                        null,
                        GM.MEMBERID,
                        case
                            when GM.ISPRIMARY = 1 then 'Primary Group Contact'
                            else (    select dbo.UDA_BUILDLIST(distinct RTC.DESCRIPTION)
                                    from dbo.RELATIONSHIP R
                                    join dbo.RELATIONSHIPTYPECODE RTC
                                    on R.RELATIONSHIPTYPECODEID = RTC.ID
                                    where C.ID = R.RELATIONSHIPCONSTITUENTID
                                    and R.RECIPROCALCONSTITUENTID = @PRIMARYMEMBERID
                                    )
                        end RELATIONSHIP,
                        (
                            rtrim(C.NAME + ' ' +
                                coalesce(
                                    replace(
                                        stuff(
                                            (select '' + STATUS from
                                                (select ',(Deceased)' STATUS
                                                 from dbo.DECEASEDCONSTITUENT DC
                                                 where DC.ID = C.ID
                                                 union all
                                                 select ',(Inactive)' STATUS
                                                 from dbo.CONSTITUENT
                                                 where CONSTITUENT.ID = C.ID
                                                 and CONSTITUENT.ISINACTIVE=1
                                                ) as SUBQ for xml path(''))
                                        ,1,1,'')
                                    , '),(', ', ')
                                ,'')
                            )
                        ) as NAME,
                        C.ISINACTIVE,
                        (select count(ID) from dbo.RELATIONSHIP R where R.RELATIONSHIPCONSTITUENTID = GM.MEMBERID) COUNT,
                        0 ISSPOUSE,
                        C.ISORGANIZATION,
                        dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID,'9297f3a4-c0c5-4947-9cb2-f1d9eb15b155', GM.MEMBERID) LOADDATALIST,
                        dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID,'0C836902-A398-47a0-91EB-8B66E434148E', GM.MEMBERID) LOADPAGE,
                        C.ISGROUP,
                        case when GD.GROUPTYPECODE = 0 then 1 else 0 end ISHOUSEHOLD
                    from
                        dbo.GROUPMEMBER GM
                    inner join 
                        dbo.CONSTITUENT C on GM.MEMBERID = C.ID
                    left outer join 
                        dbo.GROUPDATA GD on GD.ID = GM.MEMBERID
                    left outer join 
                        dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
                    where
                        GM.GROUPID = @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))