Try ERPNext Buy Support Partners Foundation

Loan termination

Hi community,

I have a challenge that am working on.

Sometimes employees who are on loans want to pay off the loan in two ways at once in two senarios
a. He brings physical cash to pay the outstanding loan so that the loan will stop running.
b. He can ask hr to deduct the remaining outstanding balance from his salary so in this case when salary runs instead of deducting based on the equal monthly installment of say 100 it will deduct the whole oustanding balance of 300 (3 months EMI)

This is what i have done so far.

  1. I added the following fields before amended_from field to the loan.json

{
“allow_bulk_edit”: 0,
“allow_in_quick_entry”: 0,
“allow_on_submit”: 0,
“bold”: 0,
“collapsible”: 1,
“columns”: 0,
“fetch_if_empty”: 0,
“fieldname”: “loan_termination_info”,
“fieldtype”: “Section Break”,
“hidden”: 0,
“ignore_user_permissions”: 0,
“ignore_xss_filter”: 0,
“in_filter”: 0,
“in_global_search”: 0,
“in_list_view”: 0,
“in_standard_filter”: 0,
“label”: “Loan Termination Info”,
“length”: 0,
“no_copy”: 0,
“permlevel”: 0,
“precision”: “”,
“print_hide”: 0,
“print_hide_if_no_value”: 0,
“read_only”: 0,
“remember_last_selected_value”: 0,
“report_hide”: 0,
“reqd”: 0,
“search_index”: 0,
“set_only_once”: 0,
“translatable”: 0,
“unique”: 0
},
{
“allow_bulk_edit”: 0,
“allow_in_quick_entry”: 0,
“allow_on_submit”: 1,
“bold”: 0,
“collapsible”: 0,
“columns”: 0,
“default”: “”,
“depends_on”: “eval:doc.status==“Disbursed””,
“fetch_if_empty”: 0,
“fieldname”: “terminate_loan”,
“fieldtype”: “Check”,
“hidden”: 0,
“ignore_user_permissions”: 0,
“ignore_xss_filter”: 0,
“in_filter”: 0,
“in_global_search”: 0,
“in_list_view”: 0,
“in_standard_filter”: 0,
“label”: “Terminate Loan”,
“length”: 0,
“no_copy”: 0,
“permlevel”: 0,
“precision”: “”,
“print_hide”: 0,
“print_hide_if_no_value”: 0,
“read_only”: 0,
“remember_last_selected_value”: 0,
“report_hide”: 0,
“reqd”: 0,
“search_index”: 0,
“set_only_once”: 0,
“translatable”: 0,
“unique”: 0
},
{
“allow_bulk_edit”: 0,
“allow_in_quick_entry”: 0,
“allow_on_submit”: 1,
“bold”: 0,
“collapsible”: 0,
“columns”: 0,
“fetch_if_empty”: 0,
“depends_on”: “eval:doc.status==“Terminate Loan””,
“fieldname”: “termination_amount”,
“fieldtype”: “Currency”,
“hidden”: 0,
“ignore_user_permissions”: 0,
“ignore_xss_filter”: 0,
“in_filter”: 0,
“in_global_search”: 0,
“in_list_view”: 0,
“in_standard_filter”: 0,
“label”: “Termination Amount”,
“length”: 0,
“no_copy”: 0,
“options”: “Company:company:default_currency”,
“permlevel”: 0,
“precision”: “”,
“print_hide”: 0,
“print_hide_if_no_value”: 0,
“read_only”: 1,
“remember_last_selected_value”: 0,
“report_hide”: 0,
“reqd”: 0,
“search_index”: 0,
“set_only_once”: 0,
“translatable”: 0,
“unique”: 0
},
{
“allow_bulk_edit”: 0,
“allow_in_quick_entry”: 0,
“allow_on_submit”: 0,
“bold”: 0,
“collapsible”: 0,
“columns”: 0,
“fetch_if_empty”: 0,
“fieldname”: “column_break_23”,
“fieldtype”: “Column Break”,
“hidden”: 0,
“ignore_user_permissions”: 0,
“ignore_xss_filter”: 0,
“in_filter”: 0,
“in_global_search”: 0,
“in_list_view”: 0,
“in_standard_filter”: 0,
“length”: 0,
“no_copy”: 0,
“permlevel”: 0,
“precision”: “”,
“print_hide”: 0,
“print_hide_if_no_value”: 0,
“read_only”: 0,
“remember_last_selected_value”: 0,
“report_hide”: 0,
“reqd”: 0,
“search_index”: 0,
“set_only_once”: 0,
“translatable”: 0,
“unique”: 0
},
{
“allow_bulk_edit”: 0,
“allow_in_quick_entry”: 0,
“allow_on_submit”: 1,
“bold”: 0,
“collapsible”: 0,
“columns”: 0,
“default”: “”,
“depends_on”: “eval:doc.status==“Terminate Loan””,
“fetch_if_empty”: 0,
“fieldname”: “terminate_loan_from_salary”,
“fieldtype”: “Check”,
“hidden”: 0,
“ignore_user_permissions”: 0,
“ignore_xss_filter”: 0,
“in_filter”: 0,
“in_global_search”: 0,
“in_list_view”: 0,
“in_standard_filter”: 0,
“label”: “Terminate Loan From Salary”,
“length”: 0,
“no_copy”: 0,
“permlevel”: 0,
“precision”: “”,
“print_hide”: 0,
“print_hide_if_no_value”: 0,
“read_only”: 0,
“remember_last_selected_value”: 0,
“report_hide”: 0,
“reqd”: 0,
“search_index”: 0,
“set_only_once”: 0,
“translatable”: 0,
“unique”: 0
},
{
“allow_bulk_edit”: 0,
“allow_in_quick_entry”: 0,
“allow_on_submit”: 0,
“bold”: 0,
“collapsible”: 0,
“columns”: 0,
“depends_on”: “eval:doc.status==“Terminated””,
“fetch_if_empty”: 0,
“fieldname”: “termination_date”,
“fieldtype”: “Date”,
“hidden”: 0,
“ignore_user_permissions”: 0,
“ignore_xss_filter”: 0,
“in_filter”: 0,
“in_global_search”: 0,
“in_list_view”: 0,
“in_standard_filter”: 0,
“label”: “Termination Date”,
“length”: 0,
“no_copy”: 0,
“permlevel”: 0,
“precision”: “”,
“print_hide”: 0,
“print_hide_if_no_value”: 0,
“read_only”: 0,
“remember_last_selected_value”: 0,
“report_hide”: 0,
“reqd”: 0,
“search_index”: 0,
“set_only_once”: 0,
“translatable”: 0,
“unique”: 0
},

  1. I updated the refresh function to add termination button in loans.js

refresh: function (frm) {
if (frm.doc.docstatus == 1) {
if (frm.doc.status == “Sanctioned”) {
frm.add_custom_button((‘Create Disbursement Entry’), function() {
frm.trigger(“make_jv”);
}).addClass(“btn-primary”);
} else if (frm.doc.status == “Disbursed” && frm.doc.repayment_start_date && (frm.doc.applicant_type == ‘Member’ || frm.doc.repay_from_salary == 0)) {
frm.add_custom_button(
(‘Create Repayment Entry’), function() {
frm.trigger(“make_repayment_entry”);
}).addClass(“btn-primary”);
}
if (frm.doc.terminate_loan == 1 && frm.doc.terminate_loan_from_salary == 0) {
frm.add_custom_button(__(‘Terminate Loan’), function() {
frm.trigger(“make_termination_jv”);
}).addClass(“btn-primary”);
}
}
frm.trigger(“toggle_fields”);
},

  1. In loans.py
    i have done the following.

A. under def set_missing_fields(self):
I have added::

    if self.status == "Terminated":
		self.balance_amount = 0.0

to look like this::

def set_missing_fields(self):
if not self.company:
self.company = erpnext.get_default_company()

	if not self.posting_date:
		self.posting_date = nowdate()

	if self.loan_type and not self.interest_rate:
		self.interest_rate = frappe.db.get_value("Loan Type", self.loan_type, "interest_rate")

	if self.repayment_method == "Repay Over Number of Periods":
		self.monthly_repayment_amount = get_monthly_repayment_amount(self.repayment_method, self.loan_amount, self.interest_rate, self.repayment_periods)

	if self.status == "Repaid/Closed":
		self.total_amount_paid = self.total_payment

	if self.status == "Terminated":
		self.balance_amount = 0.0

B. Added def make_termination_entry(self):
after def make_jv_entry(self): function block

def make_termination_entry(self):
self.check_permission(‘write’)
journal_entry = frappe.new_doc(‘Journal Entry’)
journal_entry.voucher_type = ‘Bank Entry’
journal_entry.user_remark = _(‘Against Loan: {0}’).format(self.name)
journal_entry.company = company
journal_entry.posting_date = nowdate()
account_amt_list = []

	account_amt_list.append({
		"account": payment_account,
		"debit_in_account_currency": self.termination_amount,
		"reference_type": "Loan",
		"reference_name": loan,
		})
	account_amt_list.append({
		"account": loan_account,
		"credit_in_account_currency": self.termination_amount,
		"party_type": applicant_type,
		"party": applicant,
		"reference_type": "Loan",
		"reference_name": loan,
		})
	journal_entry.set("accounts", account_amt_list)

	return journal_entry.as_dict()

C. modified def make_repayment_schedule(self):
by inserting

if self.status == ‘Disbursed’ and self.terminate_loan_from_salary == 1:
principal_amount = balance_amount

to look like this:

def make_repayment_schedule(self):
	self.repayment_schedule = []
	payment_date = self.repayment_start_date
	balance_amount = self.loan_amount
	while(balance_amount > 0):
		interest_amount = rounded(balance_amount * flt(self.interest_rate) / (12*100))
		principal_amount = self.monthly_repayment_amount - interest_amount
		balance_amount = rounded(balance_amount + interest_amount - self.monthly_repayment_amount)

		if balance_amount < 0:
			principal_amount += balance_amount
			balance_amount = 0.0

		if self.status == 'Disbursed' and self.terminate_loan_from_salary == 1:
			principal_amount = balance_amount

		total_payment = principal_amount + interest_amount
		self.append("repayment_schedule", {
			"payment_date": payment_date,
			"principal_amount": principal_amount,
			"interest_amount": interest_amount,
			"total_payment": total_payment,
			"loan_balance": balance_amount
		})
		next_payment_date = add_months(payment_date, 1)
		payment_date = next_payment_date

D. Added def update_termination_amount(doc):
After def update_total_amount_paid(doc): function block.

def update_termination_amount(doc):
termination_amount = 0
if status == “Disbursed”:
for data in doc.repayment_schedule:
self.total_payment += data.total_payment
if data.paid:
self.total_amount_paid += data.total_payment
termination_amount = self.total_payment - self.total_amount_paid
frappe.db.set_value(“Loan”, doc.name, “termination_amount”, termination_amount)

E. After def update_disbursement_status(doc): function block i added

def update_disbursement_status(doc):
disbursement = frappe.db.sql("""
select posting_date, ifnull(sum(credit_in_account_currency), 0) as disbursed_amount
from tabGL Entry
where account = %s and against_voucher_type = ‘Loan’ and against_voucher = %s
“”", (doc.payment_account, doc.name), as_dict=1)[0]

disbursement_date = None
if not disbursement or disbursement.disbursed_amount == 0:
	status = "Sanctioned"
elif disbursement.disbursed_amount == doc.loan_amount:
	disbursement_date = disbursement.posting_date
	status = "Disbursed"
elif disbursement.disbursed_amount > doc.loan_amount:
	frappe.throw(_("Disbursed Amount cannot be greater than Loan Amount {0}").format(doc.loan_amount))

if status == 'Disbursed' and getdate(disbursement_date) > getdate(frappe.db.get_value("Loan", doc.name, "repayment_start_date")):
		frappe.throw(_("Disbursement Date cannot be after Loan Repayment Start Date"))

frappe.db.sql("""
	update `tabLoan`
	set status = %s, disbursement_date = %s
	where name = %s
""", (status, disbursement_date, doc.name))

F. Finally at the bottom I added

@frappe.whitelist()
def make_termination_entry(loan, company, loan_account, applicant_type, applicant, termination_amount,
payment_account=None, interest_income_account=None):

journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Bank Entry'
journal_entry.user_remark = _('Against Loan: {0}').format(loan)
journal_entry.company = company
journal_entry.posting_date = nowdate()
journal_entry.paid_loan = json.dumps(row_name)
account_amt_list = []

account_amt_list.append({
	"account": payment_account,
	"debit_in_account_currency": termination_amount,
	"reference_type": "Loan",
	"reference_name": loan,
	})
account_amt_list.append({
	"account": loan_account,
	"credit_in_account_currency": termination_amount,
	"party_type": applicant_type,
	"party": applicant,
	"reference_type": "Loan",
	"reference_name": loan,
	})
journal_entry.set("accounts", account_amt_list)

return journal_entry.as_dict()	 

Now I need help in making this code run as am facing some challenges in getting this code run well

It may be possible (with some hacks) to make it work without too much customization. Since “Create Repayment Entry” is provided OOTB, use the same function to foreclose loan.

js

Override loan.js with custom script to -

  1. display Create Repayment Entry even if repay_from_salary is checked.
  2. override make_repayment_entry and bring all unpaid rows from repayment_schedule in dialog. By default it brings only rows where repayment date is less than today.

py

  1. update principal_amount, interest_amount in repayment_schedule to 0 using on_update_after_submit hook on loan doctype

rest of the flow should work as is. I’m trying this way