Topic: Inventory Valuation Report showing 0 (zero) on Unit Cost

Hello,

I facing all the item showing zero on Unit Cost.
I'm using Indonesian Currency,
My Preferences :
Date Format:DDMMYYY   
Date Separator:    -
Thousand Separator: .
Decimal Separator: ,

I 'm not sure what may causes, but when I created new company using USA standar Account, it works. Is it related to currency?

Re: Inventory Valuation Report showing 0 (zero) on Unit Cost

Yes it is related to the currency. Try to increase the currency decimals in user preferences, f.i. 6.

Joe

Re: Inventory Valuation Report showing 0 (zero) on Unit Cost

joe wrote:

Yes it is related to the currency. Try to increase the currency decimals in user preferences, f.i. 6.

Joe

Unfortunately it doesn't help. The problem persist.
Any ideas?

Re: Inventory Valuation Report showing 0 (zero) on Unit Cost

Maybe even more decimals.

Joe

Re: Inventory Valuation Report showing 0 (zero) on Unit Cost

No Joe. It is not the decimals.

Here I found just now.
I created new company and start with empty data.
I inputed Direct Supplier Invoice using date 27-11-2017.
When I access Inventory Valuation Report with end date 27-11-2017 it shows 0 (zero) on Unit Cost.
But when I put End Date 28-11-2017 or 30-11-2017 the Unit Cost shows the value.

Don't you think if I put 27-11-2017 The unit cost allready should have value?

Re: Inventory Valuation Report showing 0 (zero) on Unit Cost

It makes me confused, I tried using new COA USA, and try the same test using USD as home currency. It works fine with The end date 27-11-2017. Hmmm?

Re: Inventory Valuation Report showing 0 (zero) on Unit Cost

I agree that this sounds strange. Maybe someone else can help us investigate this. I have only heard about problems with normal 4 decimals when huge currency rates exists.

/Joe

Re: Inventory Valuation Report showing 0 (zero) on Unit Cost

The sql in the function getAverageCost() defined in the file reporting/rep301.php (Inventory Valuation Report) is like:

SELECT move.*, IF(ISNULL(supplier.supplier_id), debtor.debtor_no, supplier.supplier_id) person_id
FROM 1_stock_moves move
     LEFT JOIN 1_supp_trans credit ON credit.trans_no=move.trans_no AND credit.type=move.type
     LEFT JOIN 1_grn_batch grn ON grn.id=move.trans_no AND 25=move.type
     LEFT JOIN 1_suppliers supplier ON IFNULL(grn.supplier_id, credit.supplier_id)=supplier.supplier_id
     LEFT JOIN 1_debtor_trans cust_trans ON cust_trans.trans_no=move.trans_no AND cust_trans.type=move.type
     LEFT JOIN 1_debtors_master debtor ON cust_trans.debtor_no=debtor.debtor_no
WHERE stock_id=102
  AND move.tran_date < '2017-11-27' 
  AND standard_cost > 0.001 
  AND qty <> 0 
  AND move.type <> 16 -- ST_LOCTRANSFER
ORDER BY tran_date;

This means that when the purchase invoice is made on 2017-11-27, the sql above computes the average cost to just before the said date and not including it. If a subsequent date is used for the query, then all is well (save for the irrational value at times not obeying the decimal limit).

@joe: must we correct line 65 in it:

            AND move.tran_date < '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND move.type <> ".ST_LOCTRANSFER;

to be

            AND move.tran_date <= '$to_date' AND standard_cost > 0.001 AND qty <> 0 AND move.type <> ".ST_LOCTRANSFER;

so that it takes into consideration all transaction up until the end of the day and not to just before the date?

The decimals issue can be corrected by appropriately formatting the return value at the end of the said function or where it it used in line 211 in the report although line 229 uses it without assignment when $details is present. The culprit is line 357 in includes/current_user.inc for irrational numbers with large number of decimal places when the value of $dec becomes 14. It is best hard coded to 2.

Post's attachments

Screenshots.zip 72.4 kb, 1 downloads since 2017-12-08 

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

Re: Inventory Valuation Report showing 0 (zero) on Unit Cost

The default value of the precision parameter in php.ini on a 32 bit system is 14 even when absent. In some installs it comes preset at 17. All float values that are a result of a fraction that has recurring decimals ad infinitum will generally truncate off at ini_get('precision')-3 decimals, to make up for a:
1. sign,
2. single integer to the left of the decimal point and
3. the decimal point itself.

Hence the line 356 of includes/current_user.inc:

        if ($len > $dec)

should be:

        if ($len > $dec && $len < ini_get('precision')-3)

@joe: can commit both fixes (decimals and report sql)

Re: Inventory Valuation Report showing 0 (zero) on Unit Cost

Ok, I will.

/Joe

Re: Inventory Valuation Report showing 0 (zero) on Unit Cost

Fixed files committed. Even report 308 was having the same issue.

/Joe