From ac5e6ffeffe1d7906e422547a02b42d5cb3d0581 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Sat, 19 Mar 2011 09:34:56 +0100 Subject: [PATCH] More regarding costing valuations --- includes/db/inventory_db.inc | 19 ++++- inventory/includes/db/items_adjust_db.inc | 29 ++++++++ purchasing/includes/db/invoice_db.inc | 90 ++++++++++++++++++++++- sales/includes/db/sales_credit_db.inc | 28 ++++--- 4 files changed, 146 insertions(+), 20 deletions(-) diff --git a/includes/db/inventory_db.inc b/includes/db/inventory_db.inc index 56e94196..6a1a7f52 100644 --- a/includes/db/inventory_db.inc +++ b/includes/db/inventory_db.inc @@ -399,18 +399,29 @@ function void_stock_move($type, $type_no) while ($row = db_fetch($result)) { add_stock_move($type, $row["stock_id"], $type_no, $row["loc_code"], - sql2date($row["tran_date"]), $row["reference"], -$row["qty"], $row["standard_cost"], $row["person_id"], $row["visible"], - $row["price"], $row["discount_percent"]); + sql2date($row["tran_date"]), $row["reference"], -$row["qty"], $row["standard_cost"], $row["person_id"], $row["visible"], + $row["price"], $row["discount_percent"]); // If there is cost difference, then cost has to be adjusted. // IMP : Adjusting cost is suitable instead of normal averaging as it will not work properly for manufactured items. Reason being Avg cost is suitable only for purchased items + $curr_std_cost = get_standard_cost($row["stock_id"]); - if ($curr_std_cost != $row["standard_cost"]) + + //Chaitanya : 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"]; + else + $trans_rate = $row["standard_cost"]; + + if ($curr_std_cost != $trans_rate) { - $cost_diff = $row["standard_cost"] - $curr_std_cost; + $cost_diff = $trans_rate - $curr_std_cost; update_average_material_cost(0, $row["stock_id"], $cost_diff, -$row["qty"], sql2date($row["tran_date"]), true); } + } } diff --git a/inventory/includes/db/items_adjust_db.inc b/inventory/includes/db/items_adjust_db.inc index 1ca7d1ed..d0b87fc6 100644 --- a/inventory/includes/db/items_adjust_db.inc +++ b/inventory/includes/db/items_adjust_db.inc @@ -82,6 +82,35 @@ function add_stock_adjustment_item($adj_id, $stock_id, $location, $date_, $type, display_db_error("Cannot do inventory adjustment for Service item : $stock_id", ""); } + //Chaitanya : If negative adjustment result in negative or zero inventory + //then difference should be adjusted + $qoh = get_qoh_on_date($stock_id); + if ($qoh + $quantity <= 0 && $qoh > 0) //Positive inventory turning zero/negative + { + global $Refs; + + $id = get_next_trans_no(ST_JOURNAL); + $ref = $Refs->get_next(ST_JOURNAL); + $diff = get_standard_cost($stock_id) - $standard_cost; + + $stock_gl_code = get_stock_gl_code($stock_id); + $memo = _("For zero inventory of ").$stock_id." INVADJ REF: ".$reference; + //Reverse the inventory effect if $qoh <=0 + add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, + $stock_gl_code["inventory_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo, + -$qoh * $diff); + //GL Posting to inventory adjustment account + add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, + $stock_gl_code["adjustment_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], $memo, + $qoh * $diff); + + add_audit_trail(ST_JOURNAL, $id, $date_); + add_comments(ST_JOURNAL, $id, $date_, $memo); + $Refs->save(ST_JOURNAL, $id, $ref); + } + update_average_material_cost(null, $stock_id, $standard_cost, $quantity, $date_); add_stock_move(ST_INVADJUST, $stock_id, $adj_id, $location, diff --git a/purchasing/includes/db/invoice_db.inc b/purchasing/includes/db/invoice_db.inc index 9373d888..411a7a0b 100644 --- a/purchasing/includes/db/invoice_db.inc +++ b/purchasing/includes/db/invoice_db.inc @@ -260,7 +260,54 @@ function add_supp_invoice($supp_trans, $invoice_no=0) // do not receive as ref b $total += add_gl_trans($trans_type, $invoice_id, $date_, $stock_gl_code["inventory_account"], $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], 'GRN Provision', $diff_amt, null, null, null, - "The general ledger transaction could not be added for the GRN of the inventory item"); + "The general ledger transaction could not be added for the GRN of the inventory item"); + + //Chaitanya + //If QOH is 0 or negative then update_average_material_cost will be skipped + //Thus difference in PO and Supp Invoice should be handled separately + + $qoh = get_qoh_on_date($entered_grn->item_code); + if ($qoh <= 0) + { + global $Refs; + + //Chaitanya : Post a journal entry + $id = get_next_trans_no(ST_JOURNAL); + $ref = $Refs->get_next(ST_JOURNAL); + $stock_id = $entered_grn->item_code; + $stock_gl_code = get_stock_gl_code($stock_id); + $memo = _("Supplier invoice adjustment for zero inventory of ").$stock_id." "._("Invoice")." ".$supp_trans->reference; + //Reverse the inventory effect if $qoh <=0 + add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, + $stock_gl_code["inventory_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], + $memo, -$entered_grn->this_quantity_inv * $diff); + //GL Posting to inventory adjustment account + add_gl_trans_std_cost(ST_JOURNAL, $id, $date_, + $stock_gl_code["adjustment_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], + $memo, $entered_grn->this_quantity_inv * $diff); + + add_audit_trail(ST_JOURNAL, $id, $date_); + add_comments(ST_JOURNAL, $id, $date_, $memo); + $Refs->save(ST_JOURNAL, $id, $ref); + } + + /*$qoh = get_qoh_on_date($entered_grn->item_code); + if ($qoh <= 0) + { + $memo = "Diff. in cost: ".$diff; + //Reverse the inventory effect if $qoh <=0 + 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 * $diff, $supp_trans->supplier_id, "", null, $memo); + //GL Posting to inventory adjustment account + add_gl_trans_supplier($trans_type, $invoice_id, $date_, + $stock_gl_code["adjustment_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], + $entered_grn->this_quantity_inv * $diff, $supp_trans->supplier_id, "", null, $memo); + }*/ //add_gl_trans($trans_type, $invoice_id, $date_, $stock_gl_code["cogs_account"], // 0, 0, 'GRN Provision', -$diff_amt, null, null, null, @@ -497,14 +544,18 @@ function void_supp_invoice($type, $type_no) $batch = get_grn_batch_from_item($details_row["grn_item_id"]); $grn = get_grn_batch($batch); if ($type == ST_SUPPCREDIT) // credit note 2009-06-14 Joe Hunt Must restore the po and grn - { // We must get the corresponding invoice item to check for price chg. + { $match = get_matching_invoice_item($details_row["stock_id"], $details_row["po_detail_item_id"]); - if ($match !== false) + + //Chaitanya : Skipped costing block & handle in void_stock_move + // We must get the corresponding invoice item to check for price chg. + /*if ($match !== false) $mat_cost = update_average_material_cost($grn["supplier_id"], $details_row["stock_id"], $match["unit_price"], -$details_row["quantity"], sql2date($match['tran_date']), $match['tran_date'] !== $trans['tran_date']); else $mat_cost = update_average_material_cost($grn["supplier_id"], $details_row["stock_id"], - $details_row["FullUnitPrice"], -$details_row["quantity"], $old_date, $old[1] !== $trans['tran_date']); + $details_row["FullUnitPrice"], -$details_row["quantity"], $old_date, $old[1] !== $trans['tran_date']);*/ + $sql = "UPDATE ".TB_PREF."purch_order_details SET quantity_ordered = quantity_ordered + ".-$details_row["quantity"].", "; if ($match !== false) @@ -523,6 +574,37 @@ function void_supp_invoice($type, $type_no) // Only adjust the avg for the diff $mat_cost = update_average_material_cost(null, $details_row["stock_id"], $diff, -$details_row["quantity"], $old_date, true); + + //Chaitanya : Reverse effect + //If QOH is 0 or negative then update_average_material_cost will be skipped + //Thus difference in PO and Supp Invoice should be handled separately + + $qoh = get_qoh_on_date($details_row["stock_id"]); + if ($diff*$details_row["quantity"] !=0 && $qoh <= 0) + { + global $Refs; + + //Chaitanya : Post a journal entry + $id = get_next_trans_no(ST_JOURNAL); + $ref = $Refs->get_next(ST_JOURNAL); + $stock_id = $details_row["stock_id"]; + $stock_gl_code = get_stock_gl_code($stock_id); + $memo = "Reversing Supplier invoice adjustment for zero inventory of ".$stock_id." Invoice: ".$trans['reference']; + //Reverse the inventory effect if $qoh <=0 + add_gl_trans_std_cost(ST_JOURNAL, $id, $old_date, + $stock_gl_code["inventory_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], + $memo, $details_row["quantity"] * $diff); + //GL Posting to inventory adjustment account + add_gl_trans_std_cost(ST_JOURNAL, $id, $old_date, + $stock_gl_code["adjustment_account"], + $stock_gl_code['dimension_id'], $stock_gl_code['dimension2_id'], + $memo, -$details_row["quantity"] * $diff); + + add_audit_trail(ST_JOURNAL, $id, $old_date); + add_comments(ST_JOURNAL, $id, $old_date, $memo); + $Refs->save(ST_JOURNAL, $id, $ref); + } } /*$deliveries = get_deliveries_between($details_row["stock_id"], $old_date, $date_); if ($deliveries[0] != 0) // have deliveries been done during the period? diff --git a/sales/includes/db/sales_credit_db.inc b/sales/includes/db/sales_credit_db.inc index ce1cb17f..d5be9c44 100644 --- a/sales/includes/db/sales_credit_db.inc +++ b/sales/includes/db/sales_credit_db.inc @@ -171,10 +171,14 @@ function write_credit_note(&$credit_note, $write_off_acc) // Insert a stock movement coming back in to show the credit note and // a reversing stock movement to show the write off // + function add_credit_movements_item(&$credit_note, &$credit_line, $credit_type, $price, $credited_invoice=0) { + //Chaitanya : Stamp current cost in stock moves $credit_line does not fetch cost + $curr_std_cost = get_standard_cost($credit_line->stock_id); + if ($credit_type == "Return") { $reference = "Return "; @@ -188,20 +192,20 @@ function add_credit_movements_item(&$credit_note, &$credit_line, if ($credited_invoice) $reference .= "Ex Inv: " . $credited_invoice; - add_stock_move_customer(ST_CUSTCREDIT, $credit_line->stock_id, - key($credit_note->trans_no), $credit_note->Location, - $credit_note->document_date, $reference, -$credit_line->qty_dispatched, - $credit_line->standard_cost, 0, $price, - $credit_line->discount_percent); - - } - add_stock_move_customer(ST_CUSTCREDIT, $credit_line->stock_id, - key($credit_note->trans_no), $credit_note->Location, - $credit_note->document_date, $reference, $credit_line->qty_dispatched, - $credit_line->standard_cost, 0, $price, - $credit_line->discount_percent); + add_stock_move_customer(ST_CUSTCREDIT, $credit_line->stock_id, + key($credit_note->trans_no), $credit_note->Location, + $credit_note->document_date, $reference, -$credit_line->qty_dispatched, + $curr_std_cost, 0, $price, + $credit_line->discount_percent); } + add_stock_move_customer(ST_CUSTCREDIT, $credit_line->stock_id, + key($credit_note->trans_no), $credit_note->Location, + $credit_note->document_date, $reference, $credit_line->qty_dispatched, + $curr_std_cost, 0, $price, + $credit_line->discount_percent); + +} //---------------------------------------------------------------------------------------- -- 2.30.2