DELIMITER $$
CREATE PROCEDURE `sp_ins_trans_stop_master`(IN `in_dml_mode` CHAR(1), IN `in_ay_id` CHAR(7), IN `in_inst_id` SMALLINT(5), IN `in_stop_id` MEDIUMINT(8), IN `in_stop_name` VARCHAR(150), IN `in_stop_address` VARCHAR(100), IN `in_description` VARCHAR(100), IN `in_longitude` MEDIUMINT(8), IN `in_latitude` MEDIUMINT(8), IN `in_pickup_charges` DOUBLE(10,2), IN `in_pickup_charges_tax` DOUBLE(10,2), IN `in_drop_charges` DOUBLE(10,2), IN `in_drop_charges_tax` DOUBLE(10,2), IN `in_both_way_charges` DOUBLE(10,2), IN `in_both_way_charges_tax` DOUBLE(10,2), IN `in_total_fee_amt` DOUBLE(10,2), IN `in_distance_from_school` DOUBLE(10,2), IN `in_created_by` VARCHAR(20), IN `in_is_active` TINYINT(1), OUT `out_err_code` CHAR(5), OUT `out_err_desc` TEXT, OUT `out_stop_id` TEXT)
    NO SQL
begin

  declare l_routine_name varchar(50) default 'sp_ins_trans_stop_master';
  declare l_err_code char(5) default '00000';
  declare l_err_desc text default '';    	
  declare l_routine_block varchar(15);
  declare l_stop_id  mediumint(8);
    	
  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_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_stop_id  = fn_sequence_generator('TRANS_STOP_MASTER');

          set out_stop_id=l_stop_id;

           insert into  trans_stop_master(
             inst_id, stop_id, stop_name, stop_address, description, longitude, latitude,distance_from_school, created_by,updated_by)  
            values(
                 in_inst_id, l_stop_id, in_stop_name, in_stop_address, in_description, in_longitude, in_latitude, in_distance_from_school,
              in_created_by,in_created_by);

            INSERT INTO trans_fee_struct_master(ay_id, inst_id, stop_id, pickup_charges, pickup_charges_tax, 
              drop_charges, drop_charges_tax, both_way_charges, both_way_charges_tax, total_fee_amt,
              created_by, updated_by, is_active)
            VALUES (in_ay_id, in_inst_id, l_stop_id, in_pickup_charges, in_pickup_charges_tax, 
              in_drop_charges, in_drop_charges_tax, in_both_way_charges, in_both_way_charges_tax, in_total_fee_amt, 
              in_created_by, in_created_by, in_is_active);
            
          end;    
  
  	    when 'U' then
      set l_routine_block = 'update';      
          begin
          	update  trans_stop_master set valid_to = NOW(), updated_by = in_created_by, 
              updated_on = NOW(), is_deleted = 1 
            where stop_id=in_stop_id  
              and is_deleted = 0 
            	and inst_id = in_inst_id;

          set out_stop_id=in_stop_id;

           insert into  trans_stop_master(
             inst_id, stop_id, stop_name, stop_address, description, longitude, latitude, distance_from_school,
              created_by,updated_by)  
            values(
                 in_inst_id, in_stop_id, in_stop_name, in_stop_address, in_description, in_longitude, in_latitude, 
              in_distance_from_school,in_created_by,in_created_by);

          update  trans_fee_struct_master set valid_to = NOW(), updated_by = in_created_by, 
              updated_on = NOW(), is_deleted = 1 
            where stop_id=in_stop_id  
              and is_deleted = 0 
            	and inst_id = in_inst_id and ay_id=in_ay_id;

          INSERT INTO trans_fee_struct_master(ay_id, inst_id, stop_id, pickup_charges, pickup_charges_tax, 
              drop_charges, drop_charges_tax, both_way_charges, both_way_charges_tax, total_fee_amt, 
              created_by, updated_by, is_active)
            VALUES (in_ay_id, in_inst_id, in_stop_id, in_pickup_charges, in_pickup_charges_tax, 
              in_drop_charges, in_drop_charges_tax, in_both_way_charges, in_both_way_charges_tax, in_total_fee_amt, 
              in_created_by, in_created_by, in_is_active
              );
            


          end;    
      

    when 'D' then
      set l_routine_block = 'delete';      
          begin
          	update  trans_stop_master set valid_to = NOW(), updated_by = in_created_by, 
              updated_on = NOW(), is_deleted = 1 
            where stop_id=in_stop_id  
              and is_deleted = 0 
            	and inst_id = in_inst_id;

          set out_stop_id=in_stop_id;


          update  trans_fee_struct_master set valid_to = NOW(), updated_by = in_created_by, 
              updated_on = NOW(), is_deleted = 1 
            where stop_id=in_stop_id  
              and is_deleted = 0 
            	and inst_id = in_inst_id;


          end;    
      end case;
    
    
    commit;
     set out_err_code = l_err_code;
      set out_err_desc = l_err_desc;

end$$
DELIMITER ;