...
7 Database changes
7.1 Chart of Accounts Table (acc_coa)
Table stores the “Chart of Accounts” for an organization
| Column Name | Data Type | Description |
|---|---|---|
| id | int | |
| gl_code | varchar(45) | |
| category | varchar(45) | Whether the account falls under “asset”, “income”, “expense”, “liability” or “equity” |
| parent_Id | int | References “id” of table “m_acc_coa”. For enabling a hierarchical COA |
| name | varchar(45) | |
| type | varchar(45) | Either “header” or “detail” account. Header accounts will not have transactions logged against them |
| description | varchar(500) |
...
7.2 Modifications to Loan Product (“m_product_loan”)
Table stores the “Chart of Accounts” for an organization
...
| Column Name | Data Type | Description |
|---|---|---|
| accounting_rule | varchar(45) | The type of accounting required. Either “none”, “cash” or “accrual” *Selecting none would disable posting of all accounting transactions |
| loan_coa_mapping_id | int | Null-able foreign key to acc_loan_coa_mapping |
7.3 Mapping Loan product to Chart of accounts (acc_loan_coa_mapping)
The table maps the GL codes applicable for a particular loan product. Going forward, we would be having a similar table for mapping GL codes to saving products
| Column Name | Data Type | Description |
|---|---|---|
id | int |
|
loan_portfolio_gl_code | int | Foreign Key to acc_coa |
fund_gl_code | int | Foreign Key to acc_coa |
receivable_interest_gl_code | int | Null-able foreign Key to acc_coa |
receivable_fee_gl_code | int | Null-able foreign Key to acc_coa |
receivable_penalty_gl_code | int | Null-able foreign Key to acc_coa |
interest_income_gl_code | int | Foreign Key to acc_coa |
fee_income_gl_code | int | Foreign Key to acc_coa |
penalty_income_gl_code | int | Foreign Key to acc_coa |
write_off_gl_code | int | Foreign Key to acc_coa |
7.4 Journal Entries (acc_journal_entry)
Table stores all journal entries generated in the system (either created automatically by the system and those created manually)
| Column Name | Data Type | Description |
|---|---|---|
id |
| Primary key for the entry |
entry_date | DATE TIME | The entry date for the journal entry (can be backdated to any date etc) |
batch_id | varchar(45) | Batch key for the entry, could be a UUID generated by Java.Used to link together a set of related Journal entries |
type | varchar(45) | Whether the entry is a debit or a credit |
amount | Float |
|
description | varchar(500) | user notes for manual entries |
branch_id |
| Branch with which this entry is associated |
reversal_id |
| Foreign key to another journal entry which reversed this journal entry (id column of acc_journal_entry) |
is_reversed | Boolean | Flag indicating if the Journal entry is reversed (an entry can be reversed only once) |
entity_type | varchar(45) | “Loan” or “Savings” account for system generated entries. Null for manual entries |
entity_id | int | Nullable foreign key to a loan or savings account |
system_generated | Boolean | Flag indicating whether the entry was logged automatically by the system |
created_date | Date | Audit feild to indicated the actual date on which the entry was posted into the system |
created_by | varchar(45) | Foreign key to user who created the Entry (for manual entries) |
7.5 Journal Entries (acc_journal_entry)
Table stores details of all branches and dates for which Manual Journal entries have been closed
| Column Name | Data Type | Description |
|---|---|---|
Id | Int |
|
closing_date | DATE |
|
office_id | Int | Id of the office for which the closure date is applicable |
created_by | varchar(45) |
|
created_date | DATE |
|
last_updated_date | DATE |
|