delimiter //
CREATE PROCEDURE sp_ins_user_entitlements_json(IN `in_dml_mode` CHAR(1), IN `in_ay_id` CHAR(7), IN `in_inst_id` SMALLINT(5), IN `in_user_type` CHAR(3), IN `in_user_id` MEDIUMINT(8), IN `in_role_id` MEDIUMINT(5),IN `in_insert_row_count` MEDIUMINT(5), IN `in_json_str` TEXT, IN `in_is_active` TINYINT(1), IN `in_created_by` VARCHAR(20), OUT `out_err_code` CHAR(7), OUT `out_err_desc` TEXT)
BEGIN
  declare l_routine_name varchar(50) default 'sp_ins_user_entitlements_json';
  declare l_routine_block varchar(15);
  declare l_err_code char(5) default '00000';
  declare l_err_desc text default '';    	
  declare l_json_str text;
  declare l_json_str_len int;
  declare l_json_substr text;
  declare l_separator_idx int default 1;
  declare l_json_key_val_sep_len tinyint(1) default 3;
  declare l_template_col_name varchar(25);
  declare l_template_col_value text;
  declare l_looping_done int default false;
  declare l_insert_row_count MEDIUMINT(5);
  declare l_single_quot_const varchar(5) default '''';
  declare l_val_sep_const varchar(5) default ',';
  declare l_json_val_dbl_quot_const varchar(5) default '"';
  declare l_val_str_start char(1) default '(';
  declare l_val_str_end char(1) default ')';
  declare l_insert_sql_str text default 'INSERT INTO user_entitlements(ay_id, inst_id, user_id, txn_id, 
    is_create_allowed, is_read_allowed,is_update_allowed, is_delete_allowed, create_auth_type, 
    update_auth_type , delete_auth_type, is_active, created_by, updated_by ,user_type,role_id) VALUES'; 
	declare l_insert_val_str_part text;
	declare l_insert_val_str_full text;
  declare cur_json_template cursor for 
  select col_name from json_template_master 
  where tbl_name = 'user_entitlements' 
  order by col_pos;
  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 
        (in_ay_id, in_inst_id, l_routine_name, l_routine_block, 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;
  set l_insert_row_count = in_insert_row_count;
  set l_json_str = trim(in_json_str);
  set l_json_str_len = length(l_json_str);
  while (l_insert_row_count > 0) do
    set l_routine_block = 'inside while loop';
    #insert into sp_error_log (routine_name, label_name, sql_log)
    #values(l_routine_name, 'Before Open Cursor', l_insert_row_count);
    set l_insert_val_str_part = '';
    open cur_json_template;
    cursor_loop: loop
      fetch cur_json_template into l_template_col_name;
        if l_looping_done then
          leave cursor_loop;
        end if;
      set l_separator_idx = instr(l_json_str, l_template_col_name) + 
        length(l_template_col_name) + l_json_key_val_sep_len;
      set l_json_substr = substring(l_json_str, l_separator_idx, l_json_str_len);      
      set l_separator_idx = instr(l_json_substr, l_json_val_dbl_quot_const);
      #insert into proc_error_log (routine_name, label_name, sql_log)
      #values(l_routine_name, 'Before Case', l_template_col_name);
        set l_template_col_value = substring(l_json_substr, 1, l_separator_idx - 1);             
      set l_template_col_value = concat(l_single_quot_const, l_template_col_value, 
        l_single_quot_const);
      set l_insert_val_str_part = concat_ws(l_val_sep_const, l_insert_val_str_part, 
        l_template_col_value);
      set l_json_str = l_json_substr;
      set l_json_str_len = length(l_json_str);
    end loop;
    set l_insert_row_count = l_insert_row_count - 1;
    set l_looping_done = false;
    if length(trim(l_insert_val_str_full)) > 0 then        
      set l_insert_val_str_full = concat(l_insert_val_str_full, l_val_str_start, 
        l_insert_val_str_part, l_val_str_end, ',');
    else
      set l_insert_val_str_full = concat(l_val_str_start, l_insert_val_str_part, 
        l_val_str_end, ',');
    end if;
    set l_insert_val_str_part = '';
    set l_insert_val_str_full = replace(l_insert_val_str_full, '(,', '(');
    #insert into sp_error_log (routine_name, label_name, sql_log)
    #values(l_routine_name, 'Before Close Cursor', l_insert_val_str_full);
    close cur_json_template;
  end while;
  set l_insert_sql_str = concat(l_insert_sql_str, 
    substring(l_insert_val_str_full, 1, length(l_insert_val_str_full) - 1));
  #insert into proc_error_logs (routine_name, label_name, sql_log)
  #values(l_routine_name, 'Before Close Cursor', l_insert_sql_str);
  case in_dml_mode
    when 'I' THEN
      begin
        start transaction;  
          set l_routine_block = 'before execute immediate';
              call sp_execute_immediate (l_insert_sql_str); 
        commit;
      end;
    when 'U' THEN
      begin
        start transaction;  
          set l_routine_block = 'before soft delete';
         update user_entitlements set valid_to = now(), updated_by = in_created_by, 
            updated_on = now(), is_deleted = 1 
          where inst_id = in_inst_id and user_type=in_user_type  and user_id = in_user_id  AND role_id=in_role_id
            and is_deleted = 0 and is_active=1;
          set l_routine_block = 'before execute immediate';
          call sp_execute_immediate (l_insert_sql_str);        
        commit;
      end;
    end case;
  set out_err_code = l_err_code;
  set out_err_desc = l_err_desc;
END;
//
delimiter ;