MPESA Loan Repayments and Savings Deposits Functional Spec v1

Owned by Kay Chau
Last updated: Oct 27, 2010 by Jeff Brewster (Unlicensed)Version comment
14 min read

THIS VERSION IS FOR HISTORICAL PURPOSES - FOR CURRENT SPECIFICATION, REFER TO:

MPESA Loan Repayments and Savings Deposits Functional Spec v2

M-PESA - Import Loan Repayments plus Savings Deposits

Release: May 2010

Current Owners:

Kay Chau

Status (Draft, In Review, Stable, Approved)

Stable


Introduction

KEEF is planning to have mobile integration with M-PESA.  They are using Mifos for their MIS platform.  KEEF needs the ability to import a file of transaction information from M-PESA into Mifos.  They also have the need for importing single amounts that span several different transactions.

User Stories (Epics)

Priority

User Story

Section in FR

1

As a user (accountant) at the HO, I want to be able to import a file of loan repayment transactions plus savings deposits from M-PESA into Mifos so that all details for our clients' payments are accurately recorded in Mifos

 

1

As a loan officer at a BO, I want to be able to see the clients at branch have paid their loans in Mifos

 

1

As a loan officer at a BO, I want to be able to see savings accounts of the clients updated with the correct deposit information

 

Goals

  • Ability to import file of loan repayment transactions plus savings deposits into Mifos
  • Transaction History in Mifos accurately shows loan payments and savings deposits
  • Create a plugin similar to what has been done for Al Majmoua for the Audi Bank import.

Non-Goals

The following items will not be addressed in this version of the plugin:

  • Additional logging of import than what we do already in Mifos for logging
  • Ability to review what is imported at import time

Definitions and Terminology

Term

Definitions

 

 
  • Mandatory fields will be preceded by *
  • Links are italicized
  • Buttons are Button

Related Documents

Import Transactions

This feature is in the Admin section and will allow the User to import transactions

Use Cases

Administrator sets up order in which a transaction can applied

Actors

  • Administrator

Preconditions

Basic Flow

  1. Administrator edits a properties file with new setting for order of products a transaction import is applied to.  The products are listed with commas by short name. 

Post-conditions

  • All transactions imported as repayments/deposits are imported in this order unless product names are found in the import row.

Validations

  • When import happens, validation will be done to try to import in this logic.  If the accounts are not found, an error is thrown.
  • If this property is not set, then all transaction rows with no other information on loan or savings accounts will thrown an error.

Accountant imports transactions (M-PESA) - file with no errors

Actors

  • Accountant

Preconditions

  • Accountant has permissions to import transactions
  • Accountant has file in xls (Excel 97 format) to import

Basic Flow

  1. Accountant logs onto Mifos and navigates to the Admin section. Accountant selects Import transactions.
  2. Mifos displays new screen for Import Transactions.
  3. Accountant selects M-PESA for Import format, and selects file from their computer for import. Accountant clicks on Continue.
  4. Mifos imports file and checks for any errors (see Validations below). If there are no errors, Mifos displays Review & Submit screen with "There are no errors found. Click Submit to continue with import.".
  5. Accountant clicks on Submit. Mifos displays confirmation screen that import was successful.

Post-conditions

  • All data available in the file has been imported and all the correct tables and loan account data correctly populated.

Validations

  • If a row has a cell that's empty in a required column is empty, that row is rejected and appropriate error message displayed.
  • No bad data - ie, no numbers in columns that require text and vice versa. See FR's below.

Alternative Flows

Accountant cancels Import

  1. At step 4, Accountant clicks on Cancel instead. Mifos returns User to the Admin screen.

Post-conditions

  • No data has been imported.

Import has errors and Accountant chooses to continue

  1. At step 4, Mifos determines there are errors from Validations above.  Mifos displays Review & Submit screen with error messages depending on types of error.
  2. Accountant chooses to continue with import of the valid rows and clicks on Submit.   Mifos displays confirmation screen that import was successful.

Post-conditions

  • Valid rows are imported and invalid rows are rejected.

Import has errors and Accountant chooses to Cancel

  1. At step 4, Mifos determines there are errors from Validations above.  Mifos displays Review & Submit screen with error messages depending on types of error.
  2. Accountant chooses to cancel and clicks on Cancel instead. Mifos returns User to the Admin screen.

Post-conditions

  • No data has been imported.

Import has errors and Accountant chooses to import a different file

  1. At step 4, Mifos determines there are errors from Validations above.  Mifos displays Review & Submit screen with error messages depending on types of error.
  2. Accountant chooses to import a different file and clicks on Edit Import Information instead. Mifos returns User to the previous screen.  Return to Step 2 of Basic Flow.

Post-conditions

  • No data has been imported.

Loan Officer checks data has been updated in clients' loan and savings accounts

Actors

  • Loan Officer

Preconditions

  • Loan Officer has permissions to view clients loan data
  • Administrator has already imported data for that date.

Basic Flow

  1. Loan Officer logs onto Mifos and navigates to client A's advance loan account.
  2. Mifos displays Transaction History. Loan Officer confirms that a repayment has been updated correctly.
  3. LO then navigates to client A's regular loan account.  LO confirms that repayment there has been updated correctly.
  4. LO then navigates to client A's savings account.  LO confirms that savings deposit has been updated correctly.

Post-conditions

  •  None

Alternative Flows

Loan Officer doesn't see the data updated

  1. Loan Officer selects a client who did not pay for the date of transaction the Administrator had imported.
  2. Loan Officer goes to view that client's transaction history and sees that no transaction has been made.

User Stories

Priority

Size

User Stories

Mingle card #

1

X-Small

As a User, I can give permission to import bank transactions.

 

1

Small

As a User, I can click on Import Transactions and see options to import my file (M-PESA).

 

1

Small

As a User, I can confirm that I want to import the data into Mifos.

 

1

Medium

Populate all data from file import in Mifos

 

2

Medium

As a User, I can click on Import Transactions and see options to import my file (generic)

 

Import Transactions (M-PESA) Functional Requirements

Add new option for M-PESA in Bank Transactions

FR#

Pri

Description

Comments / Mockups

2.1

P1 

Import format Field has option: MPESA

 

2.2

P1

If there is no import plugin set, then the Import Format options will be blank and if the User tries to import a file and Continue, the following error message will be displayed at the top.  Also if the User does not select a plugin even if there are options then the following error message will be displayed.

Please select the import type.

 

2.3

P1

If there is no Mode of Payment configured in Mifos to be "MPESA/ZAP", then an error message will be thrown on the Import screen:

No payment type found named <bank>
where <bank> is the first cell of that file.

 

Import File Details and Validations (M-PESA)

FR#

Pri

Description

Comments / Mockups

3.1

P1

M-PESA is an Excel format (97)

 

3.2

P1

M-PESA import file first few lines contain description of file.  These are to be ignored.  Only data after row of column headings will be imported in the Transactions section.

 

3.3

P1

Import file will have columns in below table 

 

3.4

P1

There will be bare basic error checking - to successfully import the data.

 

3.5

P1

If a row contains a cell that's missing a required field, Mifos displays an error message for each row this occurs.
Row <#> is missing data.
where the row # is the original row # of the import file.

 

3.6

P1

Dates are in the format YYYY-MM-DD HH:MM:ss.  If any value under Date column does not start in format of YYYY-MM-DD, then Mifos displays an error message for each row this occurs.
  Date in Row <#> does not begin with expected format (YYYY-MM-DD).
where the row # is the original row # of the import file.

 

3.8

P1

If first value under the Transaction Party Details column does not contain an client Government ID that matches any client in Mifos and/or the next values do not correspond to a Loan Product or Savings Product in Mifos or does not match any open loan account or savings account where a payment/deposit can be applied to the client, or if the default order set in configuration cannot be applied in any way, Mifos throws an error message

Client ID could not be found from Transaction Party Details or accounts could be applied to transaction in Row <#>


where the row # is the original row # of the import file.

See new attachment for example of import file and below for more details

3.81

P1

Check that Status column says "Completed".  If it has any other status, Mifos throws the following error message:
Status in Row <#> is <status> instead of Completed.
where <#> is the row # of import file, and <status> is the status found in the Status column.

 

3.9

P1

Check if the same file name has been imported.  If so, then throw an error message and reject the whole import.

Same file name has been imported.  Please import a different file.

 

3.10

P1

After clicking on Continue, Mifos will display the Review & Submit screen with the following:
Review the information below.  Click Submit if you want to continue with import or click Edit to make changes. Click Cancel to return to Admin page without submitting information.

Import information

Import file name: <name of file>
Import Status: <#> rows contained no errors and will be imported.

where <#> is the number of valid rows being imported. 
See mockup.

 

3.11

P1

User can then either

  1. Click on Edit import information to go back to previous screen and upload new file.
  2. Continue with import and Submit.
  3. Cancel out of the workflow (returning to Admin screen).

 

3.12

P1

If User clicks on Submit, Mifos imports the file and displays confirmation screen

 

3.13

P1

There is no option to revert a file upload once it has been submitted.

 

3.14

P1

There is no checking of duplicate rows (see 3.9)

 

3.15

P1

Currency of values imported is directly inherited from the loan product that the loan account is mapped to.

 

3.16

P1

If there are no rows found with import data, the following error message should be thrown:

No rows found with import data.

 

M-PESA Import Columns and Description

Column Name

Required

Description

Comments

Validations

Range

Example

Maps to Mifos

Receipt

No

Receipt ID

Receipt ID is exclusive to MPESA, we can ignore

None

 

 

 

Date

Yes

Payment date

 

Validate date is in range (see above)

YYYY-MM-DD time

2009-08-26 12:39:42

Transaction Date

Details

No

Contains information payment received from phone number, telephone number, MFI account number

Ignore

 

 

 

 

Status

Yes

Completed

Check that Status says Completed. 

Must be Completed to accept row.  Other statuses throw an error

Completed, Attempted, Cancelled, Declined

Completed

 

Withdrawn

No

Amount paid from MFI

Only use for Disbursals, Phase 2

 

 

00

 

Paid In

Yes

Transaction amount paid to MFI

 

Validate it's an amount and check same validations in Mifos against digits after decimal

Should be digits after decimal = 0

200

Transaction amount

Balance

No

Balance of MFI

Ignore

 

 

520,499

 

Balance Confirmed

No

automatic check in system

Ignore

 

 

true

 

Transaction Type

No

 

Ignore

 

 

Pay Utility

 

Other Party Info

No

Phone number, telephone number

Ignore

 

 

0722926212 - CYNTHIA OMONDI 

 

Transaction Party Details

Yes

"Client account number assigned by MFI"

We will use this to have Client National ID and include short names) of product(s) applied to the client's accounts if it is different than the default order set in ImportTransactionOrder

Validate that ID matches a client's in Mifos, and validate 3-4 letter codes corresponds to a loan or savings product in Mifos

Just client ID, to client ID plus product(s)

1. 3029149635
2. 30556 AL1

1. Client's National ID
2. Client's National ID, and Advance Loan 1

Loan Repayment and Savings Deposit Details

FR#

Pri

Description

Comments / Mockups

4.1

P1

Assumption: All transaction detail is for loan repayments (loan fees, interest, principal) and/or savings deposits. There is no data in file for loan disbursals, savings withdrawals, client, group or center fees, or client attendance.

 

4.2

P1

Date is actual repayment date recorded

 

4.3

P1

For Loan Accounts: Amount that is being paid - pre-payments, partial payments, and over-payments if there are future installments are allowed.  Amount in file is applied in the following order - Penalties, Fees, Interest, Principal.

 

4.4

P1

Date cannot be before today in Mifos

If they have backdated transactions on, then date can be before today up til last meeting date

4.5

P1

Mode of Payment must be configured in Mifos to have MPESA/ZAP, which will be the mode of payment used for all transactions in that import.

 

4.6

P1

If there is an overpayment of the entire loan, Mifos should throw an error.

 

4.7

P1

Default order of transactions is set in new setting in a properties file called ImportTransactionOrder.
Accounts to which repayments and deposits are made should be applied to in the order specified here when importing a row.
Example: ImportTransactionOrder = AL1, NL1, SP1
In this case, all rows that do not specify a product name after the client ID should have its transaction amount applied to in this order.  Only the next payment due will be applied.  In this example, if the client owes $10 on AL1 next installment and $20 on NL1, and the client pays $100 then those respective amounts would be applied, and then $70 deposited in SP1.
There should always be only 1 savings account and at the end

Will the properties file reside with the plugin as a separate file or in the global properties file

4.8

P1

If there is any product short name specified after the client ID, then the whole transaction amount should just be applied to that account. The product can be a loan or savings product.
There's existing logic in there to take also a few loan or savings accounts if it's specified.  We'll leave that in there for now but we can choose to remove it later.

 

4.9

P1

Related to 4.7, it is not always necessary for the client to have accounts with all products set in the ImportTransactionOrder  field.  If the first account is not found, the plugin should continue to parse and skip to the next account.  If NO accounts are found bearing any of the products listed in the ImportTransactionOrder , THEN an error should be thrown indicating accounts were not found.

 

Other Assumptions

  • Name payment mode = MPESA/ZAP

QA Considerations

  • Performance History - PAR, other values calculated correctly
  • View Loan Account Details - correct dates and values imported
  • View Savings Account Details - correct dates and values imported
  • Transaction History - each transaction should still be logged, with the user who did the import

Standard Considerations

Security (Permissions, Roles, and Data Scope)

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?

No

Are you adding any new permissions or changing existing permission to control this feature?

No - permission already added

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 special considerations.  Default value is checked in ADMIN role and unchecked for all other roles.

What will be the default values for default roles in a new installation?

 

Impacts to System

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 Undo Loans?

No

 

 

Globalization/Localization

Will this feature support users localizing data that they enter?

 

Does this feature involve any date/time related data, and if so how should conversions be handled?

 

Is there currency or other numeric data ? If so does it require any special handling or validation?

 

Logging

Change Log

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.

Does the feature affect any existing reports?

No

Does the feature require adding any new reports?

No

Performance

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 or ASP page timeout?

 

Will the feature contain any bulk insert/update/delete transactions?

 

Will the feature contain any caching mechanisms or cache refreshing mechanisms?

Unclear

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 size of file

Does the feature affect existing batch jobs or require adding any new batch jobs?

Unclear

Setup and Installation

New Installations

Will the feature include demo data?

No

Does the feature require any data to be gathered at setup runtime?

No

Backward Compatibility and Upgrades

Is there any data conversion that needs to be done as part of an upgrade?

 

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.

ADMIN role should be updated to have this permission checked.

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

If different user groups are using the same database, are there concerns over the sharing of data related to the feature?

 

Are there expected to be performance related issues with having many customers sharing the same hardware in support of this feature?

 

Configuration

Does this feature require changes to configuration files?

No

If so, is this feature enabled or disabled by default?

N/A

Open Questions and Notes

Answered

  • Early payments
  • Only disburse full amount - in the future hope to disburse in segments - won't have this use case this time
  • Group and individual amounts
  • GLIM off?

Fees

  • 3 upfront 1 time fees that are assessed
    o Client appraisal fixed amount, % amount, upfront insurance fee - sometimes fixed, sometimes % fee
    o Legal Fee is at each account level
    o Fees when clients go into default
  • Group fee - service charge charged at every meeting
  • Disbursement will not include fees, fees are applied manually

No Excel sheet at M-PESA right now

Low number of transactions - 4 per week

What currency does KEEF use?  Kenyan Shillings

  • What does KEEF want mode of payment to be - we will default to M-PESA for this plugin
  • Will disbursals and repayments be in separate files?  Yes

File is in XLS format

  • How do we distinguish between transactions?  Can we have some business logic to determine this if Mifos account number can't be used in the spreadsheet?  2 options:
    • Use combination of client unique ID and short name of the loan product

Unanswered

Review and Approvals

Date

Name

Role

Status

 

 

PM

 

 

 

Dev

 

 

 

QA