fix: optimize reposting of sle and gle (#24694)
* fix: optimize update_gl_entries_after method
* fix: Optimized reposting patch
* fix: accounting dimensions
* added reload_doc in patch
* Update item_reposting_for_incorrect_sl_and_gl.py
Co-authored-by: Rohit Waghchaure <rohitw1991@gmail.com>
diff --git a/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py b/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
index 774c58a..1bd42f5 100644
--- a/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
+++ b/erpnext/accounts/doctype/accounting_dimension/accounting_dimension.py
@@ -58,6 +58,9 @@
if not self.fieldname:
self.fieldname = scrub(self.label)
+ def on_update(self):
+ frappe.flags.accounting_dimensions = None
+
def make_dimension_in_accounting_doctypes(doc):
doclist = get_doctypes_with_dimensions()
doc_count = len(get_accounting_dimensions())
@@ -186,12 +189,14 @@
return doclist
def get_accounting_dimensions(as_list=True):
- accounting_dimensions = frappe.get_all("Accounting Dimension", fields=["label", "fieldname", "disabled", "document_type"])
+ if frappe.flags.accounting_dimensions is None:
+ frappe.flags.accounting_dimensions = frappe.get_all("Accounting Dimension",
+ fields=["label", "fieldname", "disabled", "document_type"])
if as_list:
- return [d.fieldname for d in accounting_dimensions]
+ return [d.fieldname for d in frappe.flags.accounting_dimensions]
else:
- return accounting_dimensions
+ return frappe.flags.accounting_dimensions
def get_checks_for_pl_and_bs_accounts():
dimensions = frappe.db.sql("""SELECT p.label, p.disabled, p.fieldname, c.default_dimension, c.company, c.mandatory_for_pl, c.mandatory_for_bs
diff --git a/erpnext/accounts/doctype/gl_entry/gl_entry.py b/erpnext/accounts/doctype/gl_entry/gl_entry.py
index b0a864f..ce76d0a 100644
--- a/erpnext/accounts/doctype/gl_entry/gl_entry.py
+++ b/erpnext/accounts/doctype/gl_entry/gl_entry.py
@@ -27,30 +27,30 @@
def validate(self):
self.flags.ignore_submit_comment = True
- self.check_mandatory()
self.validate_and_set_fiscal_year()
self.pl_must_have_cost_center()
- self.validate_cost_center()
if not self.flags.from_repost:
+ self.check_mandatory()
+ self.validate_cost_center()
self.check_pl_account()
self.validate_party()
self.validate_currency()
- def on_update_with_args(self, adv_adj, update_outstanding = 'Yes', from_repost=False):
- if not from_repost:
+ def on_update(self):
+ adv_adj = self.flags.adv_adj
+ if not self.flags.from_repost:
self.validate_account_details(adv_adj)
self.validate_dimensions_for_pl_and_bs()
self.validate_allowed_dimensions()
+ validate_balance_type(self.account, adv_adj)
+ validate_frozen_account(self.account, adv_adj)
- validate_frozen_account(self.account, adv_adj)
- validate_balance_type(self.account, adv_adj)
-
- # Update outstanding amt on against voucher
- if self.against_voucher_type in ['Journal Entry', 'Sales Invoice', 'Purchase Invoice', 'Fees'] \
- and self.against_voucher and update_outstanding == 'Yes' and not from_repost:
- update_outstanding_amt(self.account, self.party_type, self.party, self.against_voucher_type,
- self.against_voucher)
+ # Update outstanding amt on against voucher
+ if (self.against_voucher_type in ['Journal Entry', 'Sales Invoice', 'Purchase Invoice', 'Fees']
+ and self.against_voucher and self.flags.update_outstanding == 'Yes'):
+ update_outstanding_amt(self.account, self.party_type, self.party, self.against_voucher_type,
+ self.against_voucher)
def check_mandatory(self):
mandatory = ['account','voucher_type','voucher_no','company']
@@ -58,7 +58,7 @@
if not self.get(k):
frappe.throw(_("{0} is required").format(_(self.meta.get_label(k))))
- account_type = frappe.db.get_value("Account", self.account, "account_type")
+ account_type = frappe.get_cached_value("Account", self.account, "account_type")
if not (self.party_type and self.party):
if account_type == "Receivable":
frappe.throw(_("{0} {1}: Customer is required against Receivable account {2}")
@@ -73,7 +73,7 @@
.format(self.voucher_type, self.voucher_no, self.account))
def pl_must_have_cost_center(self):
- if frappe.db.get_value("Account", self.account, "report_type") == "Profit and Loss":
+ if frappe.get_cached_value("Account", self.account, "report_type") == "Profit and Loss":
if not self.cost_center and self.voucher_type != 'Period Closing Voucher':
frappe.throw(_("{0} {1}: Cost Center is required for 'Profit and Loss' account {2}. Please set up a default Cost Center for the Company.")
.format(self.voucher_type, self.voucher_no, self.account))
@@ -140,25 +140,16 @@
.format(self.voucher_type, self.voucher_no, self.account, self.company))
def validate_cost_center(self):
- if not hasattr(self, "cost_center_company"):
- self.cost_center_company = {}
+ if not self.cost_center: return
- def _get_cost_center_company():
- if not self.cost_center_company.get(self.cost_center):
- self.cost_center_company[self.cost_center] = frappe.db.get_value(
- "Cost Center", self.cost_center, "company")
+ is_group, company = frappe.get_cached_value('Cost Center',
+ self.cost_center, ['is_group', 'company'])
- return self.cost_center_company[self.cost_center]
-
- def _check_is_group():
- return cint(frappe.get_cached_value('Cost Center', self.cost_center, 'is_group'))
-
- if self.cost_center and _get_cost_center_company() != self.company:
+ if company != self.company:
frappe.throw(_("{0} {1}: Cost Center {2} does not belong to Company {3}")
.format(self.voucher_type, self.voucher_no, self.cost_center, self.company))
- if not self.flags.from_repost and not self.voucher_type == 'Period Closing Voucher' \
- and self.cost_center and _check_is_group():
+ if (self.voucher_type != 'Period Closing Voucher' and is_group):
frappe.throw(_("""{0} {1}: Cost Center {2} is a group cost center and group cost centers cannot be used in transactions""").format(
self.voucher_type, self.voucher_no, frappe.bold(self.cost_center)))
@@ -184,7 +175,6 @@
if not self.fiscal_year:
self.fiscal_year = get_fiscal_year(self.posting_date, company=self.company)[0]
-
def validate_balance_type(account, adv_adj=False):
if not adv_adj and account:
balance_must_be = frappe.db.get_value("Account", account, "balance_must_be")
@@ -250,7 +240,7 @@
def validate_frozen_account(account, adv_adj=None):
- frozen_account = frappe.db.get_value("Account", account, "freeze_account")
+ frozen_account = frappe.get_cached_value("Account", account, "freeze_account")
if frozen_account == 'Yes' and not adv_adj:
frozen_accounts_modifier = frappe.db.get_value( 'Accounts Settings', None,
'frozen_accounts_modifier')
diff --git a/erpnext/accounts/general_ledger.py b/erpnext/accounts/general_ledger.py
index 287c79f..b42c0c6 100644
--- a/erpnext/accounts/general_ledger.py
+++ b/erpnext/accounts/general_ledger.py
@@ -44,9 +44,9 @@
frappe.throw(_("You cannot create or cancel any accounting entries with in the closed Accounting Period {0}")
.format(frappe.bold(accounting_periods[0].name)), ClosedAccountingPeriod)
-def process_gl_map(gl_map, merge_entries=True):
+def process_gl_map(gl_map, merge_entries=True, precision=None):
if merge_entries:
- gl_map = merge_similar_entries(gl_map)
+ gl_map = merge_similar_entries(gl_map, precision)
for entry in gl_map:
# toggle debit, credit if negative entry
if flt(entry.debit) < 0:
@@ -69,7 +69,7 @@
return gl_map
-def merge_similar_entries(gl_map):
+def merge_similar_entries(gl_map, precision=None):
merged_gl_map = []
accounting_dimensions = get_accounting_dimensions()
for entry in gl_map:
@@ -88,7 +88,9 @@
company = gl_map[0].company if gl_map else erpnext.get_default_company()
company_currency = erpnext.get_company_currency(company)
- precision = get_field_precision(frappe.get_meta("GL Entry").get_field("debit"), company_currency)
+
+ if not precision:
+ precision = get_field_precision(frappe.get_meta("GL Entry").get_field("debit"), company_currency)
# filter zero debit and credit entries
merged_gl_map = filter(lambda x: flt(x.debit, precision)!=0 or flt(x.credit, precision)!=0, merged_gl_map)
@@ -132,8 +134,8 @@
gle.update(args)
gle.flags.ignore_permissions = 1
gle.flags.from_repost = from_repost
- gle.insert()
- gle.run_method("on_update_with_args", adv_adj, update_outstanding, from_repost)
+ gle.flags.adv_adj = adv_adj
+ gle.flags.update_outstanding = update_outstanding or 'Yes'
gle.submit()
if not from_repost:
diff --git a/erpnext/accounts/utils.py b/erpnext/accounts/utils.py
index 60d1e20..5eb2aab 100644
--- a/erpnext/accounts/utils.py
+++ b/erpnext/accounts/utils.py
@@ -902,10 +902,9 @@
warehouse_account = get_warehouse_account_map(company)
gle = get_voucherwise_gl_entries(stock_vouchers, posting_date)
-
for voucher_type, voucher_no in stock_vouchers:
existing_gle = gle.get((voucher_type, voucher_no), [])
- voucher_obj = frappe.get_doc(voucher_type, voucher_no)
+ voucher_obj = frappe.get_cached_doc(voucher_type, voucher_no)
expected_gle = voucher_obj.get_gl_entries(warehouse_account)
if expected_gle:
if not existing_gle or not compare_existing_and_expected_gle(existing_gle, expected_gle):
diff --git a/erpnext/controllers/stock_controller.py b/erpnext/controllers/stock_controller.py
index ea9659c..e0031c9 100644
--- a/erpnext/controllers/stock_controller.py
+++ b/erpnext/controllers/stock_controller.py
@@ -74,7 +74,7 @@
gl_list = []
warehouse_with_no_account = []
- precision = frappe.get_precision("GL Entry", "debit_in_account_currency")
+ precision = self.get_debit_field_precision()
for item_row in voucher_details:
sle_list = sle_map.get(item_row.name)
@@ -131,7 +131,13 @@
if frappe.db.get_value("Warehouse", wh, "company"):
frappe.throw(_("Warehouse {0} is not linked to any account, please mention the account in the warehouse record or set default inventory account in company {1}.").format(wh, self.company))
- return process_gl_map(gl_list)
+ return process_gl_map(gl_list, precision=precision)
+
+ def get_debit_field_precision(self):
+ if not frappe.flags.debit_field_precision:
+ frappe.flags.debit_field_precision = frappe.get_precision("GL Entry", "debit_in_account_currency")
+
+ return frappe.flags.debit_field_precision
def update_stock_ledger_entries(self, sle):
sle.valuation_rate = get_valuation_rate(sle.item_code, sle.warehouse,
@@ -244,7 +250,7 @@
.format(item.idx, frappe.bold(item.item_code), msg), title=_("Expense Account Missing"))
else:
- is_expense_account = frappe.db.get_value("Account",
+ is_expense_account = frappe.get_cached_value("Account",
item.get("expense_account"), "report_type")=="Profit and Loss"
if self.doctype not in ("Purchase Receipt", "Purchase Invoice", "Stock Reconciliation", "Stock Entry") and not is_expense_account:
frappe.throw(_("Expense / Difference account ({0}) must be a 'Profit or Loss' account")
@@ -493,7 +499,7 @@
elif not is_reposting_pending():
check_if_stock_and_account_balance_synced(self.posting_date,
self.company, self.doctype, self.name)
-
+
def is_reposting_pending():
return frappe.db.exists("Repost Item Valuation",
{'docstatus': 1, 'status': ['in', ['Queued','In Progress']]})
diff --git a/erpnext/patches/v13_0/item_reposting_for_incorrect_sl_and_gl.py b/erpnext/patches/v13_0/item_reposting_for_incorrect_sl_and_gl.py
index f60e0d3..06f7f98 100644
--- a/erpnext/patches/v13_0/item_reposting_for_incorrect_sl_and_gl.py
+++ b/erpnext/patches/v13_0/item_reposting_for_incorrect_sl_and_gl.py
@@ -4,11 +4,26 @@
from erpnext.accounts.utils import update_gl_entries_after
def execute():
- data = frappe.db.sql(''' SELECT name, item_code, warehouse, voucher_type, voucher_no, posting_date, posting_time
- from `tabStock Ledger Entry` where creation > '2020-12-26 12:58:55.903836' and is_cancelled = 0
- order by timestamp(posting_date, posting_time) asc, creation asc''', as_dict=1)
+ frappe.reload_doc('stock', 'doctype', 'repost_item_valuation')
- for index, d in enumerate(data):
+ reposting_project_deployed_on = frappe.db.get_value("DocType", "Repost Item Valuation", "creation")
+
+ data = frappe.db.sql('''
+ SELECT
+ name, item_code, warehouse, voucher_type, voucher_no, posting_date, posting_time
+ FROM
+ `tabStock Ledger Entry`
+ WHERE
+ creation > %s
+ and is_cancelled = 0
+ ORDER BY timestamp(posting_date, posting_time) asc, creation asc
+ ''', reposting_project_deployed_on, as_dict=1)
+
+ frappe.db.auto_commit_on_many_writes = 1
+ print("Reposting Stock Ledger Entries...")
+ total_sle = len(data)
+ i = 0
+ for d in data:
update_entries_after({
"item_code": d.item_code,
"warehouse": d.warehouse,
@@ -19,9 +34,13 @@
"sle_id": d.name
}, allow_negative_stock=True)
- frappe.db.auto_commit_on_many_writes = 1
+ i += 1
+ if i%100 == 0:
+ print(i, "/", total_sle)
+
+ print("Reposting General Ledger Entries...")
for row in frappe.get_all('Company', filters= {'enable_perpetual_inventory': 1}):
update_gl_entries_after('2020-12-25', '01:58:55', company=row.name)
- frappe.db.auto_commit_on_many_writes = 0
\ No newline at end of file
+ frappe.db.auto_commit_on_many_writes = 0
diff --git a/erpnext/stock/stock_ledger.py b/erpnext/stock/stock_ledger.py
index e4f5725..f54b3c1 100644
--- a/erpnext/stock/stock_ledger.py
+++ b/erpnext/stock/stock_ledger.py
@@ -58,8 +58,9 @@
if repost_entry.status == 'In Progress':
frappe.throw(_("Cannot cancel the transaction. Reposting of item valuation on submission is not completed yet."))
if repost_entry.status == 'Queued':
- frappe.delete_doc("Repost Item Valuation", repost_entry.name)
-
+ doc = frappe.get_doc("Repost Item Valuation", repost_entry.name)
+ doc.cancel()
+ doc.delete()
def set_as_cancel(voucher_type, voucher_no):
frappe.db.sql("""update `tabStock Ledger Entry` set is_cancelled=1,
@@ -133,7 +134,7 @@
self.item_code = args.get("item_code")
if self.args.sle_id:
self.args['name'] = self.args.sle_id
-
+
self.company = frappe.get_cached_value("Warehouse", self.args.warehouse, "company")
self.get_precision()
self.valuation_method = get_valuation_method(self.item_code)
@@ -201,7 +202,7 @@
and timestamp(posting_date, time_format(posting_time, %(time_format)s)) < timestamp(%(posting_date)s, time_format(%(posting_time)s, %(time_format)s))
order by timestamp(posting_date, posting_time) desc, creation desc
limit 1""", args, as_dict=1)
-
+
return sle[0] if sle else frappe._dict()
@@ -224,7 +225,7 @@
if sle.dependant_sle_voucher_detail_no:
entries_to_fix = self.get_dependent_entries_to_fix(entries_to_fix, sle)
-
+
self.update_bin()
if self.exceptions:
@@ -439,7 +440,7 @@
# Recalculate subcontracted item's rate in case of subcontracted purchase receipt/invoice
if frappe.db.get_value(sle.voucher_type, sle.voucher_no, "is_subcontracted"):
- doc = frappe.get_cached_doc(sle.voucher_type, sle.voucher_no)
+ doc = frappe.get_doc(sle.voucher_type, sle.voucher_no)
doc.update_valuation_rate(reset_outgoing_rate=False)
for d in (doc.items + doc.supplied_items):
d.db_update()