| -- |
| -- Licensed to the Apache Software Foundation (ASF) under one |
| -- or more contributor license agreements. See the NOTICE file |
| -- distributed with this work for additional information |
| -- regarding copyright ownership. The ASF licenses this file |
| -- to you under the Apache License, Version 2.0 (the |
| -- "License"); you may not use this file except in compliance |
| -- with the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, |
| -- software distributed under the License is distributed on an |
| -- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| -- KIND, either express or implied. See the License for the |
| -- specific language governing permissions and limitations |
| -- under the License. |
| -- |
| |
| -- drop tables in base-schema |
| SET foreign_key_checks = 0; |
| |
| -- drop accounting subsystem |
| DROP TABLE IF EXISTS `acc_gl_account`; |
| DROP TABLE IF EXISTS `acc_gl_closure`; |
| DROP TABLE IF EXISTS `acc_gl_journal_entry`; |
| DROP TABLE IF EXISTS `acc_product_mapping`; |
| |
| -- drop portfolio subsystem |
| DROP TABLE IF EXISTS `c_configuration`; |
| DROP TABLE IF EXISTS `m_appuser`; |
| DROP TABLE IF EXISTS `m_appuser_role`; |
| DROP TABLE IF EXISTS `m_calendar`; |
| DROP TABLE IF EXISTS `m_calendar_instance`; |
| DROP TABLE IF EXISTS `m_charge`; |
| DROP TABLE IF EXISTS `m_client`; |
| DROP TABLE IF EXISTS `m_client_identifier`; |
| DROP TABLE IF EXISTS `m_code`; |
| DROP TABLE IF EXISTS `m_code_value`; |
| DROP TABLE IF EXISTS `m_currency`; |
| DROP TABLE IF EXISTS `m_deposit_account`; |
| DROP TABLE IF EXISTS `m_deposit_account_transaction`; |
| DROP TABLE IF EXISTS `m_document`; |
| DROP TABLE IF EXISTS `m_fund`; |
| DROP TABLE IF EXISTS `m_group`; |
| DROP TABLE IF EXISTS `m_group_level`; |
| DROP TABLE IF EXISTS `m_group_client`; |
| DROP TABLE IF EXISTS `m_guarantor`; |
| DROP TABLE IF EXISTS `m_loan`; |
| DROP TABLE IF EXISTS `m_loan_charge`; |
| DROP TABLE IF EXISTS `m_loan_arrears_aging`; |
| DROP TABLE IF EXISTS `m_loan_collateral`; |
| DROP TABLE IF EXISTS `m_loan_officer_assignment_history`; |
| DROP TABLE IF EXISTS `m_loan_repayment_schedule`; |
| DROP TABLE IF EXISTS `m_loan_transaction`; |
| DROP TABLE IF EXISTS `m_note`; |
| DROP TABLE IF EXISTS `m_office`; |
| DROP TABLE IF EXISTS `m_office_transaction`; |
| DROP TABLE IF EXISTS `m_organisation_currency`; |
| DROP TABLE IF EXISTS `m_permission`; |
| DROP TABLE IF EXISTS `m_portfolio_command_source`; |
| DROP TABLE IF EXISTS `m_product_deposit`; |
| DROP TABLE IF EXISTS `m_product_loan`; |
| DROP TABLE IF EXISTS `m_product_loan_charge`; |
| DROP TABLE IF EXISTS `m_role`; |
| DROP TABLE IF EXISTS `m_role_permission`; |
| DROP TABLE IF EXISTS `m_savings_account`; |
| DROP TABLE IF EXISTS `m_savings_account_transaction`; |
| DROP TABLE IF EXISTS `m_savings_product`; |
| DROP TABLE IF EXISTS `m_staff`; |
| DROP TABLE IF EXISTS `ref_loan_transaction_processing_strategy`; |
| DROP TABLE IF EXISTS `x_registered_table`; |
| |
| -- drop reporting related tables |
| DROP TABLE IF EXISTS `r_enum_value`; |
| DROP TABLE IF EXISTS `rpt_sequence`; |
| DROP TABLE IF EXISTS `stretchy_parameter`; |
| DROP TABLE IF EXISTS `stretchy_report`; |
| DROP TABLE IF EXISTS `stretchy_report_parameter`; |
| |
| SET foreign_key_checks = 1; |
| |
| -- DDL for reference/lookup tables |
| CREATE TABLE `m_currency` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `code` varchar(3) NOT NULL, |
| `decimal_places` smallint(5) NOT NULL, |
| `display_symbol` varchar(10) DEFAULT NULL, |
| `name` varchar(50) NOT NULL, |
| `internationalized_name_code` varchar(50) NOT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `code` (`code`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_organisation_currency` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `code` varchar(3) NOT NULL, |
| `decimal_places` smallint(5) NOT NULL, |
| `name` varchar(50) NOT NULL, |
| `display_symbol` varchar(10) DEFAULT NULL, |
| `internationalized_name_code` varchar(50) NOT NULL, |
| PRIMARY KEY (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `ref_loan_transaction_processing_strategy` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `code` varchar(100) DEFAULT NULL, |
| `name` varchar(255) DEFAULT NULL, |
| `createdby_id` bigint(20) DEFAULT NULL, |
| `created_date` datetime DEFAULT NULL, |
| `lastmodifiedby_id` bigint(20) DEFAULT NULL, |
| `lastmodified_date` datetime DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `ltp_strategy_code` (`code`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `c_configuration` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `name` varchar(50) DEFAULT NULL, |
| `enabled` tinyint(1) NOT NULL DEFAULT '0', |
| PRIMARY KEY (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_code` ( |
| `id` int(11) NOT NULL AUTO_INCREMENT, |
| `code_name` varchar(100) DEFAULT NULL, |
| `is_system_defined` TINYINT(1) NOT NULL DEFAULT '0', |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `code_name` (`code_name`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_code_value` ( |
| `id` int(11) NOT NULL AUTO_INCREMENT, |
| `code_id` int(11) NOT NULL, |
| `code_value` varchar(100) DEFAULT NULL, |
| `order_position` int(11) NOT NULL DEFAULT '0', |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `code_value` (`code_id`,`code_value`), |
| KEY `FKCFCEA42640BE071Z` (`code_id`), |
| CONSTRAINT `FKCFCEA42640BE071Z` FOREIGN KEY (`code_id`) REFERENCES `m_code` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_document` ( |
| `id` int(20) NOT NULL AUTO_INCREMENT, |
| `parent_entity_type` varchar(50) NOT NULL, |
| `parent_entity_id` int(20) NOT NULL DEFAULT '0', |
| `name` varchar(250) NOT NULL, |
| `file_name` varchar(250) NOT NULL, |
| `size` int(20) DEFAULT '0', |
| `type` varchar(50) DEFAULT NULL, |
| `description` varchar(1000) DEFAULT NULL, |
| `location` varchar(500) NOT NULL DEFAULT '0', |
| PRIMARY KEY (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| /*not a major table - just intended for database reporting use for enums and values that would be hidden in java*/ |
| CREATE TABLE `r_enum_value` ( |
| `enum_name` varchar(100) NOT NULL, |
| `enum_id` int(11) NOT NULL, |
| `enum_message_property` varchar(100) NOT NULL, |
| `enum_value` varchar(100) NOT NULL, |
| PRIMARY KEY (`enum_name`,`enum_id`), |
| UNIQUE KEY `enum_message_property` (`enum_name`,`enum_message_property`), |
| UNIQUE KEY `enum_value` (`enum_name`,`enum_value`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| /* used to link MySql tables to Mifos X application tables for additional data needs */ |
| CREATE TABLE `x_registered_table` ( |
| `registered_table_name` varchar(50) NOT NULL, |
| `application_table_name` varchar(50) NOT NULL, |
| PRIMARY KEY (`registered_table_name`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE IF NOT EXISTS `m_calendar` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `title` varchar(50) NOT NULL, |
| `description` varchar(100) DEFAULT NULL, |
| `location` varchar(50) DEFAULT NULL, |
| `start_date` date NOT NULL, |
| `end_date` date DEFAULT NULL, |
| `duration` smallint(6) DEFAULT NULL, |
| `calendar_type_enum` smallint(5) NOT NULL, |
| `repeating` tinyint(1) NOT NULL DEFAULT '0', |
| `recurrence` varchar(100) DEFAULT NULL, |
| `remind_by_enum` smallint(5) DEFAULT NULL, |
| `first_reminder` smallint(11) DEFAULT NULL, |
| `second_reminder` smallint(11) DEFAULT NULL, |
| `createdby_id` bigint(20) DEFAULT NULL, |
| `lastmodifiedby_id` bigint(20) DEFAULT NULL, |
| `created_date` datetime DEFAULT NULL, |
| `lastmodified_date` datetime DEFAULT NULL, |
| PRIMARY KEY (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE IF NOT EXISTS `m_calendar_instance` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `calendar_id` bigint(20) NOT NULL, |
| `entity_id` bigint(20) NOT NULL, |
| `entity_type_enum` smallint(5) NOT NULL, |
| PRIMARY KEY (`id`), |
| KEY `FK_m_calendar_m_calendar_instance` (`calendar_id`), |
| CONSTRAINT `FK_m_calendar_m_calendar_instance` FOREIGN KEY (`calendar_id`) REFERENCES `m_calendar` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| -- ================= end of reference/lookup tables ============= |
| |
| -- DDL for office related tables |
| CREATE TABLE `m_office` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `parent_id` bigint(20) DEFAULT NULL, |
| `hierarchy` varchar(100) DEFAULT NULL, |
| `external_id` varchar(100) DEFAULT NULL, |
| `name` varchar(50) NOT NULL, |
| `opening_date` date NOT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `name_org` (`name`), |
| UNIQUE KEY `externalid_org` (`external_id`), |
| KEY `FK2291C477E2551DCC` (`parent_id`), |
| CONSTRAINT `FK2291C477E2551DCC` FOREIGN KEY (`parent_id`) REFERENCES `m_office` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_office_transaction` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `from_office_id` bigint(20) DEFAULT NULL, |
| `to_office_id` bigint(20) DEFAULT NULL, |
| `currency_code` varchar(3) NOT NULL, |
| `currency_digits` int(11) NOT NULL, |
| `transaction_amount` decimal(19,6) NOT NULL, |
| `transaction_date` date NOT NULL, |
| `description` varchar(100) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| KEY `FK1E37728B93C6C1B6` (`to_office_id`), |
| KEY `FK1E37728B783C5C25` (`from_office_id`), |
| CONSTRAINT `FK1E37728B783C5C25` FOREIGN KEY (`from_office_id`) REFERENCES `m_office` (`id`), |
| CONSTRAINT `FK1E37728B93C6C1B6` FOREIGN KEY (`to_office_id`) REFERENCES `m_office` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| -- ============ end of office related tables ========== |
| |
| -- DDL for admin tables |
| CREATE TABLE `m_permission` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `grouping` varchar(45) DEFAULT NULL, |
| `code` varchar(100) NOT NULL, |
| `entity_name` varchar(100) DEFAULT NULL, |
| `action_name` varchar(100) DEFAULT NULL, |
| `can_maker_checker` tinyint(1) NOT NULL DEFAULT '1', |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `code` (`code`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_role` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `name` varchar(100) NOT NULL, |
| `description` varchar(500) NOT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `unq_name` (`name`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_role_permission` ( |
| `role_id` bigint(20) NOT NULL, |
| `permission_id` bigint(20) NOT NULL, |
| PRIMARY KEY (`role_id`,`permission_id`), |
| KEY `FK8DEDB04815CEC7AB` (`role_id`), |
| KEY `FK8DEDB048103B544B` (`permission_id`), |
| CONSTRAINT `FK8DEDB048103B544B` FOREIGN KEY (`permission_id`) REFERENCES `m_permission` (`id`), |
| CONSTRAINT `FK8DEDB04815CEC7AB` FOREIGN KEY (`role_id`) REFERENCES `m_role` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_appuser` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `is_deleted` tinyint(1) NOT NULL DEFAULT '0', |
| `office_id` bigint(20) DEFAULT NULL, |
| `username` varchar(100) NOT NULL, |
| `firstname` varchar(100) NOT NULL, |
| `lastname` varchar(100) NOT NULL, |
| `password` varchar(255) NOT NULL, |
| `email` varchar(100) NOT NULL, |
| `firsttime_login_remaining` bit(1) NOT NULL, |
| `nonexpired` bit(1) NOT NULL, |
| `nonlocked` bit(1) NOT NULL, |
| `nonexpired_credentials` bit(1) NOT NULL, |
| `enabled` bit(1) NOT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `username_org` (`username`), |
| KEY `FKB3D587CE0DD567A` (`office_id`), |
| CONSTRAINT `FKB3D587CE0DD567A` FOREIGN KEY (`office_id`) REFERENCES `m_office` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_appuser_role` ( |
| `appuser_id` bigint(20) NOT NULL, |
| `role_id` bigint(20) NOT NULL, |
| PRIMARY KEY (`appuser_id`,`role_id`), |
| KEY `FK7662CE59B4100309` (`appuser_id`), |
| KEY `FK7662CE5915CEC7AB` (`role_id`), |
| CONSTRAINT `FK7662CE5915CEC7AB` FOREIGN KEY (`role_id`) REFERENCES `m_role` (`id`), |
| CONSTRAINT `FK7662CE59B4100309` FOREIGN KEY (`appuser_id`) REFERENCES `m_appuser` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| -- ================ end of user admin tables =============== |
| |
| -- DDL for organisation wide related concepts |
| CREATE TABLE `m_portfolio_command_source` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `action_name` varchar(50) NOT NULL, |
| `entity_name` varchar(50) NOT NULL, |
| `office_id` bigint(20) DEFAULT NULL, |
| `group_id` bigint(20) DEFAULT NULL, |
| `client_id` bigint(20) DEFAULT NULL, |
| `loan_id` bigint(20) DEFAULT NULL, |
| `savings_account_id` bigint(20) DEFAULT NULL, |
| `api_get_url` varchar(100) NOT NULL, |
| `resource_id` bigint(20) DEFAULT NULL, |
| `subresource_id` bigint(20) DEFAULT NULL, |
| `command_as_json` text NOT NULL, |
| `maker_id` bigint(20) NOT NULL, |
| `made_on_date` datetime NOT NULL, |
| `checker_id` bigint(20) DEFAULT NULL, |
| `checked_on_date` datetime DEFAULT NULL, |
| `processing_result_enum` smallint(5) NOT NULL, |
| PRIMARY KEY (`id`), |
| KEY `FK_m_maker_m_appuser` (`maker_id`), |
| KEY `FK_m_checker_m_appuser` (`checker_id`), |
| KEY `action_name` (`action_name`), |
| KEY `entity_name` (`entity_name`,`resource_id`), |
| KEY `made_on_date` (`made_on_date`), |
| KEY `checked_on_date` (`checked_on_date`), |
| KEY `processing_result_enum` (`processing_result_enum`), |
| KEY `office_id` (`office_id`), |
| KEY `group_id` (`office_id`), |
| KEY `client_id` (`office_id`), |
| KEY `loan_id` (`office_id`), |
| CONSTRAINT `FK_m_checker_m_appuser` FOREIGN KEY (`checker_id`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `FK_m_maker_m_appuser` FOREIGN KEY (`maker_id`) REFERENCES `m_appuser` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_charge` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `name` varchar(100) DEFAULT NULL, |
| `currency_code` varchar(3) NOT NULL, |
| `charge_applies_to_enum` smallint(5) NOT NULL, |
| `charge_time_enum` smallint(5) NOT NULL, |
| `charge_calculation_enum` smallint(5) NOT NULL, |
| `amount` decimal(19,6) NOT NULL, |
| `is_penalty` tinyint(1) NOT NULL DEFAULT '0', |
| `is_active` tinyint(1) NOT NULL, |
| `is_deleted` tinyint(1) NOT NULL DEFAULT '0', |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `name` (`name`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_fund` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `name` varchar(255) DEFAULT NULL, |
| `external_id` varchar(100) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `fund_name_org` (`name`), |
| UNIQUE KEY `fund_externalid_org` (`external_id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_staff` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `is_loan_officer` tinyint(1) NOT NULL DEFAULT '0', |
| `office_id` bigint(20) DEFAULT NULL, |
| `firstname` varchar(50) DEFAULT NULL, |
| `lastname` varchar(50) DEFAULT NULL, |
| `display_name` varchar(100) NOT NULL, |
| `joining_date` date, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `display_name` (`display_name`), |
| KEY `FK_m_staff_m_office` (`office_id`), |
| CONSTRAINT `FK_m_staff_m_office` FOREIGN KEY (`office_id`) REFERENCES `m_office` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| -- ============ end of organisation wide related tables =========== |
| |
| -- DDL client/group related tables |
| CREATE TABLE `m_group_level` ( |
| `id` INT(11) NOT NULL AUTO_INCREMENT, |
| `parent_id` INT(11) NULL DEFAULT NULL, |
| `super_parent` TINYINT(1) NOT NULL, |
| `level_name` VARCHAR(100) NOT NULL, |
| `recursable` TINYINT(1) NOT NULL, |
| `can_have_clients` TINYINT(1) NOT NULL, |
| PRIMARY KEY (`id`), |
| INDEX `Parent_levelId_reference` (`parent_id`), |
| CONSTRAINT `Parent_levelId_reference` FOREIGN KEY (`parent_id`) REFERENCES `m_group_level` (`id`) |
| )ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_group` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `office_id` bigint(20) NOT NULL, |
| `staff_id` BIGINT(20) DEFAULT NULL, |
| `parent_id` BIGINT(20) NULL DEFAULT NULL, |
| `level_Id` INT(11) NOT NULL, |
| `hierarchy` VARCHAR(100) NULL DEFAULT NULL, |
| `name` varchar(100) NOT NULL, |
| `external_id` varchar(100) DEFAULT NULL, |
| `is_deleted` tinyint(1) NOT NULL DEFAULT '0', |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `name` (`name`, `level_id`), |
| UNIQUE KEY `external_id` (`external_id`, `level_Id`), |
| KEY `office_id` (`office_id`), |
| KEY `staff_id` (`staff_id`), |
| CONSTRAINT `m_group_ibfk_1` FOREIGN KEY (`office_id`) REFERENCES `m_office` (`id`), |
| CONSTRAINT `Parent_Id_reference` FOREIGN KEY (`parent_id`) REFERENCES `m_group` (`id`), |
| CONSTRAINT `FK_m_group_level` FOREIGN KEY (`level_Id`) REFERENCES `m_group_level` (`id`), |
| CONSTRAINT `FK_m_group_m_staff` FOREIGN KEY (`staff_id`) REFERENCES `m_staff` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_client` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `account_no` varchar(20) NOT NULL, |
| `office_id` bigint(20) NOT NULL, |
| `external_id` varchar(100) DEFAULT NULL, |
| `firstname` varchar(50) DEFAULT NULL, |
| `middlename` varchar(50) DEFAULT NULL, |
| `lastname` varchar(50) DEFAULT NULL, |
| `fullname` varchar(100) DEFAULT NULL, |
| `display_name` varchar(100) NOT NULL, |
| `image_key` varchar(500) DEFAULT NULL, |
| `joined_date` date DEFAULT NULL, |
| `is_deleted` tinyint(1) NOT NULL DEFAULT '0', |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `account_no_UNIQUE` (`account_no`), |
| UNIQUE KEY `external_id` (`external_id`), |
| KEY `FKCE00CAB3E0DD567A` (`office_id`), |
| CONSTRAINT `FKCE00CAB3E0DD567A` FOREIGN KEY (`office_id`) REFERENCES `m_office` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_client_identifier` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `client_id` bigint(20) NOT NULL, |
| `document_type_id` int(11) NOT NULL, |
| `document_key` varchar(50) NOT NULL, |
| `description` varchar(500) DEFAULT NULL, |
| `createdby_id` bigint(20) DEFAULT NULL, |
| `lastmodifiedby_id` bigint(20) DEFAULT NULL, |
| `created_date` datetime DEFAULT NULL, |
| `lastmodified_date` datetime DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `unique_identifier_key` (`document_type_id`,`document_key`), |
| UNIQUE KEY `unique_client_identifier` (`client_id`,`document_type_id`), |
| KEY `FK_m_client_document_m_client` (`client_id`), |
| KEY `FK_m_client_document_m_code_value` (`document_type_id`), |
| CONSTRAINT `FK_m_client_document_m_client` FOREIGN KEY (`client_id`) REFERENCES `m_client` (`id`), |
| CONSTRAINT `FK_m_client_document_m_code_value` FOREIGN KEY (`document_type_id`) REFERENCES `m_code_value` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_group_client` ( |
| `group_id` bigint(20) NOT NULL, |
| `client_id` bigint(20) NOT NULL, |
| PRIMARY KEY (`group_id`,`client_id`), |
| KEY `client_id` (`client_id`), |
| CONSTRAINT `m_group_client_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `m_group` (`id`), |
| CONSTRAINT `m_group_client_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `m_client` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| -- ==== end of client/group related tables ========== |
| |
| -- DDL for loan and loan related tables |
| CREATE TABLE `m_product_loan` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `currency_code` varchar(3) NOT NULL, |
| `currency_digits` smallint(5) NOT NULL, |
| `principal_amount` decimal(19,6) NOT NULL, |
| `arrearstolerance_amount` decimal(19,6) DEFAULT NULL, |
| `name` varchar(100) NOT NULL, |
| `description` varchar(500) DEFAULT NULL, |
| `fund_id` bigint(20) DEFAULT NULL, |
| `nominal_interest_rate_per_period` decimal(19,6) NOT NULL, |
| `interest_period_frequency_enum` smallint(5) NOT NULL, |
| `annual_nominal_interest_rate` decimal(19,6) NOT NULL, |
| `interest_method_enum` smallint(5) NOT NULL, |
| `interest_calculated_in_period_enum` smallint(5) NOT NULL DEFAULT '1', |
| `repay_every` smallint(5) NOT NULL, |
| `repayment_period_frequency_enum` smallint(5) NOT NULL, |
| `number_of_repayments` smallint(5) NOT NULL, |
| `amortization_method_enum` smallint(5) NOT NULL, |
| `accounting_type` smallint(5) NOT NULL, |
| `loan_transaction_strategy_id` bigint(20) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `unq_name` (`name`), |
| KEY `FKA6A8A7D77240145` (`fund_id`), |
| KEY `FK_ltp_strategy` (`loan_transaction_strategy_id`), |
| CONSTRAINT `FKA6A8A7D77240145` FOREIGN KEY (`fund_id`) REFERENCES `m_fund` (`id`), |
| CONSTRAINT `FK_ltp_strategy` FOREIGN KEY (`loan_transaction_strategy_id`) REFERENCES `ref_loan_transaction_processing_strategy` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_product_loan_charge` ( |
| `product_loan_id` bigint(20) NOT NULL, |
| `charge_id` bigint(20) NOT NULL, |
| PRIMARY KEY (`product_loan_id`,`charge_id`), |
| KEY `charge_id` (`charge_id`), |
| CONSTRAINT `m_product_loan_charge_ibfk_1` FOREIGN KEY (`charge_id`) REFERENCES `m_charge` (`id`), |
| CONSTRAINT `m_product_loan_charge_ibfk_2` FOREIGN KEY (`product_loan_id`) REFERENCES `m_product_loan` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_loan` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `account_no` varchar(20) NOT NULL, |
| `external_id` varchar(100) DEFAULT NULL, |
| `client_id` bigint(20) DEFAULT NULL, |
| `group_id` bigint(20) DEFAULT NULL, |
| `product_id` bigint(20) DEFAULT NULL, |
| `fund_id` bigint(20) DEFAULT NULL, |
| `loan_officer_id` bigint(20) DEFAULT NULL, |
| `loanpurpose_cv_id` int(11) DEFAULT NULL, |
| `loan_status_id` smallint(5) NOT NULL, |
| `currency_code` varchar(3) NOT NULL, |
| `currency_digits` smallint(5) NOT NULL, |
| `principal_amount` decimal(19,6) NOT NULL, |
| `arrearstolerance_amount` decimal(19,6) DEFAULT NULL, |
| `nominal_interest_rate_per_period` decimal(19,6) NOT NULL, |
| `interest_period_frequency_enum` smallint(5) NOT NULL, |
| `annual_nominal_interest_rate` decimal(19,6) NOT NULL, |
| `interest_method_enum` smallint(5) NOT NULL, |
| `interest_calculated_in_period_enum` smallint(5) NOT NULL DEFAULT '1', |
| `term_frequency` smallint(5) NOT NULL DEFAULT '0', |
| `term_period_frequency_enum` smallint(5) NOT NULL DEFAULT '2', |
| `repay_every` smallint(5) NOT NULL, |
| `repayment_period_frequency_enum` smallint(5) NOT NULL, |
| `number_of_repayments` smallint(5) NOT NULL, |
| `amortization_method_enum` smallint(5) NOT NULL, |
| `submittedon_date` date DEFAULT NULL, |
| `submittedon_userid` bigint(20) DEFAULT NULL, |
| `approvedon_date` date DEFAULT NULL, |
| `approvedon_userid` bigint(20) DEFAULT NULL, |
| `expected_disbursedon_date` date DEFAULT NULL, |
| `expected_firstrepaymenton_date` date DEFAULT NULL, |
| `interest_calculated_from_date` date DEFAULT NULL, |
| `disbursedon_date` date DEFAULT NULL, |
| `disbursedon_userid` bigint(20) DEFAULT NULL, |
| `expected_maturedon_date` date DEFAULT NULL, |
| `maturedon_date` date DEFAULT NULL, |
| `closedon_date` date DEFAULT NULL, |
| `closedon_userid` bigint(20) DEFAULT NULL, |
| `total_charges_due_at_disbursement_derived` decimal(19,6) DEFAULT NULL, |
| `principal_disbursed_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `principal_repaid_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `principal_writtenoff_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `principal_outstanding_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `interest_charged_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `interest_repaid_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `interest_waived_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `interest_writtenoff_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `interest_outstanding_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `fee_charges_charged_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `fee_charges_repaid_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `fee_charges_waived_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `fee_charges_writtenoff_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `fee_charges_outstanding_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `penalty_charges_charged_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `penalty_charges_repaid_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `penalty_charges_waived_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `penalty_charges_writtenoff_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `penalty_charges_outstanding_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `total_expected_repayment_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `total_repayment_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `total_expected_costofloan_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `total_costofloan_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `total_waived_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `total_writtenoff_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `total_outstanding_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `rejectedon_date` date DEFAULT NULL, |
| `rejectedon_userid` bigint(20) DEFAULT NULL, |
| `rescheduledon_date` date DEFAULT NULL, |
| `withdrawnon_date` date DEFAULT NULL, |
| `withdrawnon_userid` bigint(20) DEFAULT NULL, |
| `writtenoffon_date` date DEFAULT NULL, |
| `loan_transaction_strategy_id` bigint(20) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `loan_account_no_UNIQUE` (`account_no`), |
| UNIQUE KEY `loan_externalid_UNIQUE` (`external_id`), |
| KEY `FKB6F935D87179A0CB` (`client_id`), |
| KEY `FKB6F935D8C8D4B434` (`product_id`), |
| KEY `FK7C885877240145` (`fund_id`), |
| KEY `FK_loan_ltp_strategy` (`loan_transaction_strategy_id`), |
| KEY `FK_m_loan_m_staff` (`loan_officer_id`), |
| KEY `group_id` (`group_id`), |
| KEY `FK_m_loanpurpose_codevalue` (`loanpurpose_cv_id`), |
| KEY `FK_submittedon_userid` (`submittedon_userid`), |
| KEY `FK_approvedon_userid` (`approvedon_userid`), |
| KEY `FK_rejectedon_userid` (`rejectedon_userid`), |
| KEY `FK_withdrawnon_userid` (`withdrawnon_userid`), |
| KEY `FK_disbursedon_userid` (`disbursedon_userid`), |
| KEY `FK_closedon_userid` (`closedon_userid`), |
| CONSTRAINT `FK7C885877240145` FOREIGN KEY (`fund_id`) REFERENCES `m_fund` (`id`), |
| CONSTRAINT `FKB6F935D87179A0CB` FOREIGN KEY (`client_id`) REFERENCES `m_client` (`id`), |
| CONSTRAINT `FKB6F935D8C8D4B434` FOREIGN KEY (`product_id`) REFERENCES `m_product_loan` (`id`), |
| CONSTRAINT `FK_approvedon_userid` FOREIGN KEY (`approvedon_userid`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `FK_closedon_userid` FOREIGN KEY (`closedon_userid`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `FK_disbursedon_userid` FOREIGN KEY (`disbursedon_userid`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `FK_loan_ltp_strategy` FOREIGN KEY (`loan_transaction_strategy_id`) REFERENCES `ref_loan_transaction_processing_strategy` (`id`), |
| CONSTRAINT `FK_m_loanpurpose_codevalue` FOREIGN KEY (`loanpurpose_cv_id`) REFERENCES `m_code_value` (`id`), |
| CONSTRAINT `FK_m_loan_m_staff` FOREIGN KEY (`loan_officer_id`) REFERENCES `m_staff` (`id`), |
| CONSTRAINT `FK_rejectedon_userid` FOREIGN KEY (`rejectedon_userid`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `FK_submittedon_userid` FOREIGN KEY (`submittedon_userid`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `FK_withdrawnon_userid` FOREIGN KEY (`withdrawnon_userid`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `m_loan_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `m_group` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_loan_arrears_aging` ( |
| `loan_id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `principal_overdue_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `interest_overdue_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `fee_charges_overdue_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `penalty_charges_overdue_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `total_overdue_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `overdue_since_date_derived` date DEFAULT NULL, |
| PRIMARY KEY (`loan_id`), |
| CONSTRAINT `m_loan_arrears_aging_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `m_loan` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_guarantor` ( |
| `id` BIGINT(20) NOT NULL AUTO_INCREMENT, |
| `loan_id` BIGINT(20) NOT NULL, |
| `client_reln_cv_id` INT(11) DEFAULT NULL, |
| `type_enum` SMALLINT(5) NOT NULL, |
| `entity_id` BIGINT(20) NULL DEFAULT NULL, |
| `firstname` VARCHAR(50) NULL DEFAULT NULL, |
| `lastname` VARCHAR(50) NULL DEFAULT NULL, |
| `dob` DATE NULL DEFAULT NULL, |
| `address_line_1` VARCHAR(500) NULL DEFAULT NULL, |
| `address_line_2` VARCHAR(500) NULL DEFAULT NULL, |
| `city` VARCHAR(50) NULL DEFAULT NULL, |
| `state` VARCHAR(50) NULL DEFAULT NULL, |
| `country` VARCHAR(50) NULL DEFAULT NULL, |
| `zip` VARCHAR(20) NULL DEFAULT NULL, |
| `house_phone_number` VARCHAR(20) NULL DEFAULT NULL, |
| `mobile_number` VARCHAR(20) NULL DEFAULT NULL, |
| `comment` VARCHAR(500) NULL DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| INDEX `FK_m_guarantor_m_loan` (`loan_id`), |
| CONSTRAINT `FK_m_guarantor_m_code_value` FOREIGN KEY (`client_reln_cv_id`) REFERENCES `m_code_value` (`id`) |
| CONSTRAINT `FK_m_guarantor_m_loan` FOREIGN KEY (`loan_id`) REFERENCES `m_loan` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_loan_charge` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `loan_id` bigint(20) NOT NULL, |
| `charge_id` bigint(20) NOT NULL, |
| `is_penalty` tinyint(1) NOT NULL DEFAULT '0', |
| `charge_time_enum` smallint(5) NOT NULL, |
| `due_for_collection_as_of_date` date DEFAULT NULL, |
| `charge_calculation_enum` smallint(5) NOT NULL, |
| `calculation_percentage` decimal(19,6) DEFAULT NULL, |
| `calculation_on_amount` decimal(19,6) DEFAULT NULL, |
| `amount` decimal(19,6) NOT NULL, |
| `amount_paid_derived` decimal(19,6) DEFAULT NULL, |
| `amount_waived_derived` decimal(19,6) DEFAULT NULL, |
| `amount_writtenoff_derived` decimal(19,6) DEFAULT NULL, |
| `amount_outstanding_derived` decimal(19,6) NOT NULL DEFAULT '0.000000', |
| `is_paid_derived` tinyint(1) NOT NULL DEFAULT '0', |
| `waived` tinyint(1) NOT NULL DEFAULT '0', |
| PRIMARY KEY (`id`), |
| KEY `charge_id` (`charge_id`), |
| KEY `m_loan_charge_ibfk_2` (`loan_id`), |
| CONSTRAINT `m_loan_charge_ibfk_1` FOREIGN KEY (`charge_id`) REFERENCES `m_charge` (`id`), |
| CONSTRAINT `m_loan_charge_ibfk_2` FOREIGN KEY (`loan_id`) REFERENCES `m_loan` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_loan_collateral` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `loan_id` bigint(20) NOT NULL, |
| `type_cv_id` int(11) NOT NULL, |
| `value` DECIMAL(19,6) DEFAULT NULL, |
| `description` varchar(500) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| KEY `FK_collateral_m_loan` (`loan_id`), |
| KEY `FK_collateral_code_value` (`type_cv_id`), |
| CONSTRAINT `FK_collateral_m_loan` FOREIGN KEY (`loan_id`) REFERENCES `m_loan` (`id`), |
| CONSTRAINT `FK_collateral_code_value` FOREIGN KEY (`type_cv_id`) REFERENCES `m_code_value` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_loan_officer_assignment_history` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `loan_id` bigint(20) NOT NULL, |
| `loan_officer_id` bigint(20) DEFAULT NULL, |
| `start_date` date NOT NULL, |
| `end_date` date DEFAULT NULL, |
| `createdby_id` bigint(20) DEFAULT NULL, |
| `created_date` datetime DEFAULT NULL, |
| `lastmodified_date` datetime DEFAULT NULL, |
| `lastmodifiedby_id` bigint(20) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| KEY `fk_m_loan_officer_assignment_history_0001` (`loan_id`), |
| KEY `fk_m_loan_officer_assignment_history_0002` (`loan_officer_id`), |
| CONSTRAINT `fk_m_loan_officer_assignment_history_0001` FOREIGN KEY (`loan_id`) REFERENCES `m_loan` (`id`), |
| CONSTRAINT `fk_m_loan_officer_assignment_history_0002` FOREIGN KEY (`loan_officer_id`) REFERENCES `m_staff` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_loan_repayment_schedule` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `loan_id` bigint(20) NOT NULL, |
| `fromdate` date DEFAULT NULL, |
| `duedate` date NOT NULL, |
| `installment` smallint(5) NOT NULL, |
| `principal_amount` decimal(19,6) DEFAULT NULL, |
| `principal_completed_derived` decimal(19,6) DEFAULT NULL, |
| `principal_writtenoff_derived` decimal(19,6) DEFAULT NULL, |
| `interest_amount` decimal(19,6) DEFAULT NULL, |
| `interest_completed_derived` decimal(19,6) DEFAULT NULL, |
| `interest_writtenoff_derived` decimal(19,6) DEFAULT NULL, |
| `fee_charges_amount` decimal(19,6) DEFAULT NULL, |
| `fee_charges_completed_derived` decimal(19,6) DEFAULT NULL, |
| `fee_charges_writtenoff_derived` decimal(19,6) DEFAULT NULL, |
| `fee_charges_waived_derived` decimal(19,6) DEFAULT NULL, |
| `penalty_charges_amount` decimal(19,6) DEFAULT NULL, |
| `penalty_charges_completed_derived` decimal(19,6) DEFAULT NULL, |
| `penalty_charges_writtenoff_derived` decimal(19,6) DEFAULT NULL, |
| `penalty_charges_waived_derived` decimal(19,6) DEFAULT NULL, |
| `completed_derived` bit(1) NOT NULL, |
| `createdby_id` bigint(20) DEFAULT NULL, |
| `created_date` datetime DEFAULT NULL, |
| `lastmodified_date` datetime DEFAULT NULL, |
| `lastmodifiedby_id` bigint(20) DEFAULT NULL, |
| `interest_waived_derived` decimal(19,6) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| KEY `FK488B92AA40BE0710` (`loan_id`), |
| CONSTRAINT `FK488B92AA40BE0710` FOREIGN KEY (`loan_id`) REFERENCES `m_loan` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_loan_transaction` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `loan_id` bigint(20) NOT NULL, |
| `is_reversed` TINYINT(1) NOT NULL, |
| `transaction_type_enum` smallint(5) NOT NULL, |
| `transaction_date` date NOT NULL, |
| `amount` decimal(19,6) NOT NULL, |
| `principal_portion_derived` decimal(19,6) DEFAULT NULL, |
| `interest_portion_derived` decimal(19,6) DEFAULT NULL, |
| `fee_charges_portion_derived` decimal(19,6) DEFAULT NULL, |
| `penalty_charges_portion_derived` decimal(19,6) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| KEY `FKCFCEA42640BE0710` (`loan_id`), |
| CONSTRAINT `FKCFCEA42640BE0710` FOREIGN KEY (`loan_id`) REFERENCES `m_loan` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| -- ======== end of loan related tables ========== |
| |
| CREATE TABLE `m_savings_product` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `name` varchar(100) NOT NULL, |
| `description` varchar(500) NOT NULL, |
| `currency_code` varchar(3) NOT NULL, |
| `currency_digits` smallint(5) NOT NULL, |
| `nominal_interest_rate_per_period` decimal(19,6) NOT NULL, |
| `nominal_interest_rate_period_frequency_enum` smallint(5) NOT NULL, |
| `interest_period_enum` SMALLINT(5) NOT NULL, |
| `interest_calculation_type_enum` SMALLINT(5) NOT NULL, |
| `interest_calculation_days_in_year_type_enum` SMALLINT(5) NOT NULL, |
| `min_required_opening_balance` decimal(19,6) DEFAULT NULL, |
| `lockin_period_frequency` decimal(19,6) DEFAULT NULL, |
| `lockin_period_frequency_enum` smallint(5) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `sp_unq_name` (`name`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_savings_account` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `account_no` varchar(20) NOT NULL, |
| `external_id` varchar(100) DEFAULT NULL, |
| `client_id` bigint(20) DEFAULT NULL, |
| `group_id` bigint(20) DEFAULT NULL, |
| `product_id` bigint(20) DEFAULT NULL, |
| `status_enum` SMALLINT(5) NOT NULL DEFAULT 300, |
| `activation_date` DATE DEFAULT NULL, |
| `currency_code` varchar(3) NOT NULL, |
| `currency_digits` smallint(5) NOT NULL, |
| `nominal_interest_rate_per_period` decimal(19,6) NOT NULL, |
| `nominal_interest_rate_period_frequency_enum` smallint(5) NOT NULL, |
| `annual_nominal_interest_rate` decimal(19,6) NOT NULL, |
| `interest_period_enum` SMALLINT(5) NOT NULL, |
| `interest_calculation_type_enum` SMALLINT(5) NOT NULL, |
| `interest_calculation_days_in_year_type_enum` SMALLINT(5) NOT NULL, |
| `min_required_opening_balance` decimal(19,6) DEFAULT NULL, |
| `lockin_period_frequency` decimal(19,6) DEFAULT NULL, |
| `lockin_period_frequency_enum` smallint(5) DEFAULT NULL, |
| `lockedin_until_date_derived` DATE DEFAULT NULL, |
| `total_deposits_derived` decimal(19,6) DEFAULT NULL, |
| `total_withdrawals_derived` decimal(19,6) DEFAULT NULL, |
| `total_interest_earned_derived` decimal(19,6) DEFAULT NULL, |
| `total_interest_posted_derived` decimal(19,6) DEFAULT NULL, |
| `account_balance_derived` decimal(19,6) NOT NULL DEFAULT 0, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `sa_account_no_UNIQUE` (`account_no`), |
| UNIQUE KEY `sa_externalid_UNIQUE` (`external_id`), |
| KEY `FKSA00000000000001` (`client_id`), |
| KEY `FKSA00000000000002` (`group_id`), |
| KEY `FKSA00000000000003` (`product_id`), |
| CONSTRAINT `FKSA00000000000001` FOREIGN KEY (`client_id`) REFERENCES `m_client` (`id`), |
| CONSTRAINT `FKSA00000000000002` FOREIGN KEY (`group_id`) REFERENCES `m_group` (`id`), |
| CONSTRAINT `FKSA00000000000003` FOREIGN KEY (`product_id`) REFERENCES `m_savings_product` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `m_savings_account_transaction` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `savings_account_id` bigint(20) NOT NULL, |
| `transaction_type_enum` smallint(5) NOT NULL, |
| `transaction_date` date NOT NULL, |
| `amount` decimal(19,6) NOT NULL, |
| `is_reversed` tinyint(1) NOT NULL, |
| `running_balance_derived` DECIMAL(19,6) NULL, |
| `balance_number_of_days_derived` INT NULL, |
| `balance_end_date_derived` DATE NULL, |
| `cumulative_balance_derived` DECIMAL(19,6) NULL, |
| PRIMARY KEY (`id`), |
| KEY `FKSAT0000000001` (`savings_account_id`), |
| CONSTRAINT `FKSAT0000000001` FOREIGN KEY (`savings_account_id`) REFERENCES `m_savings_account` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| -- end of savings account related tables |
| |
| -- DDL for notes associated with all client/group and financial accounts |
| CREATE TABLE `m_note` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `client_id` bigint(20) DEFAULT NULL, |
| `group_id` bigint(20) DEFAULT NULL, |
| `loan_id` bigint(20) DEFAULT NULL, |
| `loan_transaction_id` bigint(20) DEFAULT NULL, |
| `note_type_enum` smallint(5) NOT NULL, |
| `note` varchar(1000) DEFAULT NULL, |
| `created_date` datetime DEFAULT NULL, |
| `createdby_id` bigint(20) DEFAULT NULL, |
| `lastmodified_date` datetime DEFAULT NULL, |
| `lastmodifiedby_id` bigint(20) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| KEY `FK7C9708924D26803` (`loan_transaction_id`), |
| KEY `FK7C97089541F0A56` (`createdby_id`), |
| KEY `FK7C970897179A0CB` (`client_id`), |
| KEY `FK_m_note_m_group` (`group_id`), |
| KEY `FK7C970898F889C3F` (`lastmodifiedby_id`), |
| KEY `FK7C9708940BE0710` (`loan_id`), |
| CONSTRAINT `FK7C9708924D26803` FOREIGN KEY (`loan_transaction_id`) REFERENCES `m_loan_transaction` (`id`), |
| CONSTRAINT `FK7C9708940BE0710` FOREIGN KEY (`loan_id`) REFERENCES `m_loan` (`id`), |
| CONSTRAINT `FK7C97089541F0A56` FOREIGN KEY (`createdby_id`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `FK7C970897179A0CB` FOREIGN KEY (`client_id`) REFERENCES `m_client` (`id`), |
| CONSTRAINT `FK_m_note_m_group` FOREIGN KEY (`group_id`) REFERENCES `m_group` (`id`), |
| CONSTRAINT `FK7C970898F889C3F` FOREIGN KEY (`lastmodifiedby_id`) REFERENCES `m_appuser` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| -- DDL for accounting sub system related tables |
| CREATE TABLE `acc_gl_account` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `name` varchar(45) NOT NULL, |
| `parent_id` bigint(20) DEFAULT NULL, |
| `gl_code` varchar(45) NOT NULL, |
| `disabled` tinyint(1) NOT NULL DEFAULT '0', |
| `manual_journal_entries_allowed` tinyint(1) NOT NULL DEFAULT '1', |
| `account_usage` tinyint(1) NOT NULL DEFAULT '2', |
| `classification_enum` smallint(5) NOT NULL, |
| `description` varchar(500) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `acc_gl_code` (`gl_code`), |
| KEY `FK_ACC_0000000001` (`parent_id`), |
| CONSTRAINT `FK_ACC_0000000001` FOREIGN KEY (`parent_id`) REFERENCES `acc_gl_account` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `acc_gl_closure` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `office_id` bigint(20) NOT NULL, |
| `closing_date` date NOT NULL, |
| `is_deleted` int(20) NOT NULL DEFAULT '0', |
| `createdby_id` bigint(20) DEFAULT NULL, |
| `lastmodifiedby_id` bigint(20) DEFAULT NULL, |
| `created_date` datetime DEFAULT NULL, |
| `lastmodified_date` datetime DEFAULT NULL, |
| `comments` varchar(500) DEFAULT NULL, |
| PRIMARY KEY (`id`), |
| UNIQUE KEY `office_id_closing_date` (`office_id`,`closing_date`), |
| KEY `FK_acc_gl_closure_m_office` (`office_id`), |
| KEY `FK_acc_gl_closure_m_appuser` (`createdby_id`), |
| KEY `FK_acc_gl_closure_m_appuser_2` (`lastmodifiedby_id`), |
| CONSTRAINT `FK_acc_gl_closure_m_appuser` FOREIGN KEY (`createdby_id`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `FK_acc_gl_closure_m_appuser_2` FOREIGN KEY (`lastmodifiedby_id`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `FK_acc_gl_closure_m_office` FOREIGN KEY (`office_id`) REFERENCES `m_office` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `acc_gl_journal_entry` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `account_id` bigint(20) NOT NULL, |
| `office_id` bigint(20) NOT NULL, |
| `reversal_id` bigint(20) DEFAULT NULL, |
| `transaction_id` varchar(50) NOT NULL, |
| `reversed` tinyint(1) NOT NULL DEFAULT '0', |
| `manual_entry` TINYINT(1) NOT NULL DEFAULT '0', |
| `entry_date` date NOT NULL, |
| `type_enum` smallint(5) NOT NULL, |
| `amount` decimal(19,6) NOT NULL, |
| `description` varchar(500) DEFAULT NULL, |
| `entity_type_enum` smallint(5) DEFAULT NULL, |
| `entity_id` bigint(20) DEFAULT NULL, |
| `createdby_id` bigint(20) NOT NULL, |
| `lastmodifiedby_id` bigint(20) NOT NULL, |
| `created_date` datetime NOT NULL, |
| `lastmodified_date` datetime NOT NULL, |
| PRIMARY KEY (`id`), |
| KEY `FK_acc_gl_journal_entry_m_office` (`office_id`), |
| KEY `FK_acc_gl_journal_entry_m_appuser` (`createdby_id`), |
| KEY `FK_acc_gl_journal_entry_m_appuser_2` (`lastmodifiedby_id`), |
| KEY `FK_acc_gl_journal_entry_acc_gl_journal_entry` (`reversal_id`), |
| KEY `FK_acc_gl_journal_entry_acc_gl_account` (`account_id`), |
| CONSTRAINT `FK_acc_gl_journal_entry_acc_gl_account` FOREIGN KEY (`account_id`) REFERENCES `acc_gl_account` (`id`), |
| CONSTRAINT `FK_acc_gl_journal_entry_acc_gl_journal_entry` FOREIGN KEY (`reversal_id`) REFERENCES `acc_gl_journal_entry` (`id`), |
| CONSTRAINT `FK_acc_gl_journal_entry_m_appuser` FOREIGN KEY (`createdby_id`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `FK_acc_gl_journal_entry_m_appuser_2` FOREIGN KEY (`lastmodifiedby_id`) REFERENCES `m_appuser` (`id`), |
| CONSTRAINT `FK_acc_gl_journal_entry_m_office` FOREIGN KEY (`office_id`) REFERENCES `m_office` (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `acc_product_mapping` ( |
| `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| `gl_account_id` bigint(20) DEFAULT NULL, |
| `product_id` bigint(20) DEFAULT NULL, |
| `product_type` smallint(5) DEFAULT NULL, |
| `financial_account_type` smallint(5) DEFAULT NULL, |
| PRIMARY KEY (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| -- =========== end of accounting related tables ========== |
| |
| -- DDL for reporting related tables |
| CREATE TABLE `rpt_sequence` ( |
| `id` int(11) NOT NULL AUTO_INCREMENT, |
| PRIMARY KEY (`id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `stretchy_parameter` ( |
| `parameter_id` int(11) NOT NULL AUTO_INCREMENT, |
| `parameter_name` varchar(45) NOT NULL, |
| `parameter_variable` varchar(45) DEFAULT NULL, |
| `parameter_label` varchar(45) NOT NULL, |
| `parameter_displayType` varchar(45) NOT NULL, |
| `parameter_FormatType` varchar(10) NOT NULL, |
| `parameter_default` varchar(45) NOT NULL, |
| `special` varchar(1) DEFAULT NULL, |
| `selectOne` varchar(1) DEFAULT NULL, |
| `selectAll` varchar(1) DEFAULT NULL, |
| `parameter_sql` text, |
| `parent_parameter_id` int(11) NULL DEFAULT NULL, |
| PRIMARY KEY (`parameter_id`), |
| UNIQUE KEY `name_UNIQUE` (`parameter_name`), |
| INDEX `fk_stretchy_parameter_0001_idx` (`parent_parameter_id`), |
| CONSTRAINT `fk_stretchy_parameter_0001` FOREIGN KEY (`parent_parameter_id`) REFERENCES `stretchy_parameter` (`parameter_id`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `stretchy_report` ( |
| `report_id` int(11) NOT NULL AUTO_INCREMENT, |
| `report_name` varchar(100) NOT NULL, |
| `report_type` varchar(20) NOT NULL, |
| `report_subtype` varchar(20) DEFAULT NULL, |
| `report_category` varchar(45) DEFAULT NULL, |
| `report_sql` text, |
| `description` text, |
| `core_report` tinyint(1) DEFAULT '0', |
| `use_report` tinyint(1) DEFAULT '0', |
| PRIMARY KEY (`report_id`), |
| UNIQUE KEY `report_name_UNIQUE` (`report_name`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| |
| CREATE TABLE `stretchy_report_parameter` ( |
| `report_id` int(11) NOT NULL, |
| `parameter_id` int(11) NOT NULL, |
| `report_parameter_name` varchar(45) DEFAULT NULL, |
| PRIMARY KEY (`report_id`,`parameter_id`), |
| UNIQUE KEY `report_id_name_UNIQUE` (`report_id`,`report_parameter_name`) |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| -- =========== end of reporting related tables ============ |
