Understanding the PowerApps Solution File

create table account_close (
  account_close_id int not null,
  accounts_id int not null,
  sub_accounts_id int not null,
  close_date datetime not null,
  transaction_head_id int not null,
  constraint PK_account_close primary key (account_close_id)
);

create table account_groups (
  account_groups_id int not null,
  name nvarchar (1200) not null,
  group_type int not null,
  account_primary_group smallint not null,
  sequence_no smallint not null,
  other_name nvarchar (1200),
  constraint PK_account_groups primary key (account_groups_id)
);

create table accounts (
  accounts_id int not null,
  account_groups_id int not null,
  account_name nvarchar (1200) not null,
  account_type int not null,
  balance_type tinyint not null,
  sequence_no int not null,
  status bit not null,
  memberwise_acc bit not null,
  proper_account_name nvarchar (1200),
  english_name nvarchar (1200),
  constraint PK_accounts primary key (accounts_id)
);

create table accounts_family (
  accounts_family_id int not null,
  accounts_id int not null,
  related_accounts_id int not null,
  constraint PK_accounts_family primary key (accounts_family_id)
);

create table accounts_restricted (
  accounts_restricted_id int not null,
  accounts_id int not null,
  trans_type tinyint not null,
  constraint PK_restricted_accounts primary key (accounts_restricted_id)
);

create table accrued_interest (
  accrued_interest_id int not null,
  accounts_id int not null,
  sub_accounts_id int not null,
  int_date datetime not null,
  int_rate real not null,
  balance money not null,
  int_amount money not null,
  last_modified_on datetime not null,
  last_modified_by nvarchar (1200) not null,
  constraint PK_accrued_interest primary key (accrued_interest_id)
);

create table agent (
  agent_id int not null,
  members_id int not null,
  saving_accounts_id int not null,
  saving_sub_accounts_id int not null,
  tds_exemption_id int not null,
  security_accounts_id int not null,
  security_sub_accounts_id int not null,
  security_deposit_per real not null,
  security_deposit_limit money not null,
  status tinyint not null,
  tds_apply bit not null,
  mach_accounts_id int null,
  mach_sub_accounts_id int null,
  mach_deposit_perc_rs real null,
  mach_deposit_limit money null,
  mach_deduction_type bit null,
  tds_rate real null,
  constraint PK_agent primary key (agent_id)
);

create table agent_collection (
  agent_collection_id int not null,
  agent_id int not null,
  transaction_head_id int not null,
  trans_date datetime not null,
  trans_amount money not null,
  constraint PK_agent_collection primary key (agent_collection_id)
);

create table agent_collection_details (
  agent_collection_details_id int not null,
  agent_collection_id int not null,
  sub_accounts_id int not null,
  collection_amount money not null,
  collection_date datetime not null,
  receipt_no nvarchar (1200) not null,
  constraint PK_agent_collection_details primary key (agent_collection_details_id)
);

create table agent_details (
  agent_details_id int not null,
  agent_id int not null,
  accounts_id int not null,
  commission_rate real not null,
  constraint PK_agent_details primary key (agent_details_id)
);

create table agent_sub_accounts (
  agent_sub_accounts_id int not null,
  agent_id int not null,
  sub_accounts_id int not null,
  commission_rate real null,
  constraint PK_agent_sub_accounts primary key (agent_sub_accounts_id)
);

create table agent_transfer (
  agent_transfer_id int not null,
  from_agents_id int not null,
  to_agents_id int not null,
  trans_date datetime not null,
  constraint PK_agent_transfer primary key (agent_transfer_id)
);

create table agent_transfer_details (
  agent_transfer_details_id int not null,
  agent_transfer_id int not null,
  sub_accounts_id int not null,
  constraint PK_agent_transfer_details primary key (agent_transfer_details_id)
);

create table approval_rights (
  approval_rights_id int default 0 not null,
  members_id int default 0 not null,
  name nvarchar (1200) not null,
  constraint PK_approval_rights primary key (approval_rights_id)
);

create table bank (
  bank_id int not null,
  name nvarchar (1200) not null,
  code int not null,
  constraint PK_bank primary key (bank_id)
);

create table bank_accounts (
  bank_accounts_id int not null,
  sub_accounts_id int default 0 not null,
  last_modified_by nvarchar (1200) not null,
  name nvarchar (1200) not null,
  acc_no nvarchar (1200) not null,
  branch nvarchar (1200) not null,
  address nvarchar (1200) not null,
  acc_type smallint not null,
  against_invst bit default 0 not null,
  last_modified_on datetime not null,
  status tinyint not null,
  constraint PK_bank_accounts primary key (bank_accounts_id)
);

create table bank_branch (
  bank_branch_id int not null,
  bank_id int not null,
  city_id int not null,
  name nvarchar (1200) not null,
  ifsc nvarchar (1200) not null,
  address nvarchar (1200) not null,
  head_office bit not null,
  branch_code int not null,
  weekly_off smallint not null,
  clearing_day smallint not null,
  micr int not null,
  constraint PK_bank_branch primary key (bank_branch_id)
);

create table branch (
  branch_id int not null,
  name nvarchar (1200) not null,
  address nvarchar (1200) not null,
  reg_no nvarchar (1200) not null,
  opening_balance datetime not null,
  weekly_off tinyint not null,
  logo image null,
  note nvarchar (1200),
  version_info nvarchar (1200),
  gst_in nvarchar (1200),
  constraint PK_branch primary key (branch_id)
);

create table bs (
  bs_id int default 0 not null,
  financial_years_id int default 0 not null,
  accounts_id int default 0 not null,
  amount money not null,
  year_end_date datetime not null,
  users_id nvarchar (1200) not null,
  constraint PK_bs primary key (bs_id)
);

create table calender (
  calender_id int default 0 not null,
  financial_years_id int not null,
  month_id int default 0 not null,
  yearly_trans_dates datetime not null,
  day_type tinyint default 0 not null,
  day_end bit default 0 not null,
  current_day bit not null,
  description nvarchar (1200) not null,
  constraint PK_calender primary key (calender_id)
);

create table calender_details (
  calender_details_id int not null,
  session_id int not null,
  start_date datetime not null,
  start_time datetime not null,
  stop_time datetime not null,
  session_end bit not null,
  current_session bit not null,
  constraint PK_calender_details primary key (calender_details_id)
);

create table caste (
  caste_id int default 0 not null,
  name nvarchar (1200) not null,
  constraint PK_caste primary key (caste_id)
);

create table category (
  category_id int not null,
  name nvarchar (1200) not null,
  delete_flag int not null,
  constraint PK_category primary key (category_id)
);

create table charges (
  charges_id int not null,
  accounts_id int not null,
  charge_type int not null,
  description nvarchar (1200) not null,
  charge_amt money not null,
  per_amt smallint not null,
  service_tax real default 0 not null,
  tenure smallint null,
  constraint PK_charges primary key (charges_id)
);

create table cheque_in (
  cheque_in_id int default 0 not null,
  cheque_register_id int default 0 not null,
  drawn_on_bank nvarchar (1200) not null,
  branch nvarchar (1200) not null,
  constraint PK_cheque_in primary key (cheque_in_id)
);

create table cheque_register (
  cheque_register_id int not null,
  bank_accounts_id int not null,
  issue_date datetime not null,
  pay_to nvarchar (1200) not null,
  amount money not null,
  amount_in_word nvarchar (1200) not null,
  cheque_type smallint not null,
  clearence_date datetime not null,
  narration nvarchar (1200) not null,
  cheque_no int not null,
  status tinyint not null,
  constraint PK_cheque_register primary key (cheque_register_id)
);

create table cheque_return (
  cheque_return_id int not null,
  transaction_head_id int not null,
  cheque_register_id int null,
  constraint PK_cheque_return primary key (cheque_return_id)
);

create table cheque_transactions (
  cheque_transactions_id int default 0 not null,
  cheque_register_id int default 0 not null,
  transaction_head_id int default 0 not null,
  constraint PK_cheque_transactions primary key (cheque_transactions_id)
);

create table city (
  city_id int not null,
  taluka_id int not null,
  name nvarchar (1200) not null,
  pin int null,
  constraint PK_city primary key (city_id)
);

create table district (
  district_id int not null,
  name nvarchar (1200) not null,
  state_id int null,
  constraint PK_district primary key (district_id)
);

create table dividend_paid (
  dividend_paid_id int not null,
  dividend_register_id int not null,
  accounts_id int not null,
  sub_accounts_id int not null,
  transaction_head_id int not null,
  paid_amt money not null,
  paid_date datetime not null,
  constraint PK_dividend_paid primary key (dividend_paid_id)
);

create table dividend_register (
  dividend_register_id int not null,
  dividend_resolutions_id int default 0 not null,
  members_id int not null,
  transaction_head_id int default 0 not null,
  warrant_no int not null,
  warrant_date datetime not null,
  shares_balance money default 0 not null,
  dividend_amount money not null,
  constraint PK_dividend_register primary key (dividend_register_id)
);

create table dividend_resolutions (
  dividend_resolutions_id int not null,
  financial_years_id int not null,
  last_modified_by nvarchar (1200) not null,
  record_date datetime not null,
  dividend_per real not null,
  reso_date datetime not null,
  reso_no int not null,
  remarks nvarchar (1200) not null,
  last_modified_on datetime not null,
  member_class smallint null,
  constraint PK_dividend_resolutions primary key (dividend_resolutions_id)
);

create table dividend_setting (
  dividend_setting_id int default 0 not null,
  div_calc_type bit default 0 not null,
  eligibility bit default 0 not null,
  months_validity int default 0 not null,
  div_round bit default 0 not null,
  start_month int null,
  constraint PK_dividend_setting primary key (dividend_setting_id)
);

create table employees (
  employees_id int not null,
  members_id int not null,
  designation_employee_id int not null,
  current_employee bit default 0 not null,
  director_employee tinyint default 0 not null,
  constraint PK_employees primary key (employees_id)
);

create table fd_charges (
  fd_charges_id int not null,
  fd_schemes_id int not null,
  charges_id int not null,
  constraint PK_fd_charges primary key (fd_charges_id)
);

create table fd_extra_benefit (
  fd_extra_benefit_id int not null,
  fd_schemes_id int not null,
  system_constants_id int not null,
  constraint PK_fd_extra_benefit primary key (fd_extra_benefit_id)
);

create table fd_interest_post (
  fd_interest_post_id int not null,
  sub_accounts_id int not null,
  transaction_head_id int not null,
  amount money not null,
  int_date datetime not null,
  rate real not null,
  posting tinyint default 0 not null,
  changed_manually bit not null,
  constraint PK_fd_interest_post primary key (fd_interest_post_id)
);

create table fd_interest_rate (
  fd_interest_rate_id int not null,
  fd_schemes_id int not null,
  tenure int not null,
  tenure_type smallint not null,
  int_rate real not null,
  end_date datetime not null,
  constraint PK_fd_interest_rate primary key (fd_interest_rate_id)
);

create table fd_interest_rate_close (
  fd_interest_rate_close_id int not null,
  fd_schemes_id int not null,
  tenure int not null,
  tenure_type smallint not null,
  interest_rate real not null,
  change_date datetime not null,
  constraint PK_fd_interest_rate_close primary key (fd_interest_rate_close_id)
);

create table fd_mode_op (
  fd_mode_op_id int not null,
  fd_accounts_id int not null,
  mode_of_operation int not null,
  constraint PK_fd_mode_op primary key (fd_mode_op_id)
);

create table fd_nominee (
  fd_nominee_id int not null,
  fd_accounts_id int not null,
  occupation_id int not null,
  relation_id int not null,
  city_id int not null,
  location_id int not null,
  address nvarchar (1200) not null,
  pin int not null,
  telephone nvarchar (1200) not null,
  mobile nvarchar (1200) not null,
  fax nvarchar (1200) not null,
  email nvarchar (1200) not null,
  prefix tinyint not null,
  full_name nvarchar (1200) not null,
  gender tinyint not null,
  age smallint not null,
  [sequence] smallint default 0 not null,
  constraint PK_fd_nominee primary key (fd_nominee_id)
);

create table fd_schemes (
  fd_schemes_id int not null,
  accounts_id int not null,
  last_modified_by nvarchar (1200) not null,
  [type] smallint not null,
  name nvarchar (1200) not null,
  description nvarchar (1200) not null,
  receipt_prefix nvarchar (1200) not null,
  receipt_start_no int not null,
  multiplier real not null,
  interest_type tinyint not null,
  compounding tinyint not null,
  int_rate_after_mat real not null,
  return_amt_by tinyint not null,
  close_break_int_post tinyint not null,
  auto_renewal bit not null,
  open_end bit not null,
  status tinyint not null,
  last_modified_on datetime not null,
  compounding_wt_posting smallint null,
  discounted bit null,
  broken_period int null,
  int_rate_snr_citizen real null,
  no_int_upto_after_mat tinyint null,
  constraint PK_fd_schemes primary key (fd_schemes_id)
);

create table financial_years (
  financial_years_id int not null,
  start_date datetime not null,
  end_date datetime not null,
  locked bit default 0 not null,
  is_current bit default 0 not null,
  transaction_head_id int default -1 not null,
  constraint PK_financial_years primary key (financial_years_id)
);

create table head_office (
  head_office_id int not null,
  city_id int default 0 not null,
  location_id int default 0 not null,
  head_office_no nvarchar (1200) not null,
  name nvarchar (1200) not null,
  address nvarchar (1200) not null,
  telephone nvarchar (1200) not null,
  mobile nvarchar (1200) not null,
  fax nvarchar (1200) default '0' not null,
  email nvarchar (1200) default N 'default' not null,
  pin int not null,
  head_off_to_desig nvarchar (1200) not null,
  status bit default 1 not null,
  constraint PK_head_office primary key (head_office_id)
);

create table loan_accounts (
  loan_accounts_id int not null,
  sub_accounts_id int not null,
  loan_schemes_id int not null,
  approval_rights_id int not null,
  members_id int not null,
  guardian_emp_id int not null,
  last_modified_by nvarchar (1200) not null,
  loan_reason_id int not null,
  loan_amount money not null,
  interest_rate real not null,
  open_date datetime not null,
  first_installment_date datetime not null,
  tenure int not null,
  installment_amt money not null,
  total_installments int not null,
  end_date datetime not null,
  reso_no int not null,
  approval_date datetime not null,
  ledger_folio_no nvarchar (1200) not null,
  secured bit not null,
  account_remarks nvarchar (1200) not null,
  approval_remarks nvarchar (1200) not null,
  close_date datetime not null,
  status tinyint not null,
  last_modified_on datetime not null,
  total_recovery money null,
  posted_int money null,
  min_balance money null,
  constraint PK_loan_accounts primary key (loan_accounts_id)
);

create NONCLUSTERED INDEX approval_rights_id on loan_accounts (approval_rights_id asc);

create NONCLUSTERED INDEX close_date on loan_accounts (close_date asc);

create NONCLUSTERED INDEX end_date on loan_accounts (end_date asc);

create NONCLUSTERED INDEX first_installment_date on loan_accounts (first_installment_date asc);

create NONCLUSTERED INDEX guardian_emp_id on loan_accounts (guardian_emp_id asc);

create NONCLUSTERED INDEX installment_amt on loan_accounts (installment_amt asc);

create NONCLUSTERED INDEX interest_rate on loan_accounts (interest_rate asc);

create NONCLUSTERED INDEX loan_amount on loan_accounts (loan_amount asc);

create NONCLUSTERED INDEX loan_schemes_id on loan_accounts (loan_schemes_id asc);

create NONCLUSTERED INDEX members_id on loan_accounts (members_id asc);

create NONCLUSTERED INDEX open_date on loan_accounts (open_date asc);

create NONCLUSTERED INDEX status on loan_accounts (status asc);

create NONCLUSTERED INDEX sub_accounts_id on loan_accounts (sub_accounts_id asc);

create NONCLUSTERED INDEX tenure on loan_accounts (tenure asc);

create NONCLUSTERED INDEX total_installments on loan_accounts (total_installments asc);

create table loan_charges (
  loan_charges_id int not null,
  loan_schemes_id int not null,
  charges_id int not null,
  deduction tinyint not null,
  constraint PK_loan_charges primary key (loan_charges_id)
);

create table loan_co_applicant (
  loan_co_applicant_id int not null,
  loan_accounts_id int not null,
  members_id int not null,
  constraint PK_loan_co_applicant primary key (loan_co_applicant_id)
);

create table loan_disbursement (
  loan_disbursement_id int not null,
  loan_accounts_id int not null,
  transaction_head_id int not null,
  constraint PK_loan_disbursement primary key (loan_disbursement_id)
);

create table loan_guarantor (
  loan_guarantor_id int not null,
  loan_accounts_id int not null,
  members_id int not null,
  constraint PK_loan_guarantor primary key (loan_guarantor_id)
);

create table loan_interest_post (
  loan_interest_post_id int not null,
  sub_accounts_id int not null,
  transaction_head_id int not null,
  amount money not null,
  int_date datetime not null,
  due_amount money default 0 not null,
  rate real not null,
  posting tinyint default 0 not null,
  changed_manually bit not null,
  constraint PK_loan_interest_post primary key (loan_interest_post_id)
);

create NONCLUSTERED INDEX sub_accounts_id on loan_interest_post (sub_accounts_id asc);

create NONCLUSTERED INDEX transaction_head_id on loan_interest_post (transaction_head_id asc);

create table loan_interest_rate (
  loan_interest_rate_id int not null,
  loan_schemes_id int not null,
  tenure int not null,
  interest_rate real not null,
  penal_interest_rate real default 0 not null,
  max_limit money not null,
  end_date datetime not null,
  over_limit_rate real null,
  constraint PK_loan_interest_rate primary key (loan_interest_rate_id)
);

create table loan_overdue_charges (
  loan_overdue_charges_id int not null,
  loan_overdue_rpt_id int not null,
  loan_accounts_id int not null,
  users_id nvarchar (1200) not null,
  as_on_date datetime not null,
  total_charge_amt money null,
  charge_acc_name_1 nvarchar (1200),
  charge_acc_name_2 nvarchar (1200),
  charge_acc_name_3 nvarchar (1200),
  charge_acc_name_4 nvarchar (1200),
  charge_acc_name_5 nvarchar (1200),
  charge_acc_name_6 nvarchar (1200),
  charge_acc_name_7 nvarchar (1200),
  charge_acc_name_8 nvarchar (1200),
  charge_acc_name_9 nvarchar (1200),
  charge_acc_name_10 nvarchar (1200),
  charge_acc_id_1 int null,
  charge_acc_id_2 int null,
  charge_acc_id_3 int null,
  charge_acc_id_4 int null,
  charge_acc_id_5 int null,
  charge_acc_id_6 int null,
  charge_acc_id_7 int null,
  charge_acc_id_8 int null,
  charge_acc_id_9 int null,
  charge_acc_id_10 int null,
  charge_amt_1 money null,
  charge_amt_2 money null,
  charge_amt_3 money null,
  charge_amt_4 money null,
  charge_amt_5 money null,
  charge_amt_6 money null,
  charge_amt_7 money null,
  charge_amt_8 money null,
  charge_amt_9 money null,
  charge_amt_10 money null,
  money_1 money null,
  money_2 money null,
  money_3 money null,
  money_4 money null,
  money_5 money null,
  date_1 datetime null,
  date_2 datetime null,
  date_3 datetime null,
  date_4 datetime null,
  date_5 datetime null,
  constraint PK_loan_overdue_charges primary key (loan_overdue_charges_id)
);

create table loan_overdue_rpt (
  loan_overdue_rpt_id int not null,
  loan_accounts_id int not null,
  npa_type_settings_id int not null,
  as_on_date datetime not null,
  outstanding_amt money not null,
  total_recovery money not null,
  int_receivable money not null,
  penal_int_receivable money not null,
  other_receivable money not null,
  overdue_amt money not null,
  overdue_installments int not null,
  overdue_date datetime not null,
  security_value money not null,
  total_receivable_pro money not null,
  unsecure_receivable money not null,
  npa_since datetime not null,
  provision_secure money not null,
  provision_unsecure money not null,
  pro_sec_per float not null,
  pro_unsec_per float not null,
  total_provision money not null,
  unsecur_interest float not null,
  remark nvarchar (1200) not null,
  users_id nvarchar (1200),
  from_date datetime null,
  paid_interest money null,
  charges money null,
  opening_bal money null,
  current_recovery money null,
  constraint PK_loan_overdue_rpt primary key (loan_overdue_rpt_id)
);

create table loan_reason (
  loan_reason_id int not null,
  reason nvarchar (1200) not null,
  constraint PK_loan_reason primary key (loan_reason_id)
);

create table loan_repayment (
  loan_repayment_id int default 0 not null,
  loan_accounts_id int default 0 not null,
  loan_schedule_id int default 0 not null,
  transaction_head_id int default 0 not null,
  principal money default 0 not null,
  interest money default 0 not null,
  penal_int money not null,
  balance money default 0 not null,
  trans_date datetime not null,
  repayment_type tinyint not null,
  constraint PK_loan_repayment primary key (loan_repayment_id)
);

create NONCLUSTERED INDEX balance on loan_repayment (balance asc);

create NONCLUSTERED INDEX interest on loan_repayment (interest asc);

create NONCLUSTERED INDEX loan_accounts_id on loan_repayment (loan_accounts_id asc);

create NONCLUSTERED INDEX loan_schedule_id on loan_repayment (loan_schedule_id asc);

create NONCLUSTERED INDEX penal_int on loan_repayment (penal_int asc);

create NONCLUSTERED INDEX principal on loan_repayment (principal asc);

create NONCLUSTERED INDEX repayment_type on loan_repayment (repayment_type asc);

create NONCLUSTERED INDEX trans_date on loan_repayment (trans_date asc);

create NONCLUSTERED INDEX transaction_head_id on loan_repayment (transaction_head_id asc);

create table loan_schedule (
  loan_schedule_id int default 0 not null,
  loan_accounts_id int default 0 not null,
  installment_no int not null,
  installment_date datetime not null,
  principal money default 0 not null,
  interest money default 0 not null,
  balance money default 0 not null,
  paid bit default 0 not null,
  constraint PK_loan_schedule primary key (loan_schedule_id)
);

create NONCLUSTERED INDEX balance on loan_schedule (balance asc);

create NONCLUSTERED INDEX installment_date on loan_schedule (installment_date asc);

create NONCLUSTERED INDEX installment_no on loan_schedule (installment_no asc);

create NONCLUSTERED INDEX interest on loan_schedule (interest asc);

create NONCLUSTERED INDEX loan_accounts_id on loan_schedule (loan_accounts_id asc);

create NONCLUSTERED INDEX paid on loan_schedule (paid asc);

create NONCLUSTERED INDEX principal on loan_schedule (principal asc);

create table loan_schemes (
  loan_schemes_id int not null,
  accounts_id int default 0 not null,
  deposit_account_id int not null,
  last_modified_by nvarchar (1200) not null,
  name nvarchar (1200) not null,
  [type] smallint not null,
  int_days_month tinyint default 0 not null,
  installment_type tinyint not null,
  demand_loan bit default 0 not null,
  rebate_rate real default 0 not null,
  change_installment_amt bit not null,
  renew_per_same_scheme real default 0 not null,
  renew_per_other_scheme real default 0 not null,
  penelty_on tinyint default 0 not null,
  due_installments int default 0 not null,
  penal_installment_calc tinyint default 0 not null,
  total_guarantors int default 0 not null,
  gtr_regular int default 0 not null,
  gtr_nominal int default 0 not null,
  deposit_loan_limit real not null,
  deposit_loan_on tinyint default 1 not null,
  tenure_new_member int default 0 not null,
  tenure_retirement int default 0 not null,
  gtr_tenure_new int default 0 not null,
  gtr_tenure_retirement int default 0 not null,
  gtr_total_accounts int default 0 not null,
  gtr_total_loan_amt money default 0 not null,
  status tinyint default 0 not null,
  last_modified_on datetime not null,
  cross_gtr bit not null,
  hpteband bit not null,
  interest_as_on bit null,
  oir_provision tinyint null,
  compounding_freq tinyint null,
  set_first_installment_date bit null,
  constraint PK_loan_schemes primary key (loan_schemes_id)
);

create table loan_settings (
  loan_settings_id int default 0 not null,
  max_loan_tenure int not null,
  max_installment int not null,
  gtr_tenure_new int default 0 not null,
  gtr_tenure_retirement int default 0 not null,
  gtr_total_accounts int default 0 not null,
  gtr_total_loan_amt money default 0 not null,
  gtr_loan_amt_limit_on tinyint not null,
  tenure_new_member int default 0 not null,
  tenure_retirement int default 0 not null,
  cross_gtr bit not null,
  constraint PK_loan_settings primary key (loan_settings_id)
);

create table location (
  location_id int not null,
  name nvarchar (1200) not null,
  constraint PK_location primary key (location_id)
);

create table member_address_contact (
  member_address_contact_id int not null,
  members_id int not null,
  city_id int default 0 not null,
  location_id int default 0 not null,
  address nvarchar (1200) not null,
  pin int not null,
  telephone nvarchar (1200) not null,
  mobile nvarchar (1200) not null,
  fax nvarchar (1200) not null,
  email nvarchar (1200) not null,
  constraint PK_member_address_contact primary key (member_address_contact_id)
);

create table member_address_permanent (
  member_address_permanent_id int not null,
  members_id int not null,
  city_id int default 0 not null,
  location_id int default 0 not null,
  address nvarchar (1200) not null,
  pin int not null,
  telephone nvarchar (1200) not null,
  mobile nvarchar (1200) not null,
  fax nvarchar (1200) not null,
  email nvarchar (1200) not null,
  constraint PK_member_address_permanent primary key (member_address_permanent_id)
);

create table member_approval (
  member_approval_id int not null,
  members_id int not null,
  approval_rights_id int default -1 not null,
  intention nvarchar (1200) not null,
  approve_date datetime not null,
  comment nvarchar (1200) not null,
  resolution_no nvarchar (50) not null,
  dividend_transfer_accounts_id int null,
  dividend_transfer_sub_accounts_id int null,
  employees_id int null,
  constraint PK_member_approval primary key (member_approval_id)
);

create table member_death (
  member_death_id int not null,
  members_id int null,
  death_dte date null,
  reason varchar(50),
  constraint PK_member_death primary key (member_death_id)
);

create table member_fund_transfer (
  member_fund_transfer_id int not null,
  ft_bank_branch_id int not null,
  members_id int not null,
  bank_account_no nvarchar (1200) not null,
  account_type nvarchar (1200) not null,
  constraint PK__member_f__2CE338406D59ADBC primary key (member_fund_transfer_id)
);

create table member_job_info (
  member_job_info_id int not null,
  members_id int not null,
  department_id int default 0 not null,
  designation_member_id int default 0 not null,
  emp_code nvarchar (1200) not null,
  confirmation_date datetime not null,
  retirement_date datetime not null,
  savings_acc_details nvarchar (1200) not null,
  basic_sal money default 0 not null,
  other_allowances money not null,
  gross_sal money not null,
  sub_department_id int null,
  constraint PK_member_job_info primary key (member_job_info_id)
);

create table member_kyc_document (
  member_kyc_document_id int not null,
  members_id int not null,
  identity_proof smallint not null,
  address_proof smallint not null,
  narration nvarchar (1200) not null,
  constraint PK_member_kyc_document primary key (member_kyc_document_id)
);

create table member_name_change (
  member_name_change_id int not null,
  members_id int not null,
  last_modified_by nvarchar (1200) not null,
  old_name nvarchar (1200) not null,
  last_modified_on datetime not null,
  constraint PK_member_name_change primary key (member_name_change_id)
);

create table member_nominee (
  member_nominee_id int not null,
  members_id int not null,
  occupation_id int default 0 not null,
  relation_id int default 0 not null,
  city_id int default 0 not null,
  location_id int default 0 not null,
  address nvarchar (1200) not null,
  telephone nvarchar (1200) not null,
  mobile nvarchar (1200) not null,
  fax nvarchar (1200) not null,
  email nvarchar (1200) not null,
  prefix tinyint not null,
  full_name nvarchar (1200) not null,
  gender tinyint not null,
  age smallint not null,
  [sequence] smallint default 0 not null,
  constraint PK_member_nominee_1 primary key (member_nominee_id)
);

create table members (
  members_id int not null,
  offices_id int not null,
  occupation_id int not null,
  caste_id int not null,
  last_modified_by nvarchar (1200) not null,
  member_option_value_id_1 int not null,
  member_option_value_id_2 int not null,
  member_option_value_3 nvarchar (1200) not null,
  member_option_value_4 nvarchar (1200) not null,
  [type] tinyint not null,
  appli_date datetime not null,
  prefix tinyint not null,
  full_name nvarchar (1200) not null,
  gender tinyint not null,
  birth_date datetime not null,
  age smallint not null,
  pan_no nvarchar (1200) not null,
  esi_epf_no nvarchar (1200) not null,
  ledger_folio_no nvarchar (1200) not null,
  fixed_asset_remark nvarchar (1200) not null,
  close_date datetime not null,
  status tinyint not null,
  last_modified_on datetime not null,
  transfer_history nvarchar (1200),
  adhar_id bigint null,
  constraint PK_members primary key (members_id)
);

create table [month] (
  month_id int not null,
  name nvarchar (1200) not null,
  constraint PK_month_id primary key (month_id)
);

create table month_end (
  month_end_id int default 0 not null,
  month_id int default 0 not null,
  financial_years_id int not null,
  month_end bit default 0 not null,
  quarter_end bit default 0 not null,
  constraint PK_month_end primary key (month_end_id)
);

create table nominee (
  nominee_id int not null,
  sub_accounts_id int not null,
  relation_id int default 0 not null,
  prefix tinyint not null,
  full_name nvarchar (1200) not null,
  age smallint not null,
  address nvarchar (1200) not null,
  nomination_date datetime not null,
  shares_percentage int not null,
  guardian_name nvarchar (1200) not null,
  guardian_address nvarchar (1200) not null,
  guardian_relation_id int not null,
  [sequence] smallint default 0 not null,
  constraint PK_nominee primary key (nominee_id)
);

create table occupation (
  occupation_id int not null,
  name nvarchar (1200) not null,
  constraint PK_occupation primary key (occupation_id)
);

create table recurring_accounts (
  recurring_accounts_id int not null,
  sub_accounts_id int not null,
  recurring_schemes_id int not null,
  members_id int not null,
  last_modified_by nvarchar (1200) not null,
  open_date datetime not null,
  monthly_amount money not null,
  period int not null,
  interest_rate real not null,
  maturity_date datetime not null,
  maturity_amount money not null,
  ledger_folio_no nvarchar (1200) not null,
  account_remarks nvarchar (1200) not null,
  approval_remarks nvarchar (1200) not null,
  tds_apply bit not null,
  tds_exemption_id int default 0 not null,
  close_date datetime not null,
  status tinyint not null,
  last_modified_on datetime not null,
  constraint PK_recurring_accounts primary key (recurring_accounts_id)
);

create table recurring_charges (
  recurring_charges_id int not null,
  recurring_schemes_id int not null,
  charges_id int not null,
  constraint PK_recurring_charges primary key (recurring_charges_id)
);

create table recurring_interest_post (
  recurring_interest_post_id int not null,
  sub_accounts_id int not null,
  transaction_head_id int not null,
  amount money not null,
  int_date datetime not null,
  rate real not null,
  posting tinyint default 0 not null,
  changed_manually bit not null,
  constraint PK_recurring_interest_post primary key (recurring_interest_post_id)
);

create table recurring_interest_rate (
  recurring_interest_rate_id int default 0 not null,
  recurring_schemes_id int default 0 not null,
  tenure int default 0 not null,
  int_rate real default 0 not null,
  end_date datetime not null,
  constraint PK_recurring_interest_rate primary key (recurring_interest_rate_id)
);

create table recurring_mode_op (
  recurring_mode_op_id int not null,
  recurring_accounts_id int not null,
  mode_of_operation int not null,
  constraint PK_recurring_mode_op primary key (recurring_mode_op_id)
);

create table recurring_schemes (
  recurring_schemes_id int not null,
  accounts_id int default 0 not null,
  last_modified_by nvarchar (1200) not null,
  name nvarchar (1200) not null,
  description nvarchar (1200) not null,
  open_end bit default 0 not null,
  interest_type tinyint default 0 not null,
  compounding tinyint default 2 not null,
  salarywise_monthly_amt bit default 0 not null,
  status tinyint default 0 not null,
  no_int_upto tinyint default 0 not null,
  premat_close_penalty_month tinyint default 0 not null,
  premat_close_penalty_rate real default 0 not null,
  missing_inst_for_penalty tinyint default 0 not null,
  missing_inst_penalty_rate real default 0 not null,
  last_modified_on datetime not null,
  compounding_wt_posting tinyint default 0 null,
  premat_close_int_deduct_rate real default 0 null,
  missing_inst_penalty_per_amt smallint default 0 null,
  int_rate_snr_citizen real null,
  cr_trans_after_maturity_date bit null,
  constraint PK_recurring_schemes primary key (recurring_schemes_id)
);

create table relation (
  relation_id int default 0 not null,
  relation nvarchar (1200) not null,
  constraint PK_relation primary key (relation_id)
);

create table religion (
  religion_id int not null,
  name nvarchar (1200) not null,
  constraint PK_religion primary key (religion_id)
);

create table saving_accounts (
  saving_accounts_id int not null,
  sub_accounts_id int not null,
  saving_schemes_id int not null,
  members_id int not null,
  last_modified_by nvarchar (1200) not null,
  open_date datetime not null,
  interest_rate real not null,
  ledger_folio_no nvarchar (1200) not null,
  account_remarks nvarchar (1200) not null,
  approval_remarks nvarchar (1200) not null,
  tds_apply bit not null,
  tds_exemption_id int default 0 not null,
  status tinyint not null,
  close_date datetime not null,
  last_modified_on datetime not null,
  constraint PK_saving_accounts primary key (saving_accounts_id)
);

create table saving_charges (
  saving_charges_id int not null,
  saving_schemes_id int not null,
  charges_id int not null,
  constraint PK_saving_charges primary key (saving_charges_id)
);

create table saving_interest_post (
  saving_interest_post_id int not null,
  sub_accounts_id int not null,
  transaction_head_id int not null,
  amount money not null,
  int_date datetime not null,
  rate real not null,
  posting tinyint not null,
  changed_manually bit not null,
  constraint PK_saving_interest_post primary key (saving_interest_post_id)
);

create table saving_interest_rate (
  saving_interest_rate_id int not null,
  saving_schemes_id int not null,
  int_rate real not null,
  end_date datetime not null,
  constraint PK_saving_interest_rate primary key (saving_interest_rate_id)
);

create table saving_introducer (
  saving_introducer_id int not null,
  saving_accounts_id int not null,
  members_id int not null,
  constraint PK_saving_introducer primary key (saving_introducer_id)
);

create table saving_joint_accounts (
  saving_joint_accounts_id int not null,
  saving_accounts_id int not null,
  members_id int not null,
  minor bit not null,
  constraint PK_saving_joint_accounts primary key (saving_joint_accounts_id)
);

create table saving_mode_op (
  saving_mode_op_id int not null,
  saving_accounts_id int not null,
  mode_of_operation int not null,
  constraint PK_saving_mode_op primary key (saving_mode_op_id)
);

create table saving_nominee (
  saving_nominee_id int not null,
  saving_accounts_id int not null,
  occupation_id int not null,
  relation_id int not null,
  city_id int not null,
  location_id int not null,
  address nvarchar (1200) not null,
  pin nvarchar (1200) not null,
  telephone nvarchar (1200) not null,
  mobile nvarchar (1200) not null,
  fax nvarchar (1200) not null,
  email nvarchar (1200) not null,
  prefix nvarchar (1200) not null,
  full_name nvarchar (1200) not null,
  gender tinyint not null,
  age smallint not null,
  [sequence] smallint default 0 not null,
  constraint PK_saving_nominee primary key (saving_nominee_id)
);

create table saving_schemes (
  saving_schemes_id int not null,
  accounts_id int not null,
  last_modified_by nvarchar (1200) not null,
  [type] smallint not null,
  name nvarchar (1200) not null,
  min_age smallint default 0 not null,
  min_balance money not null,
  max_withdrawal money not null,
  min_withdrawal money default 0 not null,
  max_deposit money not null,
  min_deposit money default 0 not null,
  month_start int default 0 not null,
  interest_calc tinyint not null,
  start_int_month int default 0 not null,
  transaction_limit int not null,
  status tinyint not null,
  last_modified_on datetime not null,
  min_int_setting tinyint null,
  min_int_amt money null,
  constraint PK_saving_schemes primary key (saving_schemes_id)
);

create table share_close (
  share_close_id int not null,
  members_id int not null,
  transaction_head_id int default 0 not null,
  resolution_no nvarchar (1200) not null,
  approval_date datetime not null,
  close_date datetime not null,
  shares_id int null,
  constraint PK_share_close primary key (share_close_id)
);

create table share_rules (
  share_rules_id int not null,
  last_modified_by nvarchar (1200) not null,
  face_value money not null,
  series nvarchar (1200) not null,
  prev_approved_capital money not null,
  approved_capital money not null,
  limit_per_member money not null,
  change_date datetime not null,
  fd_scheme_id int default 0 not null,
  last_modified_on datetime not null,
  renew_certificate_no bit null,
  class smallint null,
  constraint PK_share_rules primary key (share_rules_id)
);

create table shares (
  shares_id int not null,
  share_rules_id int not null,
  members_id int not null,
  transaction_head_id int default 0 not null,
  share_no_from int not null,
  share_no_to int not null,
  certi_no int not null,
  share_amount money not null,
  issue_date datetime not null,
  resolution_no int not null,
  approval_date datetime not null,
  mode tinyint not null,
  status tinyint not null,
  constraint PK_shares primary key (shares_id)
);

create table state (
  state_id int not null,
  name nvarchar (1200) not null,
  constraint PK_state primary key (state_id)
);

create table sub_account_type (
  sub_account_type_id int not null,
  name nvarchar (1200) not null,
  s_group int not null,
  constraint PK_sub_account_type primary key (sub_account_type_id)
);

create table sub_accounts (
  sub_accounts_id int not null,
  accounts_id int not null,
  members_id int not null,
  sub_accounts_no int not null,
  name nvarchar (1200) not null,
  status tinyint not null,
  constraint PK_sub_accounts primary key (sub_accounts_id)
);

create table sub_accounts_balance (
  sub_accounts_balance_id int not null,
  accounts_id int not null,
  sub_accounts_id int not null,
  balance money default 0 not null,
  [date] datetime not null,
  trans_by nvarchar (1200) not null,
  transaction_head_id int not null,
  constraint PK_sub_accounts_balance primary key (sub_accounts_balance_id)
);

create table sub_accounts_short_name (
  sub_accounts_short_name_id int not null,
  agent_id int not null,
  sub_accounts_id int not null,
  short_name nvarchar (1200) not null,
  smart_card_no nvarchar (1200) not null,
  constraint PK_sub_accounts_short_name primary key (sub_accounts_short_name_id)
);

create table sub_category (
  sub_category_id int not null,
  name nvarchar (1200) not null,
  delete_flag int not null,
  constraint PK_sub_category primary key (sub_category_id)
);

create table sub_department (
  sub_department_id int default 0 not null,
  department_id int default 0 not null,
  sub_department nvarchar (1200) not null,
  status bit default 1 not null,
  constraint PK_sub_department primary key (sub_department_id)
);

create table sub_type_of_category (
  sub_type_of_category_id int not null,
  name nvarchar (1200) not null,
  delete_flag int not null,
  constraint PK_sub_type_of_category primary key (sub_type_of_category_id)
);

create table system_constants (
  system_constants_id int not null,
  [type] int not null,
  constant int not null,
  constant_name nvarchar (1200) not null,
  constraint PK_system_constants primary key (system_constants_id)
);

create table system_settings (
  system_settings_id int default 0 not null,
  district_id int default 0 not null,
  taluka_id int default 0 not null,
  city_id int default 0 not null,
  min_member_age smallint default 0 not null,
  single_user bit default 0 not null,
  cashier_setting bit default 0 not null,
  denomination_setting bit default 0 not null,
  photo_identification bit default 0 not null,
  passing bit default 0 not null,
  cash_limit money default -1 not null,
  eng_Name bit default 0 not null,
  last_modified_on datetime not null,
  last_modified_by nvarchar (1200) not null,
  trans_at_close smallint default 1 null,
  current_projects smallint null,
  renew_certificate_no bit null,
  overdue_calc_type smallint null,
  senior_citizen_age smallint null,
  reset_voucher_no_daily bit null,
  voucher_no_serially bit null,
  c_class_shares bit null,
  b_class_shares bit null,
  unclear_balance bit null,
  unpass_balance bit null,
  demand_update smallint null,
  demand_zero_share_bal bit null,
  print_voucher bit null,
  advance_interest bit null,
  password_expiry_duration smallint null,
  old_passwords tinyint null,
  emp_code_as_number bit null,
  rec_close_extra_month bit null,
  photo_sign_for_dr_trans bit null,
  state_id int null,
  master_passing bit null,
  agent_commision_limit real null,
  auto_logout int null,
  tds_limit money null,
  member_cash_daily money null,
  member_cash_yearly money null,
  constraint PK_system_settings primary key (system_settings_id)
);

create table taluka (
  taluka_id int not null,
  district_id int not null,
  name nvarchar (1200) not null,
  constraint PK_taluka primary key (taluka_id)
);

create table tds_exemption (
  tds_exemption_id int not null,
  reason nvarchar (1200) not null,
  constraint PK_tds_exemption primary key (tds_exemption_id)
);

create table trans_groups (
  trans_groups_id int not null,
  transaction_head_id int default 0 not null,
  trans_group_no int not null,
  constraint PK_trans_groups primary key (trans_groups_id)
);

create table transaction_details (
  transaction_details_id int not null,
  transaction_head_id int not null,
  accounts_id int not null,
  sub_accounts_id int not null,
  trans_amount money not null,
  constraint PK_transaction_details primary key (transaction_details_id)
);

create NONCLUSTERED INDEX accounts_id on transaction_details (accounts_id asc);

create NONCLUSTERED INDEX sub_accounts_id on transaction_details (sub_accounts_id asc);

create NONCLUSTERED INDEX trans_amount on transaction_details (trans_amount asc);

create NONCLUSTERED INDEX transaction_head_id on transaction_details (transaction_head_id asc);

create table transaction_head (
  transaction_head_id int not null,
  voucher_master_id int default 0 not null,
  financial_years_id int default 0 not null,
  users_id nvarchar (1200) not null,
  session_id int default 0 not null,
  branch_id int default 0 not null,
  voucher_no int default 0 not null,
  trans_date datetime not null,
  trans_time datetime not null,
  trans_amount money not null,
  trans_narration nvarchar (1200) not null,
  mode_of_operation tinyint default 0 not null,
  constraint PK_transaction_head primary key (transaction_head_id)
);

create NONCLUSTERED INDEX trans_date on transaction_head (trans_date asc);

create NONCLUSTERED INDEX voucher_master_id on transaction_head (voucher_master_id asc);

create table transaction_passing (
  transaction_passing_id int default 0 not null,
  transaction_head_id int default 0 not null,
  users_id nvarchar (1200) not null,
  passing_date datetime not null,
  passing_time datetime not null,
  constraint PK_transaction_passing primary key (transaction_passing_id)
);

create NONCLUSTERED INDEX transaction_head_id on transaction_passing (transaction_head_id asc);

create table fd_accounts (
  fd_accounts_id int not null,
  sub_accounts_id int not null,
  fd_schemes_id int not null,
  members_id int not null,
  last_modified_by nvarchar (1200) not null,
  tenure_months int not null,
  tenure_days int not null,
  fd_amount money not null,
  open_date datetime not null,
  interest_start_date datetime not null,
  interest_rate real not null,
  maturity_date datetime not null,
  maturity_amount money not null,
  receipt_prefix nvarchar (1200),
  receipt_no int not null,
  receipt_date datetime not null,
  reinvest_interest bit not null,
  reinvest_int_frequency tinyint not null,
  renewal_amt_type tinyint not null,
  interest_transfer_to int not null,
  int_transfer_freq tinyint not null,
  transfer_at_close int not null,
  ledger_folio_no nvarchar (1200) not null,
  account_remarks nvarchar (1200) not null,
  approval_remarks nvarchar (1200) not null,
  tds_apply bit not null,
  tds_exemption_id int default 0 not null,
  status tinyint not null,
  last_modified_on datetime not null,
  no_of_prints tinyint default 0 not null,
  discounted_interest_rate real null,
  account_type smallint null,
  constraint PK_fd_accounts primary key (fd_accounts_id),
  constraint FK_fd_accounts_fd_schemes foreign KEY (fd_schemes_id) references fd_schemes (fd_schemes_id)
);