title: Oracle SQL Query for distributing payments
body: <p>I am trying to achieve distribution of payments over multiple invoice transactions as part of a batch process. The invoice and payment transactions are kept in the same table, that acts as a transactions register.</p>
  
  <p>So for eg. we have in our transactions register table:</p>
  
  <pre><code>ID,   USER ,  TYPE     , AMOUNT   
  1     Mr. X   Invoice      1000   
  2     Mr. X   Invoice      2000   
  3     Mr. X   Invoice      1000   
  4     Mr. X   Payment     -3000   
  5     Mr. X   Payment      -500   
  </code></pre>
  
  <p>I am looking for a query that will take this total payment of 3500 for this user, start from the first invoice, distribute the payment to each invoice and dump it in a new table.</p>
  
  <p>The end result for the new table would be as below.</p>
  
  <pre><code>ID    User ,  TYPE     ,   AMOUNT   , AMOUNT_PAID
  1     Mr. X   Invoice        1000            1000
  2     Mr. X   Invoice        2000            2000
  3     Mr. X   Invoice        1000            500
  </code></pre>
  
  <p>I am trying to avoid using loops in PL/SQL blocks. 
  Any comments are highly appreciated!</p>