USP_DATALIST_RELATIONSHIPS_INDTOGROUP

                CREATE procedure dbo.USP_DATALIST_RELATIONSHIPS_INDTOGROUP
                (
                    @CONSTITUENTID uniqueidentifier,
                    @ONLYCURRENT bit = 0,
                    @RECIPROCALTYPECODEID uniqueidentifier = null
                ) as
                    set nocount on;

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

                    declare @CONSTITUENTSEARCHTYPE bit;
                    select @CONSTITUENTSEARCHTYPE = ~CONSTITUENT.ISGROUP from dbo.CONSTITUENT where CONSTITUENT.ID = @CONSTITUENTID;

                    select
                        R.ID,
                        R.RECIPROCALCONSTITUENTID,
                        (select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID=R.RECIPROCALTYPECODEID) as 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,
                        R.STARTDATE,
                        R.ENDDATE,
                        C.ISGROUP,
                        case when GD.GROUPTYPECODE = 0 then 1 else 0 end
                    from
                        dbo.RELATIONSHIP R
                    inner join
                        dbo.CONSTITUENT C on R.RECIPROCALCONSTITUENTID = C.ID
                    left outer join
                        dbo.GROUPDATA GD on GD.ID = C.ID
                    where
                        R.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                    and
                        C.ISGROUP = @CONSTITUENTSEARCHTYPE
                    and
                        C.ISORGANIZATION = 0
                        and (@CURRENTDATE is null
                                or R.STARTDATE is null
                                or R.STARTDATE <= @CURRENTDATE
                            )
                        and (@CURRENTDATE is null
                                or R.ENDDATE is null
                                or R.ENDDATE >= @CURRENTDATE
                            )
                        and (@RECIPROCALTYPECODEID is null
                                or R.RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID
                            )
                    order by
                        RELATIONSHIP, C.KEYNAME, C.NAME