1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to make Fifo and Lifo wise inventory Valuation Report

Discussion in 'SQL PL/SQL' started by Sharry, May 31, 2009.

  1. Sharry

    Sharry Guest

    My question is I want Stock Report with Costing menthod like First in First out and Last in Last out

    ok I have 4 Tables

    1) Item

    Code (SQL):
    CREATE TABLE item (
      item_code NUMBER(10),
      item_name VARCHAR2(20));
     


    2) Opening

    Code (SQL):
    CREATE TABLE opening (
      opening_date     DATE,
      item_code        NUMBER(10),
      item_name        VARCHAR2(20),
      opening_quantity NUMBER(10),
      opening_rate     NUMBER(10),
      opening_amount   NUMBER(10))
     


    3) Purchase

    Code (SQL):
    CREATE TABLE purchase (
      purchase_date     DATE,
      item_code         NUMBER(10),
      item_name         VARCHAR2(20),
      purchase_quantity NUMBER(10),
      purchase_rate     NUMBER(10),
      purchase_amount   NUMBER(10))  


     
    4) Sale

    Code (SQL):
    CREATE TABLE sale (
      sale_date     DATE,
      item_code     NUMBER(10),
      item_name     VARCHAR2(20),
      sale_quantity NUMBER(10))  



     
    Code (SQL):
    INSERT INTO item
    VALUES     ('1',
                'Needle');

     
    Values for Opening

    Code (SQL):
    INSERT INTO opening
    VALUES     ('25-dec-2009',
                '1',
                'Needle',
                '10',
                '35',
                '350');

     
    Value for Purchase

    Code (SQL):
    INSERT INTO purchase
    VALUES     ('26-dec-2009',
                '1',
                'Needle',
                '20',
                '40',
                '800');
    Code (SQL):
    INSERT INTO purchase
    VALUES     ('27-dec-2009',
                '1',
                'Needle',
                '10',
                '39',
                '390');


    Value for Sale

    Code (SQL):
    INSERT INTO sale
    VALUES     ('26-dec-2009',
                '1',
                'Needle',
                '15');



    Now i need my stock possition with First in First out and Last in Last out base

    I need First in First out like this

    Item no, Item name ,Stock Qty,Rate ,Amount,
    1,Needle,25,39.6,990



    The rate 39.6 Come Because we sale our opening 10 Needle @ 35 and 5 Needle @ 40
    And Remaining 25 Quantity Come with 15 @ 40 & 10 @ 39
    15*40= 600
    10*39=390
    Total 25 @ 39.6(Avg) = 990

    This is we called Fifo (First in First Out)

    Another is Lifo (Last in Last Out)

    The Stock will Come for last in last out like this

    Item no, Item name ,Stock Qty,Rate ,Amount,
    1,Needle,25,38,950




    Last in last out come because we sale 10 needle which come @ 39 & 5 needle which come @ 40
    So the remaining 25 needle we calculate
    15 Needle @ 40 &
    10 Needle @ 35

    15*40 = 600
    10*35 = 350

    Total 25 Needle @ 38(Avg) = 950

    That's All i need my Stock position with Fifo and Lifo


    Regards


    Shahzaib ismail
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi Shahzaib,

    There are many things to consider here, the first of which is the your table design. I understand that you are trying to design an inventory tracking system. Typically such a system should have a transactions table. Here you should make entries by transactions type. As an example such a table can be transaction_id, transaction_date, item_code, transaction_type (purchase or sell), quantity, previous_cost, transaction_cost, new_cost.

    You can have two such transactions table, for example transactions_fifo, transactions_lifo. Whenever a purchase or sell entry is to be made, it should go in both of these tables and procedure should run in which updates the row and inserts the new_cost in FIFO or LIFO calculation. In my opinion this would be a lot more feasible in what you are attempting to do.

    If you want to stick to your design, you still will need to write a procedure that does the calculation for you.
     
  3. samit_gandhi

    samit_gandhi Guest

    Hi

    Have you got the solution? If yes than pls give me the idea how to make it.

    Thanks in advance

    Samit Gandhi