UFN_MEMBERSHIP_GETMEMBERSWITHINFORMATION

            CREATE function dbo.UFN_MEMBERSHIP_GETMEMBERSWITHINFORMATION
            (
                @MEMBERSHIPID uniqueidentifier,
                @MEMBERSHIPLEVELID uniqueidentifier,
                @SOURCECONSTITUENTID uniqueidentifier
            )
            returns table as

            return
            (
                select
                    C.ID as ID,
                    NF.NAME as NAME,
                    (
                        select top 1
                            RELATIONSHIPTYPECODE.DESCRIPTION
                        from
                            dbo.RELATIONSHIP
                            inner join dbo.RELATIONSHIPTYPECODE on RELATIONSHIP.RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
                        where
                            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = C.ID
                            and RELATIONSHIP.RECIPROCALCONSTITUENTID = @SOURCECONSTITUENTID
                    ) as RELATIONSHIP,
                    C.LOOKUPID as LOOKUPID,
                    case
                        when exists
                        (
                            select top 1
                                1
                            from
                                dbo.RELATIONSHIP
                                inner join dbo.RELATIONSHIPTYPECODE on RELATIONSHIP.RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
                            where
                                RELATIONSHIP.RELATIONSHIPCONSTITUENTID = C.ID
                                and RELATIONSHIP.RECIPROCALCONSTITUENTID = @SOURCECONSTITUENTID
                                and (RELATIONSHIPTYPECODE.ID = '1A08E4A4-4929-4B4C-B3E9-826136A63314' or RELATIONSHIPTYPECODE.ID = 'C7D1CA96-7135-42F7-AF3A-D00BDF1B6C3E')
                        ) then 1
                        else 0
                    end as ISCHILD,
                    C.BIRTHDATE as BIRTHDATE,
                    C.AGE as AGE,
                    ML.MEMBERSALLOWED as MEMBERS,
                    ML.CHILDRENALLOWED as CHILDREN
                from
                    dbo.MEMBER M
                    inner join dbo.CONSTITUENT C on M.CONSTITUENTID = C.ID
                    inner join dbo.MEMBERSHIP MS on MS.ID = M.MEMBERSHIPID
                    inner join dbo.MEMBERSHIPLEVEL ML on MS.MEMBERSHIPLEVELID = ML.ID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                where
                    MEMBERSHIPID = @MEMBERSHIPID and
                    C.ID <> @SOURCECONSTITUENTID and
                    ISDROPPED = 0
            )