/
MPESA Loan Repayments and Savings Deposits Functional Spec v2

MPESA Loan Repayments and Savings Deposits Functional Spec v2

Mar 03, 2011

M-PESA - Import Loan Repayments plus Savings Deposits v2

Release

Leila E

Current Owners:

Kay Chau

Status (Draft, In Review, Approved)

Approved (Marie Valdez)

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

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

  • Update plugin similar to what has been done for Al Majmoua for the Audi Bank import.

  • Properly account for all transactions read, ignored, errored, and imported.

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

Term

Definitions

 

 

  • Mandatory fields will be preceded by *

  • Links are italicized

  • Buttons are Button

Related Documents

MPESA Loan Repayments and Savings Deposits Functional Spec v1

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.

Import Transactions (M-PESA) Functional Requirements

Import File Details and Validations (M-PESA)

FR#

Pri

Description

Comments / Mockups

FR#

Pri

Description

Comments / Mockups

1.1

P1

M-PESA is an Excel format (97)

The file as is from Safaricom will not work, workaround is to open the file in Excel and save.  User must save with .xls extension. 

1.2

P1

M-PESA import file's 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.

 

1.3

P1

Import file will have columns in below table 

 

1.4

P1

First check if row should be ignored or accepted.

If Status is not Completed, and Transaction Type is not Pay Utility, then IGNORE the row.  This row will contribute to # of rows ignored.

Else, accept the row, and continue doing error checks.

 

1.5

P1

If a row contains a cell that's missing a required field, Mifos displays an error message for each row this occurs.  Only check the rows that are not ignored.

Row <#> error - <Receipt ID> - missing required data.
where the row # is the original row # of the import file.

 

1.6

P1

When importing, must check to see if Receipt ID already exists in any transaction in Mifos - if YES, then row has error and Mifos throws an error message

Row <#> error - <Receipt ID> - Transactions with same Receipt ID have already been imported.

where the row # is the original row # of the import file and <receipt ID> is the receipt ID that was found.

 

1.7

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.

Row <#> error - <Receipt ID> - Date does not begin with expected format (YYYY-MM-DD).

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

 

1.8

P1

Compare Other Party Info - Take first 10 digits as phone number, and compare with Client's Phone Number fields in Mifos.  If there is not a match, then Mifos throws an error message

Row <#> error - <Receipt ID> - Client with mobile number <mobile number> was not found.

where the row # is the original row # of the import file and <mobile number> is the phone number.

If there are too many matches, then Mifos throws this error message.

Row <#> error - <Receipt ID> - More than 1 client with mobile number <mobile number> was found.

If there is a match, use this as identifier for transaction.

Updated 10/4

1.9

P1

Changes to Transaction Party Details - no more taking of National ID - remove this functionality.  Compare first code listed (before first space) with any loan or savings product in Mifos.  If the code exists, import that whole transaction amount to the account associated with that code.  Otherwise, ignore what's in the field.

 

1.10

P1

See FR 4.9 for additional errors to check for.


1.12

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.

 

1.13

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:

<# read> rows were read
       <# imported> rows contained no errors and will be imported
       <# ignored> rows will be ignored  (see FR 1.4 for calculation)
       <# error> rows contained errors and were not imported

Total amount of transactions imported: <total # imported>
Total amount of transactions with error: <total # error>

List all rows with error messages in red

See below FR#7 for additional changes

1.14

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).

 

1.15

P1

If User clicks on Submit, Mifos imports the file and displays confirmation screen that import was successful.  If it's not successful, it will list what error there was, this is different than FR 1.13.

 

1.16

P1

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

 

1.17

P1

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

 

1.18

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

Column Name

Required

Description

Comments

Validations

Range

Example

Maps to Mifos

Receipt

Yes

Receipt ID is proof of payment and a unique MPESA identifier that can be cross-reference with clients phone receipt. Need to be imported into Mifos since needed in the KEEF Collection Sheet.

Receipt ID is exclusive to MPESA

Check that there is not an existing RECEIPT ID

Alphanumeric

Y29DW127

Transaction Details - Receipt ID

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, anything else IGNORE