ALTER PROCEDURE "DBA"."pr_taotjaot"(in a_komb_id smallint, in a_level smallint, in a_tabel char(13), in a_join char(500), in a_where char(500), in a_meet char(16), in a_kulu_obj char(16), in a_teg_ala char(16), in a_kulu_liik char(16), in a_tulu_liik char(16), in a_projekt char(16), in a_osakond char(16), in a_subjekt char(16), in a_teg_suund char(16), in a_taot char(16))
BEGIN
  declare li_i integer;
  declare li_tr_level smallint;
  declare li_sktrnr integer;
  declare li_mark smallint;
  declare li_row integer;
  declare li_viit integer;
  declare ls_mask char(23);
  declare ls_nimi char(100);
  declare ls_join char(500);
  declare li_nr integer;
  declare ls_kood char(16);
  declare ls_kood1 char(16);
  declare ls_kood2 char(16);
  declare ls_kood3 char(16);
  declare ls_kood4 char(16);
  declare ls_kood5 char(16);
  declare ls_kood6 char(16);
  declare ls_kood7 char(16);
  declare li_pos smallint;
  declare li_pos1 smallint;
  declare li_pos2 smallint;
  declare li_pos3 smallint;
  declare li_pos4 smallint;
  declare li_pos5 smallint;
  declare li_pos6 smallint;
  declare li_pos7 smallint;
  declare ls_where char(500);
  declare li_rows integer;
  declare li_tsykel integer;
  declare li_ntsykel integer;
  declare li_8nr integer;
  declare li_2ntsykel integer;
  declare li_3ntsykel integer;
  declare li_4ntsykel integer;
  declare li_5ntsykel integer;
  declare li_6ntsykel integer;
  declare li_7ntsykel integer;
  declare li_8ntsykel integer;
  declare err_notfound exception for sqlstate value '02000';
  declare c_list dynamic scroll cursor for select if "kp_sktr_mark"."sktrnr" is null and li_sktrnr>0 then 0 else 1 endif,taotlist.taot,taotlist.nimi,taotlist.taotnr from taotlist left outer join "kp_sktr_mark" on "kp_sktr_mark"."sktrnr"=li_sktrnr and "taotlist"."taotnr"="kp_sktr_mark"."xxxnr" order by taotlist.taot asc,taotlist.kell desc;
  select trim(tm_rmpt.mask_taotlist) into ls_mask from tm_rmpt where tm_rmpt."id"=1;
  if ls_mask is null or Length(ls_mask)=0 then
    set ls_mask='@@@@@@@@@@@@@@@@'
  end if;
  set li_pos=0;
  set li_i=locate(ls_mask,'@');
  if li_i>0 then
    pos_loop: while li_i < 25 loop
      if substring(ls_mask,li_i,1)='@' then
        set li_pos=li_pos+1
      else
        if li_pos1 is null then set li_pos1=li_pos
        elseif li_pos2 is null then set li_pos2=li_pos
        elseif li_pos3 is null then set li_pos3=li_pos
        elseif li_pos4 is null then set li_pos4=li_pos
        elseif li_pos5 is null then set li_pos5=li_pos
        elseif li_pos6 is null then set li_pos6=li_pos
        elseif li_pos7 is null then set li_pos7=li_pos
        else leave pos_loop
        end if
      end if;
      set li_i=li_i+1
    end loop pos_loop;
    set li_tr_level=get_pr_tr_level(a_komb_id, a_level+1);
    select if "tr_tryk_taot" is null then 0 else "tr_tryk_taot" endif into li_sktrnr from "pr_komb" where "id"=a_komb_id;
    set ls_join=a_join+' left outer join taotlist on '+a_tabel+'.taotnr=taotlist.taotnr';
    if length(a_where)>0 then
      set a_where=a_where+' and '
    end if;
    set li_row=1;
    -- 1
    open c_list;
    list1_loop: loop
      fetch absolute li_row c_list into li_mark,a_taot,ls_nimi,li_nr;
      if sqlstate = err_notfound then -- kui eof
        leave list1_loop
      end if;
      set li_viit=li_row;
      set ls_kood1=left(a_taot,li_pos1);
      set ls_kood2=left(a_taot,li_pos2);
      if li_mark=1 then
        set ls_where=a_where+' left(taotlist.taot,'+convert(char(2),li_pos1)+')='''+ls_kood1+'''';
        set li_rows=pr_trykkel(a_komb_id,a_level,li_tr_level,a_tabel,ls_join,ls_where,a_meet,a_kulu_obj,a_teg_ala,a_kulu_liik,a_tulu_liik,a_projekt,a_osakond,a_subjekt,a_teg_suund,a_taot,ls_nimi)
      else
        set li_rows=1
      end if;
      set li_tsykel=0;
      set li_ntsykel=0;
      list1alam_loop: loop
        fetch absolute li_row c_list into li_mark,ls_kood,ls_nimi,li_nr;
        if sqlstate = err_notfound or left(ls_kood,li_pos1)<>ls_kood1 then
          leave list1alam_loop
        end if;
        set li_tsykel=li_tsykel+1;
        if left(ls_kood,li_pos2)<>ls_kood2 then
          set li_ntsykel=li_ntsykel+1;
        end if;
        set li_row=li_row+1
      end loop list1alam_loop;
      -- 2
      if li_rows>0 and li_tsykel>1 then
        set li_row=li_viit;
        if li_ntsykel>0 then
          set li_row=li_row+1
        end if;
        set li_2ntsykel=li_ntsykel;
        list2_loop: loop
          fetch absolute li_row c_list into li_mark,a_taot,ls_nimi,li_nr;
          if sqlstate = err_notfound or left(a_taot,li_pos1)<>ls_kood1 then -- kui eof
            leave list2_loop
          end if;
          set li_viit=li_row;
          set ls_kood2=left(a_taot,li_pos2);
          set ls_kood3=left(a_taot,li_pos3);
          if li_2ntsykel>0 and li_mark=1 then
            set ls_where=a_where+' left(taotlist.taot,'+convert(char(2),li_pos2)+')='''+ls_kood2+'''';
            set li_rows=pr_trykkel(a_komb_id,a_level,li_tr_level,a_tabel,ls_join,ls_where,a_meet,a_kulu_obj,a_teg_ala,a_kulu_liik,a_tulu_liik,a_projekt,a_osakond,a_subjekt,a_teg_suund,a_taot,ls_nimi)
          else
            set li_rows=1
          end if;
          set li_tsykel=0;
          set li_ntsykel=0;
          list2alam_loop: loop
            fetch absolute li_row c_list into li_mark,ls_kood,ls_nimi,li_nr;
            if sqlstate = err_notfound or left(ls_kood,li_pos2)<>ls_kood2 then
              leave list2alam_loop
            end if;
            set li_tsykel=li_tsykel+1;
            if left(ls_kood,li_pos3)<>ls_kood3 then
              set li_ntsykel=li_ntsykel+1;
            end if;
            set li_row=li_row+1
          end loop list2alam_loop;
          -- 3
          if li_rows>0 and li_tsykel>1 then
            set li_row=li_viit;
            if li_ntsykel>0 then
              set li_row=li_row+1
            end if;
            set li_3ntsykel=li_ntsykel;
            list3_loop: loop
              fetch absolute li_row c_list into li_mark,a_taot,ls_nimi,li_nr;
              if sqlstate = err_notfound or left(a_taot,li_pos2)<>ls_kood2 then -- kui eof
                leave list3_loop
              end if;
              set li_viit=li_row;
              set ls_kood3=left(a_taot,li_pos3);
              set ls_kood4=left(a_taot,li_pos4);
              if li_3ntsykel>0 and li_mark=1 then
                set ls_where=a_where+' left(taotlist.taot,'+convert(char(2),li_pos3)+')='''+ls_kood3+'''';
                set li_rows=pr_trykkel(a_komb_id,a_level,li_tr_level,a_tabel,ls_join,ls_where,a_meet,a_kulu_obj,a_teg_ala,a_kulu_liik,a_tulu_liik,a_projekt,a_osakond,a_subjekt,a_teg_suund,a_taot,ls_nimi)
              else
                set li_rows=1
              end if;
              set li_tsykel=0;
              set li_ntsykel=0;
              list3alam_loop: loop
                fetch absolute li_row c_list into li_mark,ls_kood,ls_nimi,li_nr;
                if sqlstate = err_notfound or left(ls_kood,li_pos3)<>ls_kood3 then
                  leave list3alam_loop
                end if;
                set li_tsykel=li_tsykel+1;
                if left(ls_kood,li_pos4)<>ls_kood4 then
                  set li_ntsykel=li_ntsykel+1;
                end if;
                set li_row=li_row+1
              end loop list3alam_loop;
              -- 4
              if li_rows>0 and li_tsykel>1 then
                set li_row=li_viit;
                if li_ntsykel>0 then
                  set li_row=li_row+1
                end if;
                set li_4ntsykel=li_ntsykel;
                list4_loop: loop
                  fetch absolute li_row c_list into li_mark,a_taot,ls_nimi,li_nr;
                  if sqlstate = err_notfound or left(a_taot,li_pos3)<>ls_kood3 then -- kui eof
                    leave list4_loop
                  end if;
                  set li_viit=li_row;
                  set ls_kood4=left(a_taot,li_pos4);
                  set ls_kood5=left(a_taot,li_pos5);
                  if li_4ntsykel>0 and li_mark=1 then
                    set ls_where=a_where+' left(taotlist.taot,'+convert(char(2),li_pos4)+')='''+ls_kood4+'''';
                    set li_rows=pr_trykkel(a_komb_id,a_level,li_tr_level,a_tabel,ls_join,ls_where,a_meet,a_kulu_obj,a_teg_ala,a_kulu_liik,a_tulu_liik,a_projekt,a_osakond,a_subjekt,a_teg_suund,a_taot,ls_nimi)
                  else
                    set li_rows=1
                  end if;
                  set li_tsykel=0;
                  set li_ntsykel=0;
                  list4alam_loop: loop
                    fetch absolute li_row c_list into li_mark,ls_kood,ls_nimi,li_nr;
                    if sqlstate = err_notfound or left(ls_kood,li_pos4)<>ls_kood4 then
                      leave list4alam_loop
                    end if;
                    set li_tsykel=li_tsykel+1;
                    if left(ls_kood,li_pos5)<>ls_kood5 then
                      set li_ntsykel=li_ntsykel+1;
                    end if;
                    set li_row=li_row+1
                  end loop list4alam_loop;
                  -- 5
                  if li_rows>0 and li_tsykel>1 then
                    set li_row=li_viit;
                    if li_ntsykel>0 then
                      set li_row=li_row+1
                    end if;
                    set li_5ntsykel=li_ntsykel;
                    list5_loop: loop
                      fetch absolute li_row c_list into li_mark,a_taot,ls_nimi,li_nr;
                      if sqlstate = err_notfound or left(a_taot,li_pos4)<>ls_kood4 then -- kui eof
                        leave list5_loop
                      end if;
                      set li_viit=li_row;
                      set ls_kood5=left(a_taot,li_pos5);
                      set ls_kood6=left(a_taot,li_pos6);
                      if li_5ntsykel>0 and li_mark=1 then
                        set ls_where=a_where+' left(taotlist.taot,'+convert(char(2),li_pos5)+')='''+ls_kood5+'''';
                        set li_rows=pr_trykkel(a_komb_id,a_level,li_tr_level,a_tabel,ls_join,ls_where,a_meet,a_kulu_obj,a_teg_ala,a_kulu_liik,a_tulu_liik,a_projekt,a_osakond,a_subjekt,a_teg_suund,a_taot,ls_nimi)
                      else
                        set li_rows=1
                      end if;
                      set li_tsykel=0;
                      set li_ntsykel=0;
                      list5alam_loop: loop
                        fetch absolute li_row c_list into li_mark,ls_kood,ls_nimi,li_nr;
                        if sqlstate = err_notfound or left(ls_kood,li_pos5)<>ls_kood5 then
                          leave list5alam_loop
                        end if;
                        set li_tsykel=li_tsykel+1;
                        if left(ls_kood,li_pos6)<>ls_kood6 then
                          set li_ntsykel=li_ntsykel+1;
                        end if;
                        set li_row=li_row+1
                      end loop list5alam_loop;
                      -- 6
                      if li_rows>0 and li_tsykel>1 then
                        set li_row=li_viit;
                        if li_ntsykel>0 then
                          set li_row=li_row+1
                        end if;
                        set li_6ntsykel=li_ntsykel;
                        list6_loop: loop
                          fetch absolute li_row c_list into li_mark,a_taot,ls_nimi,li_nr;
                          if sqlstate = err_notfound or left(a_taot,li_pos5)<>ls_kood5 then -- kui eof
                            leave list6_loop
                          end if;
                          set li_viit=li_row;
                          set ls_kood6=left(a_taot,li_pos6);
                          set ls_kood7=left(a_taot,li_pos7);
                          if li_6ntsykel>0 and li_mark=1 then
                            set ls_where=a_where+' left(taotlist.taot,'+convert(char(2),li_pos6)+')='''+ls_kood6+'''';
                            set li_rows=pr_trykkel(a_komb_id,a_level,li_tr_level,a_tabel,ls_join,ls_where,a_meet,a_kulu_obj,a_teg_ala,a_kulu_liik,a_tulu_liik,a_projekt,a_osakond,a_subjekt,a_teg_suund,a_taot,ls_nimi)
                          else
                            set li_rows=1
                          end if;
                          set li_tsykel=0;
                          set li_ntsykel=0;
                          list6alam_loop: loop
                            fetch absolute li_row c_list into li_mark,ls_kood,ls_nimi,li_nr;
                            if sqlstate = err_notfound or left(ls_kood,li_pos6)<>ls_kood6 then
                              leave list6alam_loop
                            end if;
                            set li_tsykel=li_tsykel+1;
                            if left(ls_kood,li_pos7)<>ls_kood7 then
                              set li_ntsykel=li_ntsykel+1;
                            end if;
                            set li_row=li_row+1
                          end loop list6alam_loop;
                          -- 7
                          if li_rows>0 and li_tsykel>1 then
                            set li_row=li_viit;
                            if li_ntsykel>0 then
                              set li_row=li_row+1
                            end if;
                            set li_7ntsykel=li_ntsykel;
                            list7_loop: loop
                              fetch absolute li_row c_list into li_mark,a_taot,ls_nimi,li_nr;
                              if sqlstate = err_notfound or left(a_taot,li_pos6)<>ls_kood6 then -- kui eof
                                leave list7_loop
                              end if;
                              set li_viit=li_row;
                              set ls_kood7=left(a_taot,li_pos7);
                              if li_7ntsykel>0 and li_mark=1 then
                                set ls_where=a_where+' left(taotlist.taot,'+convert(char(2),li_pos7)+')='''+ls_kood7+'''';
                                set li_rows=pr_trykkel(a_komb_id,a_level,li_tr_level,a_tabel,ls_join,ls_where,a_meet,a_kulu_obj,a_teg_ala,a_kulu_liik,a_tulu_liik,a_projekt,a_osakond,a_subjekt,a_teg_suund,a_taot,ls_nimi)
                              else
                                set li_rows=1
                              end if;
                              set li_tsykel=0;
                              set li_ntsykel=0;
                              list7alam_loop: loop
                                fetch absolute li_row c_list into li_mark,ls_kood,ls_nimi,li_nr;
                                if sqlstate = err_notfound or left(ls_kood,li_pos7)<>ls_kood7 then
                                  leave list7alam_loop
                                end if;
                                set li_tsykel=li_tsykel+1;
                                if ls_kood<>a_taot then
                                  set li_ntsykel=li_ntsykel+1;
                                end if;
                                set li_row=li_row+1
                              end loop list7alam_loop;
                              -- 8
                              if li_rows>0 and li_tsykel>1 then
                                set li_row=li_viit;
                                if li_ntsykel>0 then
                                  set li_row=li_row+1
                                end if;
                                set li_8ntsykel=li_ntsykel;
                                list8_loop: loop
                                  fetch absolute li_row c_list into li_mark,a_taot,ls_nimi,li_nr;
                                  if sqlstate = err_notfound or left(a_taot,li_pos7)<>ls_kood7 then -- kui eof
                                    leave list8_loop
                                  end if;
                                  set li_viit=li_row;
                                  if li_8ntsykel>0 and li_mark=1 then
                                    set ls_where=a_where+' taotlist.taot='''+a_taot+'''';
                                    set li_rows=pr_trykkel(a_komb_id,a_level,li_tr_level,a_tabel,ls_join,ls_where,a_meet,a_kulu_obj,a_teg_ala,a_kulu_liik,a_tulu_liik,a_projekt,a_osakond,a_subjekt,a_teg_suund,a_taot,ls_nimi)
                                  else
                                    set li_rows=1
                                  end if;
                                  set li_tsykel=0;
                                  set li_ntsykel=0;
                                  list8alam_loop: loop
                                    fetch absolute li_row c_list into li_mark,ls_kood,ls_nimi,li_8nr;
                                    if sqlstate = err_notfound or ls_kood<>a_taot then
                                      leave list8alam_loop
                                    end if;
                                    if li_8nr <> li_nr then
                                      set li_ntsykel=li_ntsykel+1;
                                    end if;
                                    set li_tsykel=li_tsykel+1;
                                    set li_row=li_row+1
                                  end loop list8alam_loop;
                                  -- nimi 9
                                  if li_rows>0 and li_tsykel>1 then
                                    set li_row=li_viit;
                                    if li_ntsykel>0 then
                                      set li_row=li_row+1
                                    end if;
                                    list9_loop: loop
                                      fetch absolute li_row c_list into li_mark,ls_kood,ls_nimi,li_nr;
                                      if sqlstate = err_notfound or ls_kood<>a_taot then -- kui eof
                                        leave list9_loop
                                      end if;
                                      if li_mark=1 then
                                        set ls_where=a_where+' '+a_tabel+'.taotnr='+convert(char(10),li_nr);
                                        set li_rows=pr_trykkel(a_komb_id,a_level,li_tr_level,a_tabel,ls_join,ls_where,a_meet,a_kulu_obj,a_teg_ala,a_kulu_liik,a_tulu_liik,a_projekt,a_osakond,a_subjekt,a_teg_suund,a_taot,ls_nimi)
                                      end if;
                                      set li_row=li_row+1
                                    end loop list9_loop
                                  end if
                                end loop list8_loop
                              end if
                            end loop list7_loop
                          end if
                        end loop list6_loop
                      end if
                    end loop list5_loop
                  end if
                end loop list4_loop
              end if
            end loop list3_loop
          end if
        end loop list2_loop
      end if
    end loop list1_loop;
    close c_list
  end if
END