M-PESA - Import Loan Repayments plus Savings Deposits v2
Release | Leila E |
Current Owners: | Kay Chau |
Status (Draft, In Review, Approved) | Approved (Marie Valdez) |
Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Introduction
...
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
...
- At step 4, Mifos determines there are errors from Validations above. Mifos displays Review & Submit screen with error messages depending on types of error.
- 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 Transactions (M-PESA) Functional Requirements
Import File Details and Validations (M-PESA)
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. |
|
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. |
|
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 |
|
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. |
|
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 | 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. |
|
1.13 | P1 | After clicking on Continue, Mifos will display the Review & Submit screen with the following: | See below FR#7 for additional changes |
1.14 | P1 | User can then either
|
|
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: |
|
Anchor | ||||
---|---|---|---|---|
|
...
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 | 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 | 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 | Validate 3-4 letter codes (everything before first space) corresponds to a loan or savings product in Mifos | Mifos product codes | SP1 | Savings Product 1 |
Loan Repayment and Savings Deposit Details
FR# | Pri | Description | Comments / Mockups |
---|---|---|---|
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. | 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. |
|
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. |
|
Other Assumptions
- Name payment mode = MPESA
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
...
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 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
...
- If 2 accounts of the same product exist, an error is returned with the following error message - More than one account matches product code <CODE>