1 (edited by Petros 10/09/2015 03:21:00 pm)

Topic: material_cost doubles itself, strangely

I noticed that when a produced items is sold out, and on a zero 'qoh' a new batch is produced, stock master table takes almost the double of the total material_cost per unit the production consumed while labor_cost and overhead_cost lines are properly distributed. I've checked this on fa2.3.10 to fa2.3.24 and it persists.
The double figure that went to material_cost of the item in-turn provides wrong IV report and unfortunately, cost of good sold calculates itself from this exaggerated material_cost. Hence, the problem not only disrupts some reports we can avoid using but also the GL itself when the item is sold.
Can someone help me on this please, I'm tired of manually uploading actual material costs in the db tables, and I'm sure that's not how FA is made.

2 (edited by apmuthu 10/06/2015 05:58:42 pm)

Re: material_cost doubles itself, strangely

Which FA v2.3.24 are you using? Take the Post Release, the v2.3.24+ which is on the GitHub Repo Master.

Please let us know if the problem persists.

Please state what version of PHP/MySQL and what Chart of Accounts you are using and on what platform/version.

Re: material_cost doubles itself, strangely

Hello again,
Your prompt reply motivated me to do the test all over again. I'll report every bit here:
1. Launched my UniServer Zero XI version 11.0.3, php 5.4.3 mysql 5.0.10 on mozilla 40.0.3 (win 8.1 64x pc)
2. Downloaded fa_master from your link ... fa installation on
3. Chart of account= standard new co. American, Lang = Eng
4. Created cash customer, supplier, 4 test items: i1, i2, i3 and i4:
      i1 and i2 are purchased types, inv account= 1510 inventory
      i3 is a semi processed item, inv account= 1530 WIP, assembled in 1530 WIP
      i4 is finished good, inv account = 1540 FG, assembled in same
5. BOM: each i1+ each i2+$3 Labor cost+$2 Overhead cost = 1 unit of i3
              each i3 +$3 Labor cost+$2 Overhead cost = 1 unit of i4
6. First purchase made i1 bought for $5, i2 bought for $10 and sent to production: a$20 cost i3 is made successfully and is reported as a work-in-process item on balancesheet as desired.
7. Further processed i3 to i4 now having $25 worth finished item. sold it for $33 and P/L statement correctly shows my $8 profit while B/S report shows zero inventories.
8. Second round purchase made for i1 & i2, same price as above. Produced i3 right away (for $3+$2 additional costs as above). Expecting a $20 cost i3 in all respective reports...
*B/sheet correctly shows $20 stock in progress, Costed inv report also correct
*Inventory valuation shows i3 @ $30 cost
*Went to my db, stock_master table, i3 row material_cost column shows $30
9. There's no point in continuing further as i3 will be picked up to the next production as a $30 worth material. Which then renders loss.

Re: material_cost doubles itself, strangely

It looks like your PHP 5.4+ may be the problem. FA 2.3 was designed for PHP 5.3.

Now getting to the troubleshooting, place inspection points as in the wiki and enable the various debug flags in the config.php and see what fails where.

Provide a test backup (upload the sql somewhere and provide link here) of the fa install after all your above transactions and a list of menu navigations to replicate your error. It will save time to go thru the data entry screens.

Re: material_cost doubles itself, strangely


I've left you a read me txt file for details.
Best of luck.
Write me back if anything is missing.

6 (edited by apmuthu 10/09/2015 12:55:11 pm)

Re: material_cost doubles itself, strangely

Did a sql_trail for the Direct purchase Invoice (turned on in the config.php file just before the "Process Invoice" button was clicked) and there were 144 SQL queries!
SELECT = 100
UPDATE =  18

It appears that each line item (stock_id) rate in the purchase Direct Invoice form populates the stock_master.material_cost and purch_data.price (for the specific supplier_id) fields even if it already exists and has not changed. It must certainly be here that something is going wrong.

The following SQL run on the FA db would yield the necessary sequence of SQLs that were executed:

SELECT id , `result`, msg, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(`sql`, '&#039;', "'"),'&lt;','<'),'&gt;','>'),"\n",""),"\t",""),"\r","") AS SQL_used FROM `0_sql_trail`

The file purchasing/includes/db/grn_db.inc has the function update_average_material_cost() that provides the material cost for computations in other files. Must see if there is any anomaly here - missing scope/vars, etc.

The value of the $_POST variable just before Work Order Add just before executing line 264 in manufacturing/work_order_entry.php is:

    [wo_ref] => WO11
    [type] => 0
    [_stock_id_edit] => I3
    [stock_id] => I3
    [StockLocation] => DEF
    [quantity] => 1
    [date_] => 09/10/2015
    [Labour] => 3.00
    [cr_lab_acc] => 1060
    [Costs] => 2.00
    [cr_acc] => 1060
    [memo_] => 
    [RequDate] => 
    [ADD_ITEM] => Add Workorder
    [_focus] => wo_ref
    [_modified] => 0
    [_token] => a8c1a6693552df346a1fd03d90b1287e8c32cd276dfc60712b91f2154ef04926
    [_random] => 700059.4337549664

Only the last 2 elements change on WO Addition.

Post's attachments

Purchase Direct Invoice SQL Trail.xls 49.5 kb, 3 downloads since 2015-10-09 

You don't have the permssions to download the attachments of this post.

Re: material_cost doubles itself, strangely

I kindly disagree with the procedure being applied. I believe the purchase module is completely flawless. That's why FA in a non manufacturing business is error free. Even in a manufacturing business, as long as the product being produced has qoh <>0 it works perfectly. Problem arises WHEN it saves a production of an item that is qoh=0. The area that should be trailed is the one that sends material_cost value to stock_master table, material_cost column, upon saving a successful work order.

8 (edited by apmuthu 10/09/2015 06:42:09 pm)

Re: material_cost doubles itself, strangely

You're right. Further investigation reveals something to do with NULL not being zero as the php code computes the material cost and adds to it the existing value.... just before inserting the Work Order cost entries.

Attached is the sql trail started for the Add Work Order button click alone - 82 queries!

In manufacturing/includes/db/work_orders_quick_db.inc, the function add_work_order_quick() is the one being executed in our case.

In manufacturing/includes/db/work_orders_db.inc, line 48 may be changed from:

            $material_cost += $m_cost;


            $material_cost = $m_cost;

and let us see if it makes a difference. Otherwise there could be some DELTA in subtraction causing the "==" to fail.

Post's attachments

Work Order Assembly Entry.xls 33 kb, 1 downloads since 2015-10-09 

You don't have the permssions to download the attachments of this post.

9 (edited by apmuthu 10/09/2015 07:23:32 pm)

Re: material_cost doubles itself, strangely

Yes, that is the solution!

When $qoh is 0, only the new qty should dictate the material cost and not add to it.

@joe: please correct it in the stable version:

manufacturing/includes/db/work_orders_db.inc line 48.

The patch which includes proper initial sorting of Work Orders as well is:

--- old/manufacturing/includes/db/work_orders_db.inc    Sun May 10 02:37:36 2015
+++ new/manufacturing/includes/db/work_orders_db.inc    Sat Oct 10 00:40:29 2015
@@ -45,7 +45,7 @@
     if ($qoh + $qty != 0)
         if ($qoh == 0) // 27.10.2014 apmuthu and dz.
-            $material_cost += $m_cost;
+            $material_cost = $m_cost;
             $material_cost = ($qoh * $material_cost + $qty * $m_cost) /    ($qoh + $qty);
@@ -555,6 +555,7 @@
         $sql .= " AND workorder.required_by < '$Today' ";
+    $sql .= " ORDER BY workorder.id DESC";
     return $sql;
Post's attachments

56180be310bae.pdf 2.6 kb, 3 downloads since 2015-10-09 

You don't have the permssions to download the attachments of this post.

Re: material_cost doubles itself, strangely

Sure, I will do this asap.

Thank you for investigating this.


Re: material_cost doubles itself, strangely

My repo has been committed with this fix.

Re: material_cost doubles itself, strangely

Release 2.3.24 stable has been fixed and the file is commited.

You can download the file /manufacturing/includes/db/work_orders_db.inc here.

You should replace the file on your server setup.