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

[tonydsouza1987]

Heloo Guys..
I am working on a Inventory project for catering projects. The products are flour, rice, juice, Meat, Fruits, Vegetables, etc. Here the products are consumed/issued.
We purchase items at difference price at different times.
For example :
Juice Bottle bought @ $10 of 200 units.(On Jan 1st 2012)
Juice Bottle bought @ $12 of 500 units.(On Jan 10th 2012)
Juice Bottle bought @ $15 of 300 units.(On Jan 20th 2012)

I want to use FIFO inventory valuation method on this. I can’t use any other methods like Weighted Avg.
Using FIFO : At this point the consumption of 200 units should be of value $10, again if 100 units are consumed then the consumed value should be $12,etc.
How to build database tables on this approach and how to link the tables?
I am using SQL Server 2005 with VB6. At the end of month, I need to display Weekly/Monthly Consumption reports.

I have made following tables so far :

tblProductsMaster :
Product Code(PK), Product Desc, Qty, UOM(FK), Min Stock Lvl, Reorder Lvl, Category, Price.
Every product is unique record in tblProductsmaster.

tblUOM :
UOMcode(PK),UOMName.

tblConsumption :
ConsumptionNo, RequestedBy, RequestedDate, ApprovedBy, ApprovedDate > Header Fields
Product(FK), Product Desc, UOM, Qty, Price > Line Items

Products are entered in Products master having different prices. I don’t need purchase tables, since I directly take the products in tblProductsMaster.
I also need to implement Base UOM, Alternate UOM, Conversion Factor. This is secondary. How to make tables and link them? I don’t know much about creating database tables and linking. Please someone help me……………. It would be very grateful if you help on this matter.

[szlamany]

Well - your tables have problems - you have duplicates of fields in your tables.

Here's a SQL script I've created that you can execute in SQL SERVER Management Studio to create tables that might fit your needs better (first time you run it it will error on the DROP DATABASE - but that's fine - works every time after that)

Use Master
Go
Drop Database TestInventory
Go

Create Database TestInventory
Go
Use TestInventory
Go
Set NoCount On

Create Table ProductMaster_T
	(ProductCode	int not null identity
	,ProductDesc	varchar(100) not null
	,MinStockLvl	int not null
	,ReorderLvl		int not null
	,Constraint PKProductMaster
		Primary Key (ProductCode)
	)

Create Table Inventory_T
	(InventoryNo	int not null identity
	,ProductCode	int not null
	,Price			money not null
	,Qty			int not null
	,Constraint PKInventory
		Primary Key (InventoryNo)
	)

Create Table Consumption_T
	(ConsumptionNo	int not null identity
	,InventoryNo	int not null
	,Qty			int not null
	,Constraint PKConsumption
		Primary Key (ConsumptionNo)
	)

Insert into ProductMaster_T values ('Juice Bottle',10,20)
Insert into ProductMaster_T values ('Milk',5,10)
Insert into ProductMaster_T values ('Eggs',12,144)
Insert into ProductMaster_T values ('Oatmeal',25,50)

Insert into Inventory_T values (1,10,40)
Insert into Inventory_T values (2,5,15)
Insert into Inventory_T values (3,2.5,144)
Insert into Inventory_T values (2,6,10)
Insert into Inventory_T values (3,2.9,144)
Insert into Inventory_T values (1,12,20)

Select * From ProductMaster_T Order by ProductCode
Select PM.*,IV.* From Inventory_T IV
	Left Join ProductMaster_T PM on PM.ProductCode=IV.ProductCode
	Order by IV.InventoryNo

Insert into Consumption_T values (1,-10)
Insert into Consumption_T values (1,-20)
Insert into Consumption_T values (2,-5)
Insert into Consumption_T values (3,-12)
Insert into Consumption_T values (1,-5)
Insert into Consumption_T values (1,-5)
Insert into Consumption_T values (6,-10)
Insert into Consumption_T values (3,-48)
Insert into Consumption_T values (2,-5)
Insert into Consumption_T values (2,-5)
Insert into Consumption_T values (3,-12)
Insert into Consumption_T values (6,-10)
Insert into Consumption_T values (3,-24)

Then run these two queries

Select PM.*,IV.*,CS.*
	,IV.Qty+IsNull((Select Sum(CS2.Qty) From Consumption_T CS2 Where CS2.InventoryNo=CS.InventoryNo and CS2.ConsumptionNo<=CS.ConsumptionNo),0) "Remaining"
	From Consumption_T CS
	Left Join Inventory_T IV on IV.InventoryNo=CS.InventoryNo
	Left Join ProductMaster_T PM on PM.ProductCode=IV.ProductCode
	Order by CS.ConsumptionNo
Select PM.*,IV.*,CS.*
	,IV.Qty+IsNull((Select Sum(CS2.Qty) From Consumption_T CS2 Where CS2.InventoryNo=CS.InventoryNo and CS2.ConsumptionNo<=CS.ConsumptionNo),0) "Remaining"
	From Consumption_T CS
	Left Join Inventory_T IV on IV.InventoryNo=CS.InventoryNo
	Left Join ProductMaster_T PM on PM.ProductCode=IV.ProductCode
	Order by PM.ProductCode,CS.ConsumptionNo

[tonydsouza1987]

Thank you szlamany.
The code you gave works fine.
While consuming(consume form), I want to display a list of products from inventory table. Say for example, 'Juice Bottle' has two entries in Inventory table. One with price $10(40 qty) and another with price $12(20 Qty). In consume form I want only to display only first entry i.e $10(40 Qty) to consume, if all the 40 quantities are comsumed then I want to display $12(20).
While consuming the user selects the product from a listview , In this listview the product should not show as below :
Juice Bottle $10 40Qty
Juice Bottle $20 20Qty
If it is shown as above then the user may select any two and consume it.I want to show as below :
juice Bottle $10 40Qty
After the quantity for $10 is zero, then it should show as below :
juice Bottle $12 20Qty

The code you gave is correct to check the Inventory, but while consuming I want the older product consume first, then new product, and so on...(FIFO style)... How to do this? This is done in SQL Server or VB6?

[szlamany]

That's a lot of questions...

Is this a school project or a real application?

Is this a multi-user system?

[tonydsouza1987]

This project is for catering company.This is a multi-user LAN based vb6 application.

[szlamany]

The final two queries I gave you were just for showing how to join the tables I created - using a sub-query to show the "amount" left of a particular PRODUCT-at-a-PRICE-point...

If this is a multi-user system you have some issues you must realize up front...

UI on all PC's will not be at the same "state" as different users consume your inventory...

Ultimately a STORED PROCEDURE in the SQL DB will consume the product - and must be able to consume what is "really left at that moment" and not what the user saw on the UI...

...or error out if the condition still does not exist as the user saw it

I prefer the second - which would mean you error out - causing the UI to re-draw the current condition and then the user re-takes from that more current value set.

[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....

[szlamany]

What code have you written already? Show some VB code for your UI so we all can discuss how to link that to the database access...

Personally I see no reason to have the user enter 40 items at $10 and 5 items at $12.

They want to take 45 items - as long as the screen shows them that there are 40 @ $10 and 20 @ $12 then they realize that the hit will be against two different price points.

But regardless - I can't help you develop a STORED PROCEDURE unless I see how you are going to have the user enter the "request" for product.

Show some code.

[tonydsouza1987]

I have not written any code yet. Writing code will be easy when I have the recordset in hand and show it in a listview. The idea is like this. When a user clicks a button, a usercontol(with listview) will be displayed loading the recordset of inventory.When the user double clicks, the product will be chosen and added to consume list(listview). When done adding items to consume list, the user clicks the SAVE button to save consume form. While saving again I will check the database stock to see if ther is stock. This is the idea. If u have any better idea u can share with me.

Reason:
OK I agree that the user may select any one. You are right. I was thinking something wrong. User may select a $10 Line or a $12 line. Thats right.
But I want only two lines of 'Juice Bottle', not 6 lines from your last query. That query is below:

Select PM.*,IV.*,CS.*
	,IV.Qty+IsNull((Select Sum(CS2.Qty) From Consumption_T CS2 Where CS2.InventoryNo=CS.InventoryNo and CS2.ConsumptionNo<=CS.ConsumptionNo),0) "Remaining"
	From Consumption_T CS
	Left Join Inventory_T IV on IV.InventoryNo=CS.InventoryNo
	Left Join ProductMaster_T PM on PM.ProductCode=IV.ProductCode
	Order by PM.ProductCode,CS.ConsumptionNo

[szlamany]

OK I agree that the user may select any one. You are right. I was thinking something wrong. User may select a $10 Line or a $12 line. Thats right.
But I want only two lines of 'Juice Bottle', not 6 lines from your last query.

Do you always want TWO LINES of any given product??

[tonydsouza1987]

Do you always want TWO LINES of any given product??

If there are three diff prices then I need three lines, 4 diff prices then 4 lines, etc... Is this possible? I dont mean always TWO LINES of any given product..

[tonydsouza1987]

Hello szlamany, please can you give the query...?

[szlamany]

That query showed 6 rows for each product because it was showing the "ALREADY CONSUMED" item detail.

This query shows just the PRODUCT joined to the INVENTORY

Select PM.*,IV.*
	From Inventory_T IV 
	Left Join ProductMaster_T PM on PM.ProductCode=IV.ProductCode
	Order by PM.ProductCode,IV.InventoryNo

[tonydsouza1987]

This works fine... Thank you very much...