UFN_RESERVATION_GETDEPOSITPAIDDATE

CREATE function dbo.UFN_RESERVATION_GETDEPOSITPAIDDATE
(
    @RESERVATIONID uniqueidentifier
)
returns date
with execute as caller
as begin
    declare @DEPOSITAMOUNT money;
    select @DEPOSITAMOUNT = [DEPOSITAMOUNT]
    from dbo.[RESERVATION] 
    where [ID] = @RESERVATIONID;

    declare @PAYMENTTOTAL money = 0;
    declare @PAYMENTAMOUNT money;
    declare @PAYMENTDATE datetime;
    declare @PREVIOUSPAYMENTDATE datetime;

    declare reservationpayments_cursor cursor local fast_forward for 
    select
        (SALESORDERPAYMENT.AMOUNT - PAYMENTREFUNDTOTAL.AMOUNTREFUNDED) as AMOUNT,
        REVENUE.DATE
    from
        dbo.[REVENUE]
    inner join
        dbo.[SALESORDERPAYMENT] on [REVENUE].[ID] = [SALESORDERPAYMENT].[PAYMENTID]
    outer apply (
        select isnull(sum(CREDITPAYMENT.AMOUNT), 0) as AMOUNTREFUNDED
        from dbo.CREDITPAYMENT
        where CREDITPAYMENT.REVENUEID = REVENUE.ID
    ) PAYMENTREFUNDTOTAL
    where
        [SALESORDERPAYMENT].[SALESORDERID] = @RESERVATIONID
        and SALESORDERPAYMENT.AMOUNT > PAYMENTREFUNDTOTAL.AMOUNTREFUNDED
    order by
        [REVENUE].[DATE] asc;

    open reservationpayments_cursor;

    fetch next from reservationpayments_cursor
    into @PAYMENTAMOUNT, @PAYMENTDATE;

    while @@FETCH_STATUS = 0 and @PAYMENTTOTAL < @DEPOSITAMOUNT begin
        set @PAYMENTTOTAL += @PAYMENTAMOUNT;
        set @PREVIOUSPAYMENTDATE = @PAYMENTDATE;

        fetch next from reservationpayments_cursor
        into @PAYMENTAMOUNT, @PAYMENTDATE;
    end

    --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

    close reservationpayments_cursor;
    deallocate reservationpayments_cursor;

    declare @DEPOSITPAIDDATE date;

    if @PAYMENTTOTAL >= @DEPOSITAMOUNT begin
        set @DEPOSITPAIDDATE = @PREVIOUSPAYMENTDATE;
    end

    return @DEPOSITPAIDDATE;
end