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.