      set out_err_code = l_err_code;
      set out_err_desc = l_err_desc;
		end;
    start transaction;
       set l_routine_block = 'update';   
         begin
          simple_loop: LOOP
			         SET a=a+1;
			         SET in_student_id=SPLIT_STR(in_student_ids,',',a);
			         IF in_student_id='' THEN
			            LEAVE simple_loop;
			         END IF;
      			IF ( in_admission_num_gen_mode='Y'  AND  in_stud_status=100 ) THEN 
      				call sp_custom_seq_generator(in_inst_id,'STUD_ADMISSION_NUM',in_ay_id,in_created_by,l_admission_num) ;
      				IF length(trim(l_admission_num)) = 0 THEN
      				   SIGNAL SQLSTATE '10006' 
      				  SET MESSAGE_TEXT = 'An error occurred while generating Student Admission Num';
      				END IF;
      			END IF;		
      			IF ( in_stud_num_gen_mode='Y' AND  in_stud_status=100 ) THEN 
      					call sp_custom_seq_generator(in_inst_id,'STUD_GUID',in_ay_id,in_created_by,l_stud_num) ;
      					IF length(trim(l_stud_num)) = 0 THEN
      					   SIGNAL SQLSTATE '10009' 
      					    SET MESSAGE_TEXT = 'An error occurred while generating Student Num';
      					END IF;
      			END IF;
            select enrolled_for_std INTO l_standard_id FROM student_master 
                    where student_id=in_student_id and inst_id=in_inst_id 
                    and is_active=1 and is_deleted=0 and ay_id=in_ay_id;
               update student_master set 
              		standard_id=l_standard_id,
              		student_num=l_stud_num,
                  admission_num=l_admission_num,
                  stud_status=in_stud_status
                  where inst_id =in_inst_id and student_id=in_student_id 
                  and is_active=1 and is_deleted=0 ;
            Update user_master SET role_id=22 
            WHERE inst_id =in_inst_id and user_id=in_student_id 
                  and is_active=1 and is_deleted=0 AND user_type='STU' ;
      	CALL `sp_ins_user_role_map`('U', in_inst_id, 'STU', in_student_id, 
          22, in_created_by, l_err_code, l_err_desc);
				CALL `sp_ins_user_role_map_entitlements`('U', in_inst_id, 'STU',
          in_student_id, in_created_by, l_err_code, l_err_desc);
          END LOOP simple_loop;
        end;   
    commit;
    set out_err_code = l_err_code;
      set out_err_desc = l_err_desc;
end$$

DROP PROCEDURE IF EXISTS `sp_temp_tb_use_to_update_student_info`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_temp_tb_use_to_update_student_info`(IN `in_dml_mode` CHAR(1), OUT `out_err_code` CHAR(5), OUT `out_err_desc` TEXT)
BEGIN
declare l_routine_name varchar(50) default 'sp_temp_tb_use_to_update_student_info';
declare l_err_code char(5) default '00000';
declare l_err_desc text default '';        
declare l_routine_block varchar(15);
declare l_looping_done int default false;
declare l_student_id smallint(5) default 0; 
declare l_delete_flag tinyint(1) default 0;   
declare l_gr_number varchar(50);
declare l_religion varchar(50);
declare l_student_subcategory varchar(50);
declare l_student_category varchar(50);
declare l_birth_place varchar(50);
declare l_enrollment_date date;
declare l_nationality varchar(50);
  

declare cur_stud_mst cursor for 
  select student_id
  from temp_tb_use_to_update_student_info;

 declare continue handler for not found set l_looping_done = true;
declare exit handler for sqlexception
    begin
    rollback;
    
    get diagnostics condition 1
      l_err_code = RETURNED_SQLSTATE, l_err_desc = MESSAGE_TEXT;

    set out_err_code = l_err_code;
    set out_err_desc = l_err_desc;

       end;
open cur_stud_mst;

  cursor_loop_new:loop
     fetch cur_stud_mst into l_student_id;
         if l_looping_done then
             leave cursor_loop_new;
         end if;
      
  start transaction;
  
    case in_dml_mode 
     
        when 'U' then
    
        begin
          set l_routine_block = 'update';

              select gr_number,religion,student_subcategory,student_category, birth_place,
                enrollment_date,nationality
                INTO l_gr_number,l_religion,l_student_subcategory,l_student_category, l_birth_place,
                l_enrollment_date,l_nationality
                from temp_tb_use_to_update_student_info WHERE student_id=l_student_id;

          
              update student_master set valid_to = now(), 
                gr_number=l_gr_number,religion=l_religion,student_subcategory=l_student_subcategory,
                student_category=l_student_category, birth_place=l_birth_place,
                enrollment_date=l_enrollment_date,nationality=l_nationality
               where student_id = l_student_id and is_active=1 and is_deleted=0 ;

        end;    

    end case;
    end loop;
  commit;
  set out_err_code = l_err_code;
  set out_err_desc = l_err_desc;
END$$

DROP PROCEDURE IF EXISTS `update_student_data_random_requirement`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_student_data_random_requirement`(IN `in_inst_id` SMALLINT(5), IN `in_ay_id` VARCHAR(100), OUT `out_err_code` CHAR(5), OUT `out_err_desc` TEXT)
BEGIN
declare l_routine_name varchar(50) default 'update_student_data_random_requirement';
declare l_err_code char(5) default '00000';
declare l_err_desc text default '';        
declare l_routine_block varchar(15);
declare l_looping_done int default false;
declare l_student_id mediumint(5) default 0; 
declare l_delete_flag tinyint(1) default 0;  
  
  declare	l_c1	text default '';	
declare	l_c2	text default '';	
declare	l_c3	text default '';	
declare	l_c4	text default '';	
declare	l_c5	text default '';	
declare	l_c6	text default '';	
declare	l_c7	text default '';	
declare	l_c8	text default '';	
declare	l_c9	text default '';	
declare	l_c10	text default '';	
declare	l_c11	text default '';	
declare	l_c12	text default '';	
declare	l_c13	text default '';	
declare	l_c14	text default '';	
declare	l_c15	text default '';	
declare	l_c16	text default '';	
declare	l_c17	text default '';	
declare	l_c18	text default '';	
declare	l_c19	text default '';	
declare	l_c20	text default '';	
declare	l_c21	text default '';	
declare	l_c22	text default '';	
declare	l_c23	text default '';	
declare	l_c24	text default '';	
declare	l_c25	text default '';	

  declare l_is_back_data_exist text default '';
  declare l_contact_id_1 text default '';
  declare l_contact_id_2 text default '';
  declare l_is_opt_data_exist text default '';
  declare l_parent_id int ;
  
   
--   declare l_parent_id   mediumint(8);	  
--    declare l_middle_name    varchar(50);
--    declare l_last_name   varchar(50);
--    declare l_mobile_phone    varchar(50);
 

-- declare cur_csv_data cursor for 
--   select student_id, middle_name, last_name, mobile_phone_main from student_master 
--    where inst_id=in_inst_id and ay_id=in_ay_id and (contact_id_1=0 OR contact_id_1 is NULL) and stud_status=100 and is_active=1
--   and is_deleted=0;

  declare cur_csv_data cursor for 
  select student_id, c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25 
    FROM rough_work_temp_student_master 
   where inst_id=in_inst_id and ay_id=in_ay_id and student_id!='';

 declare continue handler for not found set l_looping_done = true;
declare exit handler for sqlexception
    begin
    rollback;
    
    get diagnostics condition 1
      l_err_code = RETURNED_SQLSTATE, l_err_desc = MESSAGE_TEXT;

   

    set out_err_code = l_err_code;
    set out_err_desc = l_err_desc;

       end;
open cur_csv_data;

  cursor_loop_new:loop
     fetch cur_csv_data into l_student_id,
      l_c1,l_c2,l_c3,l_c4,l_c5,l_c6,l_c7,l_c8,l_c9,l_c10,l_c11,l_c12,l_c13,l_c14,l_c15,l_c16,
      l_c17,l_c18,l_c19,l_c20,l_c21,l_c22,l_c23,l_c24,l_c25;
         if l_looping_done then
             leave cursor_loop_new;
         end if;
      
  start transaction;

    #parent data insert
  
--     if(l_middle_name!='' AND l_last_name!='')then
-- 
--      set l_parent_id = fn_sequence_generator('PARENT_MASTER');
-- 
--           insert into  parent_master (ay_id,inst_id,parent_id, salutation,first_name,middle_name,last_name, 
-- 
--                    	gender,relation_type,relation,mobile_phone_1, is_active,created_by, updated_by) 	 						
-- 
--             values(in_ay_id,in_inst_id,l_parent_id,'MR.',l_middle_name,'', l_last_name,
--             
--             'M' , 'PARENT' ,'FATHER' ,l_mobile_phone, '1', ' ', ' ');
-- 
--      update student_master set contact_id_1=l_parent_id where student_id=l_student_id and is_deleted='0' ;
-- 
--   end if;


  #update data of parent





    #update student info

--     Update student_master set  gender=l_c4,birth_place=l_c5 ,blood_group=l_c6,
--       aadhar_card_num=l_c7,address_line_1=l_c16 ,religion=l_c17,
--       student_category=l_c20,student_subcategory=l_c18 ,gr_number=l_c21
--       WHERE inst_id = in_inst_id AND ay_id = in_ay_id AND is_deleted= 0  AND student_id=l_student_id;


--      Update student_master set  aadhar_card_num=l_caste_income_cer_no
--       WHERE inst_id = in_inst_id AND is_deleted= 0  AND student_id=l_student_id and is_active=1;


    # insert bank details of student

--     if(l_account_number!='' AND l_ifsc_code!='')then
-- 
--       select count(l_student_id) into l_is_back_data_exist from fin_bank_account_master where inst_id=in_inst_id
--         and party_id=l_student_id and party_type='STU';
-- 
--       if (l_is_back_data_exist = 0) then
--     set l_bank_id = fn_sequence_generator('ADD_BANK_ACCOUNT');
--             
--     
--            insert into fin_bank_account_master (ay_id, inst_id,bank_id,bank_name,
--                    bank_display_name,account_no,ifsc_code,status,
--                    party_id,party_type,is_active,created_by,updated_by) 
--                                         
--                                values(in_ay_id, in_inst_id,l_bank_id,l_bank_name,
--                                 l_bank_name,l_account_number,l_ifsc_code,'1',
--                                 l_student_id,'STU','1','','');
--           end if;
--  end if;

    #optional field data insert

--     select COUNT(user_id) INTO l_is_opt_data_exist
--               from optional_field_details where inst_id=in_inst_id and ay_id=in_ay_id
--             and user_id=l_student_id AND user_type='STU' and is_active=1 and is_deleted=0;
-- 
--     if(l_father_income!='')then
-- 
--         if(l_is_opt_data_exist=0)then
--         
--             INSERT INTO optional_field_details(inst_id, ay_id, field_set_id, country_id, state_id, board_id, 
--               user_type, user_id, f12, f29, is_active, is_deleted) 
--             values(in_inst_id, in_ay_id,'1-18-KSEEB','1','18','KSEEB','STU',l_student_id,l_caste_income_cer_no,
--       l_father_income ,'1','0');
--         
--         else
--         
--             #optional field data update
--         
--              Update optional_field_details set  f29=l_father_income, f12=l_caste_income_cer_no
--                      WHERE inst_id = in_inst_id AND is_deleted= 0  AND user_id=l_student_id AND user_type='STU';
--         
--         end if;
--     end if;


    #update parents (mother-father) data

        select contact_id_1,contact_id_2 into l_contact_id_1 ,  l_contact_id_2
          from student_master where inst_id=in_inst_id and ay_id=in_ay_id
        and student_id=l_student_id and is_active=1 and is_deleted=0;


    if((l_contact_id_1=0 OR l_contact_id_1 is null OR l_contact_id_1='') and l_c8!='')then

      set l_parent_id = fn_sequence_generator('PARENT_MASTER');

      insert into parent_master(ay_id, inst_id,parent_id,salutation,first_name,
              middle_name,last_name,birth_date,
              gender,relation_type,relation,
              is_active,created_by, updated_by,student_id_default) 
    	 			values(in_ay_id, in_inst_id,l_parent_id,'MR.',l_c8,
              l_c9,l_c10,l_c11,
              'M','PARENT','FATHER',
              1,'SYSTEM', 'SYSTEM',l_student_id
              );
      update student_master set contact_id_1=l_parent_id where student_id=l_student_id  
        and inst_id=in_inst_id and ay_id=in_ay_id and is_active=1 AND is_deleted=0;

      ELSEIF(l_contact_id_1 > 0)then
        
              Update parent_master set  birth_date=l_c11, first_name=l_c8, middle_name=l_c9,last_name=l_c10
             WHERE inst_id = in_inst_id AND is_deleted= 0  AND parent_id=l_contact_id_1;
      end if;


    if((l_contact_id_2=0 OR l_contact_id_2 is null OR l_contact_id_2='') and l_c12!='')then

      set l_parent_id = fn_sequence_generator('PARENT_MASTER');

      insert into parent_master(ay_id, inst_id,parent_id,salutation,first_name,
              middle_name,last_name,birth_date,
              gender,relation_type,relation,
              is_active,created_by, updated_by,student_id_default) 
    	 			values(in_ay_id, in_inst_id,l_parent_id,'MRS.',l_c12,
              l_c13,l_c14,l_c15,
              'F','PARENT','MOTHER',
              1,'SYSTEM', 'SYSTEM',l_student_id
              );
      update student_master set contact_id_2=l_parent_id where student_id=l_student_id  
        and inst_id=in_inst_id and ay_id=in_ay_id and is_active=1 AND is_deleted=0;

      ELSEIF(l_contact_id_2 > 0)then
        
              Update parent_master set  birth_date=l_c15, first_name=l_c12, middle_name=l_c13,last_name=l_c14
             WHERE inst_id = in_inst_id AND is_deleted= 0  AND parent_id=l_contact_id_2;
      end if;

    end loop;
  commit;
  set out_err_code = l_err_code;
  set out_err_desc = l_err_desc;
END$$

DROP PROCEDURE IF EXISTS `update_student_data_using_CSV`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_student_data_using_CSV`(IN `in_inst_id` SMALLINT(5), IN `in_ay_id` VARCHAR(100), OUT `out_err_code` CHAR(5), OUT `out_err_desc` TEXT)
BEGIN
declare l_routine_name varchar(50) default 'update_student_data_using_CSV';
declare l_err_code char(5) default '00000';
declare l_err_desc text default '';        
declare l_routine_block varchar(15);
declare l_looping_done int default false;
declare l_student_id mediumint(5) default 0; 
declare l_delete_flag tinyint(1) default 0;   
  declare l_parent_id   mediumint(8);	   
   declare l_mother_name   varchar(50);
   declare l_middle_name    varchar(50);
   declare l_last_name   varchar(50);
   declare l_mobile_phone    varchar(50);
 

declare cur_csv_data cursor for 
   select rm.student_id, rm.mother_name ,rm.middle_name, rm.last_name, rm.contact_number from student_master sm  
 JOIN rough_work_temp_student_master rm  ON sm.student_id=rm.student_id 
   where sm.inst_id=in_inst_id and sm.ay_id=in_ay_id and  (sm.contact_id_2=0 or sm.contact_id_2 is NULL);


 declare continue handler for not found set l_looping_done = true;
declare exit handler for sqlexception
    begin
    rollback;
    
    get diagnostics condition 1
      l_err_code = RETURNED_SQLSTATE, l_err_desc = MESSAGE_TEXT;

   

    set out_err_code = l_err_code;
    set out_err_desc = l_err_desc;

       end;
open cur_csv_data;

  cursor_loop_new:loop
     fetch cur_csv_data into l_student_id,l_mother_name ,l_middle_name,l_last_name,l_mobile_phone;
         if l_looping_done then
             leave cursor_loop_new;
         end if;
      
  start transaction;
  
 #parent data insert
  
--     if(l_middle_name!='' AND l_last_name!='')then
-- 
--      set l_parent_id = fn_sequence_generator('PARENT_MASTER');
-- 
--           insert into  parent_master (ay_id,inst_id,parent_id, salutation,first_name,middle_name,last_name, 
-- 
--                    	gender,relation_type,relation,mobile_phone_1, is_active,created_by, updated_by) 	 						
-- 
--             values(in_ay_id,in_inst_id,l_parent_id,'MR.',l_middle_name,'', l_last_name,
--             
--             'M' , 'PARENT' ,'FATHER' ,l_mobile_phone, '1', ' ', ' ');
-- 
--      update student_master set contact_id_1=l_parent_id where student_id=l_student_id and is_deleted='0' ;
-- 
--   end if;


  #update data of parent





    #update student info

--     Update student_master set  is_rte=l_is_rte,enrollment_date=l_admission_date ,
--       birth_place= CONCAT(l_birth_place,', Taluka:',l_birth_taluka,', District:',l_birth_district,', State:',l_birth_state)
--       WHERE inst_id = in_inst_id AND is_deleted= 0  AND student_id=l_student_id;


     Update student_master set  aadhar_card_num=l_caste_income_cer_no
      WHERE inst_id = in_inst_id AND is_deleted= 0  AND student_id=l_student_id and is_active=1;


    # insert bank details of student

--     if(l_account_number!='' AND l_ifsc_code!='')then
-- 
--       select count(l_student_id) into l_is_back_data_exist from fin_bank_account_master where inst_id=in_inst_id
--         and party_id=l_student_id and party_type='STU';
-- 
--       if (l_is_back_data_exist = 0) then
--     set l_bank_id = fn_sequence_generator('ADD_BANK_ACCOUNT');
--             
--     
--            insert into fin_bank_account_master (ay_id, inst_id,bank_id,bank_name,
--                    bank_display_name,account_no,ifsc_code,status,
--                    party_id,party_type,is_active,created_by,updated_by) 
--                                         
--                                values(in_ay_id, in_inst_id,l_bank_id,l_bank_name,
--                                 l_bank_name,l_account_number,l_ifsc_code,'1',
--                                 l_student_id,'STU','1','','');
--           end if;
--  end if;

    #optional field data insert

--     select COUNT(user_id) INTO l_is_opt_data_exist
--               from optional_field_details where inst_id=in_inst_id and ay_id=in_ay_id
--             and user_id=l_student_id AND user_type='STU' and is_active=1 and is_deleted=0;
-- 
--     if(l_father_income!='')then
-- 
--         if(l_is_opt_data_exist=0)then
--         
--             INSERT INTO optional_field_details(inst_id, ay_id, field_set_id, country_id, state_id, board_id, 
--               user_type, user_id, f12, f29, is_active, is_deleted) 
--             values(in_inst_id, in_ay_id,'1-18-KSEEB','1','18','KSEEB','STU',l_student_id,l_caste_income_cer_no,
--       l_father_income ,'1','0');
--         
--         else
--         
--             #optional field data update
--         
--              Update optional_field_details set  f29=l_father_income, f12=l_caste_income_cer_no
--                      WHERE inst_id = in_inst_id AND is_deleted= 0  AND user_id=l_student_id AND user_type='STU';
--         
--         end if;
--     end if;


    #update parents (mother-father) data

--         select contact_id_1,contact_id_2 into l_contact_id_1 ,  l_contact_id_2
--           from student_master where inst_id=in_inst_id and ay_id=in_ay_id
--         and student_id=l_student_id and is_active=1 and is_deleted=0;
-- 
--       if(l_contact_id_1!=0)then
--         Update parent_master set  occupation=l_father_occupation,income_bracket='2' ,
--               edu_qual_degree= 'OTHER' , edu_qual_stream=l_father_qualification
--              WHERE inst_id = in_inst_id AND is_deleted= 0  AND parent_id=l_contact_id_1;
--       end if;
-- 
--     if(l_contact_id_2!=0)then
--         Update parent_master set  occupation=l_mother_occupation,
--           edu_qual_degree= 'OTHER' , edu_qual_stream=l_mother_qualification
--              WHERE inst_id = in_inst_id AND is_deleted= 0  AND parent_id=l_contact_id_2;
--       end if;
    end loop;
  commit;
  set out_err_code = l_err_code;
  set out_err_desc = l_err_desc;
END$$

DROP PROCEDURE IF EXISTS `vidyalkh_schema_integration`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `vidyalkh_schema_integration`(IN `in_dml_mode` CHAR(1), IN `in_ay_id` CHAR(7), IN `in_inst_id` SMALLINT(5), IN `in_exam_mst_id` MEDIUMINT(8), IN `in_standard_id` CHAR(3), IN `in_division_id` CHAR(1), IN `in_group_type` CHAR(3), IN `in_group_id` VARCHAR(10), IN `in_gradeset_mst_id` SMALLINT(5), IN `in_evaluation_type` VARCHAR(20), IN `in_exam_cat_id` VARCHAR(10), IN `in_exam_term` VARCHAR(10), IN `in_exam_name` VARCHAR(25), IN `in_exam_start_date` DATE, IN `in_exam_end_date` DATE, IN `in_exam_weightage` TINYINT(3), IN `in_is_system_def` TINYINT(1), IN `in_status` VARCHAR(25), IN `in_status_desc` VARCHAR(100), IN `in_is_active` TINYINT(1), IN `in_created_by` VARCHAR(20), OUT `out_err_code` CHAR(5), OUT `out_err_desc` TEXT)
    NO SQL
begin
	declare l_routine_name varchar(50) default 'sp_ins_exam_master'; 
	declare l_err_code char(5) default '00000'; 
	declare l_err_desc text default '';    	 
	declare l_routine_block varchar(15); 
	declare l_exam_mst_id MEDIUMINT(8); 
	declare l_exam_actual_start_date DATE; 
	declare l_exam_actual_end_date DATE; 
	/* Exception Handler Block */
	declare exit handler for sqlexception 
	begin 
		rollback; 
		get diagnostics condition 1 
        l_err_code = RETURNED_SQLSTATE, l_err_desc = MESSAGE_TEXT; 
		insert into db_error_log(ay_id, inst_id, routine_name, routine_block, err_code, err_desc, err_type, err_severity, created_by)  
		values (in_ay_id, in_inst_id, l_routine_name, l_routine_name, l_err_code, l_err_desc,'ERROR', 0, in_created_by); 
		set out_err_code = l_err_code ;
		set out_err_desc = l_err_desc; 
	end;
    start transaction; 
      case in_dml_mode  
        when 'I' then 
          begin 
           set l_routine_block = 'insert'; 
			set l_exam_mst_id = fn_sequence_generator('EXAM_MASTER');
			insert into exam_master (ay_id, inst_id, exam_mst_id, standard_id, division_id, group_type, group_id, 
				gradeset_mst_id, evaluation_type, exam_cat_id, exam_term, exam_name, exam_start_date, exam_end_date, exam_weightage, 
				is_system_def, status, status_desc, created_by, is_active)  
			values(in_ay_id, in_inst_id, l_exam_mst_id, in_standard_id, in_division_id, in_group_type, in_group_id,
				in_gradeset_mst_id, in_evaluation_type, in_exam_cat_id, in_exam_term, in_exam_name, in_exam_start_date, in_exam_end_date, in_exam_weightage,
				in_is_system_def, in_status, in_status_desc,in_created_by, in_is_active); 
          end;    
		/* Update */	
  	    when 'S' then
         set l_routine_block = 'statusChange'; 
          begin
			SET @selectInsertExamMaster = CONCAT_WS("" , "insert into exam_master( 
			ay_id, inst_id, exam_mst_id,  standard_id, division_id, group_type, group_id, gradeset_mst_id, evaluation_type, exam_cat_id, exam_term, exam_name, exam_start_date, exam_end_date, exam_weightage, is_system_def, 
			status, 
			status_desc, 
			valid_from, valid_to, created_by, 
			updated_by, 
			is_active, is_deleted) 
			SELECT 	ay_id, inst_id, exam_mst_id, standard_id, division_id, group_type, group_id, gradeset_mst_id, evaluation_type, exam_cat_id, exam_term, exam_name, exam_start_date, exam_end_date, exam_weightage, is_system_def,", in_status , " as status,", in_status_desc , " as status_desc, valid_from, valid_to,created_by,", in_created_by , " as updated_by, '1' as is_active,'5' as is_deleted from exam_master WHERE 
			exam_mst_id = ", in_exam_mst_id ," and 
			inst_id = '", in_inst_id ,"' and 
			ay_id= '", in_ay_id ,"' and 
			is_active = 1 and is_deleted = 0" );
			/*INSERT INTO proc_error_logs VALUES('1','selectInsertExamMaster',@selectInsertExamMaster);*/ 		 
			PREPARE stmt FROM @selectInsertExamMaster;  
			EXECUTE stmt;  
			update exam_master set valid_to = NOW(), updated_by = in_created_by,  
				updated_on = NOW(), is_deleted = 1 
            where exam_mst_id = in_exam_mst_id 
				and is_deleted = 0  
            	and inst_id = in_inst_id; 
/* If Schedule Published Status (50), Then change the exam dates */	
			insert into proc_error_logs values ('in_status','-',in_status );	
			if (in_status= '50') then        
			 SELECT MIN(exam_start_time), MAX(exam_end_time) INTO l_exam_actual_start_date,  l_exam_actual_end_date 
				FROM exam_details 
				WHERE exam_mst_id = in_exam_mst_id 
				AND is_deleted = 0  
            	AND inst_id = in_inst_id; 
              insert into proc_error_logs values ('l_exam_actual_start_date','-',l_exam_actual_start_date );	
              insert into proc_error_logs values ('l_exam_actual_end_date','-',l_exam_actual_end_date );	
				update exam_master set valid_to = NOW(),exam_start_date=l_exam_actual_start_date, exam_end_date=l_exam_actual_end_date, updated_by = in_created_by,  
					updated_on = NOW(), is_deleted = 0
				where exam_mst_id = in_exam_mst_id 
					and is_deleted = 5  
					and inst_id = in_inst_id; 
			else
				update exam_master set valid_to = NOW(), updated_by = in_created_by,  
					updated_on = NOW(), is_deleted = 0
				where exam_mst_id = in_exam_mst_id 
					and is_deleted = 5  
					and inst_id = in_inst_id; 
			end if; 
          end;     
		/* Status Change */	
		when 'U' then
         set l_routine_block = 'update'; 
          begin
          	update exam_master set valid_to = NOW(), updated_by = in_created_by,  
				updated_on = NOW(), is_deleted = 1 
            where exam_mst_id = in_exam_mst_id 
				and is_deleted = 0  
            	and inst_id = in_inst_id; 
			 insert into exam_master (ay_id, inst_id, exam_mst_id, standard_id, division_id, group_type, group_id, 
				gradeset_mst_id, evaluation_type, exam_cat_id, exam_term, exam_name, exam_start_date, exam_end_date, exam_weightage, 
				is_system_def, status, status_desc, created_by, is_active)  
			values(in_ay_id, in_inst_id, in_exam_mst_id, in_standard_id, in_division_id, in_group_type, in_group_id,
				in_gradeset_mst_id, in_evaluation_type, in_exam_cat_id, in_exam_term, in_exam_name, in_exam_start_date, in_exam_end_date, in_exam_weightage,
				in_is_system_def, in_status, in_status_desc, in_created_by, in_is_active); 
          end;     
      end case;
    commit;
    set out_err_code = l_err_code; 
    set out_err_desc = l_err_desc; 
END$$

--
-- Functions
--
DROP FUNCTION IF EXISTS `FIRST_DAY`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `FIRST_DAY`(`dt` DATETIME) RETURNS date
    NO SQL
BEGIN
    RETURN DATE_ADD(DATE_ADD(LAST_DAY(dt),INTERVAL 1 DAY),INTERVAL - 1 MONTH);
END$$

DROP FUNCTION IF EXISTS `fn_custom_num_generator`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_custom_num_generator`(`in_ay_id` CHAR(7), `in_inst_id` SMALLINT(5), `in_entity_name` VARCHAR(25), `in_operation_code` VARCHAR(25), `in_initiator_id` VARCHAR(25)) RETURNS varchar(50) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
  declare l_routine_name varchar(50) default 'fn_custom_num_generator';
  declare l_routine_block varchar(25);
  declare l_err_code char(5) default '00000';
  declare l_err_desc text default '';    	
  declare l_curs_1_loop_done int default false;
  declare l_curs_2_loop_done int default false;
  declare l_param_count tinyint(2); 
  declare l_separator_char char(1);
  declare l_id_prefix varchar(50); 
  declare l_id_seed int(10); 
  declare l_separator_idx int default 1;
  declare l_token varchar(1000); 
  declare l_inst_abbr varchar(100);  
  declare l_inst_br_abbr varchar(100);  
  declare l_demand_num varchar(1000) default ''; 
  declare cur_seq_gen_master cursor for 
  select param_count, seperator_char, id_prefix, id_seed 
  from sequence_generator_master 
  where id_category = in_entity_name and ay_id = in_ay_id and inst_id = in_inst_id
  and is_deleted = 0;
  declare cur_inst_seq_params cursor for 
  select inst_abbr, inst_br_abbr 
  from institution_sequence_params
  where ay_id = in_ay_id and inst_id = in_inst_id and is_deleted = 0;
  declare continue handler for not found set l_curs_1_loop_done = true, 
    l_curs_2_loop_done = true;
  declare exit handler for sqlexception
	  begin     
      get diagnostics condition 1
        l_err_code = returned_sqlstate, l_err_desc = message_text;
      insert into db_error_log(ay_id, inst_id, routine_name, routine_block, err_code, 
        err_desc, err_type, err_severity, created_by) 
      values 
        (in_ay_id, in_inst_id, l_routine_name, l_routine_block, l_err_code, l_err_desc, 
        'ERROR', 0, in_created_by);
      return 0;
		end;
    open cur_seq_gen_master;
    open cur_inst_seq_params;
    cursor_loop_1: loop
      fetch cur_inst_seq_params into l_inst_abbr, l_inst_br_abbr;
      #insert into proc_error_logs (routine_name, label_name, sql_log)
      #values(l_routine_name, 'Inside Cursor 1', l_inst_abbr);
      set l_curs_1_loop_done = true;
      if l_curs_1_loop_done then
        leave cursor_loop_1;
      end if;
    end loop;
    close cur_inst_seq_params;
    cursor_loop_2: loop
      fetch cur_seq_gen_master into l_param_count, l_separator_char, l_id_prefix, 
        l_id_seed;
      #insert into proc_error_logs (routine_name, label_name, sql_log)
      #values(l_routine_name, 'Inside Cursor 2', l_param_count);
      if l_curs_2_loop_done then
        leave cursor_loop_2;
      end if;
      while (l_param_count > 0) do
        set l_routine_block = 'inside while loop';
        set l_separator_idx = instr(l_id_prefix, l_separator_char);
        set l_token = substring(l_id_prefix, 1, l_separator_idx - 1);
        set l_id_prefix = substring(l_id_prefix, l_separator_idx + 1, 
          length(l_id_prefix));
        #insert into proc_error_logs (routine_name, label_name, sql_log)
        #values(l_routine_name, l_routine_block, l_token);
        case l_token
          when '{INST_ABBR}' THEN
            begin
              set l_demand_num = concat(l_demand_num, l_separator_char, l_inst_abbr);
            end;
          when '{INST_BR}' THEN
            begin            
              set l_demand_num = concat(l_demand_num, l_separator_char, l_inst_br_abbr);
            end;
          when '{AY_ID}' THEN
            begin            
              set l_demand_num = concat(l_demand_num, l_separator_char, in_ay_id);
            end;
          when '{OP_CODE}' THEN
            begin            
              set l_demand_num = concat(l_demand_num, l_separator_char, 
                in_operation_code);
            end;
          when '{DD-MON}' THEN
            begin            
              set l_demand_num = concat(l_demand_num, l_separator_char, day(current_date()));
            end;
        end case;
        set l_param_count = l_param_count - 1;
        set l_separator_idx = 0;
      end while;
    end loop;
    close cur_seq_gen_master;
    begin
      select id_seed into l_id_seed from sequence_generator_master 
      where id_category = in_entity_name and ay_id = in_ay_id and inst_id = in_inst_id
      for update;
      set l_id_seed = l_id_seed + 1;
      update sequence_generator_master set id_seed = l_id_seed, 
        updated_by = in_initiator_id, updated_on = now()
      where id_category = in_entity_name and ay_id = in_ay_id and inst_id = in_inst_id
        and is_deleted = 0;
      set l_demand_num = concat(l_demand_num, l_separator_char, l_id_seed);
      set l_demand_num = substring(l_demand_num, 2, length(l_demand_num));
    end;  
    #insert into proc_error_logs (routine_name, label_name, sql_log)
    #values(l_routine_name, 'Value : ', l_demand_num);
RETURN l_demand_num;
end$$

DROP FUNCTION IF EXISTS `fn_custom_num_generator_1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_custom_num_generator_1`(`in_ay_id` CHAR(7), `in_inst_id` SMALLINT(5), `in_entity_name` VARCHAR(25), `in_operation_code` VARCHAR(25), `in_initiator_id` VARCHAR(25)) RETURNS varchar(50) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
  declare l_routine_name varchar(50) default 'fn_custom_num_generator';
  declare l_routine_block varchar(25);
  declare l_err_code char(5) default '00000';
  declare l_err_desc text default '';    	
  declare l_curs_1_loop_done int default false;
  declare l_curs_2_loop_done int default false;
  declare l_param_count tinyint(2); 
  declare l_separator_char char(1);
  declare l_id_prefix varchar(50); 
  declare l_id_seed int(10); 
  declare l_separator_idx int default 1;
  declare l_token varchar(1000); 
  declare l_inst_abbr varchar(100);  
  declare l_inst_br_abbr varchar(100);  
  declare l_demand_num varchar(1000) default ''; 
  declare cur_seq_gen_master cursor for 
  select param_count, seperator_char, id_prefix, id_seed 
  from sequence_generator_master 
  where id_category = in_entity_name and ay_id = in_ay_id and inst_id = in_inst_id
  and is_deleted = 0;
  declare cur_inst_seq_params cursor for 
  select inst_abbr, inst_br_abbr 
  from institution_sequence_params
  where ay_id = in_ay_id and inst_id = in_inst_id and is_deleted = 0;
  declare continue handler for not found set l_curs_1_loop_done = true, 
    l_curs_2_loop_done = true;
  declare exit handler for sqlexception
	  begin     
      get diagnostics condition 1
        l_err_code = returned_sqlstate, l_err_desc = message_text;
      insert into db_error_log(ay_id, inst_id, routine_name, routine_block, err_code, 
        err_desc, err_type, err_severity, created_by) 
      values 
        (in_ay_id, in_inst_id, l_routine_name, l_routine_block, l_err_code, l_err_desc, 
        'ERROR', 0, in_created_by);
      return 0;
		end;
    open cur_seq_gen_master;
    open cur_inst_seq_params;
    cursor_loop_1: loop
      fetch cur_inst_seq_params into l_inst_abbr, l_inst_br_abbr;
      #insert into proc_error_logs (routine_name, label_name, sql_log)
      #values(l_routine_name, 'Inside Cursor 1', l_inst_abbr);
      set l_curs_1_loop_done = true;
      if l_curs_1_loop_done then
        leave cursor_loop_1;
      end if;
    end loop;
    close cur_inst_seq_params;
    cursor_loop_2: loop
      fetch cur_seq_gen_master into l_param_count, l_separator_char, l_id_prefix, 
        l_id_seed;
      #insert into proc_error_logs (routine_name, label_name, sql_log)
      #values(l_routine_name, 'Inside Cursor 2', l_param_count);
      if l_curs_2_loop_done then
        leave cursor_loop_2;
      end if;
      while (l_param_count > 0) do
        set l_routine_block = 'inside while loop';
        set l_separator_idx = instr(l_id_prefix, l_separator_char);
        set l_token = substring(l_id_prefix, 1, l_separator_idx - 1);
        set l_id_prefix = substring(l_id_prefix, l_separator_idx + 1, 
          length(l_id_prefix));
        #insert into proc_error_logs (routine_name, label_name, sql_log)
        #values(l_routine_name, l_routine_block, l_token);
        case l_token
          when '{INST_ABBR}' THEN
            begin
              set l_demand_num = concat(l_demand_num, l_separator_char, l_inst_abbr);
            end;
          when '{INST_BR}' THEN
            begin            
              set l_demand_num = concat(l_demand_num, l_separator_char, l_inst_br_abbr);
            end;
          when '{AY_ID}' THEN
            begin            
              set l_demand_num = concat(l_demand_num, l_separator_char, in_ay_id);
            end;
          when '{OP_CODE}' THEN
            begin            
              set l_demand_num = concat(l_demand_num, l_separator_char, 
                in_operation_code);
            end;
          when '{DD-MON}' THEN
            begin            
              set l_demand_num = concat(l_demand_num, l_separator_char, day(current_date()));
            end;
        end case;
        set l_param_count = l_param_count - 1;
        set l_separator_idx = 0;
      end while;
    end loop;
    close cur_seq_gen_master;
    begin
      select id_seed into l_id_seed from sequence_generator_master 
      where id_category = in_entity_name and ay_id = in_ay_id and inst_id = in_inst_id
      for update;
      set l_id_seed = l_id_seed + 1;
      update sequence_generator_master set id_seed = l_id_seed, 
        updated_by = in_initiator_id, updated_on = now()
      where id_category = in_entity_name and ay_id = in_ay_id and inst_id = in_inst_id
        and is_deleted = 0;
      set l_demand_num = concat(l_demand_num, l_separator_char, l_id_seed);
      set l_demand_num = substring(l_demand_num, 2, length(l_demand_num));
    end;  
    #insert into proc_error_logs (routine_name, label_name, sql_log)
    #values(l_routine_name, 'Value : ', l_demand_num);
RETURN l_demand_num;
end$$

DROP FUNCTION IF EXISTS `fn_sequence_generator`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_sequence_generator`(`in_tbl_name` VARCHAR(25)) RETURNS mediumint(8)
BEGIN
  declare l_id_num mediumint(8);
  declare l_tbl_name varchar(25);
  set l_tbl_name = upper(trim(in_tbl_name));
  if (isnull(in_tbl_name)) || (length(l_tbl_name) = 0) then
    set l_id_num = 0;
  end if;
  case l_tbl_name 
    when 'HOLIDAY_MASTER' then
      begin
        insert into holiday_mst_seq_generator(id_ts) values (NOW());   
      end;
    when 'EXAM_MASTER' then
      begin
        insert into exam_mst_seq_generator(id_ts) values (NOW());   
      end;
    when 'EXAM_DETAILS' then
      begin
        insert into exam_dtl_seq_generator(id_ts) values (NOW());   
      end;
    when 'EXAM_GROUP_MASTER' then
      begin
        insert into exam_grp_mst_seq_generator(id_ts) values (NOW());   
      end;
    when 'EXAM_GROUP_DETAILS' then
      begin
        insert into exam_grp_dtls_seq_generator(id_ts) values (NOW());   
      end ;
    when 'DEMAND_MASTER' then
      begin
        insert into fee_demand_mst_seq_generator(id_ts) values (NOW());   
      end;
    when 'QUICKPAY_MASTER' then
      begin
        insert into fee_quickpay_mst_seq_generator(id_ts) values (NOW());   
      end; 
    when 'QUICKPAY_DETAILS' then
      begin
        insert into fee_quickpay_dtl_seq_generator(id_ts) values (NOW());   
      end;
    when 'FEE_DEMAND_DETAILS' then
      begin
        insert into fee_demand_dtl_seq_generator(id_ts) values (NOW());   
      end; 
    when 'DEMAND_GROUP' then 
      begin 
        insert into fee_demand_grp_seq_generator(id_ts) values (NOW());  
      end;
   when 'FEE_STRUCTURE_MASTER' then
      begin
        insert into fee_structure_mst_seq_generator(id_ts) values (NOW()); 
      end;
   when 'FEE_STRUCTURE_DETAILS' then
      begin
        insert into fee_structure_dtl_seq_generator(id_ts) values (NOW());           
      end;
  when 'FEE_TYPE_MASTER' then
      begin
        insert into fee_type_master_seq_generator(id_ts) values (NOW()); 
      end;
	      when 'COLLECTION_MASTER' then 
      begin 
        insert into collection_mst_seq_generator(id_ts) values (NOW());    
      end;
    when 'COLLECTION_DETAILS' then 
      begin 
        insert into collection_dtls_seq_generator(id_ts) values (NOW());    
      end; 
  when 'CCE_GRADESET_MASTER' then
      begin
        insert into cce_gradeset_mst_seq_generator(id_ts) values (NOW());   
      end;
  when 'CCE_GRADE_MASTER' then
      begin
        insert into cce_grade_mst_seq_generator(id_ts) values (NOW());   
      end;  
  when 'HOUSE_MASTER' then
      begin
        insert into house_mst_seq_generator(id_ts) values (NOW());   
      end;  
 when 'INSTITUTION_MASTER' then
      begin
        insert into inst_mst_seq_generator(id_ts) values (NOW());   
      end;
 when 'STUDENT_MASTER' then
      begin
        insert into stud_mst_seq_generator(id_ts) values (NOW());   
      end;
 when 'USER_MASTER' then
      begin
        insert into `user_mst_seq_generator`(id_ts) values (NOW());   
      end;
 when 'PARENT_MASTER' then
      begin
        insert into `parent_mst_seq_generator`(id_ts) values (NOW());   
      end; 
 when 'EMPLOYEE_MASTER' then
      begin
        insert into emp_mst_seq_generator(id_ts) values (NOW());   
      end;
 when 'FILE_UPLOAD_MASTER' then
      begin
        insert into file_upload_seq_generator(id_ts) values (NOW());   
      end;  
   when 'STANDARD_MASTER' then
        begin
          insert into standard_mst_seq_generator(id_ts) values (NOW());   
        end;
      when 'SUBJECT_SYLLABUS_MAP' then
        begin
          insert into sub_syllabus_map_seq_generator(id_ts) values (NOW());   
        end;
        when 'STANDARD_DIVISION_MAP' then
        begin
          insert into standard_division_map_seq_generator(id_ts) values (NOW());   
        end;
         when 'SUBJECT_MASTER' then
        begin
          insert into subject_master_seq_generator(id_ts) values (NOW());   
        end;
         when 'SUBJECT_STANDARD_MAP' then
        begin
          insert into subject_standard_map_seq_generator(id_ts) values (NOW());   
        end;
         when 'SUBJECT_BOOK_MAP' then
        begin
          insert into subject_book_map_seq_generator(id_ts) values (NOW());   
        end;
          when 'SUBJECT_STRUCTURE_MAP' then
        begin
          insert into subject_structure_map_seq_generator(id_ts) values (NOW());   
        end;
         when 'BOOK_MASTER' then
        begin
          insert into book_mst_seq_generator(id_ts) values (NOW());   
        end;
        when 'BOOK_CHAPTER_MAP' then
        begin
          insert into book_chapter_map_seq_generator(id_ts) values (NOW());   
        end;
        when 'EMP_SUBJECT_MAP' then
        begin
          insert into emp_subject_map_seq_generator(id_ts) values (NOW());   
        end;
        when 'STUDENT_ATTENDANCE_MASTER' then
       begin
        insert into stud_attendance_mst_seq_generator(id_ts) values (NOW());   
      end;
	    when 'STUDENT_ATTENDANCE_DETAIL' then
      begin
        insert into stud_attendance_dtl_seq_generator(id_ts) values (NOW());   
      end;
	when 'STUDENT_HOMEWORK_MASTER' then
      begin
        insert into student_homework_mst_seq_generator(id_ts) values (NOW());   
      end;
	when 'STUDENT_HOMEWORK_DETAILS' then
      begin
        insert into student_homework_dtl_seq_generator(id_ts) values (NOW());   
      end;
	when 'STUDENT_ASSIGNMENT_MASTER' then
       begin
        insert into stud_assignment_mst_seq_generator(id_ts) values (NOW());   
      end;
   when 'STUDENT_ASSIGNMENT_DETAIL' then
      begin
        insert into stud_assignment_dtl_seq_generator(id_ts) values (NOW());   
      end;
	when 'NOTIFICATION_MASTER' then
		begin
		insert into notification_mst_seq_generator(id_ts) values (NOW());   
		end;
  when 'DEPARTMENT_MASTER' then
      begin
        insert into dept_mst_seq_generator(id_ts) values (NOW());   
      end;
  when 'STUDENT_LEAVE_DETAILS' then
      begin
        insert into student_leave_dtl_seq_generator(id_ts) values (NOW());   
      end;
   when 'EMP_LEAVE_DETAILS' then
      begin
        insert into emp_leave_dtl_seq_generator(id_ts) values (NOW());   
      end;
  when 'EVENT_CALENDAR_MASTER' then
      begin
        insert into event_calendar_seq_generator(id_ts) values (NOW());   
      end;
 when 'TIMETABLE_PERIOD_MAP' then
      begin
        insert into timetable_period_map_seq_generator(id_ts) values (NOW());   
      end;
when 'EMP_ATTENDANCE_MASTER' then
      begin
        insert into emp_attendance_master_seq_generator(id_ts) values (NOW());   
      end;
  when 'EMP_ATTENDANCE_DETAIL' then
      begin
        insert into emp_attendance_details_seq_generator(id_ts) values (NOW());   
      end;
 when 'CHANNEL_PARTNER_DETAILS' then
      begin
        insert into channel_part_details_seq_generator(id_ts) values (NOW());   
      end;
when 'GALLARY_ALBUM' then
      begin
        insert into gallery_album_seq_generator(id_ts) values (NOW());  
      end;
   when 'GALLARY_PHOTO' then
      begin
        insert into gallery_photos_seq_generator(id_ts) values (NOW());  
      end;
  when 'INFORMATION_POST' then
     begin
       insert into information_post_seq_generator(id_ts) values (NOW());  
     end;
when 'ADMISSION_STAGE_MASTER' then
       begin
          insert into admisssion_stage_mst_seq_generator(id_ts) values (NOW());
        end;
when 'INTERVIEW_SCHEDULE' then
       begin
          insert into interview_schedule_mst_seq_generator(id_ts) values (NOW());
        end;
when 'TIMETABLE_MASTER' then
       begin
          insert into timetable_mst_seq_generator(id_ts) values (NOW());
        end;
when 'GRADESET_MASTER' then
       begin
          insert into gradeset_mst_seq_generator(id_ts) values (NOW());
        end;
       when 'GRADE_DETAILS' then
       begin
          insert into gradedetails_mst_seq_generator(id_ts) values (NOW());
        end;
when 'MEAL_CALENDER_MASTER' then
       begin
          insert into meal_calendar_mst_seq_generator(id_ts) values (NOW());
        end;
      when 'MEAL_CALENDAR_DETAIL' then
       begin
          insert into meal_calendar_dtl_seq_generator(id_ts) values (NOW());
        end;
         when 'NEWS_POST' then
     begin
       insert into news_post_seq_generator(id_ts) values (NOW());  
     end;
  when 'ORGANIZATION_MASTER' then
       begin
          insert into organization_mst_seq_generator(id_ts) values (NOW());
        end;
        
         when 'TIMESLOT_DETAILS' then
       begin
          insert into timeslot_details_seq_generator(id_ts) values (NOW());
        end;
        
      when 'VIDEO_POST' then
      begin
         insert into videopost_mst_seq_generator(id_ts) values (NOW());
       end;

	when 'SUBJECT_PACKAGE_MASTER' then
      begin
         insert into sub_package_mst_seq_generator(id_ts) values (NOW());
       end;

      when 'SUBJECT_PACKAGE_DETAILS' then
      begin
         insert into sub_package_dtl_seq_generator(id_ts) values (NOW());
       end;
       
        when 'ADD_SMS_CREDIT' then
       begin
          insert into add_sms_credit_mst_seq_generator(id_ts) values (NOW());
        end;
        when 'ADD_BOOK' then
       begin
          insert into add_new_library_book_mst_seq_generator(id_ts) values (NOW());
        end;
      when 'FEEDBACK_MASTER' then
       begin
          insert into feedback_dtl_seq_generator(id_ts) values (NOW());
        end;
 
     when 'COMPLAINT_REPLY_DETAILS' then
       begin
          insert into reply_dtl_seq_generator(id_ts) values (NOW());
        end;
        
        when 'USER_MANAGEMENT' then
       begin
          insert into user_management_seq_generator(id_ts) values (NOW());
        end;
         when 'ORDER_MASTER' then
      begin
         insert into order_mst_seq_generator(id_ts) values (NOW());
       end;
    when 'ORDER_DETAILS' then
      begin
         insert into order_dtl_seq_generator(id_ts) values (NOW());
       end;
     when 'KITCHEN_ITEM_MASTER' then
      begin
         insert into kitchen_item_mst_seq_generator(id_ts) values 			(NOW());
       end;
     when 'GROUP_MASTER' then
      begin
         insert into group_mst_seq_generator(id_ts) values (NOW());
       end;
    when 'GROUP_DETAILS' then
      begin
         insert into group_dtl_seq_generator(id_ts) values (NOW());
       end;
        when 'SETUP_STUD_MASTER' then
      begin
         insert into setup_stud_master_seq_generator(id_ts) values              (NOW());
       end;
      when 'ADD_BANK_ACCOUNT' then
       begin
          insert into add_bank_account_seq_generator(id_ts) values              (NOW());
        end;
      when 'EXAM_META' then
        begin
          insert into exam_result_meta_seq_generator(id_ts) values              (NOW());
        end;
      when 'ADD_ACCOUNT_HEAD_MASTER' then
       begin
        insert into add_account_head_seq_generator(id_ts) values (NOW());
       end;
       
        when 'QMS_TASK_DATA_MASTER' then
       begin
          insert into qms_task_data_seq_generator(id_ts) values (NOW());
        end;
          when 'ADD_PARTY_MASTER' then
       begin
          insert into add_party_master_seq_generator(id_ts) values (NOW());
        end;
      when 'FIN_INVOICE_MASTER' then
       begin
          insert into fin_invoice_mst_seq_generator(id_ts) values (NOW());
        end;
      when 'FIN_INVOICE_DETAILS' then
       begin
          insert into fin_invoice_details_seq_generator(id_ts) values (NOW());
        end;
      when 'FIN_EMP_PAYROLL_MASTER' then
       begin
          insert into fin_emp_payroll_mst_seq_generator(id_ts) values (NOW());
        end;
      when 'FIN_EMP_PAYROLL_DETAILS' then
       begin
          insert into fin_emp_payroll_details_seq_generator(id_ts) values (NOW());
        end;
    when 'FIN_EMP_SALARY_MASTER' then
       begin
          insert into fin_emp_salary_mst_seq_generator(id_ts) values (NOW());
        end;
      when 'FIN_EMP_SALARY_DETAILS' then
       begin
          insert into fin_emp_salary_details_seq_generator(id_ts) values (NOW());
        end;
         when 'FIN_DEBIT_DEMAND_MASTER' then
       begin
        insert into fin_debit_demand_master_seq_generator(id_ts) values (NOW());
       end;
     when 'FIN_DEBIT_DEMAND_GROUP' then
       begin
        insert into fin_debit_demand_group_seq_generator(id_ts) values (NOW());
       end;
      when 'FIN_DEBIT_MASTER' then
       begin
        insert into fin_debit_master_seq_generator(id_ts) values (NOW());
       end;
     when 'FIN_DEBIT_DETAILS' then
       begin
        insert into fin_debit_details_seq_generator(id_ts) values (NOW());
       end;
       
       when 'TRANS_VEHICLE_MASTER' then
       begin
          insert into trans_vehicle_master_seq_generator(id_ts) values (NOW());
        end;

       when 'TRANS_STOP_MASTER' then
       begin
          insert into trans_stop_master_seq_generator(id_ts) values (NOW());
        end;

      when 'TRANS_GPS_DEVICE_MASTER' then
       begin
          insert into trans_gps_device_master_seq_generator(id_ts) values (NOW());
        end;

      when 'TRANS_VEHICLE_DRIVER_MAP' then
       begin
          insert into trans_vehicle_driver_map_seq_generator(id_ts) values (NOW());
        end;

      when 'TRANS_ROUTE_MASTER' then
       begin
          insert into trans_route_master_seq_generator(id_ts) values (NOW());
        end;

    when 'TRANS_ROUTE_STOPS_MAP' then
       begin
          insert into trans_route_stops_master_seq_generator(id_ts) values (NOW());
        end;

      when 'TRANS_STUDENT_ROUTE_MAP' then
       begin
          insert into trans_stud_route_stops_master_seq_generator(id_ts) values (NOW());
        end;
        
        when 'QAMS_INCIDENT_DATA_MASTER' then
       begin
          insert into qams_incident_mst_seq_generator(id_ts) values (NOW());
        end;
        
         when 'INVENTORY_ORDER_MASTER' then
      begin
         insert into inventory_order_mst_seq_generator(id_ts) values (NOW());
       end;
    when 'INVENTORY_ORDER_DETAILS' then
      begin
         insert into inventory_order_dtl_seq_generator(id_ts) values (NOW());
       end;
     when 'INVENTORY_ITEM_MASTER' then
      begin
         insert into inventory_item_mst_seq_generator(id_ts) values (NOW());
       end;
	    when 'INVENTORY_ISSUE_MASTER' then
       begin
          insert into inventory_issue_item_master_seq_generator(id_ts) values (NOW());
        end;
       when 'INVENTORY_ISSUE_DETAILS' then
       begin
          insert into inventory_issue_item_details_seq_generator(id_ts) values (NOW());
        end;
 when 'ADD_LIB_ACCOUNT' then
       begin
          insert into library_account_seq_generator(id_ts) values (NOW());
        end; 
        
         when 'FORM_FIELD_DATA_DETAILS' then
    begin
       insert into form_data_details_seq_generator(id_ts) values (NOW());
     end;
         when 'EMP_LEAVE_PACKAGE_MASTER' then
    begin
       insert into emp_leave_pack_mst_seq_generator(id_ts) values (NOW());
     end;
    when 'EMP_LEAVE_PACKAGE_DETAILS' then
    begin
       insert into  emp_leave_pack_dtl_seq_generator(id_ts) values (NOW());
     end;       
 
     when 'BANK_BOOK' then
       begin
          insert into bank_book_mst_seq_generator(id_ts) values (NOW());
        end;
        
        when 'QAMS_OTHER_USERS_MASTER' then
    begin
       insert into qams_other_user_seq_generator(id_ts) values (NOW());
     end;
      when 'TIMETABLE_META' then
       begin
          insert into timetable_meta_seq_generator(id_ts) values (NOW());
        end;
        when 'QAMS_ASSET_MASTER' then
       begin
          insert into qams_asset_master_seq_generator(id_ts) values (NOW());
        end;
        when 'FIN_ARREARS_MASTER' then
    begin
        insert into  fin_arrear_mst_seq_generator(id_ts) values (NOW());
    end;   
      when 'FIN_ARREARS_DETAILS' then
    begin
        insert into  fin_arrear_dtl_seq_generator(id_ts) values (NOW());
    end;  
     when 'INVENTORY_ITEM_CATEGORY' then
    begin
        insert into  inventory_item_category_seq_generator(id_ts) values    (NOW());
    end;  
    
    when 'CONTRACT_AND_RENEWAL' then
    begin
        insert into  contract_and_renew_seq_generator(id_ts) values (NOW());
    end;
    
     when 'VIRTUAL_CLS_POST_MASTER' then
    begin
        insert into  virtual_class_post_mst_seq_generator(id_ts) values (NOW());
    end;  
    
  end case;
  set l_id_num = last_insert_id();
RETURN l_id_num;
END$$

DROP FUNCTION IF EXISTS `SPLIT_STR`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `SPLIT_STR`(`x` VARCHAR(255), `delim` VARCHAR(12), `pos` INT) RETURNS varchar(255) CHARSET utf8 COLLATE utf8_unicode_ci
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
    LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
    delim, '')$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `account_head_master`
--

CREATE TABLE IF NOT EXISTS `account_head_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `acct_head_id` smallint(5) NOT NULL,
  `parent_id` smallint(5) NOT NULL,
  `acct_head_level` tinyint(1) NOT NULL,
  `acct_head_category` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `acct_head_type` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `acct_head_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `acct_head_display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `acct_head_desc` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `acct_head_abbr` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `is_system_def` tinyint(1) NOT NULL,
  `is_discountable` tinyint(1) NOT NULL,
  `is_waivable` tinyint(1) NOT NULL,
  `is_taxable` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 19:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=682;

-- --------------------------------------------------------

--
-- Table structure for table `acct_head_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `acct_head_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `add_account_head_seq_generator`
--

CREATE TABLE IF NOT EXISTS `add_account_head_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `add_bank_account_seq_generator`
--

CREATE TABLE IF NOT EXISTS `add_bank_account_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `add_new_library_book_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `add_new_library_book_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `add_party_master_seq_generator`
--

CREATE TABLE IF NOT EXISTS `add_party_master_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `add_sms_credit_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `add_sms_credit_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `admission_fees_config`
--

CREATE TABLE IF NOT EXISTS `admission_fees_config` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `standard_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `stage_id` mediumint(8) NOT NULL,
  `admission_fee_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `fees_id` mediumint(8) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `admission_stage_master`
--

CREATE TABLE IF NOT EXISTS `admission_stage_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `stage_id` mediumint(8) NOT NULL,
  `stage_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `stage_seq` smallint(5) NOT NULL,
  `stage_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `stage_abbr` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `stage_icon` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `stage_color` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 21:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `admission_stage_status_master`
--

CREATE TABLE IF NOT EXISTS `admission_stage_status_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `stage_id` mediumint(8) NOT NULL,
  `status_id` mediumint(8) NOT NULL,
  `status_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `status_type` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `status_abbr` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `status_pos` tinyint(3) NOT NULL,
  `status_seq` tinyint(3) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 21:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `admisssion_stage_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `admisssion_stage_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `alert_master`
--

CREATE TABLE IF NOT EXISTS `alert_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `alert_id` mediumint(8) NOT NULL,
  `alert_type` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `alert_desc` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_push_alert` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `alert_subject` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `alert_priority` int(1) DEFAULT '2',
  `generator_class_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `properties_str` varchar(2000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_enabled` char(1) COLLATE utf8_unicode_ci DEFAULT 'y',
  `from_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'vidyalekha',
  `system_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `alert_freq` int(18) DEFAULT '-2',
  `alert_cycle_count` int(18) DEFAULT '1',
  `param_adtnl_type` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL,
  `token_1` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `token_2` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `token_3` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `token_4` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `create_date` date DEFAULT NULL,
  `next_run_date` date DEFAULT NULL,
  `last_run_date` date DEFAULT NULL,
  `group_code` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_subscribed` tinyint(1) DEFAULT '0',
  `is_on_screen` tinyint(1) DEFAULT '0',
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2038-01-01 02:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `alert_msg_queue`
--

CREATE TABLE IF NOT EXISTS `alert_msg_queue` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `alert_id` mediumint(8) NOT NULL,
  `address` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `from_address` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `cc_address` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `subject` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `alert_msg` blob NOT NULL,
  `msg_priority` tinyint(1) NOT NULL,
  `created_on` date NOT NULL,
  `expiries_on` date NOT NULL,
  `sent_on` date NOT NULL,
  `max_retry_nbr` tinyint(2) NOT NULL DEFAULT '5',
  `retry_count` tinyint(2) NOT NULL DEFAULT '0',
  `retry_interval` smallint(5) NOT NULL DEFAULT '0',
  `retried_on` date NOT NULL,
  `msg_status` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'I',
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2038-01-01 02:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `appl_data_master`
--

CREATE TABLE IF NOT EXISTS `appl_data_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `data_type` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `data_key` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `data_value` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `data_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `data_abbr` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `data_pos` smallint(5) NOT NULL,
  `is_system_def` tinyint(1) NOT NULL,
  `is_editable` tinyint(1) NOT NULL,
  `is_validations` tinyint(1) DEFAULT NULL,
  `is_notification` tinyint(1) DEFAULT NULL,
  `is_updated_by_institute` tinyint(1) NOT NULL DEFAULT '0',
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `appl_data_notif_templates`
--

CREATE TABLE IF NOT EXISTS `appl_data_notif_templates` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `data_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `data_key` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `subject` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `message_body` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 16:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='appl_data_notif_templates';

-- --------------------------------------------------------

--
-- Table structure for table `appl_license_master`
--

CREATE TABLE IF NOT EXISTS `appl_license_master` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `fy_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `is_current_ay` tinyint(1) NOT NULL,
  `is_next_ay` tinyint(1) NOT NULL,
  `grace_period` tinyint(3) NOT NULL,
  `grace_start_date` date NOT NULL,
  `grace_end_date` date NOT NULL,
  `free_period` tinyint(3) NOT NULL,
  `free_start_date` date NOT NULL,
  `free_end_date` date NOT NULL,
  `is_license_expired` tinyint(1) NOT NULL,
  `is_license_renewed` tinyint(1) NOT NULL,
  `renewal_date` date NOT NULL,
  `license_start_date` date NOT NULL,
  `license_end_date` date NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 21:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `appl_location_master`
--

CREATE TABLE IF NOT EXISTS `appl_location_master` (
  `location_id` int(9) NOT NULL,
  `parent_id` int(5) NOT NULL,
  `location_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `location_abbr` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `pincode` mediumint(10) NOT NULL,
  `location_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'appl_data_master : LOCATION_TYPE',
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `appl_txn_master`
--

CREATE TABLE IF NOT EXISTS `appl_txn_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL COMMENT 'E.g. 2015-16',
  `inst_id` smallint(5) NOT NULL,
  `txn_id` mediumint(8) NOT NULL,
  `parent_id` int(3) NOT NULL,
  `txn_type` enum('M','S','T') COLLATE utf8_unicode_ci NOT NULL COMMENT 'appl_data_master : APPLICATION_TXN_TYPE',
  `txn_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `txn_abbr` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `route_url` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `icon_class` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `txn_desc` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `txn_lvl` tinyint(1) NOT NULL,
  `txn_seq` int(3) NOT NULL,
  `is_menu_txn` tinyint(1) NOT NULL,
  `is_blacked_out` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `assessment_score_details`
--

CREATE TABLE IF NOT EXISTS `assessment_score_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `score_id` mediumint(8) NOT NULL,
  `exam_id` mediumint(8) NOT NULL,
  `student_id` mediumint(8) NOT NULL,
  `desc_indicator_id` mediumint(8) NOT NULL,
  `assessment_score` decimal(5,2) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `asset_master`
--

CREATE TABLE IF NOT EXISTS `asset_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `asset_id` mediumint(8) NOT NULL,
  `asset_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `asset_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `asset_value` decimal(11,2) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `attendance_config`
--

CREATE TABLE IF NOT EXISTS `attendance_config` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `user_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `previous_att_upload_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `previous_attendance_status` enum('P','A') COLLATE utf8_unicode_ci NOT NULL,
  `current_attendance_status` enum('P','A') COLLATE utf8_unicode_ci NOT NULL,
  `new_attendance_status` enum('P','A') COLLATE utf8_unicode_ci NOT NULL,
  `new_att_upload_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `device_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=780;

-- --------------------------------------------------------

--
-- Table structure for table `audit_history`
--

CREATE TABLE IF NOT EXISTS `audit_history` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `comment_type` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `main_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sub_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `comment` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 16:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `bank_book_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `bank_book_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `bank_branch_map`
--

CREATE TABLE IF NOT EXISTS `bank_branch_map` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `bank_id` smallint(5) NOT NULL,
  `branch_id` smallint(5) NOT NULL,
  `branch_type` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `branch_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `branch_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `ifsc_code` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
  `working_hours_from` time NOT NULL,
  `working_hours_to` time NOT NULL,
  `weekly_holiday` varchar(9) COLLATE utf8_unicode_ci NOT NULL,
  `address_line_1` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `address_line_2` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `locality` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `state` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `pincode` mediumint(7) NOT NULL,
  `fixed_phone_1` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `mobile_phone_1` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `fixed_phone_2` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `mobile_phone_2` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 16:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `bank_master`
--

CREATE TABLE IF NOT EXISTS `bank_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `bank_id` smallint(5) NOT NULL,
  `bank_type` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `bank_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `bank_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 21:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `base_table`
--

CREATE TABLE IF NOT EXISTS `base_table` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `_desc` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `biomatric_attendance_details`
--

CREATE TABLE IF NOT EXISTS `biomatric_attendance_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `device_log_id` int(11) NOT NULL,
  `biometric_att_date` date NOT NULL,
  `file_upload_id` mediumint(8) NOT NULL,
  `employee_code` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_name` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `company` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `department` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `degination` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `grade` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `team` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shift` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `in_time` time DEFAULT NULL,
  `out_time` time DEFAULT NULL,
  `duration` timestamp NULL DEFAULT NULL,
  `late_by` timestamp NULL DEFAULT NULL,
  `early_by` timestamp NULL DEFAULT NULL,
  `status` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `punch_records` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `overtime` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `attendance_type` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `device_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `device_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `bio_type_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 16:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `book_chapter_map`
--

CREATE TABLE IF NOT EXISTS `book_chapter_map` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `book_chpt_map_id` mediumint(8) NOT NULL,
  `curriculum_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `book_id` mediumint(8) NOT NULL,
  `chapter_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `chapter_num` tinyint(3) NOT NULL,
  `chapter_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `chapter_desc` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `chapter_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `is_system_def` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `book_chapter_map_seq_generator`
--

CREATE TABLE IF NOT EXISTS `book_chapter_map_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `book_master`
--

CREATE TABLE IF NOT EXISTS `book_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `book_id` mediumint(8) NOT NULL,
  `curriculum_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `grading_system` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `standard_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `book_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `book_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `book_display_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `book_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `book_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `publisher_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `is_system_def` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=1489;

-- --------------------------------------------------------

--
-- Table structure for table `book_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `book_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=82;

-- --------------------------------------------------------

--
-- Table structure for table `campaign_history`
--

CREATE TABLE IF NOT EXISTS `campaign_history` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `campaign_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `campaign_description` varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_group_id` mediumint(8) DEFAULT NULL,
  `message_body` varchar(5000) COLLATE utf8_unicode_ci NOT NULL,
  `transaction_id` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `total_sent_count` mediumint(8) NOT NULL,
  `sms_credits` mediumint(8) NOT NULL,
  `success_count` mediumint(8) NOT NULL,
  `failed_count` mediumint(8) NOT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 12:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `certificates_generation_config`
--

CREATE TABLE IF NOT EXISTS `certificates_generation_config` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `doc_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `num_generation_mode` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `seed_number` int(11) NOT NULL,
  `frequency` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `store_file` tinyint(1) NOT NULL,
  `is_config_applicable` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 18:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL,
  `is_deleted` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `certificates_issue_history`
--

CREATE TABLE IF NOT EXISTS `certificates_issue_history` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `doc_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `certificate_number` int(11) NOT NULL DEFAULT '0',
  `user_id` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `user_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `issued_date` datetime NOT NULL,
  `issue_status` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `issued_by` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `handovered_to` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `file_upload_id` int(11) DEFAULT NULL,
  `comments` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 23:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=682;

-- --------------------------------------------------------

--
-- Table structure for table `certificates_optional_field_config`
--

CREATE TABLE IF NOT EXISTS `certificates_optional_field_config` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `doc_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `table_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `db_field` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `field_seq` tinyint(3) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=682;

-- --------------------------------------------------------

--
-- Table structure for table `channel_partner_details`
--

CREATE TABLE IF NOT EXISTS `channel_partner_details` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `channel_partner_id` mediumint(8) NOT NULL,
  `payroll_type` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `joining_date` date DEFAULT NULL,
  `resignation_date` date DEFAULT NULL,
  `salutation` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `middle_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `gender` enum('M','F','O') COLLATE utf8_unicode_ci NOT NULL,
  `edu_qual_degree1` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `edu_qual_stream1` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `edu_qual_degree2` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `edu_qual_stream2` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `previous_designation` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `work_exp_desc` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `work_exp_years` tinyint(2) DEFAULT NULL,
  `work_exp_months` tinyint(2) DEFAULT NULL,
  `emp_desc` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `birth_date` date NOT NULL,
  `birth_place` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `blood_group` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `nationality` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `religion` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `category` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `subcategory` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_line_1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_line_2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `locality` int(4) DEFAULT NULL,
  `city` int(4) DEFAULT NULL,
  `district` int(4) DEFAULT NULL,
  `state` int(4) DEFAULT NULL,
  `country` int(4) DEFAULT NULL,
  `pincode` mediumint(7) DEFAULT NULL,
  `fixed_phone_1` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mobile_phone_1` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fixed_phone_2` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mobile_phone_2` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email_main` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email_alt` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fax_number` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `profile_logo_path` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 12:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `channel_part_details_seq_generator`
--

CREATE TABLE IF NOT EXISTS `channel_part_details_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `class_definition_master`
--

CREATE TABLE IF NOT EXISTS `class_definition_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `standard_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `grading_system` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `class_def_id` mediumint(8) NOT NULL,
  `class_def_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `class_def_desc` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `class_def_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `exam_cgpa_marks` decimal(10,2) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `class_homework_details`
--

CREATE TABLE IF NOT EXISTS `class_homework_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `stud_hw_mst_id` mediumint(8) NOT NULL,
  `hw_group_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `sub_std_map_id` mediumint(8) NOT NULL,
  `hw_desc` text COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 18:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=8192;

-- --------------------------------------------------------

--
-- Table structure for table `class_master`
--

CREATE TABLE IF NOT EXISTS `class_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL COMMENT 'E.g. 2015-16',
  `inst_id` smallint(5) NOT NULL,
  `standard_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `division_id` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `shift_type` enum('Morning','Afternoon','Evening') COLLATE utf8_unicode_ci NOT NULL,
  `class_id` char(8) COLLATE utf8_unicode_ci NOT NULL,
  `curriculum_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `grading_system` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `gradeset_mst_id` smallint(5) NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `class_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `class_abbr` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `class_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `attendance_type` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `RFID_updated` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 21:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `class_teacher_mentor_map`
--

CREATE TABLE IF NOT EXISTS `class_teacher_mentor_map` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `class_id` char(8) COLLATE utf8_unicode_ci NOT NULL,
  `standard_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `division_id` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `post` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 16:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `collection_details`
--

CREATE TABLE IF NOT EXISTS `collection_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `collection_dtl_id` mediumint(8) NOT NULL,
  `collection_mst_id` mediumint(8) NOT NULL,
  `demand_mst_id` mediumint(8) NOT NULL COMMENT 'appl_data_master : FEE_PENALTY_TYPE',
  `demand_grp_id` mediumint(8) NOT NULL,
  `collection_type` enum('DEM','ADH') COLLATE utf8_unicode_ci NOT NULL,
  `pmt_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_amt` decimal(10,2) DEFAULT NULL,
  `tax_amt` decimal(10,2) NOT NULL,
  `total_pmt_amt` double(10,2) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=252;

-- --------------------------------------------------------

--
-- Table structure for table `collection_dtls_seq_generator`
--

CREATE TABLE IF NOT EXISTS `collection_dtls_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `collection_master`
--

CREATE TABLE IF NOT EXISTS `collection_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `fin_acct_id` tinyint(4) NOT NULL DEFAULT '1',
  `collection_mst_id` mediumint(8) NOT NULL,
  `demand_grp_id` mediumint(8) NOT NULL,
  `party_id` mediumint(8) NOT NULL,
  `party_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `party_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_rcpt_num` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_date` date NOT NULL,
  `pmt_amt` decimal(10,2) DEFAULT NULL,
  `tax_amt` decimal(10,2) NOT NULL,
  `total_pmt_amt` double(10,2) NOT NULL,
  `pmt_mode` varchar(15) COLLATE utf8_unicode_ci NOT NULL COMMENT 'appl_data_master : FEE_PAYMENT_MODE',
  `pmt_mode_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_instru_num` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_instru_bank` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_instru_branch` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_instru_val_date` date NOT NULL,
  `pmt_instru_holder_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_instru_chg_amt` double(10,2) NOT NULL,
  `is_suspense_txn` tinyint(1) NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=252;

-- --------------------------------------------------------

--
-- Table structure for table `collection_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `collection_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `complaint_details`
--

CREATE TABLE IF NOT EXISTS `complaint_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `complaint_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `raised_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `raised_by` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `initiator_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `sub_category_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `subject_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `complaint_desc` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `complaint_file_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `complaint_file_path` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `addressed_to` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `closed_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `sla_ends_on` timestamp NULL DEFAULT '2037-12-31 08:59:59',
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_and_renewal`
--

CREATE TABLE IF NOT EXISTS `contract_and_renewal` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` mediumint(8) NOT NULL,
  `con_renewal_id` mediumint(8) NOT NULL,
  `vendor_id` mediumint(8) NOT NULL,
  `file_upload_id` mediumint(8) NOT NULL,
  `title` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `amount` int(10) NOT NULL,
  `frequency` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `no_of_occurrences` smallint(5) NOT NULL,
  `send_notification` tinyint(1) NOT NULL,
  `remainder_sent` mediumint(8) NOT NULL,
  `start_date` date NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 10:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contract_and_renew_seq_generator`
--

CREATE TABLE IF NOT EXISTS `contract_and_renew_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=74;

-- --------------------------------------------------------

--
-- Table structure for table `custom_group_details`
--

CREATE TABLE IF NOT EXISTS `custom_group_details` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `custom_group_mst_id` mediumint(8) NOT NULL,
  `custom_group_dtl_id` mediumint(8) NOT NULL,
  `group_title` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `group_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `group_ids` varchar(2000) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` mediumint(8) NOT NULL,
  `user_name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_type` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `is_admin` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=8192;

-- --------------------------------------------------------

--
-- Table structure for table `custom_group_master`
--

CREATE TABLE IF NOT EXISTS `custom_group_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `custom_group_mst_id` mediumint(8) NOT NULL,
  `group_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `group_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `group_abbr` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `group_expiry_date` date NOT NULL DEFAULT '2037-12-30',
  `group_category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=8192;

-- --------------------------------------------------------

--
-- Table structure for table `db_error_log`
--

CREATE TABLE IF NOT EXISTS `db_error_log` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inst_id` smallint(5) NOT NULL,
  `routine_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `routine_block` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `err_code` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `err_desc` varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
  `err_type` enum('ERROR','WARNING','INFO','DEBUG','VERBOSE') COLLATE utf8_unicode_ci DEFAULT NULL,
  `err_severity` tinyint(1) NOT NULL,
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `demand_details`
--

CREATE TABLE IF NOT EXISTS `demand_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `demand_mst_id` mediumint(8) NOT NULL,
  `demand_dtl_id` mediumint(8) NOT NULL,
  `demand_grp_id` mediumint(8) NOT NULL,
  `txn_id` mediumint(8) NOT NULL,
  `installment_num` tinyint(2) NOT NULL,
  `txn_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `party_id` mediumint(8) NOT NULL,
  `party_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `party_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `demand_num` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `gen_date` date NOT NULL,
  `due_date` date NOT NULL,
  `grace_period` tinyint(3) NOT NULL,
  `final_date` date NOT NULL,
  `amt_due` double(10,2) NOT NULL,
  `tax_due` double(10,2) NOT NULL,
  `total_amt_due` double(10,2) NOT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 21:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=247;

-- --------------------------------------------------------

--
-- Table structure for table `demand_dtl_seq_generator`
--

CREATE TABLE IF NOT EXISTS `demand_dtl_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `demand_grp_seq_generator`
--

CREATE TABLE IF NOT EXISTS `demand_grp_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `demand_master`
--

CREATE TABLE IF NOT EXISTS `demand_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inst_id` smallint(5) NOT NULL,
  `demand_mst_id` mediumint(8) NOT NULL,
  `demand_grp_id` mediumint(8) NOT NULL,
  `demand_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `demand_num` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `txn_id` mediumint(8) NOT NULL,
  `opt_txn_id` mediumint(8) DEFAULT '0',
  `txn_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `installment_num` tinyint(2) NOT NULL,
  `party_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `party_id` mediumint(8) NOT NULL,
  `party_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `gen_date` date NOT NULL,
  `due_date` date NOT NULL,
  `grace_period` tinyint(3) NOT NULL,
  `final_date` date NOT NULL,
  `amt_due` double(10,2) NOT NULL,
  `tax_due` double(10,2) NOT NULL,
  `amt_clear` double(10,2) NOT NULL,
  `tax_clear` double(10,2) NOT NULL,
  `amt_unclear` double(10,2) NOT NULL,
  `tax_unclear` double(10,2) NOT NULL,
  `total_amt_due` double(10,2) NOT NULL,
  `fin_acct_id` smallint(5) NOT NULL DEFAULT '1',
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=247;

-- --------------------------------------------------------

--
-- Table structure for table `demand_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `demand_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `department_master`
--

CREATE TABLE IF NOT EXISTS `department_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `dept_id` smallint(5) NOT NULL,
  `parent_id` smallint(5) NOT NULL,
  `dept_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `dept_display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `dept_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `dept_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `dept_level` tinyint(2) NOT NULL DEFAULT '0',
  `dept_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `dept_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `dept_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `division_master`
--

CREATE TABLE IF NOT EXISTS `division_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `division_id` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `division_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `division_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `division_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `document_master`
--

CREATE TABLE IF NOT EXISTS `document_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` varchar(7) DEFAULT NULL,
  `inst_id` smallint(5) NOT NULL,
  `entity_type` varchar(15) NOT NULL,
  `entity_id` mediumint(8) NOT NULL,
  `file_upload_id` mediumint(8) DEFAULT NULL,
  `module_name` varchar(20) NOT NULL,
  `doc_category` varchar(30) NOT NULL,
  `doc_subcategory` varchar(30) DEFAULT NULL,
  `doc_name` varchar(50) DEFAULT NULL,
  `is_mandatory` tinyint(1) DEFAULT NULL,
  `doc_upload_status` varchar(25) DEFAULT '0',
  `doc_submit_status` varchar(25) DEFAULT '0',
  `status_desc` varchar(100) DEFAULT NULL,
  `is_verified` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 14:29:59',
  `created_by` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `email_template_master`
--

CREATE TABLE IF NOT EXISTS `email_template_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `txn_id` mediumint(8) NOT NULL,
  `recipient_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `email_title` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `email_body` varchar(5000) COLLATE utf8_unicode_ci NOT NULL,
  `param_count` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `employee_master`
--

CREATE TABLE IF NOT EXISTS `employee_master` (
  `id` mediumint(8) NOT NULL,
  `org_id` smallint(5) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `emp_type` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `payroll_type` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dept_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `emp_category` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `emp_designation` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `emp_num` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `joining_date` date DEFAULT NULL,
  `resignation_date` date DEFAULT NULL,
  `salutation` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `middle_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `gender` enum('M','F','O') COLLATE utf8_unicode_ci NOT NULL,
  `emp_grade` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `edu_qual_degree1` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `edu_qual_stream1` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `edu_qual_degree2` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `edu_qual_stream2` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `previous_org` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `previous_designation` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `work_exp_desc` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `work_exp_years` tinyint(2) DEFAULT NULL,
  `work_exp_months` tinyint(2) DEFAULT NULL,
  `emp_desc` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `birth_date` date NOT NULL,
  `birth_place` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `marital_status` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `anniversary_date` date DEFAULT NULL,
  `children_count` tinyint(2) DEFAULT NULL,
  `father_name` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mother_name` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `husband_name` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `blood_group` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `nationality` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `religion` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `category` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `subcategory` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_line_1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_line_2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `locality` int(4) DEFAULT NULL,
  `city` int(4) DEFAULT NULL,
  `district` int(4) DEFAULT NULL,
  `state` int(4) DEFAULT NULL,
  `country` int(4) DEFAULT NULL,
  `pincode` mediumint(7) DEFAULT NULL,
  `fixed_phone_1` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mobile_phone_1` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fixed_phone_2` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mobile_phone_2` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `app_registration_id` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email_main` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email_alt` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fax_number` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `aadhar_card_num` varchar(12) COLLATE utf8_unicode_ci NOT NULL,
  `profile_logo_path` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `emp_status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `accessible_institute` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `bulk_upload_status` tinyint(3) NOT NULL,
  `emp_bulk_file_upload_id` mediumint(8) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 23:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `employee_shift_details`
--

CREATE TABLE IF NOT EXISTS `employee_shift_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `user_id` mediumint(8) NOT NULL,
  `shift` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shift_in_time` time DEFAULT NULL,
  `shift_out_time` time DEFAULT NULL,
  `is_in_time_applicable` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_attendance_details`
--

CREATE TABLE IF NOT EXISTS `emp_attendance_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `attendance_mst_id` mediumint(8) NOT NULL,
  `attendance_dtl_id` mediumint(8) NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `is_half_day` tinyint(1) NOT NULL,
  `leave_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `leave_reason` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `attendance_status` enum('P','A') COLLATE utf8_unicode_ci NOT NULL,
  `attendance_comments` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `emp_att_upload_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `bio_type_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_attendance_details_seq_generator`
--

CREATE TABLE IF NOT EXISTS `emp_attendance_details_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_attendance_master`
--

CREATE TABLE IF NOT EXISTS `emp_attendance_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `attendance_mst_id` mediumint(8) NOT NULL,
  `file_upload_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `attendance_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'appl_data_master : EMP_ATTENDANCE_TYPE',
  `attendance_date` date NOT NULL,
  `emp_count` tinyint(3) NOT NULL,
  `present_count` tinyint(3) NOT NULL,
  `halfday_count` tinyint(3) NOT NULL,
  `absent_count` tinyint(3) NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL COMMENT 'appl_data_master : EMP_ATTENDANCE_STATUS',
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `attendance_comments` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `attendance_upload_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_attendance_master_seq_generator`
--

CREATE TABLE IF NOT EXISTS `emp_attendance_master_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_bank_account_details`
--

CREATE TABLE IF NOT EXISTS `emp_bank_account_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `branch_id` smallint(5) NOT NULL,
  `account_num` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `account_since` date NOT NULL,
  `account_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_dept_event_details`
--

CREATE TABLE IF NOT EXISTS `emp_dept_event_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `emp_dept_event_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `event_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `emp_dept_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_dept_map`
--

CREATE TABLE IF NOT EXISTS `emp_dept_map` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `dept_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `emp_role` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `allocation_start_date` date NOT NULL,
  `allocation_end_date` date NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_leave_details`
--

CREATE TABLE IF NOT EXISTS `emp_leave_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `emp_leave_id` mediumint(8) NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `supervisor_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `leave_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `leave_status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `leave_applied_on` date NOT NULL,
  `leave_start_date` date NOT NULL,
  `leave_end_date` date NOT NULL,
  `leave_duration` tinyint(3) NOT NULL,
  `is_half_day` tinyint(1) NOT NULL,
  `half_day_shift` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `is_back_dated` tinyint(1) NOT NULL,
  `initiator_comments` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `approved_on` date NOT NULL,
  `approved_by` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `approval_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `approver_comments` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_leave_dtl_seq_generator`
--

CREATE TABLE IF NOT EXISTS `emp_leave_dtl_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=82;

-- --------------------------------------------------------

--
-- Table structure for table `emp_leave_package_details`
--

CREATE TABLE IF NOT EXISTS `emp_leave_package_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `package_dtl_id` mediumint(8) NOT NULL,
  `package_id` mediumint(8) NOT NULL,
  `leave_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `no_of_leaves` tinyint(3) NOT NULL,
  `min_used_leave` mediumint(8) NOT NULL,
  `carry_forward_leave` mediumint(8) NOT NULL,
  `total_max_cf_leave` mediumint(8) NOT NULL,
  `credit_frequency` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `is_reimbursement` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 23:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_leave_package_history`
--

CREATE TABLE IF NOT EXISTS `emp_leave_package_history` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(8) NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `package_id` mediumint(8) NOT NULL,
  `leave_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `credit` int(20) NOT NULL,
  `leave_balance` int(20) NOT NULL,
  `total_leave` int(20) NOT NULL,
  `used_leave` int(20) NOT NULL,
  `cary_forward_leave` int(25) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `is_active` tinyint(1) NOT NULL,
  `is_deleted` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_leave_package_map`
--

CREATE TABLE IF NOT EXISTS `emp_leave_package_map` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `package_id` mediumint(8) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 18:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_leave_package_master`
--

CREATE TABLE IF NOT EXISTS `emp_leave_package_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `package_id` mediumint(8) NOT NULL,
  `package_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `package_display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `package_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `package_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `package_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `is_default` tinyint(1) NOT NULL,
  `leave_year` mediumint(8) NOT NULL,
  `is_custom` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 23:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_leave_pack_dtl_seq_generator`
--

CREATE TABLE IF NOT EXISTS `emp_leave_pack_dtl_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_leave_pack_map_seq_generator`
--

CREATE TABLE IF NOT EXISTS `emp_leave_pack_map_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_leave_pack_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `emp_leave_pack_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `emp_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_subject_map`
--

CREATE TABLE IF NOT EXISTS `emp_subject_map` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `emp_sub_map_id` mediumint(8) NOT NULL,
  `standard_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `division_id` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `group_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `group_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `sub_std_map_id` mediumint(8) NOT NULL,
  `allocation_start_date` date NOT NULL,
  `allocation_end_date` date NOT NULL,
  `is_allocation_temp` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 14:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `emp_subject_map_seq_generator`
--

CREATE TABLE IF NOT EXISTS `emp_subject_map_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `event_calendar_category`
--

CREATE TABLE IF NOT EXISTS `event_calendar_category` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `event_cat` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `cat_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `cat_icon` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `cat_class` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `cat_order` smallint(5) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 21:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `event_calendar_master`
--

CREATE TABLE IF NOT EXISTS `event_calendar_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `event_id` mediumint(8) NOT NULL,
  `event_cat` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `event_sub_cat` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `event_title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `event_desc` varchar(5000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `client_type` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `client_id` int(10) DEFAULT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `is_mandatory` tinyint(1) NOT NULL,
  `is_global` tinyint(1) NOT NULL,
  `is_all_day` tinyint(1) NOT NULL,
  `is_notification` tinyint(1) NOT NULL,
  `is_notification_sent` tinyint(1) DEFAULT '0',
  `notif_reminder_days` smallint(2) DEFAULT NULL,
  `notification_id` mediumint(8) DEFAULT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 21:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `event_calendar_seq_generator`
--

CREATE TABLE IF NOT EXISTS `event_calendar_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `event_calendar_subcategory`
--

CREATE TABLE IF NOT EXISTS `event_calendar_subcategory` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `event_cat` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `event_sub_cat` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `subcat_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `subcat_icon` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `subcat_class` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `subcat_order` smallint(5) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 21:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `event_participant_map`
--

CREATE TABLE IF NOT EXISTS `event_participant_map` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `event_id` mediumint(8) NOT NULL,
  `group_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `group_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `group_ids` varchar(2000) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 15:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_category_master`
--

CREATE TABLE IF NOT EXISTS `exam_category_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `exam_cat_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `curriculum_type` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `grading_system` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `exam_cat_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `exam_cat_display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `exam_cat_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `is_system_def` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_details`
--

CREATE TABLE IF NOT EXISTS `exam_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `exam_mst_id` mediumint(8) NOT NULL,
  `exam_dtl_id` mediumint(8) NOT NULL,
  `standard_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `division_id` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `group_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `group_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `curriculum_type` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `grading_system` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `gradeset_mst_id` smallint(5) NOT NULL,
  `evaluation_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `sub_std_map_id` mediumint(8) NOT NULL,
  `exam_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `exam_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `exam_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `exam_start_time` datetime NOT NULL,
  `exam_end_time` datetime NOT NULL,
  `exam_max_marks` decimal(5,1) NOT NULL,
  `exam_min_marks` decimal(5,1) NOT NULL,
  `exam_weightage` tinyint(3) NOT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 21:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=310;

-- --------------------------------------------------------

--
-- Table structure for table `exam_dtl_seq_generator`
--

CREATE TABLE IF NOT EXISTS `exam_dtl_seq_generator` (
  `id_num` int(11) NOT NULL,
  `id_ts` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_group_details`
--

CREATE TABLE IF NOT EXISTS `exam_group_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `exam_grp_mst_id` mediumint(8) NOT NULL,
  `exam_grp_dtl_id` mediumint(8) NOT NULL,
  `exam_mst_id` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `exam_weightage` tinyint(3) NOT NULL,
  `exam_grp_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `exam_best_of` int(11) NOT NULL,
  `exam_grp_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=4096;

-- --------------------------------------------------------

--
-- Table structure for table `exam_group_master`
--

CREATE TABLE IF NOT EXISTS `exam_group_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `curriculum_type` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `grading_system` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `exam_set` int(11) NOT NULL DEFAULT '1',
  `standard_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `division_id` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `group_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `group_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `exam_grp_mst_id` mediumint(8) NOT NULL,
  `exam_term` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `exam_grp_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `exam_grp_display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `exam_grp_abbr` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `is_system_def` tinyint(1) NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_group_score_details`
--

CREATE TABLE IF NOT EXISTS `exam_group_score_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `exam_grp_mst_id` mediumint(8) NOT NULL,
  `sub_std_map_id` mediumint(8) NOT NULL,
  `student_id` mediumint(8) NOT NULL,
  `exam_marks` double(5,2) NOT NULL,
  `exam_points` decimal(5,2) NOT NULL,
  `exam_grade` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `gradset_dtl_id` mediumint(8) NOT NULL,
  `exam_remarks` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `is_fail` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_group_summary`
--

CREATE TABLE IF NOT EXISTS `exam_group_summary` (
  `id` int(11) NOT NULL,
  `ay_id` varchar(10) NOT NULL,
  `inst_id` int(11) NOT NULL,
  `exam_grp_mst_id` int(11) NOT NULL,
  `exam_group_name` varchar(20) NOT NULL,
  `group_type` varchar(10) NOT NULL,
  `group_id` varchar(20) NOT NULL,
  `class_name` varchar(50) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `exam_grp_dtls_seq_generator`
--

CREATE TABLE IF NOT EXISTS `exam_grp_dtls_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_grp_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `exam_grp_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_master`
--

CREATE TABLE IF NOT EXISTS `exam_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `exam_mst_id` mediumint(8) NOT NULL,
  `standard_id` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `division_id` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `group_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `group_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `gradeset_mst_id` smallint(5) NOT NULL,
  `evaluation_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'appl_data_master : %_EVAL_TYPE',
  `exam_cat_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `exam_term` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `exam_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `exam_start_date` date NOT NULL,
  `exam_end_date` date NOT NULL,
  `exam_weightage` tinyint(3) NOT NULL,
  `is_system_def` tinyint(1) NOT NULL,
  `exam_visibility` char(1) COLLATE utf8_unicode_ci DEFAULT 'A',
  `student_report_format` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `section_levels` tinyint(1) DEFAULT '0',
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_master_PM`
--

CREATE TABLE IF NOT EXISTS `exam_master_PM` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `exam_mst_id` mediumint(8) NOT NULL,
  `standard_id` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `division_id` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `group_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `group_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `gradeset_mst_id` smallint(5) NOT NULL,
  `evaluation_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'appl_data_master : %_EVAL_TYPE',
  `exam_cat_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `exam_term` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `exam_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `exam_start_date` date NOT NULL,
  `exam_end_date` date NOT NULL,
  `exam_weightage` tinyint(3) NOT NULL,
  `is_system_def` tinyint(1) NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 21:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `exam_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_remarks`
--

CREATE TABLE IF NOT EXISTS `exam_remarks` (
  `id` int(11) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `exam_id` int(11) DEFAULT NULL,
  `exam_type` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `student_id` int(11) DEFAULT NULL,
  `remarks_meta` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `remarks` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `remarks_by` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `updated_on` timestamp NULL DEFAULT NULL,
  `is_active` tinyint(4) DEFAULT NULL,
  `is_deleted` tinyint(4) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_results_summary`
--

CREATE TABLE IF NOT EXISTS `exam_results_summary` (
  `id` int(11) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `class_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `exam_id` mediumint(8) NOT NULL,
  `exam_type` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `exam_subjects_count` int(4) DEFAULT NULL,
  `exam_total_marks` decimal(7,1) NOT NULL,
  `exam_gradeset_id` int(11) DEFAULT NULL,
  `student_id` int(11) NOT NULL,
  `student_marks_total` decimal(7,1) NOT NULL,
  `student_percentage` decimal(5,2) NOT NULL,
  `student_grade` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `student_grade_points` int(11) DEFAULT NULL,
  `student_rank` smallint(6) DEFAULT NULL,
  `created_by` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `updated_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `is_active` tinyint(4) DEFAULT NULL,
  `is_deleted` tinyint(4) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_result_meta`
--

CREATE TABLE IF NOT EXISTS `exam_result_meta` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `meta_mst_id` mediumint(8) NOT NULL,
  `exam_mst_id` mediumint(8) NOT NULL,
  `exam_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `for_exam` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `group_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `group_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `meta_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `meta_term` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `meta_details` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 10:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_result_meta_details`
--

CREATE TABLE IF NOT EXISTS `exam_result_meta_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `meta_mst_id` mediumint(8) NOT NULL,
  `student_id` mediumint(8) NOT NULL,
  `F1` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `F2` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `F3` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `F4` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `F5` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `F6` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `F7` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `F8` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `F9` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `F10` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `F11` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `F12` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_result_meta_seq_generator`
--

CREATE TABLE IF NOT EXISTS `exam_result_meta_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_score_details`
--

CREATE TABLE IF NOT EXISTS `exam_score_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `exam_dtl_id` bigint(20) NOT NULL,
  `student_id` mediumint(8) NOT NULL,
  `exam_marks` double(5,2) NOT NULL,
  `exam_points` decimal(5,2) NOT NULL,
  `exam_grade` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `gradset_dtl_id` mediumint(8) NOT NULL,
  `exam_status` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'P - Present; A - Absent; D - Debarred;',
  `exam_remarks` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `is_fail` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=137;

-- --------------------------------------------------------

--
-- Table structure for table `exam_score_details_mig`
--

CREATE TABLE IF NOT EXISTS `exam_score_details_mig` (
  `standard_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `division_id` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `middle_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `subject_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `fa_1` double(5,2) NOT NULL,
  `fa_2` double(5,2) NOT NULL,
  `fa_3` double(5,2) NOT NULL,
  `fa_4` double(5,2) NOT NULL,
  `sa_1` double(5,2) NOT NULL,
  `sa_2` double(5,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `exam_student_code_details`
--

CREATE TABLE IF NOT EXISTS `exam_student_code_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `exam_mst_id` mediumint(8) NOT NULL,
  `group_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `group_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `student_id` mediumint(8) NOT NULL,
  `exam_student_code` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 16:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE IF NOT EXISTS `failed_jobs` (
  `id` int(10) unsigned NOT NULL,
  `connection` text COLLATE utf8_unicode_ci NOT NULL,
  `queue` text COLLATE utf8_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `feedback_complaint_attachements`
--

CREATE TABLE IF NOT EXISTS `feedback_complaint_attachements` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `feedback_id` mediumint(8) NOT NULL,
  `type` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `reply_id` mediumint(8) NOT NULL,
  `file_upload_id` mediumint(8) NOT NULL,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `feedback_dtl_seq_generator`
--

CREATE TABLE IF NOT EXISTS `feedback_dtl_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=34;

-- --------------------------------------------------------

--
-- Table structure for table `feedback_management_master`
--

CREATE TABLE IF NOT EXISTS `feedback_management_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `feedback_id` mediumint(8) NOT NULL,
  `sender_user_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `feedback_from` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `feedback_date` date NOT NULL,
  `type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `feedback_for` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `receiver_user_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `feedback_for_person` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `feedback_for_direct_person` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `rating` int(11) NOT NULL,
  `feedback_comment` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `is_read` tinyint(1) DEFAULT '0',
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 16:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `feedback_reply_detail`
--

CREATE TABLE IF NOT EXISTS `feedback_reply_detail` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `feedback_id` mediumint(8) NOT NULL,
  `reply_from` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `reply_user_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `reply_date` date NOT NULL,
  `reply` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `is_read` tinyint(4) NOT NULL DEFAULT '0',
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 10:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_demand_dtls_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fee_demand_dtls_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_demand_grp_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fee_demand_grp_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_demand_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fee_demand_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_quickpay_dtl_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fee_quickpay_dtl_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_quickpay_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fee_quickpay_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_structure_details`
--

CREATE TABLE IF NOT EXISTS `fee_structure_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `fee_struct_mst_id` mediumint(8) NOT NULL,
  `fee_struct_dtl_id` mediumint(8) NOT NULL,
  `acct_head_id` mediumint(8) NOT NULL,
  `student_cat_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'appl_data_master : STUD_CATEGORY_TYPE',
  `fee_date_from` date NOT NULL,
  `fee_date_to` date NOT NULL,
  `fee_due_date` date NOT NULL,
  `fee_grace_period` tinyint(3) NOT NULL,
  `installment_name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `installment_num` tinyint(2) NOT NULL,
  `fee_amt` double(12,2) NOT NULL,
  `disc_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL COMMENT 'appl_data_master : FEE_DISCOUNT_TYPE',
  `disc_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `disc_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `disc_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `disc_rate` double(5,2) NOT NULL,
  `disc_amt` double(10,2) NOT NULL,
  `net_fee_amt` double(10,2) NOT NULL,
  `tax_amt` double(10,2) NOT NULL,
  `total_fee_amt` double(12,2) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_structure_dtl_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fee_structure_dtl_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_structure_master`
--

CREATE TABLE IF NOT EXISTS `fee_structure_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `fin_acct_id` smallint(6) NOT NULL DEFAULT '1',
  `fee_struct_mst_id` mediumint(8) NOT NULL,
  `shift_type` enum('Morning','Afternoon','Evening') COLLATE utf8_unicode_ci NOT NULL,
  `group_type` enum('SCH','ADH','STD','CLS','GRP') COLLATE utf8_unicode_ci NOT NULL,
  `group_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `fee_pmt_term` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `fee_term_num` tinyint(2) NOT NULL,
  `fee_date_from` date NOT NULL,
  `fee_date_to` date NOT NULL,
  `fee_due_date` date NOT NULL,
  `fee_grace_period` tinyint(3) NOT NULL,
  `fee_struct_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `fee_struct_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `fee_struct_abbr` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `is_installment_based` tinyint(1) NOT NULL,
  `installment_count` tinyint(2) NOT NULL,
  `is_stud_cat_based` tinyint(1) NOT NULL,
  `fee_amt` decimal(12,2) NOT NULL,
  `tax_amt` double(12,2) NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `fee_options` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fee_display_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `auto_reminder` tinyint(4) NOT NULL DEFAULT '0',
  `reminder_options` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 03:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_structure_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fee_structure_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_struct_dtl_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fee_struct_dtl_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_struct_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fee_struct_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fee_type_master_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fee_type_master_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `file_uploads`
--

CREATE TABLE IF NOT EXISTS `file_uploads` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `inst_id` smallint(5) NOT NULL,
  `file_upload_id` mediumint(8) NOT NULL,
  `file_category` varchar(200) NOT NULL,
  `file_name` varchar(200) NOT NULL,
  `file_type` varchar(20) DEFAULT NULL,
  `file_size` varchar(20) DEFAULT NULL,
  `file_path` varchar(200) NOT NULL,
  `original_file_name` varchar(200) DEFAULT NULL,
  `thumbnail_name` varchar(200) DEFAULT NULL,
  `thumbnail_path` varchar(200) DEFAULT NULL,
  `ip_address` varchar(15) DEFAULT NULL,
  `description` varchar(20) DEFAULT NULL,
  `short_url` varchar(200) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 21:59:59',
  `created_by` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `file_upload_details`
--

CREATE TABLE IF NOT EXISTS `file_upload_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `standard_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `division_id` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `file_upload_id` mediumint(8) NOT NULL,
  `class_id` char(8) COLLATE utf8_unicode_ci NOT NULL,
  `file_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `file_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `upload_path` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `uploaded_by` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `upload_date` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `upload_comments` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `file_upload_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `file_upload_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `file_upload_seq_generator`
--

CREATE TABLE IF NOT EXISTS `file_upload_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_accounts`
--

CREATE TABLE IF NOT EXISTS `fin_accounts` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `fin_acct_id` mediumint(8) NOT NULL,
  `sequence_entity_id` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `payment_sequence_entity_id` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `sequence_entity_mode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `acct_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `acct_display_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `acct_short_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `address` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `pan_card_no` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `gst_reg_no` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `logo_path` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT '1',
  `is_hidden_students` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `mandatory_first` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `preferences` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `razorpay_acct_no` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=8192;

-- --------------------------------------------------------

--
-- Table structure for table `fin_arrears_details`
--

CREATE TABLE IF NOT EXISTS `fin_arrears_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `arrear_mst_id` mediumint(8) NOT NULL,
  `arrear_dtl_id` mediumint(8) NOT NULL,
  `released_amount` bigint(20) NOT NULL,
  `recovery_date` date NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 12:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_arrears_master`
--

CREATE TABLE IF NOT EXISTS `fin_arrears_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `arrear_mst_id` mediumint(8) NOT NULL,
  `sal_mst_id` mediumint(8) NOT NULL,
  `user_id` mediumint(8) NOT NULL,
  `arrear_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `amount` mediumint(8) NOT NULL,
  `approved_amount` bigint(20) NOT NULL,
  `released_amount` bigint(20) NOT NULL,
  `status` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `comments` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `paid_date` date NOT NULL,
  `recovery_date` date NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 18:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_arrear_dtl_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_arrear_dtl_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_arrear_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_arrear_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_bank_account_master`
--

CREATE TABLE IF NOT EXISTS `fin_bank_account_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `bank_id` mediumint(8) NOT NULL,
  `bank_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `bank_display_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `branch` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `account_no` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `ifsc_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `address` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `bank_contact_no` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `status` tinyint(1) DEFAULT '1',
  `party_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `party_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `bank_holder` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 10:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_bank_book`
--

CREATE TABLE IF NOT EXISTS `fin_bank_book` (
  `id` mediumint(9) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `fin_acct_id` int(11) NOT NULL,
  `bank_book_mst_id` int(11) NOT NULL,
  `bank_mst_id` int(11) NOT NULL,
  `trans_type` char(2) CHARACTER SET latin1 NOT NULL,
  `trans_amount` int(11) NOT NULL,
  `trans_category` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `trans_mode` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `trans_date` date NOT NULL,
  `trans_status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `trans_posted_date` date DEFAULT NULL,
  `trans_realized_date` date DEFAULT NULL,
  `trans_ref_no` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `narration` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `trans_by` int(11) DEFAULT NULL,
  `trans_reconciliation_type` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `trans_reconciliation_id` int(11) DEFAULT NULL,
  `trans_reconciliation_status` tinyint(4) DEFAULT NULL,
  `reciept_photo` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `status` tinyint(4) NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11) DEFAULT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) DEFAULT '1',
  `is_deleted` tinyint(1) DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_debit_demand_group_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_debit_demand_group_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_debit_demand_master`
--

CREATE TABLE IF NOT EXISTS `fin_debit_demand_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inst_id` smallint(5) NOT NULL,
  `fin_acct_id` mediumint(8) NOT NULL,
  `debit_demand_mst_id` mediumint(8) NOT NULL,
  `debit_demand_grp_id` mediumint(8) NOT NULL,
  `debit_demand_num` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `user_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `txn_id` mediumint(8) NOT NULL,
  `exp_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `exp_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `exp_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `installment_num` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `party_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `party_id` mediumint(8) NOT NULL,
  `party_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `acct_head_id` smallint(5) NOT NULL,
  `gen_date` date NOT NULL,
  `due_date` date NOT NULL,
  `amt_payble` double(10,2) NOT NULL,
  `tax_payble` double(10,2) NOT NULL,
  `amt_paid` double(10,2) NOT NULL,
  `tax_paid` double(10,2) NOT NULL,
  `amt_unclear` double(10,2) NOT NULL,
  `tax_unclear` double(10,2) NOT NULL,
  `total_amt_paid` double(10,2) NOT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `is_bill_pending` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 10:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=247;

-- --------------------------------------------------------

--
-- Table structure for table `fin_debit_demand_master_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_debit_demand_master_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_debit_details`
--

CREATE TABLE IF NOT EXISTS `fin_debit_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `fin_acct_id` mediumint(8) NOT NULL,
  `debit_dtl_id` mediumint(8) NOT NULL,
  `debit_mst_id` mediumint(8) NOT NULL,
  `debit_demand_mst_id` mediumint(8) NOT NULL,
  `debit_demand_grp_id` mediumint(8) NOT NULL,
  `exp_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_amt` decimal(10,2) NOT NULL,
  `tax_amt` decimal(10,2) NOT NULL,
  `total_pmt_amt` double(10,2) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 10:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=252;

-- --------------------------------------------------------

--
-- Table structure for table `fin_debit_details_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_debit_details_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=82;

-- --------------------------------------------------------

--
-- Table structure for table `fin_debit_master`
--

CREATE TABLE IF NOT EXISTS `fin_debit_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `file_upload_id` mediumint(8) NOT NULL,
  `fin_acct_id` mediumint(8) NOT NULL,
  `debit_mst_id` mediumint(8) NOT NULL,
  `debit_demand_grp_id` mediumint(8) NOT NULL,
  `fin_receipt` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `exp_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `exp_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `party_id` mediumint(8) NOT NULL,
  `party_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `party_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `exp_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_rcpt_num` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `pmt_date` date NOT NULL,
  `pmt_amt` decimal(10,2) DEFAULT NULL,
  `tax_amt` decimal(10,2) NOT NULL DEFAULT '0.00',
  `total_pmt_amt` double(10,2) NOT NULL,
  `pmt_mode` varchar(15) COLLATE utf8_unicode_ci NOT NULL COMMENT 'appl_data_master : FEE_PAYMENT_MODE',
  `pmt_mode_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_instru_num` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_instru_bank` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_instru_branch` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_instru_val_date` date DEFAULT NULL,
  `pmt_bank_mst_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_suspense_txn` tinyint(1) DEFAULT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status_desc` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_bill_pending` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 10:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=252;

-- --------------------------------------------------------

--
-- Table structure for table `fin_debit_master_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_debit_master_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=82;

-- --------------------------------------------------------

--
-- Table structure for table `fin_default_fee_strct_id_config`
--

CREATE TABLE IF NOT EXISTS `fin_default_fee_strct_id_config` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(8) NOT NULL,
  `module_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `fin_acct_id` smallint(5) NOT NULL,
  `fee_struct_mst_id` smallint(8) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 23:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_emp_payroll_details_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_emp_payroll_details_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_emp_payroll_master`
--

CREATE TABLE IF NOT EXISTS `fin_emp_payroll_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `fin_acct_id` mediumint(8) NOT NULL,
  `template_id` mediumint(8) NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `applied_date` date DEFAULT NULL,
  `remark` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_emp_payroll_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_emp_payroll_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_emp_salary_details_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_emp_salary_details_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_emp_salary_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_emp_salary_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_generic_setting`
--

CREATE TABLE IF NOT EXISTS `fin_generic_setting` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `is_bounce_back_applicable` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `bounce_back_charges` int(5) NOT NULL DEFAULT '0',
  `is_fee_auto_reminder` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `days_before_due_date` smallint(3) DEFAULT '0',
  `days_after_due_date` smallint(3) DEFAULT '0',
  `is_penalty_after_due_date` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 10:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_invoice_details`
--

CREATE TABLE IF NOT EXISTS `fin_invoice_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `invoice_mst_id` mediumint(8) NOT NULL,
  `invoice_dtl_id` mediumint(8) NOT NULL,
  `installment_remark` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `amount` bigint(50) NOT NULL,
  `tax_id` mediumint(8) NOT NULL,
  `tax` bigint(50) NOT NULL,
  `total_amount` bigint(50) NOT NULL,
  `due_date` date DEFAULT NULL,
  `payment_date` date DEFAULT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_invoice_details_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_invoice_details_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_invoice_master`
--

CREATE TABLE IF NOT EXISTS `fin_invoice_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `invoice_mst_id` mediumint(8) NOT NULL,
  `file_upload_id` mediumint(8) NOT NULL,
  `installment_remark` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `invoice_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `acct_head_id` mediumint(8) NOT NULL,
  `fin_acct_id` mediumint(8) NOT NULL,
  `party_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `party_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `amount` bigint(50) NOT NULL,
  `tax_id` mediumint(8) NOT NULL,
  `tax` bigint(50) NOT NULL,
  `total_amount` bigint(50) NOT NULL,
  `invoice_date` date DEFAULT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `installment_count` mediumint(8) NOT NULL,
  `attachment` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_invoice_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `fin_invoice_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_modulewise_config`
--

CREATE TABLE IF NOT EXISTS `fin_modulewise_config` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `module_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `acct_head_id` smallint(5) NOT NULL,
  `fin_acct_id` smallint(5) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 23:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_party_master`
--

CREATE TABLE IF NOT EXISTS `fin_party_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `party_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `bank_id` mediumint(8) NOT NULL,
  `address_id` mediumint(8) NOT NULL,
  `party_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `party_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `address` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `party_contact_no` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `pan_card_no` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `gst_reg_no` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_payment_recovery`
--

CREATE TABLE IF NOT EXISTS `fin_payment_recovery` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `recovery_mst_id` mediumint(8) NOT NULL,
  `sal_mst_id` mediumint(8) NOT NULL,
  `user_id` mediumint(8) NOT NULL,
  `released_amount` bigint(20) NOT NULL,
  `recovery_date` date NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 07:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_payroll_template_details`
--

CREATE TABLE IF NOT EXISTS `fin_payroll_template_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `payroll_mst_id` mediumint(8) NOT NULL,
  `payroll_dtl_id` mediumint(8) NOT NULL,
  `component_id` mediumint(8) NOT NULL,
  `component_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `component_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `amount` bigint(50) NOT NULL,
  `is_leave_deduct` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_payroll_template_master`
--

CREATE TABLE IF NOT EXISTS `fin_payroll_template_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `payroll_mst_id` mediumint(8) NOT NULL,
  `template_id` mediumint(8) NOT NULL,
  `payroll_template_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `comments` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `salary_allowance_amount` bigint(50) NOT NULL,
  `salary_deduction_amount` bigint(50) NOT NULL,
  `net_amount` bigint(50) NOT NULL,
  `payroll_date` date DEFAULT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_processing_user_list`
--

CREATE TABLE IF NOT EXISTS `fin_processing_user_list` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `sal_mst_id` mediumint(8) NOT NULL,
  `salary_month` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `fin_acct_id` mediumint(8) NOT NULL,
  `user_id` mediumint(8) NOT NULL,
  `user_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 18:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_salary_component`
--

CREATE TABLE IF NOT EXISTS `fin_salary_component` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `component_id` mediumint(8) NOT NULL,
  `component_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `component_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `tax_deduction` tinyint(1) NOT NULL,
  `tax_per_month` bigint(50) NOT NULL,
  `tax_per_year` bigint(50) NOT NULL,
  `provident_fund` tinyint(1) NOT NULL,
  `is_ESIC` tinyint(1) NOT NULL,
  `is_system_def` tinyint(1) NOT NULL DEFAULT '1',
  `comments` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `component_value` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `component_category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `formula` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `is_formula_based` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `arrears_recovery_flag` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_salary_details`
--

CREATE TABLE IF NOT EXISTS `fin_salary_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `sal_mst_id` mediumint(8) NOT NULL,
  `sal_dtl_id` mediumint(8) NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `template_id` mediumint(8) NOT NULL,
  `gross` bigint(50) NOT NULL,
  `deduction` bigint(50) NOT NULL,
  `net_amount` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `salary_status` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_salary_details_components`
--

CREATE TABLE IF NOT EXISTS `fin_salary_details_components` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `sal_dtl_id` mediumint(8) NOT NULL,
  `emp_id` mediumint(8) NOT NULL,
  `component_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `component_id` mediumint(8) NOT NULL,
  `amount` bigint(50) NOT NULL,
  `amt_paid` double(10,2) NOT NULL,
  `arrears` double(10,2) NOT NULL,
  `status` tinyint(2) NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `leave_deduct_amount` bigint(30) NOT NULL,
  `consume_days` mediumint(8) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_salary_generic_setting`
--

CREATE TABLE IF NOT EXISTS `fin_salary_generic_setting` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `leave_credit_frequency` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `salary_process` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `leave_deduction` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `from_earning` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `from_deduction` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `working_days` mediumint(8) NOT NULL,
  `leave_deduction_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `attendance_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `leave_deduct_amt_display_on` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `leave_amt_deduct_from` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 18:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_salary_master`
--

CREATE TABLE IF NOT EXISTS `fin_salary_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `sal_mst_id` mediumint(8) NOT NULL,
  `fin_acct_id` mediumint(8) NOT NULL,
  `salary_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `comments` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `salary_year` mediumint(8) DEFAULT NULL,
  `salary_month` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `salary_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `fromDate` date NOT NULL,
  `toDate` date NOT NULL,
  `salary_status` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `total_salary` bigint(100) NOT NULL,
  `salary_date` date NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 05:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fin_user_manual_leave`
--

CREATE TABLE IF NOT EXISTS `fin_user_manual_leave` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `sal_mst_id` mediumint(8) NOT NULL,
  `user_id` mediumint(8) NOT NULL,
  `user_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `working_days` mediumint(8) NOT NULL,
  `present_days` mediumint(8) NOT NULL,
  `absent_days` mediumint(8) NOT NULL,
  `leave_balance` decimal(7,2) NOT NULL,
  `paid_leave` decimal(7,1) NOT NULL,
  `latemark` decimal(7,1) NOT NULL,
  `deduct_leave` decimal(7,2) NOT NULL,
  `month` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `assign_date` date NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-29 18:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `form_data_details_seq_generator`
--

CREATE TABLE IF NOT EXISTS `form_data_details_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=82;

-- --------------------------------------------------------

--
-- Table structure for table `form_field_data_details`
--

CREATE TABLE IF NOT EXISTS `form_field_data_details` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `form_data_id` mediumint(8) NOT NULL,
  `c1` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c2` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c3` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c4` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c5` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c6` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c7` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c8` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c9` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c10` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c11` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c12` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c13` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c14` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c15` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `c16` tinyint(1) DEFAULT NULL,
  `c17` tinyint(1) DEFAULT NULL,
  `c18` tinyint(1) DEFAULT NULL,
  `c19` tinyint(1) DEFAULT NULL,
  `c20` tinyint(1) DEFAULT NULL,
  `c21` date DEFAULT NULL,
  `c22` date DEFAULT NULL,
  `c23` date DEFAULT NULL,
  `c24` date DEFAULT NULL,
  `c25` date DEFAULT NULL,
  `c26` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `c27` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `c28` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `c29` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `c30` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=682;

-- --------------------------------------------------------

--
-- Table structure for table `form_field_master`
--

CREATE TABLE IF NOT EXISTS `form_field_master` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `form_id` smallint(5) NOT NULL,
  `field_id` smallint(5) NOT NULL,
  `field_label` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `field_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `data_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `array_value` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `default_value` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `field_seq` smallint(5) NOT NULL,
  `is_filter` tinyint(1) NOT NULL DEFAULT '0',
  `width_percent` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=682;

-- --------------------------------------------------------

--
-- Table structure for table `gallery_album`
--

CREATE TABLE IF NOT EXISTS `gallery_album` (
  `id` int(11) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `album_id` int(11) NOT NULL,
  `fb_album_id` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `album_title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `album_slug` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `album_description` varchar(5000) COLLATE utf8_unicode_ci NOT NULL,
  `group_type` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `group_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `cover_image` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `is_social_media` tinyint(1) NOT NULL DEFAULT '0',
  `is_website` tinyint(1) NOT NULL DEFAULT '0',
  `seq_number` smallint(5) DEFAULT '0',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `gallery_album_seq_generator`
--

CREATE TABLE IF NOT EXISTS `gallery_album_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `gallery_participant_map`
--

CREATE TABLE IF NOT EXISTS `gallery_participant_map` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `album_id` int(11) NOT NULL,
  `group_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `group_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `group_ids` varchar(2000) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 10:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `gallery_photos`
--

CREATE TABLE IF NOT EXISTS `gallery_photos` (
  `id` int(11) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `album_id` int(11) NOT NULL,
  `photo_id` int(11) NOT NULL,
  `photo_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `photo_path` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `photo_extension` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `photo_mime` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `photo_width` smallint(5) NOT NULL,
  `photo_height` smallint(5) NOT NULL,
  `thumb_width` smallint(5) NOT NULL,
  `thumb_height` smallint(5) NOT NULL,
  `original_width` smallint(5) NOT NULL,
  `original_height` smallint(5) NOT NULL,
  `filesize` int(5) NOT NULL,
  `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `caption` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `alt_text` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `source` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `credit` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `featured` tinyint(1) NOT NULL DEFAULT '0',
  `sequence` int(11) NOT NULL DEFAULT '99999999',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `gallery_photos_seq_generator`
--

CREATE TABLE IF NOT EXISTS `gallery_photos_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `gradedetails_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `gradedetails_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `gradeset_details`
--

CREATE TABLE IF NOT EXISTS `gradeset_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `gradeset_mst_id` smallint(5) NOT NULL,
  `gradeset_dtl_id` mediumint(8) NOT NULL,
  `grade_name` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `grade_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `grade_abbr` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `score_range_from` decimal(5,2) NOT NULL,
  `score_range_to` decimal(5,2) NOT NULL,
  `grade_points` decimal(5,2) NOT NULL,
  `grade_pos` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=1170;

-- --------------------------------------------------------

--
-- Table structure for table `gradeset_master`
--

CREATE TABLE IF NOT EXISTS `gradeset_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `gradeset_mst_id` smallint(5) NOT NULL,
  `curriculum_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `grading_system` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `gradeset_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `gradeset_display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `gradeset_abbr` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `is_system_def` tinyint(1) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=1260;

-- --------------------------------------------------------

--
-- Table structure for table `gradeset_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `gradeset_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `group_dtl_seq_generator`
--

CREATE TABLE IF NOT EXISTS `group_dtl_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `group_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `group_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `holiday_master`
--

CREATE TABLE IF NOT EXISTS `holiday_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `holiday_id` mediumint(8) NOT NULL,
  `holiday_type` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `holiday_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `holiday_desc` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `holiday_weekday` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `holiday_start_date` date DEFAULT NULL,
  `holiday_end_date` date DEFAULT NULL,
  `holiday_duration` tinyint(2) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 21:59:59',
  `created_by` varchar(25) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `holiday_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `holiday_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `house_master`
--

CREATE TABLE IF NOT EXISTS `house_master` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `house_id` smallint(5) NOT NULL,
  `house_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `house_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `house_abbr` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `house_logo_path` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `house_slogan` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `house_color` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 21:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `house_mst_seq_generator`
--

CREATE TABLE IF NOT EXISTS `house_mst_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `inbound_communication_details`
--

CREATE TABLE IF NOT EXISTS `inbound_communication_details` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `inbound_communication_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `communication_created_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `communication_valid_from` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `communication_valid_to` timestamp NULL DEFAULT '2037-12-31 08:59:59',
  `communication_title` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `communication_desc` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `status_desc` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `communication_addressed_to` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-31 08:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `information_category`
--

CREATE TABLE IF NOT EXISTS `information_category` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inst_id` smallint(5) NOT NULL,
  `category_id` smallint(5) NOT NULL,
  `category_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `category_description` varchar(5000) COLLATE utf8_unicode_ci NOT NULL,
  `position` tinyint(2) NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 16:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `information_posts`
--

CREATE TABLE IF NOT EXISTS `information_posts` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `post_id` mediumint(8) NOT NULL,
  `post_category` smallint(5) NOT NULL,
  `post_title` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `post_slug` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `post_content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `positions` tinyint(2) NOT NULL,
  `scope` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `is_facebook` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `level` tinyint(1) NOT NULL,
  `status` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `created_by` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL,
  `is_deleted` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COLLATE=utf16_bin;

-- --------------------------------------------------------

--
-- Table structure for table `information_post_seq_generator`
--

CREATE TABLE IF NOT EXISTS `information_post_seq_generator` (
  `id_num` mediumint(8) NOT NULL,
  `id_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `institute_device_map`
--

CREATE TABLE IF NOT EXISTS `institute_device_map` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `device_id` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `device_type` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `device_attendance_type` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `serial_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 16:29:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AVG_ROW_LENGTH=1;

-- --------------------------------------------------------

--
-- Table structure for table `institution_config`
--

CREATE TABLE IF NOT EXISTS `institution_config` (
  `id` mediumint(8) NOT NULL,
  `ay_id` char(7) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inst_id` smallint(5) NOT NULL,
  `inst_type` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT 'appl_data_master : INSTITUTION_TYPE',
  `edu_board` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `fy_start_date` date NOT NULL,
  `fy_end_date` date NOT NULL,
  `ay_start_date` date NOT NULL,
  `ay_end_date` date NOT NULL,
  `current_ay` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `next_ay` char(7) COLLATE utf8_unicode_ci NOT NULL,
  `attendance_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `first_day_of_week` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `weekly_holiday` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `date_format` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `currency_code` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `class_display_format` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `instruction_mode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `grading_system` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `terms_per_year` int(1) NOT NULL,
  `enrollment_num_gen_mode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `enrollment_num_gen_id` smallint(10) NOT NULL,
  `stud_num_gen_mode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `stud_num_gen_id` smallint(10) NOT NULL,
  `emp_num_gen_mode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `emp_num_gen_id` smallint(10) NOT NULL,
  `admission_num_gen_mode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `admission_num_gen_id` smallint(10) NOT NULL,
  `demand_num_gen_mode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `demand_num_gen_id` smallint(10) NOT NULL,
  `pmt_rcpt_num_gen_mode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `pmt_rcpt_num_gen_id` smallint(10) NOT NULL,
  `lib_book_num_gen_mode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `lib_book_num_gen_id` smallint(10) NOT NULL,
  `web_theme_id` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `app_theme_id` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inst_house_flag` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `records_per_page` tinyint(3) NOT NULL,
  `online_payment_enabled` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `sms_override` tinyint(1) NOT NULL,
  `sms_sender_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `force_stop_notifications` tinyint(1) NOT NULL,
  `inst_sms_master_stop` tinyint(1) NOT NULL DEFAULT '0',
  `app_inactive_days_sms_override` tinyint(3) NOT NULL DEFAULT '7',
  `inst_sms_credit_value` mediumint(5) NOT NULL,
  `is_group_homework` tinyint(1) NOT NULL DEFAULT '0',
  `is_stu_picture_upload` tinyint(1) NOT NULL DEFAULT '1',
  `is_stu_profile_update` tinyint(1) NOT NULL DEFAULT '1',
  `is_emp_picture_upload` tinyint(1) NOT NULL DEFAULT '1',
  `is_emp_profile_update` tinyint(1) NOT NULL DEFAULT '1',
  `stud_name_format` varchar(5) COLLATE utf8_unicode_ci DEFAULT 'FML',
  `stud_name_case` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `emp_name_format` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `emp_name_case` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 10:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `institution_gps_config`
--

CREATE TABLE IF NOT EXISTS `institution_gps_config` (
  `id` mediumint(8) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `provider` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `api_url` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `gps_api_key` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `gps_enabled` tinyint(3) DEFAULT NULL,
  `is_timeframe_common` tinyint(1) DEFAULT '1',
  `pick_start_time` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pick_end_time` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `drop_start_time` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `drop_end_time` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 10:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `institution_master`
--

CREATE TABLE IF NOT EXISTS `institution_master` (
  `id` mediumint(9) NOT NULL,
  `inst_id` smallint(5) NOT NULL,
  `org_id` smallint(5) DEFAULT NULL,
  `inst_name` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inst_display_name` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inst_abbr` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inst_desc` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inst_logo_path` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_line_1` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_line_2` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `locality` int(4) DEFAULT NULL,
  `city` int(4) DEFAULT NULL,
  `state` int(4) DEFAULT NULL,
  `district` int(4) NOT NULL,
  `country` int(4) DEFAULT NULL,
  `pincode` mediumint(7) DEFAULT NULL,
  `registration_num` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `udise_code` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `affiliation_number` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `foundation_date` date DEFAULT NULL,
  `onboarding_date` date DEFAULT NULL,
  `website_address` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email_main` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email_alt` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `facebook_address` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `twitter_address` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `linkedin_address` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `instagram_address` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `contact_salutation_1` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_name_1` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name_1` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `contact_role_1` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `contact_salutation_2` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_name_2` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name_2` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `contact_role_2` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `contact_salutation_3` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_name_3` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name_3` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `contact_role_3` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_country_code_1` tinyint(3) DEFAULT NULL,
  `tel_area_code_1` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_number_1` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_type_1` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_country_code_2` tinyint(3) DEFAULT NULL,
  `tel_area_code_2` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_number_2` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_type_2` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_country_code_3` tinyint(3) DEFAULT NULL,
  `tel_area_code_3` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_number_3` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_type_3` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `referral_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `website_reference` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `valid_from` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `valid_to` timestamp NOT NULL DEFAULT '2037-12-30 10:59:59',
  `created_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'SYSTEM',
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
