I agree with you. I will check in detail later

Finally to maintain database consistency on existing installations I have reverted the changes in add_stock_adjustment_item() and related fixes in reports rep301, rep308.  We will address all this in forthgoing 2.5 release. We plan to change stock_moves table structure to better upport inventory cost tracking. All comments regarding this feature are welcome  in this thread.

As far as I understand, after revertion all should still work as expected, as suggested changes where mostly technical. If I'm wrong, please do not hesitate to reopen the thread and explain the lasting problems (if any) once again.
J.

The recent changes on 2019-06-17 (1, 2) have been assimilated in my repo separating the actual changes and the whitespace synch.

Please try again. For some reason you were not upgraded internally as Senior Member. Even if it was shown.

Joe

Slightly update of add_stock_adjustment_item by @itronics. Committed to stable repo. Only keeping the parameter names.

/Joe

I have tested your changes in /inventory/includes/db/items_adjust_db.inc, /reporting/rep301.php and /reporting/rep308.php.
And the test passed ok.
These changes has been committed to stable repo.
Do you have more files to change, @boxygen, because we plan to ship a minor release 2.4.7 next week?
Major changes will wait to the upcoming release 2.5 unstable that also are about to ship.

/Joe

I think I can follow you now. I will just test this tomorrow to ensure that it is right. If the reports take the domestic price from the underlying ex_rate values in the grn and sub credit transaction lines.

Joe

You can do either, or but I think better keep the one in post # 11. I have tested it. It has changed conditions as well as some removal of logical bugs and

Post #8 should be committed otherwise domestic_price will not calculate correctly.

With my finally suggested codes in post#8 and post#11 domestic_price is calculating correctly the average cost.

Regards.

Should we change back to the former functions, get_domestic_price, in rep301 and rep308, but wich these changed rows?
And post #8 should be changed? and the wrongly calculated domestic price?

/Joe

function get_domestic_price needs some more adjustments. But it requires changes in post#8 to be committed first.

function get_domestic_price($myrow, $stock_id)
{
    if ($myrow['type'] == ST_SUPPRECEIVE || $myrow['type'] == ST_SUPPCREDIT || $myrow['type'] == ST_INVADJUST)
     {
        $price = $myrow['price'];
        if ($myrow['person_id'] > 0)
        {
            // Do we have foreign currency?
            $supp = get_supplier($myrow['person_id']);
            $currency = $supp['curr_code'];
            $ex_rate = $myrow['ex_rate'];
            $price *= $ex_rate;
        }
    }
    else
        $price = $myrow['standard_cost']; //pick standard_cost for sales deliveries

    return $price;
}

Explanation:

1. We need price Value only for ST_SUPPRECEIVE, ST_SUPPCREDIT AND ST_INVADUST. For Sales Deliveries we need the standard_cost for Average cost calculation in rep301, rep308.

2. $price *= $ex_rate is currently $price /= $ex_rate which is a logical mistake.

3. We can pick Exchange Rate directly from Supplier Transaction for GRN and Supplier Credit Note. For this we need some more changes in rep301 and rep308

Following partial code in the SQL Query shall be replaced with

IF(ISNULL(supplier.supplier_id), debtor.debtor_no, supplier.supplier_id) person_id

This code

supplier.supplier_id person_id, IF(ISNULL(grn.rate), credit.rate, grn.rate) ex_rate

Explanation
1. We need person_id only for suppliers to get exchange rate of the transaction in get_domestic_price function
2. ex_rate is directly picked from either GRN or Supplier Credit

I have tested all this after creating a fresh item and executing all type of transactions on it as in this image

There is one understanding that ST_CUSTCREDIT transactions will always pick the prevailing unit_cost.

Regards.

Regarding database update for inventory adjustment, we will have to add this in upgrade procedure to release 2.5. We do not have updating procedure for minor releases, so implementing this in 2.4.* would require run the query outside user's awareness. Skilled user suffered with this problem can run the query manually anyway.

J.

function add_stock_adjustment_item($adj_id, $stock_id, $location, $date_, $reference,
    $quantity, $price, $memo_)
{
    $mb_flag = get_mb_flag($stock_id);
    if (is_service($mb_flag))
    {
        display_db_error("Cannot do inventory adjustment for Service item : $stock_id", "");
    }
    update_average_material_cost(null, $stock_id, $price, $quantity, $date_);
    if (is_fixed_asset($mb_flag)) {
        $sql = "UPDATE ".TB_PREF."stock_master SET inactive=1
            WHERE stock_id=".db_escape($stock_id);
        db_query($sql,"The inactive flag for the fixed asset could not be updated");
    }
  $standard_cost = get_unit_cost($stock_id); //added by faisal
    add_stock_move(ST_INVADJUST, $stock_id, $adj_id, $location,
        $date_, $reference, $quantity, $standard_cost, $price);
    $inv_value = $price * $quantity;
    $adj_value = $price * -($quantity);
    if (is_fixed_asset($mb_flag)) {
        // get the initial value of the fixed assset.
        $row = get_fixed_asset_move($stock_id, ST_SUPPRECEIVE);
        $inv_value = $row['price'] * $quantity;
        $adj_value = (-($row['price']) + $price) * $quantity;
    }
    if ($price > 0 || is_fixed_asset($mb_flag))
    {
        $stock_gl_codes = get_stock_gl_code($stock_id);
        add_gl_trans_std_cost(ST_INVADJUST, $adj_id, $date_,
            $stock_gl_codes['adjustment_account'], $stock_gl_codes['dimension_id'], $stock_gl_codes['dimension2_id'], $memo_, $adj_value  );
        add_gl_trans_std_cost(ST_INVADJUST, $adj_id, $date_, $stock_gl_codes['inventory_account'], 0, 0, $memo_, $inv_value);
    }
    if (is_fixed_asset($mb_flag)) {
        // Additional gl entry for fixed asset.
        $grn_act = get_company_pref('default_loss_on_asset_disposal_act');
        add_gl_trans_std_cost(ST_INVADJUST, $adj_id, $date_, $grn_act, 0, 0, $memo_, ($price * -($quantity)));
    }
}

FOR LEGACY DATA ACCURACY

In order to keep rep301 and rep308 workable with older data than this commit one update query needs to be executed to bring standard_cost values in the price field for all transactions through inventory_adjustment.

UPDATE stock_moves SET price=standard_cost WHERE type=17

WORKAROUND FOR COST UPDATE

@itronics for logging Cost Update tracking in stock_moves I have a workaround solution. May be you endorse it. Just sharing the idea. If approved I can work on code.

We can execute 2 Inventory Adjustments per location for that particular item when cost update is executed.

#1. This will bring the QOH to ZERO even for -ve inventory on the prevailing cost price in the system.
#2. This will bring the QOH on the same position either +ve or -ve at the New Cost give through Cost Update.

With this workaround we will be able to bring correct results in rep301 and rep308 as well.

EXCHANGE RATE AFFECT

In this function get_domestic_price the exchange rate factor is applied when person_id > 0. But for Inventory Adjustments person_id is always 0

However you are right that there shall be a column to record Exchange Rate of that transaction so that any change in exchange_rates shall not affect the transaction.

We will refine the logging feature of stock_moves in the next days, however the changes will go probably to unstable branch. for next 2.5 release.

J.