USP_DATALIST_RELATIONSHIPS_ORGTOGROUP

                CREATE procedure dbo.USP_DATALIST_RELATIONSHIPS_ORGTOGROUP
          (
            @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 @ISGROUP bit;
            declare @ISORGANIZATION bit;

            select 
              @ISGROUP = C.ISGROUP,
              @ISORGANIZATION = C.ISORGANIZATION
            from 
              dbo.CONSTITUENT C
            where 
              C.ID = @CONSTITUENTID;

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