fix: Advance TDS in TDS payable monthly report
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.js b/erpnext/accounts/doctype/payment_entry/payment_entry.js
index 3e11bc0..19d73b1 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.js
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.js
@@ -93,6 +93,15 @@
}
});
+ frm.set_query("advance_tax_account", function() {
+ return {
+ filters: {
+ "company": frm.doc.company,
+ "root_type": ["in", ["Asset", "Liability"]]
+ }
+ }
+ });
+
frm.set_query("reference_doctype", "references", function() {
if (frm.doc.party_type == "Customer") {
var doctypes = ["Sales Order", "Sales Invoice", "Journal Entry", "Dunning"];
@@ -1103,10 +1112,12 @@
current_tax_amount *= (tax.add_deduct_tax == "Deduct") ? -1.0 : 1.0;
- if(i==0) {
- tax.total = flt(frm.doc.paid_amount + current_tax_amount, precision("total", tax));
- } else {
- tax.total = flt(frm.doc["taxes"][i-1].total + current_tax_amount, precision("total", tax));
+ if (!tax.included_in_paid_amount) {
+ if(i==0) {
+ tax.total = flt(frm.doc.paid_amount + current_tax_amount, precision("total", tax));
+ } else {
+ 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;
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.json b/erpnext/accounts/doctype/payment_entry/payment_entry.json
index ec92db0..d239c41 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.json
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.json
@@ -864,7 +864,7 @@
"index_web_pages_for_search": 1,
"is_submittable": 1,
"links": [],
- "modified": "2020-11-29 20:03:57.772062",
+ "modified": "2021-02-27 13:56:20.007336",
"modified_by": "Administrator",
"module": "Accounts",
"name": "Payment Entry",
diff --git a/erpnext/accounts/doctype/payment_entry/payment_entry.py b/erpnext/accounts/doctype/payment_entry/payment_entry.py
index 251b581..33eb6f2 100644
--- a/erpnext/accounts/doctype/payment_entry/payment_entry.py
+++ b/erpnext/accounts/doctype/payment_entry/payment_entry.py
@@ -65,7 +65,7 @@
self.validate_allocated_amount()
self.validate_paid_invoices()
self.ensure_supplier_is_not_blocked()
- self.validate_advance_tax_account()
+ self.set_advance_tax_account()
self.set_status()
def on_submit(self):
@@ -311,9 +311,13 @@
+ "<br><br>" + _("If this is undesirable please cancel the corresponding Payment Entry."),
title=_("Warning"), indicator="orange")
- def validate_advance_tax_account(self):
+ def set_advance_tax_account(self):
if self.get('taxes') and not self.advance_tax_account:
- frappe.throw(_('Please select advance tax account'))
+ unrealized_profit_loss_account = frappe.db.get_value('Company', self.company, 'unrealized_profit_loss_account')
+ if not unrealized_profit_loss_account:
+ frappe.throw(_("Please select advance tax account or add Unrealized Profit / Loss Account in company master"))
+
+ self.advance_tax_account = unrealized_profit_loss_account
def validate_journal_entry(self):
for d in self.get("references"):
@@ -418,8 +422,10 @@
net_total = order_amount[0][0]
included_in_paid_amount = 1
+ # Adding args as purchase invoice to get TDS amount
args = frappe._dict({
'company': self.company,
+ 'doctype': 'Purchase Invoice',
'supplier': self.party,
'posting_date': self.posting_date,
'net_total': net_total
@@ -727,7 +733,7 @@
rev_dr_cr + "_in_account_currency": d.base_tax_amount \
if account_currency==self.company_currency \
else d.tax_amount,
- "cost_center": d.cost_center
+ "cost_center": d.cost_center or self.cost_center
}, account_currency, item=d))
def add_deductions_gl_entries(self, gl_entries):
@@ -830,12 +836,14 @@
else:
current_tax_amount *= 1.0
- if i == 0:
- tax.total = flt(self.paid_amount + current_tax_amount, self.precision("total", tax))
- else:
- tax.total = flt(self.taxes[i-1].total + current_tax_amount, self.precision("total", tax))
+ if not tax.included_in_paid_amount:
+ if i == 0:
+ tax.total = flt(self.paid_amount + current_tax_amount, self.precision("total", tax))
+ else:
+ tax.total = flt(self.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 * self.source_exchange_rate
+
self.total_taxes_and_charges += current_tax_amount
self.base_total_taxes_and_charges += current_tax_amount * self.source_exchange_rate
diff --git a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
index 8f7248f..5d4796c 100644
--- a/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
+++ b/erpnext/accounts/doctype/purchase_invoice/purchase_invoice.py
@@ -935,7 +935,7 @@
rev_dr_cr + "_in_account_currency": unallocated_amount \
if account_currency==self.company_currency \
else unallocated_amount,
- 'cost_center': tax.cost_center
+ 'cost_center': tax.cost_center or self.cost_center
}, account_currency, item=tax))
frappe.db.set_value('Advance Taxes and Charges', tax.name, 'allocated_amount', tax.allocated_amount + unallocated_amount)
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 5c1cbaa..b9ee4a0 100644
--- a/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
+++ b/erpnext/accounts/doctype/tax_withholding_category/tax_withholding_category.py
@@ -49,7 +49,7 @@
if not parties:
parties.append(party)
- fiscal_year = get_fiscal_year(inv.posting_date, company=inv.company)
+ fiscal_year = get_fiscal_year(inv.get('posting_date') or inv.get('transaction_date'), company=inv.company)
tax_details = get_tax_withholding_details(tax_withholding_category, fiscal_year[0], inv.company)
if not tax_details:
@@ -154,7 +154,7 @@
tax_deducted = get_deducted_tax(taxable_vouchers, fiscal_year, tax_details)
tax_amount = 0
- posting_date = inv.posting_date
+ posting_date = inv.get('posting_date') or inv.get('transaction_date')
if party_type == 'Supplier':
ldc = get_lower_deduction_certificate(fiscal_year, pan_no)
if tax_deducted:
@@ -257,7 +257,7 @@
if ((threshold and inv.net_total >= threshold) or (cumulative_threshold and supp_credit_amt >= cumulative_threshold)):
if ldc and is_valid_certificate(
ldc.valid_from, ldc.valid_upto,
- inv.posting_date, tax_deducted,
+ inv.get('posting_date') or inv.get('transaction_date'), tax_deducted,
inv.net_total, ldc.certificate_limit
):
tds_amount = get_ltds_amount(supp_credit_amt, 0, ldc.certificate_limit, ldc.rate, tax_details)
diff --git a/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.js b/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.js
index 344539e..011c296 100644
--- a/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.js
+++ b/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.js
@@ -55,6 +55,32 @@
}
},
{
+ "fieldname":"purchase_order",
+ "label": __("Purchase Order"),
+ "fieldtype": "Link",
+ "options": "Purchase Order",
+ "get_query": function() {
+ return {
+ "filters": {
+ "name": ["in", frappe.query_report.invoices]
+ }
+ }
+ },
+ on_change: function() {
+ let supplier = frappe.query_report.get_filter_value('supplier');
+ if(!supplier) return; // return if no supplier selected
+
+ // filter order based on selected supplier
+ let orders = [];
+ frappe.query_report.order_data.map(d => {
+ if(d.supplier==supplier)
+ orders.push(d.name)
+ });
+ frappe.query_report.orders = orders;
+ frappe.query_report.refresh();
+ }
+ },
+ {
"fieldname":"from_date",
"label": __("From Date"),
"fieldtype": "Date",
@@ -75,15 +101,24 @@
onload: function(report) {
// fetch all tds applied invoices
frappe.call({
- "method": "erpnext.accounts.report.tds_payable_monthly.tds_payable_monthly.get_tds_invoices",
+ "method": "erpnext.accounts.report.tds_payable_monthly.tds_payable_monthly.get_tds_invoices_and_orders",
callback: function(r) {
let invoices = [];
- r.message.map(d => {
+ let orders = [];
+
+ r.message.invoices.map(d => {
invoices.push(d.name);
});
- report["invoice_data"] = r.message;
+ r.message.orders.map(d => {
+ orders.push(d.name);
+ });
+
+ report["invoice_data"] = r.message.invoices;
report["invoices"] = invoices;
+
+ report["order_data"] = r.message.orders;
+ report["invoices"] = orders;
}
});
}
diff --git a/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.py b/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.py
index a9fb237..7ce61f4 100644
--- a/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.py
+++ b/erpnext/accounts/report/tds_payable_monthly/tds_payable_monthly.py
@@ -8,14 +8,18 @@
def execute(filters=None):
filters["invoices"] = frappe.cache().hget("invoices", frappe.session.user)
+ filters["orders"] = frappe.cache().hget("orders", frappe.session.user)
validate_filters(filters)
set_filters(filters)
+ # TDS payment entries
+ payment_entries = get_payment_entires(filters)
+
columns = get_columns(filters)
- if not filters["invoices"]:
+ if not (filters.get("invoices") and filters.get('orders')):
return columns, []
- res = get_result(filters)
+ res = get_result(filters, payment_entries)
return columns, res
@@ -26,9 +30,14 @@
def set_filters(filters):
invoices = []
+ orders = []
- if not filters["invoices"]:
- filters["invoices"] = get_tds_invoices()
+ if not filters.get("invoices"):
+ filters["invoices"] = get_tds_invoices_and_orders()['invoices']
+
+ if not filters.get("orders"):
+ filters["orders"] = get_tds_invoices_and_orders()['orders']
+
if filters.supplier and filters.purchase_invoice:
for d in filters["invoices"]:
if d.name == filters.purchase_invoice and d.supplier == filters.supplier:
@@ -42,12 +51,28 @@
if d.name == filters.purchase_invoice:
invoices.append(d)
+ if filters.supplier and filters.purchase_order:
+ for d in filters.get("orders"):
+ if d.name == filters.purchase_order and d.supplier == filters.supplier:
+ orders.append(d)
+ elif filters.supplier and not filters.purchase_order:
+ for d in filters.get("orders"):
+ if d.supplier == filters.supplier:
+ orders.append(d)
+ elif filters.purchase_order and not filters.supplier:
+ for d in filters.get("invoices"):
+ if d.name == filters.purchase_order:
+ orders.append(d)
+
filters["invoices"] = invoices if invoices else filters["invoices"]
+ filters["orders"] = orders if orders else filters["orders"]
filters.naming_series = frappe.db.get_single_value('Buying Settings', 'supp_master_name')
-def get_result(filters):
- supplier_map, tds_docs = get_supplier_map(filters)
- gle_map = get_gle_map(filters)
+def get_result(filters, payment_entries):
+ supplier_map, tds_docs = get_supplier_map(filters, payment_entries)
+ documents = [d.get('name') for d in filters.get('invoices')] + [d.get('name') for d in payment_entries]
+
+ gle_map = get_gle_map(filters, documents)
out = []
for d in gle_map:
@@ -62,10 +87,11 @@
for k in gle_map[d]:
if k.party == supplier_map[d] and k.credit > 0:
- total_amount_credited += k.credit
- elif account_list and k.account == account and k.credit > 0:
- tds_deducted = k.credit
- total_amount_credited += k.credit
+ total_amount_credited += (k.credit - k.debit)
+ elif account_list and k.account == account and (k.credit - k.debit) > 0:
+ tds_deducted = (k.credit - k.debit)
+ total_amount_credited += (k.credit - k.debit)
+ voucher_type = k.voucher_type
rate = [i.tax_withholding_rate for i in tds_doc.rates
if i.fiscal_year == gle_map[d][0].fiscal_year]
@@ -73,32 +99,40 @@
if rate and len(rate) > 0 and tds_deducted:
rate = rate[0]
- if getdate(filters.from_date) <= gle_map[d][0].posting_date \
- and getdate(filters.to_date) >= gle_map[d][0].posting_date:
- row = [supplier.pan, supplier.name]
+ row = [supplier.pan, supplier.name]
- if filters.naming_series == 'Naming Series':
- row.append(supplier.supplier_name)
+ if filters.naming_series == 'Naming Series':
+ row.append(supplier.supplier_name)
- row.extend([tds_doc.name, supplier.supplier_type, rate, total_amount_credited,
- tds_deducted, gle_map[d][0].posting_date, "Purchase Invoice", d])
- out.append(row)
+ row.extend([tds_doc.name, supplier.supplier_type, rate, total_amount_credited,
+ tds_deducted, gle_map[d][0].posting_date, voucher_type, d])
+ out.append(row)
return out
-def get_supplier_map(filters):
+def get_supplier_map(filters, payment_entries):
# create a supplier_map of the form {"purchase_invoice": {supplier_name, pan, tds_name}}
# pre-fetch all distinct applicable tds docs
supplier_map, tds_docs = {}, {}
pan = "pan" if frappe.db.has_column("Supplier", "pan") else "tax_id"
+ supplier_list = [d.supplier for d in filters["invoices"]] + [d.supplier for d in filters["orders"]]
+
supplier_detail = frappe.db.get_all('Supplier',
- {"name": ["in", [d.supplier for d in filters["invoices"]]]},
+ {"name": ["in", supplier_list]},
["tax_withholding_category", "name", pan+" as pan", "supplier_type", "supplier_name"])
for d in filters["invoices"]:
supplier_map[d.get("name")] = [k for k in supplier_detail
if k.name == d.get("supplier")][0]
+ for d in filters["orders"]:
+ supplier_map[d.get("name")] = [k for k in supplier_detail
+ if k.name == d.get("supplier")][0]
+
+ for d in payment_entries:
+ supplier_map[d.get("name")] = [k for k in supplier_detail
+ if k.name == d.get("supplier")][0]
+
for d in supplier_detail:
if d.get("tax_withholding_category") not in tds_docs:
tds_docs[d.get("tax_withholding_category")] = \
@@ -106,13 +140,19 @@
return supplier_map, tds_docs
-def get_gle_map(filters):
+def get_gle_map(filters, documents):
# create gle_map of the form
# {"purchase_invoice": list of dict of all gle created for this invoice}
gle_map = {}
- gle = frappe.db.get_all('GL Entry',\
- {"voucher_no": ["in", [d.get("name") for d in filters["invoices"]]], 'is_cancelled': 0},
- ["fiscal_year", "credit", "debit", "account", "voucher_no", "posting_date"])
+ filter_obj = {}
+ gle = frappe.db.get_all('GL Entry',
+ {
+ "voucher_no": ["in", documents],
+ 'is_cancelled': 0,
+ 'posting_date': ("between", [filters.get('from_date'), filters.get('to_date')]),
+ },
+ ["fiscal_year", "credit", "debit", "account", "voucher_no", "posting_date", "voucher_type"],
+ )
for d in gle:
if not d.voucher_no in gle_map:
@@ -201,8 +241,28 @@
return columns
+def get_payment_entires(filters):
+ filter_dict = {
+ 'posting_date': ("between", [filters.get('from_date'), filters.get('to_date')]),
+ 'party_type': 'Supplier',
+ 'apply_tax_withholding_amount': 1
+ }
+
+ if filters.get('purchase_order') or filters.get('purchase_invoice'):
+ parent = frappe.db.get_all('Payment Entry Reference',
+ {
+ 'reference_name': ('in', [d.get('name') for d in filters.get('orders')] +
+ [d.get('name') for d in filters.get('invoices')])
+ }, ['parent'])
+ filter_dict.update({'name': ('in', [d.get('parent') for d in parent])})
+
+ payment_entries = frappe.get_all('Payment Entry', fields=['name', 'party_name as supplier'],
+ filters=filter_dict)
+
+ return payment_entries
+
@frappe.whitelist()
-def get_tds_invoices():
+def get_tds_invoices_and_orders():
# fetch tds applicable supplier and fetch invoices for these suppliers
suppliers = [d.name for d in frappe.db.get_list("Supplier",
{"tax_withholding_category": ["!=", ""]}, ["name"])]
@@ -210,7 +270,16 @@
invoices = frappe.db.get_list("Purchase Invoice",
{"supplier": ["in", suppliers]}, ["name", "supplier"])
- invoices = [d for d in invoices if d.supplier]
- frappe.cache().hset("invoices", frappe.session.user, invoices)
+ orders = frappe.db.get_list("Purchase Order",
+ {"supplier": ["in", suppliers]}, ["name", "supplier"])
- return invoices
+ invoices = [d for d in invoices if d.supplier]
+ orders = [d for d in orders if d.supplier]
+
+ frappe.cache().hset("invoices", frappe.session.user, invoices)
+ frappe.cache().hset("orders", frappe.session.user, invoices)
+
+ return {
+ 'invoices': invoices,
+ 'orders': orders
+ }
diff --git a/erpnext/buying/doctype/purchase_order/purchase_order.json b/erpnext/buying/doctype/purchase_order/purchase_order.json
index 64eda5f..47ec212 100644
--- a/erpnext/buying/doctype/purchase_order/purchase_order.json
+++ b/erpnext/buying/doctype/purchase_order/purchase_order.json
@@ -1375,7 +1375,7 @@
"idx": 105,
"is_submittable": 1,
"links": [],
- "modified": "2020-11-28 17:42:25.177827",
+ "modified": "2021-02-27 22:07:23.487138",
"modified_by": "Administrator",
"module": "Buying",
"name": "Purchase Order",
diff --git a/erpnext/setup/doctype/company/company.js b/erpnext/setup/doctype/company/company.js
index 9957aad..9dc7fc4 100644
--- a/erpnext/setup/doctype/company/company.js
+++ b/erpnext/setup/doctype/company/company.js
@@ -276,7 +276,7 @@
["expenses_included_in_asset_valuation", {"account_type": "Expenses Included In Asset Valuation"}],
["capital_work_in_progress_account", {"account_type": "Capital Work in Progress"}],
["asset_received_but_not_billed", {"account_type": "Asset Received But Not Billed"}],
- ["unrealized_profit_loss_account", {"root_type": "Liability"},]
+ ["unrealized_profit_loss_account", {"root_type": ["in", ["Liability", "Asset"]]}]
], function(i, v) {
erpnext.company.set_custom_query(frm, v);
});