SQL & PL/SQL

Dear SQL'er Expert,

Very need SQL Expert Logical.



create table mr_awal_btps(tx number, saldo_sub number, saldo_main number);

truncate table mr_awal_btps;

insert into mr_awal_btps
select 10000,4000,10000 from dual
union all
select 5000, 3000, 0 from dual
union all
select 4000, 3000, 0 from dual
union all
select 3000,1000,0 from dual
union all
select 2000,1500,0 from dual
union all
select 3000,1500,0 from dual
union all
select 2000,1500,0 from dual

commit;

select * from mr_awal_btps;


-- there is a problem like this :
-- if sub < tx then borrow to main saldo, and main saldo minus by lack
-- however, if tx - sub > remain main saldo then current row will be skip and proceed to next row
-- So, last result should be :


INS SUB MAIN REMAIN_MAIN_saldo

*10000 4000 10000 4000*
*5000 3000 0 2000*
*4000 3000 0 1000*
*3000 1000 0 1000 --> stay to 1000 because ins - sub = 3000 - 1000 = 2000 and 2000 > remain main saldo*
*2000 1500 0 500*
*3000 1500 0 500 --> stay to 1000 because ins - sub = 3000 - 1500 = 1500 and 1500 > remain main saldo*
*2000 1500 0 0*



--------------------------------------------------------------------------------------------------------------------
this my last query which still uncomplete :
------------------------------------------


select
x5.*,
case when lag(flag) over (order by rownum) = 1 and flag = 0 then 0 else 1 end
from
(
select
x4.*,
case when lag(t1,rownum-1) over (order by rownum) + t2 > 0 then 1 else 0 end flag
from
(
select
x3.*
from
(
select
x2.*,
sum(case when rownum = 1 then 0 else lack end) over (order by rownum rows unbounded preceding) t2
from
(
select
x.*,
(sub - ins) lack,
case when (main + sub - ins) < 0 then 0 else (main + sub - ins) end t1
from
mr_awal_btps x
) x2
) x3
) x4
) x5



Is there any row to embed where clause form in Rows Unbounded Preceding Clause ?


Thx so much.
Any help will be appreciated.