I guess there is no point of getting rid of the cursor.

You may mark a payment "Paid" only after the payment has been actually made.

And I bet the payments are made one by one.

Which means using some sort of a cursor (loop) anyway.

What you need to do is within the loop, when the next "Awaiting" payment is picked up for processing calculate the already paid amount (SUM where Status = 'Paid'), see if adding the current one will go over the limit and if yes - split it accordingly into "Paid" and "Brought forward" parts.

All other "Awaiting" to be marked "Cancelled" (UPDATE where Status = 'Awaiting')