Home » Developer & Programmer » Reports & Discoverer » Inventory Valuation with Fifo and Lifo (Developer 6I, Database 10g window XP)
|
|
Re: Inventory Valuation with Fifo and Lifo [message #405134 is a reply to message #405111] |
Tue, 26 May 2009 08:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
shahzaib_4vip@hotmail.com
Messages: 410 Registered: December 2008 Location: karachi
|
Senior Member |
|
|
Thanks for your Reply Little Foot
Basically i have 3 Tables Which is
1) Opening
2) Purchase
3) Sale
The column of those Table are
[U][B]Purchase[/B][/U]
1)Item_no
2)item_name
3)purchase_date
4)purchase_qty
5)purchase_rate
6)Purchase_amount
Same With Sale and Opening
Now i create my Stock Report which is Look like this and the amount coming with opening rate I need Fifo and Lifo wise rate
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),
rate,
(Nvl(item2.quantity,0) + Nvl(purqt,0) - Nvl(salqt,0)) * Nvl(rate,0) AS "Stock Amount"
FROM item2,
(SELECT item_no_sale,
Sum(Nvl(quantity,0)) sq
FROM sale2
WHERE saledate BETWEEN Nvl(:D1,saledate) AND Nvl(:Date_from,saledate)
GROUP BY item_no_sale) s,
(SELECT item_no_pur,
Sum(Nvl(quantity,0)) q
FROM purchase2
WHERE purchase_date BETWEEN Nvl(:D1,purchase_date) AND Nvl(:Date_from,purchase_date)
GROUP BY item_no_pur) p,
(SELECT item_no_pur,
Sum(quantity) purqt
FROM purchase2
WHERE purchase_date < Nvl(:D1,purchase_date)
GROUP BY item_no_pur) pur,
(SELECT item_no_sale,
Sum(quantity) salqt
FROM sale2
WHERE saledate < Nvl(:D1,saledate)
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 Nvl(:Item_no,item_no) AND Nvl(:item_no_to,item_no)
AND catagory_no BETWEEN Nvl(:Catagory_from,catagory_no) AND Nvl(:catagory_to,catagory_no)
ORDER BY item2.item_no
/
Some one told me to use Push and Pop Procedure for Fifo and Lifo I don't have any idea amount Procedure and i am not create any procedure so if you know what is pop and push procedure and what will they work for Fifo and Lifo Inventory valuation then please let me know
Hope you understand
Regards
Shahzaib Ismail
|
|
|
Goto Forum:
Current Time: Mon Aug 19 22:58:17 CDT 2024
|