Home » SQL & PL/SQL » SQL & PL/SQL » Remaining Stock out put with costing method (Developer 6I Database 10g window xp)
Remaining Stock out put with costing method [message #405893] Sun, 31 May 2009 05:41 Go to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
I am Confused wheather this tread relate to SQL PL/SQL or Report any way i chose SQL/PLSQL

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
CREATE TABLE item (
  item_code NUMBER(10),
  item_name VARCHAR2(20));



2) Opening
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
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

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



INSERT INTO item
VALUES     ('1',
            'Needle'); 

Values for Opening
INSERT INTO opening
VALUES     ('25-dec-2009',
            '1',
            'Needle',
            '10',
            '35',
            '350'); 


Value for Purchase

INSERT INTO purchase
VALUES     ('26-dec-2009',
            '1',
            'Needle',
            '20',
            '40',
            '800');

INSERT INTO purchase
VALUES     ('27-dec-2009',
            '1',
            'Needle',
            '10',
            '39',
            '390'); 


Value for Sale

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

Re: Remaining Stock out put with costing method [message #405900 is a reply to message #405893] Sun, 31 May 2009 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'25-dec-2009' is not a date, it is a string.

Regards
Michel
Re: Remaining Stock out put with costing method [message #405902 is a reply to message #405893] Sun, 31 May 2009 09:57 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Thanks for your reply
Sir Michel Cadot


But i don't get your answer


'25-dec-2009' is not a date, it is a string


String ? assume this is date 25-dec-09


Regards


Shahzaib ismail
Re: Remaining Stock out put with costing method [message #405903 is a reply to message #405893] Sun, 31 May 2009 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
'with oracle strings have single quote marks at each end. Is this a date'?

what date is below?
'07-08-09'
What day? 7,8,9?
what month? 7,8,9?
what year? 7,8,9?

[Updated on: Sun, 31 May 2009 10:02]

Report message to a moderator

Re: Remaining Stock out put with costing method [message #405905 is a reply to message #405893] Sun, 31 May 2009 10:07 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Thanks for your reply
Sir BlackSwan

what can i do for the result which i submit in my question ?

To change my String ? Or some thing else

my question is not that what is string or date my question is how can i get my stock report with Fifo and Lifo value.


Regards

Shahzaib Ismail

Re: Remaining Stock out put with costing method [message #405906 is a reply to message #405893] Sun, 31 May 2009 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
the function which converts strings to DATE is TO_DATE().
TO_DATE & other functions are documented in SQL Reference manual found at http://tahiti.oracle.com
Re: Remaining Stock out put with costing method [message #405907 is a reply to message #405893] Sun, 31 May 2009 10:16 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
OK so this is relate to my question

first i insert my date with to_date function then ?


Regards

Shahzaib ismail
Re: Remaining Stock out put with costing method [message #405909 is a reply to message #405893] Sun, 31 May 2009 10:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Post SQL you have tried & results

[Updated on: Sun, 31 May 2009 10:22]

Report message to a moderator

Re: Remaining Stock out put with costing method [message #405910 is a reply to message #405893] Sun, 31 May 2009 10:28 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
I already mension in my first message that what i want and what i done my problem is i am successful to create a stock possition but need the amount of stock with First in First out or Last in First out order

I create a Report with Parameter

SELECT   item_no,
         item_name,
         Nvl(item2.quantity,0) + Nvl(purqt,0) - Nvl(salqt,0) ok,
         p.q,
         s.sq,
         Nvl(item2.quantity,0) + Nvl(purqt,0) - Nvl(salqt,0) + Nvl(p.q,0) - Nvl(s.sq,0)
FROM     item2,
         (SELECT   item_no_sale,
                   Sum(Nvl(quantity,0)) sq
          FROM     sale2
          WHERE    saledate BETWEEN :D1 AND :Date_from
          GROUP BY item_no_sale) s,
         (SELECT   item_no_pur,
                   Sum(Nvl(quantity,0)) q
          FROM     purchase2
          WHERE    purchase_date BETWEEN :D1 AND :Date_from
          GROUP BY item_no_pur) p,
         (SELECT   item_no_pur,
                   Sum(quantity) purqt
          FROM     purchase2
          WHERE    purchase_date < :D1
          GROUP BY item_no_pur) pur,
         (SELECT   item_no_sale,
                   Sum(quantity) salqt
          FROM     sale2
          WHERE    saledate < :D1
          GROUP BY item_no_sale) sale
WHERE    pur.item_no_pur (+)  = item2.item_no
         AND sale.item_no_sale (+)  = item2.item_no
         AND p.item_no_pur (+)  = item2.item_no
         AND s.item_no_sale (+)  = item2.item_no
         AND item_no BETWEEN :Item_no AND :item_no_to
ORDER BY item2.item_no 



Regards

Shahzaib ISmail
Re: Remaining Stock out put with costing method [message #405958 is a reply to message #405893] Mon, 01 June 2009 03:02 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Waiting for reply

Shahzaib ismail
Re: Remaining Stock out put with costing method [message #460805 is a reply to message #405958] Mon, 14 June 2010 22:10 Go to previous message
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
Senior Member

have you solved your problem?

If yes than guide me thank you

samit gandhi
Previous Topic: Stored Procedure
Next Topic: DBLink
Goto Forum:
  


Current Time: Sat Dec 10 03:06:55 CST 2016

Total time taken to generate the page: 0.08524 seconds