def get_stock_qty(item_code, warehouse): return get_previous_sle({ "item_code": item_code, "warehouse": warehouse, "posting_date": nowdate(), "posting_time": nowtime() }).get("qty_after_transaction") or 0
def get_incoming_rate(args): """Get Incoming Rate based on valuation method""" from erpnext.stock.stock_ledger import get_previous_sle, get_valuation_rate if isinstance(args, basestring): args = json.loads(args) in_rate = 0 if (args.get("serial_no") or "").strip(): in_rate = get_avg_purchase_rate(args.get("serial_no")) else: valuation_method = get_valuation_method(args.get("item_code")) previous_sle = get_previous_sle(args) if valuation_method == 'FIFO': if previous_sle: previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]') in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0 elif valuation_method == 'Moving Average': in_rate = previous_sle.get('valuation_rate') or 0 if not in_rate: voucher_no = args.get('voucher_no') or args.get('name') in_rate = get_valuation_rate(args.get('item_code'), args.get('warehouse'), args.get('voucher_type'), voucher_no, args.get('allow_zero_valuation'), currency=erpnext.get_company_currency(args.get('company')), company=args.get('company')) return in_rate
def update_stock_ledger(self): """ find difference between current and expected entries and create stock ledger entries based on the difference""" from erpnext.stock.stock_ledger import get_previous_sle row_template = ["item_code", "warehouse", "qty", "valuation_rate"] if not self.reconciliation_json: msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1) data = json.loads(self.reconciliation_json) for row_num, row in enumerate(data[data.index(self.head_row)+1:]): row = frappe._dict(zip(row_template, row)) row["row_num"] = row_num if row.qty in ("", None) or row.valuation_rate in ("", None): previous_sle = get_previous_sle({ "item_code": row.item_code, "warehouse": row.warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time }) if row.qty in ("", None): row.qty = previous_sle.get("qty_after_transaction") if row.valuation_rate in ("", None): row.valuation_rate = previous_sle.get("valuation_rate") # if row.qty and not row.valuation_rate: # frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code)) self.insert_entries(row)
def test_delivery_note_no_gl_entry(self): set_perpetual_inventory(0) self.assertEqual(cint(frappe.defaults.get_global_default("auto_accounting_for_stock")), 0) make_stock_entry(target="_Test Warehouse - _TC", qty=5, basic_rate=100) stock_queue = json.loads( get_previous_sle( { "item_code": "_Test Item", "warehouse": "_Test Warehouse - _TC", "posting_date": nowdate(), "posting_time": nowtime(), } ).stock_queue or "[]" ) dn = create_delivery_note() sle = frappe.get_doc("Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name}) self.assertEqual(sle.stock_value_difference, -1 * stock_queue[0][1]) self.assertFalse(get_gl_entries("Delivery Note", dn.name))
def set_actual_qty(self): allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock")) for d in self.get("items"): previous_sle = get_previous_sle( { "item_code": d.item_code, "warehouse": d.s_warehouse or d.t_warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time, } ) # get actual stock at source warehouse d.actual_qty = previous_sle.get("qty_after_transaction") or 0 # validate qty during submit if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty: frappe.throw( _( """Row {0}: Qty not avalable in warehouse {1} on {2} {3}. Available Qty: {4}, Transfer Qty: {5}""" ).format(d.idx, d.s_warehouse, self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty), NegativeStockError, )
def set_actual_qty(self): allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock")) for d in self.get("items"): previous_sle = get_previous_sle( { "item_code": d.item_code, "warehouse": d.s_warehouse or d.t_warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time, } ) # get actual stock at source warehouse d.actual_qty = previous_sle.get("qty_after_transaction") or 0 # validate qty during submit if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty: frappe.throw( _( "Row {0}: Qty not available for {4} in warehouse {1} at posting time of the entry ({2} {3})".format( d.idx, frappe.bold(d.s_warehouse), formatdate(self.posting_date), format_time(self.posting_time), frappe.bold(d.item_code), ) ) + "<br><br>" + _("Available qty is {0}, you need {1}").format( frappe.bold(d.actual_qty), frappe.bold(d.transfer_qty) ), NegativeStockError, title=_("Insufficient Stock"), )
def update_stock_ledger(self): """ find difference between current and expected entries and create stock ledger entries based on the difference""" from erpnext.stock.stock_ledger import get_previous_sle for row in self.items: previous_sle = get_previous_sle({ "item_code": row.item_code, "warehouse": row.warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time }) if previous_sle: if row.qty in ("", None): row.qty = previous_sle.get("qty_after_transaction", 0) if row.valuation_rate in ("", None): row.valuation_rate = previous_sle.get("valuation_rate", 0) if row.qty and not row.valuation_rate: frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code)) if ((previous_sle and row.qty == previous_sle.get("qty_after_transaction") and row.valuation_rate == previous_sle.get("valuation_rate")) or (not previous_sle and not row.qty)): continue self.insert_entries(row)
def _test_reco_sle_gle(self, valuation_method): set_perpetual_inventory() # [[qty, valuation_rate, posting_date, # posting_time, expected_stock_value, bin_qty, bin_valuation]] input_data = [ [50, 1000, "2012-12-26", "12:00"], [25, 900, "2012-12-26", "12:00"], ["", 1000, "2012-12-20", "12:05"], [20, "", "2012-12-26", "12:05"], [0, "", "2012-12-31", "12:10"] ] for d in input_data: repost_stock_as_per_valuation_method(valuation_method) last_sle = get_previous_sle({ "item_code": "_Test Item", "warehouse": "_Test Warehouse - _TC", "posting_date": d[2], "posting_time": d[3] }) # submit stock reconciliation stock_reco = create_stock_reconciliation(qty=d[0], rate=d[1], posting_date=d[2], posting_time=d[3]) # check stock value sle = frappe.db.sql("""select * from `tabStock Ledger Entry` where voucher_type='Stock Reconciliation' and voucher_no=%s""", stock_reco.name, as_dict=1) qty_after_transaction = flt(d[0]) if d[0] != "" else flt(last_sle.get("qty_after_transaction")) valuation_rate = flt(d[1]) if d[1] != "" else flt(last_sle.get("valuation_rate")) if qty_after_transaction == last_sle.get("qty_after_transaction") \ and valuation_rate == last_sle.get("valuation_rate"): self.assertFalse(sle) else: self.assertEqual(sle[0].qty_after_transaction, qty_after_transaction) self.assertEqual(sle[0].stock_value, qty_after_transaction * valuation_rate) # no gl entries self.assertTrue(frappe.db.get_value("Stock Ledger Entry", {"voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name})) self.assertFalse(get_stock_and_account_difference(["_Test Account Stock In Hand - _TC"])) stock_reco.cancel() self.assertFalse(frappe.db.get_value("Stock Ledger Entry", {"voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name})) self.assertFalse(frappe.db.get_value("GL Entry", {"voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name})) set_perpetual_inventory(0)
def get_stock_and_rate(self, force=False): """get stock and incoming rate on posting date""" raw_material_cost = 0.0 if not self.posting_date or not self.posting_time: frappe.throw(_("Posting date and posting time is mandatory")) allow_negative_stock = cint(frappe.db.get_default("allow_negative_stock")) for d in self.get('mtn_details'): d.transfer_qty = flt(d.transfer_qty) args = frappe._dict({ "item_code": d.item_code, "warehouse": d.s_warehouse or d.t_warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time, "qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty, "serial_no": d.serial_no }) # get actual stock at source warehouse d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0 # validate qty during submit if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty: frappe.throw(_("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}. Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse, self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty)) # get incoming rate if not d.bom_no: if not flt(d.incoming_rate) or d.s_warehouse or self.purpose == "Sales Return" or force: incoming_rate = flt(self.get_incoming_rate(args), self.precision("incoming_rate", d)) if incoming_rate > 0: d.incoming_rate = incoming_rate d.amount = flt(flt(d.transfer_qty) * flt(d.incoming_rate), self.precision("amount", d)) if not d.t_warehouse: raw_material_cost += flt(d.amount) # set incoming rate for fg item if self.purpose in ["Manufacture", "Repack"]: number_of_fg_items = len([t.t_warehouse for t in self.get("mtn_details") if t.t_warehouse]) for d in self.get("mtn_details"): if d.bom_no or (d.t_warehouse and number_of_fg_items == 1): if not flt(d.incoming_rate) or force: operation_cost_per_unit = 0 if d.bom_no: bom = frappe.db.get_value("BOM", d.bom_no, ["operating_cost", "quantity"], as_dict=1) operation_cost_per_unit = flt(bom.operating_cost) / flt(bom.quantity) d.incoming_rate = flt(operation_cost_per_unit + (raw_material_cost + flt(self.total_fixed_cost)) / flt(d.transfer_qty), self.precision("incoming_rate", d)) d.amount = flt(flt(d.transfer_qty) * flt(d.incoming_rate), self.precision("transfer_qty", d)) break
def get_qty_after_transaction(**args): args = frappe._dict(args) last_sle = get_previous_sle({ "item_code": args.item_code or "_Test Item", "warehouse": args.warehouse or "_Test Warehouse - _TC", "posting_date": args.posting_date or nowdate(), "posting_time": args.posting_time or nowtime() }) return flt(last_sle.get("qty_after_transaction"))
def get_warehouse_details(self, args): ret = {} if args.get("warehouse") and args.get("item_code"): args.update({"posting_date": self.posting_date, "posting_time": self.posting_time}) args = frappe._dict(args) ret = { "actual_qty": get_previous_sle(args).get("qty_after_transaction") or 0, "basic_rate": get_incoming_rate(args), } return ret
def get_warehouse_details(args): if isinstance(args, basestring): args = json.loads(args) args = frappe._dict(args) ret = {} if args.warehouse and args.item_code: args.update({"posting_date": args.posting_date, "posting_time": args.posting_time}) ret = { "actual_qty": get_previous_sle(args).get("qty_after_transaction") or 0, "basic_rate": get_incoming_rate(args), } return ret
def get_opening_balance(filters, columns): if not (filters.item_code and filters.warehouse and filters.from_date): return from erpnext.stock.stock_ledger import get_previous_sle last_entry = get_previous_sle({ "item_code": filters.item_code, "warehouse_condition": get_warehouse_condition(filters.warehouse), "posting_date": filters.from_date, "posting_time": "00:00:00" }) row = [""]*len(columns) row[1] = _("'Opening'") for i, v in ((9, 'qty_after_transaction'), (11, 'valuation_rate'), (12, 'stock_value')): row[i] = last_entry.get(v, 0) return row
def get_stock_and_rate(self, force=False): """get stock and incoming rate on posting date""" raw_material_cost = 0.0 if not self.posting_date or not self.posting_time: frappe.throw(_("Posting date and posting time is mandatory")) allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock")) for d in self.get('items'): d.transfer_qty = flt(d.transfer_qty) args = frappe._dict({ "item_code": d.item_code, "warehouse": d.s_warehouse or d.t_warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time, "qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty, "serial_no": d.serial_no, }) # get actual stock at source warehouse d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0 # validate qty during submit if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty: frappe.throw(_("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}. Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse, self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty), NegativeStockError) # get incoming rate if not d.bom_no: if not flt(d.incoming_rate) or d.s_warehouse or self.purpose == "Sales Return" or force: incoming_rate = flt(self.get_incoming_rate(args), self.precision("incoming_rate", d)) if incoming_rate > 0: d.incoming_rate = incoming_rate d.amount = flt(flt(d.transfer_qty) * flt(d.incoming_rate), d.precision("amount")) if not d.t_warehouse: raw_material_cost += flt(d.amount) self.add_operation_cost(raw_material_cost, force)
def test_delivery_note_no_gl_entry(self): company = frappe.db.get_value('Warehouse', '_Test Warehouse - _TC', 'company') set_perpetual_inventory(0, company) make_stock_entry(target="_Test Warehouse - _TC", qty=5, basic_rate=100) stock_queue = json.loads(get_previous_sle({ "item_code": "_Test Item", "warehouse": "_Test Warehouse - _TC", "posting_date": nowdate(), "posting_time": nowtime() }).stock_queue or "[]") dn = create_delivery_note() sle = frappe.get_doc("Stock Ledger Entry", {"voucher_type": "Delivery Note", "voucher_no": dn.name}) self.assertEqual(sle.stock_value_difference, -1*stock_queue[0][1]) self.assertFalse(get_gl_entries("Delivery Note", dn.name))
def get_opening_balance(filters, columns): if not (filters.item_code and filters.warehouse and filters.from_date): return from erpnext.stock.stock_ledger import get_previous_sle last_entry = get_previous_sle({ "item_code": filters.item_code, "warehouse": filters.warehouse, "posting_date": filters.from_date, "posting_time": "00:00:00" }) row = [""] * len(columns) row[1] = _("'Opening'") for i, v in ((9, 'qty_after_transaction'), (11, 'valuation_rate'), (12, 'stock_value')): row[i] = last_entry.get(v, 0) return row
def set_actual_qty(self): allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock")) for d in self.get('items'): previous_sle = get_previous_sle({ "item_code": d.item_code, "warehouse": d.s_warehouse or d.t_warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time }) # get actual stock at source warehouse d.actual_qty = previous_sle.get("qty_after_transaction") or 0 # validate qty during submit if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty: frappe.throw(_("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}. Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse, self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty), NegativeStockError)
def get_rate(item_code, warehouse, based_on, price_list): time = frappe.utils.now_datetime().time() date = frappe.utils.now_datetime().date() balance = 0 if warehouse: previous_sle = get_previous_sle({ "item_code": item_code, "warehouse": warehouse, "posting_date": date, "posting_time": time }) # get actual stock at source warehouse balance = previous_sle.get("qty_after_transaction") or 0 condition = "" if price_list == "Standard Buying": condition += " and buying = 1 " elif price_list == "Standard Selling": condition += " and selling = 1 " query = """ SELECT * FROM `tabItem Price` WHERE item_code=%s {0} ORDER BY valid_from DESC LIMIT 1""".format( condition) item_price = frappe.db.sql(query, item_code, as_dict=1) rate = item_price[0].price_list_rate if len(item_price) > 0 else 0 print(based_on) if based_on == "Valuation Rate": print("WALA DIR") item_record = frappe.db.sql( """ SELECT * FROM `tabItem` WHERE item_code=%s""", item_code, as_dict=1) rate = item_record[0].valuation_rate if len(item_record) > 0 else 0 if based_on == "Last Purchase Rate": print("WALA DIR") item_record = frappe.db.sql( """ SELECT * FROM `tabItem` WHERE item_code=%s""", item_code, as_dict=1) rate = item_record[0].last_purchase_rate if len(item_record) > 0 else 0 return rate, balance
def get_warehouse_details(args): if isinstance(args, basestring): args = json.loads(args) args = frappe._dict(args) ret = {} if args.warehouse and args.item_code: args.update({ "posting_date": args.posting_date, "posting_time": args.posting_time, }) ret = { "actual_qty": get_previous_sle(args).get("qty_after_transaction") or 0, "basic_rate": get_incoming_rate(args) } return ret
def get_incoming_rate(args): """Get Incoming Rate based on valuation method""" from erpnext.stock.stock_ledger import get_previous_sle in_rate = 0 if (args.get("serial_no") or "").strip(): in_rate = get_avg_purchase_rate(args.get("serial_no")) else: valuation_method = get_valuation_method(args.get("item_code")) previous_sle = get_previous_sle(args) if valuation_method == 'FIFO': if not previous_sle: return 0.0 previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]') in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0 elif valuation_method == 'Moving Average': in_rate = previous_sle.get('valuation_rate') or 0 return in_rate
def get_stock_and_rate(self): """get stock and incoming rate on posting date""" for d in self.get('mtn_details'): args = frappe._dict({ "item_code": d.item_code, "warehouse": d.s_warehouse or d.t_warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time, "qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty, "serial_no": d.serial_no, "bom_no": d.bom_no, }) # get actual stock at source warehouse d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0 # get incoming rate if not flt(d.incoming_rate): d.incoming_rate = self.get_incoming_rate(args) d.amount = flt(d.transfer_qty) * flt(d.incoming_rate)
def get_stock_balance(item_code, warehouse, posting_date=None, posting_time=None, with_valuation_rate=False): """Returns stock balance quantity at given warehouse on given posting date or current date. If `with_valuation_rate` is True, will return tuple (qty, rate)""" from erpnext.stock.stock_ledger import get_previous_sle if not posting_date: posting_date = nowdate() if not posting_time: posting_time = nowtime() last_entry = get_previous_sle({ "item_code": item_code, "warehouse":warehouse, "posting_date": posting_date, "posting_time": posting_time }) if with_valuation_rate: return (last_entry.qty_after_transaction, last_entry.valuation_rate) if last_entry else (0.0, 0.0) else: return last_entry.qty_after_transaction if last_entry else 0.0
def get_stock_and_rate(self): """get stock and incoming rate on posting date""" for d in getlist(self.doclist, 'mtn_details'): args = frappe._dict({ "item_code": d.item_code, "warehouse": d.s_warehouse or d.t_warehouse, "posting_date": self.doc.posting_date, "posting_time": self.doc.posting_time, "qty": d.s_warehouse and -1*d.transfer_qty or d.transfer_qty, "serial_no": d.serial_no, "bom_no": d.bom_no, }) # get actual stock at source warehouse d.actual_qty = get_previous_sle(args).get("qty_after_transaction") or 0 # get incoming rate if not flt(d.incoming_rate): d.incoming_rate = self.get_incoming_rate(args) d.amount = flt(d.transfer_qty) * flt(d.incoming_rate)
def get_opening_balance(filters, columns): if not (filters.item_code and filters.warehouse and filters.from_date): return from erpnext.stock.stock_ledger import get_previous_sle last_entry = get_previous_sle({ "item_code": filters.item_code, "warehouse_condition": get_warehouse_condition(filters.warehouse), "posting_date": filters.from_date, "posting_time": "00:00:00" }) row = { "item_code": _("'Opening'"), "qty_after_transaction": last_entry.get("qty_after_transaction", 0), "valuation_rate": last_entry.get("valuation_rate", 0), "stock_value": last_entry.get("stock_value", 0) } return row
def get_stock_balance(item_code, warehouse, posting_date=None, posting_time=None, with_valuation_rate=False): """Returns stock balance quantity at given warehouse on given posting date or current date. If `with_valuation_rate` is True, will return tuple (qty, rate)""" from erpnext.stock.stock_ledger import get_previous_sle if not posting_date: posting_date = nowdate() if not posting_time: posting_time = nowtime() last_entry = get_previous_sle({ "item_code": item_code, "warehouse":warehouse, "posting_date": posting_date, "posting_time": posting_time }) if with_valuation_rate: return (last_entry.qty_after_transaction, last_entry.valuation_rate) if last_entry else (0.0, 0.0) else: return last_entry.qty_after_transaction or 0.0
def radpp_make_entry(self, args, allow_negative_stock=False, via_landed_cost_voucher=False): args.update({"doctype": "Stock Ledger Entry"}) sle = frappe.get_doc(args) sle.flags.ignore_permissions = 1 sle.allow_negative_stock=allow_negative_stock sle.via_landed_cost_voucher = via_landed_cost_voucher sle.valuation_rate = flt(sle.valuation_rate) sle.insert() parent = frappe.get_doc(sle.voucher_type,sle.voucher_no) # assert previous_sle_args = { "item_code": sle.item_code, "warehouse": sle.warehouse, "posting_date": parent.posting_date, "posting_time": parent.posting_time, "sle": sle.name } from erpnext.stock.stock_ledger import get_previous_sle previous_sle = get_previous_sle(previous_sle_args) prev_stock_value = (previous_sle.stock_value or 0.0) if previous_sle else 0.0 sle.valuation_rate = args['valuation_rate'] sle.qty_after_transaction = args['qty_after_transaction'] sle.stock_queue = [[sle.qty_after_transaction, sle.valuation_rate]] sle.stock_value = flt(sle.qty_after_transaction) * flt(sle.valuation_rate) # rounding as per precision sle.stock_value = flt(sle.stock_value, sle.precision) stock_value_difference = sle.stock_value - prev_stock_value sle.prev_stock_value = sle.stock_value # update current sle sle.stock_queue = json.dumps(sle.stock_queue) sle.stock_value_difference = stock_value_difference frappe.get_doc(sle).db_update() sle.save() sle.submit() return sle.name
def get_opening_balance(filters, _columns, date, balance_type, item_code): if not (item_code and filters.warehouse and date): return from erpnext.stock.stock_ledger import get_previous_sle last_entry = get_previous_sle({ "item_code": item_code, "warehouse_condition": get_warehouse_condition(filters.warehouse), "posting_date": date, "posting_time": "00:00:00" }) row = {} row["voucher_type"] = _(balance_type) for dummy, v in ((9, 'actual_qty')): row[v] = last_entry.get(v, 0) return row
def get_stock_balance(item_code, warehouse, posting_date=None, posting_time=None, with_valuation_rate=False, with_serial_no=False): """Returns stock balance quantity at given warehouse on given posting date or current date. If `with_valuation_rate` is True, will return tuple (qty, rate)""" from erpnext.stock.stock_ledger import get_previous_sle if not posting_date: posting_date = nowdate() if not posting_time: posting_time = nowtime() args = { "item_code": item_code, "warehouse": warehouse, "posting_date": posting_date, "posting_time": posting_time } last_entry = get_previous_sle(args) if with_valuation_rate: if with_serial_no: serial_nos = last_entry.get("serial_no") if (serial_nos and len(get_serial_nos_data(serial_nos)) < last_entry.qty_after_transaction): serial_nos = get_serial_nos_data_after_transactions(args) return ((last_entry.qty_after_transaction, last_entry.valuation_rate, serial_nos) if last_entry else (0.0, 0.0, 0.0)) else: return (last_entry.qty_after_transaction, last_entry.valuation_rate) if last_entry else (0.0, 0.0) else: return last_entry.qty_after_transaction if last_entry else 0.0
def insert_stock_ledger_entries(self): """ find difference between current and expected entries and create stock ledger entries based on the difference""" from erpnext.stock.utils import get_valuation_method from erpnext.stock.stock_ledger import get_previous_sle row_template = ["item_code", "warehouse", "qty", "valuation_rate"] if not self.doc.reconciliation_json: msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1) data = json.loads(self.doc.reconciliation_json) for row_num, row in enumerate(data[data.index(self.head_row)+1:]): row = frappe._dict(zip(row_template, row)) row["row_num"] = row_num previous_sle = get_previous_sle({ "item_code": row.item_code, "warehouse": row.warehouse, "posting_date": self.doc.posting_date, "posting_time": self.doc.posting_time }) # check valuation rate mandatory if row.qty != "" and not row.valuation_rate and \ flt(previous_sle.get("qty_after_transaction")) <= 0: frappe.throw(_("As existing qty for item: ") + row.item_code + _(" at warehouse: ") + row.warehouse + _(" is less than equals to zero in the system, valuation rate is mandatory for this item")) change_in_qty = row.qty != "" and \ (flt(row.qty) - flt(previous_sle.get("qty_after_transaction"))) change_in_rate = row.valuation_rate != "" and \ (flt(row.valuation_rate) - flt(previous_sle.get("valuation_rate"))) if get_valuation_method(row.item_code) == "Moving Average": self.sle_for_moving_avg(row, previous_sle, change_in_qty, change_in_rate) else: self.sle_for_fifo(row, previous_sle, change_in_qty, change_in_rate)
def update_stock_ledger(self): """ find difference between current and expected entries and create stock ledger entries based on the difference""" from erpnext.stock.stock_ledger import get_previous_sle sl_entries = [] for row in self.items: item = frappe.get_doc("Item", row.item_code) if item.has_serial_no or item.has_batch_no: self.get_sle_for_serialized_items(row, sl_entries) else: previous_sle = get_previous_sle({ "item_code": row.item_code, "warehouse": row.warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time }) if previous_sle: if row.qty in ("", None): row.qty = previous_sle.get("qty_after_transaction", 0) if row.valuation_rate in ("", None): row.valuation_rate = previous_sle.get("valuation_rate", 0) if row.qty and not row.valuation_rate: frappe.throw( _("Valuation Rate required for Item {0} at row {1}"). format(row.item_code, row.idx)) if ((previous_sle and row.qty == previous_sle.get("qty_after_transaction") and (row.valuation_rate == previous_sle.get("valuation_rate") or row.qty == 0)) or (not previous_sle and not row.qty)): continue sl_entries.append(self.get_sle_for_items(row)) if sl_entries: self.make_sl_entries(sl_entries)
def execute(filters=None): columns, data = get_columns(filters), [] time = frappe.utils.now_datetime().time() date = frappe.utils.now_datetime().date() average_stock_dates = get_dates(filters.get("average_stock")) item_group = filters.get("item_group") item = filters.get("item") condition = "" if item_group: condition += " and item_group='{0}'".format(item_group) if item: condition += " and name='{0}'".format(item) query = """ SELECT * FROM `tabItem` WHERE disabled=0 {0}""".format( condition) items = frappe.db.sql(query, as_dict=1) for item in items: query = """ SELECT * FROM `tabStock Ledger Entry` WHERE item_code='{0}' and actual_qty < 0""".format(item.name) sle_check = frappe.db.sql(query, as_dict=1) if len(sle_check) > 0: obj = { "item_code": item.name, } get_sle(item.name, average_stock_dates, obj) obj['current_stock'] = get_previous_sle({ "item_code": item.name, "posting_date": date, "posting_time": time }).get("qty_after_transaction") or 0 obj['stock_position'] = obj['current_stock'] - obj[ 'average_sell_stock'] obj['stock_month'] = obj['current_stock'] / ( obj['average_sale_per_month'] if obj['average_sale_per_month'] > 0 else 1) data.append(obj) print(data) return columns, data
def get_incoming_rate(args): """Get Incoming Rate based on valuation method""" if isinstance(args, basestring): args = json.loads(args) in_rate = 0 batch_wise_rate = cint( frappe.db.get_single_value( "Stock Settings", 'exact_cost_valuation_for_batch_wise_items')) if args.get("batch_no") and batch_wise_rate: in_rate = get_batch_rate(args.get("batch_no")) elif (args.get("serial_no") or "").strip(): in_rate = get_avg_purchase_rate(args.get("serial_no")) else: valuation_method = get_valuation_method(args.get("item_code")) previous_sle = get_previous_sle(args) if valuation_method == 'FIFO': if previous_sle: previous_stock_queue = json.loads( previous_sle.get('stock_queue', '[]') or '[]') in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0 elif valuation_method == 'Moving Average': in_rate = previous_sle.get('valuation_rate') or 0 if not in_rate: voucher_no = args.get('voucher_no') or args.get('name') in_rate = get_valuation_rate(args.get('item_code'), args.get('warehouse'), args.get('voucher_type'), voucher_no, args.get('allow_zero_valuation'), currency=erpnext.get_company_currency( args.get('company')), company=args.get('company')) return in_rate
def set_actual_qty(self): allow_negative_stock = cint(frappe.db.get_value("Stock Settings", None, "allow_negative_stock")) for d in self.get('items'): previous_sle = get_previous_sle({ "item_code": d.item_code, "warehouse": d.s_warehouse or d.t_warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time }) # get actual stock at source warehouse d.actual_qty = previous_sle.get("qty_after_transaction") or 0 # validate qty during submit if d.docstatus==1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty: frappe.throw(_("Row {0}: Qty not available for {4} in warehouse {1} at posting time of the entry ({2} {3})").format(d.idx, frappe.bold(d.s_warehouse), formatdate(self.posting_date), format_time(self.posting_time), frappe.bold(d.item_code)) + '<br><br>' + _("Available qty is {0}, you need {1}").format(frappe.bold(d.actual_qty), frappe.bold(d.transfer_qty)), NegativeStockError, title=_('Insufficient Stock'))
def set_actual_qty(self): allow_negative_stock = cint( frappe.db.get_value("Stock Settings", None, "allow_negative_stock")) allow_start = True for d in self.get('items'): previous_sle = get_previous_sle({ "item_code": d.item_code, "warehouse": self.warehouse, "posting_date": nowdate(), "posting_time": nowtime() }) # get actual stock at source warehouse d.actual_qty = previous_sle.get("qty_after_transaction") or 0 # validate qty if not allow_negative_stock and d.actual_qty < d.qty: allow_start = False return allow_start
def insert_stock_ledger_entries(self): """ find difference between current and expected entries and create stock ledger entries based on the difference""" from erpnext.stock.utils import get_valuation_method from erpnext.stock.stock_ledger import get_previous_sle row_template = ["item_code", "warehouse", "qty", "valuation_rate"] if not self.reconciliation_json: msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1) data = json.loads(self.reconciliation_json) for row_num, row in enumerate(data[data.index(self.head_row)+1:]): row = frappe._dict(zip(row_template, row)) row["row_num"] = row_num previous_sle = get_previous_sle({ "item_code": row.item_code, "warehouse": row.warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time }) # check valuation rate mandatory if row.qty != "" and not row.valuation_rate and \ flt(previous_sle.get("qty_after_transaction")) <= 0: frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code)) change_in_qty = row.qty != "" and \ (flt(row.qty) - flt(previous_sle.get("qty_after_transaction"))) change_in_rate = row.valuation_rate != "" and \ (flt(row.valuation_rate) - flt(previous_sle.get("valuation_rate"))) if get_valuation_method(row.item_code) == "Moving Average": self.sle_for_moving_avg(row, previous_sle, change_in_qty, change_in_rate) else: self.sle_for_fifo(row, previous_sle, change_in_qty, change_in_rate)
def update_stock_ledger(self): """ find difference between current and expected entries and create stock ledger entries based on the difference""" from erpnext.stock.stock_ledger import get_previous_sle row_template = ["item_code", "warehouse", "qty", "valuation_rate"] if not self.reconciliation_json: msgprint(_("""Stock Reconciliation file not uploaded"""), raise_exception=1) data = json.loads(self.reconciliation_json) for row_num, row in enumerate(data[data.index(self.head_row) + 1:]): row = frappe._dict(zip(row_template, row)) row["row_num"] = row_num if row.qty in ("", None) or row.valuation_rate in ("", None): previous_sle = get_previous_sle({ "item_code": row.item_code, "warehouse": row.warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time }) if row.qty in ("", None): row.qty = previous_sle.get("qty_after_transaction") if row.valuation_rate in ("", None): row.valuation_rate = previous_sle.get("valuation_rate") # if row.qty and not row.valuation_rate: # frappe.throw(_("Valuation Rate required for Item {0}").format(row.item_code)) self.insert_entries(row)
def get_incoming_rate(args): """Get Incoming Rate based on valuation method""" from erpnext.stock.stock_ledger import get_previous_sle in_rate = 0 if args.get("serial_no"): in_rate = get_avg_purchase_rate(args.get("serial_no")) elif args.get("bom_no"): result = frappe.db.sql("""select ifnull(total_cost, 0) / ifnull(quantity, 1) from `tabBOM` where name = %s and docstatus=1 and is_active=1""", args.get("bom_no")) in_rate = result and flt(result[0][0]) or 0 else: valuation_method = get_valuation_method(args.get("item_code")) previous_sle = get_previous_sle(args) if valuation_method == 'FIFO': if not previous_sle: return 0.0 previous_stock_queue = json.loads(previous_sle.get('stock_queue', '[]') or '[]') in_rate = get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0 elif valuation_method == 'Moving Average': in_rate = previous_sle.get('valuation_rate') or 0 return in_rate
def get_incoming_rate(args, raise_error_if_no_rate=True): """Get Incoming Rate based on valuation method""" from erpnext.stock.stock_ledger import get_previous_sle, get_valuation_rate if isinstance(args, string_types): args = json.loads(args) in_rate = 0 if (args.get("serial_no") or "").strip(): in_rate = get_avg_purchase_rate(args.get("serial_no")) else: valuation_method = get_valuation_method(args.get("item_code")) previous_sle = get_previous_sle(args) if valuation_method == "FIFO": if previous_sle: previous_stock_queue = json.loads( previous_sle.get("stock_queue", "[]") or "[]") in_rate = (get_fifo_rate(previous_stock_queue, args.get("qty") or 0) if previous_stock_queue else 0) elif valuation_method == "Moving Average": in_rate = previous_sle.get("valuation_rate") or 0 if not in_rate: voucher_no = args.get("voucher_no") or args.get("name") in_rate = get_valuation_rate( args.get("item_code"), args.get("warehouse"), args.get("voucher_type"), voucher_no, args.get("allow_zero_valuation"), currency=erpnext.get_company_currency(args.get("company")), company=args.get("company"), raise_error_if_no_rate=raise_error_if_no_rate, ) return flt(in_rate)
def add_exploded_items(self): exploded_items = {} for d in self.get('exploded_items'): exploded_items[d.item_code] = d self.set('exploded_items', []) from erpnext.stock.doctype.stock_entry.stock_entry import get_best_warehouse from erpnext.stock.stock_ledger import get_previous_sle posting_date = nowdate() posting_time = nowtime() for d in sorted(self.cur_exploded_items, key=itemgetter(0)): ch = self.append('exploded_items', {}) for i in self.cur_exploded_items[d].keys(): ch.set(i, self.cur_exploded_items[d][i]) best_warehouse,enough_stock = get_best_warehouse(ch.item_code,ch.stock_qty,company = self.company) if best_warehouse: ch.actual_qty = get_previous_sle({ "item_code": ch.item_code, "warehouse": best_warehouse, "posting_date": posting_date, "posting_time": posting_time }).get("qty_after_transaction") or 0 else: ch.actual_qty = 0 ch.source_warehouse = best_warehouse ch.amount = flt(ch.stock_qty) * flt(ch.rate) ch.qty_consumed_per_unit = flt(ch.stock_qty) if exploded_items.get(ch.item_code): ch.dutible = exploded_items[ch.item_code].dutible
def get_incoming_rate(self, args): incoming_rate = 0 if self.purpose == "Sales Return" and \ (self.delivery_note_no or self.sales_invoice_no): sle = frappe.db.sql("""select name, posting_date, posting_time, actual_qty, stock_value, warehouse from `tabStock Ledger Entry` where voucher_type = %s and voucher_no = %s and item_code = %s limit 1""", ((self.delivery_note_no and "Delivery Note" or "Sales Invoice"), self.delivery_note_no or self.sales_invoice_no, args.item_code), as_dict=1) if sle: args.update({ "posting_date": sle[0].posting_date, "posting_time": sle[0].posting_time, "sle": sle[0].name, "warehouse": sle[0].warehouse, }) previous_sle = get_previous_sle(args) incoming_rate = (flt(sle[0].stock_value) - flt(previous_sle.get("stock_value"))) / \ flt(sle[0].actual_qty) else: incoming_rate = get_incoming_rate(args) return incoming_rate
def get_incoming_rate(self, args): incoming_rate = 0 if self.doc.purpose == "Sales Return" and \ (self.doc.delivery_note_no or self.doc.sales_invoice_no): sle = frappe.db.sql("""select name, posting_date, posting_time, actual_qty, stock_value, warehouse from `tabStock Ledger Entry` where voucher_type = %s and voucher_no = %s and item_code = %s limit 1""", ((self.doc.delivery_note_no and "Delivery Note" or "Sales Invoice"), self.doc.delivery_note_no or self.doc.sales_invoice_no, args.item_code), as_dict=1) if sle: args.update({ "posting_date": sle[0].posting_date, "posting_time": sle[0].posting_time, "sle": sle[0].name, "warehouse": sle[0].warehouse, }) previous_sle = get_previous_sle(args) incoming_rate = (flt(sle[0].stock_value) - flt(previous_sle.get("stock_value"))) / \ flt(sle[0].actual_qty) else: incoming_rate = get_incoming_rate(args) return incoming_rate
def get_stock_and_rate(self, force=False): """get stock and incoming rate on posting date""" raw_material_cost = 0.0 if not self.posting_date or not self.posting_time: frappe.throw(_("Posting date and posting time is mandatory")) allow_negative_stock = cint( frappe.db.get_value("Stock Settings", None, "allow_negative_stock")) for d in self.get('items'): d.transfer_qty = flt(d.transfer_qty) args = frappe._dict({ "item_code": d.item_code, "warehouse": d.s_warehouse or d.t_warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time, "qty": d.s_warehouse and -1 * d.transfer_qty or d.transfer_qty, "serial_no": d.serial_no, }) # get actual stock at source warehouse d.actual_qty = get_previous_sle(args).get( "qty_after_transaction") or 0 # validate qty during submit if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty: frappe.throw( _("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}. Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse, self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty), NegativeStockError) # get incoming rate if not d.bom_no: if not flt( d.incoming_rate ) or d.s_warehouse or self.purpose == "Sales Return" or force: incoming_rate = flt(self.get_incoming_rate(args), self.precision("incoming_rate", d)) if incoming_rate > 0: d.incoming_rate = incoming_rate d.amount = flt( flt(d.transfer_qty) * flt(d.incoming_rate), d.precision("amount")) if not d.t_warehouse: raw_material_cost += flt(d.amount) self.add_operation_cost(raw_material_cost, force)
def _test_reco_sle_gle(self, valuation_method): insert_existing_sle(warehouse='Stores - TCP1') company = frappe.db.get_value('Warehouse', 'Stores - TCP1', 'company') # [[qty, valuation_rate, posting_date, # posting_time, expected_stock_value, bin_qty, bin_valuation]] input_data = [[50, 1000], [25, 900], ["", 1000], [20, ""], [0, ""]] for d in input_data: set_valuation_method("_Test Item", valuation_method) last_sle = get_previous_sle({ "item_code": "_Test Item", "warehouse": "Stores - TCP1", "posting_date": nowdate(), "posting_time": nowtime() }) # submit stock reconciliation stock_reco = create_stock_reconciliation( qty=d[0], rate=d[1], posting_date=nowdate(), posting_time=nowtime(), warehouse="Stores - TCP1", company=company, expense_account="Stock Adjustment - TCP1") # check stock value sle = frappe.db.sql("""select * from `tabStock Ledger Entry` where voucher_type='Stock Reconciliation' and voucher_no=%s""", stock_reco.name, as_dict=1) qty_after_transaction = flt(d[0]) if d[0] != "" else flt( last_sle.get("qty_after_transaction")) valuation_rate = flt(d[1]) if d[1] != "" else flt( last_sle.get("valuation_rate")) if qty_after_transaction == last_sle.get("qty_after_transaction") \ and valuation_rate == last_sle.get("valuation_rate"): self.assertFalse(sle) else: self.assertEqual(flt(sle[0].qty_after_transaction, 1), flt(qty_after_transaction, 1)) self.assertEqual( flt(sle[0].stock_value, 1), flt(qty_after_transaction * valuation_rate, 1)) # no gl entries self.assertTrue( frappe.db.get_value( "Stock Ledger Entry", { "voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name })) acc_bal, stock_bal, wh_list = get_stock_and_account_balance( "Stock In Hand - TCP1", stock_reco.posting_date, stock_reco.company) self.assertEqual(flt(acc_bal, 1), flt(stock_bal, 1)) stock_reco.cancel()
def update_stock_ledger(self): """ find difference between current and expected entries and create stock ledger entries based on the difference""" from erpnext.stock.stock_ledger import get_previous_sle sl_entries = [] has_serial_no = False has_batch_no = False for row in self.items: item = frappe.get_doc("Item", row.item_code) if item.has_batch_no: has_batch_no = True if item.has_serial_no or item.has_batch_no: has_serial_no = True self.get_sle_for_serialized_items(row, sl_entries) else: if row.serial_no or row.batch_no: frappe.throw(_("Row #{0}: Item {1} is not a Serialized/Batched Item. It cannot have a Serial No/Batch No against it.") \ .format(row.idx, frappe.bold(row.item_code))) previous_sle = get_previous_sle({ "item_code": row.item_code, "warehouse": row.warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time }) if previous_sle: if row.qty in ("", None): row.qty = previous_sle.get("qty_after_transaction", 0) if row.valuation_rate in ("", None): row.valuation_rate = previous_sle.get( "valuation_rate", 0) if row.qty and not row.valuation_rate: frappe.throw( _("Valuation Rate required for Item {0} at row {1}"). format(row.item_code, row.idx)) if ((previous_sle and row.qty == previous_sle.get("qty_after_transaction") and (row.valuation_rate == previous_sle.get("valuation_rate") or row.qty == 0)) or (not previous_sle and not row.qty)): continue sl_entries.append(self.get_sle_for_items(row)) if sl_entries: if has_serial_no: sl_entries = self.merge_similar_item_serial_nos(sl_entries) allow_negative_stock = False if has_batch_no: allow_negative_stock = True self.make_sl_entries(sl_entries, allow_negative_stock=allow_negative_stock) if has_serial_no and sl_entries: self.update_valuation_rate_for_serial_no()
def get_opening_balance(filters, columns): #frappe.msgprint(frappe._('get_opening_balance ' + str(filters))) if not (filters.item_code and filters.company): return warehouse_list = [] from erpnext.stock.stock_ledger import get_previous_sle if not (filters.item_code and filters.warehouse and filters.company): for warehouse in frappe.get_list("Warehouse", fields=["name"]): last_sle_for_each_warehouse = get_previous_sle({ "item_code": filters.item_code, "warehouse": warehouse['name'], "posting_date": datetime.datetime.now().date(), "posting_time": datetime.datetime.now().time() }) if last_sle_for_each_warehouse: warehouse_list.append({'warehouse': warehouse['name']}) else: last_sle_for_each_warehouse = get_previous_sle({ "item_code": filters.item_code, "warehouse": filters.warehouse, "posting_date": datetime.datetime.now().date(), "posting_time": datetime.datetime.now().time() }) if last_sle_for_each_warehouse: warehouse_list.append({'warehouse': filters.warehouse}) # frappe.msgprint(frappe._('len(warehouse_list) : ' + str(warehouse_list))) last_sle_for_each_warehouse = [ get_previous_sle({ "item_code": filters.item_code, "warehouse": warehouse['warehouse'], "posting_date": datetime.datetime.now().date(), "posting_time": datetime.datetime.now().time() }) for warehouse in warehouse_list ] rows = [] for index, balance in enumerate(last_sle_for_each_warehouse): #frappe.msgprint(frappe._('balance[qty_after_transaction] : ' + str(balance.get('qty_after_transaction')))) dict_sle_value = { 2: _("Opening"), 5: balance['qty_after_transaction'], 6: 0.0, 8: balance['warehouse'] } if index > 0: dict_sle_value[7] = balance[ 'qty_after_transaction'] + last_sle_for_each_warehouse[ index - 1]['qty_after_transaction'] if index == 0: dict_sle_value[7] = balance['qty_after_transaction'] if index < (len(last_sle_for_each_warehouse) - 1): next_ = last_sle_for_each_warehouse[index + 1] balance['qty_after_transaction'] = dict_sle_value[7] rows.append([ dict_sle_value.get(x) if x in dict_sle_value else "" for x in range(len(columns)) ]) return rows
def get_stock_and_rate(self, force=False): """get stock and incoming rate on posting date""" raw_material_cost = 0.0 if not self.posting_date or not self.posting_time: frappe.throw(_("Posting date and posting time is mandatory")) allow_negative_stock = cint( frappe.db.get_default("allow_negative_stock")) for d in self.get('mtn_details'): d.transfer_qty = flt(d.transfer_qty) args = frappe._dict({ "item_code": d.item_code, "warehouse": d.s_warehouse or d.t_warehouse, "posting_date": self.posting_date, "posting_time": self.posting_time, "qty": d.s_warehouse and -1 * d.transfer_qty or d.transfer_qty, "serial_no": d.serial_no }) # get actual stock at source warehouse d.actual_qty = get_previous_sle(args).get( "qty_after_transaction") or 0 # validate qty during submit if d.docstatus == 1 and d.s_warehouse and not allow_negative_stock and d.actual_qty < d.transfer_qty: frappe.throw( _("""Row {0}: Qty not avalable in warehouse {1} on {2} {3}. Available Qty: {4}, Transfer Qty: {5}""").format(d.idx, d.s_warehouse, self.posting_date, self.posting_time, d.actual_qty, d.transfer_qty)) # get incoming rate if not d.bom_no: if not flt( d.incoming_rate ) or d.s_warehouse or self.purpose == "Sales Return" or force: incoming_rate = flt(self.get_incoming_rate(args), self.precision("incoming_rate", d)) if incoming_rate > 0: d.incoming_rate = incoming_rate d.amount = flt( flt(d.transfer_qty) * flt(d.incoming_rate), self.precision("amount", d)) if not d.t_warehouse: raw_material_cost += flt(d.amount) # set incoming rate for fg item if self.purpose in ["Manufacture", "Repack"]: number_of_fg_items = len([ t.t_warehouse for t in self.get("mtn_details") if t.t_warehouse ]) for d in self.get("mtn_details"): if d.bom_no or (d.t_warehouse and number_of_fg_items == 1): if not flt(d.incoming_rate) or force: operation_cost_per_unit = 0 if d.bom_no: bom = frappe.db.get_value( "BOM", d.bom_no, ["operating_cost", "quantity"], as_dict=1) operation_cost_per_unit = flt( bom.operating_cost) / flt(bom.quantity) d.incoming_rate = flt( operation_cost_per_unit + (raw_material_cost + flt(self.total_fixed_cost)) / flt(d.transfer_qty), self.precision("incoming_rate", d)) d.amount = flt( flt(d.transfer_qty) * flt(d.incoming_rate), self.precision("transfer_qty", d)) break
def get_sle_for_serialized_items(self, row, sl_entries): from erpnext.stock.stock_ledger import get_previous_sle serial_nos = get_serial_nos(row.serial_no) # To issue existing serial nos if row.current_qty and (row.current_serial_no or row.batch_no): args = self.get_sle_for_items(row) args.update({ 'actual_qty': -1 * row.current_qty, 'serial_no': row.current_serial_no, 'batch_no': row.batch_no, 'valuation_rate': row.current_valuation_rate }) if row.current_serial_no: args.update({ 'qty_after_transaction': 0, }) sl_entries.append(args) for serial_no in serial_nos: args = self.get_sle_for_items(row, [serial_no]) previous_sle = get_previous_sle({ "item_code": row.item_code, "posting_date": self.posting_date, "posting_time": self.posting_time, "serial_no": serial_no }) if previous_sle and row.warehouse != previous_sle.get("warehouse"): # If serial no exists in different warehouse new_args = args.copy() new_args.update({ 'actual_qty': -1, 'qty_after_transaction': cint(previous_sle.get('qty_after_transaction')) - 1, 'warehouse': previous_sle.get("warehouse", '') or row.warehouse, 'valuation_rate': previous_sle.get("valuation_rate") }) sl_entries.append(new_args) if row.qty: args = self.get_sle_for_items(row) args.update({ 'actual_qty': row.qty, 'incoming_rate': row.valuation_rate, 'valuation_rate': row.valuation_rate }) sl_entries.append(args) if serial_nos == get_serial_nos(row.current_serial_no): # update valuation rate self.update_valuation_rate_for_serial_nos(row, serial_nos)
def test_item_cost_reposting(self): company = "_Test Company" # _Test Item for Reposting at Stores warehouse on 10-04-2020: Qty = 50, Rate = 100 create_stock_reconciliation(item_code="_Test Item for Reposting", warehouse="Stores - _TC", qty=50, rate=100, company=company, expense_account="Stock Adjustment - _TC" if frappe.get_all("Stock Ledger Entry") else "Temporary Opening - _TC", posting_date='2020-04-10', posting_time='14:00') # _Test Item for Reposting at FG warehouse on 20-04-2020: Qty = 10, Rate = 200 create_stock_reconciliation(item_code="_Test Item for Reposting", warehouse="Finished Goods - _TC", qty=10, rate=200, company=company, expense_account="Stock Adjustment - _TC" if frappe.get_all("Stock Ledger Entry") else "Temporary Opening - _TC", posting_date='2020-04-20', posting_time='14:00') # _Test Item for Reposting transferred from Stores to FG warehouse on 30-04-2020 se = make_stock_entry(item_code="_Test Item for Reposting", source="Stores - _TC", target="Finished Goods - _TC", company=company, qty=10, expense_account="Stock Adjustment - _TC" if frappe.get_all("Stock Ledger Entry") else "Temporary Opening - _TC", posting_date='2020-04-30', posting_time='14:00') target_wh_sle = frappe.db.get_value('Stock Ledger Entry', { "item_code": "_Test Item for Reposting", "warehouse": "Finished Goods - _TC", "voucher_type": "Stock Entry", "voucher_no": se.name }, ["valuation_rate"], as_dict=1) self.assertEqual(target_wh_sle.get("valuation_rate"), 150) # Repack entry on 5-5-2020 repack = create_repack_entry(company=company, posting_date='2020-05-05', posting_time='14:00') finished_item_sle = frappe.db.get_value('Stock Ledger Entry', { "item_code": "_Test Finished Item for Reposting", "warehouse": "Finished Goods - _TC", "voucher_type": "Stock Entry", "voucher_no": repack.name }, ["incoming_rate", "valuation_rate"], as_dict=1) self.assertEqual(finished_item_sle.get("incoming_rate"), 540) self.assertEqual(finished_item_sle.get("valuation_rate"), 540) # Reconciliation for _Test Item for Reposting at Stores on 12-04-2020: Qty = 50, Rate = 150 sr = create_stock_reconciliation( item_code="_Test Item for Reposting", warehouse="Stores - _TC", qty=50, rate=150, company=company, expense_account="Stock Adjustment - _TC" if frappe.get_all("Stock Ledger Entry") else "Temporary Opening - _TC", posting_date='2020-04-12', posting_time='14:00') # Check valuation rate of finished goods warehouse after back-dated entry at Stores target_wh_sle = get_previous_sle({ "item_code": "_Test Item for Reposting", "warehouse": "Finished Goods - _TC", "posting_date": '2020-04-30', "posting_time": '14:00' }) self.assertEqual(target_wh_sle.get("incoming_rate"), 150) self.assertEqual(target_wh_sle.get("valuation_rate"), 175) # Check valuation rate of repacked item after back-dated entry at Stores finished_item_sle = frappe.db.get_value('Stock Ledger Entry', { "item_code": "_Test Finished Item for Reposting", "warehouse": "Finished Goods - _TC", "voucher_type": "Stock Entry", "voucher_no": repack.name }, ["incoming_rate", "valuation_rate"], as_dict=1) self.assertEqual(finished_item_sle.get("incoming_rate"), 790) self.assertEqual(finished_item_sle.get("valuation_rate"), 790) # Check updated rate in Repack entry repack.reload() self.assertEqual(repack.items[0].get("basic_rate"), 150) self.assertEqual(repack.items[1].get("basic_rate"), 750)
def _test_reco_sle_gle(self, valuation_method): set_perpetual_inventory() # [[qty, valuation_rate, posting_date, # posting_time, expected_stock_value, bin_qty, bin_valuation]] input_data = [[50, 1000, "2012-12-26", "12:00"], [25, 900, "2012-12-26", "12:00"], ["", 1000, "2012-12-20", "12:05"], [20, "", "2012-12-26", "12:05"], [0, "", "2012-12-31", "12:10"]] for d in input_data: set_valuation_method("_Test Item", valuation_method) last_sle = get_previous_sle({ "item_code": "_Test Item", "warehouse": "_Test Warehouse - _TC", "posting_date": d[2], "posting_time": d[3] }) # submit stock reconciliation stock_reco = create_stock_reconciliation(qty=d[0], rate=d[1], posting_date=d[2], posting_time=d[3]) # check stock value sle = frappe.db.sql("""select * from `tabStock Ledger Entry` where voucher_type='Stock Reconciliation' and voucher_no=%s""", stock_reco.name, as_dict=1) qty_after_transaction = flt(d[0]) if d[0] != "" else flt( last_sle.get("qty_after_transaction")) valuation_rate = flt(d[1]) if d[1] != "" else flt( last_sle.get("valuation_rate")) if qty_after_transaction == last_sle.get("qty_after_transaction") \ and valuation_rate == last_sle.get("valuation_rate"): self.assertFalse(sle) else: self.assertEqual(sle[0].qty_after_transaction, qty_after_transaction) self.assertEqual(sle[0].stock_value, qty_after_transaction * valuation_rate) # no gl entries self.assertTrue( frappe.db.get_value( "Stock Ledger Entry", { "voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name })) self.assertFalse( get_stock_and_account_difference( ["_Test Account Stock In Hand - _TC"])) stock_reco.cancel() self.assertFalse( frappe.db.get_value( "Stock Ledger Entry", { "voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name })) self.assertFalse( frappe.db.get_value( "GL Entry", { "voucher_type": "Stock Reconciliation", "voucher_no": stock_reco.name })) set_perpetual_inventory(0)