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:
The orderline table is also the same, but this time I add data for three orders:
So just to repeat, here is the original FIFO picking for one order:
It is easy to do a "batch" pick of the total quantities needed for the three orders:
It yields this output which is OK - except we cannot tell how much of each individual pick is for each order:
So let's apply a bit more analytics:
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:
Now we are able to join on "overlapping" qty intervals:
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:
And then we can tidy this up and get a new picking list query:
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:
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.
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
Post a Comment