FrontAccounting forum — Items Cost in negative in inventory sales report

Re: Items Cost in negative in inventory sales report

null@example.com (boxygen) — Sat, 01 Jun 2019 08:14:46 +0000

The Code I have suggested in post#34 will affect many reports. So doing this may need to change reports. And also the previous data may need to be adjusted in stock_moves table.

Hence therefore a separate log idea is better i think.

Re: Items Cost in negative in inventory sales report

null@example.com (boxygen) — Thu, 30 May 2019 19:10:48 +0000

Further investigating this topic I found that the function defined at Line # 425 of /includes/db/inventory_db.inc function update_stock_move is not used anywhere in the system.

I have tried to help @zia why the cost is -ve. This might be helpful for others in this forum so I am attaching here.

This Image explains the affect of using Cost Update on the Average Cost of the Product

Re: Items Cost in negative in inventory sales report

null@example.com (boxygen) — Wed, 29 May 2019 21:40:31 +0000

Hello @joe, I found that there is already a Log System for Average Cost in FA in stock_moves table but that is not properly used with Stock Adjustments.

In the file /inventory/includes/db/items_adjust_db.inc if we modify code at Line # 103 as below then Stock_Moves Table will have complete log of Transaction Affecting the Average Cost.

$calculated_avg_cost = get_unit_cost($stock_id); //added by faisal
    add_stock_move(ST_INVADJUST, $stock_id, $adj_id, $location, //modified by faisal
        $date_, $reference, $quantity,$calculated_avg_cost, $standard_cost);

After Doing this /inventory/inquiry/stock_movements.php can be modified to show the columns as below

array_push($th, _("Date"), _("Detail"), _("Quantity In"), _("Quantity Out"), _("Quantity On Hand"),_("Price"), _("Avg Cost"));//Modified by faisal

and

qty_cell($after_qty, false, $dec);
    amount_cell($myrow["price"]); //added by faisal
    amount_cell($myrow["standard_cost"]); //added by faisal

Re: Items Cost in negative in inventory sales report

null@example.com (boxygen) — Mon, 27 May 2019 07:54:20 +0000

Fine, In the log some more fields are necessary but for that we may need to increase arguments in function. Like TransactionDate, TransactionType and TransactionRef or ID, User

Similarly the direct CostUpdate of any items shall also reflect in this Log.

Re: Items Cost in negative in inventory sales report

null@example.com (joe) — Mon, 27 May 2019 04:35:48 +0000

I have a meeting with Janusz medio June. We will, among other thngs, discuss this topic too.

Joe

Re: Items Cost in negative in inventory sales report

null@example.com (boxygen) — Mon, 27 May 2019 02:57:09 +0000

@joe, we can add this function to purchasing/includes/db/grn_db.inc. to create a log file in the company's folder for each item whenever the average cost is updated in the system for any reason.

This function (in my instance) also sends an Email Alert to BCC Email of the company.

function update_cost_log($new_material_cost, $curr_material_cost, $qty, $price, $stock_id, $qoh )
{
  global $path_to_root, $db_connections;
    $company = $db_connections[$_SESSION["wa_current_user"]->company]["name"]; //added by faisal
    $date = Today()." ".Now();// date("Y-m-d h:i:sa", $d);

  $tran_type = $qty > 0 ? "Stock IN" : "Stock Out";

  $log = "date=$date,  company=$company,  stock_id=".get_item($stock_id)['description'].",";
  $log .="Quntity On Hand=$qoh,";
  $log .= "Current Material Cost=$curr_material_cost,";
  $log .= "$tran_type=$qty,";
  $log .= "Price to Receive Stock=$price,";
  $log .= "Calculated Material Cost = $new_material_cost";

  file_put_contents(company_path()."/".$stock_id.'.log',$log.PHP_EOL,FILE_APPEND);

if (!get_company_pref('avg_cost_alert')) return;

      require_once($path_to_root . "/reporting/includes/class.mail.inc");

      $mail = new email("ABC", "smtp@abc.net");

          $mail->phpmailer->addAddress(get_company_pref('bcc_email'));

          $mail->subject("Average Cost Alert");
          $mail->text($log);
          $mail->send();
    }

This function can be called at Line # 74 in the above file as below

if ($curr_material_cost <> $material_cost)
        update_cost_log($material_cost, $curr_material_cost, $qty, $price_in_home_currency, $stock_id, $qoh); //by faisal

The same process can be repeated for manufacturing/includes/db/work_order_costing_db.inc

Re: Items Cost in negative in inventory sales report

null@example.com (boxygen) — Sun, 26 May 2019 15:36:36 +0000

@zia, first of all you are using very older version. At FA on daily basis bugs are identified and rectified. It may be very cumbersome for any senior member here to support you on an older version. I would recommend that to avoid any future problems you keep your FA instance updated to the latest repo.

Secondly you can send me your ERPs credentials via Forum Email. I will try to locate the problem.

Re: Items Cost in negative in inventory sales report

null@example.com (zia) — Sun, 26 May 2019 11:26:05 +0000

any update for my issue. @boxygen or any other expert.

Re: Items Cost in negative in inventory sales report

null@example.com (boxygen) — Sun, 26 May 2019 10:51:43 +0000

May be we can maintain a log file in the company's folder for each item. That Log file can be called in Item Setup to view history with Date and Time, User and Transaction affecting the Average Cost. The user shall be able to refresh that log file.

Re: Items Cost in negative in inventory sales report

null@example.com (joe) — Sun, 26 May 2019 09:31:58 +0000

Of course, but please help me finding where to fix this.

Joe

Re: Items Cost in negative in inventory sales report

null@example.com (boxygen) — Sun, 26 May 2019 07:00:51 +0000

@joe, this area of FA that average cost of the item get changes but there is no LOG maintained for such change shall be addressed in 2.5.

This has placed me at very critical situation sometimes when a client asks why this Average Cost arrives?

Re: Items Cost in negative in inventory sales report

null@example.com (zia) — Thu, 23 May 2019 05:49:00 +0000

any help guys.

Re: Items Cost in negative in inventory sales report

null@example.com (barbarian) — Wed, 22 May 2019 08:23:52 +0000

kvvaradha wrote:

@barbarian - he also don't know when the entries are created. Because all of them are auto created. This entries created by the update average cost method. This issue was fixed from 2.4 onwards. So you can compare code between the two version and get it

Oh, so it is auto created means direct supplier invoice feature?

Re: Items Cost in negative in inventory sales report

null@example.com (zia) — Wed, 22 May 2019 07:30:18 +0000

@kvvaradha,

if you allow me then i can share with you erp credentials then you can check, because i am stuck here and did every possible thing,

Re: Items Cost in negative in inventory sales report

null@example.com (kvvaradha) — Wed, 22 May 2019 07:24:29 +0000

There are two possibilities, either you have any entries left to void or the standard cost was not updated. Check once again from beginning to end and get solution for it