feat: Allow tax withholding category selection at invoice level (#20870)
* feat: Allow tax withholding category selection at invoice level
* fix: Linitng fixes
* feat: TDS calculation using common PAN
* fix: Add provision to deduct Lower TDS in purchase invoice
* fix: Consider only ref docs company while computing TDS
* fix: Default permission fixes
* fix: Add validation for dates in fiscal year
* fix: Undefined variable
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js
index 9292b63..3cf4d59 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.js
@@ -261,12 +261,25 @@
price_list: this.frm.doc.buying_price_list
}, function() {
me.apply_pricing_rule();
-
me.frm.doc.apply_tds = me.frm.supplier_tds ? 1 : 0;
+ me.frm.doc.tax_withholding_category = me.frm.supplier_tds;
me.frm.set_df_property("apply_tds", "read_only", me.frm.supplier_tds ? 0 : 1);
+ me.frm.set_df_property("tax_withholding_category", "hidden", me.frm.supplier_tds ? 0 : 1);
})
},
+ apply_tds: function(frm) {
+ var me = this;
+
+ if (!me.frm.doc.apply_tds) {
+ me.frm.set_value("tax_withholding_category", '');
+ me.frm.set_df_property("tax_withholding_category", "hidden", 1);
+ } else {
+ me.frm.set_value("tax_withholding_category", me.frm.supplier_tds);
+ me.frm.set_df_property("tax_withholding_category", "hidden", 0);
+ }
+ },
+
credit_to: function() {
var me = this;
if(this.frm.doc.credit_to) {
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.json b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.json
index 0e09454..98ba5c7 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.json
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.json
@@ -13,6 +13,7 @@
"supplier_name",
"tax_id",
"due_date",
+ "tax_withholding_category",
"column_break1",
"company",
"posting_date",
@@ -1294,13 +1295,21 @@
"fieldtype": "Check",
"label": "Is Internal Supplier",
"read_only": 1
+ },
+ {
+ "fieldname": "tax_withholding_category",
+ "fieldtype": "Link",
+ "hidden": 1,
+ "label": "Tax Withholding Category",
+ "options": "Tax Withholding Category",
+ "print_hide": 1
}
],
"icon": "fa fa-file-text",
"idx": 204,
"is_submittable": 1,
"links": [],
- "modified": "2020-04-17 13:05:25.199832",
+ "modified": "2020-04-18 13:05:25.199832",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Purchase Invoice",
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
index 0283d30..b1ae194 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
@@ -1002,7 +1002,7 @@
if not self.apply_tds:
return
- tax_withholding_details = get_party_tax_withholding_details(self)
+ tax_withholding_details = get_party_tax_withholding_details(self, self.tax_withholding_category)
if not tax_withholding_details:
return
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 6c31e9e..dd6b4fd 100644
--- a/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
+++ b/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
@@ -6,23 +6,42 @@
import frappe
from frappe import _
from frappe.model.document import Document
-from frappe.utils import flt
+from frappe.utils import flt, getdate
from erpnext.accounts.utils import get_fiscal_year
class TaxWithholdingCategory(Document):
pass
-def get_party_tax_withholding_details(ref_doc):
- tax_withholding_category = frappe.db.get_value('Supplier', ref_doc.supplier, 'tax_withholding_category')
+def get_party_tax_withholding_details(ref_doc, tax_withholding_category=None):
+
+ pan_no = ''
+ suppliers = []
+
+ if not tax_withholding_category:
+ tax_withholding_category, pan_no = frappe.db.get_value('Supplier', ref_doc.supplier, ['tax_withholding_category', 'pan'])
+
if not tax_withholding_category:
return
+ if not pan_no:
+ pan_no = frappe.db.get_value('Supplier', ref_doc.supplier, 'pan')
+
+ # Get others suppliers with the same PAN No
+ if pan_no:
+ suppliers = [d.name for d in frappe.get_all('Supplier', fields=['name'], filters={'pan': pan_no})]
+
+ if not suppliers:
+ suppliers.append(ref_doc.supplier)
+
fy = get_fiscal_year(ref_doc.posting_date, company=ref_doc.company)
tax_details = get_tax_withholding_details(tax_withholding_category, fy[0], ref_doc.company)
if not tax_details:
frappe.throw(_('Please set associated account in Tax Withholding Category {0} against Company {1}')
.format(tax_withholding_category, ref_doc.company))
- tds_amount = get_tds_amount(ref_doc, tax_details, fy)
+
+ tds_amount = get_tds_amount(suppliers, ref_doc.net_total, ref_doc.company,
+ tax_details, fy, ref_doc.posting_date, pan_no)
+
tax_row = get_tax_row(tax_details, tds_amount)
return tax_row
@@ -51,6 +70,7 @@
frappe.throw(_("No Tax Withholding data found for the current Fiscal Year."))
def get_tax_row(tax_details, tds_amount):
+
return {
"category": "Total",
"add_deduct_tax": "Deduct",
@@ -60,25 +80,36 @@
"tax_amount": tds_amount
}
-def get_tds_amount(ref_doc, tax_details, fiscal_year_details):
+def get_tds_amount(suppliers, net_total, company, tax_details, fiscal_year_details, posting_date, pan_no=None):
fiscal_year, year_start_date, year_end_date = fiscal_year_details
tds_amount = 0
tds_deducted = 0
- def _get_tds(amount):
+ def _get_tds(amount, rate):
if amount <= 0:
return 0
- return amount * tax_details.rate / 100
+ return amount * rate / 100
+
+ ldc_name = frappe.db.get_value('Lower Deduction Certificate',
+ {
+ 'pan_no': pan_no,
+ 'fiscal_year': fiscal_year
+ }, 'name')
+ ldc = ''
+
+ if ldc_name:
+ ldc = frappe.get_doc('Lower Deduction Certificate', ldc_name)
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)
+ where company = %s and
+ party in %s and fiscal_year=%s and credit > 0
+ """, (company, tuple(suppliers), fiscal_year), as_dict=1)
vouchers = [d.voucher_no for d in entries]
- advance_vouchers = get_advance_vouchers(ref_doc.supplier, fiscal_year)
+ advance_vouchers = get_advance_vouchers(suppliers, fiscal_year=fiscal_year, company=company)
tds_vouchers = vouchers + advance_vouchers
@@ -93,7 +124,20 @@
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)
+ if ldc:
+ limit_consumed = frappe.db.get_value('Purchase Invoice',
+ {
+ 'supplier': ('in', suppliers),
+ 'apply_tds': 1,
+ 'docstatus': 1
+ }, 'sum(net_total)')
+
+ if ldc and is_valid_certificate(ldc.valid_from, ldc.valid_upto, posting_date, limit_consumed, net_total,
+ ldc.certificate_limit):
+
+ tds_amount = get_ltds_amount(net_total, limit_consumed, ldc.certificate_limit, ldc.rate, tax_details)
+ else:
+ tds_amount = _get_tds(net_total, tax_details.rate)
else:
supplier_credit_amount = frappe.get_all('Purchase Invoice Item',
fields = ['sum(net_amount)'],
@@ -106,43 +150,79 @@
fields = ['sum(credit_in_account_currency)'],
filters = {
'parent': ('in', vouchers), 'docstatus': 1,
- 'party': ref_doc.supplier,
+ 'party': ('in', suppliers),
'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
+ supplier_credit_amount += net_total
- debit_note_amount = get_debit_note_amount(ref_doc.supplier, year_start_date, year_end_date)
+ debit_note_amount = get_debit_note_amount(suppliers, year_start_date, year_end_date)
supplier_credit_amount -= debit_note_amount
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)
+
+ if ldc and is_valid_certificate(ldc.valid_from, ldc.valid_upto, posting_date, tds_deducted, net_total,
+ ldc.certificate_limit):
+ tds_amount = get_ltds_amount(supplier_credit_amount, 0, ldc.certificate_limit, ldc.rate,
+ tax_details)
+ else:
+ tds_amount = _get_tds(supplier_credit_amount, tax_details.rate)
return tds_amount
-def get_advance_vouchers(supplier, fiscal_year=None, company=None, from_date=None, to_date=None):
+def get_advance_vouchers(suppliers, fiscal_year=None, company=None, from_date=None, to_date=None):
condition = "fiscal_year=%s" % fiscal_year
+
+ if company:
+ condition += "and company =%s" % (company)
if from_date and to_date:
- condition = "company=%s and posting_date between %s and %s" % (company, from_date, to_date)
+ condition += "and posting_date between %s and %s" % (company, from_date, to_date)
+
+ ## Appending the same supplier again if length of suppliers list is 1
+ ## since tuple of single element list contains None, For example ('Test Supplier 1', )
+ ## and the below query fails
+ if len(suppliers) == 1:
+ suppliers.append(suppliers[0])
return frappe.db.sql_list("""
select distinct voucher_no
from `tabGL Entry`
- where party=%s and %s and debit > 0
- """, (supplier, condition)) or []
+ where party in %s and %s and debit > 0
+ """, (tuple(suppliers), condition)) or []
-def get_debit_note_amount(supplier, year_start_date, year_end_date, company=None):
- condition = ""
+def get_debit_note_amount(suppliers, year_start_date, year_end_date, company=None):
+ condition = "and 1=1"
if company:
condition = " and company=%s " % company
+ if len(suppliers) == 1:
+ suppliers.append(suppliers[0])
+
return flt(frappe.db.sql("""
select abs(sum(net_total))
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)))
\ No newline at end of file
+ where supplier in %s and is_return=1 and docstatus=1
+ and posting_date between %s and %s %s
+ """, (tuple(suppliers), year_start_date, year_end_date, condition)))
+
+def get_ltds_amount(current_amount, deducted_amount, certificate_limit, rate, tax_details):
+ if current_amount < (certificate_limit - deducted_amount):
+ return current_amount * rate/100
+ else:
+ ltds_amount = (certificate_limit - deducted_amount)
+ tds_amount = current_amount - ltds_amount
+
+ return ltds_amount * rate/100 + tds_amount * tax_details.rate/100
+
+def is_valid_certificate(valid_from, valid_upto, posting_date, deducted_amount, current_amount, certificate_limit):
+ valid = False
+
+ if ((getdate(valid_from) <= getdate(posting_date) <= getdate(valid_upto)) and
+ certificate_limit > deducted_amount):
+ valid = True
+
+ return valid
\ No newline at end of file
diff --git a/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.py b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.py
index 2e805f8..c7cfee7 100644
--- a/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.py
+++ b/erpnext/accounts/report/tds_computation_summary/tds_computation_summary.py
@@ -44,9 +44,14 @@
out = []
for supplier in filters.supplier:
tds = frappe.get_doc("Tax Withholding Category", supplier.tax_withholding_category)
- rate = [d.tax_withholding_rate for d in tds.rates if d.fiscal_year == filters.fiscal_year][0]
+ rate = [d.tax_withholding_rate for d in tds.rates if d.fiscal_year == filters.fiscal_year]
+
+ if rate:
+ rate = rate[0]
+
try:
account = [d.account for d in tds.accounts if d.company == filters.company][0]
+
except IndexError:
account = []
total_invoiced_amount, tds_deducted = get_invoice_and_tds_amount(supplier.name, account,
@@ -76,7 +81,7 @@
supplier_credit_amount = flt(sum([d.credit for d in entries]))
vouchers = [d.voucher_no for d in entries]
- vouchers += get_advance_vouchers(supplier, company=company,
+ vouchers += get_advance_vouchers([supplier], company=company,
from_date=from_date, to_date=to_date)
tds_deducted = 0
@@ -89,7 +94,7 @@
""".format(', '.join(["'%s'" % d for d in vouchers])),
(account, from_date, to_date, company))[0][0])
- debit_note_amount = get_debit_note_amount(supplier, from_date, to_date, company=company)
+ debit_note_amount = get_debit_note_amount([supplier], from_date, to_date, company=company)
total_invoiced_amount = supplier_credit_amount + tds_deducted - debit_note_amount
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index a73e8c1..801d583 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -661,6 +661,7 @@
erpnext.patches.v12_0.create_irs_1099_field_united_states
erpnext.patches.v12_0.move_bank_account_swift_number_to_bank
erpnext.patches.v12_0.rename_bank_reconciliation_fields # 2020-01-22
+erpnext.patches.v12_0.add_permission_in_lower_deduction
erpnext.patches.v12_0.set_received_qty_in_material_request_as_per_stock_uom
erpnext.patches.v12_0.rename_account_type_doctype
erpnext.patches.v12_0.recalculate_requested_qty_in_bin
diff --git a/erpnext/patches/v12_0/add_permission_in_lower_deduction.py b/erpnext/patches/v12_0/add_permission_in_lower_deduction.py
new file mode 100644
index 0000000..af9bf74
--- /dev/null
+++ b/erpnext/patches/v12_0/add_permission_in_lower_deduction.py
@@ -0,0 +1,13 @@
+import frappe
+from frappe.permissions import add_permission, update_permission_property
+
+def execute():
+ company = frappe.get_all('Company', filters = {'country': 'India'})
+ if not company:
+ return
+
+ frappe.reload_doc('regional', 'doctype', 'Lower Deduction Certificate')
+
+ add_permission('Lower Deduction Certificate', 'Accounts Manager', 0)
+ update_permission_property('Lower Deduction Certificate', 'Accounts Manager', 0, 'write', 1)
+ update_permission_property('Lower Deduction Certificate', 'Accounts Manager', 0, 'create', 1)
\ No newline at end of file
diff --git a/erpnext/regional/doctype/lower_deduction_certificate/__init__.py b/erpnext/regional/doctype/lower_deduction_certificate/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/regional/doctype/lower_deduction_certificate/__init__.py
diff --git a/erpnext/regional/doctype/lower_deduction_certificate/lower_deduction_certificate.js b/erpnext/regional/doctype/lower_deduction_certificate/lower_deduction_certificate.js
new file mode 100644
index 0000000..8257bf8
--- /dev/null
+++ b/erpnext/regional/doctype/lower_deduction_certificate/lower_deduction_certificate.js
@@ -0,0 +1,8 @@
+// Copyright (c) 2020, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+
+frappe.ui.form.on('Lower Deduction Certificate', {
+ // refresh: function(frm) {
+
+ // }
+});
diff --git a/erpnext/regional/doctype/lower_deduction_certificate/lower_deduction_certificate.json b/erpnext/regional/doctype/lower_deduction_certificate/lower_deduction_certificate.json
new file mode 100644
index 0000000..f48fe6f
--- /dev/null
+++ b/erpnext/regional/doctype/lower_deduction_certificate/lower_deduction_certificate.json
@@ -0,0 +1,138 @@
+{
+ "actions": [],
+ "autoname": "field:certificate_no",
+ "creation": "2020-03-10 23:12:10.072631",
+ "doctype": "DocType",
+ "editable_grid": 1,
+ "engine": "InnoDB",
+ "field_order": [
+ "certificate_details_section",
+ "section_code",
+ "fiscal_year",
+ "column_break_3",
+ "certificate_no",
+ "section_break_3",
+ "supplier",
+ "column_break_7",
+ "pan_no",
+ "validity_details_section",
+ "valid_from",
+ "column_break_10",
+ "valid_upto",
+ "section_break_9",
+ "rate",
+ "column_break_14",
+ "certificate_limit"
+ ],
+ "fields": [
+ {
+ "fieldname": "certificate_no",
+ "fieldtype": "Data",
+ "in_list_view": 1,
+ "label": "Certificate No",
+ "reqd": 1,
+ "unique": 1
+ },
+ {
+ "fieldname": "section_code",
+ "fieldtype": "Select",
+ "label": "Section Code",
+ "options": "192\n193\n194\n194A\n194C\n194D\n194H\n194I\n194J\n194LA\n194LBB\n194LBC\n195",
+ "reqd": 1
+ },
+ {
+ "fieldname": "section_break_3",
+ "fieldtype": "Section Break",
+ "label": "Deductee Details"
+ },
+ {
+ "fieldname": "supplier",
+ "fieldtype": "Link",
+ "in_list_view": 1,
+ "label": "Supplier",
+ "options": "Supplier",
+ "reqd": 1
+ },
+ {
+ "fetch_from": "supplier.pan",
+ "fetch_if_empty": 1,
+ "fieldname": "pan_no",
+ "fieldtype": "Data",
+ "in_list_view": 1,
+ "label": "PAN No",
+ "reqd": 1
+ },
+ {
+ "fieldname": "validity_details_section",
+ "fieldtype": "Section Break",
+ "label": "Validity Details"
+ },
+ {
+ "fieldname": "valid_upto",
+ "fieldtype": "Date",
+ "label": "Valid Upto",
+ "reqd": 1
+ },
+ {
+ "fieldname": "section_break_9",
+ "fieldtype": "Section Break"
+ },
+ {
+ "fieldname": "rate",
+ "fieldtype": "Percent",
+ "label": "Rate Of TDS As Per Certificate",
+ "reqd": 1
+ },
+ {
+ "fieldname": "certificate_limit",
+ "fieldtype": "Currency",
+ "label": "Certificate Limit",
+ "reqd": 1
+ },
+ {
+ "fieldname": "certificate_details_section",
+ "fieldtype": "Section Break",
+ "label": "Certificate Details"
+ },
+ {
+ "fieldname": "column_break_3",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "column_break_10",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "column_break_14",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "column_break_7",
+ "fieldtype": "Column Break"
+ },
+ {
+ "fieldname": "valid_from",
+ "fieldtype": "Date",
+ "in_list_view": 1,
+ "label": "Valid From",
+ "reqd": 1
+ },
+ {
+ "fieldname": "fiscal_year",
+ "fieldtype": "Link",
+ "label": "Fiscal Year",
+ "options": "Fiscal Year",
+ "reqd": 1
+ }
+ ],
+ "links": [],
+ "modified": "2020-04-23 23:04:41.203721",
+ "modified_by": "Administrator",
+ "module": "Regional",
+ "name": "Lower Deduction Certificate",
+ "owner": "Administrator",
+ "permissions": [],
+ "sort_field": "modified",
+ "sort_order": "DESC",
+ "track_changes": 1
+}
\ No newline at end of file
diff --git a/erpnext/regional/doctype/lower_deduction_certificate/lower_deduction_certificate.py b/erpnext/regional/doctype/lower_deduction_certificate/lower_deduction_certificate.py
new file mode 100644
index 0000000..e8a8ed8
--- /dev/null
+++ b/erpnext/regional/doctype/lower_deduction_certificate/lower_deduction_certificate.py
@@ -0,0 +1,26 @@
+# -*- coding: utf-8 -*-
+# Copyright (c) 2020, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import _
+from frappe.utils import getdate
+from frappe.model.document import Document
+from erpnext.accounts.utils import get_fiscal_year
+
+class LowerDeductionCertificate(Document):
+ def validate(self):
+ if getdate(self.valid_upto) < getdate(self.valid_from):
+ frappe.throw(_("Valid Upto date cannot be before Valid From date"))
+
+ fiscal_year = get_fiscal_year(fiscal_year=self.fiscal_year, as_dict=True)
+
+ if not (fiscal_year.year_start_date <= getdate(self.valid_from) \
+ <= fiscal_year.year_end_date):
+ frappe.throw(_("Valid From date not in Fiscal Year {0}").format(frappe.bold(self.fiscal_year)))
+
+ if not (fiscal_year.year_start_date <= getdate(self.valid_upto) \
+ <= fiscal_year.year_end_date):
+ frappe.throw(_("Valid Upto date not in Fiscal Year {0}").format(frappe.bold(self.fiscal_year)))
+
diff --git a/erpnext/regional/doctype/lower_deduction_certificate/test_lower_deduction_certificate.py b/erpnext/regional/doctype/lower_deduction_certificate/test_lower_deduction_certificate.py
new file mode 100644
index 0000000..7e95020
--- /dev/null
+++ b/erpnext/regional/doctype/lower_deduction_certificate/test_lower_deduction_certificate.py
@@ -0,0 +1,10 @@
+# -*- coding: utf-8 -*-
+# Copyright (c) 2020, Frappe Technologies Pvt. Ltd. and Contributors
+# See license.txt
+from __future__ import unicode_literals
+
+# import frappe
+import unittest
+
+class TestLowerDeductionCertificate(unittest.TestCase):
+ pass
diff --git a/erpnext/regional/india/setup.py b/erpnext/regional/india/setup.py
index b4e3558..8593966 100644
--- a/erpnext/regional/india/setup.py
+++ b/erpnext/regional/india/setup.py
@@ -61,7 +61,7 @@
)).insert()
def add_permissions():
- for doctype in ('GST HSN Code', 'GST Settings', 'GSTR 3B Report'):
+ for doctype in ('GST HSN Code', 'GST Settings', 'GSTR 3B Report', 'Lower Deduction Certificate'):
add_permission(doctype, 'All', 0)
for role in ('Accounts Manager', 'Accounts User', 'System Manager'):
add_permission(doctype, role, 0)