delimiter // CREATE PROCEDURE sp_ins_data_clonning BEGIN declare l_routine_name varchar(50) default 'sp_ins_data_clonning'; 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_inst_id smallint(5) default 0; declare l_delete_flag tinyint(1) default 0; declare l_records smallint(5) default null; declare l_user_id mediumint(8); declare l_old_user_id mediumint(8); declare l_looping_libraryModule_user_done int default false; declare cur_institution_mst cursor for select inst_id,is_deleted from institution_master where inst_id NOT IN (-1,0) and is_active=1 and is_deleted=0 group by inst_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; insert into db_error_log(ay_id, inst_id, routine_name, routine_block, err_code, err_desc, err_type, err_severity, created_by) values ("", l_inst_id, l_routine_name, 'SQL Exception Handler', l_err_code, l_err_desc, 'ERROR', 0, ''); set out_err_code = l_err_code; set out_err_desc = l_err_desc; end; open cur_institution_mst; cursor_loop: loop fetch cur_institution_mst into l_inst_id, l_delete_flag; if l_looping_done then leave cursor_loop; end if; start transaction; case in_dml_mode when 'I' then begin set l_routine_block = 'insert'; -- INSERT INTO user_groups_master(inst_id, grp_name, grp_type, grp_display_name, grp_icon, -- grp_class, grp_order) -- select l_inst_id, grp_name, grp_type, grp_display_name, grp_icon, grp_class, grp_order -- from user_groups_master where inst_id = 0 -- and is_deleted = 0 and is_active=1 and grp_type IN('CLASS_LIST'); -- select user_id into l_user_id from user_master where inst_id=l_inst_id and role_id=11 -- or inst_id=l_inst_id and role_id=10 and is_active=1 and is_deleted=0; -- -- select user_id into l_old_user_id -- FROM module_user_master where inst_id=l_inst_id and is_active=1 -- and is_deleted=0 and user_id=l_user_id; -- if(l_old_user_id ='')then -- insert into module_user_master (ay_id, inst_id,user_id, module_acct_id, -- module_name,is_create_allowed,is_read_allowed,is_update_allowed, -- is_delete_allowed, -- created_by,updated_by,is_active) -- values('2018-19', l_inst_id, l_user_id, '1', 'library', -- '1','1','1','1', 'system', 'system',1); -- end if; -- INSERT INTO appl_data_master(inst_id, data_type, data_key, data_value, -- data_desc, data_abbr, data_pos, is_system_def, is_editable, is_validations, -- is_notification, is_active, is_deleted) -- VALUES (l_inst_id, 'GLOBAL_VARIABLES', 'MIGRATION_DONE_FOR_YEAR', '', -- 'MIGRATION_DONE_FOR_YEAR', '', '21', 0, 1, 0, -- 0, 1, 0); #notification_config data INSERT INTO notification_config(ay_id, inst_id, notif_template_id, notif_name, notif_desc, txn_id, txn_category, txn_sub_category, is_sms, sms_template_id, is_mail, mail_template_id, is_app, app_template_id, is_web, web_template_id, priority, handler_method, is_auto_mode, created_by, updated_by) select '', l_inst_id, notif_template_id, notif_name, notif_desc, txn_id, txn_category, txn_sub_category, is_sms, sms_template_id, is_mail, mail_template_id, is_app, app_template_id, is_web, web_template_id, priority, handler_method, is_auto_mode, created_by, created_by from notification_config where ay_id = '*******' and inst_id = 0 and is_deleted = 0 and is_active=1 and notif_template_id IN(153); #notif_app_template_master data INSERT INTO notif_app_template_master(app_template_id, ay_id, inst_id, push_message, push_title, push_subtitle, push_action, push_vibrate, push_sound, push_large_icon, push_small_icon, push_registration_id, push_params, push_opt1, push_opt2, recipient_type, created_by, updated_by) select app_template_id, '', l_inst_id,push_message, push_title, push_subtitle,push_action, push_vibrate, push_sound, push_large_icon, push_small_icon, push_registration_id, push_params, push_opt1, push_opt2, recipient_type, created_by, created_by from notif_app_template_master where ay_id = '*******' and inst_id = 0 and is_deleted = 0 and is_active=1 and app_template_id IN(53); #notif_web_template_master data INSERT INTO notif_web_template_master(web_template_id, ay_id, inst_id, web_from, web_subject, web_params, web_description, web_icon, web_action_link, web_opt1, web_opt2, recipient_type, created_by, updated_by) select web_template_id, '', l_inst_id,web_from, web_subject, web_params, web_description, web_icon, web_action_link, web_opt1, web_opt2, recipient_type, created_by, created_by from notif_web_template_master where ay_id = '*******' and inst_id = 0 and is_deleted = 0 and is_active=1 and web_template_id IN(53); #notif_email_template_master data INSERT INTO notif_email_template_master(mail_template_id, ay_id, inst_id, email_subject, email_from, email_cc_address, email_bcc_address, email_replyto, email_body, email_params, email_attach_path1, email_attach_path2, email_opt1, email_opt2, recipient_type, created_by, updated_by) select mail_template_id, '', l_inst_id,email_subject, email_from, email_cc_address, email_bcc_address, email_replyto, email_body, email_params, email_attach_path1, email_attach_path2, email_opt1, email_opt2, recipient_type, created_by, created_by from notif_email_template_master where ay_id = '*******' and inst_id = 0 and is_deleted = 0 and is_active=1 and mail_template_id IN(53); #11 notif_sms_template_master data INSERT INTO notif_sms_template_master(sms_template_id, ay_id, inst_id, sms_from_mobile_no, sms_body, sms_params, sms_opt1, sms_opt2, recipient_type, created_by, updated_by) select sms_template_id, '', l_inst_id,sms_from_mobile_no, sms_body, sms_params, sms_opt1, sms_opt2, recipient_type, created_by, created_by from notif_sms_template_master where ay_id = '*******' and inst_id = 0 and is_deleted = 0 and is_active=1 and sms_template_id IN(53); -- INSERT INTO setup_tress_master( inst_id, step_id,status,created_by, created_on) -- select l_inst_id, step_id, status, created_by, created_on from setup_tress_master where inst_id = 0 ; -- if(l_inst_id > 1081)then -- INSERT INTO sub_module_notif_config(inst_id, module_name, module_display_name, -- role_id, role_users, send, created_by, updated_by) -- select l_inst_id,module_name, module_display_name, -- role_id, role_users, send, created_by, created_by -- from sub_module_notif_config where inst_id = 0 -- and is_deleted = 0 and is_active=1 ; -- end if; -- INSERT INTO fin_accounts(inst_id, fin_acct_id,sequence_entity_id, -- sequence_entity_mode,acct_name,acct_display_name, -- acct_short_name,is_default,created_by, created_on) -- select l_inst_id, fin_acct_id, sequence_entity_id, -- sequence_entity_mode,acct_name,acct_display_name, -- acct_short_name,is_default, created_by, created_on -- from fin_accounts where inst_id = 0 ; -- INSERT INTO role_master (ay_id, inst_id, role_id, role_type, parent_id, role_level, -- role_name, role_display_name, role_desc, role_abbr, is_default, inst_alerts_allowed, -- valid_from, valid_to, created_by, created_on, updated_by, updated_on, is_active, is_deleted) -- select '', l_inst_id, role_id, role_type, parent_id, role_level, -- role_name, role_display_name, role_desc, role_abbr, is_default, inst_alerts_allowed, -- valid_from, valid_to, created_by, created_on, updated_by, updated_on, is_active, is_deleted -- from role_master where inst_id = 0 and ay_id='*******' -- and is_deleted = 0 and is_active=1 and role_id IN(50,51,52,53); -- if(l_inst_id < 1123)then -- INSERT INTO role_master (ay_id, inst_id, role_id, role_type, parent_id, role_level, -- role_name, role_display_name, role_desc, role_abbr, is_default, inst_alerts_allowed, -- valid_from, valid_to, created_by, created_on, updated_by, updated_on, is_active, is_deleted) -- select '', l_inst_id, role_id, role_type, parent_id, role_level, -- role_name, role_display_name, role_desc, role_abbr, is_default, inst_alerts_allowed, -- valid_from, valid_to, created_by, created_on, updated_by, updated_on, is_active, is_deleted -- from role_master where inst_id = 0 and ay_id='*******' -- and is_deleted = 0 and is_active=1 and role_id IN(32); -- end if; -- INSERT INTO role_master (ay_id, inst_id, role_id, role_type, parent_id, role_level, -- role_name, role_display_name, role_desc, role_abbr, is_default, inst_alerts_allowed, -- valid_from, valid_to, created_by, created_on, updated_by, updated_on, is_active, is_deleted) -- select '', l_inst_id, role_id, role_type, parent_id, role_level, -- role_name, role_display_name, role_desc, role_abbr, is_default, inst_alerts_allowed, -- valid_from, valid_to, created_by, created_on, updated_by, updated_on, is_active, is_deleted -- from role_master where inst_id = 0 and ay_id='*******' -- and is_deleted = 0 and is_active=1 and role_id IN(33,34,35,36,37,38,39,40); -- INSERT INTO user_groups_master (id, inst_id, grp_name, grp_type, grp_display_name, grp_icon, grp_class, grp_order, group_module, valid_from, valid_to, created_by, created_on, updated_by, updated_on, is_active, is_deleted) VALUES -- ('', l_inst_id, 'STU', 'HOSTEL', 'Hostel', '', '', 16, '', 'now()', 'now()', 'SYSTEM', '2016-06-23 00:32:36', 'SYSTEM', 'now()', 1, 0), -- ('', l_inst_id, 'STU', 'NON_HOSTEL', 'Non Hostel', '', '', 17, '', 'now()', 'now()', 'SYSTEM', '2016-06-23 00:32:36', 'SYSTEM', 'now()', 1, 0), -- ('', l_inst_id, 'STU', 'TRANSPORT', 'Transport', '', '', 18, '', 'now()', 'now()', 'SYSTEM', '2016-06-23 00:32:36', 'SYSTEM', 'now()', 1, 0), -- ('', l_inst_id, 'TRANSPORT', 'ROUTE', 'Route Name', '', '', 37, '', 'now()', '2037-12-31 15:59:59', '0', 'now()', '0', 'now()', 1, 0); -- INSERT INTO appl_data_master(inst_id, data_type, data_key, data_value, -- data_desc, data_abbr, data_pos, is_system_def, is_editable, is_validations, -- is_notification, is_active, is_deleted) -- VALUES (l_inst_id, 'GLOBAL_VARIABLES', 'school_code', '', -- 'school_code', '', '23', 0, 1, 0, -- 0, 1, 0); -- INSERT INTO appl_data_master(inst_id, data_type, data_key, data_value, -- data_desc, data_abbr, data_pos, is_system_def, is_editable, is_validations, -- is_notification, is_active, is_deleted) -- VALUES (l_inst_id, 'SUB_MODULE_NOTIFICATION_CONFIG', 'CLASS_ABSENT_PERCENTAGE', '1', -- 'CLASS_ABSENT_PERCENTAGE', 'CLASS_ABSENT_PERCENTAGE', '7', 1, 1, 0, -- 0, 1, 0); -- -- INSERT INTO appl_data_master(inst_id, data_type, data_key, data_value, -- data_desc, data_abbr, data_pos, is_system_def, is_editable, is_validations, -- is_notification, is_active, is_deleted) -- VALUES (l_inst_id, 'SUB_MODULE_NOTIFICATION_CONFIG', 'CUSTOM_NOTIFICATION', '1', -- 'CUSTOM_NOTIFICATION', 'CUSTOM_NOTIFICATION', '8', 1, 1, 0, -- 0, 1, 0); -- INSERT INTO sub_module_notif_config(inst_id, module_name, module_display_name, -- role_id, role_users, send,is_active, is_deleted) -- VALUES (l_inst_id, 'CLASS_ABSENT_PERCENTAGE', 'Class absenty percentage', -- 11, '', 0,1, 0); -- -- INSERT INTO sub_module_notif_config(inst_id, module_name, module_display_name, -- role_id, role_users, send,is_active, is_deleted) -- VALUES (l_inst_id, 'CUSTOM_NOTIFICATION', 'Custom Notification', -- 11, '', 0,1, 0); -- INSERT INTO event_calendar_category(inst_id, event_cat, cat_name, cat_icon, cat_class, cat_order) -- select l_inst_id, event_cat, cat_name, cat_icon, cat_class, cat_order -- from event_calendar_category where inst_id = 0 -- and is_deleted = 0 and is_active=1 and event_cat IN('VIRTUAL_CLASSROOM'); -- -- INSERT INTO event_calendar_subcategory(inst_id, event_cat, event_sub_cat, subcat_name, subcat_icon, -- subcat_class, subcat_order) -- select l_inst_id, event_cat, event_sub_cat, subcat_name, subcat_icon, subcat_class, subcat_order -- from event_calendar_subcategory where inst_id = 0 -- and is_deleted = 0 and is_active=1 and event_cat IN('VIRTUAL_CLASSROOM'); -- select COUNT(1) into l_records from admission_stage_master where inst_id=l_inst_id and -- is_active=1 and is_deleted=0; -- -- if(l_records > 0)then -- INSERT INTO admission_stage_master( ay_id, inst_id, stage_id, stage_name, stage_seq, stage_desc, -- stage_abbr, stage_icon, stage_color, is_visible, created_by) -- select '2020-21', l_inst_id, stage_id, stage_name, stage_seq, stage_desc, stage_abbr, -- stage_icon, stage_color, is_visible, created_by -- from admission_stage_master where inst_id = 0 -- and is_deleted = 0 and is_active=1 and stage_id IN('8'); -- end if; end; end case; end loop; commit; set out_err_code = l_err_code; set out_err_desc = l_err_desc; END; // delimiter ;