Import loan account data

Release	Release J
Current Owners:
Status (Draft, In Review, Stable / Approved):	In Review

Contents:

Introduction

This feature allows to create new loan accounts through importing .xls fle.

Goals

Basic goals:

Allow to import loan accounts data in .xls file based on provided template.
Allow bulk creation of new account with imported .xls file.

Non-Goals

Bulk edit of existing loan accounts

Definitions and Terminology

Term	Definitions
User	User of Mifos system with appropriate rights
Customer	A group or client, recipent of loan account.
XLS Template	A spreadsheet template use for importing accounts.
Loan account	A account of type loan, opened for client or group. Account is instance of Loan product.
Loan product	A basic definition of available loan.
XLS document	An instance of XLS template.

User Stories

Priority	User Stories	Section in FR
1	As an User, I want to be able to import XLS document with loan accounts, so that accounts from document with be created in Mifos.

Use Cases

User import loan accounts data

Actors

User - a Mifos system user

Preconditions

User is logged.
User has permissions to import new loans data.
User has permissions to create new loan accounts.
User created XLS document using provided XLS template.

Basic Flow

User go to Administration page ("Admin").
User clicks on link "Import Loans"
System open new page "Import Loans".
User selects document with loans data (XLS Document) and uploads it to Mifos server.
System parse uploaded document and shows to user how many rows were parsed successfully and which rows were not. Each row with errors has explanation.
User clicks "Save" button to save imported rows if any of them were parsed successfully.
1. No rows were parsed successfully: see alternative flow: "No rows parsed successfully".
System saves data and present a simple summary to user.

Post-conditions

System displays page with short summary.
All imported and parsed successfully rows are persisted in database.

Alternative Flows

No rows parsed successfully

System displays 0 successfully parsed rows.
"Save" button is inactive.
User corrects his XLS Document and clicks on button "Edit".
System open page "Import Loans"
User redoes import procedure from step 3 from basic flow.

Validations

Functional Requirements

FR#	Description	Comments/Mockups
1	Loans import data is an Excel (97) file.
2	Excel file is checked against set of basic rules: empty file empty column data in column cannot be read and parsed
3	Excel file is checked against Mifos' restrictions
4	For account without number a one should be generated by system.
5	Each row error message should contain row and cell number and error explanation.

XLS Template Descritpion

First row of template contains name/description of a file. Second row contains column headers. Both rows are ignored by parser.

Actual data begins in the third row. Each row contains information for one loan account. Order of columns is important and cannot be changed.

Column name	Column description	Is Mandatory	Comment
Account Number	Contains a text representation of loan account global identification number.	No.	For empty column parser with create new account and generate number. If column is not empty parser will check for duplicates and create account with provided number.
Customer Global ID	Contains a text representation of global identification for customer for which we create new account. Customer must exist in the system.	Yes.	Customer must be in state "Active".
Product Name	Contains a name of loan product which will be used as a base for new loan account. Product must exist in the system.	Yes.	This is a full name of a product. Parser won't accept short name or product number.
Status Name	Contains a text representation of account status.	Yes.	Allowed values are: Cancelled Active in good standing Active in bad standing Approved Closed Obligation Met Closed Rescheduled Closed Written OFF Partial Application Pending Approval Values are not case-sensitive.
Cancel Flag Reason	Contains a text representation of reason for cancelling account.	Only if Status Name is set to "Cancelled"	Allowed values are: Withdraw Other Rejected Reversal Values are not case-sensitive.
Loan Amount	Contains a decimal representation of a loan amount.	Yes.	Loan amount must conform to loan product requirements and Mifos restrictions on decimal values.
Interest Rate	Contains a decimal representation of interest rate.	Yes.	Interest rate must conform to loan product requirements and Mifos restrictions on decimal values.
No. Of Installments	Contains a integer representation of number of installments.	Yes.	Number of installments must conform to loan product definition.
Disbursal Date	Contains a date representation of disbursal date for account.	Yes.	Default date format is: D.MM.YYYY where D is day, MM is month (always two digits) and YYYY is year (always four digits). Date must conform to loan product requirements, customer requirements and Mifos restrictions.
Grace Period	Contains an integer representation of grace period.	Depends on loan product.	This column is mandatory if loan products allows for grace period. Otherwise this column is ignored.
Source Of Fund	Contains a text representation of source of found for loan account.	No.	Must conform to values defined in Mifos.
Purpose	Contains a text representation of loan account purpose.	No.	Must conform to values defined in Mifos.
Collateral Type	Contains a text representation of collateral type.	No.	Must conform to values defined in Mifos.
Collateral Notes	Contains a text representation of collateral notes.	No.	Must conform to values defined in Mifos.
External ID	Contains a text representation of external id.	No.	Must conform to values defined in Mifos.

Error messages

Here is a list of errors messages. Each message that applies to either specific column or row is preceded by row number and column number.

Error	Applies to	Description
Missing account number	Column: Account Number	Account number was expected, but application didn't found any in data file.
Customer with global id # not found	Column: Customer Global ID	Provided customer global ID was not found in Mifos database. '#' is replaced with provided customer global ID.
Missing product name	Column: Product Name	Product name was expected, but application didn't found any in data file.
Active and applicable product with name # not found	Column: Product Name	System didn't find loan product definition with provided name that is active and applicable to provided customer.
Cannot add new loan to inactive customer	Column: Customer Global ID	Provided customer is inactive, so no new loans can be added.
Missing account status name	Column: Status Name	Account status name was expected, but application didn't found any in data file.
Loan status is incorrect;	Column: Status Name	Provided status name is not applicable to loan account.
Missing reason for cancellation	Column: Cancel Flag Reason	Account status required status explanation flag, application didn't found any in data file.
Wrong reason for cancelled account	Column: Cancel Flag Reason	Provided cancellation status is not applicable to this account.
Product's frequency of installments is different than customer's meeting schedule.	Column: Product Name	Provided product's frequency of installments is different than provided customer's meeting schedule so account cannot be created.
Missing loan amount	Column: Loan Amount	Loan amount was expected, but application didn't found any in data file.
Loan amount is out of allowed range	Column: Loan Amount	Loan amount is out of range specified by loan product.
The amount is invalid because the number of digits before the decimal separator exceeds the allowed number of #.	Column: Loan Amount	Loan amount violates number of digits before decimal separator in defined in system configuration. '#' is replaced by allowed number of digits.
The amount is invalid because the number of digits after the decimal separator exceeds the allowed number of #.	Column: Loan Amount	Loan amount violates number of digits after decimal separator in defined in system configuration. '#' is replaced by allowed number of digits.
Missing interest rate	Column: Interest Rate	Interest rate was expected, but application didn't found any in data file.
Interest rate is out of allowed range	Column: Interest Rate	Interest rate is out of range specified by loan product.
The interest rate is invalid as the number of digits after the decimal separator exceeds the allowed number of #.	Column: Interest Rate	Interest rate violates number of digits after decimal separator in defined in system configuration. '#' is replaced by allowed number of digits.
The interest rate is invalid as the number of digits before the decimal separator exceeds the allowed number of #.	Column: Interest Rate	Interest rate violates number of digits before decimal separator in defined in system configuration. '#' is replaced by allowed number of digits.
Missing number of installments	Column: No. Of Installments	Number of installments was expected, but application didn't found any in data file.
Number of installments is out of allowed range	Column: No. Of Installments	Number of installments is out of range specified by loan product.
Missing disbursal date	Column: Disbursal Date	Disbursal date was expected, but application didn't found any in data file.
Invalid date: #	Column: Disbursal Date	Provided date cannot be parsed by application or was invalid by Mifos restriction. In latter case error details are shown.
The disbursement date is invalid. Disbursement date must be on or after todays date.	Column: Disbursal Date	One of error details for Disbursal Date error.
The disbursement date is invalid. It cannot be before the customers activation date #	Column: Disbursal Date	One of error details for Disbursal Date error. '#' is replaced by customer activation date.
The disbursement date is invalid. It cannot be before the product start date #.	Column: Disbursal Date	One of error details for Disbursal Date error. '#' is replaced by product start date.
The disbursement date is invalid. It must fall on a valid customer meeting schedule.	Column: Disbursal Date	One of error details for Disbursal Date error.
Missing grace period	Column: Grace Period	Grace period was expected, but application didn't found any in data file.
Grace period for repayments must be less than number of loan installments	Column: Grace Period	Provided grace period value is greater then provided number of loan installments.
The Grace period cannot be greater than in loan product definition	Column: Grace Period	Provided grace period value is greater then value defined in loan product definition.
Unknown source of fund: #	Column: Source Of Fund	Provided source of fund wasn't found in Mifos database. '# ' is replaced with provided source of fund.
Not enough input rows	File	Provided file doesn't have expected minimal number of rows
Unknown loan purpose: #	Column: Purpose	Provided loan purpose wasn't found in Mifos database. '# ' is replaced with provided loan purpose.
Unknown collateral type: #	Column: Collateral Type	Provided collateral type wasn't found in Mifos database. '# ' is replaced with provided collateral type.

Standard Considerations

Security

Security (Permissions, Roles, and Data Scope)	Yes/No	Comments
Does the user need to be in a particular user hierarchy to use this feature?	No
Does the office hierarchy affect use of this feature?	No
Are you using any existing permissions to control this feature?	Yes	Permission to add new loan accounts
Are you adding any new permissions or changing existing permission to control this feature?	Yes	Permission to import new loan accounts
Are you using any existing activities to control this feature?	No
Are you adding any new activities or changing existing activities to control this feature?	No
Are there any special considerations for upgrade scenarios? What will be the default value for new permissions?	No.	Default values will be set to true.
What will be the default values for default roles in a new installation?	--	Default is true for admin role.

Impacts to System

Impacts to System	Yes/No	Comments
Does this feature affect Bulk Loan Creation? How?	No
Does this feature affect Collection Sheet Entry? How?	No
Does this feature affect Redo Loans?	No
Does this feature affect Reverse Loans?	No
Is this feature affected by Holidays?	No

Globalization/Localization

Globalization/Localization	Yes/No	Comments
Will this feature support users localizing data that they enter?	No
Does this feature involve any date/time related data, and if so how should conversions be handled?	Yes
Is there currency or other numeric data ? If so does it require any special handling or validation? Is there a need for mult-currency? (Loan Accounts)	Yes	All numeric data should be validated against Mifos' restrictions.

Logging

Change Log

Change Log	Yes/No	Comments
Do changes to the data that is collected or stored by the new feature have to be fully logged by the system?	Yes
Does the administrator configuring the system need the ability to turn on or off logging for this feature?	No
Is the feature currently logged but the structure of the logged records changing?	No

Reporting

Provide any relevant information about reporting requirements for the new features and answer the questions below, providing detail to explain any particular area when necessary.

Reporting	Yes/No	Comments
Does the feature affect any existing reports?	No
Does the feature require adding any new reports?	No

Performance

Performance	Yes/No	Comments
Will the feature be a high use-case scenario?	No
Will the feature have potential for high concurrency?	No
Does the feature include complex UI or data gathering logic that will be used by a significant portion of the user base?	No
Does the feature contain risks of database connection timeout?	Yes
Will the feature contain any bulk insert/update/delete transactions?	Yes
Will the feature contain any caching mechanisms or cache refreshing mechanisms?	No
Could the feature result in a large amount of data being sent to the client or between the database and web server?	Yes
Would users on a low bandwidth connection likely face issues with a part of this feature?	Depends on number of rows in file.
Does the feature affect existing batch jobs or require adding any new batch jobs?	No

Setup and Installation

New Installations

New Installations	Yes/No	Comments
Does this feature require both Mifos Business Intelligence Suite and Mifos Product?	No
Does this feature require special work for hosting? (sys admin)	No

Backward Compatibility and Upgrades

Backward Compatibility and Upgrades	Yes/No	Comments
Is there any data conversion that needs to be done as part of an upgrade?	No.
Will customers lose data or will the way existing data is stored change significantly?	No.
Will another feature, workflow or portion of the data model be deprecated as a result of this new feature?	No.
Will existing role permissions be changed or impacted by this feature? If so provide details in the security section.	No.
Will existing customers need to learn a new UI process or change the way they use the system as a result of this new feature?	No.

Hosting Support

Hosting Support	Yes/No	Comments
If different user groups are using the same database, are there concerns over the sharing of data related to the feature?	No
Are there expected to be performance related issues with having many customers sharing the same hardware in support of this feature?	Yes	Depends on file size.

Configuration

Configuration	Yes/No	Comments
Does this feature require changes to configuration files?	No.
If so, is this feature enabled or disabled by default?	----
Are existing configuration properties used to control this feature? If yes, which ones?	No.
Are new configuration properties added for this feature?	No.

Open Issues

MIFOS-5662

Reviews and Approvals

...

Legacy Mifos 2.x Documentation

Import loan account data using XLS template