create table priorities (bucket, priority) as 
  select 'A', 1 from dual union all 
  select 'B', 2 from dual union all 
  select 'C', 3 from dual union all 
  select 'D', 4 from dual union all 
  select 'E', 5 from dual union all 
  select 'F', 6 from dual union all 
  select 'G', 7 from dual union all 
  select 'H', 8 from dual 

create table volumes (item, quantity) as 
  select 'P1', 8 from dual union all 
  select 'P2', 7 from dual 

create table data (bucket, item, present_qty) as 
  select 'A', 'P1', 6 from dual union all 
  select 'B', 'P1', 9 from dual union all 
  select 'C', 'P1', 8 from dual union all 
  select 'D', 'P1', 1 from dual union all 
  select 'F', 'P1', 0 from dual union all 
  select 'G', 'P1', 8 from dual union all 
  select 'H', 'P1', 72 from dual union all 
  select 'A', 'P2', 3 from dual union all 
  select 'B', 'P2', 5 from dual union all 
  select 'C', 'P2', 7 from dual union all 
  select 'D', 'P2', 9 from dual union all 
  select 'E', 'P2', 1 from dual union all 
  select 'F', 'P2', 4 from dual union all 
  select 'H', 'P2', 4 from dual 

with 
  p (bucket, rn) as ( 
    select bucket, row_number() over (order by priority) 
    from   priorities 
  ) 
, prep (bucket, rn, item, cap, qty, total_qty) as ( 
    select p.bucket, p.rn, v.item, v.quantity, coalesce(d.present_qty, 0), 
           sum(coalesce(d.present_qty, 0)) over (partition by v.item) 
    from   p cross join volumes v 
             left outer join data d on p.bucket = d.bucket and v.item = d.item 
  ) 
--select * from prep order by item, rn; /* 
, fill_buckets (bucket, rn, item, cap, qty, fill_qty) as ( 
    select bucket, rn, item, cap, qty, greatest(0, least(cap, total_qty - cap * (rn - 1))) 
    from   prep 
  ) 
--select * from fill_buckets order by item, rn; /* 
, delta (bucket, rn, item, cap, qty, to_add, to_subtr) as ( 
    select bucket, rn, item, cap, qty, greatest(0, fill_qty - qty), greatest(0, qty - fill_qty) 
    from   fill_buckets 
  ) 
--select * from delta order by item, rn; /* 
, cumulative_add_subtr (flag, bucket, rn, item, x_qty) as ( 
    select case when to_add > 0 then 'A' else 'S' end, 
           bucket, rn, item, 
           case when to_add > 0 
                then sum(to_add)   over (partition by item order by rn) 
                else sum(to_subtr) over (partition by item order by rn desc) end 
    from   delta 
    where  to_add > 0 or to_subtr > 0 
) 
--select * from cumulative_add_subtr order by item, x_qty, flag; /* 
, match (flag, bucket, rn, item, x_qty, x_bucket, x_rn) as ( 
    select flag, bucket, rn, item, 
           x_qty - lag(x_qty, 1, 0) over (partition by item order by x_qty, flag), 
           case flag when 'A' 
                     then first_value(case flag when 'S' then bucket end ignore nulls) 
                          over (partition by item order by x_qty, flag 
                                rows between current row and unbounded following) 
                     else first_value(case flag when 'A' then bucket end ignore nulls) 
                          over (partition by item order by x_qty, flag 
                                rows between current row and unbounded following) end, 
           case flag when 'A' 
                     then first_value(case flag when 'S' then rn end ignore nulls) 
                          over (partition by item order by x_qty, flag 
                                rows between current row and unbounded following) 
                     else first_value(case flag when 'A' then rn end ignore nulls) 
                          over (partition by item order by x_qty, flag 
                                rows between current row and unbounded following) end 
    from   cumulative_add_subtr 
  ) 
--select * from match; /* 
, helper (x) as ( 
    select 'A' from dual union all select 'S' from dual 
  )   
, mirrored (bucket, item, add_qty, subtr_qty, add_from, add_to, rn_from, rn_to) as ( 
    select case h.x when m.flag then bucket else x_bucket end, 
           item, 
           case h.x when 'A' then x_qty end, 
           case h.x when 'S' then x_qty end, 
           case h.x when 'A' then case flag when 'A' then x_bucket else   bucket end end, 
           case h.x when 'S' then case flag when 'A' then   bucket else x_bucket end end, 
           case h.x when 'A' then case flag when 'A' then x_rn     else   rn     end end, 
           case h.x when 'S' then case flag when 'A' then   rn     else x_rn     end end 
    from   match m cross join helper h 
    where  x_qty > 0 
  ) 
--select * from mirrored order by item, m.rn; /* 
, almost_done (bucket, item, rn, cap, add_qty, subtr_qty, add_from, add_to, result_qty) as ( 
    select d.bucket, d.item, d.rn, d.cap, m.add_qty, m.subtr_qty, m.add_from, m.add_to, 
           d.qty + case when d.to_add > 0 
                        then sum(m.add_qty) over (partition by d.bucket, d.item 
                                                  order by m.rn_from desc) else 0 end 
                 - case when d.to_subtr > 0 
                        then sum(m.subtr_qty) over (partition by d.bucket, d.item 
                                                    order by m.rn_to) else 0 end 
    from   delta d left outer join mirrored m on d.bucket = m.bucket and d.item = m.item 
  ) 
--select * from almost_done; /* 
select bucket, item, result_qty - nvl(add_qty, 0) + nvl(subtr_qty, 0) as start_qty, 
       add_qty, subtr_qty, result_qty, 
       case result_qty when cap then 'Y' else 'N' end as status, 
       add_from, add_to 
from   almost_done 
order  by item, rn, case when add_qty > 0 then start_qty else -start_qty end