UFN_MEMBERSHIPDUESBATCH_GETALLBENEFITSFORCONTEXTVIEW

CREATE function dbo.UFN_MEMBERSHIPDUESBATCH_GETALLBENEFITSFORCONTEXTVIEW
(
    @BATCHMEMBERSHIPDUESID uniqueidentifier
)
returns table
as return
    select [NAME]
            , [USEPERCENT]
            , [VALUEAMOUNT]
            , [VALUEPERCENT]
            , [VALUE]
            , [QUANTITY]
            , [TOTAL]
    from
    (
        select            -- non-percent benefits only

                b.[NAME]
                , b.[USEPERCENT]
                , BMDB.UNITVALUE as [VALUEAMOUNT]
                , 0 as [VALUEPERCENT]
                , '' as [VALUE]
                , BMDB.[QUANTITY]
                , BMDB.[UNITVALUE] * BMDB.[QUANTITY] as [TOTAL]
            from dbo.BATCHMEMBERSHIPDUESBENEFIT BMDB
                inner join dbo.BENEFIT b
                    on BMDB.BENEFITID = b.ID
            where
                BMDB.BATCHMEMBERSHIPDUESID = @BATCHMEMBERSHIPDUESID
                and b.USEPERCENT = 0

            union

            select            -- percent benefits only

                b.[NAME]
                , b.[USEPERCENT]
                , 0 as [VALUEAMOUNT]
                , BMDPB.[VALUEPERCENT]
                , '' as [VALUE]
                , 1 as [QUANTITY]
                , BMDPB.[VALUEPERCENT] * BMDPB.[PERCENTAPPLICABLEAMOUNT] / 100 as [TOTAL]
            from dbo.BATCHMEMBERSHIPDUESPERCENTAGEBENEFIT BMDPB
                inner join dbo.BENEFIT b
                    on BMDPB.BENEFITID = b.ID
                inner join dbo.BATCHMEMBERSHIPDUES BMD
                    on BMDPB.BATCHMEMBERSHIPDUESID = BMD.ID
            where
                BMDPB.BATCHMEMBERSHIPDUESID = @BATCHMEMBERSHIPDUESID
                and b.USEPERCENT = 1
    ) as BENEFITS