multiple invoice payments not computing
We often receive more than a single payment for an invoice. I have PAYMENTS joined to INVOICES through a INVOICE_PAYMENTS table. INVOICES contains Total Amount (sum of all LINES) and Total Due (Total Amount - PAYMENTS amount).
When I try to enter a new payment in the PAYMENTS table, and look-up the INVOICE, the total due calculation has dropped any other payments. For example, if Amount = 200, and a Payment of 50 has already been made, Due = 150. When I enter a 2nd payment of 150, the Due amount changes to 50, where I expect 0.