UFN_MEMBERSHIP_GETMEMBERSWITHINFORMATION_2

            CREATE function dbo.UFN_MEMBERSHIP_GETMEMBERSWITHINFORMATION_2
            (
                @MEMBERSHIPID uniqueidentifier,
                @MEMBERSHIPLEVELID uniqueidentifier,
                @SOURCECONSTITUENTID uniqueidentifier,
                @BATCHMEMBERSHIPDUESID 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
                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

                union all

                select
                    BRC.ID as ID,
                    BRC.NAME as NAME,
                    (
                        select top 1
                            RELATIONSHIPTYPECODE.DESCRIPTION
                        from
                            dbo.BATCHREVENUECONSTITUENTRELATION
                            inner join dbo.RELATIONSHIPTYPECODE on BATCHREVENUECONSTITUENTRELATION.RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
                        where
                            BATCHREVENUECONSTITUENTRELATION.RELATIONID = CONSTITUENTID
                    ) as RELATIONSHIP,
                    null as LOOKUPID,
                    case
                        when exists
                        (
                            select top 1
                                1
                            from
                                dbo.BATCHREVENUECONSTITUENTRELATION
                                inner join dbo.RELATIONSHIPTYPECODE on BATCHREVENUECONSTITUENTRELATION.RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
                            where
                                BATCHREVENUECONSTITUENTRELATION.RELATIONID = CONSTITUENTID
                                and (RELATIONSHIPTYPECODE.ID = '1A08E4A4-4929-4B4C-B3E9-826136A63314' or RELATIONSHIPTYPECODE.ID = 'C7D1CA96-7135-42F7-AF3A-D00BDF1B6C3E')
                        ) then 1
                        else 0
                    end as ISCHILD,
                    BRC.BIRTHDATE as BIRTHDATE,
                    dbo.UFN_AGEFROMFUZZYDATE(BRC.BIRTHDATE, getdate()) as AGE
                from
                    dbo.BATCHMEMBERSHIPDUESMEMBER BMDM
                    inner join dbo.BATCHREVENUECONSTITUENT BRC on BRC.ID = BMDM.CONSTITUENTID
                where
                    BRC.ID <> @SOURCECONSTITUENTID and
                    BMDM.BATCHMEMBERSHIPDUESID = @BATCHMEMBERSHIPDUESID
            )