需要帮助来创建具有FIFO评估方法的库存数据库表 [英] Need Help to create DB Tables of Inventory with FIFO valuation methods

问题描述

伙计们..
我正在为餐饮项目开发库存项目.产品是面粉，大米，果汁，肉，水果，蔬菜等.在这里消费/发行产品.
我们在不同的时间以不同的价格购买商品.
例如:
果汁瓶@ 200单位的$ 10购买.(2012年1月1日)
以500单位的$ 12购买果汁瓶.(2012年1月10日)
以300美元的价格购买15美元的果汁瓶.(2012年1月20日)

我想对此使用FIFO库存评估方法.我不能使用加权平均等其他方法.
使用FIFO:此时200个单位的消耗量应为$ 10，如果再次消耗100个单位，则消耗量应为$ 12，依此类推.
如何使用这种方法构建数据库表以及如何链接表?
我正在将SQL Server 2005与VB6一起使用.在月底，我需要显示每周/每月消费报告.

到目前为止，我已经制作了以下表格:

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

产品会以不同的价格输入到产品主数据中.我不需要购买表，因为我直接将产品带到tblProductsMaster中.
我还需要实现基本UOM，备用UOM，转换因子.这是次要的.如何制作表格并链接它们?我对创建数据库表和链接了解不多.请有人帮助我……………….如果您对此事有所帮助，将不胜感激.

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.

推荐答案

10个，共200个单位.(2012年1月1日)
果汁瓶购买了@

10 of 200 units.(On Jan 1st 2012)
Juice Bottle bought @

500个单位中的12个.(2012年1月10日)
果汁瓶购买了@

12 of 500 units.(On Jan 10th 2012)
Juice Bottle bought @

300个单位中的15个.(2012年1月20日)

我想对此使用FIFO库存评估方法.我不能使用加权平均等其他方法.
使用FIFO:此时，200个单位的消耗应该是有价值的

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

这篇关于需要帮助来创建具有FIFO评估方法的库存数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！