USP_DATALIST_RELATIONSHIPS_TOSTUDENT

                CREATE procedure dbo.USP_DATALIST_RELATIONSHIPS_TOSTUDENT 
                (
                    @CONSTITUENTID uniqueidentifier,
                    @ONLYCURRENT bit = 0,
                    @RECIPROCALTYPECODEID uniqueidentifier = null,
                    @ONLYCONTACTS bit = 0,
                    @SHOWSTUDENTS bit = 1,                    
                    @SHOWINDIVIDUALS bit = 1,
                    @SHOWFACULTY bit = 1,
                    @SHOWORGANIZATIONS bit = 1,
                    @SHOWGROUPS bit = 0,
                    @SHOWHOUSEHOLDS bit = 0,
                    @ONLYEMERGENCYCONTACTS bit = 0
                )
                as
                set nocount on;

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

                select    
                    RELATIONSHIP.ID,
                    RELATIONSHIP.RECIPROCALCONSTITUENTID,
                    (
                        rtrim(CONSTITUENT.NAME + ' ' +
                            coalesce(
                                replace(
                                    stuff(
                                        (select '' + STATUS from
                                        (select ',(Deceased)' STATUS
                                            from dbo.DECEASEDCONSTITUENT DC
                                            where DC.ID = CONSTITUENT.ID
                                        union all
                                        select ',(Inactive)' STATUS
                                            from dbo.CONSTITUENT C
                                            where C.ID = CONSTITUENT.ID
                                            and C.ISINACTIVE=1
                                    ) as SUBQ for xml path(''))
                                    ,1,1,'')
                                , '),(', ', ')
                            ,'')
                        )
                    ) as NAME,
                    case
                        when CONSTITUENT.ISORGANIZATION = 1 then 1
                        when CONSTITUENT.ISGROUP = 1 then 
                            case
                                when GROUPDATA.GROUPTYPECODE = 0 then 2
                            else 3
                            end
                        when dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTITUENT.ID) = 1 then 4
                        when dbo.UFN_CONSTITUENT_ISFACULTY(CONSTITUENT.ID) = 1 then 5
                        else 0
                    end RECIPROCALCONSTITUENTTYPE,
                    case
                        when CONSTITUENT.ISORGANIZATION = 1 then 'Organization'
                        when CONSTITUENT.ISGROUP = 1 then 
                            case
                                when GROUPDATA.GROUPTYPECODE = 0 then 'Household'
                                else 'Group'
                            end
                        when dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTITUENT.ID) = 1 then 'Student'    
                        when dbo.UFN_CONSTITUENT_ISFACULTY(CONSTITUENT.ID) = 1 then 'Faculty'                        
                        else 'Individual'
                    end RELATIONSHIPTYPE,
                    case
                        when RELATIONSHIP.ISSPOUSE = 1 then 'Spouse'
                        when RELATIONSHIP.ISPRIMARYCONTACT = 1 then 'Primary contact'
                        when RELATIONSHIP.ISCONTACT = 1 then 'Contact'
                        when (select RECIPROCAL.ISEMERGENCYCONTACT 
                            from dbo.RELATIONSHIP RECIPROCAL 
                            where RECIPROCAL.RELATIONSHIPCONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID and 
                            RECIPROCAL.RECIPROCALCONSTITUENTID=@CONSTITUENTID and 
                            RECIPROCAL.RELATIONSHIPTYPECODEID = RELATIONSHIP.RECIPROCALTYPECODEID and
                            RECIPROCAL.RECIPROCALTYPECODEID = RELATIONSHIP.RELATIONSHIPTYPECODEID)= 1 then 'Emergency contact'
                        else ''
                    end TYPE,
                    (select DESCRIPTION from dbo.RELATIONSHIPTYPECODE where ID=RELATIONSHIP.RECIPROCALTYPECODEID) RELATIONSHIP,
                    RELATIONSHIP.STARTDATE,
                    RELATIONSHIP.ENDDATE,
                    RELATIONSHIP.ISSPOUSE,
                    RELATIONSHIP.ISPRIMARYCONTACT,
                    RELATIONSHIP.ISCONTACT
                from 
                    dbo.RELATIONSHIP
                    inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
                    left join dbo.GROUPDATA on CONSTITUENT.ID = GROUPDATA.ID
                where 
                    RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                    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)
                    and 
                        (@ONLYCONTACTS = 0
                        or RELATIONSHIP.ISCONTACT = @ONLYCONTACTS)
                    and (@SHOWINDIVIDUALS = 1 or CONSTITUENT.ISORGANIZATION = 1 or CONSTITUENT.ISGROUP = 1 or
                            (dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTITUENT.ID) = 1) or
                            (dbo.UFN_CONSTITUENT_ISFACULTY(CONSTITUENT.ID) = 1))
                    and (@SHOWORGANIZATIONS = 1 or CONSTITUENT.ISORGANIZATION = 0)
                    and (@SHOWGROUPS = 1 or CONSTITUENT.ISGROUP = 0 or GROUPDATA.GROUPTYPECODE = 0)
                    and (@SHOWHOUSEHOLDS = 1 or CONSTITUENT.ISGROUP = 0 or GROUPDATA.GROUPTYPECODE = 1)
                    and (@SHOWSTUDENTS = 1 or (dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTITUENT.ID) = 0))
                    and (@SHOWFACULTY = 1 or (dbo.UFN_CONSTITUENT_ISFACULTY(CONSTITUENT.ID) = 0))
                    and (@ONLYEMERGENCYCONTACTS = 0 
                        or (select RECIPROCAL.ISEMERGENCYCONTACT 
                        from dbo.RELATIONSHIP RECIPROCAL 
                        where RECIPROCAL.RELATIONSHIPCONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID and 
                            RECIPROCAL.RECIPROCALCONSTITUENTID = @CONSTITUENTID and 
                            RECIPROCAL.RELATIONSHIPTYPECODEID = RELATIONSHIP.RECIPROCALTYPECODEID and
                            RECIPROCAL.RECIPROCALTYPECODEID = RELATIONSHIP.RELATIONSHIPTYPECODEID) = 1)
                order by
                    RELATIONSHIPTYPE, 
                    CONSTITUENT.KEYNAME, 
                    CONSTITUENT.NAME;