• Registered: 12/25/2007
  • Posts: 2,448

Topic: Planned changes in stock_moves table structure in FA 2.5

In FA 2.4 table stock_moves was designed as log of changes in inventory, but due to some inconsistencies in table's fields usage in the code, when some costing problem appears it is not easy to track where the problem arose. Therefore we plan to change stock_moves structure in next major release, to make it better also for inventory cost tracking purposes.

Following changes are planned:
1. 'reference' field removed
Rationale: this field is both not used and double redundant to refs table and reference fields in respective transaction tables.
2. `standard_cost` changed to `avg_cost` - store for item average cost (stock_master.material_cost)  before transaction
Rationale: while this field is not strictly necessary, it make tracking average cost changes easier. BTW the standard_cost name is just historical artefact not  related to what is stored here now;
3. `price` superseded by `unit_cost` - store for unit_cost in transaction
Rationale: currently price field is used for cost calculations, while this is the cost which is needed finally. Price is stored in transaction lines records, and always can be retrieved if needed.

Finally planned stock_moves table structure in FA 2.5:

CREATE TABLE `0_stock_moves` (
  `trans_id` int(11) NOT NULL AUTO_INCREMENT,
  `type` smallint(6) NOT NULL DEFAULT '0',
  `trans_no` int(11) NOT NULL DEFAULT '0',
  `stock_id` char(20) NOT NULL DEFAULT '',
  `loc_code` char(5) NOT NULL DEFAULT '',
  `tran_date` date NOT NULL DEFAULT '0000-00-00',
  `qty` double NOT NULL DEFAULT '1',
  `unit_cost` double NOT NULL DEFAULT '0',
  `avg_cost` double NOT NULL DEFAULT '0', 
  
  PRIMARY KEY (`trans_id`),
  KEY `type` (`type`,`trans_no`),
  KEY `Move` (`stock_id`,`loc_code`,`tran_date`)
) ENGINE=InnoDB;

All comments are welcome.
J.

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 654

Re: Planned changes in stock_moves table structure in FA 2.5

`standard_cost` changed to `avg_cost` - store for item average cost (stock_master.material_cost)  before transaction

I think is the store of average cost after transaction. the resulting average cost of the item due the unit_cost being used in the transaction.

www.boxygen.pk

boxygen's Website

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 654

Re: Planned changes in stock_moves table structure in FA 2.5

function update_stock_move($type, $trans_no, $stock_id, $cost)
{
    $sql = "UPDATE ".TB_PREF."stock_moves SET standard_cost=".db_escape($cost)
            ." WHERE type=".db_escape($type)
            ."    AND trans_no=".db_escape($trans_no)
            ."    AND stock_id=".db_escape($stock_id);
    db_query($sql, "The stock movement standard_cost cannot be updated");
}

This function on includes/db/inventory_db.inc is not called from anywhere

www.boxygen.pk

boxygen's Website

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 654

Re: Planned changes in stock_moves table structure in FA 2.5

on includes/db/inventory_db.inc at line # 475 we shall add this condition

if ($type!=ST_LOCTRANSFER)

before the following function call

update_average_material_cost($row["supplier_id"], $row["stock_id"],
                    $unit_cost, -$row["qty"], sql2date($row["tran_date"]), false, "Void Stock Move # $type_no and type = $type_no");

Rationale

There is no reason for updating average cost while Voiding Stock Move due to Location Transfer. The inventory is neither gaining not loosing here.

If we don't do this then Avg Unit Cost is adversely affected. Because the stock_moves table doesn't contain any price for Location Transfer hence $unit_cost parameter passed to update_average_material_cost is ZERO so the resulting weighted average is not correct at the end of transaction.

www.boxygen.pk

boxygen's Website

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 654

Re: Planned changes in stock_moves table structure in FA 2.5

@itronics I have once again gone through your post#1. If I am not mistaken you are planning to use

1. avg_cost (old standard_cost) as storage for material cost before transaction and
2. unit_cost (old price) as storage for material_cost after transaction

??.

If yes, then this would not be OK. Although we can retrieve price from related tables for Sale and Purchase transactions but this is not true for Stock Adjustment Transactions.

So whether or not you change the name of price column, it shall contain the unit_cost that is affecting the avg_cost. So If an Item A is purchase @ $20 then supp_invoice_items.unit_price will contain $20 as well as unit_cost column in stock_moves will also contain $20.

I hope I have clarified my view point.

www.boxygen.pk

boxygen's Website

  • From: Pakistan, Karachi
  • Registered: 03/03/2015
  • Posts: 654

Re: Planned changes in stock_moves table structure in FA 2.5

If you agree with my post#5 then

This condition

$types = array(ST_SUPPCREDIT, ST_SUPPRECEIVE);
            if (in_array($type, $types))
                $unit_cost = $row["price"];
            else
                $unit_cost = $row["standard_cost"];

in function void_stock_move in includes/db/inventory_db.inc will be changed to

$unit_cost = $row["price"];

Because now this will work for all transaction types to fetch the unit_cost affecting avg_cost.

www.boxygen.pk

boxygen's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Planned changes in stock_moves table structure in FA 2.5

The said function update_stock_move() in Post #3 above is not used in any of the known extensions either now.

apmuthu's Website