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:

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