[RESOLVED] Need Help to create DB Tables of Inventory with FIFO valuation methods

[tonydsouza1987]

Only two users will be adding/updating Inventory. The remaining users will see the report or stock at the end of the day. I will take care to display the live inventory to two users, thats ok.

But my question was different.
Assume the below products are in Inventory table :
Juice Bottle $10 40Qty
Juice Bottle $12 20Qty

While consuming I want to display only Juice Bottle,$10,40Qty.
SO they they should not have the option to consume the second line i.e Juice Bottle $12 20Qty. This is a FIFO style valuation method. The first product must be consumed first. Can you give me a store procedure for this? This sproc should display all the items(unique Inventory No) discarding the second line in above case. The sproc should give the result as below(considering your example) :

ProductCode      Desc		 InvNo 	Price	  QtyInStock
1		      Juice Bottle	 1	        10	  40
2		      Milk		 2	        5	  15
3		      Eggs		 3	        2.50	  144

Please see above the line Juice Bottle,$12,20Qty is not shown. This should not show in the query result. And QtyInStock is the remaining qty.
I think I am asking you lot of questions, this is the last question. Please give me the sproc to do the same on your tables examples.
Thanks for your valuable support and time....