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