Bug [0000037] Price diff and deliveries between po receive and supp invoice. (Again)

diff --git a/CHANGELOG.txt b/CHANGELOG.txt
index 2f52c72b55f45d74efce3089b6a12f8d0686678f..d0d52fb32348ae83e10686bc35474e7f30dc1efd 100644 (file)
--- 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 562c8d1b1d866c970dc552026b5ec0cca685e4da..46a16b41706de5cfecbe43c15f52f1f6af9c733a 100644 (file)
--- a/includes/db/inventory_db.inc
+++ b/includes/db/inventory_db.inc
@@ -1,6 +1,6 @@
 <?php
 
-function get_qoh_on_date($stock_id, $location=null, $date_=null)
+function get_qoh_on_date($stock_id, $location=null, $date_=null, $exclude=0)
 {
        if ($date_ == null)
                $date_ = Today();
@@ -17,6 +17,18 @@ function get_qoh_on_date($stock_id, $location=null, $date_=null)
        $result = db_query($sql, "QOH calulcation failed");
 
        $myrow = db_fetch_row($result);
+       if ($exclude > 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 bb24300a945808e279d98b897c43f1dec77c66f4..756c972fecb5d3f59b6daa7e378999430b263e56 100644 (file)
--- 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 84d205a2cc35a29513c63dbbefb9e549879b6a84..014e5ce85d612c85376ffd50be678b9791a1dad7 100644 (file)
--- 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)
     {