set stock balance as per serial no count
Cherry Picked and Modified by @anandpdoshi
Conflicts:
stock/doctype/stock_ledger/stock_ledger.py
utilities/repost_stock.py
diff --git a/erpnext/patches/repair_tools/set_stock_balance_as_per_serial_no.py b/erpnext/patches/repair_tools/set_stock_balance_as_per_serial_no.py
new file mode 100644
index 0000000..e9a717d
--- /dev/null
+++ b/erpnext/patches/repair_tools/set_stock_balance_as_per_serial_no.py
@@ -0,0 +1,13 @@
+# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
+# License: GNU General Public License v3. See license.txt
+
+from __future__ import unicode_literals
+import frappe
+
+def execute():
+ from erpnext.utilities.repost_stock import set_stock_balance_as_per_serial_no
+ frappe.db.auto_commit_on_many_writes = 1
+
+ set_stock_balance_as_per_serial_no()
+
+ frappe.db.auto_commit_on_many_writes = 0
diff --git a/erpnext/stock/doctype/stock_ledger/stock_ledger.py b/erpnext/stock/doctype/stock_ledger/stock_ledger.py
index f566e05..b5a6bc8 100644
--- a/erpnext/stock/doctype/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/doctype/stock_ledger/stock_ledger.py
@@ -3,47 +3,7 @@
from __future__ import unicode_literals
import frappe
-
-from frappe.utils import flt, now
from frappe.model.document import Document
class StockLedger(Document):
-
- def update_stock(self, values, is_amended = 'No'):
- for v in values:
- sle_id = ''
-
- # reverse quantities for cancel
- if v.get('is_cancelled') == 'Yes':
- v['actual_qty'] = -flt(v['actual_qty'])
- # cancel matching entry
- frappe.db.sql("""update `tabStock Ledger Entry` set is_cancelled='Yes',
- modified=%s, modified_by=%s
- where voucher_no=%s and voucher_type=%s""",
- (now(), frappe.session.user, v['voucher_no'], v['voucher_type']))
-
- if v.get("actual_qty"):
- sle_id = self.make_entry(v)
-
- args = v.copy()
- args.update({
- "sle_id": sle_id,
- "is_amended": is_amended
- })
-
- frappe.get_doc('Warehouse', v["warehouse"]).update_bin(args)
-
-
- def make_entry(self, args):
- args.update({"doctype": "Stock Ledger Entry"})
- sle = frappe.get_doc(args)
- sle.ignore_permissions = 1
- sle.insert()
- return sle.name
-
- def repost(self):
- """
- Repost everything!
- """
- for wh in frappe.db.sql("select name from tabWarehouse"):
- frappe.get_doc('Warehouse', wh[0]).repost_stock()
+ pass
diff --git a/erpnext/utilities/repost_stock.py b/erpnext/utilities/repost_stock.py
index 507974b..e4028b6 100644
--- a/erpnext/utilities/repost_stock.py
+++ b/erpnext/utilities/repost_stock.py
@@ -4,32 +4,34 @@
from __future__ import unicode_literals
import frappe
-from frappe.utils import flt
-
+from frappe.utils import flt, cstr, nowdate, nowtime
+from erpnext.stock.utils import update_bin
+from erpnext.stock.stock_ledger import update_entries_after
+from erpnext.accounts.utils import get_fiscal_year
def repost(allow_negative_stock=False):
"""
Repost everything!
"""
frappe.db.auto_commit_on_many_writes = 1
-
+
if allow_negative_stock:
frappe.db.set_default("allow_negative_stock", 1)
-
- for d in frappe.db.sql("""select distinct item_code, warehouse from
+
+ for d in frappe.db.sql("""select distinct item_code, warehouse from
(select item_code, warehouse from tabBin
union
select item_code, warehouse from `tabStock Ledger Entry`) a"""):
repost_stock(d[0], d[1], allow_negative_stock)
-
+
if allow_negative_stock:
- frappe.db.set_default("allow_negative_stock",
+ frappe.db.set_default("allow_negative_stock",
frappe.db.get_value("Stock Settings", None, "allow_negative_stock"))
frappe.db.auto_commit_on_many_writes = 0
def repost_stock(item_code, warehouse):
repost_actual_qty(item_code, warehouse)
-
+
if item_code and warehouse:
update_bin(item_code, warehouse, {
"reserved_qty": get_reserved_qty(item_code, warehouse),
@@ -39,17 +41,16 @@
})
def repost_actual_qty(item_code, warehouse):
- from erpnext.stock.stock_ledger import update_entries_after
try:
update_entries_after({ "item_code": item_code, "warehouse": warehouse })
except:
pass
-
+
def get_reserved_qty(item_code, warehouse):
reserved_qty = frappe.db.sql("""
- select
+ select
sum((dnpi_qty / so_item_qty) * (so_item_qty - so_item_delivered_qty))
- from
+ from
(
(select
qty as dnpi_qty,
@@ -60,9 +61,9 @@
(
select ifnull(delivered_qty, 0) from `tabSales Order Item`
where name = dnpi.parent_detail_docname
- ) as so_item_delivered_qty,
+ ) as so_item_delivered_qty,
parent, name
- from
+ from
(
select qty, parent_detail_docname, parent, name
from `tabPacked Item` dnpi_in
@@ -76,45 +77,45 @@
(select qty as dnpi_qty, qty as so_item_qty,
ifnull(delivered_qty, 0) as so_item_delivered_qty, parent, name
from `tabSales Order Item` so_item
- where item_code = %s and warehouse = %s
+ where item_code = %s and reserved_warehouse = %s
and exists(select * from `tabSales Order` so
- where so.name = so_item.parent and so.docstatus = 1
+ where so.name = so_item.parent and so.docstatus = 1
and so.status != 'Stopped'))
) tab
- where
+ where
so_item_qty >= so_item_delivered_qty
""", (item_code, warehouse, item_code, warehouse))
return flt(reserved_qty[0][0]) if reserved_qty else 0
-
+
def get_indented_qty(item_code, warehouse):
indented_qty = frappe.db.sql("""select sum(pr_item.qty - ifnull(pr_item.ordered_qty, 0))
from `tabMaterial Request Item` pr_item, `tabMaterial Request` pr
- where pr_item.item_code=%s and pr_item.warehouse=%s
- and pr_item.qty > ifnull(pr_item.ordered_qty, 0) and pr_item.parent=pr.name
+ where pr_item.item_code=%s and pr_item.warehouse=%s
+ and pr_item.qty > ifnull(pr_item.ordered_qty, 0) and pr_item.parent=pr.name
and pr.status!='Stopped' and pr.docstatus=1""", (item_code, warehouse))
-
+
return flt(indented_qty[0][0]) if indented_qty else 0
def get_ordered_qty(item_code, warehouse):
ordered_qty = frappe.db.sql("""
select sum((po_item.qty - ifnull(po_item.received_qty, 0))*po_item.conversion_factor)
from `tabPurchase Order Item` po_item, `tabPurchase Order` po
- where po_item.item_code=%s and po_item.warehouse=%s
- and po_item.qty > ifnull(po_item.received_qty, 0) and po_item.parent=po.name
+ where po_item.item_code=%s and po_item.warehouse=%s
+ and po_item.qty > ifnull(po_item.received_qty, 0) and po_item.parent=po.name
and po.status!='Stopped' and po.docstatus=1""", (item_code, warehouse))
-
+
return flt(ordered_qty[0][0]) if ordered_qty else 0
-
+
def get_planned_qty(item_code, warehouse):
planned_qty = frappe.db.sql("""
- select sum(ifnull(qty, 0) - ifnull(produced_qty, 0)) from `tabProduction Order`
+ select sum(ifnull(qty, 0) - ifnull(produced_qty, 0)) from `tabProduction Order`
where production_item = %s and fg_warehouse = %s and status != "Stopped"
and docstatus=1 and ifnull(qty, 0) > ifnull(produced_qty, 0)""", (item_code, warehouse))
return flt(planned_qty[0][0]) if planned_qty else 0
-
-
+
+
def update_bin(item_code, warehouse, qty_dict=None):
from erpnext.stock.utils import get_bin
bin = get_bin(item_code, warehouse)
@@ -123,9 +124,69 @@
if flt(bin.get(fld)) != flt(val):
bin.set(fld, flt(val))
mismatch = True
-
+
if mismatch:
bin.projected_qty = flt(bin.actual_qty) + flt(bin.ordered_qty) + \
flt(bin.indented_qty) + flt(bin.planned_qty) - flt(bin.reserved_qty)
-
- bin.save()
\ No newline at end of file
+
+ bin.save()
+
+def set_stock_balance_as_per_serial_no(item_code=None, posting_date=None, posting_time=None,
+ fiscal_year=None):
+ if not posting_date: posting_date = nowdate()
+ if not posting_time: posting_time = nowtime()
+ if not fiscal_year: fiscal_year = get_fiscal_year(posting_date)[0]
+
+ condition = " and item.name='%s'" % item_code.replace("'", "\'") if item_code else ""
+
+ bin = frappe.db.sql("""select bin.item_code, bin.warehouse, bin.actual_qty, item.stock_uom
+ from `tabBin` bin, tabItem item
+ where bin.item_code = item.name and item.has_serial_no = 'Yes' %s""" % condition)
+
+ for d in bin:
+ serial_nos = frappe.db.sql("""select count(name) from `tabSerial No`
+ where item_code=%s and warehouse=%s and status = 'Available' and docstatus < 2""", (d[0], d[1]))
+
+ if serial_nos and flt(serial_nos[0][0]) != flt(d[2]):
+ print d[0], d[1], d[2], serial_nos[0][0]
+
+ sle = frappe.db.sql("""select valuation_rate, company from `tabStock Ledger Entry`
+ where item_code = %s and warehouse = %s and ifnull(is_cancelled, 'No') = 'No'
+ order by posting_date desc limit 1""", (d[0], d[1]))
+
+ sle_dict = {
+ 'doctype' : 'Stock Ledger Entry',
+ 'item_code' : d[0],
+ 'warehouse' : d[1],
+ 'transaction_date' : nowdate(),
+ 'posting_date' : posting_date,
+ 'posting_time' : posting_time,
+ 'voucher_type' : 'Stock Reconciliation (Manual)',
+ 'voucher_no' : '',
+ 'voucher_detail_no' : '',
+ 'actual_qty' : flt(serial_nos[0][0]) - flt(d[2]),
+ 'stock_uom' : d[3],
+ 'incoming_rate' : sle and flt(serial_nos[0][0]) > flt(d[2]) and flt(sle[0][0]) or 0,
+ 'company' : sle and cstr(sle[0][1]) or 0,
+ 'fiscal_year' : fiscal_year,
+ 'is_cancelled' : 'No',
+ 'batch_no' : '',
+ 'serial_no' : ''
+ }
+
+ sle_doc = frappe.get_doc(sle_dict)
+ sle_doc.insert()
+
+ args = sle_dict.copy()
+ args.update({
+ "sle_id": sle_doc.name,
+ "is_amended": 'No'
+ })
+
+ update_bin(args)
+ update_entries_after({
+ "item_code": d[0],
+ "warehouse": d[1],
+ "posting_date": posting_date,
+ "posting_time": posting_time
+ })