fix: incorrect TCS amount while customer has advance payment (#35397)
* fix: incorrect TCS amount while customer has advance payment
* test: only unallocated advance should for threshold breach validation
diff --git a/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py b/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
index 1f2d980..d8c0370 100644
--- a/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
+++ b/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
@@ -3,8 +3,10 @@
import frappe
-from frappe import _
+from frappe import _, qb
from frappe.model.document import Document
+from frappe.query_builder import Criterion
+from frappe.query_builder.functions import Abs, Sum
from frappe.utils import cint, getdate
@@ -346,26 +348,33 @@
def get_advance_vouchers(
parties, company=None, from_date=None, to_date=None, party_type="Supplier"
):
- # for advance vouchers, debit and credit is reversed
- dr_or_cr = "debit" if party_type == "Supplier" else "credit"
+ """
+ Use Payment Ledger to fetch unallocated Advance Payments
+ """
- filters = {
- dr_or_cr: [">", 0],
- "is_opening": "No",
- "is_cancelled": 0,
- "party_type": party_type,
- "party": ["in", parties],
- }
+ ple = qb.DocType("Payment Ledger Entry")
- if party_type == "Customer":
- filters.update({"against_voucher": ["is", "not set"]})
+ conditions = []
+
+ conditions.append(ple.amount.lt(0))
+ conditions.append(ple.delinked == 0)
+ conditions.append(ple.party_type == party_type)
+ conditions.append(ple.party.isin(parties))
+ conditions.append(ple.voucher_no == ple.against_voucher_no)
if company:
- filters["company"] = company
- if from_date and to_date:
- filters["posting_date"] = ["between", (from_date, to_date)]
+ conditions.append(ple.company == company)
- return frappe.get_all("GL Entry", filters=filters, distinct=1, pluck="voucher_no") or [""]
+ if from_date and to_date:
+ conditions.append(ple.posting_date[from_date:to_date])
+
+ advances = (
+ qb.from_(ple).select(ple.voucher_no).distinct().where(Criterion.all(conditions)).run(as_list=1)
+ )
+ if advances:
+ advances = [x[0] for x in advances]
+
+ return advances
def get_taxes_deducted_on_advances_allocated(inv, tax_details):
@@ -499,6 +508,7 @@
def get_tcs_amount(parties, inv, tax_details, vouchers, adv_vouchers):
tcs_amount = 0
+ ple = qb.DocType("Payment Ledger Entry")
# sum of debit entries made from sales invoices
invoiced_amt = (
@@ -516,18 +526,20 @@
)
# sum of credit entries made from PE / JV with unset 'against voucher'
+
+ conditions = []
+ conditions.append(ple.amount.lt(0))
+ conditions.append(ple.delinked == 0)
+ conditions.append(ple.party.isin(parties))
+ conditions.append(ple.voucher_no == ple.against_voucher_no)
+ conditions.append(ple.company == inv.company)
+
+ advances = (
+ qb.from_(ple).select(Abs(Sum(ple.amount))).where(Criterion.all(conditions)).run(as_list=1)
+ )
+
advance_amt = (
- frappe.db.get_value(
- "GL Entry",
- {
- "is_cancelled": 0,
- "party": ["in", parties],
- "company": inv.company,
- "voucher_no": ["in", adv_vouchers],
- },
- "sum(credit)",
- )
- or 0.0
+ qb.from_(ple).select(Abs(Sum(ple.amount))).where(Criterion.all(conditions)).run()[0][0] or 0.0
)
# sum of credit entries made from sales invoice
diff --git a/erpnext/accounts/doctype/tax_withholding_category/test_tax_withholding_category.py b/erpnext/accounts/doctype/tax_withholding_category/test_tax_withholding_category.py
index bc4f670..4580b13 100644
--- a/erpnext/accounts/doctype/tax_withholding_category/test_tax_withholding_category.py
+++ b/erpnext/accounts/doctype/tax_withholding_category/test_tax_withholding_category.py
@@ -152,6 +152,60 @@
for d in reversed(invoices):
d.cancel()
+ def test_tcs_on_unallocated_advance_payments(self):
+ frappe.db.set_value(
+ "Customer", "Test TCS Customer", "tax_withholding_category", "Cumulative Threshold TCS"
+ )
+
+ vouchers = []
+
+ # create advance payment
+ pe = create_payment_entry(
+ payment_type="Receive", party_type="Customer", party="Test TCS Customer", paid_amount=20000
+ )
+ pe.paid_from = "Debtors - _TC"
+ pe.paid_to = "Cash - _TC"
+ pe.submit()
+ vouchers.append(pe)
+
+ # create invoice
+ si1 = create_sales_invoice(customer="Test TCS Customer", rate=5000)
+ si1.submit()
+ vouchers.append(si1)
+
+ # reconcile
+ pr = frappe.get_doc("Payment Reconciliation")
+ pr.company = "_Test Company"
+ pr.party_type = "Customer"
+ pr.party = "Test TCS Customer"
+ pr.receivable_payable_account = "Debtors - _TC"
+ pr.get_unreconciled_entries()
+ invoices = [x.as_dict() for x in pr.get("invoices")]
+ payments = [x.as_dict() for x in pr.get("payments")]
+ pr.allocate_entries(frappe._dict({"invoices": invoices, "payments": payments}))
+ pr.reconcile()
+
+ # make another invoice
+ # sum of unallocated amount from payment entry and this sales invoice will breach cumulative threashold
+ # TDS should be calculated
+ si2 = create_sales_invoice(customer="Test TCS Customer", rate=15000)
+ si2.submit()
+ vouchers.append(si2)
+
+ si3 = create_sales_invoice(customer="Test TCS Customer", rate=10000)
+ si3.submit()
+ vouchers.append(si3)
+
+ # assert tax collection on total invoice amount created until now
+ tcs_charged = sum([d.base_tax_amount for d in si2.taxes if d.account_head == "TCS - _TC"])
+ tcs_charged += sum([d.base_tax_amount for d in si3.taxes if d.account_head == "TCS - _TC"])
+ self.assertEqual(tcs_charged, 1500)
+
+ # cancel invoice and payments to avoid clashing
+ for d in reversed(vouchers):
+ d.reload()
+ d.cancel()
+
def test_tds_calculation_on_net_total(self):
frappe.db.set_value(
"Supplier", "Test TDS Supplier4", "tax_withholding_category", "Cumulative Threshold TDS"