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  |
shahzaib_4vip@hotmail.com
Messages: 410
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 #405910 is a reply to message #405893] |
Sun, 31 May 2009 10:28  |
shahzaib_4vip@hotmail.com
Messages: 410
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
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Feb 10 16:37:59 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
