From 9fc19175befbc44cfaf9f26e509c4e2562497479 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Wed, 20 Aug 2008 21:51:14 +0000 Subject: [PATCH] Bug [0000037] Price diff and deliveries between po receive and supp invoice. (Again) --- CHANGELOG.txt | 8 +++++++- includes/db/inventory_db.inc | 25 +++++++++++++++++++++++-- purchasing/includes/db/grn_db.inc | 8 ++++++-- purchasing/includes/db/invoice_db.inc | 19 +++++++------------ 4 files changed, 43 insertions(+), 17 deletions(-) diff --git a/CHANGELOG.txt b/CHANGELOG.txt index 2f52c72b..d0d52fb3 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -19,8 +19,14 @@ Legend: ! -> Note $ -> Affected files +20-Aug-2008 Joe Hunt +# Bug [0000037] Price diff and deliveries between po receive and supp invoice. (Again) +$ /includes/db/inventory_db.inc + /purchase/includes/db/grn_db.inc + /purchase/includes/db/invoice_db.inc + 20-Aug-2008 -# Voiding PO GRN's could be dangerous, if deliveries have been done. Eliminating it. Use item adjustment with 0 price. +# [0000041] Voiding PO GRN's could be dangerous, if deliveries have been done. Eliminating it. Use item adjustment with 0 price. $ /admin/db/voiding_db.inc # Fixing a minor bug in grn_db.inc $ /purchasing/includes/db/grn_db.inc diff --git a/includes/db/inventory_db.inc b/includes/db/inventory_db.inc index 562c8d1b..46a16b41 100644 --- a/includes/db/inventory_db.inc +++ b/includes/db/inventory_db.inc @@ -1,6 +1,6 @@ 0) + { + $sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves + WHERE stock_id='$stock_id' + AND type=$exclude + AND tran_date = '$date'"; + + $result = db_query($sql, "QOH calulcation failed"); + $myrow2 = db_fetch_row($result); + if ($myrow2 !== false) + $myrow[0] -= $myrow2[0]; + } return $myrow[0]; } @@ -78,7 +90,7 @@ Function get_stock_gl_code($stock_id) // $price - in $person_id's currency function add_stock_move($type, $stock_id, $trans_no, $location, - $date_, $reference, $quantity, $std_cost, $person_id=null, $show_or_hide=1, + $date_, $reference, $quantity, $std_cost, $person_id=0, $show_or_hide=1, $price=0, $discount_percent=0, $error_msg="") { // do not add a stock move if it's a non-inventory item @@ -101,6 +113,15 @@ function add_stock_move($type, $stock_id, $trans_no, $location, return db_insert_id(); } +function update_stock_move_pid($type, $stock_id, $from, $to, $pid, $cost) +{ + $from = date2sql($from); + $to = date2sql($to); + $sql = "UPDATE ".TB_PREF."stock_moves SET standard_cost=$cost WHERE type=$type + AND stock_id='$stock_id' AND tran_date>='$from' AND tran_date<='$to' AND person_id = $pid"; + db_query($sql, "The stock movement standard_cost cannot be updated"); +} + //-------------------------------------------------------------------------------------------------- function get_stock_moves($type, $type_no, $visible=false) diff --git a/purchasing/includes/db/grn_db.inc b/purchasing/includes/db/grn_db.inc index bb24300a..756c972f 100644 --- a/purchasing/includes/db/grn_db.inc +++ b/purchasing/includes/db/grn_db.inc @@ -13,7 +13,12 @@ function update_average_material_cost($supplier, $stock_id, $price, $qty, $date, $result = db_query($sql); $myrow = db_fetch($result); $material_cost = $myrow['material_cost']; - $qoh = get_qoh_on_date($stock_id, null, $date); + if ($adj_only) + $exclude = 13; + else + $exclude = 0; + $qoh = get_qoh_on_date($stock_id, null, $date, $exclude); + if ($qoh + $qty <= 0) $material_cost = 0; else @@ -69,7 +74,6 @@ function add_grn(&$po, $date_, $reference, $location) $order_line->standard_cost, $order_line->receive_qty, $order_line->price); /* Update location stock records - NB a po cannot be entered for a service/kit parts */ - add_stock_move(25, $order_line->stock_id, $grn, $location, $date_, "", $order_line->receive_qty, $order_line->standard_cost, $po->supplier_id, 1, $order_line->price); diff --git a/purchasing/includes/db/invoice_db.inc b/purchasing/includes/db/invoice_db.inc index 84d205a2..014e5ce8 100644 --- a/purchasing/includes/db/invoice_db.inc +++ b/purchasing/includes/db/invoice_db.inc @@ -87,12 +87,9 @@ function get_deliveries_between($stock_id, $from, $to) { $from = date2sql($from); $to = date2sql($to); - $sql = "SELECT SUM(quantity), AVG(standard_cost) FROM ".TB_PREF."debtor_trans, ".TB_PREF."debtor_trans_details - WHERE ".TB_PREF."debtor_trans.trans_no = ".TB_PREF."debtor_trans_details.debtor_trans_no AND - ".TB_PREF."debtor_trans.tran_date >= '$from' AND - ".TB_PREF."debtor_trans.tran_date <= '$to' AND - ".TB_PREF."debtor_trans_details.stock_id = '$stock_id' AND - ".TB_PREF."debtor_trans_details.debtor_trans_type = 13"; + $sql = "SELECT SUM(-qty), AVG(standard_cost) FROM ".TB_PREF."stock_moves + WHERE type=13 AND stock_id='$stock_id' AND + tran_date>='$from' AND tran_date<='$to' GROUP BY stock_id"; $result = db_query($sql, "The deliveries could not be updated"); return db_fetch_row($result); } @@ -167,7 +164,6 @@ function add_supp_invoice($supp_trans) // do not receive as ref because we chang add_supp_invoice_gl_item($trans_type, $invoice_id, $entered_gl_code->gl_code, $entered_gl_code->amount, $memo_); } - foreach ($supp_trans->grn_items as $entered_grn) { @@ -182,9 +178,8 @@ function add_supp_invoice($supp_trans) // do not receive as ref because we chang $old = update_supp_received_items_for_invoice($entered_grn->id, $entered_grn->po_detail_item, $entered_grn->this_quantity_inv, $entered_grn->chg_price); $stock_gl_code = get_stock_gl_code($entered_grn->item_code); - $stock_entry_account = $stock_gl_code["inventory_account"]; - add_gl_trans_supplier($trans_type, $invoice_id, $date_, $stock_entry_account, + add_gl_trans_supplier($trans_type, $invoice_id, $date_, $stock_gl_code["inventory_account"], $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $entered_grn->this_quantity_inv * $line_taxfree, $supp_trans->supplier_id); // -------------- if price changed since po received. 16 Aug 2008 Joe Hunt @@ -198,7 +193,7 @@ function add_supp_invoice($supp_trans) // do not receive as ref because we chang $diff, $entered_grn->this_quantity_inv, $old_date, true); // function just above this $deliveries = get_deliveries_between($entered_grn->item_code, $old_date, $date_); - if ($deliveries[0] != 0) // have deliveries been done during the period? + if ($deliveries[0] != 0) // have deliveries been done during the period? { $amt = ($mat_cost - $deliveries[1]) * $deliveries[0]; // $amt in home currency @@ -210,16 +205,16 @@ function add_supp_invoice($supp_trans) // do not receive as ref because we chang add_gl_trans($trans_type, $invoice_id, $date_, $stock_gl_code["inventory_account"], 0, 0, _("Cost diff."), -$amt, null, null, null, "The general ledger transaction could not be added for the price variance of the inventory item"); + update_stock_move_pid(13, $entered_grn->item_code, $old_date, $date_, 0, $mat_cost); } + update_stock_move_pid(25, $entered_grn->item_code, $old_date, $old_date, $supp_trans->supplier_id, $mat_cost); } - // ---------------------------------------------------------------------- add_supp_invoice_item($trans_type, $invoice_id, $entered_grn->item_code, $entered_grn->item_description, 0, $line_taxfree, $line_tax, $entered_grn->this_quantity_inv, $entered_grn->id, $entered_grn->po_detail_item, ""); } /* end of GRN postings */ - /* Now the TAX account */ foreach ($taxes as $taxitem) { -- 2.30.2