[Mifos-developer] accounting export snag

Sam Birney

unread,

Dec 1, 2009, 11:04:00 AM12/1/09

to Developer

Hi all,

I've made good progress fixing various accounting bugs and testing with the finance department, and I think I am down to three remaining known issues. Two of them I know how to solve so it is only a matter of time. However, one of them may be a subtle gap with Mifos that I only now understand.

First some background:

(I'm omitting any fee complexities that aren't directly relevant to this problem)

For the most part, Al Majmoua has two types of loan fees: for example, a $5 fixed loan insurance fee and a 2% overhead fee. The $5 goes into one account meant to cover write-offs. If, for instance, a client dies during a loan, the loan is written off, and they even give some money to the family out of this account for helping with funeral costs. Meanwhile, the 2% fee goes back into general operations budget and is meant to cover some of the operational costs for servicing the loan. So, it is important for them to track these separately in accounting, so they can know if they are covering their expenses in each of these two categories. Let's say we are disbursing a $2000 loan for the sake of example.

How Mifos works:

The FINANCIAL_TRXN table has one row for each double-entry accounting action, i.e. a debit and credit for each movement of money. So, if I disburse a loan that has the two above fees (which are deducted from the check at disbursal time), there is now a debit and credit row for the loan disbursal itself, and there are 4 rows representing the fees in this table, one debit and one credit for each of the $5 fee repayment and the 2% fee repayment. So far so good.

Now for fees. There is a FEES table that holds the definitions, including the fee_name and amount calculation, so it would have two rows, one for a $5 fixed fee and one for a 2% formula fee. There is an ACCOUNT_FEES table which holds the actual amounts of fee instances, so it would have a row with an amount of 40.0 and a row with an amount of 5.0 in this example. Then there is a FEE_TRXN_DETAIL table that joins the ACCOUNT_FEES entries with the ACCOUNT_TRXN table via the ACCOUNT_TRXN_ID.

The problem:

FINANCIAL_TRXN joins to ACCOUNT_TRXN by ACCOUNT_ID. Similarly, FEE_TRXN_DETAIL joins to ACCOUNT_TRXN by ACCOUNT_ID. So, when I join the two sides in this example I get eight rows for fees, and half of the rows are incorrect pairings of the $5 actions with the 2% fee descriptions and vice-versa. I might be brain dead from staring at this too long, but I don't see any way to tell which half are the correct rows and which are the incorrect. The only way I see this working as desired for this use case is if the FEE_TRXN_DETAIL table actually joined to the more specific FINANCIAL_TRXN table instead of through the less specific ACCOUNT_TRXN table.

So, any ideas appreciated...

thanks,
Sam

John Woodlock

unread,

Dec 1, 2009, 7:17:50 PM12/1/09

to Mifos software development

Hi Sam

You are right. Its not easy or obvious how to get back from financial_txrn to the actual entry in fee_trxn_detail (or loan_trxn_detail either to see the split between principal and interest). I think (and I might be wrong) that there should be a table called ACCOUNT_TRXN_DETAIL which would be a supertype of CUSTOMER_TRXN_DETAIL, FEE_TRXN_DETAIL and LOAN_TRXN_DETAIL). That would be related to FINANCIAL_TRXN rather than, as currently, ACCOUNT_TRXN.

ACCOUNT_TRXN -> many->
ACCOUNT_TRXN_DETAIL ->2->
FINANCIAL_TRXN

Anyway, does the following sql help your immediate problem at all.
(i.e. specify the fin_action_id and match the amounts which is, of course, very ugly)

select fin.*, at.account_trxn_id, at.account_id, at.payment_id, at.amount,
ft.fee_trxn_detail_id, ft.account_trxn_id, ft.account_fee_id, ft.fee_amount from account_trxn at
join fee_trxn_detail ft on at.account_trxn_id = ft.account_trxn_id
join financial_trxn fin on fin.account_trxn_id = at.account_trxn_id
where fin.fin_action_id = 3
and fin.posted_amount = ft.fee_amount

John

------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing.
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev

Kay Chau

unread,

Dec 1, 2009, 8:02:45 PM12/1/09

to Mifos software development

Hi Sam,

I dug a little deeper into this and see what you’re saying. There is nothing unique about what’s saved in regards to each fee and the transactions in the table.

I see a couple of workarounds for this that may work …

One is you can set up those fees to save to separate unique GL accounts. I know you are not using the GL, but if you can just save it to different accounts, then you can join on those tables since fees and fin_trxn both have the GLCODE_ID saved.

Another is the fee_trxn_detail table does tell you the exact fee_amount for that transaction. It is hacking a bit but can you infer from those amounts saved which amount belongs to which fee, and then when you take the transaction detail from fin_trxn, you can just match the amount you expect to the right fee? Hope that makes sense..

Sam Birney

unread,

Dec 2, 2009, 6:00:53 AM12/2/09

to mifos-d...@lists.sourceforge.net

Thanks John & Kay,

you guys are smart cookies. John, I like your long-term table
redesign proposal. As for the short-term workarounds you suggest:

1) I thought about this too, and joining based on the amount should
work most of the time, unless they have a 2% fee on a $250 loan, which
I think would then break us. That might really happen, or sometimes
they have a $10 fixed fee or different percentages, and may add new
product configurations with different fees in the future, so as
tempting as this approach is it should probably be avoided.

2) Kay, using different GL codes for each fee sounds like a brilliant
workaround, I'm going to try that. Hopefully I don't have to tunnel
underground to avoid any trapdoors, we'll see ;)

thanks much!

Sam Birney

unread,

Dec 2, 2009, 8:41:15 AM12/2/09

to mifos-d...@lists.sourceforge.net

So...

I updated my mifosChartOfAccounts.custom.xml in the following way:

and made sure the value in my mifosBeanConfig.custom.xml under both
actionToDebitAccount and actionToCreditAccount matched the top-level
Fees-Received element:

Then I changed my selenium script that creates the desired fees, using
the following data:

<code>
fee_codes = {
"$5 Insurance Fee":"7132000",
"$10 Insurance Fee":"7132001",
"$30 Minimum Fee":"7132002",
"$60 Maximum Fee":"7132003",
"$100 Maximum Fee":"7132004",
"Overdue Principal":"7132005",
"Overdue Interest":"7132006",
"2% Loan Fee":"7132007",
"2.5% Loan Fee":"7132008",
"3.5% Loan Fee":"7132009",
}
</code>

Then I refreshed my database, started my mifos instance again, ran my
script to populate fees, and then disbursed some test loans.
Amazingly, the FINANCIAL_TRXN.GLCODE_ID column was now being populated
with the correct codes for credit actions. However, for debit actions
it was always populating the code corresponding to 7132009. This
seems like some sort of bug. The first loan I created had a 3.5% fee
and a $5 fee, but for the subsequent test loans I used other fees,
like 2% and $5, or 2.5% and $5. It was consistently using the right
codes for credits, but consistently using 7132009 for debits.

This seems like a bug that perhaps I am the first to stumble into. I
delved into the code and eventually made a change in
FeesAccountingEntry.java. Following the example for credits that
seemed to be working, I replaced

<code>
addAccountEntryDetails(feeTrxn.getFeeAmount(),
finActionFee, getGLcode(finActionFee
.getApplicableDebitCharts()),
FinancialConstants.DEBIT);
</code>

with this

<code>
addAccountEntryDetails(feeTrxn.getFeeAmount(),
finActionFee,
feeTrxn.getAccountFees().getFees().getGlCode(),
FinancialConstants.DEBIT);
</code>

Now it all seems to work, and I have a way to join financial_trxn rows
with their corresponding fees rows.
Phew.
Will you please accept this patch for Mifos?

thanks,

Jeff Brewster

unread,

Dec 2, 2009, 6:17:08 PM12/2/09

to Mifos software development

Hi Sam,
Thanks for this. I've logged this
https://mifos.dev.java.net/issues/show_bug.cgi?id=2605 to track the
issue.

Jeff

Reply all

Reply to author

Forward