Home » SQL & PL/SQL » SQL & PL/SQL » FIFO Store Consumption (Oracle 10G (10.2.0.1.0 ), Windows 2003)
FIFO Store Consumption [message #583076] Thu, 25 April 2013 08:38 Go to next message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
Dear Gurus,
I need a query to devide the total consumption of an item into parts as it was stored in store on the basis of FIFO. The item that was stored first it will consumed first.

My sample data is given below.
DROP TABLE STORE_STOCK;
CREATE TABLE STORE_STOCK
(
  no       NUMBER(4),
  vdate    DATE,
  code     VARCHAR2(8),
  clqty    NUMBER(8,3),
  dept_id  NUMBER(4)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;

DROP TABLE STORE_ISSUE;
CREATE TABLE store_issue
(
  code     VARCHAR2(8),
  idate    DATE,
  dept_id  NUMBER(4),
  qty      NUMBER(8,3)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;

INSERT INTO store_stock
            (NO, vdate, code, clqty, dept_id
            )
     VALUES (4006, TO_DATE ('12-MAR-13', 'DD-MON-RR'), '012-043D', 515.425, 34
            );

INSERT INTO store_stock
            (NO, vdate, code, clqty, dept_id
            )
     VALUES (4086, TO_DATE ('15-MAR-13', 'DD-MON-RR'), '012-043D', 870, 34
            );

INSERT INTO store_stock
            (NO, vdate, code, clqty, dept_id
            )
     VALUES (4495, TO_DATE ('13-APR-13', 'DD-MON-RR'), '012-043D', 498, 34
            );
INSERT INTO store_stock
            (NO, vdate, code, clqty, dept_id
            )
     VALUES (359, TO_DATE ('21-jul-12', 'DD-MON-RR'), '012-042C', 1050.1, 19
            );
INSERT INTO store_stock
            (NO, vdate, code, clqty, dept_id
            )
     VALUES (2144, TO_DATE ('14-nov-12', 'DD-MON-RR'), '012-042C', 990, 19
            );
INSERT INTO store_stock
            (NO, vdate, code, clqty, dept_id
            )
     VALUES (1876, TO_DATE ('23-oct-12', 'DD-MON-RR'), '012-042C', 1010, 34
            );

INSERT INTO store_issue
            (CODE, idate, dept_id,qty)
     VALUES ('012-043D', TO_DATE ('24-apr-13', 'DD-MON-RR'), 34,1650
            );


INSERT INTO store_issue
            (CODE, idate, dept_id,qty)
     VALUES ('012-042c', TO_DATE ('24-apr-13', 'DD-MON-RR'), 19,990
            );

commit;


SQL>select * from store_stock
  2  order by dept_id,vdate,no;

        NO VDATE     CODE          CLQTY    DEPT_ID
---------- --------- -------- ---------- ----------
       359 21-JUL-12 012-042C     1050.1         19
      2144 14-NOV-12 012-042C        990         19
      1876 23-OCT-12 012-042C       1010         34
      4006 12-MAR-13 012-043D    515.425         34
      4086 15-MAR-13 012-043D        870         34
      4495 13-APR-13 012-043D        498         34

6 rows selected.

SQL>select * from store_issue;

CODE     IDATE        DEPT_ID        QTY
-------- --------- ---------- ----------
012-043D 24-APR-13         34       1650
012-042C 24-APR-13         19        990

Required Output will be
        NO VDATE     CODE          CLQTY    DEPT_ID    ISS_QTY      BAL_ISS
---------- --------- -------- ---------- ----------  ----------  ---------
       359 21-JUL-12 012-042C     1050.1         19     990          0
      2144 14-NOV-12 012-042C        990         19       0          0
      1876 23-OCT-12 012-042C       1010         34    1010        640
      4006 12-MAR-13 012-043D    515.425         34    515.425    124.575
      4086 15-MAR-13 012-043D        870         34    124.575      0
      4495 13-APR-13 012-043D        498         34     0           0


The total consumed against code '012-042C' is 990 for dept_id 19 and we have stored qty 1050.1 against goods receipt no 359 dated 12-jul-12. So have issued the whole qty against no 359 and balance is 0.
For item code '012-043D' the total issuance is 1650 and we have issued it against 03 goods receipt nos.

Please help me to get the result.
I will be very thankful to all of you.

Regards
Muhammad Mohsin
Re: FIFO Store Consumption [message #583077 is a reply to message #583076] Thu, 25 April 2013 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand you BAL_ISS column.
Can you explain the rules for it. How is it computed?
Actually I don't understand ISS_QTY either.

Regards
Michel
Re: FIFO Store Consumption [message #583079 is a reply to message #583077] Thu, 25 April 2013 09:46 Go to previous messageGo to next message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
First I appology for some mistakes in output.
Output will be
      NO VDATE     CODE          CLQTY    DEPT_ID    ISS_QTY      BAL_ISS
---------- --------- -------- ---------- ----------  ----------  ---------
       359 21-JUL-12 012-042C     1050.1         19     990          0
      2144 14-NOV-12 012-042C        990         19       0          0
      4006 12-MAR-13 012-043D    515.425         34    515.425    1134.575
      4086 15-MAR-13 012-043D        870         34      870      264.575
      4495 13-APR-13 012-043D        498         34     264.575      0


Dear Michel,
We will calculate ISS_QTY column from store_issue table. Total issuance qty against 012-042C code is 990 and first entry of CL_QTY column in table store_stock (order by date) against this code is 1050.1. Hence all the qty is issued against this entry. So the BAL_ISS is 0.
For code '012-043D' the total issuance is 1650. To complete it's issuance we will devide it according to CLQTY filed.
Calculation against code 012-043D will be as following:
Total issue qty = 1650
First entry in store_issue table (order by dept_id,vdate,no)is 515.425. ISS_QTY column will 515.425 and BALL_ISS will be 1650-515.425=1134.575
Second entry in store_issue table is 870 and BAL_ISS will be 1134.575- 870=264.575
Third entry in store_issue table is 498 and BAL_ISS will be zero because 264.575 is less than 498 and our 1650 qty is completed here.

I have tried to clear you questions.

Regards
M.Mohsin
Re: FIFO Store Consumption [message #583093 is a reply to message #583079] Thu, 25 April 2013 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select s.no, s.vdate, s.code, s.clqty, s.dept_id,
  2         least(s.clqty,
  3               greatest(0, 
  4                        i.qty
  5                        -nvl(sum(s.clqty) over 
  6                               (partition by s.dept_id, s.code order by vdate, no
  7                                rows between unbounded preceding and 1 preceding),
  8                             0)
  9                       )
 10              ) iss_qty,
 11         greatest(0, 
 12                  i.qty-sum(s.clqty) over (partition by s.dept_id, s.code order by vdate, no)
 13                 ) bal_iss
 14  from store_stock s, store_issue i
 15  where i.code = s.code and i.dept_id = s.dept_id
 16  order by dept_id, vdate, no
 17  /
        NO VDATE       CODE          CLQTY    DEPT_ID    ISS_QTY    BAL_ISS
---------- ----------- -------- ---------- ---------- ---------- ----------
       359 21-JUL-2012 012-042C     1050.1         19        990          0
      2144 14-NOV-2012 012-042C        990         19          0          0
      4006 12-MAR-2013 012-043D    515.425         34    515.425   1134.575
      4086 15-MAR-2013 012-043D        870         34        870    264.575
      4495 13-APR-2013 012-043D        498         34    264.575          0

Regards
Michel

[Updated on: Thu, 25 April 2013 13:07]

Report message to a moderator

Re: FIFO Store Consumption [message #583094 is a reply to message #583076] Thu, 25 April 2013 13:11 Go to previous messageGo to next message
BlackSwan
Messages: 22786
Registered: January 2009
Senior Member
just curious, how can you have a fraction of a unit (like 0.1)?
Re: FIFO Store Consumption [message #583108 is a reply to message #583094] Thu, 25 April 2013 23:22 Go to previous message
mmohsinaziz
Messages: 88
Registered: May 2012
Member
Dear Michel,
Thankyou very much. You always solved my query problems.

Regards
Muhammad Mohsin
Previous Topic: DBMS_SQL.describe_columns2-session disconnect
Next Topic: Creating custom error message
Goto Forum:
  


Current Time: Wed Sep 17 18:48:00 CDT 2014

Total time taken to generate the page: 0.09226 seconds