Items Cost in negative in inventory sales report (Page 2 of 2)

boxygen · 05/26/2019 07:00:51 am

boxygen
Senior Member
Offline

From: Pakistan, Karachi
Registered: 03/03/2015
Posts: 651

Re: Items Cost in negative in inventory sales report

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

www.boxygen.pk

joe · 05/26/2019 09:31:58 am

joe
Administrator
Offline

Registered: 03/15/2007
Posts: 5,085

Re: Items Cost in negative in inventory sales report

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

Joe

boxygen · 05/26/2019 10:51:43 am

boxygen
Senior Member
Offline

From: Pakistan, Karachi
Registered: 03/03/2015
Posts: 651

Re: Items Cost in negative in inventory sales report

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.

www.boxygen.pk

zia · 05/26/2019 11:26:05 am

zia
Member
Offline

From: UAE, Dubai
Registered: 06/02/2018
Posts: 23

Re: Items Cost in negative in inventory sales report

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

boxygen · 05/26/2019 03:36:36 pm

boxygen
Senior Member
Offline

From: Pakistan, Karachi
Registered: 03/03/2015
Posts: 651

Re: Items Cost in negative in inventory sales report

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

www.boxygen.pk

boxygen · 05/27/2019 02:57:09 am

boxygen
Senior Member
Offline

From: Pakistan, Karachi
Registered: 03/03/2015
Posts: 651

Re: Items Cost in negative in inventory sales report

@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

www.boxygen.pk

joe · 05/27/2019 04:35:48 am

joe
Administrator
Offline

Registered: 03/15/2007
Posts: 5,085

Re: Items Cost in negative in inventory sales report

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

Joe

boxygen · 05/27/2019 07:54:20 am

boxygen
Senior Member
Offline

From: Pakistan, Karachi
Registered: 03/03/2015
Posts: 651

Re: Items Cost in negative in inventory sales report

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.

www.boxygen.pk

boxygen · 05/29/2019 09:40:31 pm

boxygen
Senior Member
Offline

From: Pakistan, Karachi
Registered: 03/03/2015
Posts: 651

Re: Items Cost in negative in inventory sales report

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

www.boxygen.pk

boxygen · 05/30/2019 07:10:48 pm

boxygen
Senior Member
Offline

From: Pakistan, Karachi
Registered: 03/03/2015
Posts: 651

Re: Items Cost in negative in inventory sales report

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

Post's attachments

NegCostStockMove.png 454.5 kb, 1 downloads since 2019-06-04

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

www.boxygen.pk

boxygen · 06/01/2019 08:14:46 am

boxygen
Senior Member
Offline

From: Pakistan, Karachi
Registered: 03/03/2015
Posts: 651

Re: Items Cost in negative in inventory sales report

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.

www.boxygen.pk

Items Cost in negative in inventory sales report (Page 2 of 2)

Posts: 26 to 36 of 36

26 Reply by boxygen 05/26/2019 07:00:51 am

Re: Items Cost in negative in inventory sales report

27 Reply by joe 05/26/2019 09:31:58 am

Re: Items Cost in negative in inventory sales report

28 Reply by boxygen 05/26/2019 10:51:43 am

Re: Items Cost in negative in inventory sales report

29 Reply by zia 05/26/2019 11:26:05 am

Re: Items Cost in negative in inventory sales report

30 Reply by boxygen 05/26/2019 03:36:36 pm

Re: Items Cost in negative in inventory sales report

31 Reply by boxygen 05/27/2019 02:57:09 am

Re: Items Cost in negative in inventory sales report

32 Reply by joe 05/27/2019 04:35:48 am

Re: Items Cost in negative in inventory sales report

33 Reply by boxygen 05/27/2019 07:54:20 am

Re: Items Cost in negative in inventory sales report

34 Reply by boxygen 05/29/2019 09:40:31 pm

Re: Items Cost in negative in inventory sales report

35 Reply by boxygen 05/30/2019 07:10:48 pm

Re: Items Cost in negative in inventory sales report

36 Reply by boxygen 06/01/2019 08:14:46 am

Re: Items Cost in negative in inventory sales report

Posts: 26 to 36 of 36