Topic: Tax included supplier’s price leaves GRC hanging

This error occurs when dealing with large quantity credit purchase from a supplier whose price includes tax.
Suppose a supplier “X” whose price includes tax and a P.O. for 5000 pcs of item “X” @ $4.35.

Receive the whole of the P.O. quantity. (i.e. process POD)
Book the supplier’s invoice.
Now, go and check on Trial balance or Balancesheet or GL enquiry how it all worked out.
You’ll find out that inventory is properly received. A/P and pertaining sales tax are registered and surprisingly, there is uncleared balance on GRC account. Why?
During the POD processing, the POD, instead of simply picking up details from the P.O., it is coded to engage itself in to developing a new inventory rate, net of tax and rounded to "user defined" decimal places. And that rounding difference multiplied by large qty such as 5000 pcs leaves Goods Received Clearing account with uncleared balance of, say, $14.
SOLUTION
Whoever can go through the codes for POD and stop it from rounding the rate it developed, solves the problem.

Re: Tax included supplier’s price leaves GRC hanging

There is a constant defined as FLOAT_COMP_DELTA - see if it affects your values. Check it's appearance in purchasing/includes/db/suppliers_db.inc under function get_supplier_details() for the sql statement's WHERE clause:

"AND ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc > ".FLOAT_COMP_DELTA

A similar code fragment is in purchasing/includes/db/supp_trans_db.inc in function get_sql_for_supplier_inquiry() apart from other files.

Re: Tax included supplier’s price leaves GRC hanging

FLOAT_COMP_DELTA  doesn't seem to have an effect (in fact left me with a blank web page at a point smile )
I managed to successfully receive items without left-over GRC balances by setting my decimal places to 10 digits. Once again confirming that it is the setting in preferences that affects inventory valuation during POD. I insist we nullify the effect of   user_price_dec() during p.o. receiving.

4 (edited by apmuthu 10/12/2015 06:01:35 am)

Re: Tax included supplier’s price leaves GRC hanging

Please try to replace line 106 in purchasing/includes/purchasing_db.inc:

    $price = round($price * $data['conversion_factor'], user_price_dec());

with

    $price = $price * $data['conversion_factor'];

along with the default setting in preferences for the decimals and revert. Also state if there was any other place where the said function user_price_dec() should not be used.

The user_price_dec(), it appears, should be used only for display purposes and the highest precision should be used while storing data. This, of course, is limited to the number of decimal places available in the table's field where it is stored.

If this were to work, then all places where INSERT / UPDATE / REPLACE are used with this function, should be re-examined.

Re: Tax included supplier’s price leaves GRC hanging

I'm afraid that will just remove rounding from purchasing prices applied in p.o and supplier's invoice. We need to remove rounding from taxfree_charge_price.
You see rounding can exist behind the screen but it should round the right items. In our case what fa does upon p.o. receive is:
ENTERED QTY X (ROUND($4.5/1.05,2))=INVENTORY VALUE
But what it should do is:
ENTERED QTY X ($4.5/1.05)=ROUND(INVENTORY VALUE,2)
Or
It should stop rounding at all as the functions in display would round it anyway.

6 (edited by apmuthu 10/12/2015 05:50:48 pm)

Re: Tax included supplier’s price leaves GRC hanging

@Petros: In file taxes/tax_calc.inc the functions get_tax_free_price_for_item() and get_tax_for_items() use the formula for calculation you stated.

Please see if these are the places you would like to change.

Rounding is done for consistent storage value in the db to persist across version changes of the db server.

Other places where such code is available are listed below.

In file purchasing/includes/db/supp_payment_db.inc lines 114 to 116 when $rate <> 0:

        $supp_amount = round($amount / $rate, user_price_dec());
        $supp_discount = round($discount / $rate, user_price_dec());
        $supp_charge = round($charge / $rate, user_price_dec());

In file purchasing/includes/db/invoice_db.inc lines 129 to 133:

    foreach ($taxes as $n => $taxitem)
    {
        $taxes[$n]['Value'] =  round2($taxitem['Value'], user_price_dec());
        $tax_total += $taxes[$n]['Value'];
    }

and lines 137 to 143:

    $item_added_tax = 0;
    if (!$supp_trans->tax_included)
    {
        $taxes = $supp_trans->get_taxes($supp_trans->tax_group_id);
        foreach ($taxes as $n => $taxitem)
            $item_added_tax += isset($taxitem['Override']) ? $taxitem['Override'] : round2($taxitem['Value'], user_price_dec());
    }

In file purchasing/includes/supp_trans_class.inc lines 112 to 117:

        foreach ($this->grn_items as $ln_itm) 
        {
            $items[] = $ln_itm->item_code;
            $prices[] =round( ($ln_itm->this_quantity_inv * $ln_itm->chg_price),
                user_price_dec());
        }

lines 160 to 162:

        foreach ($this->grn_items as $ln_itm)
            $total += round(($ln_itm->this_quantity_inv * $ln_itm->taxfree_charge_price($tax_group_id, $tax_group)),
             user_price_dec());

lines 176 to 177:

        foreach ($this->grn_items as $ln_itm)
            $total += round($ln_itm->this_quantity_inv * $ln_itm->chg_price, user_price_dec());

In file purchasing/includes/po_class.inc lines 141 to 144:

        foreach ($this->line_items as $ln_itm) {
            $items[] = $ln_itm->stock_id;
            $prices[] = round($ln_itm->price * ($receival ? $ln_itm->receive_qty : $ln_itm->quantity),  user_price_dec());
        }

lines 164 to 184:

    function get_trans_total() {
        
        $total = 0;
        $dec = user_price_dec();

        foreach ($this->line_items as $ln_itm) {
            $items[] = $ln_itm->stock_id;
            $value = round($ln_itm->quantity * $ln_itm->price, $dec);
            $prices[] =$value;
            $total += $value;
        }

        if (!$this->tax_included ) {
            $taxes = get_tax_for_items($items, $prices, 0, $this->tax_group_id,
            $this->tax_included,  $this->tax_group_array);

            foreach($taxes as $tax)
                $total += round($tax['Value'], $dec);
        }
        return $total;
    }

In file purchasing/po_entry_items.php lines 465 to 470:

            foreach($cart->line_items as $key => $line) {
                $inv->add_grn_to_trans($line->grn_item_id, $line->po_detail_rec, $line->stock_id,
                    $line->item_description, $line->receive_qty, 0, $line->receive_qty,
                    $line->price, $line->price, true, get_standard_cost($line->stock_id), '');
                $inv->ov_amount += round2(($line->receive_qty * $line->price), user_price_dec());
            }

Re: Tax included supplier’s price leaves GRC hanging

The culprit was found in taxes\tax_calc.inc like you hinted, I turned off the rounding function from all the lines (except the ones about shipping cost) and went back to my p.o. receive.

Yes! the inventory was valued correctly and once the supplier's invoice is processed, GRC account is NIL.
What's surprising again is that the purchase module continue to be flawless, while what was disrupting p.o receive was caught in taxes. It's easy to think why. A tax is a multiplier, it's a rate, and is not wise to round it as it's multiplied product is expected to be reconciled against physical documents. (The supplier's bill in our case). If anything we have to round, should be the product and not the tax nor the rate. Hence, turning it off from selected lines in tax_calc.inc makes sense. 


''Rounding is done for consistent storage value in the db to persist across version changes of the db server.''  I am in support of the existence of rounding functions here and there, if I can add, application optimization is another reason. The round codes in all php files you listed above, their purpose is obvious and are essential. However, like I said in a previous post, it should round results and not multiplicative factors such as taxes and rates.

Re: Tax included supplier’s price leaves GRC hanging

Please post your version of the modified file that works with standard settings in preferences so that it's effect may be studies in the sales and other portions it may possibly affect.

@joe / @itronics: your insights please.

Re: Tax included supplier’s price leaves GRC hanging

I think that removing roundings is not the right way to solve the problem. All the GL postings are made with defined (usually 1 cent) accuracy, and this should be preserved. I guess the problem lies somewhere in calculations/roundings order. I will try investigate it.
Janusz

Re: Tax included supplier’s price leaves GRC hanging

The issue has been fixed.
The unbalanced postings on clearing account were result of different net value calculations made in invoice and GRN processing. Now in both cases net value for goods received/invoiced is calculated from overall line tax included value.

Janusz

Re: Tax included supplier’s price leaves GRC hanging

@apmuthu
https://drive.google.com/file/d/0B8DlmARQ8isNc3Nuc0dWODRhYUk/view?usp=sharing

The Tax_calc.inc file that brought harmony between me, my received inventory and GRC account is in the link.


Cheers.

12 (edited by apmuthu 10/13/2015 09:05:32 pm)

Re: Tax included supplier’s price leaves GRC hanging

@Petros: Please try @itronics latest fix and revert with your findings.

@itronics: Please note that the same deprecated function in your fix is present in purchasing/includes/supp_trans_class.inc and check whether the same should be done there and the old function removed in it's entirety everywhere. The deprecated taxfree_charge_price() method/function is not being used in any standard extension.

Re: Tax included supplier’s price leaves GRC hanging

I confirm "Latest fix" by @itronics does the job.

Thanks

Re: Tax included supplier’s price leaves GRC hanging

@apmuthu
Yes, I'm aware of this supp_trans method. But in fact the purchasing module needs wider redesign anyway, so I don't want to introduce more changes in 2.3 than absolute minimum.
Thank you all for pointing this problem out smile.
Janusz