Introduction

Al Majmoua currently handles all repayments of loans by clients by having clients deposit their repayments directly at a bank, and then Al Majmoua is able to download online a file of these recorded transactions to import their MIS.  They need the ability to do this in Mifos as well.  See related Business requirements for more detail.

User Stories (Epics)

Goals

• Ability to import file of loan repayment transaction details into Mifos
• Transaction History in Mifos accurately shows loan payments.

Non-Goals

The following items will not be addressed in this release:

• Importing bank transactions from any bank other than Audi Bank
• No import of attendance, savings, fees, or loan disbursal information - only loan repayments are imported.
• Additional logging of import than what we do already in Mifos for logging

Definitions and Terminology

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

Related Documents

• Business Requirements

Import Bank Transactions

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

Use Cases

Administrator assigns permissions to accountant to import bank transactions

Actors

•  Administrator

Preconditions

•  None

Basic Flow

1. Administrator logs onto Mifos and navigates to the Admin section. Administrator selects Manage Roles and Permissions. Administrator selects the role for accountants.
2. Mifos displays all possible permissions for the role and Administrator scrolls down to "Bulk". Administrator confirms that "Can import transactions" is checked.

Post-conditions

• Accountant role has permission to import bank transactions

Alternative Flows

• None

Validations

• None

Accountant imports transactions (Audi Bank) - 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 Audi Bank Excel 97 or Audi Bank TSV for bank, 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. Date of Transaction is in date format YYYY/MM/DD if it's a TSV file, or does not follow the expected format of that column. 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

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 loan account.
2. Mifos displays Transaction History. Loan Officer confirms that a repayment 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.

Accountant imports bank transactions (generic) - P2

Actors

• Accountant

Preconditions

•  Accountant has permissions to import bank transactions

Basic Flow

1. Accountant logs onto Mifos and navigates to the Admin section. Accountant selects Import bank Transactions.
   1. OR Accountant clicks on link in Quick Start - Import bank Transactions
2. Mifos displays new screen for Import Bank Transactions.
3. Accountant enters date of transaction (defaulted to today). and selects file from their computer for import. File follows generic format set by Mifos.
   1. Accountant can select up to 5 custom fields they want data imported from the file to map to.  Ie - they select custom Field "Serial" and enter in column name "Serial" where the import will map to that column.  No validations are done on custom field columns.
   2. 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 returns the Accountant to the Admin screen.

Post-conditions

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

Validations

• If any cell in a required column is empty  entire import is rejected with appropriate error message.
• No bad data - ie, no numbers in columns that require text and vice versa. Date of Transaction is in date format MM/DD/YYYY or does not follow the expected format of that column. See FR's below.  This is only for columns required in generic format - there is no validation done on Custom fields.

Alternative Flows

User Stories

Import Bank Transactions (Audi Bank) Functional Requirements

Add new permission

Add new screen for Import Bank Transactions (Audi Bank)

Import File Details and Validations (Audi)

Audi Bank Import Columns and Description

Loan Repayment Details

Other Assumptions

LSIM

• LSIM will be on - but should work on or off

QA Considerations

• Performance History - PAR, other values calculated correctly
• View Loan 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)

Impacts to System

Globalization/Localization

Logging

Change Log

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.

Performance

Setup and Installation

New Installations

Backward Compatibility and Upgrades

Hosting Support

Configuration

Open Questions and Notes

Answered

• we need a sample of what data can be dumped from the banking system
  • please see samples attached to MAJ:Bank Imports 
• Is it one file for the whole MFI or one file per branch office?
  • one file per currency per bank (there are currently 5 banks) - SB
• Is this one person w/ an Administrator role doing this at the HO
  • It will be more than one user in the HO accounting department, not all of which should be admins
• What is the name of their bank?
  • Audi Bank is the main one
• Is it only Audi Bank or BSL too?  Both are referenced in some of the sentences
  • it is actually 5 banks, please see MAJ:Bank Imports 
• How important is it to share what type of error it is and at what level does it need to be at if it's necessary - ie do we need to tell what row the error occurs on or can we just say generically what the error is?
  • Medium important, for each row that has an error, an accountant is going to have to examine it and manually correct it, possibly after calling a bank or branch. So, it would be better to have Mifos report which rows have errors, otherwise the accountant will have to manually scan the import files and keep trying to import until she finds all the errors.
• Does Excel format mean it won't accept Unicode (Arabic) characters?
  • Not sure, but it doesn't matter. We only need to import (loan id, bank ref #, trans date, amount), none of which will have Arabic characters.
• Typical size of file - will this have impact on performance? - Load of transactions - 1500 transactions/file?
  • please see samples attached to MAJ:Bank Imports , these are representative of a typical day. Keep in mind though, the growth projections for MAJ:Al Majmoua 
• Can we get access to the bank's website?
  • they probably won't give us their online banking password, but just let me know what you need and I will ask their accounting department.
• I deleted the spec I had in progress from mifos.org in lieu of what you said (Sam) about Al M's desire to keep information away from competitors.  We can revisit later what we can and cannot put on mifos.org
  • thank you!!!
• Do we need to have the Excel format converted first to a different format for easier/quicker development of this feature?
  • whatever is best for feature development.
• Current spec is assuming option 2 of multicurrency (products have been created and entered in respective currency)
  • interesting... let's catch up on multi-currency then, I thought option 2 was a no-go when we discussed it Monday

can we get the bank to only put loan id in the description field? No

Unanswered

• credit rows in Audi with no loan id - are these reversals, or missing loan ids?
• look at Audi USD line 202

Review and Approvals