Bulk Entry Transaction Documentation

Owned by Former user (Deleted)
Last updated: Sept 07, 2010Version comment

Starting from a simple data set (http://www.mifos.org/developers/wiki/BulkEntryInitialStateSql) we ran a bulk entry submit with one payment of 99, and recorded the database state before and after (http://www.mifos.org/developers/wiki/BulkEntrySingleTransactionScript/view) The link below gives the diff between these two snapshots.

The summary of the changes is that rows were added to the tables:

  • account_payment
  • account_trxn
  • customer_attendance
  • financial_trxn
  • loan_activity_details
  • loan_trxn_detail

Updates were made to the following tables:

  • loan_schedule
    • PRINCIPAL_PAID: 0.0 --> 97.800
    • INTEREST_PAID: 0.0 --> 1.200
    • PAYMENT_DATE: null --> 2008-12-04
    • VERSION_NO 0 --> 1
  • loan_summary
    • PRINCIPAL_PAID: 0.0 --> 97.800
    • INTEREST_PAID: 0.0 --> 1.200

Database changes for a transaction (tables with updates have updated rows marked with ###, tables with no updated rows are inserted)

<table_data name="account_payment">
<row>
<field name="PAYMENT_ID">7</field>
<field name="ACCOUNT_ID">4</field>
<field name="PAYMENT_TYPE_ID">1</field>
<field name="CURRENCY_ID">2</field>
<field name="AMOUNT">99.000</field>
<field name="RECEIPT_NUMBER"></field>
<field name="VOUCHER_NUMBER" xsi:nil="true" />
<field name="CHECK_NUMBER" xsi:nil="true" />
<field name="PAYMENT_DATE">2008-12-04</field>
<field name="RECEIPT_DATE" xsi:nil="true" />
<field name="BANK_NAME" xsi:nil="true" />
</row>

 <table_data name="account_trxn">
<row>
<field name="ACCOUNT_TRXN_ID">7</field>
<field name="ACCOUNT_ID">4</field>
<field name="PAYMENT_ID">7</field>
<field name="PERSONNEL_ID">1</field>
<field name="ACCOUNT_ACTION_ID">1</field>
<field name="CURRENCY_ID" xsi:nil="true" />
<field name="AMOUNT_CURRENCY_ID">2</field>
<field name="AMOUNT">99.000</field>
<field name="DUE_DATE">2008-12-11</field>
<field name="COMMENTS">Payment rcvd.</field>
<field name="ACTION_DATE">2008-12-04</field>
<field name="CREATED_DATE">2008-12-05 17:51:25</field>
<field name="CUSTOMER_ID">3</field>
<field name="INSTALLMENT_ID">1</field>
<field name="RELATED_TRXN_ID" xsi:nil="true" />
</row>

<table_data name="customer_attendance">
<row>
<field name="ID">1</field>
<field name="MEETING_DATE">2008-12-04</field>
<field name="CUSTOMER_ID">3</field>
<field name="ATTENDANCE">1</field>
</row>
<row>
<field name="ID">2</field>
<field name="MEETING_DATE">2008-12-04</field>
<field name="CUSTOMER_ID">4</field>
<field name="ATTENDANCE">1</field>
</row>
<row>
<field name="ID">3</field>
<field name="MEETING_DATE">2008-12-04</field>
<field name="CUSTOMER_ID">5</field>
<field name="ATTENDANCE">1</field>
</row>

 <table_data name="financial_trxn">
<row>
<field name="TRXN_ID">13</field>
<field name="ACCOUNT_TRXN_ID">7</field>
<field name="RELATED_FIN_TRXN" xsi:nil="true" />
<field name="CURRENCY_ID" xsi:nil="true" />
<field name="FIN_ACTION_ID">1</field>
<field name="GLCODE_ID">7</field>
<field name="POSTED_AMOUNT">97.800</field>
<field name="POSTED_AMOUNT_CURRENCY_ID">2</field>
<field name="BALANCE_AMOUNT">97.800</field>
<field name="BALANCE_AMOUNT_CURRENCY_ID">2</field>
<field name="ACTION_DATE">2008-12-04</field>
<field name="POSTED_DATE">2008-12-05</field>
<field name="POSTED_BY">1</field>
<field name="ACCOUNTING_UPDATED">1</field>
<field name="NOTES">Payment rcvd.</field>
<field name="DEBIT_CREDIT_FLAG">0</field>
</row>
<row>
<field name="TRXN_ID">14</field>
<field name="ACCOUNT_TRXN_ID">7</field>
<field name="RELATED_FIN_TRXN" xsi:nil="true" />
<field name="CURRENCY_ID" xsi:nil="true" />
<field name="FIN_ACTION_ID">1</field>
<field name="GLCODE_ID">13</field>
<field name="POSTED_AMOUNT">-97.800</field>
<field name="POSTED_AMOUNT_CURRENCY_ID">2</field>
<field name="BALANCE_AMOUNT">-97.800</field>
<field name="BALANCE_AMOUNT_CURRENCY_ID">2</field>
<field name="ACTION_DATE">2008-12-04</field>
<field name="POSTED_DATE">2008-12-05</field>
<field name="POSTED_BY">1</field>
<field name="ACCOUNTING_UPDATED">1</field>
<field name="NOTES">Payment rcvd.</field>
<field name="DEBIT_CREDIT_FLAG">1</field>
</row>
<row>
<field name="TRXN_ID">15</field>
<field name="ACCOUNT_TRXN_ID">7</field>
<field name="RELATED_FIN_TRXN" xsi:nil="true" />
<field name="CURRENCY_ID" xsi:nil="true" />
<field name="FIN_ACTION_ID">2</field>
<field name="GLCODE_ID">41</field>
<field name="POSTED_AMOUNT">1.200</field>
<field name="POSTED_AMOUNT_CURRENCY_ID">2</field>
<field name="BALANCE_AMOUNT">1.200</field>
<field name="BALANCE_AMOUNT_CURRENCY_ID">2</field>
<field name="ACTION_DATE">2008-12-04</field>
<field name="POSTED_DATE">2008-12-05</field>
<field name="POSTED_BY">1</field>
<field name="ACCOUNTING_UPDATED">1</field>
<field name="NOTES">Payment rcvd.</field>
<field name="DEBIT_CREDIT_FLAG">1</field>
</row>
<row>
<field name="TRXN_ID">16</field>
<field name="ACCOUNT_TRXN_ID">7</field>
<field name="RELATED_FIN_TRXN" xsi:nil="true" />
<field name="CURRENCY_ID" xsi:nil="true" />
<field name="FIN_ACTION_ID">2</field>
<field name="GLCODE_ID">7</field>
<field name="POSTED_AMOUNT">1.200</field>
<field name="POSTED_AMOUNT_CURRENCY_ID">2</field>
<field name="BALANCE_AMOUNT">1.200</field>
<field name="BALANCE_AMOUNT_CURRENCY_ID">2</field>
<field name="ACTION_DATE">2008-12-04</field>
<field name="POSTED_DATE">2008-12-05</field>
<field name="POSTED_BY">1</field>
<field name="ACCOUNTING_UPDATED">1</field>
<field name="NOTES">Payment rcvd.</field>
<field name="DEBIT_CREDIT_FLAG">0</field>
</row>
<table_data name="loan_activity_details">
<row>
<field name="ID">7</field>
<field name="CREATED_BY">1</field>
<field name="ACCOUNT_ID">4</field>
<field name="CREATED_DATE">2008-12-04 08:00:00</field>
<field name="COMMENTS">Payment rcvd.</field>
<field name="PRINCIPAL_AMOUNT">97.800</field>
<field name="PRINCIPAL_AMOUNT_CURRENCY_ID">2</field>
<field name="INTEREST_AMOUNT">1.200</field>
<field name="INTEREST_AMOUNT_CURRENCY_ID">2</field>
<field name="PENALTY_AMOUNT">0.000</field>
<field name="PENALTY_AMOUNT_CURRENCY_ID">2</field>
<field name="FEE_AMOUNT">0.000</field>
<field name="FEE_AMOUNT_CURRENCY_ID">2</field>
<field name="BALANCE_PRINCIPAL_AMOUNT">1902.200</field>
<field name="BALANCE_PRINCIPAL_AMOUNT_CURRENCY_ID">2</field>
<field name="BALANCE_INTEREST_AMOUNT">11.800</field>
<field name="BALANCE_INTEREST_AMOUNT_CURRENCY_ID">2</field>
<field name="BALANCE_PENALTY_AMOUNT">0.000</field>
<field name="BALANCE_PENALTY_AMOUNT_CURRENCY_ID">2</field>
<field name="BALANCE_FEE_AMOUNT">0.000</field>
<field name="BALANCE_FEE_AMOUNT_CURRENCY_ID">2</field>
</row>
<table_data name="loan_schedule">
<row>
<field name="ID">1</field>
<field name="ACCOUNT_ID">4</field>
<field name="CUSTOMER_ID">3</field>
<field name="CURRENCY_ID" xsi:nil="true" />
<field name="ACTION_DATE">2008-12-11</field>
<field name="PRINCIPAL">181.800</field>
<field name="PRINCIPAL_CURRENCY_ID">2</field>
<field name="INTEREST">1.200</field>
<field name="INTEREST_CURRENCY_ID">2</field>
<field name="PENALTY">0.000</field>
<field name="PENALTY_CURRENCY_ID">2</field>
<field name="MISC_FEES">0.000</field>
<field name="MISC_FEES_CURRENCY_ID">2</field>
<field name="MISC_FEES_PAID">0.000</field>
<field name="MISC_FEES_PAID_CURRENCY_ID">2</field>
<field name="MISC_PENALTY">0.000</field>
<field name="MISC_PENALTY_CURRENCY_ID">2</field>
<field name="MISC_PENALTY_PAID">0.000</field>
<field name="MISC_PENALTY_PAID_CURRENCY_ID">2</field>
###<field name="PRINCIPAL_PAID">97.800</field>
<field name="PRINCIPAL_PAID_CURRENCY_ID">2</field>
###<field name="INTEREST_PAID">1.200</field>
<field name="INTEREST_PAID_CURRENCY_ID">2</field>
<field name="PENALTY_PAID">0.000</field>
<field name="PENALTY_PAID_CURRENCY_ID">2</field>
<field name="PAYMENT_STATUS">0</field>
<field name="INSTALLMENT_ID">1</field>
###<field name="PAYMENT_DATE">2008-12-04</field>
<field name="PARENT_FLAG" xsi:nil="true" />
###<field name="VERSION_NO">1</field>
</row>

<table_data name="loan_summary">
<row>
<field name="ACCOUNT_ID">4</field>
<field name="ORIG_PRINCIPAL">2000.000</field>
<field name="ORIG_PRINCIPAL_CURRENCY_ID">2</field>
<field name="ORIG_INTEREST">13.000</field>
<field name="ORIG_INTEREST_CURRENCY_ID">2</field>
<field name="ORIG_FEES">0.000</field>
<field name="ORIG_FEES_CURRENCY_ID">2</field>
<field name="ORIG_PENALTY">0.000</field>
<field name="ORIG_PENALTY_CURRENCY_ID">2</field>
###<field name="PRINCIPAL_PAID">97.800</field>
<field name="PRINCIPAL_PAID_CURRENCY_ID">2</field>
###<field name="INTEREST_PAID">1.200</field>
<field name="INTEREST_PAID_CURRENCY_ID">2</field>
<field name="FEES_PAID">0.000</field>
<field name="FEES_PAID_CURRENCY_ID">2</field>
<field name="PENALTY_PAID">0.000</field>
<field name="PENALTY_PAID_CURRENCY_ID">2</field>
<field name="RAW_AMOUNT_TOTAL">12.700</field>
<field name="RAW_AMOUNT_TOTAL_CURRENCY_ID">2</field>
</row>

<table_data name="loan_trxn_detail">
<row>
<field name="ACCOUNT_TRXN_ID">7</field>
<field name="PRINCIPAL_AMOUNT">97.800</field>
<field name="PRINCIPAL_AMOUNT_CURRENCY_ID">2</field>
<field name="INTEREST_AMOUNT">1.200</field>
<field name="INTEREST_AMOUNT_CURRENCY_ID">2</field>
<field name="PENALTY_AMOUNT">0.000</field>
<field name="PENALTY_AMOUNT_CURRENCY_ID">2</field>
<field name="MISC_FEE_AMOUNT">0.000</field>
<field name="MISC_FEE_AMOUNT_CURRENCY_ID">2</field>
<field name="MISC_PENALTY_AMOUNT">0.000</field>
<field name="MISC_PENALTY_AMOUNT_CURRENCY_ID">2</field>
</row>

Changes to version numbers in other tables with no other data changes were also noted, but have not been included in the dump above. It is unclear why these other objects with no changes are having their version numbers bumped. Perhaps they are being pulled into the transaction when they shouldn't be. Changes were observed in the following tables:

  • account (ACCOUNT_ID=4) VERSION_NO: 3 --> 4
  • customer (CUSTOMER_ID=3,4,5) VERSION_NO: 2 --> 3
  • meeting (MEETING_ID=2) VERSION_NO: 1 --> 4
  • recur_on_day (RECUR_ON_DAY_ID=2) VERSION_NO: 1 --> 4
  • recurrence_detail (DETAILS_ID=2) VERSION_NO: 1 --> 4