Stock Reconciliation logic simplified
diff --git a/erpnext/public/js/stock_analytics.js b/erpnext/public/js/stock_analytics.js
index d4f43e9..84c0386 100644
--- a/erpnext/public/js/stock_analytics.js
+++ b/erpnext/public/js/stock_analytics.js
@@ -138,9 +138,17 @@
item.valuation_method : sys_defaults.valuation_method;
var is_fifo = valuation_method == "FIFO";
- var diff = me.get_value_diff(wh, sl, is_fifo);
+ if(sl.voucher_type=="Stock Reconciliation") {
+ var diff = (sl.qty_after_transaction * sl.valuation_rate) - item.closing_qty_value;
+ } else {
+ var diff = me.get_value_diff(wh, sl, is_fifo);
+ }
} else {
- var diff = sl.qty;
+ if(sl.voucher_type=="Stock Reconciliation") {
+ var diff = sl.qty_after_transaction - item.closing_qty_value;
+ } else {
+ var diff = sl.qty;
+ }
}
if(posting_datetime < from_date) {
@@ -150,6 +158,8 @@
} else {
break;
}
+
+ item.closing_qty_value += diff;
}
}
},
diff --git a/erpnext/startup/report_data_map.py b/erpnext/startup/report_data_map.py
index ce71310..81d378c 100644
--- a/erpnext/startup/report_data_map.py
+++ b/erpnext/startup/report_data_map.py
@@ -78,7 +78,8 @@
"Stock Ledger Entry": {
"columns": ["name", "posting_date", "posting_time", "item_code", "warehouse",
"actual_qty as qty", "voucher_type", "voucher_no", "project",
- "ifnull(incoming_rate,0) as incoming_rate", "stock_uom", "serial_no"],
+ "ifnull(incoming_rate,0) as incoming_rate", "stock_uom", "serial_no",
+ "qty_after_transaction", "valuation_rate"],
"order_by": "posting_date, posting_time, name",
"links": {
"item_code": ["Item", "name"],
diff --git a/erpnext/stock/doctype/bin/bin.py b/erpnext/stock/doctype/bin/bin.py
index 3f74c5c..0244213 100644
--- a/erpnext/stock/doctype/bin/bin.py
+++ b/erpnext/stock/doctype/bin/bin.py
@@ -11,27 +11,27 @@
def validate(self):
if self.get("__islocal") or not self.stock_uom:
self.stock_uom = frappe.db.get_value('Item', self.item_code, 'stock_uom')
-
+
self.validate_mandatory()
-
+
self.projected_qty = flt(self.actual_qty) + flt(self.ordered_qty) + \
flt(self.indented_qty) + flt(self.planned_qty) - flt(self.reserved_qty)
-
+
def validate_mandatory(self):
qf = ['actual_qty', 'reserved_qty', 'ordered_qty', 'indented_qty']
for f in qf:
- if (not getattr(self, f, None)) or (not self.get(f)):
+ if (not getattr(self, f, None)) or (not self.get(f)):
self.set(f, 0.0)
-
+
def update_stock(self, args):
self.update_qty(args)
-
+
if args.get("actual_qty"):
from erpnext.stock.stock_ledger import update_entries_after
-
+
if not args.get("posting_date"):
args["posting_date"] = nowdate()
-
+
# update valuation and qty after transaction for post dated entry
update_entries_after({
"item_code": self.item_code,
@@ -39,21 +39,24 @@
"posting_date": args.get("posting_date"),
"posting_time": args.get("posting_time")
})
-
+
def update_qty(self, args):
# update the stock values (for current quantities)
-
- self.actual_qty = flt(self.actual_qty) + flt(args.get("actual_qty"))
+ if args.get("voucher_type")=="Stock Reconciliation":
+ self.actual_qty = args.get("qty_after_transaction")
+ else:
+ self.actual_qty = flt(self.actual_qty) + flt(args.get("actual_qty"))
+
self.ordered_qty = flt(self.ordered_qty) + flt(args.get("ordered_qty"))
self.reserved_qty = flt(self.reserved_qty) + flt(args.get("reserved_qty"))
self.indented_qty = flt(self.indented_qty) + flt(args.get("indented_qty"))
self.planned_qty = flt(self.planned_qty) + flt(args.get("planned_qty"))
-
+
self.projected_qty = flt(self.actual_qty) + flt(self.ordered_qty) + \
flt(self.indented_qty) + flt(self.planned_qty) - flt(self.reserved_qty)
-
+
self.save()
-
+
def get_first_sle(self):
sle = frappe.db.sql("""
select * from `tabStock Ledger Entry`
@@ -62,4 +65,4 @@
order by timestamp(posting_date, posting_time) asc, name asc
limit 1
""", (self.item_code, self.warehouse), as_dict=1)
- return sle and sle[0] or None
\ No newline at end of file
+ return sle and sle[0] or None
diff --git a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
index 0e92b5d..1bb189b 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
+++ b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
@@ -44,7 +44,7 @@
formatdate(self.posting_date), self.posting_time))
def validate_mandatory(self):
- mandatory = ['warehouse','posting_date','voucher_type','voucher_no','actual_qty','company']
+ mandatory = ['warehouse','posting_date','voucher_type','voucher_no','company']
for k in mandatory:
if not self.get(k):
frappe.throw(_("{0} is required").format(self.meta.get_label(k)))
diff --git a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
index 40a980d..f39829d 100644
--- a/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -129,7 +129,6 @@
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"]
@@ -141,105 +140,27 @@
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 not in ["", None] and not row.valuation_rate and \
- flt(previous_sle.get("qty_after_transaction")) <= 0:
+ 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))
- change_in_qty = row.qty not in ["", None] and \
- (flt(row.qty) - flt(previous_sle.get("qty_after_transaction")))
+ self.insert_entries(row)
- change_in_rate = row.valuation_rate not in ["", None] 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 sle_for_moving_avg(self, row, previous_sle, change_in_qty, change_in_rate):
- """Insert Stock Ledger Entries for Moving Average valuation"""
- def _get_incoming_rate(qty, valuation_rate, previous_qty, previous_valuation_rate):
- if previous_valuation_rate == 0:
- return flt(valuation_rate)
- else:
- if valuation_rate in ["", None]:
- valuation_rate = previous_valuation_rate
- return (qty * valuation_rate - previous_qty * previous_valuation_rate) \
- / flt(qty - previous_qty)
-
- if change_in_qty:
- # if change in qty, irrespective of change in rate
- incoming_rate = _get_incoming_rate(flt(row.qty), flt(row.valuation_rate),
- flt(previous_sle.get("qty_after_transaction")), flt(previous_sle.get("valuation_rate")))
-
- row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Actual Entry"
- self.insert_entries({"actual_qty": change_in_qty, "incoming_rate": incoming_rate}, row)
-
- elif change_in_rate and flt(previous_sle.get("qty_after_transaction")) > 0:
- # if no change in qty, but change in rate
- # and positive actual stock before this reconciliation
- incoming_rate = _get_incoming_rate(
- flt(previous_sle.get("qty_after_transaction"))+1, flt(row.valuation_rate),
- flt(previous_sle.get("qty_after_transaction")),
- flt(previous_sle.get("valuation_rate")))
-
- # +1 entry
- row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Valuation Adjustment +1"
- self.insert_entries({"actual_qty": 1, "incoming_rate": incoming_rate}, row)
-
- # -1 entry
- row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Valuation Adjustment -1"
- self.insert_entries({"actual_qty": -1}, row)
-
- def sle_for_fifo(self, row, previous_sle, change_in_qty, change_in_rate):
- """Insert Stock Ledger Entries for FIFO valuation"""
- previous_stock_queue = json.loads(previous_sle.get("stock_queue") or "[]")
- previous_stock_qty = sum((batch[0] for batch in previous_stock_queue))
- previous_stock_value = sum((batch[0] * batch[1] for batch in \
- previous_stock_queue))
-
- def _insert_entries():
- if previous_stock_queue != [[row.qty, row.valuation_rate]]:
- # make entry as per attachment
- if flt(row.qty):
- row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Actual Entry"
- self.insert_entries({"actual_qty": row.qty,
- "incoming_rate": flt(row.valuation_rate)}, row)
-
- # Make reverse entry
- if previous_stock_qty:
- row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Reverse Entry"
- self.insert_entries({"actual_qty": -1 * previous_stock_qty,
- "incoming_rate": previous_stock_qty < 0 and
- flt(row.valuation_rate) or 0}, row)
-
-
- if change_in_qty:
- if row.valuation_rate in ["", None]:
- # dont want change in valuation
- if previous_stock_qty > 0:
- # set valuation_rate as previous valuation_rate
- row.valuation_rate = previous_stock_value / flt(previous_stock_qty)
-
- _insert_entries()
-
- elif change_in_rate and previous_stock_qty > 0:
- # if no change in qty, but change in rate
- # and positive actual stock before this reconciliation
-
- row.qty = previous_stock_qty
- _insert_entries()
-
- def insert_entries(self, opts, row):
+ def insert_entries(self, row):
"""Insert Stock Ledger Entries"""
args = frappe._dict({
"doctype": "Stock Ledger Entry",
@@ -253,9 +174,10 @@
"stock_uom": frappe.db.get_value("Item", row.item_code, "stock_uom"),
"voucher_detail_no": row.voucher_detail_no,
"fiscal_year": self.fiscal_year,
- "is_cancelled": "No"
+ "is_cancelled": "No",
+ "qty_after_transaction": row.qty,
+ "valuation_rate": row.valuation_rate
})
- args.update(opts)
self.make_sl_entries([args])
# append to entries
@@ -295,7 +217,7 @@
if not self.expense_account:
msgprint(_("Please enter Expense Account"), raise_exception=1)
- elif not frappe.db.sql("""select * from `tabStock Ledger Entry`"""):
+ elif not frappe.db.sql("""select name from `tabStock Ledger Entry` limit 1"""):
if frappe.db.get_value("Account", self.expense_account, "report_type") == "Profit and Loss":
frappe.throw(_("Difference Account must be a 'Liability' type account, since this Stock Reconciliation is an Opening Entry"))
diff --git a/erpnext/stock/page/stock_balance/stock_balance.js b/erpnext/stock/page/stock_balance/stock_balance.js
index 1083414..c2ffc37 100644
--- a/erpnext/stock/page/stock_balance/stock_balance.js
+++ b/erpnext/stock/page/stock_balance/stock_balance.js
@@ -104,8 +104,13 @@
item.valuation_method : sys_defaults.valuation_method;
var is_fifo = valuation_method == "FIFO";
- var qty_diff = sl.qty;
- var value_diff = me.get_value_diff(wh, sl, is_fifo);
+ if(sl.voucher_type=="Stock Reconciliation") {
+ var qty_diff = sl.qty_after_trasaction - item.closing_qty;
+ var value_diff = (sl.valuation_rate * sl.qty_after_trasaction) - item.closing_value;
+ } else {
+ var qty_diff = sl.qty;
+ var value_diff = me.get_value_diff(wh, sl, is_fifo);
+ }
if(sl_posting_date < from_date) {
item.opening_qty += qty_diff;
diff --git a/erpnext/stock/stock_ledger.py b/erpnext/stock/stock_ledger.py
index 3717bf1..03bbf2f 100644
--- a/erpnext/stock/stock_ledger.py
+++ b/erpnext/stock/stock_ledger.py
@@ -95,14 +95,23 @@
qty_after_transaction += flt(sle.actual_qty)
continue
+
if sle.serial_no:
valuation_rate = get_serialized_values(qty_after_transaction, sle, valuation_rate)
- elif valuation_method == "Moving Average":
- valuation_rate = get_moving_average_values(qty_after_transaction, sle, valuation_rate)
- else:
- valuation_rate = get_fifo_values(qty_after_transaction, sle, stock_queue)
+ qty_after_transaction += flt(sle.actual_qty)
- qty_after_transaction += flt(sle.actual_qty)
+ else:
+ if sle.voucher_type=="Stock Reconciliation":
+ valuation_rate = sle.valuation_rate
+ qty_after_transaction = sle.qty_after_transaction
+ stock_queue = [[qty_after_transaction, valuation_rate]]
+ else:
+ if valuation_method == "Moving Average":
+ valuation_rate = get_moving_average_values(qty_after_transaction, sle, valuation_rate)
+ else:
+ valuation_rate = get_fifo_values(qty_after_transaction, sle, stock_queue)
+
+ qty_after_transaction += flt(sle.actual_qty)
# get stock value
if sle.serial_no: