The ETL job should copy Copy over the simple 'dimensions' such as products,
currencies currencies, offices, personnels correctly

Note:
Notes It is possible to create queries to test this more exactly.  
I didn't this time because I've used this simple area a lot and chose
to only do inspection so as to spend more time on the riskier areas
 areas  (customers, accounts and transactions).

Setup up an office hierarchy, some loan and savings products, some system users (loan officer and non loan officers) and run ETL

Pass.  
Looked good on simple inspection using queries and also current reports.

Handle being run against an initialised mifos database

Note:
This requirement is just in case this edge case produced unexpected results.  When testing, had to ensure the
 ETL job points to the correct source and destination database names by inspecting and editing the initialise dataware house 'bat' file I used and the jndi settings for kettle and the jndi setting for the

bi server

(for running reports)

run ETL against an initialised mifos 1.6.1 database

Pass

Handle simple hierarchy (loan officer, center, group, client, loan/savings account) creation

.  
Involves handling new entries and catering for status changes when making groups, clients and accounts active.

Setup a center hierarchy down to clients having loan and savings accounts
Result

Pass.  All new customers and accounts are added to the datawarehouse as expected

Handle when a client without a group membership is created and has its status changed and has a loan and a savings account.

Client:  BO 1 / client w/o group mem xxx 0003-000000028
Savings: savings product 1

 000100000000052 
Loan: loan prod 1 #

000100000000053

Pass.

New client and accounts are added to the datawarehouse as expected

Handle when a group without center hierarchy is created and has its status changed and has a client with a loan and a savings account and has its own savings and loan account as well.

Group: group w/o center hierarchy 0004-000000029 
Group Loan: group load # 000100000000055 
Group Savings: group savings product # 000100000000056 
Client: client of group w/o center xxx 0004-000000030
loan: loan prod 1 # 000100000000059
Savings: savings product 1 #

000100000000058

Pass.

New group, client and all accounts are added to the datawarehouse as expected

Handle when a center has a savings account.

Center: center with savings a/c 0004-000000031
savings: center savings #

000100000000061

Pass. Center and savings account added as expected.

Handle moving a client from one group to another in the same center.

Group: group dw1 1 - 0003-000000002
client: xfer to gr in center xxx - 0003-000000017
Savings account: savings product 1 # 000100000000036 
transfer to:  group dw1 2

Pass. Client entry added to reflect new group. and  savings entry added to link to new client entry.

Handle moving a client from one group to another in the same branch

Group: group dw1 1 - 0003-000000002
client: xfer to gr in same branch xxx - 0003-000000023
loan prod 2 # 000100000000044
transfer to: BO 1 / center 2 / group 1 for center 2

Pass. Client entry added to reflect new group. and  loan entry added to link to new client entry.

Handle moving a client from one group to another in a different branch

Group: group dw1 1 - 0003-000000002
client: xfer to gr in different branch xxx - 0003-000000024
savings product 1 # 000100000000046 
transfer to: br2 / br2 center 1 / br2 group 1

Pass.  Client entry added to reflect new group. and  savings entry added to link to new client entry.

Handle removing group membership from a client.

client: client remove group membership xxx  0003-000000050
loan: loan prod 1 # 000100000000091 
moved to 'sturty' loan officer

Fail.  The data is ETL's but the outcome looks as if the client had not group membership from the start.  
Problem is a difficulty in picking up a new client that was a group member and then has membership
removed because it looks like a client that was w/o group membership from the start.
MIFOS-3871                  

Handle moving client with no group membership to another branch.

Client: client w/o gr memb xfer to branch xxx 0003-000000045
Client Savings: savings product 1 # 000100000000082

Pass.  Client and savings account show application of 'Loan Officer Assigned', 'Branch membership'
and 'Status' changes associated with a branch membership change. (in mifos Loan Officer is nulled out
and client status is changed to 'on hold')

Handle a client with no group membership becoming a group member.

Client: client add back to group hierarchy after xxx 0003-000000051
Savings: savings product 1 # 000100000000093

Pass.

Handle a Loan Officer change for a client with no group membership.

Client: client w/o grp memb hier LO change xxx 0003-000000044
Client Savings: savings product 2 # 000100000000080 
changed 'smith' to 'xxx'

Pass. Client and Savings entries as expected.  As LO change was done on same day as client and
savings creation...  the LO change is treated as if it happened before the client active status and
before the savings account was created (because changes on the same day are appled in order of
new, hierarchy then status changes and centers are processed before groups before clients
before accounts)

Handle a Loan Officer change for a group with no hierarchy.

Group: group w/o center hier LO change 0003-000000042 
Group Savings: group savings product # 000100000000076
Client: client for group w/o center hier LO change xxx 0003-000000043
Client Loan: loan prod 1 # 000100000000078 
changed 'smith' to 'xxx'

Pass. Group, client and account entries as expected.  As LO change was done on same day as
group, client and account creation... the LO change is treated as if it happened before the group
active status and before the client and accounts were created (because changes on the same
day are appled in order of new, hierarchy then status changes and centers are processed before
groups before clients before accounts)

Handle moving group with no hierarchy from one branch to another.

Group: group w/o center hier xfer branch 0003-000000046 
Group savings: group savings product # 000100000000084
Client: client of group w/o center

hier xfer branch xxx 0003-000000047
Client Loan: loan prod 1 #

000100000000086

Fail.  Mifos audit bug - should audit like moving a client to another branch but doesnt.
MIFOS-3872              

Handle center change for a group to center in same branch

Group: group for change to center in same branch 0003-000000056 
Savings: group savings product

# 000100000000100

Pass.

Handle center change for a group to center in different branch

Group: group for change to center in diff branch 0003-000000057 
Savings: group savings product # 000100000000102 

Pass (enough). However, there is a minor mifos audit BUG where the changing of status to on-hold is not audited.
MIFOS-3873             

Handle adding a group hierarchy back to a center

Group: group going back to a center hier 0003-000000052 
Savings: group savings product # 000100000000095

Fail.  Connected to MIFOS-3871            

Handle a Loan Officer change for a center

Center: center with

a

LO change 0003-000000032
Center Savings: center savings # 000100000000067 
Group: group for center LO change 0003-000000033 
Group Savings: group savings product # 000100000000064
Client: client for center LO change xxx 0003-000000034
Client Loan: loan prod 1 # 000100000000066

Pass. center, group & client customer entries good and associated accounts good.

All savings transactions should link to the latest savings account entry that is 'open' (active or inactive but not closed) for the date matching the transaction action_date.

Saving transactions are deposits, withdrawals, adjustments and interest postings 

1. Deposit 100 into savings account.

Pass.

2. On one day - deposit 100 into a savings account, make inactive, deposit another 100 (which makes it active)
and make inactive again. Then close the account (causing a withdrawal)

Pass. All deposits and withdraws link to the latest open (inactive in this case) entry for the savings account.

All repayment transactions should link to the latest loan account entry that is 'active' (good or bad standing)for the date matching the transaction action_date

Repayment transactions are account_action_id:
1 - 'AccountAction-LoanRepayment'
9 - 'AccountAction-Adjustment'
18 - 'AccountAction-LoanReversal'

1. Disburse a loan and make a repayment (or two)

Pass.

2. Disburse a loan and make a repayment (or two) and then repay early (which closes the account). All on same day.

Pass.

3. Disburse a loan and make a repayment (or two) and then repay early (which closes the account). Then adjust the account which reopens the loan. All on same day.

Fail. No account_status_change_history entry was added by mifos so change from closed to active in good standing not identified. 
MIFOS-3479       

4. Disburse a loan and make a repayment (or two). Adjust the loan to remove the last payment.

Pass.

5. Disburse a loan and make a repayment (or two). Reverse the loan. On the same day.

Pass.

All write off transactions should link to the loan account entry having a loan writeoff status.
Write-off account_action_id is 15 - 'AccountAction-LoanWrittenOff'

Disburse a loan and make a repayment (or two). Then write-off the loan. On the same day.

Pass.

All reschedule transactions should link to the loan account entry having a loan rescheduled status.
Rescheduled account_action_id is 20 - 'AccountAction-LoanRescheduled'

Disburse a loan and make a repayment (or two). Then reschedule the loan. On the same day.

Pass.

All disbursal transactions should link to the latest loan account entry that is pre-active -
'AccountState-ApplicationApproved'standing or 'AccountState-DisbursedToLo (which is probably not used and to deprecated) for the date matching the transaction action_date

Disbursal transactions are account_action_id:
10 - 'AccountAction-LoanRepayment'
19 - 'AccountAction-DisrbursalAmountReversal' (sic)

1. Disburse a loan and make a repayment (or two) on the same day.

Pass.

2. Disburse a loan and make a repayment (or two). Reverse the loan. On the same day.

Pass.

All Customer Miscellaneous charges and penalties (that are setup on customer_schedule) should appear on the customer charges and fees table linked to an active or on-hold customer

Group: Group with lots of fees  0006-000000062  
added a misc. fee charge of 2.0 and a misc. penalty charge of 4.0 then paid using CSE.

Pass.  2 entries created on table fact_customer_fees_and_charges for the customer. The misc. fee entry had an account_charge_type_id of 2 (misc fee), and the misc. penalty had an account_charge_type_id of 3 (misc penalty).

All Customer Fees (that are setup on customer_fee_schedule) should appear on the customer charges and fees table linked to an active or on-hold customer

Group: Group with lots of fees  0006-000000062
Created a periodic fee and an upfront fee..

Pass. Entries created with an account_charge_type_id of 1 (fees)