refactor: Exchange rate revaluation to handle accounts with zero account balance (#33165)
* refactor: new type for JE - Exchange Gain or Loss
* refactor: skip few validations for Exchanage Gain Or Loss type Jour
* refactor: ERR create 2 journals for handling zero and non-zero compa
1. Additional check box accounts table to identify accounts with zero balance
2. Accounts with zero balance only in either of the 2 currencies will be handled on separate Journal
* refactor: skips few validation for allowing 0 debit/credit
* fix: General Ledger presentaion currency
* test: fix test case in general ledger
* test: fix failing test case in AR report
diff --git a/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.js b/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.js
index 926a442..f72ecc9 100644
--- a/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.js
+++ b/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.js
@@ -26,7 +26,7 @@
doc: frm.doc,
callback: function(r) {
if (r.message) {
- frm.add_custom_button(__('Journal Entry'), function() {
+ frm.add_custom_button(__('Journal Entries'), function() {
return frm.events.make_jv(frm);
}, __('Create'));
}
@@ -35,10 +35,11 @@
}
},
- get_entries: function(frm) {
+ get_entries: function(frm, account) {
frappe.call({
method: "get_accounts_data",
doc: cur_frm.doc,
+ account: account,
callback: function(r){
frappe.model.clear_table(frm.doc, "accounts");
if(r.message) {
@@ -57,7 +58,6 @@
let total_gain_loss = 0;
frm.doc.accounts.forEach((d) => {
- d.gain_loss = flt(d.new_balance_in_base_currency, precision("new_balance_in_base_currency", d)) - flt(d.balance_in_base_currency, precision("balance_in_base_currency", d));
total_gain_loss += flt(d.gain_loss, precision("gain_loss", d));
});
@@ -66,13 +66,19 @@
},
make_jv : function(frm) {
+ let revaluation_journal = null;
+ let zero_balance_journal = null;
frappe.call({
- method: "make_jv_entry",
+ method: "make_jv_entries",
doc: frm.doc,
+ freeze: true,
+ freeze_message: "Making Journal Entries...",
callback: function(r){
if (r.message) {
- var doc = frappe.model.sync(r.message)[0];
- frappe.set_route("Form", doc.doctype, doc.name);
+ let response = r.message;
+ if(response['revaluation_jv'] || response['zero_balance_jv']) {
+ frappe.msgprint(__("Journals have been created"));
+ }
}
}
});
diff --git a/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.json b/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.json
index e00b17e..0d198ca 100644
--- a/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.json
+++ b/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.json
@@ -14,6 +14,9 @@
"get_entries",
"accounts",
"section_break_6",
+ "gain_loss_unbooked",
+ "gain_loss_booked",
+ "column_break_10",
"total_gain_loss",
"amended_from"
],
@@ -60,13 +63,6 @@
"fieldtype": "Section Break"
},
{
- "fieldname": "total_gain_loss",
- "fieldtype": "Currency",
- "label": "Total Gain/Loss",
- "options": "Company:company:default_currency",
- "read_only": 1
- },
- {
"fieldname": "amended_from",
"fieldtype": "Link",
"label": "Amended From",
@@ -74,11 +70,37 @@
"options": "Exchange Rate Revaluation",
"print_hide": 1,
"read_only": 1
+ },
+ {
+ "fieldname": "gain_loss_unbooked",
+ "fieldtype": "Currency",
+ "label": "Gain/Loss from Revaluation",
+ "options": "Company:company:default_currency",
+ "read_only": 1
+ },
+ {
+ "description": "Gain/Loss accumulated in foreign currency account. Accounts with '0' balance in either Base or Account currency",
+ "fieldname": "gain_loss_booked",
+ "fieldtype": "Currency",
+ "label": "Gain/Loss already booked",
+ "options": "Company:company:default_currency",
+ "read_only": 1
+ },
+ {
+ "fieldname": "total_gain_loss",
+ "fieldtype": "Currency",
+ "label": "Total Gain/Loss",
+ "options": "Company:company:default_currency",
+ "read_only": 1
+ },
+ {
+ "fieldname": "column_break_10",
+ "fieldtype": "Column Break"
}
],
"is_submittable": 1,
"links": [],
- "modified": "2022-11-17 10:28:03.911554",
+ "modified": "2022-12-29 19:38:24.416529",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Exchange Rate Revaluation",
diff --git a/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.py b/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.py
index 68e828b..d67d59b 100644
--- a/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.py
+++ b/erpnext/accounts/doctype/exchange_rate_revaluation/exchange_rate_revaluation.py
@@ -3,10 +3,12 @@
import frappe
-from frappe import _
+from frappe import _, qb
from frappe.model.document import Document
from frappe.model.meta import get_field_precision
-from frappe.utils import flt
+from frappe.query_builder import Criterion, Order
+from frappe.query_builder.functions import NullIf, Sum
+from frappe.utils import flt, get_link_to_form
import erpnext
from erpnext.accounts.doctype.journal_entry.journal_entry import get_balance_on
@@ -19,11 +21,25 @@
def set_total_gain_loss(self):
total_gain_loss = 0
+
+ gain_loss_booked = 0
+ gain_loss_unbooked = 0
+
for d in self.accounts:
- d.gain_loss = flt(
- d.new_balance_in_base_currency, d.precision("new_balance_in_base_currency")
- ) - flt(d.balance_in_base_currency, d.precision("balance_in_base_currency"))
+ if not d.zero_balance:
+ d.gain_loss = flt(
+ d.new_balance_in_base_currency, d.precision("new_balance_in_base_currency")
+ ) - flt(d.balance_in_base_currency, d.precision("balance_in_base_currency"))
+
+ if d.zero_balance:
+ gain_loss_booked += flt(d.gain_loss, d.precision("gain_loss"))
+ else:
+ gain_loss_unbooked += flt(d.gain_loss, d.precision("gain_loss"))
+
total_gain_loss += flt(d.gain_loss, d.precision("gain_loss"))
+
+ self.gain_loss_booked = gain_loss_booked
+ self.gain_loss_unbooked = gain_loss_unbooked
self.total_gain_loss = flt(total_gain_loss, self.precision("total_gain_loss"))
def validate_mandatory(self):
@@ -35,98 +51,206 @@
@frappe.whitelist()
def check_journal_entry_condition(self):
- total_debit = frappe.db.get_value(
- "Journal Entry Account",
- {"reference_type": "Exchange Rate Revaluation", "reference_name": self.name, "docstatus": 1},
- "sum(debit) as sum",
+ exchange_gain_loss_account = self.get_for_unrealized_gain_loss_account()
+
+ jea = qb.DocType("Journal Entry Account")
+ journals = (
+ qb.from_(jea)
+ .select(jea.parent)
+ .distinct()
+ .where(
+ (jea.reference_type == "Exchange Rate Revaluation")
+ & (jea.reference_name == self.name)
+ & (jea.docstatus == 1)
+ )
+ .run()
)
- total_amt = 0
- for d in self.accounts:
- total_amt = total_amt + d.new_balance_in_base_currency
+ if journals:
+ gle = qb.DocType("GL Entry")
+ total_amt = (
+ qb.from_(gle)
+ .select((Sum(gle.credit) - Sum(gle.debit)).as_("total_amount"))
+ .where(
+ (gle.voucher_type == "Journal Entry")
+ & (gle.voucher_no.isin(journals))
+ & (gle.account == exchange_gain_loss_account)
+ & (gle.is_cancelled == 0)
+ )
+ .run()
+ )
- if total_amt != total_debit:
- return True
+ if total_amt and total_amt[0][0] != self.total_gain_loss:
+ return True
+ else:
+ return False
- return False
+ return True
@frappe.whitelist()
- def get_accounts_data(self, account=None):
- accounts = []
+ def get_accounts_data(self):
self.validate_mandatory()
- company_currency = erpnext.get_company_currency(self.company)
+ account_details = self.get_account_balance_from_gle(
+ company=self.company, posting_date=self.posting_date, account=None, party_type=None, party=None
+ )
+ accounts_with_new_balance = self.calculate_new_account_balance(
+ self.company, self.posting_date, account_details
+ )
+
+ if not accounts_with_new_balance:
+ self.throw_invalid_response_message(account_details)
+
+ return accounts_with_new_balance
+
+ @staticmethod
+ def get_account_balance_from_gle(company, posting_date, account, party_type, party):
+ account_details = []
+
+ if company and posting_date:
+ company_currency = erpnext.get_company_currency(company)
+
+ acc = qb.DocType("Account")
+ if account:
+ accounts = [account]
+ else:
+ res = (
+ qb.from_(acc)
+ .select(acc.name)
+ .where(
+ (acc.is_group == 0)
+ & (acc.report_type == "Balance Sheet")
+ & (acc.root_type.isin(["Asset", "Liability", "Equity"]))
+ & (acc.account_type != "Stock")
+ & (acc.company == company)
+ & (acc.account_currency != company_currency)
+ )
+ .orderby(acc.name)
+ .run(as_list=True)
+ )
+ accounts = [x[0] for x in res]
+
+ if accounts:
+ having_clause = (qb.Field("balance") != qb.Field("balance_in_account_currency")) & (
+ (qb.Field("balance_in_account_currency") != 0) | (qb.Field("balance") != 0)
+ )
+
+ gle = qb.DocType("GL Entry")
+
+ # conditions
+ conditions = []
+ conditions.append(gle.account.isin(accounts))
+ conditions.append(gle.posting_date.lte(posting_date))
+ conditions.append(gle.is_cancelled == 0)
+
+ if party_type:
+ conditions.append(gle.party_type == party_type)
+ if party:
+ conditions.append(gle.party == party)
+
+ account_details = (
+ qb.from_(gle)
+ .select(
+ gle.account,
+ gle.party_type,
+ gle.party,
+ gle.account_currency,
+ (Sum(gle.debit_in_account_currency) - Sum(gle.credit_in_account_currency)).as_(
+ "balance_in_account_currency"
+ ),
+ (Sum(gle.debit) - Sum(gle.credit)).as_("balance"),
+ (Sum(gle.debit) - Sum(gle.credit) == 0)
+ ^ (Sum(gle.debit_in_account_currency) - Sum(gle.credit_in_account_currency) == 0).as_(
+ "zero_balance"
+ ),
+ )
+ .where(Criterion.all(conditions))
+ .groupby(gle.account, NullIf(gle.party_type, ""), NullIf(gle.party, ""))
+ .having(having_clause)
+ .orderby(gle.account)
+ .run(as_dict=True)
+ )
+
+ return account_details
+
+ @staticmethod
+ def calculate_new_account_balance(company, posting_date, account_details):
+ accounts = []
+ company_currency = erpnext.get_company_currency(company)
precision = get_field_precision(
frappe.get_meta("Exchange Rate Revaluation Account").get_field("new_balance_in_base_currency"),
company_currency,
)
- account_details = self.get_accounts_from_gle()
- for d in account_details:
- current_exchange_rate = (
- d.balance / d.balance_in_account_currency if d.balance_in_account_currency else 0
- )
- new_exchange_rate = get_exchange_rate(d.account_currency, company_currency, self.posting_date)
- new_balance_in_base_currency = flt(d.balance_in_account_currency * new_exchange_rate)
- gain_loss = flt(new_balance_in_base_currency, precision) - flt(d.balance, precision)
- if gain_loss:
- accounts.append(
- {
- "account": d.account,
- "party_type": d.party_type,
- "party": d.party,
- "account_currency": d.account_currency,
- "balance_in_base_currency": d.balance,
- "balance_in_account_currency": d.balance_in_account_currency,
- "current_exchange_rate": current_exchange_rate,
- "new_exchange_rate": new_exchange_rate,
- "new_balance_in_base_currency": new_balance_in_base_currency,
- }
+ if account_details:
+ # Handle Accounts with balance in both Account/Base Currency
+ for d in [x for x in account_details if not x.zero_balance]:
+ current_exchange_rate = (
+ d.balance / d.balance_in_account_currency if d.balance_in_account_currency else 0
)
+ new_exchange_rate = get_exchange_rate(d.account_currency, company_currency, posting_date)
+ new_balance_in_base_currency = flt(d.balance_in_account_currency * new_exchange_rate)
+ gain_loss = flt(new_balance_in_base_currency, precision) - flt(d.balance, precision)
+ if gain_loss:
+ accounts.append(
+ {
+ "account": d.account,
+ "party_type": d.party_type,
+ "party": d.party,
+ "account_currency": d.account_currency,
+ "balance_in_base_currency": d.balance,
+ "balance_in_account_currency": d.balance_in_account_currency,
+ "zero_balance": d.zero_balance,
+ "current_exchange_rate": current_exchange_rate,
+ "new_exchange_rate": new_exchange_rate,
+ "new_balance_in_base_currency": new_balance_in_base_currency,
+ "new_balance_in_account_currency": d.balance_in_account_currency,
+ "gain_loss": gain_loss,
+ }
+ )
- if not accounts:
- self.throw_invalid_response_message(account_details)
+ # Handle Accounts with '0' balance in Account/Base Currency
+ for d in [x for x in account_details if x.zero_balance]:
+
+ # TODO: Set new balance in Base/Account currency
+ if d.balance > 0:
+ current_exchange_rate = new_exchange_rate = 0
+
+ new_balance_in_account_currency = 0 # this will be '0'
+ new_balance_in_base_currency = 0 # this will be '0'
+ gain_loss = flt(new_balance_in_base_currency, precision) - flt(d.balance, precision)
+ else:
+ new_exchange_rate = 0
+ new_balance_in_base_currency = 0
+ new_balance_in_account_currency = 0
+
+ current_exchange_rate = calculate_exchange_rate_using_last_gle(
+ company, d.account, d.party_type, d.party
+ )
+
+ gain_loss = new_balance_in_account_currency - (
+ current_exchange_rate * d.balance_in_account_currency
+ )
+
+ if gain_loss:
+ accounts.append(
+ {
+ "account": d.account,
+ "party_type": d.party_type,
+ "party": d.party,
+ "account_currency": d.account_currency,
+ "balance_in_base_currency": d.balance,
+ "balance_in_account_currency": d.balance_in_account_currency,
+ "zero_balance": d.zero_balance,
+ "current_exchange_rate": current_exchange_rate,
+ "new_exchange_rate": new_exchange_rate,
+ "new_balance_in_base_currency": new_balance_in_base_currency,
+ "new_balance_in_account_currency": new_balance_in_account_currency,
+ "gain_loss": gain_loss,
+ }
+ )
return accounts
- def get_accounts_from_gle(self):
- company_currency = erpnext.get_company_currency(self.company)
- accounts = frappe.db.sql_list(
- """
- select name
- from tabAccount
- where is_group = 0
- and report_type = 'Balance Sheet'
- and root_type in ('Asset', 'Liability', 'Equity')
- and account_type != 'Stock'
- and company=%s
- and account_currency != %s
- order by name""",
- (self.company, company_currency),
- )
-
- account_details = []
- if accounts:
- account_details = frappe.db.sql(
- """
- select
- account, party_type, party, account_currency,
- sum(debit_in_account_currency) - sum(credit_in_account_currency) as balance_in_account_currency,
- sum(debit) - sum(credit) as balance
- from `tabGL Entry`
- where account in (%s)
- and posting_date <= %s
- and is_cancelled = 0
- group by account, NULLIF(party_type,''), NULLIF(party,'')
- having sum(debit) != sum(credit)
- order by account
- """
- % (", ".join(["%s"] * len(accounts)), "%s"),
- tuple(accounts + [self.posting_date]),
- as_dict=1,
- )
-
- return account_details
-
def throw_invalid_response_message(self, account_details):
if account_details:
message = _("No outstanding invoices require exchange rate revaluation")
@@ -134,11 +258,7 @@
message = _("No outstanding invoices found")
frappe.msgprint(message)
- @frappe.whitelist()
- def make_jv_entry(self):
- if self.total_gain_loss == 0:
- return
-
+ def get_for_unrealized_gain_loss_account(self):
unrealized_exchange_gain_loss_account = frappe.get_cached_value(
"Company", self.company, "unrealized_exchange_gain_loss_account"
)
@@ -146,6 +266,130 @@
frappe.throw(
_("Please set Unrealized Exchange Gain/Loss Account in Company {0}").format(self.company)
)
+ return unrealized_exchange_gain_loss_account
+
+ @frappe.whitelist()
+ def make_jv_entries(self):
+ zero_balance_jv = self.make_jv_for_zero_balance()
+ if zero_balance_jv:
+ frappe.msgprint(
+ f"Zero Balance Journal: {get_link_to_form('Journal Entry', zero_balance_jv.name)}"
+ )
+
+ revaluation_jv = self.make_jv_for_revaluation()
+ if revaluation_jv:
+ frappe.msgprint(
+ f"Revaluation Journal: {get_link_to_form('Journal Entry', revaluation_jv.name)}"
+ )
+
+ return {
+ "revaluation_jv": revaluation_jv.name if revaluation_jv else None,
+ "zero_balance_jv": zero_balance_jv.name if zero_balance_jv else None,
+ }
+
+ def make_jv_for_zero_balance(self):
+ if self.gain_loss_booked == 0:
+ return
+
+ accounts = [x for x in self.accounts if x.zero_balance]
+
+ if not accounts:
+ return
+
+ unrealized_exchange_gain_loss_account = self.get_for_unrealized_gain_loss_account()
+
+ journal_entry = frappe.new_doc("Journal Entry")
+ journal_entry.voucher_type = "Exchange Gain Or Loss"
+ journal_entry.company = self.company
+ journal_entry.posting_date = self.posting_date
+ journal_entry.multi_currency = 1
+
+ journal_entry_accounts = []
+ for d in accounts:
+ journal_account = frappe._dict(
+ {
+ "account": d.get("account"),
+ "party_type": d.get("party_type"),
+ "party": d.get("party"),
+ "account_currency": d.get("account_currency"),
+ "balance": flt(
+ d.get("balance_in_account_currency"), d.precision("balance_in_account_currency")
+ ),
+ "exchange_rate": 0,
+ "cost_center": erpnext.get_default_cost_center(self.company),
+ "reference_type": "Exchange Rate Revaluation",
+ "reference_name": self.name,
+ }
+ )
+
+ # Account Currency has balance
+ if d.get("balance_in_account_currency") and not d.get("new_balance_in_account_currency"):
+ dr_or_cr = (
+ "credit_in_account_currency"
+ if d.get("balance_in_account_currency") > 0
+ else "debit_in_account_currency"
+ )
+ reverse_dr_or_cr = (
+ "debit_in_account_currency"
+ if dr_or_cr == "credit_in_account_currency"
+ else "credit_in_account_currency"
+ )
+ journal_account.update(
+ {
+ dr_or_cr: flt(
+ abs(d.get("balance_in_account_currency")), d.precision("balance_in_account_currency")
+ ),
+ reverse_dr_or_cr: 0,
+ "debit": 0,
+ "credit": 0,
+ }
+ )
+ elif d.get("balance_in_base_currency") and not d.get("new_balance_in_base_currency"):
+ # Base currency has balance
+ dr_or_cr = "credit" if d.get("balance_in_base_currency") > 0 else "debit"
+ reverse_dr_or_cr = "debit" if dr_or_cr == "credit" else "credit"
+ journal_account.update(
+ {
+ dr_or_cr: flt(
+ abs(d.get("balance_in_base_currency")), d.precision("balance_in_base_currency")
+ ),
+ reverse_dr_or_cr: 0,
+ "debit_in_account_currency": 0,
+ "credit_in_account_currency": 0,
+ }
+ )
+
+ journal_entry_accounts.append(journal_account)
+
+ journal_entry_accounts.append(
+ {
+ "account": unrealized_exchange_gain_loss_account,
+ "balance": get_balance_on(unrealized_exchange_gain_loss_account),
+ "debit": abs(self.gain_loss_booked) if self.gain_loss_booked < 0 else 0,
+ "credit": abs(self.gain_loss_booked) if self.gain_loss_booked > 0 else 0,
+ "debit_in_account_currency": abs(self.gain_loss_booked) if self.gain_loss_booked < 0 else 0,
+ "credit_in_account_currency": self.gain_loss_booked if self.gain_loss_booked > 0 else 0,
+ "cost_center": erpnext.get_default_cost_center(self.company),
+ "exchange_rate": 1,
+ "reference_type": "Exchange Rate Revaluation",
+ "reference_name": self.name,
+ }
+ )
+
+ journal_entry.set("accounts", journal_entry_accounts)
+ journal_entry.set_total_debit_credit()
+ journal_entry.save()
+ return journal_entry
+
+ def make_jv_for_revaluation(self):
+ if self.gain_loss_unbooked == 0:
+ return
+
+ accounts = [x for x in self.accounts if not x.zero_balance]
+ if not accounts:
+ return
+
+ unrealized_exchange_gain_loss_account = self.get_for_unrealized_gain_loss_account()
journal_entry = frappe.new_doc("Journal Entry")
journal_entry.voucher_type = "Exchange Rate Revaluation"
@@ -154,7 +398,7 @@
journal_entry.multi_currency = 1
journal_entry_accounts = []
- for d in self.accounts:
+ for d in accounts:
dr_or_cr = (
"debit_in_account_currency"
if d.get("balance_in_account_currency") > 0
@@ -179,6 +423,7 @@
dr_or_cr: flt(
abs(d.get("balance_in_account_currency")), d.precision("balance_in_account_currency")
),
+ "cost_center": erpnext.get_default_cost_center(self.company),
"exchange_rate": flt(d.get("new_exchange_rate"), d.precision("new_exchange_rate")),
"reference_type": "Exchange Rate Revaluation",
"reference_name": self.name,
@@ -196,6 +441,7 @@
reverse_dr_or_cr: flt(
abs(d.get("balance_in_account_currency")), d.precision("balance_in_account_currency")
),
+ "cost_center": erpnext.get_default_cost_center(self.company),
"exchange_rate": flt(d.get("current_exchange_rate"), d.precision("current_exchange_rate")),
"reference_type": "Exchange Rate Revaluation",
"reference_name": self.name,
@@ -206,8 +452,11 @@
{
"account": unrealized_exchange_gain_loss_account,
"balance": get_balance_on(unrealized_exchange_gain_loss_account),
- "debit_in_account_currency": abs(self.total_gain_loss) if self.total_gain_loss < 0 else 0,
- "credit_in_account_currency": self.total_gain_loss if self.total_gain_loss > 0 else 0,
+ "debit_in_account_currency": abs(self.gain_loss_unbooked)
+ if self.gain_loss_unbooked < 0
+ else 0,
+ "credit_in_account_currency": self.gain_loss_unbooked if self.gain_loss_unbooked > 0 else 0,
+ "cost_center": erpnext.get_default_cost_center(self.company),
"exchange_rate": 1,
"reference_type": "Exchange Rate Revaluation",
"reference_name": self.name,
@@ -217,42 +466,90 @@
journal_entry.set("accounts", journal_entry_accounts)
journal_entry.set_amounts_in_company_currency()
journal_entry.set_total_debit_credit()
- return journal_entry.as_dict()
+ journal_entry.save()
+ return journal_entry
+
+
+def calculate_exchange_rate_using_last_gle(company, account, party_type, party):
+ """
+ Use last GL entry to calculate exchange rate
+ """
+ last_exchange_rate = None
+ if company and account:
+ gl = qb.DocType("GL Entry")
+
+ # build conditions
+ conditions = []
+ conditions.append(gl.company == company)
+ conditions.append(gl.account == account)
+ conditions.append(gl.is_cancelled == 0)
+ if party_type:
+ conditions.append(gl.party_type == party_type)
+ if party:
+ conditions.append(gl.party == party)
+
+ voucher_type, voucher_no = (
+ qb.from_(gl)
+ .select(gl.voucher_type, gl.voucher_no)
+ .where(Criterion.all(conditions))
+ .orderby(gl.posting_date, order=Order.desc)
+ .limit(1)
+ .run()[0]
+ )
+
+ last_exchange_rate = (
+ qb.from_(gl)
+ .select((gl.debit - gl.credit) / (gl.debit_in_account_currency - gl.credit_in_account_currency))
+ .where(
+ (gl.voucher_type == voucher_type) & (gl.voucher_no == voucher_no) & (gl.account == account)
+ )
+ .orderby(gl.posting_date, order=Order.desc)
+ .limit(1)
+ .run()[0][0]
+ )
+
+ return last_exchange_rate
@frappe.whitelist()
-def get_account_details(account, company, posting_date, party_type=None, party=None):
+def get_account_details(company, posting_date, account, party_type=None, party=None):
+ if not (company and posting_date):
+ frappe.throw(_("Company and Posting Date is mandatory"))
+
account_currency, account_type = frappe.get_cached_value(
"Account", account, ["account_currency", "account_type"]
)
+
if account_type in ["Receivable", "Payable"] and not (party_type and party):
frappe.throw(_("Party Type and Party is mandatory for {0} account").format(account_type))
account_details = {}
company_currency = erpnext.get_company_currency(company)
- balance = get_balance_on(
- account, date=posting_date, party_type=party_type, party=party, in_account_currency=False
- )
+
account_details = {
"account_currency": account_currency,
}
+ account_balance = ExchangeRateRevaluation.get_account_balance_from_gle(
+ company=company, posting_date=posting_date, account=account, party_type=party_type, party=party
+ )
- if balance:
- balance_in_account_currency = get_balance_on(
- account, date=posting_date, party_type=party_type, party=party
+ if account_balance and (
+ account_balance[0].balance or account_balance[0].balance_in_account_currency
+ ):
+ account_with_new_balance = ExchangeRateRevaluation.calculate_new_account_balance(
+ company, posting_date, account_balance
)
- current_exchange_rate = (
- balance / balance_in_account_currency if balance_in_account_currency else 0
- )
- new_exchange_rate = get_exchange_rate(account_currency, company_currency, posting_date)
- new_balance_in_base_currency = balance_in_account_currency * new_exchange_rate
- account_details = account_details.update(
+ row = account_with_new_balance[0]
+ account_details.update(
{
- "balance_in_base_currency": balance,
- "balance_in_account_currency": balance_in_account_currency,
- "current_exchange_rate": current_exchange_rate,
- "new_exchange_rate": new_exchange_rate,
- "new_balance_in_base_currency": new_balance_in_base_currency,
+ "balance_in_base_currency": row["balance_in_base_currency"],
+ "balance_in_account_currency": row["balance_in_account_currency"],
+ "current_exchange_rate": row["current_exchange_rate"],
+ "new_exchange_rate": row["new_exchange_rate"],
+ "new_balance_in_base_currency": row["new_balance_in_base_currency"],
+ "new_balance_in_account_currency": row["new_balance_in_account_currency"],
+ "zero_balance": row["zero_balance"],
+ "gain_loss": row["gain_loss"],
}
)
diff --git a/erpnext/accounts/doctype/exchange_rate_revaluation_account/exchange_rate_revaluation_account.json b/erpnext/accounts/doctype/exchange_rate_revaluation_account/exchange_rate_revaluation_account.json
index 80e972b..2968359 100644
--- a/erpnext/accounts/doctype/exchange_rate_revaluation_account/exchange_rate_revaluation_account.json
+++ b/erpnext/accounts/doctype/exchange_rate_revaluation_account/exchange_rate_revaluation_account.json
@@ -10,14 +10,21 @@
"party",
"column_break_2",
"account_currency",
+ "account_balances",
"balance_in_account_currency",
+ "column_break_46yz",
+ "new_balance_in_account_currency",
"balances",
"current_exchange_rate",
- "balance_in_base_currency",
- "column_break_9",
+ "column_break_xown",
"new_exchange_rate",
+ "column_break_9",
+ "balance_in_base_currency",
+ "column_break_ukce",
"new_balance_in_base_currency",
- "gain_loss"
+ "section_break_ngrs",
+ "gain_loss",
+ "zero_balance"
],
"fields": [
{
@@ -78,7 +85,7 @@
},
{
"fieldname": "column_break_9",
- "fieldtype": "Column Break"
+ "fieldtype": "Section Break"
},
{
"fieldname": "new_exchange_rate",
@@ -102,11 +109,45 @@
"label": "Gain/Loss",
"options": "Company:company:default_currency",
"read_only": 1
+ },
+ {
+ "default": "0",
+ "description": "This Account has '0' balance in either Base Currency or Account Currency",
+ "fieldname": "zero_balance",
+ "fieldtype": "Check",
+ "label": "Zero Balance"
+ },
+ {
+ "fieldname": "new_balance_in_account_currency",
+ "fieldtype": "Currency",
+ "label": "New Balance In Account Currency",
+ "options": "account_currency",
+ "read_only": 1
+ },
+ {
+ "fieldname": "account_balances",
+ "fieldtype": "Section Break"
+ },
+ {
+ "fieldname": "column_break_46yz",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "column_break_xown",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "column_break_ukce",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "section_break_ngrs",
+ "fieldtype": "Section Break"
}
],
"istable": 1,
"links": [],
- "modified": "2022-11-17 10:26:18.302728",
+ "modified": "2022-12-29 19:38:52.915295",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Exchange Rate Revaluation Account",
diff --git a/erpnext/accounts/doctype/gl_entry/gl_entry.py b/erpnext/accounts/doctype/gl_entry/gl_entry.py
index f312048..f07a4fa 100644
--- a/erpnext/accounts/doctype/gl_entry/gl_entry.py
+++ b/erpnext/accounts/doctype/gl_entry/gl_entry.py
@@ -95,7 +95,15 @@
)
# Zero value transaction is not allowed
- if not (flt(self.debit, self.precision("debit")) or flt(self.credit, self.precision("credit"))):
+ if not (
+ flt(self.debit, self.precision("debit"))
+ or flt(self.credit, self.precision("credit"))
+ or (
+ self.voucher_type == "Journal Entry"
+ and frappe.get_cached_value("Journal Entry", self.voucher_no, "voucher_type")
+ == "Exchange Gain Or Loss"
+ )
+ ):
frappe.throw(
_("{0} {1}: Either debit or credit amount is required for {2}").format(
self.voucher_type, self.voucher_no, self.account
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.json b/erpnext/accounts/doctype/journal_entry/journal_entry.json
index 8e5ba37..3f69d5c 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.json
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.json
@@ -88,7 +88,7 @@
"label": "Entry Type",
"oldfieldname": "voucher_type",
"oldfieldtype": "Select",
- "options": "Journal Entry\nInter Company Journal Entry\nBank Entry\nCash Entry\nCredit Card Entry\nDebit Note\nCredit Note\nContra Entry\nExcise Entry\nWrite Off Entry\nOpening Entry\nDepreciation Entry\nExchange Rate Revaluation\nDeferred Revenue\nDeferred Expense",
+ "options": "Journal Entry\nInter Company Journal Entry\nBank Entry\nCash Entry\nCredit Card Entry\nDebit Note\nCredit Note\nContra Entry\nExcise Entry\nWrite Off Entry\nOpening Entry\nDepreciation Entry\nExchange Rate Revaluation\nExchange Gain Or Loss\nDeferred Revenue\nDeferred Expense",
"reqd": 1,
"search_index": 1
},
@@ -539,7 +539,7 @@
"idx": 176,
"is_submittable": 1,
"links": [],
- "modified": "2022-06-23 22:01:32.348337",
+ "modified": "2022-11-28 17:40:01.241908",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Journal Entry",
diff --git a/erpnext/accounts/doctype/journal_entry/journal_entry.py b/erpnext/accounts/doctype/journal_entry/journal_entry.py
index b63d57c..40a4803 100644
--- a/erpnext/accounts/doctype/journal_entry/journal_entry.py
+++ b/erpnext/accounts/doctype/journal_entry/journal_entry.py
@@ -601,16 +601,18 @@
d.against_account = ", ".join(list(set(accounts_debited)))
def validate_debit_credit_amount(self):
- for d in self.get("accounts"):
- if not flt(d.debit) and not flt(d.credit):
- frappe.throw(_("Row {0}: Both Debit and Credit values cannot be zero").format(d.idx))
+ if not (self.voucher_type == "Exchange Gain Or Loss" and self.multi_currency):
+ for d in self.get("accounts"):
+ if not flt(d.debit) and not flt(d.credit):
+ frappe.throw(_("Row {0}: Both Debit and Credit values cannot be zero").format(d.idx))
def validate_total_debit_and_credit(self):
self.set_total_debit_credit()
- if self.difference:
- frappe.throw(
- _("Total Debit must be equal to Total Credit. The difference is {0}").format(self.difference)
- )
+ if not (self.voucher_type == "Exchange Gain Or Loss" and self.multi_currency):
+ if self.difference:
+ frappe.throw(
+ _("Total Debit must be equal to Total Credit. The difference is {0}").format(self.difference)
+ )
def set_total_debit_credit(self):
self.total_debit, self.total_credit, self.difference = 0, 0, 0
@@ -648,16 +650,17 @@
self.set_exchange_rate()
def set_amounts_in_company_currency(self):
- for d in self.get("accounts"):
- d.debit_in_account_currency = flt(
- d.debit_in_account_currency, d.precision("debit_in_account_currency")
- )
- d.credit_in_account_currency = flt(
- d.credit_in_account_currency, d.precision("credit_in_account_currency")
- )
+ if not (self.voucher_type == "Exchange Gain Or Loss" and self.multi_currency):
+ for d in self.get("accounts"):
+ d.debit_in_account_currency = flt(
+ d.debit_in_account_currency, d.precision("debit_in_account_currency")
+ )
+ d.credit_in_account_currency = flt(
+ d.credit_in_account_currency, d.precision("credit_in_account_currency")
+ )
- d.debit = flt(d.debit_in_account_currency * flt(d.exchange_rate), d.precision("debit"))
- d.credit = flt(d.credit_in_account_currency * flt(d.exchange_rate), d.precision("credit"))
+ d.debit = flt(d.debit_in_account_currency * flt(d.exchange_rate), d.precision("debit"))
+ d.credit = flt(d.credit_in_account_currency * flt(d.exchange_rate), d.precision("credit"))
def set_exchange_rate(self):
for d in self.get("accounts"):
@@ -786,7 +789,7 @@
def build_gl_map(self):
gl_map = []
for d in self.get("accounts"):
- if d.debit or d.credit:
+ if d.debit or d.credit or (self.voucher_type == "Exchange Gain Or Loss"):
r = [d.user_remark, self.remark]
r = [x for x in r if x]
remarks = "\n".join(r)
diff --git a/erpnext/accounts/general_ledger.py b/erpnext/accounts/general_ledger.py
index c757057..41fdb6a 100644
--- a/erpnext/accounts/general_ledger.py
+++ b/erpnext/accounts/general_ledger.py
@@ -199,7 +199,14 @@
# 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
+ lambda x: flt(x.debit, precision) != 0
+ or flt(x.credit, precision) != 0
+ or (
+ x.voucher_type == "Journal Entry"
+ and frappe.get_cached_value("Journal Entry", x.voucher_no, "voucher_type")
+ == "Exchange Gain Or Loss"
+ ),
+ merged_gl_map,
)
merged_gl_map = list(merged_gl_map)
@@ -350,15 +357,26 @@
allowance = get_debit_credit_allowance(voucher_type, precision)
debit_credit_diff = get_debit_credit_difference(gl_map, precision)
+
if abs(debit_credit_diff) > allowance:
- raise_debit_credit_not_equal_error(debit_credit_diff, voucher_type, voucher_no)
+ if not (
+ voucher_type == "Journal Entry"
+ and frappe.get_cached_value("Journal Entry", voucher_no, "voucher_type")
+ == "Exchange Gain Or Loss"
+ ):
+ raise_debit_credit_not_equal_error(debit_credit_diff, voucher_type, voucher_no)
elif abs(debit_credit_diff) >= (1.0 / (10**precision)):
make_round_off_gle(gl_map, debit_credit_diff, precision)
debit_credit_diff = get_debit_credit_difference(gl_map, precision)
if abs(debit_credit_diff) > allowance:
- raise_debit_credit_not_equal_error(debit_credit_diff, voucher_type, voucher_no)
+ if not (
+ voucher_type == "Journal Entry"
+ and frappe.get_cached_value("Journal Entry", voucher_no, "voucher_type")
+ == "Exchange Gain Or Loss"
+ ):
+ raise_debit_credit_not_equal_error(debit_credit_diff, voucher_type, voucher_no)
def get_debit_credit_difference(gl_map, precision):
diff --git a/erpnext/accounts/report/accounts_receivable/test_accounts_receivable.py b/erpnext/accounts/report/accounts_receivable/test_accounts_receivable.py
index 97a9c15..afd02a0 100644
--- a/erpnext/accounts/report/accounts_receivable/test_accounts_receivable.py
+++ b/erpnext/accounts/report/accounts_receivable/test_accounts_receivable.py
@@ -184,11 +184,9 @@
err = err.save().submit()
# Submit JV for ERR
- jv = frappe.get_doc(err.make_jv_entry())
- jv = jv.save()
- for x in jv.accounts:
- x.cost_center = get_default_cost_center(jv.company)
- jv.submit()
+ err_journals = err.make_jv_entries()
+ je = frappe.get_doc("Journal Entry", err_journals.get("revaluation_jv"))
+ je = je.submit()
filters = {
"company": company,
@@ -201,7 +199,7 @@
report = execute(filters)
expected_data_for_err = [0, -5, 0, 5]
- row = [x for x in report[1] if x.voucher_type == jv.doctype and x.voucher_no == jv.name][0]
+ row = [x for x in report[1] if x.voucher_type == je.doctype and x.voucher_no == je.name][0]
self.assertEqual(
expected_data_for_err,
[
diff --git a/erpnext/accounts/report/general_ledger/test_general_ledger.py b/erpnext/accounts/report/general_ledger/test_general_ledger.py
index b10e769..c563785 100644
--- a/erpnext/accounts/report/general_ledger/test_general_ledger.py
+++ b/erpnext/accounts/report/general_ledger/test_general_ledger.py
@@ -109,8 +109,7 @@
frappe.db.set_value(
"Company", company, "unrealized_exchange_gain_loss_account", "_Test Exchange Gain/Loss - _TC"
)
- revaluation_jv = revaluation.make_jv_entry()
- revaluation_jv = frappe.get_doc(revaluation_jv)
+ revaluation_jv = revaluation.make_jv_for_revaluation()
revaluation_jv.cost_center = "_Test Cost Center - _TC"
for acc in revaluation_jv.get("accounts"):
acc.cost_center = "_Test Cost Center - _TC"
diff --git a/erpnext/accounts/report/utils.py b/erpnext/accounts/report/utils.py
index d3cd290..97cc1c4 100644
--- a/erpnext/accounts/report/utils.py
+++ b/erpnext/accounts/report/utils.py
@@ -101,11 +101,8 @@
account_currency = entry["account_currency"]
if len(account_currencies) == 1 and account_currency == presentation_currency:
- if debit_in_account_currency:
- entry["debit"] = debit_in_account_currency
-
- if credit_in_account_currency:
- entry["credit"] = credit_in_account_currency
+ entry["debit"] = debit_in_account_currency
+ entry["credit"] = credit_in_account_currency
else:
date = currency_info["report_date"]
converted_debit_value = convert(debit, presentation_currency, company_currency, date)