Troubleshooting Historical Data

Where to find history?

Following is the list of tables and explanation of what kind of history is found in it.

a)     Account_status_change_history

This is the table where you find the status change history of all types of accounts.

The table structure includes fields like

ACCOUNT_ID

OLD_STATUS

NEW_STATUS

CHANGED_DATE

So against an account, you can find multiple entries since an account has to go various stages. In order to find out the status of account at a particular date, the changed date field can be used.

This is done by finding out the last entry of the account till the date and checking the new_status field to know the current status.

b)    Change_log and change_log_detail

These are two important tables of MIFOS where you can find the history of multiple entities.

The main structure of the table is like that

ENTITY_ID

ENTITY_TYPE

CHANGED_DATE

The various entity types available for this table are

1          Client

2          LoanProduct

3          SavingsProduct

4          ProductCategory

5          ProductConfiguration

6          Fees

7          Accounts

8          Admin

9          Checklist

10         Configuration

11         Customer

12         Group

13         Login

14         Meeting

15         Office

16         Penalty

17         Personnel

19         Roleandpermission

20         Center

21         Savings

22         Loan

23         BulkEntry

Based on your entity types, you can find the particular entity_id

For example,

If the entity type is client then entity_id is customer_id, if entity type is personnel then entity_id is personnel_id and so on.

Based on the entry in change_log, you will find the detailed corresponding entry in change_log_detail.

From there you can find the old and new values.

c)     Account_payment,Account_trxn, loan_trxn_detail, fee_trxn_detail

These are the key tables to find out the transactional history. The key structure of account_trxn table is

ACCOUNT_ID

PAYMENT_ID

PERSONNEL_ID

ACCOUNT_ACTION_ID

AMOUNT

DUE_DATE

ACTION_DATE

CREATED_DATE

CUSTOMER_ID

INSTALLMENT_ID

RELATED_TRXN_ID

With the fields due_date and action_date, you will come to know about the due and actual payment dates. With account_action_id you will know the type of transaction as follows

The amount field will give the total amount. In order to view its break up in principal and interest, you need to go in loan_trxn_detail. For fees, correspondingly go in fee_trxn_detail. In order to know type of payment against the transaction, go to account_payment for it.

Where history missing/incomplete in MIFOS?

MIFOS does not track history for some of the entities and data files. A few such cases are listed below

• There is no provision track the loan counter history. Loan counter always gives the current counter against the client.

• Change_log_detail contains the old and new values as display names not the ids. Problem occurs when both names are same (that could be possible).

• There is no link between the reversed loan and the new loan created after the reversal .Ideally MIFOS should have the provision to link the parent account of the redone loan.

• Customer status changes are localized

• Office hierarchy changes are not audited in Mifos

• Office names are not ensured to be unique in Mifos.

• Can't determine the order of customer hierarchy and status changes

• account_status_change_history entries based on system date rather than transaction date

Where all it is mandatory to migrate history?

You need to have at least one entry in the following tables especially for reporting section. The reason for it is explained in next section.

i)              Account_status_change_history

ii)             Change_log

iii)            Change_log_detail

iv)            Account_trxn

v)             Loan_trxn_detail

vi)            Fee_trxn_detail

vii)           Account_payment

Functionalities and fields affected where history of transactions missing

Whenever you try to run reports with historical dates (dates before the MIFOS go live date), the reports data won’t be accurate and correct.

In order to enable the reports run historically, you need to look into history tables every time to find out the status or branch or person etc. associated with the entity at the selected time.

Fields like:

Active accounts

Active clients

Active centers

Active groups

are affected.

When data is entered through MIFOS, proper entries go in all these tables but during the data migration process, there is a possibility that the client has decided to skip migration for some entities to reduce migration complexity.  Reports run on such entities for historical dates may give unexpected results.

When transactions entries are missing for the migrated loans/savings about repayments or deposits, it’s very difficult to find out fields like

Outstanding amounts

Overdue amounts

Collected Amounts

Savings balances etc.

at any point of time.

• When transactions are missing for migrated loans, reverse and redo of loan functionality creates havoc. Amounts of reversed loans do not get reversed and it’s very difficult to give the correct numbers.

Reports affected when history missing?

The following reports getting affected:

a)     Aging report

b)    Outstanding report

c)     Demand collection reports

d)    Organizational status reports