Ability to extract pre-approved loans (phone number and net disbursement amount) from Mifos. This extract will be uploaded into the M-PESA system. Once uploaded, M-PESA will disburse the loans to the clients' M-PESA accounts via mobile.
1. Ability to diburse net disbursal loan amount. The Mifos extract should include the net loan amount, which is defined us total loan amount less loan fees due at disbursement.
Ability to import M-PESA disbursement transactions into Mifos.
1. Ability to match the M-PESA disbursement records into Mifos approved loans and corresponding fees. In the given example, there will be 1 M-PESA transaction matching 2 transactions with Mifos: the Loan for 450,000 and the up-front loan fee of 3,000. MPESA Disbursement transction of 47,000 will be equivalent to Mifos 2 transactions of 1) 50,000 disbursement and 3,000 loan fee.

User Stories

Priority	User Story	Section in FR
1	As a user (Data Encoder) at the HO, I want to be able to export Approved Loans into Excel file and import it into the M-PESA system.
1	As a user (Data Encoder) at the HO, I want to be able to import a file of disbursals from M-PESA into Mifos so that M-PESA loan disbursements are accurately recorded in Mifos.
1	As a user (Data Encoder, Accountant, Admin, Loan Officer) at either the BO or HO, I want to be able to see the loan disbursements with its corresponding fees properly recorded in Mifos.
1	As a user (Accountant) at the HO, I want to be able to reconcile transactions in the M-PESA system, Mifos, and Sage Pastel.
1	As a user (Data Encoder), I want to be able to see errors in export and import and be able to correct them.

...

Ability to split loans into several transactions given the MPESA disbursal limit. The M-PESA limit has just been changed to KSH 140,000/day and KEEF disburses by checks for loan amount > KSH 50 000.
Ability to process Client Fees
Ability to process Loan Fees NOT due at the time of disbursement. This decision has been made with the reasoning that cashless transaction for other fees can be done by getting permission from client to withdraw funds from saving to pay for other fees. This addresses the issue that the client fees (usually around KSH 100) are to small too incure a money transfer fee (KSH 20) for the clients
Changes to the Mifos product to restrict which fees can be associated with which product

Definitions and Terminology

...

Note that this is a necessity to avoid fraud. With M-PESA, money is disbursed to the clients' phone. Any user who has rights to disbuse loans through M-PESA should NOT have rights to modify a client's phone number. Otherwise, the person can disburse ALL loans to his/her personal account. That user can VIEW the client phone number however.

Actors

Data Encoder
Account Clerk

...

FR#	Pri	Description
1.1	P1	A new permission called Can Edit Phone Number is added to the Client Management section. This permission only applies to client's phone numbers, and not other entities in Mifos.
1.2	P1	It is checked by default for Administrators.
1.3	P1	If you have this permission, you can edit a client's phone number, during client creation and during client edit.
1.4	P1	If a user does NOT have this permission checked, then the field is grayed out during client creation and edit.

Export Mifos Loan Disbursals

...

Use Case - Administrator sets up M-PESA Disbursement Limit

Right. We should put this on Export. The current KEEF limit for disbursing via MPESA is KSH 50,000. We shouldnt export beyond this amount.

This might be out of scope if we put in Pentaho right now - we can hardcode now and then change it later?

Marie, 30Jan2011. It has been agreed that this will be hardcoded in the report right now and will then be a parameter when Pentaho is integrated into Mifos. This integration is scheduled in 2011.

Actors

Administrator

Preconditions

...

(P2) Data Encoder has permission to run "List of MPESA Loans" report in Pentaho.
Normal Loans have been created and approved in Mifos.

Basic Flow

AT THE END OF THE DAY, at 4pm:

Data Encoder calls/extract report “MPESA Loan Disbursals” in Pentaho. (See attached report) Take note that this extract should deduct loan fees from disbursement amount. For each loan, net disbursement amount = loan disbursement amount – total loan fees.
Data Encoder selects Loan Product = Normal Loan, Group = ALL, Date = TODAY
This extract includes
1. All Loans in Application Approved status.
2. Loan Amount <= 50,000
3. Disbursement Date = Extract Date
Data Encoder saves the file in CSV format.
Data Encoder logs into MPESA
While in the MPESA system, Data Encoder imports the file.
While in the MPESA system, Account Clerk approves the MPESA disbursement, then client receives loan amount on their phones

...

A report listing qualified approved loans is generated from Mifos and it should be exported and can be saved as a CSV file format
All loan amounts reflected on the List of MPESA Loans report will be loan amounts minus loan fees.
The extract will contain all the fields as required by MPESA system
'“MPESA Loan Disbursals' is imported and processed in the M-PESA system.
Clients received their loans via M-PESA.

...

Data Encoder calls/extract report “MPESA Loan Disbursals” in Pentaho. (See attached report) Take note that this extract should deduct loan fees from disbursement amount. For each loan, net disbursement amount = loan disbursement amount – total loan fees.
Data Encoder enters the Group for which Advance Loans are to be disbursed, and selects Advance Loan Loan product.
This extract should include:
1. All Loans in Application Approved status.
2. Loan Amount <= 50,000
3. Disbursal Date = Extract Date
4. Only clients in the group specified by the Data Encoder
Data Encoder saves the file in a CSV format.
Data Encoder logs onto MPESA
While in the MPESA system, Data Encoder imports the file.
While in the MPESA system, Account Clerk approves the MPESA disbursement, then client receives loan amount on their phones

...

FR#	Pri	Description	Comments / Mockups
2.1	P1	New property needs to be added to Mifos configuration file for setting Max MPESA Disbursal Limit	We will go ahead and add this now but it will not be used
2.2	P1	For the export, we hardcode for now the max as 50,000. When we integrate Mifos and Pentaho, we should update this extract to take in the property
2.3	P1	Report name wil default to MPESALoanDisbursalsExport_Group_Product_yyyy_mm_dd.csv. Where date is date report is run for.

M-PESA Loan Disbursals Export Columns and Description

Column Name	Required	Description	Validations	Action	Range	Example	Maps to Mifos
Amount	YES	Net Loan amount. That is loan amount less all fees e.g. if Loan value is 10,000 and advance fee is 10/= amount will be 9990. This is the amount client will receive on phone	Do not include loans with loan amount > 50,000. Loans above this amount are in Extract Summary report.	Export	numeric	10000	This should be Loan amount less associated up-front fees that are due at disbursement.
CreditIdentityString	Yes	Phone number , Phone number must have 254 and should not have + or 0	If no phone number, error is listed in Extract Summary report	Export	numeric	254723899766	Phone number
CreditIdentityStringType	Yes	Contains the following information ( Phone Details Mobile Phone Number )		Write as IS	Alphanumeric	[Phone Details Mobile Phone Number]	None
ValidationIdentityString	Yes	This field contains nothing. It is always blank		Write as IS	Blank	blank	None
ValidationIdentityStringType	Yes	Contains the following information ( Security Information National Identity Number )		Write as IS	Alphanumeric	[Security Information National Identity Numbe]	None

Import Loan Disbursal and Fee Transactions

...

All approved loans in Mifos will be disbursed in Mifos.
Each approved loan is disbursed and corresponding loan fees applied. The disbursement transaction and loan fee applications are tied to 1 receipt ID.
All loans with status "Application Pending Status" will not change. So there needs to be a check on the loan status right? YES. Again, KEEF will use this for validation later.
Repayments and savings deposits are processed as stated in Repayment and Savings Import functional specifications.

...

Loan Officer logs onto Mifos and navigates to client A's Active Normal Loan.
Mifos displays Transaction History. Loan Officer confirms that the disbursement has been recorded and appropriate fees are applied under 1 Receipt ID. Currently the Receipt ID is not displayed in Transaction History.
LO then navigates to client B's approved Advance Loan that the disbursement has been recorded and appropriate fees are applied under 1 Receipt ID.

...

Loan Officer selects a client whose Normal and Advance Loan are still Pending Approval.
Loan Officer goes to view that client's transaction history and sees that disbursement has not yet occurred.
Loan Officer selects a client whose Normal and Advance Loan are paid but not yet due for disbursal. I don’t understand how a loan can be “paid”. Can you clarify? Previous loans are fully-paid, there is an approved loan but the approved loan is not yet due for disbursement.
Loan Officer goes to view that client's transaction history and sees that disbursement has not yet occurred.

...

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 Complete. ignore? Else, accept the row, and continue doing error checks. If Transaction Type is Pay Utility, process as loan repayment or savings deposit. If Transaction Type = “Business Payment to Customer” and Details start with "Payment to", process as disbursements. Else, Ignore record.	Must Transaction Type and Details both match in order to process? Artur, for now assume yes. We assume they match. :) No harm in double-checking Safaricom, I guess.
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.
1.9	P1	Compare Withdrawn Amount to Client's Approved Loan Amount less loan fees due at time at disbursement. If match found, process the disbursal. 1. Change loan status to Active in Good Standing. 2. Create disbursal transaction record. 3. Apply applicable loan fees due at time of disbursement. Create fee transaction records. 4. Use the MPESA receipt ID and transaction date for all transaction records created. If no match found, then Mifos throws this error message: Row <#> error - <Receipt ID> - No approved loans found for client with mobile number <mobile number> and loan amount <withdrawn>. If more than 1 match found, then Mifos throws this error message: Row <#> error - <Receipt ID> - More than 1 loan found for client with mobile number <mobile number> and loan amount <withdrawn>.	Basically, first check client has one loan that's approved. If yes, then if Withdrawn Amount matches Loan Amount minus Loan Fees that have been applied due at time of disbursement, then accept, and process as if loan was disbursed and fees were paid. If the amount does not match, throw 2nd error message listed to the left. If client does not have one loan that's approved, and client has more than 1 loan, then throw 2nd error message If not, and client has no approved loans, then throw 1st error message.
1.10	P1	Ignore Transaction Party Details for disbursements.
1.11	P1	See Column Definitions below 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 <# error> rows contained errors and were not imported Total amount of repayments/deposits imported: <total # imported> Total amount of disbursements imported: <total amount of disbursements> Total amount of transactions with error: <total # error> List all rows with error messages in red
1.14	P1	User can then either Click on Edit import information to go back to previous screen and upload new file. Continue with import and `Submit.` 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.
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

Since we are using the same plugin to import a file for both disbursals and repayments, I would have liked this table to contain validations and details for all. That would have been nice. That's what I did when I did the repayments and you asked me to separate them to avoid confusion. I trust you can put them together now. :) oops ;)

Column Name	Required	Description	Validations	Action	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. This receipt ID should be recorded for disbursement and loan fees transaction.	Check that there is not an existing RECEIPT ID	Import	Alphanumeric	Y29DW127	Transaction Details - Receipt ID
Date	Yes	Payment date	None	Import	YYYY-MM-DD time	2009-08-26 12:39:42	Transaction Date
Details	No	Contains details on transactions, including approval. Since disbursement approvals are in M-PESA, it would be good to record this in Mifos.	If starts with “Business Payment Charge”, ignore the entire record. If starts with “Payment To”, process as disbursement.	Import	Note	Payment received from 0715575007 - BRENDA OUMA Acc. 27851595 (Payment) Payment to 0721472293 - HENRY OFULA. Initiated by operator Elizabeth Wanjiru. Finalized by operator Chris Zintel (Disbursement or Savings Withdrawal) Business Payment Charge. Initiated by operator Elizabeth Wanjiru. Finalized by operator Chris Zintel (Loan officer cash deposit)	None
Status	Yes	Completed	Must be Completed, anything else IGNORE	Evaluate	Completed, Attempted, Cancelled, Declined	Completed
Withdrawn	Yes	Amount paid from MFI	If Transaction Type = “Business Payment to Customer” and Details start with “Payment to”, then require amount. Find the client loan using this amount. Withdrawn = Loan Amount minus loan fees due at disbursement.	Evaluate	Numeric	-22,000	ABS (Loan amount minus Total Loan Fees due at disbursemen)
Paid In	No	Transaction amount paid to MFI	Ignore this for disbursements.	Ignore	N/a	N/a	N/a
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	If Pay Utility, process as loan repayment or savings deposit If Business Payment to Customer, process as disbursement	Evaluate	Pay Utility, Business Payment to Customer	Business Payment to Customer	N/A
Other Party Info	Yes	Safaricom generated phone number and name associated with phone number	First 10 digits must correspond to a Phone Number	Evaluate	Numeric	0722926212 - CYNTHIA OMONDI	Phone Number
Transaction Party Details	Optional	Free form field that can be used by MFI For disbursement, this will be blank. For payment, this will include short names of product(s) applied to the client's accounts if it is different than the default order set in ImportTransactionOrder For savings withdrawal, it should be savings product code. This field will always have XXXX NN/Group Code and Client Number. This is information will be used to find the customer if the phone number does not match.	N/a	Ignore	N/a	N/a	N/a

...

Does the feature affect any existing reports?

Yes. Please add the following 2 blank columns to the Group Collection Sheet.

1. Requested Loan Amount
2. Client Signature
3. Phone Number
4. Goverment ID (for KEEF, this column will have the valie of the Kenya National ID)

Does the feature require adding any new reports?

Yes. MPESA Loan Extract Summary Report

This report should show the following:

i.          Total amount of disbursements export.
ii.         Total No. of records successfully exported
iii.        Warning/alert messages where one phone number has more than 1 disbursement for the same loan.
iv.        List of errors with the following information

Record # (loan account No.)
Receipt #
Error Description
Phone #

Performance

...

The Data Encoder should apply Insurance Fee and Loan Form Fee - XXXX. Emily- - If this needs to be enforced in Mifos, then these fees should be attached to the product definition. Otherwise this should be enforced via processes/training. Jakub: No need to consider this requirement in your estimates
The Advance Loan Fee should not be applied to Normal Loans. * Emily- - again, needs to be handled via process/training. No way to automate this. Jakub: Please ignore this as while
User should not be allowed to have 2 outstanding Normal Loans. This can be handled by current functionality. Do not estimate. Can be done for product mix probably
Loan Amount should be within the range defined in the product definition.
Intereset rate should be within the range defined in the product definition.

...

Advance Loans are created and approved in Mifos.
Appropriate fees are attached to the loans.

Validations

Emily- - None of the validations below are specific to this feature; please ignore for estimations

The Data Encoder should apply Advance Loan Fee to the Advance Loan.

...

Use Case - Fees not Selected by Data Encoder

Emily- - Don't understand this use case. Right now, when you create a loan product, you can specify the fee type asssociated with the product. Either you can use this feature to enforce fees or process/training. We can make fees required for all loan accounts. Jakub: For your estimates, please ignore this use case # During saving of loan creation, user does not select fee type and leave this section blank.

Marie Q: But we canNOT make fees required for a specific product. Is that right?

Mifos should throw an error as this is a mandatory field when posting a loan.

...

Use Case – Data Encoder posts one loan twice

urg. We need to think through this much more carefully- - how we define this restriction, etc. Jakub: Please ignore for now. Kay- - can we use product mix for this # Create a Normal Loan, Approve, and Disburse.

Create another Normal Loan and try to save the loan.
Mifos should display error message “Client has an existing loan pending approval”

...

Use Case - Data Encoder picks wrong Fee type

Emily: How does the system know whether/not this is the right fee type? You need a use case for how to define the right fee type per product. If we're starting to look at changing loan product definition and fee process- - that's BIG WORK and will be way out of scope. Jakub: For now, please ignore these requirements

Data Encoder create a Normal Loan
At "apply fees", Data Encoder selects Advance Loan Fee
Mifos should throw an error message “ wrong fees type”

...

Only loans with phone numbers will be approvable.
User should not be able to approve a loan where client has phone and nationa ID number equals to "NULL"

The way this use case is written - it will require work to the Loan Product Definition pipeline, and we’ll need to think through a good way to implement it, so it’s generic. Could you simply make client phone number and national ID required for all clients? Jakub: For now, ignore this requirement - this use case has been changed above.

Export Loan Disbursals

Use Case - Data Encoder Exports a file for loan with status="Application Pending Approval"

Emily- - I’m confused on this use case. Shouldn’t it be that the export only includes approved loans? YES. So there’s no need for this use case? YES. Not for development but for KEEF's testing later.

Actors:

Data Encoder

Preconditions

...

Use Case - Extract times out

Don't think this will happen

The report times out before completed.

...

Report half-created or not created. User is able to re-run the report with complete list of data.

Emily - Is there a way to make the export atomic ie, either the entire thing is exported or it fails completely. We don’t want anything half-created

Versions Compared

Old Version 49

New Version 50

Key

User Stories

Definitions and Terminology

Export Mifos Loan Disbursals

Use Case - Administrator sets up M-PESA Disbursement Limit

M-PESA Loan Disbursals Export Columns and Description

Import Loan Disbursal and Fee Transactions

M-PESA Import Columns and Description

Performance

Use Case - Fees not Selected by Data Encoder

Use Case – Data Encoder posts one loan twice

Use Case - Data Encoder picks wrong Fee type

Export Loan Disbursals

Use Case - Data Encoder Exports a file for loan with status="Application Pending Approval"

Use Case - Extract times out

Page Comparison

Versions Compared

Old Version 49

New Version 50

Key

User Stories

Definitions and Terminology

Export Mifos Loan Disbursals

Use Case - Administrator sets up M-PESA Disbursement Limit

M-PESA Loan Disbursals Export Columns and Description

Import Loan Disbursal and Fee Transactions

M-PESA Import Columns and Description

Performance

Use Case - Fees not Selected by Data Encoder

Use Case – Data Encoder posts one loan twice

Use Case - Data Encoder picks wrong Fee type

Export Loan Disbursals

Use Case - Data Encoder Exports a file for loan with status="Application Pending Approval"

Use Case - Extract times out