Search options (Page 10 of 26)

@itronics I have once again gone through your post#1. If I am not mistaken you are planning to use

1. avg_cost (old standard_cost) as storage for material cost before transaction and
2. unit_cost (old price) as storage for material_cost after transaction

??.

If yes, then this would not be OK. Although we can retrieve price from related tables for Sale and Purchase transactions but this is not true for Stock Adjustment Transactions.

So whether or not you change the name of price column, it shall contain the unit_cost that is affecting the avg_cost. So If an Item A is purchase @ $20 then supp_invoice_items.unit_price will contain $20 as well as unit_cost column in stock_moves will also contain $20.

I hope I have clarified my view point.

function update_stock_move($type, $trans_no, $stock_id, $cost)
{
    $sql = "UPDATE ".TB_PREF."stock_moves SET standard_cost=".db_escape($cost)
            ." WHERE type=".db_escape($type)
            ."    AND trans_no=".db_escape($trans_no)
            ."    AND stock_id=".db_escape($stock_id);
    db_query($sql, "The stock movement standard_cost cannot be updated");
}

This function on includes/db/inventory_db.inc is not called from anywhere

@itronics,  yes database consistency requires some update queries to run.

I agree with you. I will check in detail later

I want to suggest that in 2.5 we shall include this modification done by @anoopmb

https://frontaccounting.com/punbb/viewtopic.php?id=7525

This is very useful approval system. It requires some database changes that we can do in 2.5. Currently it is applied only to GL module but later on we can gradually shift its application to all other modules.

Attendance with time in and time out.
Csv import for time in and time out

@barbarian, great work. I will test it. I really needed a good reporting from HTML to PDF.

@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.

@joe,
This commit needs one more commit in the function add_stock_adjustment_item on /inventory/includes/db/items_adjust_db.inc as follows

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.

Hello @BarryHavenga, check post # 90 by @BraathWaate