USP_DATALIST_RELATIONSHIPS_GROUPTOGROUP

List of relationships between a constituent group and other constituent groups.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@ONLYCURRENT	bit	IN	Only display current relationships
@RECIPROCALTYPECODEID	uniqueidentifier	IN	Relationship type
Definition

Copy


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

            select
                R.ID,
                R.RECIPROCALCONSTITUENTID,
                (select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID = R.RECIPROCALTYPECODEID) as RELATIONSHIP,
                  (
                    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 = 1
                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