fix: Loan Seurity shortfall calculation fixes
diff --git a/erpnext/loan_management/doctype/loan/loan.json b/erpnext/loan_management/doctype/loan/loan.json
index 192beee..aa5e21b 100644
--- a/erpnext/loan_management/doctype/loan/loan.json
+++ b/erpnext/loan_management/doctype/loan/loan.json
@@ -20,8 +20,8 @@
"section_break_8",
"loan_type",
"loan_amount",
- "is_secured_loan",
"rate_of_interest",
+ "is_secured_loan",
"disbursement_date",
"disbursed_amount",
"column_break_11",
@@ -334,7 +334,7 @@
],
"is_submittable": 1,
"links": [],
- "modified": "2020-07-02 20:46:40.128142",
+ "modified": "2020-08-01 12:36:11.255233",
"modified_by": "Administrator",
"module": "Loan Management",
"name": "Loan",
diff --git a/erpnext/loan_management/doctype/loan/test_loan.py b/erpnext/loan_management/doctype/loan/test_loan.py
index c65996e..23815d598 100644
--- a/erpnext/loan_management/doctype/loan/test_loan.py
+++ b/erpnext/loan_management/doctype/loan/test_loan.py
@@ -269,7 +269,7 @@
self.assertTrue(loan_security_shortfall)
self.assertEquals(loan_security_shortfall.loan_amount, 1000000.00)
- self.assertEquals(loan_security_shortfall.security_value, 400000.00)
+ self.assertEquals(loan_security_shortfall.security_value, 800000.00)
self.assertEquals(loan_security_shortfall.shortfall_amount, 600000.00)
frappe.db.sql(""" UPDATE `tabLoan Security Price` SET loan_security_price = 250
diff --git a/erpnext/loan_management/doctype/loan_security_pledge/loan_security_pledge.py b/erpnext/loan_management/doctype/loan_security_pledge/loan_security_pledge.py
index 961c05c..2bb6fd8 100644
--- a/erpnext/loan_management/doctype/loan_security_pledge/loan_security_pledge.py
+++ b/erpnext/loan_management/doctype/loan_security_pledge/loan_security_pledge.py
@@ -14,6 +14,7 @@
def validate(self):
self.set_pledge_amount()
self.validate_duplicate_securities()
+ self.validate_loan_security_type()
def on_submit(self):
if self.loan:
@@ -31,6 +32,27 @@
frappe.throw(_('Loan Security {0} added multiple times').format(frappe.bold(
security.loan_security)))
+ def validate_loan_security_type(self):
+ existing_pledge = ''
+
+ if self.loan:
+ existing_pledge = frappe.db.get_value('Loan Security Pledge', {'loan': self.loan}, ['name'])
+
+ if existing_pledge:
+ loan_security_type = frappe.db.get_value('Pledge', {'parent': existing_pledge}, ['loan_security_type'])
+ else:
+ loan_security_type = self.securities[0].loan_security_type
+
+ ltv_ratio_map = frappe._dict(frappe.get_all("Loan Security Type",
+ fields=["name", "loan_to_value_ratio"], as_list=1))
+
+ ltv_ratio = ltv_ratio_map.get(loan_security_type)
+
+ for security in self.securities:
+ if ltv_ratio_map.get(security.loan_security_type) != ltv_ratio:
+ frappe.throw(_("Loan Securities with different LTV ratio cannot be pledged against one loan"))
+
+
def set_pledge_amount(self):
total_security_value = 0
maximum_loan_value = 0
diff --git a/erpnext/loan_management/doctype/loan_security_shortfall/loan_security_shortfall.py b/erpnext/loan_management/doctype/loan_security_shortfall/loan_security_shortfall.py
index ffd9673..24527a1 100644
--- a/erpnext/loan_management/doctype/loan_security_shortfall/loan_security_shortfall.py
+++ b/erpnext/loan_management/doctype/loan_security_shortfall/loan_security_shortfall.py
@@ -7,6 +7,7 @@
from frappe.utils import get_datetime
from frappe.model.document import Document
from six import iteritems
+from erpnext.loan_management.doctype.loan_security_unpledge.loan_security_unpledge import get_pledged_security_qty
class LoanSecurityShortfall(Document):
pass
@@ -50,31 +51,29 @@
"valid_upto": (">=", update_time)
}, as_list=1))
- ltv_ratio_map = frappe._dict(frappe.get_all("Loan Security Type",
- fields=["name", "loan_to_value_ratio"], as_list=1))
-
- loans = frappe.db.sql(""" SELECT l.name, l.loan_amount, l.total_principal_paid, lp.loan_security, lp.haircut, lp.qty, lp.loan_security_type
- FROM `tabLoan` l, `tabPledge` lp , `tabLoan Security Pledge`p WHERE lp.parent = p.name and p.loan = l.name and l.docstatus = 1
- and l.is_secured_loan and l.status = 'Disbursed' and p.status = 'Pledged'""", as_dict=1)
+ loans = frappe.get_all('Loan', fields=['name', 'loan_amount', 'total_principal_paid'], filters={'status': 'Disbursed'})
loan_security_map = {}
for loan in loans:
- loan_security_map.setdefault(loan.name, {
- "loan_amount": loan.loan_amount - loan.total_principal_paid,
- "security_value": 0.0
- })
+ outstanding_amount = loan.loan_amount - loan.total_principal_paid
+ pledged_securities = get_pledged_security_qty(loan.name)
+ ltv_ratio = ''
+ security_value = 0.0
- current_loan_security_amount = loan_security_price_map.get(loan.loan_security, 0) * loan.qty
- ltv_ratio = ltv_ratio_map.get(loan.loan_security_type)
+ for security, qty in pledged_securities.items():
+ if not ltv_ratio:
+ ltv_ratio = get_ltv_ratio(security)
+ security_value += loan_security_price_map.get(security) * qty
- loan_security_map[loan.name]['security_value'] += current_loan_security_amount - (current_loan_security_amount * loan.haircut/100)
+ current_ratio = (outstanding_amount/security_value) * 100
- for loan, value in iteritems(loan_security_map):
- if (value["loan_amount"]/value['security_value'] * 100) > ltv_ratio:
- create_loan_security_shortfall(loan, value, process_loan_security_shortfall)
+ if current_ratio > ltv_ratio:
+ shortfall_amount = outstanding_amount - ((security_value * ltv_ratio) / 100)
+ create_loan_security_shortfall(loan.name, outstanding_amount, security_value, shortfall_amount,
+ process_loan_security_shortfall)
-def create_loan_security_shortfall(loan, value, process_loan_security_shortfall):
+def create_loan_security_shortfall(loan, loan_amount, security_value, shortfall_amount, process_loan_security_shortfall):
existing_shortfall = frappe.db.get_value("Loan Security Shortfall", {"loan": loan, "status": "Pending"}, "name")
@@ -85,9 +84,14 @@
ltv_shortfall.loan = loan
ltv_shortfall.shortfall_time = get_datetime()
- ltv_shortfall.loan_amount = value["loan_amount"]
- ltv_shortfall.security_value = value["security_value"]
- ltv_shortfall.shortfall_amount = value["loan_amount"] - value["security_value"]
+ ltv_shortfall.loan_amount = loan_amount
+ ltv_shortfall.security_value = security_value
+ ltv_shortfall.shortfall_amount = shortfall_amount
ltv_shortfall.process_loan_security_shortfall = process_loan_security_shortfall
ltv_shortfall.save()
+def get_ltv_ratio(loan_security):
+ loan_security_type = frappe.db.get_value('Loan Security', loan_security, 'loan_security_type')
+ ltv_ratio = frappe.db.get_value('Loan Security Type', loan_security_type, 'loan_to_value_ratio')
+ return ltv_ratio
+