UFN_MEMBERSHIPDUESBATCH_GETCHILDREN

CREATE function dbo.UFN_MEMBERSHIPDUESBATCH_GETCHILDREN
(
    @BATCHMEMBERSHIPDUESID uniqueidentifier,
    @SOURCECONSTITUENTID uniqueidentifier
)
returns table
as
return (
    select
        BATCHMEMBERSHIPDUESMEMBER.ID,
        BATCHMEMBERSHIPDUESMEMBER.CONSTITUENTID,
        coalesce(RELATIONSHIPTYPECODE.DESCRIPTION, '') as RELATIONTOPRIMARY,
        CONSTITUENT.AGE
    from dbo.BATCHMEMBERSHIPDUESMEMBER
    inner join dbo.CONSTITUENT
        on BATCHMEMBERSHIPDUESMEMBER.CONSTITUENTID = CONSTITUENT.ID
    left outer join dbo.RELATIONSHIP
        on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
            and RELATIONSHIP.RECIPROCALCONSTITUENTID = @SOURCECONSTITUENTID
    left join dbo.RELATIONSHIPTYPECODE
        on RELATIONSHIP.RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
    where BATCHMEMBERSHIPDUESID = @BATCHMEMBERSHIPDUESID
        and TYPECODE = 1
)