UFN_CONSTITUENT_STUDENTRELATIONCONSTITUENCIES

            CREATE function dbo.UFN_CONSTITUENT_STUDENTRELATIONCONSTITUENCIES()
            returns table
            as return (
                select     
                    CONSTITUENT.ID as CONSTITUENTID,
                    RELATIONSHIP.RECIPROCALCONSTITUENTID as RECIPROCALCONSTITUENTID, --student

                    RELATIONSHIP.RELATIONSHIPTYPECODEID,
                    RELATIONSHIP.RECIPROCALTYPECODEID,
                    STUDENTRELATIONCONSTITUENCYSETTINGS.CONSTITUENCYCODEID,
                    CONSTITUENCYCODE.DESCRIPTION as CONSTITUENCYCODEDESCRIPTION,
                    STUDENTRELATIONCONSTITUENCYSETTINGS.RELATEDTOCODE,
                    STUDENTRELATIONCONSTITUENCYSETTINGS.ID as STUDENTRELATIONCONSTITUENCYSETTINGSID,
                    STUDENTRELATIONCONSTITUENCYTYPES.ID as STUDENTRELATIONCONSTITUENCYTYPESID,
                    CONSTITUENT.ISCONSTITUENT,
                    CONSTITUENCYCODE.SEQUENCE as CONSTITUENCYCODESEQUENCE
                from
                    dbo.CONSTITUENT
                    inner join dbo.RELATIONSHIP on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                    inner join dbo.STUDENTRELATIONCONSTITUENCYTYPES on RELATIONSHIP.RELATIONSHIPTYPECODEID = STUDENTRELATIONCONSTITUENCYTYPES.RELATIONSHIPTYPECODEID
                    inner join dbo.STUDENTRELATIONCONSTITUENCYSETTINGS on STUDENTRELATIONCONSTITUENCYTYPES.STUDENTRELATIONCONSTITUENCYSETTINGSID = STUDENTRELATIONCONSTITUENCYSETTINGS.ID
                    inner join dbo.CONSTITUENCYCODE on STUDENTRELATIONCONSTITUENCYSETTINGS.CONSTITUENCYCODEID = CONSTITUENCYCODE.ID
                where (
                        (
                            dbo.UFN_CONSTITUENT_ISSTUDENT(RELATIONSHIP.RECIPROCALCONSTITUENTID) = 1
                            and STUDENTRELATIONCONSTITUENCYSETTINGS.RELATEDTOCODE = 0
                            and (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '4DB8F4FC-BC43-421D-B592-69BEF109B5FC') = 1
                        ) --student

                        or (
                            dbo.UFN_CONSTITUENT_ISALUMNUS(RELATIONSHIP.RECIPROCALCONSTITUENTID) = 1
                            and STUDENTRELATIONCONSTITUENCYSETTINGS.RELATEDTOCODE = 1
                            and (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '46EC3424-BA54-4431-A7DC-C6CEBB3B4592') = 1
                        ) --alumni

                    )

            )