UFN_MEMBERSHIPDUESBATCH_GETMEMBERS

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

    union all

    select
        BATCHMEMBERSHIPDUESMEMBER.ID,
        BATCHMEMBERSHIPDUESMEMBER.CONSTITUENTID,
        coalesce
        (
            (
                select top 1
                    RELATIONSHIPTYPECODE.DESCRIPTION
                from
                    dbo.BATCHREVENUECONSTITUENTRELATION
                    inner join dbo.RELATIONSHIPTYPECODE on BATCHREVENUECONSTITUENTRELATION.RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
                where
                    BATCHREVENUECONSTITUENTRELATION.RELATIONID = CONSTITUENTID
            ), '') as RELATIONTOPRIMARY,
        '' as LOOKUPID
    from
        dbo.BATCHMEMBERSHIPDUESMEMBER
        inner join dbo.BATCHREVENUECONSTITUENT on BATCHMEMBERSHIPDUESMEMBER.CONSTITUENTID = BATCHREVENUECONSTITUENT.ID
    where
        BATCHMEMBERSHIPDUESID = @BATCHMEMBERSHIPDUESID
        and TYPECODE = 0
)