Release

Leila E

Current Owners:

Kay Chau

Status (Draft, In Review, Approved)

Approved (Marie Valdez)

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

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.

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

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

Yes

Determines if this is a payment or disbursal

Check that this is Pay Utility, else ignore row

Must be Pay Utility, anything else IGNORE.

Pay Utility
Business Payment

Pay Utility

N/A

Other Party Info

Yes

Safaricom generated phone number and name associated with phone number

Compare first 10 digits to phone number. If none exists, throw error.  This will be used to identify where the transaction goes.

First 10 digits must correspond to a Phone Number

Numeric

0722926212 - CYNTHIA OMONDI 

Phone Number

Transaction Party Details

Optional

Will include Mifos Product Code if there is one, and other information such as Client's Group ID, etc

We will use this to include short names) of product(s) applied to the client's accounts if it is different than the default order set in ke.co.safaricom.MPesaXlsImporter.ImportTransactionOrder

CHANGE from previous: no more client national ID's and this field is now optional - only use if transactions to be applied are different than default order

Validate 3-4 letter codes (everything before first space) corresponds to a loan or savings product in Mifos

If not, ignore this field

Mifos product codes

SP1

Savings Product 1

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. If there is no specific loan/savings product. The amount is allocated as configured and only the first upcoming loan amount will be paid... See example worksheet on Requirements document.  Amount is applied in the following order to each loan account - Loan Penalties, Loan 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.  All transactions accepted will be tagged with Payment Mode MPESA.

4.6

P1

If there is an overpayment of the entire loan, Mifos should throw an error.   This situation can occur if the savings product is not found for the client. 

4.7

P1

Default order of transactions is set in new setting in a properties file called ke.co.safaricom.MPesaXlsImporter.ImportTransactionOrder.
Accounts to which repayments and deposits are made should be applied to in the order specified here when importing a row.
Example: ke.co.safaricom.MPesaXlsImporter.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, 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 ke.co.safaricom.MPesaXlsImporter.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 ke.co.safaricom.MPesaXlsImporter.ImportTransactionOrder, THEN an error should be thrown indicating accounts were not found.

Row <#> error - <Receipt ID> - No valid accounts found with this transaction

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?

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

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?

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

Does the feature affect any existing reports?

No

Does the feature require adding any new reports?

No

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

Will the feature include demo data?

No

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

No

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

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?

Does this feature require changes to configuration files?

No

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

N/A

Date

Name

Role

Status

PM

Dev

QA

5.1

P1

Phone number is now compared with Phone Number field for a Client.

5.2

P1

Phone Number does not have to be unique.

6.1

P1

Improve Search to take in whole name when searching for Client, Group, or Center Name

Example:

If a Group Name is saved as 150. Pasay Group.

User should be able to search for "150", "Pasay" and/or "Group" and this should come up as a Search result

6.2

P1

Add ability to search by Phone Number field in Mifos - search can have non-numeric characters.  Search will strip out non-numeric characters and compare with non-numeric form of Phone Numbers stored in Mifos.

Multiple search results can appear.

7.1

P1

In addition to displaying status of import and error messages on the page, add a link "Save Error Log" where all messages on that page are written to a text file that can be saved by the user.