Hi!

I have a requirement to suppress insertion of records into fee_trxn_detail table and I am trying to make code changes in order to effect this. I have explained below the steps followed by me. As I am new to working with the mifos source code, I request one of the core developers to evaluate the changes made by me.

Objective –

I have a onetime fee applicable to loans to be paid at the time of disbursement. When this fee is paid, entries must be inserted in account_payment, account_trxn and loan_trxn_detail tables but not in fee_trxn_detail.

Analysis –

·         As the fee is paid at the time of disbursement, I started with the disburseLoan function in LoanBO.java.

·         The function processFeesAtDisbursement is called from within this function.

·         Within the function processFeesAtDisbursement, I commented the line accountPayment.addAccountTrxn(loanTrxnDetailEntity).

Observation –

When the fees is paid, no entry is made in the account_payment, account_trxn, loan_trxn_detail and fee_trxn_detail tables.

Understanding –

Insertion of records into the 4 tables – account_payment, account_trxn, loan_trxn_detail and fee_trxn_detail is coupled. It is not possible to suppress insertion into one of these tables alone.

Could you please confirm if my understanding is correct or am I missing out on something here?

Thanks and regards,

Seetha Rajagopalan • Engineer - Product Development • Mifos • SunGard • Global Services

Embassy Icon, 3 Infantry Road, Bangalore 560 001, India

Main +91 (80) 3091 3000 X3234

P Think before you print
CONFIDENTIALITY: This e-mail (including any attachments) may contain confidential, proprietary and privileged information, and unauthorized disclosure or use is prohibited. If you receive this e-mail in error, please notify the sender and delete this e-mail from your system.

John,

I was able to pin point that the issue was due to the presence of multiple entries in the dw_loan_schedules table for some accounts, although I am still trying to figure out why this would give rise to a duplicate entry error. While trying to delete these multiple schedule entries, I stumbled upon a scenario which is not being handled by the ETL at present. I have explained the issue and discussed the fix I developed below.

·         Upon discussion with my client, I came to know that the changes made to the schedule dates were not because of the stored proc used by them.

·         The changes had occurred due to disbursement being made on a date later than the initial disbursement date.

·         When the disbursement is made on a different date, mifos adjusts the entire schedule according to the new disbursement date.

·         In this process, mifos deletes the existing schedule entries and adds new entries to the loan_schedule table.

·         The ETL currently makes incremental changes to the loan schedule entries by comparing the id in dw_loan_schedules table of the warehouse database to the id in loan_schedule table of the mifos database.

·         As the old entries have been deleted, the ETL is unable to update this entry and instead, inserts a new entry with the modified schedule date.

·         These multiple entries were later giving rise to duplicate entry error (discussed below) while running the transformation to insert historical balances and arrears.

·         This issue also gives rise to rows being inserted into the hist_loan_arrears table for these accounts as it considers the old schedule entries for arrears computation. This is wrong as there are no arrears.

·         The arrears wouldn’t have gotten calculated had the old entries been updated correctly without new entries being inserted.

Fix –

The issue arises primarily due to the fact that the ids from the 2 loan schedule tables do not match in this scenario. Hence, I have ruled out this comparison by altering the key values to be matched as loan_account_id and installment_id. As this combination remains unaltered even when the id changes, the transformation works fine even in this scenario.

Could you please confirm if this fix would be the right approach to resolve this issue?

I would also like to discuss another constraint I observed while trying to incrementally update the dw_loan_schedules table.

·         The last_updated_date column in the loan_schedule table gets updated/over written for every row even when a modification/updation is done only to a single installment of the account.

·         For a client with a huge database, it would be inconvenient to populate the warehouse using the live production database. Hence, we take regular backups and use these in a test server to set up the warehouse.

·         For a backup taken on, say, 23/01/2012, the last_updated_date for a particular loan account was displayed as 19/01/2012 (date closest to the date when the backup was taken and a change was last made to the account) for all the installments. However, the schedule dates of this account had been modified around 2 months before.

·         I was able to detect this change only when populating the warehouse for parameter date 19/01/2012.

·         This dependency on the last_updated_date column has forced us to use weekly backups to populate data so that changes can be tracked as and when they had been made.

·         According to my understanding, had the last_updated_date not been modified for all the records, it would have been easier to capture the changes made installment wise.

Could you please validate the points mentioned above and correct me if my understanding in this case is wrong?

Thanks and regards,

Seetha Rajagopalan • Engineer - Product Development • Mifos • SunGard • Global Services

Embassy Icon, 3 Infantry Road, Bangalore 560 001, India

Main +91 (80) 3091 3000 X3234

P Think before you print
CONFIDENTIALITY: This e-mail (including any attachments) may contain confidential, proprietary and privileged information, and unauthorized disclosure or use is prohibited. If you receive this e-mail in error, please notify the sender and delete this e-mail from your system.

Seetha,