How to maintain stock table in inventory

Hi,

Regarding the inventory system i have below structure, according to below structure i want to maintant stock table

how to maintant stock table.

Mstitem

Itmno numeric

Itmcode nvarchar

Itmdescription nvarchar

Itmunit nvarchar

Itmminqty numeric

Material request

TrnMR

Mrno numeric

Mrnumber varchar

Mrdate

Mrlocation

TrnMrdetails

Mrno numeric

Mritmno numeric

Mrqty numeric

Then purchase order

TrnPo

Pono numeric

Ponumber varchar

Podate

Posupplier

Podiscount

Trnpodetails

Pono numeric refn from main

PoMrno numeric

Poitmno numeric

PoQty numeric

Pounitprice numeric

When material receive

TrnGrn (good receive note)

Grnno numeric

Grnnumber nvarchar

Grndate

TrnGrndetails

Grnno numeric

Grnpono numeric

Grnitmno numeric

GrnMrno numeric

GrnQty numeric

When good receive then add in stock master.

MstStock table

Grnno numeric

Itmno numeric

Pono numeric

Mrno numeric

Stockqty numeric

Stockavgunitprice numeric

Stock table will be only

Itmno numeric

Stockqty numeric

Stockavgunitprice numeric

What my question is above is two type of Stock Tables which one should i use

First one is when i recieve material then im adding rows according to GRN and update StockQty and Stockavgunitprice which is one is from GRN

other stock table is will get the itemno from stock and added according to GRN

for example if

itemno StockQty Unitprice

000001 4 3

and if i receive same material from GRN Qty 3 then

itemno StockQty Unitprice

000001 7 4

which one is better.

becoz im want the receive qty as average not first in first out.

Thanks

Basit.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Basit Khan9y ago
for more details i put the below details.

What my question is in my inventory system how to maintain the stock table. i have two type of idea to maintain stock table. One stock table have the following field. ItemNo, GRNNo, PONo, MrNo, StockQty and StockUnitprice. In this table when i receive the Material then it will add the field as new row in stock, or another stock table is ItemNo, StockQty and StockUnit price here when receive the material then it will check the stockmaster and if the item available in stock the add/update the qty and update avgprice im asking which one is good. thanks Basit.