From 77b57add79796f4cc2a835cb993456e68d207801 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Tue, 10 May 2011 15:05:48 +0200 Subject: [PATCH] Cleanup inventory_db.inc, work_orders_db.inc and grn_db.inc by Chaitanya --- includes/db/inventory_db.inc | 74 +-------------- manufacturing/includes/db/work_orders_db.inc | 99 +++----------------- purchasing/includes/db/grn_db.inc | 12 +-- 3 files changed, 20 insertions(+), 165 deletions(-) diff --git a/includes/db/inventory_db.inc b/includes/db/inventory_db.inc index afd70690..af2898fc 100644 --- a/includes/db/inventory_db.inc +++ b/includes/db/inventory_db.inc @@ -65,7 +65,6 @@ function get_item_edit_info($stock_id) function get_standard_cost($stock_id) { - //Chaitanya : Compatibility with service items $sql = "SELECT (material_cost + labour_cost + overhead_cost) AS std_cost FROM ".TB_PREF."stock_master s WHERE stock_id=".db_escape($stock_id); $result = db_query($sql, "The standard cost cannot be retrieved"); @@ -170,19 +169,6 @@ function get_deliveries_from_trans($stock_id, $from) $result = db_query($sql, "The deliveries could not be updated"); $row = db_fetch_row($result); - //return $row; - - // Get Std cost of previsous transaction before the cut-over delivery - // This is useful to get inventory valuation - //Chaitanya : Corrected - /*$sql = "SELECT max( `trans_id` ) - FROM ".TB_PREF."stock_moves - WHERE stock_id = ".db_escape($stock_id)." - AND trans_id<'$from'"; - $result = db_query($sql, "The deliveries could not be updated"); - $trans = db_fetch_row($result); - $prev_trans = $trans[0];*/ - $sql = "SELECT standard_cost FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id) ." AND trans_id ='$from'"; @@ -196,7 +182,7 @@ function get_deliveries_from_trans($stock_id, $from) $result = db_query($sql, "The deliveries could not be updated"); $qoh = db_fetch_row($result); - $qty = $row[0] - $qoh[0]; //Qoh is minus from delivered in -ve + $qty = $row[0] - $qoh[0]; //QOH prior to -ve stock is subtracted $final_cost = $row[1] - $qoh[0]*$cost[0]; return array($qty,$final_cost); @@ -215,41 +201,7 @@ function get_purchases_from_trans($stock_id, $from) } //------------------------------------------------------------------- -/* Original Code V0 Leave as is a while -function adjust_deliveries_v0($stock_id, $material_cost, $to) -{ - if (!is_inventory_item($stock_id)) - return; - $from = last_negative_stock_begin_date($stock_id, $to); - if ($from == false || $from == "") - return; - $from = sql2date($from); - $row = get_deliveries_between($stock_id, $from, $to); - if ($row == false) - return; - $old_cost = $row[1]; - $new_cost = $row[0] * $material_cost; - $diff = $new_cost - $old_cost; - if ($diff != 0) - { - $update_no = get_next_trans_no(ST_COSTUPDATE); - if (!is_date_in_fiscalyear($to)) - $to = end_fiscalyear(); - - $stock_gl_code = get_stock_gl_code($stock_id); - - $memo_ = sprintf(_("Cost was %s changed to %s for item '%s'"), - $old_cost, $new_cost, $stock_id); - add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["cogs_account"], - $stock_gl_code["dimension_id"], $stock_gl_code["dimension2_id"], $memo_, $diff); - add_gl_trans_std_cost(ST_COSTUPDATE, $update_no, $to, $stock_gl_code["inventory_account"], - 0, 0, $memo_, -$diff); - add_audit_trail(ST_COSTUPDATE, $update_no, $to); - } -} -*/ -//New written function function adjust_deliveries($stock_id, $material_cost, $to) { if (!is_inventory_item($stock_id)) @@ -304,7 +256,6 @@ function adjust_deliveries($stock_id, $material_cost, $to) function get_stock_gl_code($stock_id) { /*Gets the GL Codes relevant to the item account */ - //Chaitanya : Updated to also provide mb_flag $sql = "SELECT mb_flag, inventory_account, cogs_account, adjustment_account, sales_account, assembly_account, dimension_id, dimension2_id FROM ".TB_PREF."stock_master WHERE stock_id = ".db_escape($stock_id); @@ -314,11 +265,10 @@ function get_stock_gl_code($stock_id) } //----------------------------------------------------------------------------------------- -//Chaitanya : New Function handle negative stock effect -//Called in add_stock_move + function handle_negative_inventory($stock_id, $quantity, $standard_cost, $date_) { - //Chaitanya : If negative adjustment result in negative or zero inventory + //If negative adjustment result in negative or zero inventory //then difference should be adjusted $qoh = get_qoh_on_date($stock_id); @@ -363,13 +313,6 @@ function add_stock_move($type, $stock_id, $trans_no, $location, $date_, $reference, $quantity, $std_cost, $person_id=0, $show_or_hide=1, $price=0, $discount_percent=0, $error_msg="") { - // Chaitanya : Removed following restriction considering WO issues - // Voiding issues and productions with Service items can not get the - // Service items compatibility - // do not add a stock move if it's a non-inventory item - //if (!is_inventory_item($stock_id)) - //return null; - $date = date2sql($date_); $sql = "INSERT INTO ".TB_PREF."stock_moves (stock_id, trans_no, type, loc_code, @@ -390,7 +333,6 @@ function add_stock_move($type, $stock_id, $trans_no, $location, return db_insert_id(); } -//Chaitanya : Added function function update_stock_move($type, $trans_no, $stock_id, $cost) { $sql = "UPDATE ".TB_PREF."stock_moves SET standard_cost=".db_escape($cost) @@ -433,12 +375,7 @@ function get_stock_moves($type, $type_no, $visible=false) function void_stock_move($type, $type_no) { - //Chaitanya : Reversing stock move rather than voiding as it is hazardous to lose stock movement trail with respect to costing - /*$sql = "UPDATE ".TB_PREF."stock_moves SET qty=0, price=0, discount_percent=0, - standard_cost=0 WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); - - db_query($sql, "Could not void stock moves"); */ - + $sql = "SELECT * from ".TB_PREF."stock_moves WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); $result = db_query($sql, "Could not void stock moves"); while ($row = db_fetch($result)) @@ -447,8 +384,7 @@ function void_stock_move($type, $type_no) if (is_inventory_item($row["stock_id"])) { // The cost has to be adjusted. - //Chaitanya : Transaction rates are stored either as price or standard_cost depending - //on types + // Transaction rates are stored either as price or standard_cost depending on types $types = array(ST_SUPPCREDIT); if (in_array($type,$types)) $trans_rate = $row["price"]; diff --git a/manufacturing/includes/db/work_orders_db.inc b/manufacturing/includes/db/work_orders_db.inc index 981b0006..3609fe05 100644 --- a/manufacturing/includes/db/work_orders_db.inc +++ b/manufacturing/includes/db/work_orders_db.inc @@ -11,7 +11,6 @@ ***********************************************************************/ //-------------------------------------------------------------------------------------- -//Chaitanya : Added $advanced Parameter for Advanced Manufacturing function add_material_cost($stock_id, $qty, $date_, $advanced=false) { $m_cost = 0; @@ -23,22 +22,18 @@ function add_material_cost($stock_id, $qty, $date_, $advanced=false) } $bom_cost = $m_cost; - //$dec = user_price_dec(); - //price_decimal_format($m_cost, $dec); $sql = "SELECT material_cost, labour_cost, overhead_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']; - //Chaitanya : Upating material cost without considering labour, overhead costs results in invalid costing if ($advanced) { //reduce overhead_cost and labour_cost from price as those will remain as is $m_cost = $m_cost - $myrow['labour_cost'] - $myrow['overhead_cost']; } - //$qoh = get_qoh_on_date($stock_id, null, $date_); $qoh = get_qoh_on_date($stock_id); $cost_adjust = false; if ($qoh < 0) @@ -49,7 +44,6 @@ function add_material_cost($stock_id, $qty, $date_, $advanced=false) } if ($qoh + $qty != 0) $material_cost = ($qoh * $material_cost + $qty * $m_cost) / ($qoh + $qty); - //$material_cost = round2($material_cost, $dec); if ($advanced && $cost_adjust) // new 2010-02-10 adjust_deliveries($stock_id, $bom_cost, $date_); @@ -59,11 +53,8 @@ function add_material_cost($stock_id, $qty, $date_, $advanced=false) db_query($sql,"The cost details for the inventory item could not be updated"); } -//Chaitanya : Added Adjustement Only Parameter function add_overhead_cost($stock_id, $qty, $date_, $costs, $adj_only=false) { - //$dec = user_price_dec(); - //price_decimal_format($costs, $dec); if ($qty != 0) $costs /= $qty; $sql = "SELECT overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = " @@ -71,7 +62,6 @@ function add_overhead_cost($stock_id, $qty, $date_, $costs, $adj_only=false) $result = db_query($sql); $myrow = db_fetch($result); $overhead_cost = $myrow['overhead_cost']; - //$qoh = get_qoh_on_date($stock_id, null, $date_); $qoh = get_qoh_on_date($stock_id); if ($qoh < 0) $qoh = 0; @@ -81,7 +71,7 @@ function add_overhead_cost($stock_id, $qty, $date_, $costs, $adj_only=false) $costs = $qty * $costs; if ($qoh>0) $overhead_cost = ($qoh * $overhead_cost + $costs) / $qoh; - else //Chaitanya : Pass JV if qoh is 0/negative + else // Journal Entry if QOH is 0/negative { global $Refs; @@ -111,17 +101,13 @@ function add_overhead_cost($stock_id, $qty, $date_, $costs, $adj_only=false) if ($qoh + $qty != 0) $overhead_cost = ($qoh * $overhead_cost + $qty * $costs) / ($qoh + $qty); } - //$overhead_cost = round2($overhead_cost, $dec); $sql = "UPDATE ".TB_PREF."stock_master SET overhead_cost=".db_escape($overhead_cost)." WHERE stock_id=".db_escape($stock_id); db_query($sql,"The cost details for the inventory item could not be updated"); } -//Chaitanya : Added Adjustement Only Parameter function add_labour_cost($stock_id, $qty, $date_, $costs, $adj_only=false) { - //$dec = user_price_dec(); - //price_decimal_format($costs, $dec); if ($qty != 0) $costs /= $qty; $sql = "SELECT labour_cost FROM ".TB_PREF."stock_master WHERE stock_id = " @@ -129,7 +115,6 @@ function add_labour_cost($stock_id, $qty, $date_, $costs, $adj_only=false) $result = db_query($sql); $myrow = db_fetch($result); $labour_cost = $myrow['labour_cost']; - //$qoh = get_qoh_on_date($stock_id, null, $date_); $qoh = get_qoh_on_date($stock_id); if ($qoh < 0) $qoh = 0; @@ -139,7 +124,7 @@ function add_labour_cost($stock_id, $qty, $date_, $costs, $adj_only=false) $costs = $qty * $costs; if ($qoh>0) $labour_cost = ($qoh * $labour_cost + $costs) / $qoh; - else //Chaitanya : Pass JV if qoh is 0/negative + else // Journal Entry if QOH is 0/negative { global $Refs; @@ -168,14 +153,12 @@ function add_labour_cost($stock_id, $qty, $date_, $costs, $adj_only=false) { if ($qoh + $qty != 0) $labour_cost = ($qoh * $labour_cost + $qty * $costs) / ($qoh + $qty); - } - //$labour_cost = round2($labour_cost, $dec); + } $sql = "UPDATE ".TB_PREF."stock_master SET labour_cost=".db_escape($labour_cost)." WHERE stock_id=".db_escape($stock_id); db_query($sql,"The cost details for the inventory item could not be updated"); } -//Chaitanya : Added Adjustement Only Parameter function add_issue_cost($stock_id, $qty, $date_, $costs, $adj_only=false) { if ($qty != 0) @@ -185,9 +168,6 @@ function add_issue_cost($stock_id, $qty, $date_, $costs, $adj_only=false) $result = db_query($sql); $myrow = db_fetch($result); $material_cost = $myrow['material_cost']; - //$dec = user_price_dec(); - //price_decimal_format($material_cost, $dec); - //$qoh = get_qoh_on_date($stock_id, null, $date_); $qoh = get_qoh_on_date($stock_id); if ($qoh < 0) $qoh = 0; @@ -197,7 +177,7 @@ function add_issue_cost($stock_id, $qty, $date_, $costs, $adj_only=false) $costs = $qty * $costs; if ($qoh>0) $material_cost = $costs / $qoh; - else //Chaitanya : Pass JV if qoh is 0/negative + else // Journal Entry if QOH is 0/negative { global $Refs; @@ -226,8 +206,7 @@ function add_issue_cost($stock_id, $qty, $date_, $costs, $adj_only=false) { if ($qoh + $qty != 0) $material_cost = ($qty * $costs) / ($qoh + $qty); - } - //$material_cost = round2($material_cost, $dec); + } $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=material_cost+" .db_escape($material_cost) ." WHERE stock_id=".db_escape($stock_id); @@ -249,9 +228,6 @@ function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id, $args->woid = 0; hook_db_prewrite($args, ST_WORKORDER); - //Chaitanya : Material cost should be added at time of production as per BOM at the time. - //add_material_cost($stock_id, $units_reqd, $date_); - $date = date2sql($date_); $required = date2sql($required_by); @@ -287,10 +263,6 @@ function update_work_order($woid, $loc_code, $units_reqd, $stock_id, 'date_', 'required_by', 'memo_'), $args); hook_db_prewrite($args, ST_WORKORDER); - //Chaitanya: Material Cost to be calculated at production - //add_material_cost($_POST['old_stk_id'], -$_POST['old_qty'], $date_); - //add_material_cost($stock_id, $units_reqd, $date_); - $date = date2sql($date_); $required = date2sql($required_by); @@ -314,9 +286,6 @@ function delete_work_order($woid) begin_transaction(); hook_db_prevoid(ST_WORKORDER, $woid); - //Chaitanya : Cost calculation are affected only at time of production - //add_material_cost($_POST['stock_id'], -$_POST['quantity'], $_POST['date_']); - // delete the work order requirements delete_wo_requirements($woid); @@ -445,18 +414,6 @@ function void_work_order($woid) $work_order = get_work_order($woid); if (!($work_order["type"] == WO_ADVANCED)) { - //Chaitanya : Removed WO costing from here. Handled in void_stock_move - //Reason - if BOM is changed since WO was executed then add_material_cost will result in incorrect costing as it is based on current BOM for material cost. - /*$date = sql2date($work_order['date_']); - $qty = $work_order['units_reqd']; - add_material_cost($work_order['stock_id'], -$qty, $date); // remove avg. cost for qty - $cost = get_gl_wo_cost($woid, WO_LABOUR); // get the labour cost and reduce avg cost - if ($cost != 0) - add_labour_cost($work_order['stock_id'], -$qty, $date, $cost); - $cost = get_gl_wo_cost($woid, WO_OVERHEAD); // get the overhead cost and reduce avg cost - if ($cost != 0) - add_overhead_cost($work_order['stock_id'], -$qty, $date, $cost);*/ - $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = " .db_escape($woid); db_query($sql, "The work order couldn't be voided"); @@ -475,15 +432,10 @@ function void_work_order($woid) // void everything inside the work order : issues, productions, payments $date = sql2date($work_order['date_']); - //Chaitanya : Removed WO costing from here. Handled in void_stock_move - //Reason - if BOM is changed since WO was executed then add_material_cost will result in incorrect costing as it is based on current BOM for material cost. - //add_material_cost($work_order['stock_id'], -$work_order['units_reqd'], $date); // remove avg. cost for qty - $result = get_work_order_productions($woid); // check the produced quantity $qty = 0; while ($row = db_fetch($result)) { - //Chaitanya : Use native function for voiding void_work_order_produce($row['id']); //Post voided entry if not prevoided explicitly @@ -492,23 +444,14 @@ function void_work_order($woid) continue; $memo_ = _("Voiding Work Order Trans # ").$woid; add_audit_trail(ST_MANURECEIVE, $row['id'], today(), _("Voided.")."\n".$memo_); - add_voided_entry(ST_MANURECEIVE, $row['id'], today(), $memo_); - - /*$qty += $row['quantity']; - // clear the production record - $sql = "UPDATE ".TB_PREF."wo_manufacture SET quantity=0 WHERE id=".$row['id']; - db_query($sql, "Cannot void a wo production"); - - void_stock_move(ST_MANURECEIVE, $row['id']); // and void the stock moves; */ + add_voided_entry(ST_MANURECEIVE, $row['id'], today(), $memo_); } - //Chaitanya : Get all work order issues - //$result = get_additional_issues($woid); // check the issued quantities + $result = get_work_order_issues($woid); $cost = 0; $issue_no = 0; while ($row = db_fetch($result)) { - //Chaitanya : Use native function for voiding void_work_order_issue($row['issue_no']); //Post voided entry if not prevoided explicitly @@ -518,32 +461,16 @@ function void_work_order($woid) $memo_ = _("Voiding Work Order Trans # ").$woid; add_audit_trail(ST_MANUISSUE, $row['issue_no'], today(), _("Voided.")."\n".$memo_); add_voided_entry(ST_MANUISSUE, $row['issue_no'], today(), $memo_); - - /*$std_cost = get_standard_cost($row['stock_id']); - $icost = $std_cost * $row['qty_issued']; - $cost += $icost; - if ($issue_no == 0) - $issue_no = $row['issue_no']; - // void the actual issue items and their quantities - $sql = "UPDATE ".TB_PREF."wo_issue_items SET qty_issued = 0 WHERE issue_id=" - .db_escape($row['id']); - db_query($sql,"A work order issue item could not be voided");*/ } - - //Chaitaya : Voiding each issue handles the - //if ($issue_no != 0) - //void_stock_move(ST_MANUISSUE, $issue_no); // and void the stock moves - //if ($cost != 0) - //add_issue_cost($work_order['stock_id'], -$qty, $date, $cost); - - //Chaitanya : Reverse the cost effects on $work_order['stock_id'] as adjustement - $cost = get_gl_wo_cost($woid, WO_LABOUR); // get the labour cost and reduce avg cost + + //Adust avg labour cost + $cost = get_gl_wo_cost($woid, WO_LABOUR); if ($cost != 0) - //add_labour_cost($work_order['stock_id'], -$qty, $date, $cost); add_labour_cost($work_order['stock_id'], 1, $date, -$cost, true); - $cost = get_gl_wo_cost($woid, WO_OVERHEAD); // get the overhead cost and reduce avg cost + + //Adust avg overhead cost + $cost = get_gl_wo_cost($woid, WO_OVERHEAD); if ($cost != 0) - //add_overhead_cost($work_order['stock_id'], -$qty, $date, $cost); add_overhead_cost($work_order['stock_id'], 1, $date, -$cost, true); $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_reqd=0,units_issued=0 WHERE id = " diff --git a/purchasing/includes/db/grn_db.inc b/purchasing/includes/db/grn_db.inc index 09d68ff3..56e694b4 100644 --- a/purchasing/includes/db/grn_db.inc +++ b/purchasing/includes/db/grn_db.inc @@ -30,9 +30,6 @@ function update_average_material_cost($supplier, $stock_id, $price, $qty, $date, $price = get_tax_free_price_for_item($stock_id, $price, $supp['tax_group_id'], $supp['tax_included']); - //$dec = user_price_dec(); - //price_decimal_format($price, $dec); - //$price = round2($price, $dec); if ($currency != null) { $ex_rate = get_exchange_rate_to_home_currency($currency, $date); @@ -48,23 +45,19 @@ function update_average_material_cost($supplier, $stock_id, $price, $qty, $date, $myrow = db_fetch($result); $material_cost = $myrow['material_cost']; - //Chaitanya : For Manufactured Items - //IF inventory adjustment is made for manufactured item, the material cost averages and labour, overhead remains as is, but in inventory valuation total standard_cost matters resulting in invalid valuation. - if (!$adj_only && $myrow['mb_flag'] == 'M') //For manufactured items price is adjusted + //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 ($price > -0.0001 && $price < 0.0001) commented out by Chaitanya - // return $material_cost; if ($adj_only) $exclude = ST_CUSTDELIVERY; else $exclude = 0; $cost_adjust = false; - //$qoh = get_qoh_on_date($stock_id, null, $date, $exclude); $qoh = get_qoh_on_date($stock_id); if ($adj_only) @@ -84,7 +77,6 @@ function update_average_material_cost($supplier, $stock_id, $price, $qty, $date, $material_cost = ($qoh * $material_cost + $qty * $price_in_home_currency) / ($qoh + $qty); } - //$material_cost = round2($material_cost, $dec); if ($cost_adjust) // new 2010-02-10 //Chaitanya : Material_cost replaced with price adjust_deliveries($stock_id, $price_in_home_currency_, $date); -- 2.30.2