FrontAccounting forum — Inventory Planning Report

Re: Inventory Planning Report

null@example.com (geesyscare) — Wed, 02 Mar 2022 03:57:54 +0000

Inventory Planning report does not calculate the raw materials qty. (BoM in Manufacturing) based pending sales order. Tried with Advanced Manufacturing as well. We could find in neither cust. order qty. nor in demand qty. is there any solutions to fix this issue?

Re: Inventory Planning Report

null@example.com (kvvaradha) — Thu, 15 Jul 2021 11:19:51 +0000

Brother It looks like the description and long description affects and also the no of query which runs for this report is huge.

@Joe we need to find an alternative way for it.

Re: Inventory Planning Report

null@example.com (geesyscare) — Thu, 15 Jul 2021 06:38:34 +0000

We have Unique "stock id" not "Description". Stock ID has assigned with auto increment. We have over 30 unique categories and over 13k Stock IDs. FA is running of 4CPU, 16GB RAM based server. We are not able use inventory planning and stock check sheet report as on date.
it is possible to sort/group it by "stock id"?

Re: Inventory Planning Report

null@example.com (apmuthu) — Tue, 05 May 2020 15:24:23 +0000

rep302.php is the Inventory Planning Report.

There are only 2 main SQLs in it in the functions:

getTransactions()
getPeriods()

Do some single stepping and check the value of the $sql variable in them and see if the tables need any (non primary key) indexes to speed it up.

Hope you have the following Unique Indexes:

`stock_category`.`description`
`stock_master`.`description`

The former is there in my default install and the latter may need to be inculcated.

How many transactions are you querying and hope your PHP.ini file has adequate memory allocated.

@joe: Adding an Unique Index for `stock_master`.`description` may be in order.

Re: Inventory Planning Report

null@example.com (geesyscare) — Mon, 04 May 2020 21:08:45 +0000

Inventory Planning is executing few lakhs of queries and system is getting hanged for next 15 minutes. Horrible.
Afraid of Inventory reports in FA.

Re: Inventory Planning Report

null@example.com (apmuthu) — Thu, 03 May 2018 02:06:15 +0000

@joe: Lines 50 and 52 can be removed in reporting/rep302.php as there can be only one description for one id for both category_id and stock_id fields.

Also Line 77 filters on ST_CUSTCREDIT (11) and ST_CUSTDELIVERY (13) instead of on ST_SUPPRECEIVE (25) and ST_CUSTDELIVERY (13) and the need to take care of transactions pertaining to ST_MANUISSUE (28) and ST_MANURECEIVE (29) with the signs being taken care of as well.

Further, lines 38 and 39:

            IF(move.stock_id IS NULL, '', move.loc_code) AS loc_code,
            SUM(IF(move.stock_id IS NULL, 0, move.qty)) AS qty_on_hand

can be re-coded as

            COALESCE(move.loc_code, '') AS loc_code,
            SUM(COALESCE(move.qty, 0)) AS qty_on_hand

Re: Inventory Planning Report

null@example.com (poncho1234) — Thu, 03 May 2018 00:45:13 +0000

To produce the report did you filter by catergory or location?

Something else that may help is go to Items & Inventory -> Inventory Item Movement select the item you have a problem with, select the time frame and post the screen shot of the result

Re: Inventory Planning Report

null@example.com (apmuthu) — Wed, 02 May 2018 17:58:31 +0000

You can still insert file write statements to spit out the inspection points / variable values. Read the Wiki.

Inventory Planning Report is reporting/rep302.php.

Re: Inventory Planning Report

null@example.com (poncho1234) — Wed, 02 May 2018 12:10:18 +0000

Hi, you can obtain a backup copy of the database as described here in the wiki

You need to give us more info for us to help you -

apmuthu wrote:

Upload attachments somewhere and provide link here. Check MySQL / apache error logs for hints.

Re: Inventory Planning Report

null@example.com (Speghatti) — Wed, 02 May 2018 11:01:43 +0000

Hi, I'm working on @BigFunHappens's system and is trying to solve the same problem as what seahawk enquired about.

Here is an example of the problem: https://imgur.com/a/LBIxjKN

The image shows an example of the inventory planning report as is shown in our system.
The values inside of the red border are incorrect.

Many items function as intended like the 1st item in the example report, but some items have the same problem as the 2nd item.

I have tried to access the MySQL logs but it seems as if we cannot view the logs with our current web host package. Could this be true?

Re: Inventory Planning Report

null@example.com (apmuthu) — Wed, 11 Jan 2017 19:00:54 +0000

Please read @joe's post above (4th post). @randr and @seahawk have not provided their sql dumps for analysis.

Rebuilding indexes in FA is quite simple - drop the index and recreate it from the schema in the Chart of Accounts used.

Re: Inventory Planning Report

null@example.com (BigFunHappens) — Wed, 11 Jan 2017 18:31:19 +0000

Hi, this is my first post, although I have been using FA for a few years now. Absolutely outstanding system. Seahawk's post was about my system when I asked him to try and solve it, and I see Randr has the same problem.
Only in Inventory Planning Report the monthly unit sales is 0 for about 25% of the stock items. The average monthly sales is thus also 0, although it shows the correct QOH, Supp Ord and Cust Ord. The Sugg Ord is then also not correct.
The correct unit sales are available from inventory movements, but it is quite a job to extract everything manually. The other reports seem to be all OK.
Some of the older DOS programs had a "rebuild indexes" option which solved this type of problem, or am I going in the wrong direction?

Re: Inventory Planning Report

null@example.com (apmuthu) — Tue, 26 Jul 2016 02:04:24 +0000

Is it possible that the said report ignores invoices not in the period of 3/4 months? Can you provide a small backup where you can show - how it is - and - how it should be - scenarios - preferably in the en_US-demo.sql instance of a Training Co.?

Re: Inventory Planning Report

null@example.com (randr) — Mon, 25 Jul 2016 18:59:50 +0000

Joe, I am ok with the calculation, I just can't figure out why some sales appear on the report and some do not.

Re: Inventory Planning Report

null@example.com (apmuthu) — Tue, 19 Jul 2016 03:19:56 +0000

@joe: Thanks for the explanation. Wiki-ed it.