ALTER FUNCTION "DBA"."get_tk_tunnus"( in a_asutnr integer, in a_aasta integer, in a_akuu integer)
returns integer
begin
  declare li_tk integer;
  declare ls_iskood char(11);
  declare ls_sugu char(1);
  declare ls_aasta char(4);
  declare li_aasta integer;
  declare li_akuu integer;
  declare li_kuid integer;
  set li_tk = 1;
  select trim(reg_nr) into ls_iskood from asulist where asutnr=a_asutnr;
  if Length(ls_iskood) > 6 then
    case "Left"(ls_iskood,1) when '1' then
      set ls_aasta='18'; set ls_sugu='M' when '2' then
      set ls_aasta='18'; set ls_sugu='N' when '3' then
      set ls_aasta='19'; set ls_sugu='M' when '4' then
      set ls_aasta='19'; set ls_sugu='N' when '5' then
      set ls_aasta='20'; set ls_sugu='M' when '6' then
      set ls_aasta='20'; set ls_sugu='N' when '7' then
      set ls_aasta='21'; set ls_sugu='M' when '8' then
      set ls_aasta='21'; set ls_sugu='N'
    else
      set ls_aasta='19'; set ls_sugu='N'
    end case
    ;
    set temporary option CONVERSION_ERROR = 'OFF';
    set li_aasta=convert(integer,ls_aasta+substring(ls_iskood,2,2));
    if li_aasta is null or li_aasta < 1800 or li_aasta > 2999 then
      set li_aasta=1900
    end if;
    set li_akuu=convert(integer,substring(ls_iskood,4,2));
    if li_akuu is null or li_akuu < 1 or li_akuu > 12 then
      set li_akuu=1
    end if;
    set temporary option CONVERSION_ERROR = 'ON';
    set li_kuid = (12 * (a_aasta - li_aasta - 1)) + a_akuu + (12 - li_akuu);
    if li_kuid > 780 or (li_aasta < 1953 and li_kuid > 756) or (ls_sugu='N' and li_aasta < 1944) then
      set li_tk = 0;
    end if;
    if li_tk = 1 then
      if li_aasta <= 1960 then
        case li_aasta when 1953 then
          if li_kuid > 756 then set li_tk = 0 end if when 1954 then
          if li_kuid > 759 then set li_tk = 0 end if when 1955 then
          if li_kuid > 762 then set li_tk = 0 end if when 1956 then
          if li_kuid > 765 then set li_tk = 0 end if when 1957 then
          if li_kuid > 768 then set li_tk = 0 end if when 1958 then
          if li_kuid > 771 then set li_tk = 0 end if when 1959 then
          if li_kuid > 774 then set li_tk = 0 end if when 1960 then
          if li_kuid > 777 then set li_tk = 0 end if
        end case
      end if
    end if;
    if li_tk = 1 then
      if ls_sugu='N' and li_aasta <= 1952 then
        case li_aasta when 1944 then
          if li_kuid > 702 then set li_tk = 0 end if when 1945 then
          if li_kuid > 708 then set li_tk = 0 end if when 1946 then
          if li_kuid > 714 then set li_tk = 0 end if when 1947 then
          if li_kuid > 720 then set li_tk = 0 end if when 1948 then
          if li_kuid > 726 then set li_tk = 0 end if when 1949 then
          if li_kuid > 732 then set li_tk = 0 end if when 1950 then
          if li_kuid > 738 then set li_tk = 0 end if when 1951 then
          if li_kuid > 744 then set li_tk = 0 end if when 1952 then
          if li_kuid > 750 then set li_tk = 0 end if
        end case
      end if
    end if
  end if;
  return li_tk;
end