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.

## No comments:

## Post a Comment