• tom
  • Senior Member
  • Offline
  • From: USA, FL, Tampa
  • Registered: 03/24/2009
  • Posts: 326

Topic: Manufacture BOM

I have ported my db from 2.4.6 to 2.4.18

I have a number of products are are assembled with a BOM and when I have all the items required I get the following error

<b>DATABASE ERROR :</b> The work requirements issued quantity couldn't be updated<br>error code : 1048<br>error message : Column 'unit_cost' cannot be null<br>sql that failed was : UPDATE 0_wo_requirements SET
                unit_cost = (units_issued*unit_cost+0)/(units_issued+0),
                units_issued = units_issued + '0'
            WHERE id = '8652'<br><br><br>

I am am able to enter other work orders

in wo_requirements the highest id is 8616 and the SQL refers to 8652

strange

I am using PHP 7.4.33 and mysql 8.0

tom's Website

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

Re: Manufacture BOM

The file manufacturing/includes/db/work_order_requirements_db.inc has the offending code at lines 66-*74:

function update_wo_requirement_issued($id, $quantity, $cost)
{
    $sql = "UPDATE ".TB_PREF."wo_requirements SET 
                unit_cost = (units_issued*unit_cost+".$quantity*$cost.")/(units_issued+".$quantity."),
                units_issued = units_issued + ".db_escape($quantity)."
            WHERE id = ".db_escape($id);

    db_query($sql, "The work requirements issued quantity couldn't be updated");
}

As 'unit_cost' is computed and assigned in the sql statement whilst being part of  the RHS as well kindly look at if it was available initially in the first place.

apmuthu's Website

  • tom
  • Senior Member
  • Offline
  • From: USA, FL, Tampa
  • Registered: 03/24/2009
  • Posts: 326

Re: Manufacture BOM

>  wo_requirements the highest id is 8616 and the SQL refers to 8652

The SQL refers to id = 8652 and the highest id in the table is 8616, what could cause the code to refer to a non-existent record?

tom's Website

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

Re: Manufacture BOM

It is possible that many IDs may be missing due to deletion but the AUTO_INCREMENT value may have advanced.

apmuthu's Website

5 Reply by tom (edited by tom 08/08/2024 11:32:37 pm)

  • tom
  • Senior Member
  • Offline
  • From: USA, FL, Tampa
  • Registered: 03/24/2009
  • Posts: 326

Re: Manufacture BOM

Humm... I did delete a handfull of closed fiscal years, but other than that not much.

I'll look at a known good backup and see what that table looks like..

Might I need to recreate the BOMs used to manufacture  that product?

tom's Website

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

Re: Manufacture BOM

The basic FA has just 80 tables.
Make a fresh install of latest FA and manually import differences taking care to adjust the Primary Key and it's references.
Then copy over the relevant images/files renaming them for ID related differences in the new dispensation.

apmuthu's Website

  • tom
  • Senior Member
  • Offline
  • From: USA, FL, Tampa
  • Registered: 03/24/2009
  • Posts: 326

Re: Manufacture BOM

I think I am getting closer to seeing what the issue is.

On the old site if I open the database in phpmyadmin the wo_requirements table has 8000 records and on the imported site only 300

I also get an error from myphpadmin when browsing that table that looks like a php issue

I am going to try mysqldump and see if I get all 8000 records for that table

--- error below

Warning in ./libraries/sql.lib.php#613
count(): Parameter must be an array or an object that implements Countable

Backtrace

./libraries/sql.lib.php#2128: PMA_isRememberSortingOrder(array)
./libraries/sql.lib.php#2062: PMA_executeQueryAndGetQueryResponse(
array,
boolean true,
string 'gotgrit_fa24',
string '0_wo_requirements',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
string '',
string './themes/pmahomme/img/',
NULL,
NULL,
NULL,
string 'SELECT * FROM `0_wo_requirements`',
NULL,
NULL,
)
./sql.php#221: PMA_executeQueryAndSendQueryResponse(
array,
boolean true,
string 'gotgrit_fa24',
string '0_wo_requirements',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
string '',
string './themes/pmahomme/img/',
NULL,
NULL,
NULL,
string 'SELECT * FROM `0_wo_requirements`',
NULL,
NULL,
)

tom's Website

  • tom
  • Senior Member
  • Offline
  • From: USA, FL, Tampa
  • Registered: 03/24/2009
  • Posts: 326

Re: Manufacture BOM

I figured out the problem!

I have some items in my BOM that have a 0 quantity this seems to no longer work.

If I remove the q=0 items I can assemble them again!

tom's Website

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

Re: Manufacture BOM

@joe: Should we allow 0 qty items in BOM to exist?

apmuthu's Website