Analytic FIFO multiplied - part 1

I have blogged before about Analytic FIFO picking as well as talked about it at KScope12 and will do again at UKOUG2012.

A few days ago Monty Latiolais, the president of ODTUG, had a need to do this - not just for one order which he already had developed the technique for, but for multiple orders, where the FIFO picking for the second order should not consider the inventory that was already allocated to the first order, and so on.

So here is a three-part demo of how to do this.


First we setup the same inventory as my original demo:

create table inventory (
   item  varchar2(10)   -- identification of the item
 , loc   varchar2(10)   -- identification of the location
 , qty   number         -- quantity present at that location
 , purch date           -- date that quantity was purchased
)
/

begin
   insert into inventory values('A1', '1-A-20', 18, DATE '2004-11-01');
   insert into inventory values('A1', '1-A-31', 12, DATE '2004-11-05');
   insert into inventory values('A1', '1-C-05', 18, DATE '2004-11-03');
   insert into inventory values('A1', '2-A-02', 24, DATE '2004-11-02');
   insert into inventory values('A1', '2-D-07',  9, DATE '2004-11-04');
   insert into inventory values('B1', '1-A-02', 18, DATE '2004-11-06');
   insert into inventory values('B1', '1-B-11',  4, DATE '2004-11-05');
   insert into inventory values('B1', '1-C-04', 12, DATE '2004-11-03');
   insert into inventory values('B1', '1-B-15',  2, DATE '2004-11-02');
   insert into inventory values('B1', '2-D-23',  1, DATE '2004-11-04');
   commit;
end;
/

The orderline table is also the same, but this time I add data for three orders:

create table orderline (
   ordno number         /* id-number of the order     */
 , item  varchar2(10)   /* identification of the item */
 , qty   number         /* quantity ordered           */
)
/

begin
   insert into orderline values (51, 'A1', 24);
   insert into orderline values (51, 'B1', 18);
   insert into orderline values (62, 'A1',  8);
   insert into orderline values (73, 'A1', 16);
   insert into orderline values (73, 'B1',  6);
   commit;
end;
/

So just to repeat, here is the original FIFO picking for one order:

select s.loc
     , s.item
     , least(s.loc_qty, s.ord_qty - s.sum_prv_qty) pick_qty
  from (
   select o.item
        , o.qty ord_qty
        , i.loc
        , i.purch
        , 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
     from orderline o
     join inventory i
       on i.item = o.item
    where o.ordno = 51
) s
 where s.sum_prv_qty < s.ord_qty
 order by s.loc
/

LOC        ITEM         PICK_QTY
---------- ---------- ----------
1-A-20     A1                 18
1-B-11     B1                  3
1-B-15     B1                  2
1-C-04     B1                 12
2-A-02     A1                  6
2-D-23     B1                  1

6 rows selected.

It is easy to do a "batch" pick of the total quantities needed for the three orders:

with orderbatch as (
   select o.item
        , sum(o.qty) qty
     from orderline o
    where o.ordno in (51, 62, 73)
    group by o.item
)
select s.loc
     , s.item
     , least(s.loc_qty, s.ord_qty - s.sum_prv_qty) pick_qty
  from (
   select o.item
        , o.qty ord_qty
        , i.loc
        , i.purch
        , 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
     from orderbatch o
     join inventory i
       on i.item = o.item
) s
 where s.sum_prv_qty < s.ord_qty
 order by s.loc
/

It yields this output which is OK - except we cannot tell how much of each individual pick is for each order:

LOC        ITEM         PICK_QTY
---------- ---------- ----------
1-A-02     B1                  5
1-A-20     A1                 18
1-B-11     B1                  4
1-B-15     B1                  2
1-C-04     B1                 12
1-C-05     A1                  6
2-A-02     A1                 24
2-D-23     B1                  1

8 rows selected.

So let's apply a bit more analytics:

with orderbatch as (
   select o.item
        , sum(o.qty) qty
     from orderline o
    where o.ordno in (51, 62, 73)
    group by o.item
)
select s.loc
     , s.item
     , 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.purch
        , 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
 order by s.item, s.purch, s.loc
/

LOC        ITEM         PICK_QTY   FROM_QTY     TO_QTY
---------- ---------- ---------- ---------- ----------
1-A-20     A1                 18          1         18
2-A-02     A1                 24         19         42
1-C-05     A1                  6         43         48
1-B-15     B1                  2          1          2
1-C-04     B1                 12          3         14
2-D-23     B1                  1         15         15
1-B-11     B1                  4         16         19
1-A-02     B1                  5         20         24

8 rows selected.

FROM_QTY and TO_QTY shows that loc 1-A-20 picks "from 1 to 18" of the 60 pieces of item A1.

Similarly we can make a FROM_QTY and TO_QTY for the orders:

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)
 order by o.item, o.ordno
/

     ORDNO ITEM              QTY   FROM_QTY     TO_QTY
---------- ---------- ---------- ---------- ----------
        51 A1                 24          1         24
        62 A1                  8         25         32
        73 A1                 16         33         48
        51 B1                 18          1         18
        73 B1                  6         19         24

Now we are able to join on "overlapping" qty intervals:

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.purch
        , 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.purch
           , 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.purch
     , f.pick_qty
     , f.from_qty
     , f.to_qty
     , o.ordno
     , o.qty
     , o.from_qty
     , o.to_qty
  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.item, f.purch, o.ordno
/

LOC    ITEM PURCH    PICK_QTY FROM_QTY TO_QTY ORDNO QTY FROM_QTY TO_QTY
------ ---- -------- -------- -------- ------ ----- --- -------- ------
1-A-20 A1   04-11-01       18        1     18    51  24        1     24
2-A-02 A1   04-11-02       24       19     42    51  24        1     24
2-A-02 A1   04-11-02       24       19     42    62   8       25     32
2-A-02 A1   04-11-02       24       19     42    73  16       33     48
1-C-05 A1   04-11-03        6       43     48    73  16       33     48
1-B-15 B1   04-11-02        2        1      2    51  18        1     18
1-C-04 B1   04-11-03       12        3     14    51  18        1     18
2-D-23 B1   04-11-04        1       15     15    51  18        1     18
1-B-11 B1   04-11-05        4       16     19    51  18        1     18
1-B-11 B1   04-11-05        4       16     19    73   6       19     24
1-A-02 B1   04-11-06        5       20     24    73   6       19     24

11 rows selected.

Notice the pick of 24 pieces of A1 at loc 2-A-02 is joined with all three orders.

Now we can get how much to pick for each individual order from each loc:

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.purch
        , 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.purch
           , 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.purch
     , f.pick_qty
     , f.from_qty
     , f.to_qty
     , o.ordno
     , o.qty
     , o.from_qty
     , o.to_qty
     , least(
          f.loc_qty
        , least(o.to_qty, f.to_qty) - greatest(o.from_qty, f.from_qty) + 1
       ) pick_ord_qty
  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.item, f.purch, o.ordno
/

LOC    ITEM PURCH    PICK_QTY FROM_QTY TO_QTY ORDNO QTY FROM_QTY TO_QTY PICK_ORD_QTY
------ ---- -------- -------- -------- ------ ----- --- -------- ------ ------------
1-A-20 A1   04-11-01       18        1     18    51  24        1     24           18
2-A-02 A1   04-11-02       24       19     42    51  24        1     24            6
2-A-02 A1   04-11-02       24       19     42    62   8       25     32            8
2-A-02 A1   04-11-02       24       19     42    73  16       33     48           10
1-C-05 A1   04-11-03        6       43     48    73  16       33     48            6
1-B-15 B1   04-11-02        2        1      2    51  18        1     18            2
1-C-04 B1   04-11-03       12        3     14    51  18        1     18           12
2-D-23 B1   04-11-04        1       15     15    51  18        1     18            1
1-B-11 B1   04-11-05        4       16     19    51  18        1     18            3
1-B-11 B1   04-11-05        4       16     19    73   6       19     24            1
1-A-02 B1   04-11-06        5       20     24    73   6       19     24            5

11 rows selected.

And then we can tidy this up and get a new picking list query:

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
/

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.

That picking list tells the operator, that at loc 2-A-02 he should pick 24 pieces total of item A1, and distribute them on his truck by 6 to ordno 51, 8 to ordno 62 and 10 to ordno 73.

Oh, the power of SQL. Oh, the power of analytic functions. :-D

Let me just quote Monty here:
"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 used for this demo.

The second part of this series shows a different way using recursive subquery factoring in Oracle 11.2.

The third part will combine this with the analytic picking route code for the supreme picking query.

Comments