Skip to Main Content
  • Questions

  • How to create FIFO report based on evaluation system

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Smith.

Asked: July 31, 2017 - 3:03 pm UTC

Last updated: May 26, 2020 - 11:51 am UTC

Version: 11g R2

Viewed 10K+ times! This question is

You Asked

I want to implement FIFO Stock model in my inventory.
So I face some problems but before staring query i describe my physical structure of the table.

create table purchase_mas
(
stock_id        number 
pur_date date,
product_code number,
product_name varchar2(10),
stock_qty  number,
stock_rate      number(7,2)
);

create table sale_mas
(
sale_id        number 
sale_date date,
product_code number,
product_name varchar2(10),
sale_qty  number,
sale_rate number(7,2)
);

insert into purchase_mas values (1,'01-Jan-17',1,'A',10,110);
insert into purchase_mas values (2,'02-Jan-17',1,'A',20,120);
insert into purchase_mas values (3,'02-Jan-17',2,'B',5,60);
insert into purchase_mas values (4,'03-Jan-17',1,'A',12,70);
insert into purchase_mas values (5,'05-Jan-17',3,'C',30,130);
insert into purchase_mas values (6,'06-Jan-17',2,'B',12,90);

commit;


insert into sale_mas values (11,'03-Jan-17',1,'A',4,130);
insert into sale_mas values (12,'03-Jan-17',1,'A',5,140);
insert into sale_mas values (13,'04-Jan-17',1,'A',7,160);

commit;


I want to like following sale report.
but here following column is just for understanding purpose.
actual_sal_qty, stock_id, remaing_stock, actual_stk_qty, actual_stk_qty_after_sale.


sale_id sale_date product_code product_name qty rate actual_sal_qty stock_id remaing_stock actual_stk_qty actual_stk_qty_after_sale stock_rate
11 03-Jan-17 1                A 4 130 4            1            6  30       26    110
12 03-Jan-17 1         A 5 140 5            1            1  26       21    110
13 04-Jan-17 1                A 7 160 1            1            0  21       14    110
13 04-Jan-17 1                A 7 160 6            2            14  21       14    120



Basic FIFO Stock Model how work?
My Inventory Start date is 01-Jan-17 product A have 30 Qty.
Suppose I Sale qty 4 of product A with 130$ on 03-Jan-17.
When I run sale report i want to know my stock rate so Sale Id 11 is able to get qty from stock id 1. and
Stock Id 1 left 6 Qty and Stock id 2 left 20 Qty and stock rate is 110.

When I sale qty 5 of same product A to other client on same day,
Here Stock id 1 is able to give 5 qty.
When i see sale report, stock rate is 110.

When I purchase product A qty 12 on 03-jan-17 but does not effect on sale report,

When I sale qty 7 of same product A on 04-jan-17,
Here Stock id 1 is able to give only 1 qty.
and stock id 2 is able to give 6 qty
When i see sale report, stock rate is different so stock rate is (( 110 * 1) + ( 120 * 6))/7 here 7 is sale qty .
Stock rate is 118.58.


How it is possible using plsql or cursor programming or select query.



and Chris said...

So, you want to apportion sales to stock purchases in a FIFO manner? I.e. sales always consume the oldest stock you have first?

If so, you can do something like this:

- First, calculate the running totals of stock and sales AND the total up to the previous row for each. You can find the prior total with:

rows between unbounded preceding and 1 preceding


In the window clause of the running sum. Then join the stock and sales data joined on product code. Also filter:

- The current stock total >= the previous sale total AND
- The current sale total >= the previous stock total

with stock as (
  select p.*, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and current row
         ), stock_qty) run_tot
  from   purchase_mas p
), sales as (
  select s.*, 
         sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and current row
         ) run_tot, 
         nvl(sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot 
  from   sale_mas s
)
  select pur_date, sale_date, stock_qty, sale_qty,
         sa.prev_run_tot sale_prev, sa.run_tot sale_tot, 
         st.prev_run_tot stock_prev, st.run_tot stock_tot
  from   stock st
  join   sales sa
  on     st.product_code = sa.product_code
  and    sa.run_tot >= st.prev_run_tot
  and    st.run_tot >= sa.prev_run_tot
  order  by pur_date, sale_date, sale_id;

PUR_DATE              SALE_DATE             STOCK_QTY  SALE_QTY  SALE_PREV  SALE_TOT  STOCK_PREV  STOCK_TOT  
01-JAN-0017 00:00:00  03-JAN-0017 00:00:00  10         4         0          4         0           10         
01-JAN-0017 00:00:00  03-JAN-0017 00:00:00  10         5         4          9         0           10         
01-JAN-0017 00:00:00  04-JAN-0017 00:00:00  10         7         9          16        0           10         
02-JAN-0017 00:00:00  04-JAN-0017 00:00:00  20         7         9          16        10          30


Then to assign the correct quantity to each row, return the smallest of:

- The sale quantity
- The stock quantity
- The overall sale total - the prev stock total
- The overall stock total - the prev sale total

To cover for the case where the current sales match the current stock total, also filter where the result of this calculation are > 0. 

with stock as (
  select p.*, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and current row
         ), stock_qty) run_tot
  from   purchase_mas p
), sales as (
  select s.*, 
         sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and current row
         ) run_tot, 
         nvl(sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot 
  from   sale_mas s
)
  select pur_date, sale_date, stock_qty, sale_qty,
         sa.prev_run_tot, sa.run_tot, st.prev_run_tot, st.run_tot,
         least (
           sale_qty,
           stock_qty,
           sa.run_tot - st.prev_run_tot,
           st.run_tot - sa.prev_run_tot
         ) qty
  from   stock st
  join   sales sa
  on     st.product_code = sa.product_code
  and    sa.run_tot >= st.prev_run_tot
  and    st.run_tot >= sa.prev_run_tot
  and    least (
           sale_qty,
           stock_qty,
           sa.run_tot - st.prev_run_tot,
           st.run_tot - sa.prev_run_tot
         ) > 0
  order  by pur_date, sale_date, sale_id;

PUR_DATE              SALE_DATE             STOCK_QTY  SALE_QTY  PREV_RUN_TOT  RUN_TOT  PREV_RUN_TOT  RUN_TOT  QTY  
01-JAN-0017 00:00:00  03-JAN-0017 00:00:00  10         4         0             4        0             10       4    
01-JAN-0017 00:00:00  03-JAN-0017 00:00:00  10         5         4             9        0             10       5    
01-JAN-0017 00:00:00  04-JAN-0017 00:00:00  10         7         9             16       0             10       1    
02-JAN-0017 00:00:00  04-JAN-0017 00:00:00  20         7         9             16       10            30       6


Note: this assumes that you can always assign sales to the next available stock, even if it arrives after the sale and/or you can only make sales if there is stock available. i.e. you don't have the situation where you have 10 stock units available, but sell 20. Or you can, but you assign the 10 left over from the sale to the next stock purchase, even if that arrives weeks later...

HT to Kim Berg Hansen for his FIFO analytic articles, which provided the groundwork for this answer:
http://www.kibeha.dk/2012/11/analytic-fifo-multiplied-part-1.html

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 10, 2018 - 12:15 pm UTC

I'd like to add an additional question using Chris's result at the end.

I'd like to show the running balance remaining of the stock quantity.

After the sale of 5, there was 5 remaining in stock.
After the sale of 4, there was 1 remaining in stock.
After the sale of 7, there was zero remaining of the original 10 in stock on 1/1/17 and 14 remaining of the 20 received on 1/2/17.

Can you show the sql code needed to add such a column?
Chris Saxon
August 10, 2018 - 2:35 pm UTC

I'm not sure what you're trying to do. Can you provide the complete test case for what you want ( create table + inserts + expected output )?

A reader, August 10, 2018 - 3:26 pm UTC 

create table purchase_mas
(
stock_id        number 
pur_date date,
product_code number,
product_name varchar2(10),
stock_qty  number,
stock_rate      number(7,2)
);

create table sale_mas
(
sale_id        number 
sale_date date,
product_code number,
product_name varchar2(10),
sale_qty  number,
sale_rate number(7,2)
);

insert into purchase_mas values (1,'01-Jan-17',1,'A',10,110);
insert into purchase_mas values (2,'02-Jan-17',1,'A',20,120);
insert into purchase_mas values (3,'02-Jan-17',2,'B',5,60);
insert into purchase_mas values (4,'03-Jan-17',1,'A',12,70);
insert into purchase_mas values (5,'05-Jan-17',3,'C',30,130);
insert into purchase_mas values (6,'06-Jan-17',2,'B',12,90);

commit;


insert into sale_mas values (11,'03-Jan-17',1,'A',4,130);
insert into sale_mas values (12,'03-Jan-17',1,'A',5,140);
insert into sale_mas values (13,'04-Jan-17',1,'A',7,160);

commit;


Using your previous query slightly modified to hide some columns that I don't want in the final result:

with stock as (
  select p.*, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and current row
         ), stock_qty) run_tot
  from   purchase_mas p
), sales as (
  select s.*, 
         sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and current row
         ) run_tot, 
         nvl(sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot 
  from   sale_mas s
)
  select pur_date, sale_date, stock_qty, sale_qty,
         least (
           sale_qty,
           stock_qty,
           sa.run_tot - st.prev_run_tot,
           st.run_tot - sa.prev_run_tot
         ) qty
  from   stock st
  join   sales sa
  on     st.product_code = sa.product_code
  and    sa.run_tot >= st.prev_run_tot
  and    st.run_tot >= sa.prev_run_tot
  and    least (
           sale_qty,
           stock_qty,
           sa.run_tot - st.prev_run_tot,
           st.run_tot - sa.prev_run_tot
         ) > 0
  order  by pur_date, sale_date, sale_id;


I get this:

PDATE       SDATE       STOCK_QTY  QTY
1/1/2017   1/3/2017 10   5
1/1/2017   1/3/2017 10   4
1/1/2017   1/4/2017 10   1
1/2/2017   1/4/2017 20   6


I want something like this as my result:

PDATE       SDATE       STOCK_QTY  QTY REMAINING_STOCK
1/1/2017    1/3/2017 10     5         5
1/1/2017    1/3/2017 10     4         1
1/1/2017    1/4/2017 10     1         0
1/2/2017    1/4/2017 20     6        14


I'd also like something like this: (In this result, I get a summary of stock quantity by date received and a sum of the quantity sold that is attributed to the correct PDATE along with the age of the stock until it was sold (or sysdate if there is unsold quantity.)

PDATE       SDATE       STOCK_QTY  QTY  AGE_IN_DAYS
1/1/2017    1/3/2017 10     5        2
1/1/2017    1/3/2017 10     4        2
1/1/2017    1/4/2017 10     1        3
1/2/2017    1/4/2017 20     6        3
1/2/2017    NULL        20    14       10


I'd like to use this data to determine the age of each stock quantity from date received to date sold. Then, any remaining quantity would be aged based on the date the query was ran (in my example, I run the query on 1/11/17 (10 days from 1/2/17)
Chris Saxon
September 04, 2018 - 10:25 am UTC

To get the remaining stock you can subtract the running total of sales from the running total of stock. And return the greatest of this and zero:

greatest ( st.run_tot - sa.run_tot, 0 )


And isn't the age of the stock simply the sale date - purchase date?

Giving:

with stock as (
  select p.*, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and current row
         ), stock_qty) run_tot
  from   purchase_mas p
), sales as (
  select s.*, 
         sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and current row
         ) run_tot, 
         nvl(sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot 
  from   sale_mas s
)
  select pur_date, sale_date, stock_qty, 
         least (
           sale_qty,
           stock_qty,
           sa.run_tot - st.prev_run_tot,
           st.run_tot - sa.prev_run_tot
         ) qty,
         greatest ( st.run_tot - sa.run_tot, 0 ) remaining,
         sale_date - pur_date age_in_days
  from   stock st
  join   sales sa
  on     st.product_code = sa.product_code
  and    sa.run_tot >= st.prev_run_tot
  and    st.run_tot >= sa.prev_run_tot
  and    least (
           sale_qty,
           stock_qty,
           sa.run_tot - st.prev_run_tot,
           st.run_tot - sa.prev_run_tot
         ) > 0
  where  st.product_name = 'A'
  order  by pur_date, sale_date, sale_id;

PUR_DATE      SALE_DATE     STOCK_QTY   QTY   REMAINING   AGE_IN_DAYS   
01-JAN-2017   03-JAN-2017            10     4           6             2 
01-JAN-2017   03-JAN-2017            10     5           1             2 
01-JAN-2017   04-JAN-2017            10     1           0             3 
02-JAN-2017   04-JAN-2017            20     6          14             2


I'm not sure what you mean by:

I'd like to use this data to determine the age of each stock quantity from date received to date sold. Then, any remaining quantity would be aged based on the date the query was ran (in my example, I run the query on 1/11/17 (10 days from 1/2/17)

Could you clarify please?

sales without stock available

Florian L., May 06, 2020 - 7:46 pm UTC

I would like to follow on the Note from the original question.

Note: this assumes that you can always assign sales to the next available stock, even if it arrives after the sale and/or you can only make sales if there is stock available. i.e. you don't have the situation where you have 10 stock units available, but sell 20. Or you can, but you assign the 10 left over from the sale to the next stock purchase, even if that arrives weeks later...

We have implemented the FIFO in our DWH with the SQL of the original answer. Due the review of the results we found out, that in same very rare cases we have sales without stock available. Based on the Note currently this sales will consume the next incoming stock transaction, but this transaction is happening afterwards. As I understood it correctly the following requirement ist not handeled in the query.

We now want to implement something like: If we are running out of Stock, please make the last sale with the total of available quantity of the stock and then skip this sale in the calculation. So we want to cut this sales to zero stock and then skip it. Then we want to skip all sales which happening during the time, when no stock is available, only when stock is available again then continue with the sales.

Is there any way to implement such logic?

Chris Saxon
May 11, 2020 - 3:02 pm UTC

So once the stock has gone (below) zero, you want to reset? i.e. start subtracting stock from whatever the next level is?

sales without stock available

Florian L., May 25, 2020 - 2:34 pm UTC

It was not really easy to explain, but i think you mean what I want.
So in some very bad cases it happend that we receive a purchases with more items than available in stock. So in theory you current stock is going below zero, what is not possible. On the other hand it is not possible for an older purchase to use items from the stock, which are coming to the stock, later than the purchase.

So the idea is to cut the original purchase, with the maximum available in stock.
In the next record there should be a new order to increase the stock, but the old purchase didn't consume anythin fro this stock. Only the next purchase is consuming this.

I modified the sample from the inital question a little bit:

create table purchase_mas
(
stock_id        number ,
pur_date date,
product_code number,
product_name varchar2(10),
stock_qty  number
);

create table sale_mas
(
sale_id        number, 
sale_date date,
product_code number,
product_name varchar2(10),
sale_qty  number
);

insert into purchase_mas values (1,'01-Jan-17',1,'A',10);
insert into purchase_mas values (2,'10-Jan-17',1,'A',20);
insert into purchase_mas values (3,'20-Jan-17',1,'A',20);

commit;


insert into sale_mas values (11,'03-Jan-17',1,'A',5);
insert into sale_mas values (12,'15-Jan-17',1,'A',40);
insert into sale_mas values (13,'22-Jan-17',1,'A',7);

commit;

with stock as (
  select p.*, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot, 
         nvl(sum(stock_qty) over (
           partition by product_code 
           order by pur_date, stock_id 
           rows between unbounded preceding and current row
         ), stock_qty) run_tot
  from   purchase_mas p
), sales as (
  select s.*, 
         sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and current row
         ) run_tot, 
         nvl(sum(sale_qty) over (
           partition by product_code 
           order by sale_date, sale_id 
           rows between unbounded preceding and 1 preceding
         ), 0) prev_run_tot 
  from   sale_mas s
)
  select pur_date, sale_date, stock_qty, sale_qty,
         sa.prev_run_tot, sa.run_tot, st.prev_run_tot, st.run_tot,
         least (
           sale_qty,
           stock_qty,
           sa.run_tot - st.prev_run_tot,
           st.run_tot - sa.prev_run_tot
         ) qty
  from   stock st
  join   sales sa
  on     st.product_code = sa.product_code
  and    sa.run_tot >= st.prev_run_tot
  and    st.run_tot >= sa.prev_run_tot
  and    least (
           sale_qty,
           stock_qty,
           sa.run_tot - st.prev_run_tot,
           st.run_tot - sa.prev_run_tot
         ) > 0
  order  by pur_date, sale_date, sale_id;

Result:
PUR_DATE      SALE_DATE     STOCK_QTY  SALE_QTY PREV_RUN_TOT  RUN_TOT  PREV_RUN_TOT_1   RUN_TOT_1    QTY
01.01.2017    03.01.2017    10         5        0             5        0                10           5
01.01.2017    15.01.2017    10         40       5             45       0                10           5
10.01.2017    15.01.2017    20         40       5             45       10               30           20
20.01.2017    15.01.2017    20         40       5             45       30               50           15
20.01.2017    22.01.2017    20         7        45            52       30               50           5


The 4th line of the result should not be possible, because the sale from the 15th is consuming stock from the 20th.

What we want ist, that we only sell in line 3 the maximum available stock, which means 25 and then ignore this sales for the further calculation. So sell the 25 items and say, stock is zero and sales is done, fine.

So we should see in line 3 that only 25 are selled and line 4 should not exists in the result. The tricky part is that the purchase from the 20.01.2017 should appear in the result with 20 to increase the stock.

Hopefully this question with the sample is easier to understand.
Chris Saxon
May 26, 2020 - 11:51 am UTC

You can't use window functions if you need to reset the counter when the total goes below zero.

You'll need to look at using recursive with, pattern matching or the model clause instead.

You can find some examples of these at:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9531712000346124348
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.