Merge pull request #16016 from rohitwaghchaure/fixed_tax_withholding_issue
[Fix] Tax Withholding, TDS is applying on the tax
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
index 1bb7c97..bfdf451 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
@@ -830,6 +830,10 @@
return
tax_withholding_details = get_party_tax_withholding_details(self)
+
+ if not tax_withholding_details:
+ return
+
accounts = []
for d in self.taxes:
if d.account_head == tax_withholding_details.get("account_head"):
@@ -839,6 +843,12 @@
if not accounts or tax_withholding_details.get("account_head") not in accounts:
self.append("taxes", tax_withholding_details)
+ to_remove = [d for d in self.taxes
+ if not d.tax_amount and d.account_head == tax_withholding_details.get("account_head")]
+
+ for d in to_remove:
+ self.remove(d)
+
# calculate totals again after applying TDS
self.calculate_taxes_and_totals()
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 f553cc0..6c31e9e 100644
--- a/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
+++ b/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
@@ -24,6 +24,7 @@
.format(tax_withholding_category, ref_doc.company))
tds_amount = get_tds_amount(ref_doc, tax_details, fy)
tax_row = get_tax_row(tax_details, tds_amount)
+
return tax_row
def get_tax_withholding_details(tax_withholding_category, fiscal_year, company):
@@ -62,46 +63,64 @@
def get_tds_amount(ref_doc, tax_details, fiscal_year_details):
fiscal_year, year_start_date, year_end_date = fiscal_year_details
tds_amount = 0
+ tds_deducted = 0
- def _get_tds():
- tds_amount = 0
- if not tax_details.threshold or ref_doc.net_total >= tax_details.threshold:
- tds_amount = ref_doc.net_total * tax_details.rate / 100
- return tds_amount
+ def _get_tds(amount):
+ if amount <= 0:
+ return 0
- if tax_details.cumulative_threshold:
- entries = frappe.db.sql("""
+ return amount * tax_details.rate / 100
+
+ entries = frappe.db.sql("""
select voucher_no, credit
from `tabGL Entry`
where party=%s and fiscal_year=%s and credit > 0
""", (ref_doc.supplier, fiscal_year), as_dict=1)
- supplier_credit_amount = flt(sum([d.credit for d in entries]))
+ vouchers = [d.voucher_no for d in entries]
+ advance_vouchers = get_advance_vouchers(ref_doc.supplier, fiscal_year)
- vouchers = [d.voucher_no for d in entries]
- vouchers += get_advance_vouchers(ref_doc.supplier, fiscal_year)
+ tds_vouchers = vouchers + advance_vouchers
- tds_deducted = 0
- if vouchers:
- tds_deducted = flt(frappe.db.sql("""
- select sum(credit)
- from `tabGL Entry`
- where account=%s and fiscal_year=%s and credit > 0
- and voucher_no in ({0})
- """.format(', '.join(["'%s'" % d for d in vouchers])),
- (tax_details.account_head, fiscal_year))[0][0])
+ if tds_vouchers:
+ tds_deducted = frappe.db.sql("""
+ SELECT sum(credit) FROM `tabGL Entry`
+ WHERE
+ account=%s and fiscal_year=%s and credit > 0
+ and voucher_no in ({0})""". format(','.join(['%s'] * len(tds_vouchers))),
+ ((tax_details.account_head, fiscal_year) + tuple(tds_vouchers)))
+
+ tds_deducted = tds_deducted[0][0] if tds_deducted and tds_deducted[0][0] else 0
+
+ if tds_deducted:
+ tds_amount = _get_tds(ref_doc.net_total)
+ else:
+ supplier_credit_amount = frappe.get_all('Purchase Invoice Item',
+ fields = ['sum(net_amount)'],
+ filters = {'parent': ('in', vouchers), 'docstatus': 1}, as_list=1)
+
+ supplier_credit_amount = (supplier_credit_amount[0][0]
+ if supplier_credit_amount and supplier_credit_amount[0][0] else 0)
+
+ jv_supplier_credit_amt = frappe.get_all('Journal Entry Account',
+ fields = ['sum(credit_in_account_currency)'],
+ filters = {
+ 'parent': ('in', vouchers), 'docstatus': 1,
+ 'party': ref_doc.supplier,
+ 'reference_type': ('not in', ['Purchase Invoice'])
+ }, as_list=1)
+
+ supplier_credit_amount += (jv_supplier_credit_amt[0][0]
+ if jv_supplier_credit_amt and jv_supplier_credit_amt[0][0] else 0)
+
+ supplier_credit_amount += ref_doc.net_total
debit_note_amount = get_debit_note_amount(ref_doc.supplier, year_start_date, year_end_date)
+ supplier_credit_amount -= debit_note_amount
- total_invoiced_amount = supplier_credit_amount + tds_deducted \
- + flt(ref_doc.net_total) - debit_note_amount
- if total_invoiced_amount >= tax_details.cumulative_threshold:
- total_applicable_tds = total_invoiced_amount * tax_details.rate / 100
- tds_amount = min(total_applicable_tds - tds_deducted, ref_doc.net_total)
- else:
- tds_amount = _get_tds()
- else:
- tds_amount = _get_tds()
+ if ((tax_details.get('threshold', 0) and supplier_credit_amount >= tax_details.threshold)
+ or (tax_details.get('cumulative_threshold', 0) and supplier_credit_amount >= tax_details.cumulative_threshold)):
+ tds_amount = _get_tds(supplier_credit_amount)
return tds_amount
@@ -114,7 +133,7 @@
select distinct voucher_no
from `tabGL Entry`
where party=%s and %s and debit > 0
- """, (supplier, condition))
+ """, (supplier, condition)) or []
def get_debit_note_amount(supplier, year_start_date, year_end_date, company=None):
condition = ""
@@ -126,4 +145,4 @@
from `tabPurchase Invoice`
where supplier=%s %s and is_return=1 and docstatus=1
and posting_date between %s and %s
- """, (supplier, condition, year_start_date, year_end_date)))
+ """, (supplier, condition, year_start_date, year_end_date)))
\ No newline at end of file
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 20e1746..2530196 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
@@ -6,6 +6,7 @@
import frappe
import unittest
from frappe.utils import today
+from erpnext.accounts.utils import get_fiscal_year
test_dependencies = ["Supplier Group"]
@@ -14,65 +15,105 @@
def setUpClass(self):
# create relevant supplier, etc
create_records()
+ create_tax_with_holding_category()
- def test_single_threshold_tds(self):
- frappe.db.set_value("Supplier", "Test TDS Supplier", "tax_withholding_category", "TDS - 194D - Individual")
- pi = create_purchase_invoice()
+ def test_cumulative_threshold_tds(self):
+ frappe.db.set_value("Supplier", "Test TDS Supplier", "tax_withholding_category", "Cumulative Threshold TDS")
+ invoices = []
+
+ # create invoices for lower than single threshold tax rate
+ for _ in xrange(2):
+ pi = create_purchase_invoice(supplier = "Test TDS Supplier")
+ pi.submit()
+ invoices.append(pi)
+
+ # create another invoice whose total when added to previously created invoice,
+ # surpasses cumulative threshhold
+ pi = create_purchase_invoice(supplier = "Test TDS Supplier")
pi.submit()
- self.assertEqual(pi.taxes_and_charges_deducted, 800)
- self.assertEqual(pi.grand_total, 15200)
+ # assert equal tax deduction on total invoice amount uptil now
+ self.assertEqual(pi.taxes_and_charges_deducted, 3000)
+ self.assertEqual(pi.grand_total, 7000)
+ invoices.append(pi)
+
+ # TDS is already deducted, so from onward system will deduct the TDS on every invoice
+ pi = create_purchase_invoice(supplier = "Test TDS Supplier", rate=5000)
+ pi.submit()
+
+ # assert equal tax deduction on total invoice amount uptil now
+ self.assertEqual(pi.taxes_and_charges_deducted, 500)
+ invoices.append(pi)
+
+ #delete invoices to avoid clashing
+ for d in invoices:
+ d.cancel()
+ frappe.delete_doc("Purchase Invoice", d.name)
+
+ def test_single_threshold_tds(self):
+ invoices = []
+ frappe.db.set_value("Supplier", "Test TDS Supplier1", "tax_withholding_category", "Single Threshold TDS")
+ pi = create_purchase_invoice(supplier = "Test TDS Supplier1", rate = 20000)
+ pi.submit()
+ invoices.append(pi)
+
+ self.assertEqual(pi.taxes_and_charges_deducted, 2000)
+ self.assertEqual(pi.grand_total, 18000)
# check gl entry for the purchase invoice
gl_entries = frappe.db.get_all('GL Entry', filters={'voucher_no': pi.name}, fields=["*"])
self.assertEqual(len(gl_entries), 3)
for d in gl_entries:
if d.account == pi.credit_to:
- self.assertEqual(d.credit, 15200)
+ self.assertEqual(d.credit, 18000)
elif d.account == pi.items[0].get("expense_account"):
- self.assertEqual(d.debit, 16000)
+ self.assertEqual(d.debit, 20000)
elif d.account == pi.taxes[0].get("account_head"):
- self.assertEqual(d.credit, 800)
+ self.assertEqual(d.credit, 2000)
else:
raise ValueError("Account head does not match.")
- # delete purchase invoice to avoid it interefering in other tests
- pi.cancel()
- frappe.delete_doc('Purchase Invoice', pi.name)
-
- def test_cumulative_threshold_tds(self):
- frappe.db.set_value("Supplier", "Test TDS Supplier", "tax_withholding_category", "TDS - 194C - Individual")
- invoices = []
-
- # create invoices for lower than single threshold tax rate
- for _ in xrange(6):
- pi = create_purchase_invoice()
- pi.submit()
- invoices.append(pi)
-
- # create another invoice whose total when added to previously created invoice,
- # surpasses cumulative threshhold
- pi = create_purchase_invoice()
+ pi = create_purchase_invoice(supplier = "Test TDS Supplier1")
pi.submit()
-
- # assert equal tax deduction on total invoice amount uptil now
- self.assertEqual(pi.taxes_and_charges_deducted, 1120)
- self.assertEqual(pi.grand_total, 14880)
invoices.append(pi)
+ # TDS amount is 1000 because in previous invoices it's already deducted
+ self.assertEqual(pi.taxes_and_charges_deducted, 1000)
+
# delete invoices to avoid clashing
for d in invoices:
d.cancel()
frappe.delete_doc("Purchase Invoice", d.name)
-def create_purchase_invoice(qty=1):
+ def test_single_threshold_tds_with_previous_vouchers(self):
+ invoices = []
+ frappe.db.set_value("Supplier", "Test TDS Supplier2", "tax_withholding_category", "Single Threshold TDS")
+ pi = create_purchase_invoice(supplier="Test TDS Supplier2")
+ pi.submit()
+ invoices.append(pi)
+
+ pi = create_purchase_invoice(supplier="Test TDS Supplier2")
+ pi.submit()
+ invoices.append(pi)
+
+ self.assertEqual(pi.taxes_and_charges_deducted, 2000)
+ self.assertEqual(pi.grand_total, 8000)
+
+ # delete invoices to avoid clashing
+ for d in invoices:
+ d.cancel()
+ frappe.delete_doc("Purchase Invoice", d.name)
+
+def create_purchase_invoice(**args):
# return sales invoice doc object
item = frappe.get_doc('Item', {'item_name': 'TDS Item'})
+
+ args = frappe._dict(args)
pi = frappe.get_doc({
"doctype": "Purchase Invoice",
"posting_date": today(),
"apply_tds": 1,
- "supplier": frappe.get_doc('Supplier', {"supplier_name": "Test TDS Supplier"}).name,
+ "supplier": args.supplier,
"company": '_Test Company',
"taxes_and_charges": "",
"currency": "INR",
@@ -81,8 +122,8 @@
"items": [{
'doctype': 'Purchase Invoice Item',
'item_code': item.name,
- 'qty': qty,
- 'rate': 16000,
+ 'qty': args.qty or 1,
+ 'rate': args.rate or 10000,
'cost_center': 'Main - _TC',
'expense_account': 'Stock Received But Not Billed - _TC'
}]
@@ -92,20 +133,73 @@
return pi
def create_records():
- # create a new supplier
- frappe.get_doc({
- "supplier_group": "_Test Supplier Group",
- "supplier_name": "Test TDS Supplier",
- "doctype": "Supplier",
- "tax_withholding_category": "TDS - 194D - Individual"
- }).insert()
+ # create a new suppliers
+ for name in ['Test TDS Supplier', 'Test TDS Supplier1', 'Test TDS Supplier2']:
+ if frappe.db.exists('Supplier', name):
+ continue
+
+ frappe.get_doc({
+ "supplier_group": "_Test Supplier Group",
+ "supplier_name": name,
+ "doctype": "Supplier",
+ }).insert()
# create an item
- frappe.get_doc({
- "doctype": "Item",
- "item_code": "TDS Item",
- "item_name": "TDS Item",
- "item_group": "All Item Groups",
- "company": "_Test Company",
- "is_stock_item": 0,
- }).insert()
\ No newline at end of file
+ if not frappe.db.exists('Item', "TDS Item"):
+ frappe.get_doc({
+ "doctype": "Item",
+ "item_code": "TDS Item",
+ "item_name": "TDS Item",
+ "item_group": "All Item Groups",
+ "is_stock_item": 0,
+ }).insert()
+
+ # create an account
+ if not frappe.db.exists("Account", "TDS - _TC"):
+ frappe.get_doc({
+ 'doctype': 'Account',
+ 'company': '_Test Company',
+ 'account_name': 'TDS',
+ 'parent_account': 'Tax Assets - _TC',
+ 'report_type': 'Balance Sheet',
+ 'root_type': 'Asset'
+ }).insert()
+
+def create_tax_with_holding_category():
+ fiscal_year = get_fiscal_year(today(), company="_Test Company")[0]
+
+ # Cummulative thresold
+ if not frappe.db.exists("Tax Withholding Category", "Cumulative Threshold TDS"):
+ frappe.get_doc({
+ "doctype": "Tax Withholding Category",
+ "name": "Cumulative Threshold TDS",
+ "category_name": "10% TDS",
+ "rates": [{
+ 'fiscal_year': fiscal_year,
+ 'tax_withholding_rate': 10,
+ 'single_threshold': 0,
+ 'cumulative_threshold': 30000.00
+ }],
+ "accounts": [{
+ 'company': '_Test Company',
+ 'account': 'TDS - _TC'
+ }]
+ }).insert()
+
+ # Single thresold
+ if not frappe.db.exists("Tax Withholding Category", "Single Threshold TDS"):
+ frappe.get_doc({
+ "doctype": "Tax Withholding Category",
+ "name": "Single Threshold TDS",
+ "category_name": "10% TDS",
+ "rates": [{
+ 'fiscal_year': fiscal_year,
+ 'tax_withholding_rate': 10,
+ 'single_threshold': 20000.00,
+ 'cumulative_threshold': 0
+ }],
+ "accounts": [{
+ 'company': '_Test Company',
+ 'account': 'TDS - _TC'
+ }]
+ }).insert()
\ No newline at end of file