Merge pull request #2304 from nabinhait/stock_reco
Repost gl entries where mismatch with stock balance
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index bd2d436..0dbe444 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -83,3 +83,5 @@
erpnext.patches.v4_2.cost_of_production_cycle
erpnext.patches.v4_2.seprate_manufacture_and_repack
execute:frappe.delete_doc("Report", "Warehouse-Wise Stock Balance")
+execute:frappe.delete_doc("DocType", "Purchase Request")
+execute:frappe.delete_doc("DocType", "Purchase Request Item")
diff --git a/erpnext/patches/v4_2/fix_gl_entries_for_stock_transactions.py b/erpnext/patches/v4_2/fix_gl_entries_for_stock_transactions.py
index e065d2d..3c554ae 100644
--- a/erpnext/patches/v4_2/fix_gl_entries_for_stock_transactions.py
+++ b/erpnext/patches/v4_2/fix_gl_entries_for_stock_transactions.py
@@ -3,24 +3,49 @@
from __future__ import unicode_literals
import frappe
+from frappe.utils import flt
def execute():
- warehouses_with_account = frappe.db.sql_list("""select master_name from tabAccount
- where ifnull(account_type, '') = 'Warehouse'""")
+ from erpnext.utilities.repost_stock import repost
+ repost()
- stock_vouchers_without_gle = frappe.db.sql("""select distinct sle.voucher_type, sle.voucher_no
+ warehouse_account = frappe.db.sql("""select name, master_name from tabAccount
+ where ifnull(account_type, '') = 'Warehouse'""")
+ warehouses = [d[1] for d in warehouse_account]
+ accounts = [d[0] for d in warehouse_account]
+
+ stock_vouchers = frappe.db.sql("""select distinct sle.voucher_type, sle.voucher_no
from `tabStock Ledger Entry` sle
where sle.warehouse in (%s)
- and not exists(select name from `tabGL Entry`
- where voucher_type=sle.voucher_type and voucher_no=sle.voucher_no)
order by sle.posting_date""" %
- ', '.join(['%s']*len(warehouses_with_account)), tuple(warehouses_with_account))
+ ', '.join(['%s']*len(warehouses)), tuple(warehouses))
- for voucher_type, voucher_no in stock_vouchers_without_gle:
- print voucher_type, voucher_no
- frappe.db.sql("""delete from `tabGL Entry`
- where voucher_type=%s and voucher_no=%s""", (voucher_type, voucher_no))
+ rejected = []
+ for voucher_type, voucher_no in stock_vouchers:
+ stock_bal = frappe.db.sql("""select sum(stock_value_difference) from `tabStock Ledger Entry`
+ where voucher_type=%s and voucher_no =%s and warehouse in (%s)""" %
+ ('%s', '%s', ', '.join(['%s']*len(warehouses))), tuple([voucher_type, voucher_no] + warehouses))
- voucher = frappe.get_doc(voucher_type, voucher_no)
- voucher.make_gl_entries()
- frappe.db.commit()
+ account_bal = frappe.db.sql("""select ifnull(sum(ifnull(debit, 0) - ifnull(credit, 0)), 0)
+ from `tabGL Entry`
+ where voucher_type=%s and voucher_no =%s and account in (%s)
+ group by voucher_type, voucher_no""" %
+ ('%s', '%s', ', '.join(['%s']*len(accounts))), tuple([voucher_type, voucher_no] + accounts))
+
+ if stock_bal and account_bal and abs(flt(stock_bal[0][0]) - flt(account_bal[0][0])) > 0.1:
+ try:
+ print voucher_type, voucher_no, stock_bal[0][0], account_bal[0][0]
+
+ frappe.db.sql("""delete from `tabGL Entry`
+ where voucher_type=%s and voucher_no=%s""", (voucher_type, voucher_no))
+
+ voucher = frappe.get_doc(voucher_type, voucher_no)
+ voucher.make_gl_entries(repost_future_gle=False, allow_negative_stock=True)
+ frappe.db.commit()
+ except Exception, e:
+ print frappe.get_traceback()
+ rejected.append([voucher_type, voucher_no])
+ frappe.db.rollback()
+
+ print "Failed to repost: "
+ print rejected
diff --git a/erpnext/stock/report/stock_balance/stock_balance.py b/erpnext/stock/report/stock_balance/stock_balance.py
index 95de739..daef2eb 100644
--- a/erpnext/stock/report/stock_balance/stock_balance.py
+++ b/erpnext/stock/report/stock_balance/stock_balance.py
@@ -58,8 +58,8 @@
#get all details
def get_stock_ledger_entries(filters):
conditions = get_conditions(filters)
- return frappe.db.sql("""select item_code, warehouse, posting_date,
- actual_qty, valuation_rate, stock_uom, company, voucher_type, qty_after_transaction
+ return frappe.db.sql("""select item_code, warehouse, posting_date, actual_qty, valuation_rate,
+ stock_uom, company, voucher_type, qty_after_transaction, stock_value_difference
from `tabStock Ledger Entry`
where docstatus < 2 %s order by posting_date, posting_time, name""" %
conditions, as_dict=1)
@@ -82,10 +82,10 @@
if d.voucher_type == "Stock Reconciliation":
qty_diff = flt(d.qty_after_transaction) - qty_dict.bal_qty
- value_diff = flt(d.stock_value) - qty_dict.bal_val
else:
qty_diff = flt(d.actual_qty)
- value_diff = flt(d.actual_qty) * flt(d.valuation_rate)
+
+ value_diff = flt(d.stock_value_difference)
if d.posting_date < filters["from_date"]:
qty_dict.opening_qty += qty_diff
diff --git a/erpnext/utilities/repost_stock.py b/erpnext/utilities/repost_stock.py
index 7d9423d..89494ad 100644
--- a/erpnext/utilities/repost_stock.py
+++ b/erpnext/utilities/repost_stock.py
@@ -22,7 +22,11 @@
(select item_code, warehouse from tabBin
union
select item_code, warehouse from `tabStock Ledger Entry`) a"""):
- repost_stock(d[0], d[1])
+ try:
+ repost_stock(d[0], d[1])
+ frappe.db.commit()
+ except:
+ frappe.db.rollback()
if allow_negative_stock:
frappe.db.set_default("allow_negative_stock",
@@ -210,9 +214,14 @@
frappe.db.sql("""update `tabSerial No` set warehouse='' where status in ('Delivered', 'Purchase Returned')""")
def repost_all_stock_vouchers():
+ warehouses_with_account = frappe.db.sql_list("""select master_name from tabAccount
+ where ifnull(account_type, '') = 'Warehouse'""")
+
vouchers = frappe.db.sql("""select distinct voucher_type, voucher_no
- from `tabStock Ledger Entry`
- order by posting_date, posting_time, name""")
+ from `tabStock Ledger Entry` sle
+ where voucher_type != "Serial No" and sle.warehouse in (%s)
+ order by posting_date, posting_time, name""" %
+ ', '.join(['%s']*len(warehouses_with_account)), tuple(warehouses_with_account))
rejected = []
i = 0