fixes to bin for reposting future entires
diff --git a/erpnext/patches/patch.py b/erpnext/patches/patch.py
index 5dd8b8d..9a82945 100644
--- a/erpnext/patches/patch.py
+++ b/erpnext/patches/patch.py
@@ -345,7 +345,7 @@
for b in bin:
bobj = get_obj('Bin',b[0])
prev_sle = bobj.get_prev_sle(posting_date = '2011-09-01', posting_time = '01:00')
- bobj.update_item_valuation(posting_date = '2011-09-01', posting_time = '01:00', prev_sle = prev_sle)
+ bobj.update_entries_after(posting_date = '2011-09-01', posting_time = '01:00', prev_sle = prev_sle)
elif patch_no == 368:
from webnotes.utils import nestedset
t = [
diff --git a/erpnext/stock/doctype/bin/bin.py b/erpnext/stock/doctype/bin/bin.py
index 13c0fee..d1cf65e 100644
--- a/erpnext/stock/doctype/bin/bin.py
+++ b/erpnext/stock/doctype/bin/bin.py
@@ -1,18 +1,11 @@
# Please edit this list and import only required elements
import webnotes
-from webnotes.utils import add_days, add_months, add_years, cint, cstr, date_diff, default_fields, flt, fmt_money, formatdate, generate_hash, getTraceback, get_defaults, get_first_day, get_last_day, getdate, has_common, month_name, now, nowdate, replace_newlines, sendmail, set_default, str_esc_quote, user_format, validate_email_add
-from webnotes.model import db_exists
-from webnotes.model.doc import Document, addchild, removechild, getchildren, make_autoname, SuperDocType
-from webnotes.model.doclist import getlist, copy_doclist
-from webnotes.model.code import get_obj, get_server_obj, run_server_obj, updatedb, check_syntax
-from webnotes import session, form, is_testing, msgprint, errprint
+from webnotes.utils import cint, cstr, flt
+from webnotes.model.code import get_obj
+from webnotes import msgprint
-set = webnotes.conn.set
sql = webnotes.conn.sql
-get_value = webnotes.conn.get_value
-in_transaction = webnotes.conn.in_transaction
-convert_to_lists = webnotes.conn.convert_to_lists
# -----------------------------------------------------------------------------------------
@@ -45,13 +38,7 @@
if serial_no:
self.check_qty_with_serial_no()
- prev_sle = self.get_prev_sle(dt, posting_time, sle_id)
- cqty = flt(prev_sle.get('bin_aqat', 0))
- # Block if actual qty becomes negative
- if (flt(cqty) + flt(actual_qty)) < 0 and flt(actual_qty) < 0 and is_cancelled == 'No':
- msgprint('Not enough quantity (requested: %s, current: %s) for Item <b>%s</b> in Warehouse <b>%s</b> as on %s %s' % (flt(actual_qty), flt(cqty), self.doc.item_code, self.doc.warehouse, dt, posting_time), raise_exception = 1)
-
- self.update_item_valuation(sle_id, dt, posting_time, serial_no, prev_sle)
+ self.update_entries_after(dt, posting_time)
def check_qty_with_serial_no(self):
"""
@@ -81,33 +68,22 @@
""", (self.doc.item_code, self.doc.warehouse), as_dict=1)
return sle and sle[0] or None
- # --------------------------------
- # get previous stock ledger entry
- # --------------------------------
-
- def get_prev_sle(self, posting_date, posting_time, sle_id = ''):
- # this function will only be called for a live entry
- # for which the "name" will be the latest (even for the same timestamp)
- # and even for a back-dated entry
- # hence there cannot be any "backdated entries" with a name greater than the
- # current one
-
- # if there are multiple entries on this timestamp, then the last one will be with
- # the last "name"
- # else, the last entry will be the highest name at the previous timestamp
- # hence, the double sort on timestamp and name should be sufficient condition
- # to get the last sle
+ def get_prev_sle(self, posting_date = '0000-00-00', posting_time = '00:00'):
+ """get previous stock ledger entry"""
+ # get the last sle before the current time-bucket, so that all values
+ # are reposted from the current time-bucket onwards.
+ # this is necessary because at the time of cancellation, there may be
+ # entries between the cancelled entries in the same time-bucket
sle = sql("""
select * from `tabStock Ledger Entry`
where item_code = %s
and warehouse = %s
- and name != %s
and ifnull(is_cancelled, 'No') = 'No'
- and timestamp(posting_date, posting_time) <= timestamp(%s, %s)
+ and timestamp(posting_date, posting_time) < timestamp(%s, %s)
order by timestamp(posting_date, posting_time) desc, name desc
limit 1
- """, (self.doc.item_code, self.doc.warehouse, sle_id, posting_date, posting_time), as_dict=1)
+ """, (self.doc.item_code, self.doc.warehouse, posting_date, posting_time), as_dict=1)
return sle and sle[0] or {}
@@ -121,11 +97,13 @@
diff = cqty + s['actual_qty']
if diff < 0 and (abs(diff) > 0.0001) and s['is_cancelled'] != 'Yes':
msgprint("""
+ Negative stock error:
Cannot complete this transaction because stock will
- become negative (%s) in future transaction for Item
- <b>%s</b> in Warehouse <b>%s</b> on <b>%s %s</b>""" % \
+ become negative (%s) for Item <b>%s</b> in Warehouse
+ <b>%s</b> on <b>%s %s</b> in Transaction %s %s""" % \
(str(diff), self.doc.item_code, self.doc.warehouse,
- s['posting_date'], s['posting_time']), raise_exception=1)
+ s['posting_date'], s['posting_time'], s['voucher_type'], s['voucher_no']), \
+ raise_exception=1)
# ------------------------------------
# get serialized inventory values
@@ -222,11 +200,14 @@
stock_val = sum([flt(d[0])*flt(d[1]) for d in self.fcfs_bal])
return stock_val
- # ----------------------
- # update item valuation
- # ----------------------
- def update_item_valuation(self, sle_id=None, posting_date=None, posting_time=None, serial_no=None, prev_sle=None):
- # no sle given, start from the first one (for repost)
+ def update_entries_after(self, posting_date, posting_time):
+ """update item valution from the give stock ledger entry (sle)
+ onwards."""
+
+ # Get prev sle
+ prev_sle = self.get_prev_sle(posting_date, posting_time)
+
+ # if no prev sle, start from the first one (for repost)
if not prev_sle:
cqty, cval, val_rate, self.fcfs_bal = 0, 0, 0, []
@@ -237,11 +218,11 @@
val_rate = flt(prev_sle.get('valuation_rate', 0))
self.fcfs_bal = eval(prev_sle.get('fcfs_stack', '[]') or '[]')
- val_method = get_obj('Valuation Control').get_valuation_method(self.doc.item_code) # get valuation method
+ # get valuation method
+ val_method = get_obj('Valuation Control').get_valuation_method(self.doc.item_code)
# recalculate the balances for all stock ledger entries
- # after this one (so that the corrected balance will reflect
- # correctly in all entries after this one)
+ # after the prev sle
sll = sql("""
select *
from `tabStock Ledger Entry`
@@ -250,30 +231,32 @@
and ifnull(is_cancelled, 'No') = 'No'
and timestamp(posting_date, posting_time) > timestamp(%s, %s)
order by timestamp(posting_date, posting_time) asc, name asc""", \
- (self.doc.item_code, self.doc.warehouse, posting_date, posting_time), as_dict = 1)
+ (self.doc.item_code, self.doc.warehouse, \
+ prev_sle.get('posting_date','0000-00-00'), prev_sle.get('posting_time', '00:00')), as_dict = 1)
- # if in live entry - update the values of the current sle
- if sle_id:
- sll = sql("select * from `tabStock Ledger Entry` where name=%s and ifnull(is_cancelled, 'No') = 'No'", sle_id, as_dict=1) + sll
- for s in sll:
+ for sle in sll:
# block if stock level goes negative on any date
- self.validate_negative_stock(cqty, s)
+ self.validate_negative_stock(cqty, sle)
- stock_val, in_rate = 0, s['incoming_rate'] # IN
- serial_nos = s["serial_no"] and ("'"+"', '".join(cstr(s["serial_no"]).split('\n')) + "'") or ''
+ stock_val, in_rate = 0, sle['incoming_rate'] # IN
+ serial_nos = sle["serial_no"] and ("'"+"', '".join(cstr(sle["serial_no"]).split('\n')) \
+ + "'") or ''
# Get valuation rate
- val_rate, stock_val = self.get_valuation_rate(val_method, serial_nos, val_rate, in_rate, stock_val, cqty, s)
+ val_rate, stock_val = self.get_valuation_rate(val_method, serial_nos, \
+ val_rate, in_rate, stock_val, cqty, s)
# Qty upto the sle
- cqty += s['actual_qty']
+ cqty += sle['actual_qty']
# Stock Value upto the sle
stock_val = self.get_stock_value(val_method, cqty, stock_val, serial_nos)
- # update current sle --> will it be good to update incoming rate in sle for outgoing stock entry?????
+
+ # update current sle --> will it be good to update incoming rate in sle
+ # for outgoing stock entry?????
sql("""update `tabStock Ledger Entry`
set bin_aqat=%s, valuation_rate=%s, fcfs_stack=%s, stock_value=%s
- where name=%s""", (cqty, flt(val_rate), cstr(self.fcfs_bal), stock_val, s['name']))
+ where name=%s""", (cqty, flt(val_rate), cstr(self.fcfs_bal), stock_val, sle['name']))
# update the bin
if sll:
diff --git a/erpnext/stock/doctype/landed_cost_wizard/landed_cost_wizard.py b/erpnext/stock/doctype/landed_cost_wizard/landed_cost_wizard.py
index 58ba751..e1d5774 100644
--- a/erpnext/stock/doctype/landed_cost_wizard/landed_cost_wizard.py
+++ b/erpnext/stock/doctype/landed_cost_wizard/landed_cost_wizard.py
@@ -86,9 +86,9 @@
d.save()
sql("update `tabStock Ledger Entry` set incoming_rate = '%s' where voucher_detail_no = '%s'"%(flt(d.valuation_rate), d.name))
- bin_name = sql("select t1.name, t2.name, t2.posting_date, t2.posting_time from `tabBin` t1, `tabStock Ledger Entry` t2 where t2.voucher_detail_no = '%s' and t2.item_code = t1.item_code and t2.warehouse = t1.warehouse LIMIT 1"%(d.name))
+ bin_name = sql("select t1.name, t2.posting_date, t2.posting_time from `tabBin` t1, `tabStock Ledger Entry` t2 where t2.voucher_detail_no = '%s' and t2.item_code = t1.item_code and t2.warehouse = t1.warehouse LIMIT 1"%(d.name))
if bin_name and bin_name[0][0]:
- obj = get_obj('Bin', bin_name[0][0]).update_item_valuation(bin_name[0][1], bin_name[0][2], bin_name[0][3])
+ obj = get_obj('Bin', bin_name[0][0]).update_entries_after(bin_name[0][1], bin_name[0][2])
# now distribute the taxes among the PRs
for lc in getlist(self.doclist, 'landed_cost_details'):
@@ -140,11 +140,11 @@
d.valuation_rate = (flt(d.purchase_rate) + (flt(d.rm_supp_cost) / flt(d.qty)) + (flt(d.item_tax_amount)/flt(d.qty))) / flt(d.conversion_factor)
d.save()
sql("update `tabStock Ledger Entry` set incoming_rate = '%s' where voucher_detail_no = '%s'"%(flt(d.valuation_rate), d.name))
- bin_name = sql("select t1.name, t2.name, t2.posting_date, t2.posting_time from `tabBin` t1, `tabStock Ledger Entry` t2 where t2.voucher_detail_no = '%s' and t2.item_code = t1.item_code and t2.warehouse = t1.warehouse LIMIT 1"%(d.name))
+ bin_name = sql("select t1.name, t2.posting_date, t2.posting_time from `tabBin` t1, `tabStock Ledger Entry` t2 where t2.voucher_detail_no = '%s' and t2.item_code = t1.item_code and t2.warehouse = t1.warehouse LIMIT 1"%(d.name))
# update valuation of the item
if bin_name and bin_name[0][0]:
- obj = get_obj('Bin', bin_name[0][0]).update_item_valuation(bin_name[0][1], bin_name[0][2], bin_name[0][3])
+ obj = get_obj('Bin', bin_name[0][0]).update_entries_after(bin_name[0][1], bin_name[0][2])
def add_deduct_taxes(self, ocd, oc, tax_amount, total_amount, total, prev_total, f=1):
ocd[oc].total_amount = flt(tax_amount.toFixed(2))
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index f040df3..e133d6f 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -131,18 +131,18 @@
update item valuation in previous date and also on post date if no qty diff
"""
- self.update_item_valuation_pre_date(d)
+ self.update_entries_pre_date(d)
if not flt(d[self.label['qty']]) and not flt(d[self.label['actual_qty']]):
# seems like a special condition when there is no actual quanitity but there is a rate, may be only for setting a rate!
self.make_sl_entry(1,d,1)
self.make_sl_entry(1,d,-1)
elif not qty_diff:
- self.update_item_valuation_post_date(d)
+ self.update_entries_post_date(d)
# update valuation rate as csv file in all sle before reconciliation date
# ------------------------------------------------------------------------
- def update_item_valuation_pre_date(self, d):
+ def update_entries_pre_date(self, d):
mar = flt(d[self.label['mar']])
# previous sle
@@ -168,15 +168,12 @@
# Update item valuation in all sle after the reconcliation date
# ---------------------------------------------------------
- def update_item_valuation_post_date(self, d):
+ def update_entries_post_date(self, d):
bin = sql("select name from `tabBin` where item_code = '%s' and warehouse = '%s'" % (d[self.label['item_code']], d[self.label['warehouse']]))
bin_obj = get_obj('Bin', bin[0][0])
- # prev sle
- prev_sle = bin_obj.get_prev_sle(self.doc.reconciliation_date,self.doc.reconciliation_time)
-
# update valuation in sle posted after reconciliation datetime
- bin_obj.update_item_valuation(posting_date = self.doc.reconciliation_date, posting_time = self.doc.reconciliation_time, prev_sle = prev_sle)
+ bin_obj.update_entries_after(posting_date = self.doc.reconciliation_date, posting_time = self.doc.reconciliation_time)
# --------------
# make sl entry
diff --git a/erpnext/stock/doctype/stock_uom_replace_utility/stock_uom_replace_utility.py b/erpnext/stock/doctype/stock_uom_replace_utility/stock_uom_replace_utility.py
index f8ac737..0fa7a3b 100644
--- a/erpnext/stock/doctype/stock_uom_replace_utility/stock_uom_replace_utility.py
+++ b/erpnext/stock/doctype/stock_uom_replace_utility/stock_uom_replace_utility.py
@@ -85,7 +85,7 @@
for w in wh:
bin = sql("select name from `tabBin` where item_code = '%s' and warehouse = '%s'" % (self.doc.item_code, w[0]))
if bin and bin[0][0]:
- get_obj("Bin", bin[0][0]).update_item_valuation(sle_id = '', posting_date = '', posting_time = '')
+ get_obj("Bin", bin[0][0]).update_entries_after(posting_date = '', posting_time = '')
# acknowledge user
msgprint("Item Valuation Updated Successfully.")
diff --git a/erpnext/stock/doctype/warehouse/warehouse.py b/erpnext/stock/doctype/warehouse/warehouse.py
index f3d600d..7384c19 100644
--- a/erpnext/stock/doctype/warehouse/warehouse.py
+++ b/erpnext/stock/doctype/warehouse/warehouse.py
@@ -68,7 +68,7 @@
bl = sql("select name from tabBin where warehouse=%s", self.doc.name)
for b in bl:
bobj = get_obj('Bin',b[0])
- bobj.update_item_valuation(posting_date = '2000-01-01', posting_time = '12:00')
+ bobj.update_entries_after(posting_date = '2000-01-01', posting_time = '12:00')
sql("COMMIT")
sql("START TRANSACTION")
diff --git a/erpnext/utilities/doctype/reposting_tool/reposting_tool.py b/erpnext/utilities/doctype/reposting_tool/reposting_tool.py
index c4f021b..489a3b6 100644
--- a/erpnext/utilities/doctype/reposting_tool/reposting_tool.py
+++ b/erpnext/utilities/doctype/reposting_tool/reposting_tool.py
@@ -88,7 +88,7 @@
bin_act_qty = flt(bin_obj.doc.actual_qty)
try:
# udpate actual qty and item valuation
- bin_obj.update_item_valuation('', '2000-01-01', '00:00')
+ bin_obj.update_entries_after('0000-00-00', '00:00')
# get bin qty
qty_dict = self.get_bin_qty(bin_obj.doc.warehouse, bin_obj.doc.item_code)