UFN_EVENTREGISTRANT_GETBALANCEDESIGNATIONPORTION

            CREATE function dbo.UFN_EVENTREGISTRANT_GETBALANCEDESIGNATIONPORTION
            (
                @REGISTRANTID uniqueidentifier
            )
            returns table
            as
                return(select
                            REGISTRANTDESIGNATION.DESIGNATIONID,
                            case when REGISTRANTDESIGNATION.AMOUNT - coalesce(AMOUNTPAIDTOWARDDESIGNATION.AMOUNT,0) < 0 then 0
                            else REGISTRANTDESIGNATION.AMOUNT - coalesce(AMOUNTPAIDTOWARDDESIGNATION.AMOUNT,0)
                            end as BALANCE
                            from dbo.REGISTRANTDESIGNATION    
                            left join (select sum(EVENTREGISTRANTPAYMENT.AMOUNT) as AMOUNT,
                                    REVENUESPLIT_EXT.DESIGNATIONID 
                                    from dbo.EVENTREGISTRANTPAYMENT 
                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
                                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                                    where (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
                                    and REVENUESPLIT_EXT.DESIGNATIONID is not null
                                    and EVENTREGISTRANTPAYMENT.REGISTRANTID = @REGISTRANTID
                                    group by REVENUESPLIT_EXT.DESIGNATIONID) as AMOUNTPAIDTOWARDDESIGNATION on  AMOUNTPAIDTOWARDDESIGNATION.DESIGNATIONID = REGISTRANTDESIGNATION.DESIGNATIONID
                        where      REGISTRANTDESIGNATION.REGISTRANTID = @REGISTRANTID)