delimiter //

CREATE PROCEDURE sp_ins_class_master(IN `in_dml_mode` CHAR(1), IN `in_ay_id` CHAR(7), IN `in_inst_id` SMALLINT(5), IN `in_standard_id` CHAR(2), IN `in_division_id` CHAR(1), IN `in_class_id` CHAR(8), IN `in_curriculum_type` VARCHAR(10), IN `in_grading_system` VARCHAR(4), IN `in_gradeset_mst_id` SMALLINT(5), IN `in_emp_id` MEDIUMINT(8), IN `in_class_name` VARCHAR(30), IN `in_class_abbr` VARCHAR(15), IN `in_class_desc` VARCHAR(50), IN `in_shift_type` VARCHAR(15), IN `in_attendance_type` VARCHAR(15), IN `in_is_active` TINYINT(1), IN `in_created_by` VARCHAR(20),IN `in_stream_id` VARCHAR(10), OUT `out_err_code` CHAR(5), OUT `out_err_desc` TEXT)
  NO SQL
begin
   declare l_routine_name varchar(50) default 'sp_ins_class_master';
  declare l_err_code char(5) default '00000';
  declare l_err_desc text default '';    	
  declare l_routine_block varchar(15);
  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';
            insert into class_master (ay_id, inst_id,class_id,standard_id, division_id,curriculum_type,
              grading_system,gradeset_mst_id,	emp_id,class_name,class_abbr,class_desc,attendance_type,shift_type,
              is_active,created_by, updated_by,stream_id) 							
            values(in_ay_id, in_inst_id, in_class_id, in_standard_id, in_division_id,in_curriculum_type,
              in_grading_system,in_gradeset_mst_id, in_emp_id,in_class_name,in_class_abbr ,in_class_desc,
              in_attendance_type,in_shift_type,in_is_active,in_created_by, in_created_by, in_stream_id);
          end;    
  	   when 'U' then
          set l_routine_block = 'update';
          begin
          	update class_master set valid_to = NOW(), updated_by = in_created_by, 
              updated_on = NOW(), is_deleted = 1 
            where class_id = in_class_id and is_deleted = 0 and ay_id = in_ay_id 
            	and inst_id = in_inst_id;
            insert into class_master (ay_id, inst_id,class_id,standard_id, division_id,curriculum_type,
              grading_system,gradeset_mst_id,	emp_id,class_name,class_abbr,class_desc,attendance_type,shift_type,
              is_active,created_by, updated_by, stream_id) 							
            values(in_ay_id, in_inst_id, in_class_id, in_standard_id, in_division_id,in_curriculum_type,
              in_grading_system,in_gradeset_mst_id, in_emp_id,in_class_name,in_class_abbr ,in_class_desc,
              in_attendance_type,in_shift_type,in_is_active,in_created_by, in_created_by, in_stream_id);
          end;    
          
           when 'D' then
        set l_routine_block = 'delete';
        begin
        	update class_master set valid_to = NOW(), updated_by = in_created_by, 
            updated_on = NOW(), is_deleted = 1 
          where class_id = in_class_id and is_deleted = 0 and ay_id = in_ay_id 
          	and inst_id = in_inst_id and curriculum_type=in_curriculum_type;
       end;  
       
      end case;
    commit;
     set out_err_code = l_err_code;
    set out_err_desc = l_err_desc;
end;
//
delimiter ;