ignored cancelled sle in valuation, fixed isue in fetching as on qty and rate in stock entry and cleaning up of code
diff --git a/material_management/doctype/bin/bin.py b/material_management/doctype/bin/bin.py
index dfaf81a..ba8398a 100644
--- a/material_management/doctype/bin/bin.py
+++ b/material_management/doctype/bin/bin.py
@@ -25,7 +25,7 @@
# -------------
# stock update
# -------------
- def update_stock(self, actual_qty=0, reserved_qty=0, ordered_qty=0, indented_qty=0, planned_qty=0, dt=None, sle_id='', posting_time='', serial_no = ''):
+ def update_stock(self, actual_qty=0, reserved_qty=0, ordered_qty=0, indented_qty=0, planned_qty=0, dt=None, sle_id='', posting_time='', serial_no = '', is_cancelled = 'No'):
if not dt: dt = nowdate()
# update the stock values (for current quantities)
@@ -40,10 +40,10 @@
# update valuation for post dated entry
if actual_qty:
- prev_sle = self.get_prev_sle(sle_id, dt, posting_time, 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:
+ 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)
@@ -57,6 +57,7 @@
select * from `tabStock Ledger Entry`
where item_code = %s
and warehouse = %s
+ and ifnull(is_cancelled, 'No') = 'No'
order by timestamp(posting_date, posting_time) asc, name asc
limit 1
""", (self.doc.item_code, self.doc.warehouse), as_dict=1)
@@ -66,7 +67,7 @@
# get previous stock ledger entry
# --------------------------------
- def get_prev_sle(self, sle_id, posting_date, posting_time, serial_no = ''):
+ 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
@@ -84,6 +85,7 @@
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)
order by timestamp(posting_date, posting_time) desc, name desc
limit 1
@@ -101,8 +103,8 @@
if cqty + s['actual_qty'] < 0 and s['is_cancelled'] != 'Yes':
msgprint(cqty)
msgprint(s['actual_qty'])
- msgprint('Cannot complete this transaction because stock will become negative for Item <b>%s</b> in Warehouse <b>%s</b> on Posting Date <b>%s</b>' % \
- (self.doc.item_code, self.doc.warehouse, s['posting_date']))
+ msgprint('Cannot complete this transaction because stock will become negative in future transaction for Item <b>%s</b> in Warehouse <b>%s</b> on <b>%s %s</b>' % \
+ (self.doc.item_code, self.doc.warehouse, s['posting_date'], s['posting_time']))
raise Exception
# ------------------------------------
@@ -148,14 +150,13 @@
# --------------------------
# get fifo inventory values
# --------------------------
- def get_fifo_inventory_values(self, val_rate, in_rate, actual_qty, incoming_rate):
+ def get_fifo_inventory_values(self, in_rate, actual_qty):
# add batch to fcfs balance
if actual_qty > 0:
self.fcfs_bal.append([flt(actual_qty), flt(in_rate)])
- val_rate = incoming_rate
+
# remove from fcfs balance
else:
- fcfs_val = 0
withdraw = flt(abs(actual_qty))
while withdraw:
if not self.fcfs_bal:
@@ -163,21 +164,21 @@
batch = self.fcfs_bal[0]
- if batch[0] < withdraw:
- # not enough in current batch, clear batch
+ if batch[0] <= withdraw:
+ # not enough or exactly same qty in current batch, clear batch
withdraw -= batch[0]
- fcfs_val += (flt(batch[0]) * flt(batch[1]))
self.fcfs_bal.pop(0)
else:
# all from current batch
- fcfs_val += (flt(withdraw) * flt(batch[1]))
batch[0] -= withdraw
withdraw = 0
- val_rate = flt(fcfs_val) / flt(abs(actual_qty))
+ fcfs_val = sum([flt(d[0])*flt(d[1]) for d in self.fcfs_bal])
+ fcfs_qty = sum([flt(d[0]) for d in self.fcfs_bal])
+ val_rate = fcfs_qty and fcfs_val / fcfs_qty or 0
+
return val_rate
-
# -------------------
# get valuation rate
# -------------------
@@ -187,7 +188,7 @@
elif val_method == 'Moving Average':
val_rate, stock_val = self.get_moving_average_inventory_values(val_rate, in_rate, opening_qty = cqty, actual_qty = s['actual_qty'], is_cancelled = s['is_cancelled'])
elif val_method == 'FIFO':
- val_rate = self.get_fifo_inventory_values(val_rate, in_rate, actual_qty = s['actual_qty'], incoming_rate = s['incoming_rate'])
+ val_rate = self.get_fifo_inventory_values(in_rate, actual_qty = s['actual_qty'])
return val_rate, stock_val
@@ -198,8 +199,7 @@
if val_method == 'Moving Average' or serial_nos:
stock_val = flt(stock_val) * flt(cqty)
elif val_method == 'FIFO':
- for d in self.fcfs_bal:
- stock_val += (flt(d[0]) * flt(d[1]))
+ stock_val = sum([flt(d[0])*flt(d[1]) for d in self.fcfs_bal])
return stock_val
# ----------------------
@@ -227,19 +227,20 @@
from `tabStock Ledger Entry`
where item_code = %s
and warehouse = %s
+ 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)
# if in live entry - update the values of the current sle
if sle_id:
- sll = sql("select * from `tabStock Ledger Entry` where name=%s", sle_id, as_dict=1) + sll
+ 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:
# block if stock level goes negative on any date
self.validate_negative_stock(cqty, s)
stock_val, in_rate = 0, s['incoming_rate'] # IN
- serial_nos = "'"+"', '".join(cstr(s["serial_no"]).split('\n')) + "'"
+ serial_nos = s["serial_no"] and ("'"+"', '".join(cstr(s["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)
@@ -249,7 +250,6 @@
# 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?????
sql("""update `tabStock Ledger Entry`
set bin_aqat=%s, valuation_rate=%s, fcfs_stack=%s, stock_value=%s
diff --git a/material_management/doctype/stock_entry/stock_entry.js b/material_management/doctype/stock_entry/stock_entry.js
index f202235..161e094 100644
--- a/material_management/doctype/stock_entry/stock_entry.js
+++ b/material_management/doctype/stock_entry/stock_entry.js
@@ -137,13 +137,14 @@
cur_frm.cscript.item_code = function(doc, cdt, cdn) {
var d = locals[cdt][cdn];
- cal_back = function(r, rt){ /*cur_frm.cscript.calc_amount(doc)*/}
// get values
args = {
- item_code: d.item_code,
- warehouse: cstr(d.s_warehouse)
+ 'item_code' : d.item_code,
+ 'warehouse' : cstr(d.s_warehouse),
+ 'transfer_qty' : d.transfer_qty,
+ 'serial_no' : d.serial_no
};
- get_server_fields('get_item_details',JSON.stringify(args),'mtn_details',doc,cdt,cdn,1,cal_back);
+ get_server_fields('get_item_details',JSON.stringify(args),'mtn_details',doc,cdt,cdn,1);
}
//==================================================================================================================
diff --git a/material_management/doctype/stock_entry/stock_entry.py b/material_management/doctype/stock_entry/stock_entry.py
index 55cd1f0..b671781 100644
--- a/material_management/doctype/stock_entry/stock_entry.py
+++ b/material_management/doctype/stock_entry/stock_entry.py
@@ -33,25 +33,25 @@
# get item details
# ----------------
def get_item_details(self, arg):
- arg, bin, in_rate = eval(arg), None, 0
- item = sql("select stock_uom, description, item_name from `tabItem` where name = %s and (ifnull(end_of_life,'')='' or end_of_life ='0000-00-00' or end_of_life > now())", (arg['item_code']), as_dict = 1)
- if not item:
- if arg['item_code']:
- msgprint("Item is not active. You can restore it from Trash")
- raise webnotes.ValidationError
+ arg, actual_qty, in_rate = eval(arg), 0, 0
+
+ item = sql("select stock_uom, description, item_name from `tabItem` where name = %s and (ifnull(end_of_life,'')='' or end_of_life ='0000-00-00' or end_of_life > now())", (arg.get('item_code')), as_dict = 1)
+ if not item:
+ msgprint("Item is not active", raise_exception=1)
- if arg['warehouse']:
- bin = sql("select actual_qty from `tabBin` where item_code = %s and warehouse = %s", (arg['item_code'], arg['warehouse']), as_dict = 1)
- in_rate = get_obj('Valuation Control').get_incoming_rate(self.doc.posting_date, self.doc.posting_time, arg['item_code'],arg['warehouse'])
+ if arg.get('warehouse'):
+ actual_qty = self.get_as_on_stock(arg.get('item_code'), arg.get('warehouse'), self.doc.posting_date, self.doc.posting_time)
+ in_rate = self.get_incoming_rate(arg.get('item_code'), arg.get('warehouse'), self.doc.posting_date, self.doc.posting_time, arg.get('transfer_qty'), arg.get('serial_no')) or 0
+
ret = {
'uom' : item and item[0]['stock_uom'] or '',
'stock_uom' : item and item[0]['stock_uom'] or '',
'description' : item and item[0]['description'] or '',
'item_name' : item and item[0]['item_name'] or '',
- 'actual_qty' : bin and flt(bin[0]['actual_qty']) or 0,
+ 'actual_qty' : actual_qty,
'qty' : 0,
'transfer_qty' : 0,
- 'incoming_rate' : flt(in_rate),
+ 'incoming_rate' : in_rate,
'conversion_factor' : 1,
'batch_no' : ''
}
@@ -73,32 +73,42 @@
return str(ret)
- # get rate of FG item
- #---------------------------
- def get_in_rate(self, pro_obj):
- # calculate_cost for production item
- get_obj('BOM Control').calculate_cost(pro_obj.doc.bom_no)
- # return cost
- return flt(get_obj('Bill Of Materials', pro_obj.doc.bom_no).doc.cost_as_per_mar)
-
- # get current_stock
- # ----------------
- def get_current_stock(self, pro_obj = ''):
+ # get stock and incoming rate on posting date
+ # ---------------------------------------------
+ def get_stock_and_rate(self, bom_no = ''):
for d in getlist(self.doclist, 'mtn_details'):
- d.s_warehouse = (self.doc.purpose != 'Production Order') and self.doc.from_warehouse or cstr(d.s_warehouse)
- d.t_warehouse = (self.doc.purpose != 'Production Order') and self.doc.to_warehouse or cstr(d.t_warehouse)
+ # assign parent warehouse
+ d.s_warehouse = cstr(d.s_warehouse) or self.doc.purpose != 'Production Order' and self.doc.from_warehouse or ''
+ d.t_warehouse = cstr(d.t_warehouse) or self.doc.purpose != 'Production Order' and self.doc.to_warehouse or ''
- if d.s_warehouse:
- bin = sql("select actual_qty from `tabBin` where item_code = %s and warehouse = %s", (d.item_code, d.s_warehouse), as_dict = 1)
- d.actual_qty = bin and flt(bin[0]['actual_qty']) or 0
- else:
- d.actual_qty = 0
- if d.fg_item:
- d.incoming_rate = pro_obj and self.get_in_rate(pro_obj) or ''
- elif self.doc.purpose not in ['Material Receipt', 'Sales Return'] and not d.incoming_rate and d.s_warehouse:
- d.incoming_rate = flt(get_obj('Valuation Control').get_incoming_rate(self.doc.posting_date, self.doc.posting_time, d.item_code, d.s_warehouse, d.transfer_qty, d.serial_no))
- d.save()
+ # get current stock at source warehouse
+ d.actual_qty = d.s_warehouse and self.get_as_on_stock(d.item_code, d.s_warehouse, self.doc.posting_date, self.doc.posting_time) or 0
+ # get incoming rate
+ if not flt(d.incoming_rate):
+ d.incoming_rate = self.get_incoming_rate(d.item_code, d.s_warehouse, self.doc.posting_date, self.doc.posting_time, d.transfer_qty, d.serial_no, d.fg_item, bom_no)
+
+ # Get stock qty on any date
+ # ---------------------------
+ def get_as_on_stock(self, item, wh, dt, tm):
+ bin = sql("select name from tabBin where item_code = %s and warehouse = %s", (item, wh))
+ bin_id = bin and bin[0][0] or ''
+ prev_sle = get_obj('Bin', bin_id).get_prev_sle(dt, tm)
+ qty = flt(prev_sle.get('bin_aqat', 0))
+ return qty
+
+ # Get incoming rate
+ # -------------------
+ def get_incoming_rate(self, item, wh, dt, tm, qty = 0, serial_no = '', fg_item = 'No', bom_no = ''):
+ in_rate = 0
+ if fg_item == 'Yes':
+ # re-calculate cost for production item from bom
+ get_obj('BOM Control').calculate_cost(bom_no)
+ in_rate = get_value('Bill Of Materials', bom_no, 'cost_as_per_mar')
+ elif wh:
+ in_rate = get_obj('Valuation Control').get_incoming_rate(dt, tm, item, wh, qty, serial_no)
+
+ return in_rate
# makes dict of unique items with it's qty
#-----------------------------------------
@@ -242,7 +252,7 @@
self.validate_for_production_order(pro_obj)
self.validate_incoming_rate()
self.validate_warehouse(pro_obj)
- self.get_current_stock(pro_obj)
+ self.get_current_stock(pro_obj.doc.bom_no)
self.calc_amount()
get_obj('Sales Common').validate_fiscal_year(self.doc.fiscal_year,self.doc.posting_date,'Posting Date')
diff --git a/material_management/doctype/stock_ledger/stock_ledger.py b/material_management/doctype/stock_ledger/stock_ledger.py
index a4498ca..a52dae7 100644
--- a/material_management/doctype/stock_ledger/stock_ledger.py
+++ b/material_management/doctype/stock_ledger/stock_ledger.py
@@ -217,7 +217,7 @@
if v["actual_qty"]:
sle_id = self.make_entry(v)
- get_obj('Warehouse', v["warehouse"]).update_bin(flt(v["actual_qty"]), 0, 0, 0, 0, v["item_code"], v["posting_date"], sle_id, v["posting_time"], '')
+ get_obj('Warehouse', v["warehouse"]).update_bin(flt(v["actual_qty"]), 0, 0, 0, 0, v["item_code"], v["posting_date"], sle_id, v["posting_time"], '', v["is_cancelled"])
# -----------
diff --git a/material_management/doctype/stock_reconciliation/stock_reconciliation.py b/material_management/doctype/stock_reconciliation/stock_reconciliation.py
index d7eb78b..51887ff 100644
--- a/material_management/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/material_management/doctype/stock_reconciliation/stock_reconciliation.py
@@ -118,7 +118,7 @@
# ------------------
def get_current_stock(self, item_code, warehouse):
bin = sql("select name from `tabBin` where item_code = '%s' and warehouse = '%s'" % (item_code, warehouse))
- prev_sle = bin and get_obj('Bin', bin[0][0]).get_prev_sle('', self.doc.reconciliation_date,self.doc.reconciliation_time) or 0
+ prev_sle = bin and get_obj('Bin', bin[0][0]).get_prev_sle(self.doc.reconciliation_date,self.doc.reconciliation_time) or 0
stock_uom = sql("select stock_uom from `tabItem` where name = %s",item_code)
return {'actual_qty': prev_sle.get('bin_aqat', 0), 'stock_uom': stock_uom[0][0]}
@@ -169,7 +169,7 @@
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)
+ 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)
diff --git a/material_management/doctype/valuation_control/valuation_control.py b/material_management/doctype/valuation_control/valuation_control.py
index d28de4e..c23753f 100644
--- a/material_management/doctype/valuation_control/valuation_control.py
+++ b/material_management/doctype/valuation_control/valuation_control.py
@@ -23,31 +23,23 @@
# Get FIFO Rate from Stack
# -------------------------
- def get_fifo_rate(self, fcfs_bal, qty):
- if qty:
- fcfs_val = 0
- withdraw = flt(qty)
- while withdraw:
- if not fcfs_bal:
- break # nothing in store
-
- batch = fcfs_bal[0]
-
- if batch[0] < withdraw:
- # not enough in current batch, clear batch
- withdraw -= batch[0]
- fcfs_val += (flt(batch[0]) * flt(batch[1]))
- fcfs_bal.pop(0)
- else:
- # all from current batch
- fcfs_val += (flt(withdraw) * flt(batch[1]))
- batch[0] -= withdraw
- withdraw = 0
- fcfs_rate = flt(fcfs_val) / flt(qty)
- return fcfs_rate
- else:
- return fcfs_bal and fcfs_bal[0][1] or 0
-
+ def get_fifo_rate(self, fcfs_stack, qty):
+ fcfs_val = 0
+ withdraw = flt(qty)
+ while withdraw:
+ batch = fcfs_stack[0]
+ if batch[0] <= withdraw:
+ # not enough or exactly same qty in current batch, clear batch
+ withdraw -= batch[0]
+ fcfs_val += (flt(batch[0]) * flt(batch[1]))
+ fcfs_stack.pop(0)
+ else:
+ # all from current batch
+ fcfs_val += (flt(withdraw) * flt(batch[1]))
+ batch[0] -= withdraw
+ withdraw = 0
+ fcfs_rate = flt(fcfs_val) / flt(qty)
+ return fcfs_rate
# --------------------------------
# get serializable inventory rate
@@ -76,15 +68,17 @@
def get_incoming_rate(self, posting_date, posting_time, item, warehouse, qty = 0, serial_no = ''):
in_rate = 0
val_method = self.get_valuation_method(item)
+ bin_obj = get_obj('Warehouse',warehouse).get_bin(item)
+
if serial_no:
in_rate = self.get_serializable_inventory_rate(serial_no)
elif val_method == 'FIFO':
- bin_obj = get_obj('Warehouse',warehouse).get_bin(item)
- prev_sle = bin_obj.get_prev_sle('',posting_date, posting_time)
- fcfs_stack = eval(prev_sle.get('fcfs_stack', '[]') or '[]')
- in_rate = fcfs_stack and self.get_fifo_rate(fcfs_stack, qty) or 0
+ in_rate = 0
+ if qty:
+ prev_sle = bin_obj.get_prev_sle(posting_date, posting_time)
+ fcfs_stack = eval(prev_sle.get('fcfs_stack', '[]') or '[]')
+ in_rate = fcfs_stack and self.get_fifo_rate(fcfs_stack, qty) or 0
elif val_method == 'Moving Average':
- bin_obj = get_obj('Warehouse',warehouse).get_bin(item)
- prev_sle = bin_obj.get_prev_sle('',posting_date, posting_time)
+ prev_sle = bin_obj.get_prev_sle(posting_date, posting_time)
in_rate = prev_sle and prev_sle.get('valuation_rate', 0) or 0
- return in_rate
+ return in_rate
diff --git a/material_management/doctype/warehouse/warehouse.py b/material_management/doctype/warehouse/warehouse.py
index 8b523dd..51c615c 100644
--- a/material_management/doctype/warehouse/warehouse.py
+++ b/material_management/doctype/warehouse/warehouse.py
@@ -52,12 +52,12 @@
# update bin
# ----------
- def update_bin(self, actual_qty, reserved_qty, ordered_qty, indented_qty, planned_qty, item_code, dt, sle_id = '',posting_time = '', serial_no = ''):
+ def update_bin(self, actual_qty, reserved_qty, ordered_qty, indented_qty, planned_qty, item_code, dt, sle_id = '',posting_time = '', serial_no = '', is_cancelled = 'No'):
self.validate_asset(item_code)
it_det = get_value('Item', item_code, 'is_stock_item')
if it_det and it_det == 'Yes':
bin = self.get_bin(item_code)
- bin.update_stock(actual_qty, reserved_qty, ordered_qty, indented_qty, planned_qty, dt, sle_id, posting_time, serial_no)
+ bin.update_stock(actual_qty, reserved_qty, ordered_qty, indented_qty, planned_qty, dt, sle_id, posting_time, serial_no, is_cancelled)
return bin
else:
msgprint("[Stock Update] Ignored %s since it is not a stock item" % item_code)