Consider the following:
a payment either applies in full to a balance, applies in part to a balance, or overpays a balance.
Now, imagine that we could find, for any balance, the cumulative balance of invoices to date. Rather than imagine that, let's do it:
create view cumulative_balance as
select a.*,
(select sum( balance )
from openitems b
where b.id = a.id and b.type = a.type and a.daysOpen >= a.daysOpen)
as cumulative_balance
from openitems a;
Now we can find the first cumulative balance less than or equal to the payment, for any id and type, and store that, and daysOpen, and cumulative balance in server variables.
Then we update all openItems with that id and type, where daysOpen <= the value we got, setting all those balances to zero.
Then we find the first non-zero balance of that id and type, and set its balance to be it's balance - (payment - the cumulative balance we stored). if there's an overpayment, this balance will be correctly negative.
With the correct query, you'll be able to do the lookup and first update in one statement.
There are two problems. One is that you can't determine, of two or more alances with the same id and type and daysOpen, which should be paid first. Adding a unique id to your table would serve as a tie-breaker for those cases.
Second is the need to save the cumulative balance to use it in the query for the second update. if you designed your table correctly, with a column for invoice_amount that wasn't updated by payments, and a payment column that was, this would solve your problem.
An even better refactoring would be to have two tables, one for invoices and one for payment: then a view could just do all the work, by comparing cumulative balances to cumulative payments, producing a list of unpaid balances or overpayments.
In fact, I designed just such a system for a major mortgage guarantee company with the initials FM. It was a bit more complicated than what you have, in that balances were calculated from a number of formulas of amounts and percentages, and multiple payers (actually, insurers, this was for mortgages that had gone into default) had to be invoiced in a prescribed order according to other rules, per defauted mortgage.
All of this was done in views, with a short (100 line or so) stored procedure that essentially did what I've outlined above: used a view that ordered the billing of invoices by these rules, applied payments (in the view), calculating what additional payments to invoice on what date to which insurer. The stored procedure then just generated invoices for the current date (which current date could be set, again using a view, to any date for testing purposes).
(The irony is that I'd taken the job onteh promise I'd get to write C++; the only C++ I wrote used the Oracle and Sybase C APIs to transfer data from the Oracle system to the Sybase one.)