X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=purchasing%2Fincludes%2Fdb%2Fgrn_db.inc;h=065b2c7d04fa0162df19529e402d9282c09d5fa4;hb=9ff22040ec9e77b56c71df9bd95f9b2c2d50bd7e;hp=da4dedffb5977536a25a11fb996c59cdbae0fc15;hpb=264c09696d65efe4532c197f317162daf8c24f32;p=fa-stable.git diff --git a/purchasing/includes/db/grn_db.inc b/purchasing/includes/db/grn_db.inc index da4dedff..065b2c7d 100644 --- a/purchasing/includes/db/grn_db.inc +++ b/purchasing/includes/db/grn_db.inc @@ -9,14 +9,9 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ -//------------------- update average material cost ------------------------------------------ Joe Hunt Mar-03-2008 +//------------------- update average material cost ---------------------------------------------- function update_average_material_cost($supplier, $stock_id, $price, $qty, $date, $adj_only=false) { - //Handle if inventory will become negative - //8-OCT-2011 : Skip negative inventory adjustment for case of adjust_only - if (is_inventory_item($stock_id) && !$adj_only) - handle_negative_inventory($stock_id, $qty, $price, $date); - // probably this function should be optimized // passing transaction cart as argument. This would // save a couple of db calls like get_supplier() @@ -40,24 +35,17 @@ function update_average_material_cost($supplier, $stock_id, $price, $qty, $date, $price_in_home_currency = $price; $price_in_home_currency_ = $price_in_home_currency; + + // Handle if inventory will become negative + // Skip negative inventory adjustment for case of adjust_only + if (is_inventory_item($stock_id) && !$adj_only) + handle_negative_inventory($stock_id, $qty, $price_in_home_currency, $date); - $sql = "SELECT mb_flag, material_cost, labour_cost, overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id=".db_escape($stock_id); + $sql = "SELECT mb_flag, material_cost FROM ".TB_PREF."stock_master WHERE stock_id=".db_escape($stock_id); $result = db_query($sql); $myrow = db_fetch($result); $material_cost = $myrow['material_cost']; - //Price adjustment for manufactured item - if (!$adj_only && $myrow['mb_flag'] == 'M') - { - $standard_cost = get_standard_cost($stock_id); - //reduce overhead_cost and labour_cost from price as those will remain as is - $price_in_home_currency = $price_in_home_currency - $myrow['labour_cost'] - $myrow['overhead_cost']; - } - - if ($adj_only) - $exclude = ST_CUSTDELIVERY; - else - $exclude = 0; $cost_adjust = false; $qoh = get_qoh_on_date($stock_id); @@ -79,10 +67,11 @@ function update_average_material_cost($supplier, $stock_id, $price, $qty, $date, $material_cost = ($qoh * $material_cost + $qty * $price_in_home_currency) / ($qoh + $qty); } - if ($cost_adjust) // new 2010-02-10 //Chaitanya : Material_cost replaced with price + if ($cost_adjust) // Material_cost replaced with price adjust_deliveries($stock_id, $price_in_home_currency_, $date); $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost)." WHERE stock_id=".db_escape($stock_id); + db_query($sql,"The cost details for the inventory item could not be updated"); return $material_cost; } @@ -98,7 +87,16 @@ function add_grn(&$po) begin_transaction(); hook_db_prewrite($po, ST_SUPPRECEIVE); - $grn = add_grn_batch($po->order_no, $po->supplier_id, $po->reference, $po->Location, $date_); + if (!is_company_currency($po->curr_code)) + { + if (!$po->ex_rate) + $po->ex_rate = get_date_exchange_rate($po->curr_code, $date_); + else + add_new_exchange_rate($po->curr_code, $date_, $po->ex_rate); + } else + $po->ex_rate = 1; + + $grn = add_grn_batch($po->order_no, $po->supplier_id, $po->reference, $po->Location, $date_, $po->ex_rate); $clearing_act = get_company_pref('grn_clearing_act'); if ($clearing_act) { // otherwise GRN clearing account is not used @@ -110,25 +108,26 @@ function add_grn(&$po) { $stock_gl_code = get_stock_gl_code($order_line->stock_id); - /*Update sales_order_details for the new quantity received and the standard cost used for postings to GL and recorded in the stock movements for FIFO/LIFO stocks valuations*/ - //------------------- update average material cost and clearing account ----- Joe Hunt Mar-03-2008 + /*Update sales_order_details for the new quantity received and the standard cost used for postings to GL + and recorded in the stock movements for FIFO/LIFO stocks valuations*/ + //------------------- update average material cost and clearing account -------------------------------- if (is_inventory_item($order_line->stock_id)) { if ($clearing_act) $total += add_gl_trans_supplier(ST_SUPPRECEIVE, $grn, $date_, $stock_gl_code["inventory_account"], $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], - $order_line->receive_qty * $order_line->taxfree_charge_price($po), $po->supplier_id, '', 0, $order_line->stock_id); - update_average_material_cost($po->supplier_id, $order_line->stock_id, $order_line->price, + $order_line->taxfree_charge_value($po), $po->supplier_id, '', 0, $order_line->stock_id); + update_average_material_cost($po->supplier_id, $order_line->stock_id, $order_line->price, $order_line->receive_qty, $date_); } - //---------------------------------------------------------------------------------------------------------------- + //------------------------------------------------------------------------------------------------------ if ($order_line->qty_received == 0) { /*This must be the first receipt of goods against this line */ /*Need to get the standard cost as it is now so we can process GL jorunals later*/ - $order_line->standard_cost = get_standard_cost($order_line->stock_id); + $order_line->standard_cost = get_unit_cost($order_line->stock_id); } - // added 2008-12-08 Joe Hunt. Update the purchase data table + // Update the purchase data table add_or_update_purchase_data($po->supplier_id, $order_line->stock_id, $order_line->price, $order_line->item_description); @@ -142,15 +141,15 @@ function add_grn(&$po) $po->line_items[$line_no]->grn_item_id = $grn_item; /* Update location stock records - NB a po cannot be entered for a service/kit parts done automatically */ add_stock_move(ST_SUPPRECEIVE, $order_line->stock_id, $grn, $po->Location, $date_, "", - $order_line->receive_qty, $order_line->standard_cost, - $po->supplier_id, 1, $order_line->taxfree_charge_price($po)); + $order_line->receive_qty, $order_line->standard_cost, $order_line->taxfree_charge_value($po)/$order_line->receive_qty); } /*quantity received is != 0 */ } /*end of order_line loop */ if ($clearing_act && $total != 0.0) { + $accs = get_supplier_accounts($po->supplier_id); $total += add_gl_trans_supplier(ST_SUPPRECEIVE, $grn, $date_, $clearing_act, - 0, 0, -$total, null); + $accs['dimension_id'], $accs['dimension2_id'], -$total, null); } $Refs->save(ST_SUPPRECEIVE, $grn, $po->reference); @@ -165,13 +164,13 @@ function add_grn(&$po) //---------------------------------------------------------------------------------------- -function add_grn_batch($po_number, $supplier_id, $reference, $location, $date_) +function add_grn_batch($po_number, $supplier_id, $reference, $location, $date_, $rate) { $date = date2sql($date_); - $sql = "INSERT INTO ".TB_PREF."grn_batch (purch_order_no, delivery_date, supplier_id, reference, loc_code) + $sql = "INSERT INTO ".TB_PREF."grn_batch (purch_order_no, delivery_date, supplier_id, reference, loc_code, rate) VALUES (".db_escape($po_number).", ".db_escape($date).", " - .db_escape($supplier_id).", ".db_escape($reference).", ".db_escape($location).")"; + .db_escape($supplier_id).", ".db_escape($reference).", ".db_escape($location).", ".db_escape($rate).")"; db_query($sql, "A grn batch record could not be inserted."); @@ -189,7 +188,6 @@ function add_grn_detail_item($grn_batch_id, $po_detail_item, $item_code, $descri quantity_ordered=".db_escape($quantity).", act_price=".db_escape($price)." WHERE po_detail_item = ".db_escape($po_detail_item); - db_query($sql, "a purchase order details record could not be updated. This receipt of goods has not been processed "); $sql = "INSERT INTO ".TB_PREF."grn_items (grn_batch_id, po_detail_item, item_code, description, qty_recd) @@ -223,11 +221,13 @@ function set_grn_item_credited(&$entered_grn, $supplier, $transno, $date) $mcost = update_average_material_cost($supplier, $entered_grn->item_code, $entered_grn->chg_price, $entered_grn->this_quantity_inv, $date); - $sql = "SELECT ".TB_PREF."grn_batch.*, ".TB_PREF."grn_items.* - FROM ".TB_PREF."grn_batch, ".TB_PREF."grn_items - WHERE ".TB_PREF."grn_items.grn_batch_id=".TB_PREF."grn_batch.id - AND ".TB_PREF."grn_items.id=".db_escape($entered_grn->id)." - AND ".TB_PREF."grn_items.item_code=".db_escape($entered_grn->item_code); + $sql = "SELECT grn.*, item.* + FROM ".TB_PREF."grn_batch grn," + .TB_PREF."grn_items item + WHERE item.grn_batch_id=grn.id + AND item.id=".db_escape($entered_grn->id)." + AND item.item_code=".db_escape($entered_grn->item_code); + $result = db_query($sql, "Could not retreive GRNS"); $myrow = db_fetch($result); @@ -249,7 +249,7 @@ function set_grn_item_credited(&$entered_grn, $supplier, $transno, $date) db_query($sql); add_stock_move(ST_SUPPCREDIT, $entered_grn->item_code, $transno, $myrow['loc_code'], $date, "", - $entered_grn->this_quantity_inv, $mcost, $supplier, 1, $entered_grn->chg_price); + $entered_grn->this_quantity_inv, $mcost, $entered_grn->chg_price); } function get_grn_items($grn_batch_id=0, $supplier_id="", $outstanding_only=false, @@ -309,13 +309,14 @@ function get_grn_items($grn_batch_id=0, $supplier_id="", $outstanding_only=false function get_grn_item_detail($grn_item_no) { - $sql = "SELECT ".TB_PREF."grn_items.*, ".TB_PREF."purch_order_details.unit_price, - ".TB_PREF."grn_items.qty_recd - ".TB_PREF."grn_items.quantity_inv AS QtyOstdg, - ".TB_PREF."purch_order_details.std_cost_unit - FROM ".TB_PREF."grn_items, ".TB_PREF."purch_order_details, ".TB_PREF."stock_master - WHERE ".TB_PREF."grn_items.po_detail_item=".TB_PREF."purch_order_details.po_detail_item - AND ".TB_PREF."stock_master.stock_id=".TB_PREF."grn_items.item_code - AND ".TB_PREF."grn_items.id=".db_escape($grn_item_no); + $sql = "SELECT grn.*, po.unit_price, grn.qty_recd - grn.quantity_inv AS QtyOstdg, + po.std_cost_unit + FROM ".TB_PREF."grn_items grn," + .TB_PREF."purch_order_details po," + .TB_PREF."stock_master item + WHERE grn.po_detail_item=po.po_detail_item + AND item.stock_id=grn.item_code + AND grn.id=".db_escape($grn_item_no); $result = db_query($sql, "could not retreive grn item details"); return db_fetch($result); @@ -333,7 +334,7 @@ function read_grn_items_to_order($grn_batch, &$order) while ($myrow = db_fetch($result)) { if ($myrow['qty_recd'] == 0 && $myrow['quantity_inv'] == 0) - continue; // 2011-01-18 Joe Hunt. We will not have empty credited rows. + continue; // We will not have empty credited rows. if (is_null($myrow["units"])) { $units = ""; @@ -365,6 +366,7 @@ function read_grn($grn_batch, &$order) $row = db_fetch($result); $po_number = $row["purch_order_no"]; + $order->ex_rate = $row["rate"]; $result = read_po_header($po_number, $order); @@ -405,8 +407,10 @@ function exists_grn($grn_batch) function exists_grn_on_invoices($grn_batch) { - $sql = "SELECT ".TB_PREF."supp_invoice_items.id FROM ".TB_PREF."supp_invoice_items,".TB_PREF."grn_items - WHERE ".TB_PREF."supp_invoice_items.grn_item_id=".TB_PREF."grn_items.id + $sql = "SELECT inv.id + FROM ".TB_PREF."supp_invoice_items inv," + .TB_PREF."grn_items grn + WHERE inv.grn_item_id=grn.id AND quantity != 0 AND grn_batch_id=".db_escape($grn_batch); $result = db_query($sql, "Cannot query GRNs"); @@ -462,4 +466,3 @@ function void_grn($grn_batch) //---------------------------------------------------------------------------------------------------------- -?> \ No newline at end of file