UFN_CONSTITUENT_GETINFORMATIONWITHRELATIONSHIP

            CREATE function dbo.UFN_CONSTITUENT_GETINFORMATIONWITHRELATIONSHIP
            (
                @SOURCECONSTITUENTID uniqueidentifier,
                @CONSTITUENTID uniqueidentifier
            )
            returns table as

            return
            (
                select
                    NF.NAME,
                    RTC.DESCRIPTION AS RELATION,
                    LOOKUPID,
                    C.BIRTHDATE as BIRTHDATE,
                    C.AGE as AGE
                from
                    dbo.CONSTITUENT C
                    left join dbo.RELATIONSHIP R on R.RELATIONSHIPCONSTITUENTID = C.ID and R.RECIPROCALCONSTITUENTID = @SOURCECONSTITUENTID
                    left join dbo.RELATIONSHIPTYPECODE RTC on R.RELATIONSHIPTYPECODEID = RTC.ID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                where
                    C.ID = @CONSTITUENTID

                union all

                select
                    BRC.NAME as NAME,
                    RTC.DESCRIPTION as RELATION,
                    null as LOOKUPID,
                    BRC.BIRTHDATE as BIRTHDATE,
                    dbo.UFN_AGEFROMFUZZYDATE(BRC.BIRTHDATE, getdate()) as AGE
                from dbo.BATCHREVENUECONSTITUENT BRC
                    left join dbo.BATCHREVENUECONSTITUENTRELATION BRCR on BRCR.RELATIONID = @CONSTITUENTID and BRCR.CONSTITUENTID = @SOURCECONSTITUENTID
                    left join dbo.RELATIONSHIPTYPECODE RTC on BRCR.RECIPROCALTYPECODEID = RTC.ID
                where
                    BRC.ID = @CONSTITUENTID
            )