...
We need to create a table to keep all loan provision categories.The table name is 'm_provision_category''. The table definition is defined below. The category names will vary based customer to customer.
| Name | Datatype | Length | Remarks |
|---|---|---|---|
| id | BIGINT | 20 | Primary Key & Auto Increment |
| category_name | VARCHAR | 100 | Not Null |
| Description | VARCHAR | 200 |
By default Mifos X system will create and insert the following categories into this table based on customer requirements. So the data will look like below.
| id | category_name | Description |
|---|---|---|
| 1 | STANDARD | Punctual Payment without any dues |
| 2 | SUB-STANDARD | Principal and/or Interest overdue by x days |
| 3 | DOUBTFUL | Principal and/or Interest overdue by y days with 1 year original term (or) Principal and/or Interest overdue by z days with more than 1 year term
|
| 4 | LOSS | Principal and/or Interest overdue by t1 days with 1 year original term (or) Principal and/or Interest overdue by t2 days with more than 1 year term |
...
Now how to associate loan provision to a product? In create loan product screen, we have the option to enable loan loss provision as shown in mock up screen below
rganization Organization can choose each category one by one and can add it to the loan product. Organization can choose minimum and maximum over dues in days and can provide provision percentage differently for each loan product. Both Liability & Expense combos will have all GL accounts. So organization can choose liability & expense accounts against each provision category differently,
Once the new loan product form is submitted, product loan provision details will be stored in 'm_loanproduct_provision_details'. The table definition is defined below.
| NAME | Datatype | Length | Remarks |
|---|---|---|---|
| id | BIGINT | 20 | PRIMARY KEY & AUTOINCREMENTAUTO INCREMENT |
| loanproduct_id | BIGINT | 20 | FOREIGN KEY |
| category_id | BIGINT | 20 | FOREIGN KEY |
| min_age | BIGINT | 20 | NOT NULL |
| max_age | BIGINT | 20 | NOT NULL |
| provision_percentage | INT | 4 | DEFAULT 0 |
| liability_account | BIGINT | 20 | FOREIGN KEY acc_gl_account.id |
| expense_account | BIGINT | 20 | FOREIGN KEY acc_gl_account.id |
| created_by | BIGINT | 20 | FOREIGN KEY m_appuser.id |
| created_on | DATE | ||
| modified_by | VARCHAR | 100 | FOREIGN KEY m_appuser.id |
| modified_on | DATE |
For any given tenant, the provision details for a particular loan product will look like below.
| id | loanproduct_id | category_id | min_age | max_age | provision_percentage | liability_account | expense_account | created_by | created_on | modified_by | modified_by |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 10345 | 1 | 0 | 0 | 5 | abcd account | dcba account | 100 | 31-Dec-2014 | 200 | 10-Jan-2015 |
| 2 | 10345 | 2 | 30 | 60 | 20 | abcd account | dcba account | 200 | 31-Dec-2014 | 400 | 10-Jan-2015 |
| 3 | 10345 | 3 | 60 | 180 | 50 | abcd account | dcba account | 400 | 31-Dec-2014 | 500 | 10-Jan-2015 |
| 4 | 10345 | 4 | 180 | 360 | 90 | abcd account | dcba account | 300 | 31-Dec-2014 | 600 | 10-Jan-2015 |
While editing the loan product, persisted provision details will be restored in the edit loan product screen
...
By using the following query we can get all active loan ids along with product id, overdue in days, outstanding loan balance, provision category id, provision percentage, due date
SELECT loan.id as loanid,
loan.product_id as productid,
GREATEST(datediff(CURDATE(),sch.duedate),0) as numberofdaysoverdue,
loan.total_outstanding_derived as outstandingbalance,
lpc.category_id,
lpc.provision_percentage,
sch.duedate
FROM m_loan_repayment_schedule as sch
LEFT JOIN m_loan as loan
ON sch.loan_id = loan.id
LEFT JOIN m_loanproduct_provision_category lpc
ON loan.product_id=lpc.product_id
and lpc.min_age <= GREATEST(datediff(CURDATE(),sch.duedate),0) and GREATEST(datediff(CURDATE(),sch.duedate),0) < lpc.max_age
WHERE loan.loan_status_id=300 and sch.principal_amount > 0 and sch.completed_derived=false and
lpc.category_id > 0 and lpc.provision_percentage > 0
GROUP BY sch.loan_id
HAVING sch.duedate=min(sch.duedate)
The result of the above query will look like below (mock data).The charge calculation for above data is shown below.
| loanid | productid | numberofdaysoverdue | outstandingbalance | category_id | provision_percentage | duedate |
|---|---|---|---|---|---|---|
| 4 | 4 | 0 | 11150.000000 | 1 | 5 | 2015-10-28 |
| 5 | 5 | 0 | 11310.000000 | 1 | 9 | 2015-10-28 |
| 13 | 11 | 692 | 16300.000000 | 4 | 100 | 2013-10-15 |
The charge calculation for above data is shown below.
| loanid | productid | numberofdaysoverdue | outstandingbalance | category_id | provision_percentage | duedate | Amount to be reserved |
|---|---|---|---|---|---|---|---|
| 4 | 4 | 0 | 11150.000000 | 1 | 5 | 2015-10-28 | 557.500000 |
| 5 | 5 | 0 | 11310.000000 | 1 | 9 | 2015-10-28 | 1017.900000 |
| 13 | 11 | 692 | 16300.000000 | 4 | 100 | 2013-10-15 | 13040.000000 |
As current journal entry is not sufficient for provision reports, we need to introduce a new table in the database. The table name is 'm_loan_provision_entry' and the table definition is shown below.
| Name | Datatype | Length | Remarks |
|---|---|---|---|
| id | BIGINT | 20 | PRIMARY KEY and AUTOINCREMENT |
| currency_code | BIGINT | 20 | FOREIGN KEY m_currency.id |
| office_id | BIGINT | 20 | FOREIGN KEY m_office.id |
| product_id | BIGINT | 20 | FOREIGN KEY m_product_loan.id |
| category_id | BIGINT | 20 | FOREIGN KEY m_provision_category.id |
| overdue_in_days | BIGINT | 20 | DEFAULT 0 |
| amount_to_be_reserved | BIGINT | 20 | DEFAULT 0 |
| created_by | BIGINT | 20 | FOREIGN KEY m_appuser.id |
| created_on | DATE | NOT NULL | |
| modified_by | BIGINT | 20 | FOREIGN KEY m_appuser.id |
| modified_on | DATE |
Whenever the provision calculation is done, system is going to make an entry in the 'provision_history' table. The structure of the table is shown below
| Name | Datatype | Length | Description |
|---|---|---|---|
| id | BIGINT | 20 | PRIMARY KEY & AUTO INCREMENT |
| created_on | DATE | NOT NULL | |
| journal_entry_created | INT | 1 | DEFAULT 0 |
| created_by | BIGINT | 20 | m_appuser_id or scheduler job dummy id |
| created_on | DATE |
Changes required in existing Classes:
- While creating any loan product template, provision categories should be added to the response object. For this
- org.mifosplatform.portfolio.loanproduct.data.LoanProductData should have one more instance variable to hold provision categories
- New interface org.mifosplatform.organisation.provisioning.service.ProvisionCategoriesReadPlatformService should have a method to retrieve all provision categories.
- The method name is List<EnumOptionData> retrieveProvisionCategories() ;
- Provision Categories should retrieved and set to LoanProductData in org.mifosplatform.LoanProductsApiResource.handleTemplate(final LoanProductData productData)
- Client application should read response param "provisioncategoies" if available it should display provision categories as shown in create product mock up screen
- While creating a loan product, the provision categories along with min age, max age, percentage to provision should be read and saved into DB. For this
- New entity class org.mifosplatform.organisation.provisioning.domain.LoanProductProvision for 'm_loanproduct_provision_category' should be added
- org.mifosplatform.portfolio.loanproduct.domain.LoanProduct should have set of LoanProductProvision
- Client application should send loan product provision categories as part of the request params while creating a loan product. The parameter name is 'loanproductprovisons'
- Provision categories should be read and added in org.mifosplatform.portfolio.loanproduct.domain.LoanProduct.assembleFromJson(...)
- While retrieving a loan product (s), we need to retrieve the provision categories and associate with the Loan Product object (s). For this
- org.mifosplatform.portfolio.loanproduct.service.LoanProductReadPlatformServiceImpl.retrieveLoanProduct(Long loanProductId) should be modified to associate provision categories
- org.mifosplatform.portfolio.loanproduct.service.LoanProductReadPlatformServiceImpl.retrieveAllLoanProducts() should be modified to associate provision categories
- org.mifosplatform.organisation.provisioning.service.ProvisionCategoriesReadPlatformService should expose a method to retrieve the provision categories based on loan product id
- While updating a loan product (s), we need to update the provision categories associated to that loan product (s). For this
- org.mifosplatform.portfolio.loanproduct.service.LoanProductWritePlatformServiceJpaRepositoryImpl.updateLoanProduct(...) should be modified to validate and update loan product provision categories
New Screens
- Provisioning History Screen
- Provisioning Screen
...