Analytic FIFO multiplied - part 2
This is part 2 of a three part posting on analytic FIFO picking of multiple orders. Part 2 shows an alternative way of doing the same thing as part 1 did - but this time using recursive subquery factoring in Oracle v. 11.2.
We use the same tables and same data as part 1, so read part 1 for the setup.
And just to recap - here's the picking list developed in part 1:
Yielding this output:
Without further ado, here's the recursive method (I'll explain after :-) :
Which yields this identical output:
The recursive subquery is this one:
It starts off with a primer to start pumping the recursion. Whatever is in the inventory is defined as the starting "remaining" qty for "recursion zero".
The second half of the UNION ALL handles the recursion. It selects itself and joins it to the orders. By "rnum + 1" the first recursion joins to the first order, the second recursion to the second order, and so on until there are no more orders.
In each recursion we calculate the PICK_QTY the same way as the our usual FIFO:
And then we calculate a new REMAINING by subtracting the PICK_QTY - that way REMAINING is going down with each recursion and so for the second order REMAINING is the inventory that is not already allocated to the first order, and so on.
It would have been nice if we could have done PICK_QTY in an inline view like we used to thus avoiding writing the same complex expression twice. But in a recursive subquery the subquery itself must be selected at "top-level" of the second half of the union all. It cannot be pushed into an inline view, so we must do it this way.
If we just joined fifo to the base orderline table, the second recursion (ordno 62) would remove all rows from fifo with item B1, because ordno 62 does not contain item B1. Therefore we need to create a fictive orderline row with qty 0 for item B1 ordno 62. This we do with this construct that makes sure all combinations of ordno and item is present in the orderlines subquery:
And in order to join correctly in the recursion, we give the orders consecutive numbers 1, 2, 3 ...:
Doing it this way with recursion we do not have the PICK_AT_LOC column readily available like when we did FIFO calculation on the total quantities. So we calculate PICK_AT_LOC with an analytic sum:
This recursive method was fun to do and has a certain elegance. But depending on your data (how big inventory and how many orders at a time) it might very well use more resources (CPU and particularly TEMP memory or even TEMP diskspace) doing the FIFO calculation over and over again for the separate orders.
The "pure" analytic version in part 1 has the advantage of doing a single (but bigger) FIFO calculation on the total quantities, but then it needs to perform more analytics to "distribute" the picked quantities on the orders. But then again - that is a single analytic calculation on the orders and then plain old joins :-)
Both methods might have their cases. I have a feeling though ;-) that the "pure" analytic wins most of the times.
Let me just quote Monty Latiolais again:
If you wish, you can download the script for this demo.
The third part of the series combines this with the route calculation.
We use the same tables and same data as part 1, so read part 1 for the setup.
And just to recap - here's the picking list developed in part 1:
with orderlines as (
select o.ordno
, o.item
, o.qty
, nvl(sum(o.qty) over (
partition by o.item
order by o.ordno
rows between unbounded preceding and 1 preceding
),0) + 1 from_qty
, nvl(sum(o.qty) over (
partition by o.item
order by o.ordno
rows between unbounded preceding and current row
),0) to_qty
from orderline o
where ordno in (51, 62, 73)
), orderbatch as (
select o.item
, sum(o.qty) qty
from orderlines o
group by o.item
), fifo as (
select s.loc
, s.item
, s.loc_qty
, least(s.loc_qty, s.ord_qty - s.sum_prv_qty) pick_qty
, sum_prv_qty + 1 from_qty
, least(sum_qty, ord_qty) to_qty
from (
select o.item
, o.qty ord_qty
, i.loc
, i.qty loc_qty
, nvl(sum(i.qty) over (
partition by i.item
order by i.purch, i.loc
rows between unbounded preceding and 1 preceding
),0) sum_prv_qty
, nvl(sum(i.qty) over (
partition by i.item
order by i.purch, i.loc
rows between unbounded preceding and current row
),0) sum_qty
from orderbatch o
join inventory i
on i.item = o.item
) s
where s.sum_prv_qty < s.ord_qty
)
select f.loc
, f.item
, f.pick_qty pick_at_loc
, o.ordno
, least(
f.loc_qty
, least(o.to_qty, f.to_qty) - greatest(o.from_qty, f.from_qty) + 1
) qty_for_ord
from fifo f
join orderlines o
on o.item = f.item
and o.to_qty >= f.from_qty
and o.from_qty <= f.to_qty
order by f.loc, o.ordno
/
Yielding this output:
LOC ITEM PICK_AT_LOC ORDNO QTY_FOR_ORD ---------- ---------- ----------- ---------- ----------- 1-A-02 B1 5 73 5 1-A-20 A1 18 51 18 1-B-11 B1 4 51 3 1-B-11 B1 4 73 1 1-B-15 B1 2 51 2 1-C-04 B1 12 51 12 1-C-05 A1 6 73 6 2-A-02 A1 24 51 6 2-A-02 A1 24 62 8 2-A-02 A1 24 73 10 2-D-23 B1 1 51 1 11 rows selected.
Without further ado, here's the recursive method (I'll explain after :-) :
with orderlinesbase as (
select o.ordno
, o.item
, o.qty
from orderline o
where ordno in (51, 62, 73)
), orderlines as (
select o.ordno
, i.item
, nvl(b.qty,0) qty
, dense_rank() over (
order by o.ordno
) rnum
from (
select distinct ordno
from orderlinesbase
) o
cross join (
select distinct item
from orderlinesbase
) i
left outer join orderlinesbase b
on b.ordno = o.ordno
and b.item = i.item
), fifo (item, loc, purch, remaining, pick_qty, ordno, rnum) as (
select i.item
, i.loc
, i.purch
, i.qty remaining
, 0 pick_qty
, 0 ordno
, 0 rnum
from inventory i
union all
select f.item
, f.loc
, f.purch
, f.remaining - greatest (
least(
f.remaining
, o.qty - nvl(sum(f.remaining) over (
partition by f.item
order by f.purch, f.loc
rows between unbounded preceding and 1 preceding
),0)
)
, 0
) remaining
, greatest(
least(
f.remaining
, o.qty - nvl(sum(f.remaining) over (
partition by f.item
order by f.purch, f.loc
rows between unbounded preceding and 1 preceding
),0)
)
, 0
) pick_qty
, o.ordno
, o.rnum
from fifo f
join orderlines o
on o.rnum = f.rnum + 1
and o.item = f.item
where f.remaining > 0
)
select f.loc
, f.item
, sum(f.pick_qty) over (
partition by f.loc, f.item
) pick_at_loc
, f.ordno
, f.pick_qty qty_for_ord
from fifo f
where f.rnum > 0
and f.pick_qty > 0
order by f.loc, f.ordno
/
Which yields this identical output:
LOC ITEM PICK_AT_LOC ORDNO QTY_FOR_ORD ---------- ---------- ----------- ---------- ----------- 1-A-02 B1 5 73 5 1-A-20 A1 18 51 18 1-B-11 B1 4 51 3 1-B-11 B1 4 73 1 1-B-15 B1 2 51 2 1-C-04 B1 12 51 12 1-C-05 A1 6 73 6 2-A-02 A1 24 51 6 2-A-02 A1 24 62 8 2-A-02 A1 24 73 10 2-D-23 B1 1 51 1 11 rows selected.
The recursive subquery is this one:
), fifo (item, loc, purch, remaining, pick_qty, ordno, rnum) as (
It starts off with a primer to start pumping the recursion. Whatever is in the inventory is defined as the starting "remaining" qty for "recursion zero".
select i.item
, i.loc
, i.purch
, i.qty remaining
, 0 pick_qty
, 0 ordno
, 0 rnum
from inventory i
The second half of the UNION ALL handles the recursion. It selects itself and joins it to the orders. By "rnum + 1" the first recursion joins to the first order, the second recursion to the second order, and so on until there are no more orders.
from fifo f
join orderlines o
on o.rnum = f.rnum + 1
and o.item = f.item
where f.remaining > 0
In each recursion we calculate the PICK_QTY the same way as the our usual FIFO:
, greatest(
least(
f.remaining
, o.qty - nvl(sum(f.remaining) over (
partition by f.item
order by f.purch, f.loc
rows between unbounded preceding and 1 preceding
),0)
)
, 0
) pick_qty
And then we calculate a new REMAINING by subtracting the PICK_QTY - that way REMAINING is going down with each recursion and so for the second order REMAINING is the inventory that is not already allocated to the first order, and so on.
, f.remaining - greatest (
least(
f.remaining
, o.qty - nvl(sum(f.remaining) over (
partition by f.item
order by f.purch, f.loc
rows between unbounded preceding and 1 preceding
),0)
)
, 0
) remaining
It would have been nice if we could have done PICK_QTY in an inline view like we used to thus avoiding writing the same complex expression twice. But in a recursive subquery the subquery itself must be selected at "top-level" of the second half of the union all. It cannot be pushed into an inline view, so we must do it this way.
If we just joined fifo to the base orderline table, the second recursion (ordno 62) would remove all rows from fifo with item B1, because ordno 62 does not contain item B1. Therefore we need to create a fictive orderline row with qty 0 for item B1 ordno 62. This we do with this construct that makes sure all combinations of ordno and item is present in the orderlines subquery:
from (
select distinct ordno
from orderlinesbase
) o
cross join (
select distinct item
from orderlinesbase
) i
left outer join orderlinesbase b
on b.ordno = o.ordno
and b.item = i.item
And in order to join correctly in the recursion, we give the orders consecutive numbers 1, 2, 3 ...:
, dense_rank() over (
order by o.ordno
) rnum
Doing it this way with recursion we do not have the PICK_AT_LOC column readily available like when we did FIFO calculation on the total quantities. So we calculate PICK_AT_LOC with an analytic sum:
, sum(f.pick_qty) over (
partition by f.loc, f.item
) pick_at_loc
This recursive method was fun to do and has a certain elegance. But depending on your data (how big inventory and how many orders at a time) it might very well use more resources (CPU and particularly TEMP memory or even TEMP diskspace) doing the FIFO calculation over and over again for the separate orders.
The "pure" analytic version in part 1 has the advantage of doing a single (but bigger) FIFO calculation on the total quantities, but then it needs to perform more analytics to "distribute" the picked quantities on the orders. But then again - that is a single analytic calculation on the orders and then plain old joins :-)
Both methods might have their cases. I have a feeling though ;-) that the "pure" analytic wins most of the times.
Let me just quote Monty Latiolais again:
"Don’t you just love these kind of challenges? It’s why we do what we do!"
If you wish, you can download the script for this demo.
The third part of the series combines this with the route calculation.
Comments
Post a Comment