Merge pull request #32204 from deepeshgarg007/pending_tds_vouchers
fix(UX): More predictable tax withholding application in invoices
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.js b/erpnext/accounts/doctype/payment_entry/payment_entry.js
index 0f53079..6039bdf 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.js
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.js
@@ -1091,7 +1091,7 @@
$.each(tax_fields, function(i, fieldname) { tax[fieldname] = 0.0; });
- frm.doc.paid_amount_after_tax = frm.doc.paid_amount;
+ frm.doc.paid_amount_after_tax = frm.doc.base_paid_amount;
});
},
@@ -1182,7 +1182,7 @@
}
cumulated_tax_fraction += tax.tax_fraction_for_current_item;
- frm.doc.paid_amount_after_tax = flt(frm.doc.paid_amount/(1+cumulated_tax_fraction))
+ frm.doc.paid_amount_after_tax = flt(frm.doc.base_paid_amount/(1+cumulated_tax_fraction))
});
},
@@ -1214,6 +1214,7 @@
frm.doc.total_taxes_and_charges = 0.0;
frm.doc.base_total_taxes_and_charges = 0.0;
+ let company_currency = frappe.get_doc(":Company", frm.doc.company).default_currency;
let actual_tax_dict = {};
// maintain actual tax rate based on idx
@@ -1234,8 +1235,8 @@
}
}
- tax.tax_amount = current_tax_amount;
- tax.base_tax_amount = tax.tax_amount * frm.doc.source_exchange_rate;
+ // tax accounts are only in company currency
+ tax.base_tax_amount = current_tax_amount;
current_tax_amount *= (tax.add_deduct_tax == "Deduct") ? -1.0 : 1.0;
if(i==0) {
@@ -1244,9 +1245,29 @@
tax.total = flt(frm.doc["taxes"][i-1].total + current_tax_amount, precision("total", tax));
}
- tax.base_total = tax.total * frm.doc.source_exchange_rate;
- frm.doc.total_taxes_and_charges += current_tax_amount;
- frm.doc.base_total_taxes_and_charges += current_tax_amount * frm.doc.source_exchange_rate;
+ // tac accounts are only in company currency
+ tax.base_total = tax.total
+
+ // calculate total taxes and base total taxes
+ if(frm.doc.payment_type == "Pay") {
+ // tax accounts only have company currency
+ if(tax.currency != frm.doc.paid_to_account_currency) {
+ //total_taxes_and_charges has the target currency. so using target conversion rate
+ frm.doc.total_taxes_and_charges += flt(current_tax_amount / frm.doc.target_exchange_rate);
+
+ } else {
+ frm.doc.total_taxes_and_charges += current_tax_amount;
+ }
+ } else if(frm.doc.payment_type == "Receive") {
+ if(tax.currency != frm.doc.paid_from_account_currency) {
+ //total_taxes_and_charges has the target currency. so using source conversion rate
+ frm.doc.total_taxes_and_charges += flt(current_tax_amount / frm.doc.source_exchange_rate);
+ } else {
+ frm.doc.total_taxes_and_charges += current_tax_amount;
+ }
+ }
+
+ frm.doc.base_total_taxes_and_charges += tax.base_tax_amount;
frm.refresh_field('taxes');
frm.refresh_field('total_taxes_and_charges');
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index 4618d08..7f245fd 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -940,6 +940,13 @@
)
if not d.included_in_paid_amount:
+ if get_account_currency(payment_account) != self.company_currency:
+ if self.payment_type == "Receive":
+ exchange_rate = self.target_exchange_rate
+ elif self.payment_type in ["Pay", "Internal Transfer"]:
+ exchange_rate = self.source_exchange_rate
+ base_tax_amount = flt((tax_amount / exchange_rate), self.precision("paid_amount"))
+
gl_entries.append(
self.get_gl_dict(
{
@@ -1033,7 +1040,7 @@
for fieldname in tax_fields:
tax.set(fieldname, 0.0)
- self.paid_amount_after_tax = self.paid_amount
+ self.paid_amount_after_tax = self.base_paid_amount
def determine_exclusive_rate(self):
if not any(cint(tax.included_in_paid_amount) for tax in self.get("taxes")):
@@ -1052,7 +1059,7 @@
cumulated_tax_fraction += tax.tax_fraction_for_current_item
- self.paid_amount_after_tax = flt(self.paid_amount / (1 + cumulated_tax_fraction))
+ self.paid_amount_after_tax = flt(self.base_paid_amount / (1 + cumulated_tax_fraction))
def calculate_taxes(self):
self.total_taxes_and_charges = 0.0
@@ -1075,7 +1082,7 @@
current_tax_amount += actual_tax_dict[tax.idx]
tax.tax_amount = current_tax_amount
- tax.base_tax_amount = tax.tax_amount * self.source_exchange_rate
+ tax.base_tax_amount = current_tax_amount
if tax.add_deduct_tax == "Deduct":
current_tax_amount *= -1.0
@@ -1089,14 +1096,20 @@
self.get("taxes")[i - 1].total + current_tax_amount, self.precision("total", tax)
)
- tax.base_total = tax.total * self.source_exchange_rate
+ tax.base_total = tax.total
if self.payment_type == "Pay":
- self.base_total_taxes_and_charges += flt(current_tax_amount / self.source_exchange_rate)
- self.total_taxes_and_charges += flt(current_tax_amount / self.target_exchange_rate)
- else:
- self.base_total_taxes_and_charges += flt(current_tax_amount / self.target_exchange_rate)
- self.total_taxes_and_charges += flt(current_tax_amount / self.source_exchange_rate)
+ if tax.currency != self.paid_to_account_currency:
+ self.total_taxes_and_charges += flt(current_tax_amount / self.target_exchange_rate)
+ else:
+ self.total_taxes_and_charges += current_tax_amount
+ elif self.payment_type == "Receive":
+ if tax.currency != self.paid_from_account_currency:
+ self.total_taxes_and_charges += flt(current_tax_amount / self.source_exchange_rate)
+ else:
+ self.total_taxes_and_charges += current_tax_amount
+
+ self.base_total_taxes_and_charges += tax.base_tax_amount
if self.get("taxes"):
self.paid_amount_after_tax = self.get("taxes")[-1].base_total
diff --git a/erpnext/accounts/doctype/payment_entry/test_payment_entry.py b/erpnext/accounts/doctype/payment_entry/test_payment_entry.py
index 02627eb..123b5df 100644
--- a/erpnext/accounts/doctype/payment_entry/test_payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/test_payment_entry.py
@@ -4,6 +4,7 @@
import unittest
import frappe
+from frappe import qb
from frappe.tests.utils import FrappeTestCase
from frappe.utils import flt, nowdate
@@ -722,6 +723,46 @@
flt(payment_entry.total_taxes_and_charges, 2), flt(10 / payment_entry.target_exchange_rate, 2)
)
+ def test_gl_of_multi_currency_payment_with_taxes(self):
+ payment_entry = create_payment_entry(
+ party="_Test Supplier USD", paid_to="_Test Payable USD - _TC", save=True
+ )
+ payment_entry.append(
+ "taxes",
+ {
+ "account_head": "_Test Account Service Tax - _TC",
+ "charge_type": "Actual",
+ "tax_amount": 100,
+ "add_deduct_tax": "Add",
+ "description": "Test",
+ },
+ )
+ payment_entry.target_exchange_rate = 80
+ payment_entry.received_amount = 12.5
+ payment_entry = payment_entry.submit()
+ gle = qb.DocType("GL Entry")
+ gl_entries = (
+ qb.from_(gle)
+ .select(
+ gle.account,
+ gle.debit,
+ gle.credit,
+ gle.debit_in_account_currency,
+ gle.credit_in_account_currency,
+ )
+ .orderby(gle.account)
+ .where(gle.voucher_no == payment_entry.name)
+ .run()
+ )
+
+ expected_gl_entries = (
+ ("_Test Account Service Tax - _TC", 100.0, 0.0, 100.0, 0.0),
+ ("_Test Bank - _TC", 0.0, 1100.0, 0.0, 1100.0),
+ ("_Test Payable USD - _TC", 1000.0, 0.0, 12.5, 0),
+ )
+
+ self.assertEqual(gl_entries, expected_gl_entries)
+
def test_payment_entry_against_onhold_purchase_invoice(self):
pi = make_purchase_invoice()
diff --git a/erpnext/accounts/doctype/payment_schedule/payment_schedule.json b/erpnext/accounts/doctype/payment_schedule/payment_schedule.json
index 6ed7a31..dde9980 100644
--- a/erpnext/accounts/doctype/payment_schedule/payment_schedule.json
+++ b/erpnext/accounts/doctype/payment_schedule/payment_schedule.json
@@ -39,6 +39,7 @@
{
"columns": 2,
"fetch_from": "payment_term.description",
+ "fetch_if_empty": 1,
"fieldname": "description",
"fieldtype": "Small Text",
"in_list_view": 1,
@@ -159,7 +160,7 @@
"index_web_pages_for_search": 1,
"istable": 1,
"links": [],
- "modified": "2021-04-28 05:41:35.084233",
+ "modified": "2022-09-16 13:57:06.382859",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Payment Schedule",
@@ -168,5 +169,6 @@
"quick_entry": 1,
"sort_field": "modified",
"sort_order": "DESC",
+ "states": [],
"track_changes": 1
}
\ No newline at end of file
diff --git a/erpnext/stock/report/item_shortage_report/item_shortage_report.py b/erpnext/stock/report/item_shortage_report/item_shortage_report.py
index 03a3a6a..9fafe91 100644
--- a/erpnext/stock/report/item_shortage_report/item_shortage_report.py
+++ b/erpnext/stock/report/item_shortage_report/item_shortage_report.py
@@ -8,8 +8,7 @@
def execute(filters=None):
columns = get_columns()
- conditions = get_conditions(filters)
- data = get_data(conditions, filters)
+ data = get_data(filters)
if not data:
return [], [], None, []
@@ -19,49 +18,39 @@
return columns, data, None, chart_data
-def get_conditions(filters):
- conditions = ""
+def get_data(filters):
+ bin = frappe.qb.DocType("Bin")
+ wh = frappe.qb.DocType("Warehouse")
+ item = frappe.qb.DocType("Item")
- if filters.get("warehouse"):
- conditions += "AND warehouse in %(warehouse)s"
- if filters.get("company"):
- conditions += "AND company = %(company)s"
-
- return conditions
-
-
-def get_data(conditions, filters):
- data = frappe.db.sql(
- """
- SELECT
+ query = (
+ frappe.qb.from_(bin)
+ .from_(wh)
+ .from_(item)
+ .select(
bin.warehouse,
bin.item_code,
- bin.actual_qty ,
- bin.ordered_qty ,
- bin.planned_qty ,
- bin.reserved_qty ,
+ bin.actual_qty,
+ bin.ordered_qty,
+ bin.planned_qty,
+ bin.reserved_qty,
bin.reserved_qty_for_production,
- bin.projected_qty ,
- warehouse.company,
- item.item_name ,
- item.description
- FROM
- `tabBin` bin,
- `tabWarehouse` warehouse,
- `tabItem` item
- WHERE
- bin.projected_qty<0
- AND warehouse.name = bin.warehouse
- AND bin.item_code=item.name
- {0}
- ORDER BY bin.projected_qty;""".format(
- conditions
- ),
- filters,
- as_dict=1,
+ bin.projected_qty,
+ wh.company,
+ item.item_name,
+ item.description,
+ )
+ .where((bin.projected_qty < 0) & (wh.name == bin.warehouse) & (bin.item_code == item.name))
+ .orderby(bin.projected_qty)
)
- return data
+ if filters.get("warehouse"):
+ query = query.where(bin.warehouse.isin(filters.get("warehouse")))
+
+ if filters.get("company"):
+ query = query.where(wh.company == filters.get("company"))
+
+ return query.run(as_dict=True)
def get_chart_data(data):
diff --git a/erpnext/stock/report/item_shortage_report/test_item_shortage_report.py b/erpnext/stock/report/item_shortage_report/test_item_shortage_report.py
new file mode 100644
index 0000000..5884c32
--- /dev/null
+++ b/erpnext/stock/report/item_shortage_report/test_item_shortage_report.py
@@ -0,0 +1,51 @@
+# Copyright (c) 2022, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+import frappe
+from frappe.tests.utils import FrappeTestCase
+
+from erpnext.selling.doctype.sales_order.test_sales_order import make_sales_order
+from erpnext.stock.doctype.item.test_item import make_item
+from erpnext.stock.report.item_shortage_report.item_shortage_report import (
+ execute as item_shortage_report,
+)
+
+
+class TestItemShortageReport(FrappeTestCase):
+ def test_item_shortage_report(self):
+ item = make_item().name
+ so = make_sales_order(item_code=item)
+
+ reserved_qty, projected_qty = frappe.db.get_value(
+ "Bin",
+ {
+ "item_code": item,
+ "warehouse": so.items[0].warehouse,
+ },
+ ["reserved_qty", "projected_qty"],
+ )
+ self.assertEqual(reserved_qty, so.items[0].qty)
+ self.assertEqual(projected_qty, -(so.items[0].qty))
+
+ filters = {
+ "company": so.company,
+ }
+ report_data = item_shortage_report(filters)[1]
+ item_code_list = [row.get("item_code") for row in report_data]
+ self.assertIn(item, item_code_list)
+
+ filters = {
+ "company": so.company,
+ "warehouse": [so.items[0].warehouse],
+ }
+ report_data = item_shortage_report(filters)[1]
+ item_code_list = [row.get("item_code") for row in report_data]
+ self.assertIn(item, item_code_list)
+
+ filters = {
+ "company": so.company,
+ "warehouse": ["Work In Progress - _TC"],
+ }
+ report_data = item_shortage_report(filters)[1]
+ item_code_list = [row.get("item_code") for row in report_data]
+ self.assertNotIn(item, item_code_list)