• From: India, Chennai
  • Registered: 12/23/2017
  • Posts: 56

Topic: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

I am a small manufacturing firms has BoM loaded to all of its products. Where Inventory Planning Report are very critical in nature.
Company has over 6000 stock id and nearly 3000 Manufacturable stock ids. Whenever we tried to generate these two report, System gets hanged and Gatway Timeout is persisting.
Because Stock Check alone is executine over 3 Lakhs queries to bring the report to users. Sometime is very more as well.
Company has 4CPU System with 16GB RAM. But No use as of now.
Now Instruction to the company, "DON'T TOUCH INVENTORY REPORTS"!!!
Could please suggest us any solutions for this issue?

Regards,
Bharathiraja Kaliyamoorthy

geesyscare's Website

  • From: Colombia
  • Registered: 12/29/2014
  • Posts: 629

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

Have a look at giving MySQL more resources - increase query memory, etc. Its not really on-topic here, but google will give you lots of help.
Just found a a post by apmuthu which deals with just this issue and there's a link to a page for tuning MySQL.
You may want to also google about the WAMP/MAMP/LAMP you are using.

Have a look at apmuthu's post, if still problems reply with version numbers of FA, PHP, MySQL and WAMP type and version

The FrontAccounting Wiki(Manual, examples, tips, setup info, links to accounting sites, etc) https://frontaccounting.com/fawiki/
  • From: India, Chennai
  • Registered: 12/23/2017
  • Posts: 56

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

No, @apmuthu's post is not helping me to overcome this problem. we are using php 7.2, nginix 1.17.
Hope, This is severe bug in FA core.
For loop in the report is endless. it goes on checking all the years and all the transaction (including BoM) of each items.
Oh my god. Completely frustrated with inventory reports.
Hope, I have been taken guidance from @notrinos | @kvvaradha.
We are still finding possibilities to fix this bugs!
Kindly give me any other idea please!

Regards,
Bharathiraja Kaliyamoorthy

geesyscare's Website

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

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

Try to scale down to PHP 5.6 or lower and see if it helps.

apmuthu's Website

  • From: India, TN, Karur
  • Registered: 05/16/2014
  • Posts: 1,195

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

Brother. Again here. As we already spoken about it. We have large Amount of data. So it makes more queries again and again to get both reports results. And we have two options
1. Introduce a new table to record the available stock , ordered , waiting to process, and in manufacturing.  It would need to change the program to run it every movement of inventory.
2. To make a cron job and prepare this report in server's free time like morning 2 am to 6 am.


Or we can increase the server config and reduce unnecessary tables to give some extra cache to run

Subscription service based on FA
HRM CRM POS batch Themes

kvvaradha's Website

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

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

rep302.php - Inventory Planning Report - Line 52
rep303.php - Stock Check Sheet - Line 164

The above reports have a function getTransactions() which uses item.description in the GROUP BY clause - this assumes that each item.stock_id can have different item.description(s) - may even try to remove the offending Line making sure it is unique or make an index for it with:

ALTER TABLE `1_stock_master` ADD INDEX `ItemDetailIdx` (`description`);

apmuthu's Website

  • From: India, Chennai
  • Registered: 12/23/2017
  • Posts: 56

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

@apmuthu sir,
We have tried to fix the issues as per your guidance.
There are number Queries got reduced a bit while running the report.
But We could not fix the issue as of now.  We are not able generate these reports.

Regards,
Bharathiraja Kaliyamoorthy

geesyscare's Website

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

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

What is the size of your gzipped sql backup from FA?
Maybe it can be simulated in a quarantined cloud container and provide the reports and study the sqls that need to be optimised.

We need to first try it on say PHP 5.3.3 / MySQL 5.1 on a test Debian 6.0 container. This can be tedious and time consuming besides the discerned attention to technical detail. Is it worth it?

apmuthu's Website

  • From: Colombia
  • Registered: 12/29/2014
  • Posts: 629

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

@apmuthu, the problem is on line 161 when the function 'get_demand_asm_qty' is called. I verified this by commenting out the line and with approx 3.5k items in the inventory the report ran in less than 5 seconds, where as before uncommented it timed out.

get_demand_asm_qty line 104 in includes/db/manufacturing_db.inc calls another function stock_demand_manufacture also in the same file (line 54) this from what I understand cycles for up to 10 reiterations per item

The FrontAccounting Wiki(Manual, examples, tips, setup info, links to accounting sites, etc) https://frontaccounting.com/fawiki/
  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

The sql construct starting at line 107 refers to an alias: Demmand. Is it used and is the mis-spelling intended? The said column does not appear to be explicitly named anywhere in usage.

The only indexes worth creating are:

ALTER TABLE 0_sales_orders ADD INDEX FromStkLocIdx (from_stk_loc); 
ALTER TABLE 0_stock_master ADD INDEX MBFlagIdx (mb_flag);

The function stock_demand_manufacture is recursive in nature to cycle through all BOMs that may have other BOMs as part of itself. Check that the BOMs do not have any parent entities as part of the downline.

apmuthu's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

I understand this discussion.
The reason for implementing the routines with recursivity is that the assembled items can have a BOM with another assembled item and so on.

Joe

  • From: India, Chennai
  • Registered: 12/23/2017
  • Posts: 56

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

Can we create a seperate table to record demand, Quantity on Hand and Supp. order with respect to stock id and location id?
I have just started using FA. But i am afraid about its scalability and data handling capabilities.
Please advice!

Regards,
Bharathiraja Kaliyamoorthy

geesyscare's Website

  • From: India, Chennai
  • Registered: 12/23/2017
  • Posts: 56

Re: Stock Check Sheet | Inventory Planning Reports has serious Bugs.

kvvaradha wrote:

Brother. Again here. As we already spoken about it. We have large Amount of data. So it makes more queries again and again to get both reports results. And we have two options
1. Introduce a new table to record the available stock , ordered , waiting to process, and in manufacturing.  It would need to change the program to run it every movement of inventory.
2. To make a cron job and prepare this report in server's free time like morning 2 am to 6 am.


Or we can increase the server config and reduce unnecessary tables to give some extra cache to run

Yes Brother. Agreed. Hope, Your idea will solve our problem now. We have to implement this concept and see the performance after implementation.
But What would be the permanent solutions to this issue?

Regards,
Bharathiraja Kaliyamoorthy

geesyscare's Website