CREATE FUNCTION "DBA"."get_ea_automkomb"( in a_id integer default 100001 )
RETURNS INTEGER
BEGIN
  declare li_komb integer;
  declare li_id_lkd integer;
  declare li_id_col integer;
  declare li_id_col1 integer;
  declare li_id_col2 integer;
  select "id" into li_komb from ea_komb where "id"=a_id;
  if li_komb is null then
    select "id" into li_id_lkd from ea_lkd_def where "id"=100001;
    if li_id_lkd is null then
      insert into ea_lkd_def ("id","nimi") values (100001,'Automaatne lehekülg');
      -- column 1 Eelarve
      set li_id_col1=null;
      select first "id" into li_id_col1 from ea_col_def where tyyp=1 and objnr is null and varper=1 and eaobj=0;
      if li_id_col1 is null then
        insert into ea_col_def(nimi,kirjeldus,tyyp,peralg,perlop,varaasta,varper,eaobj) values ('Eelarve','Lisatud automaatselt',1,ymd(2020,1,1),ymd(2020,12,31),0,1,0);
        select first "id" into li_id_col1 from ea_col_def where tyyp=1 and objnr is null and varper=1 and eaobj=0;
      end if;
      if li_id_col1 is not null then
        insert into ea_lkd_col(id_lkd,id_col) values (100001,li_id_col1)
      end if;
      -- column 2 Tekkepohine kulu
      set li_id_col2=null;
      select first "id" into li_id_col2 from ea_col_def where tyyp=3 and objnr is null and varper=1 and eaobj=0;
      if li_id_col2 is null then
        insert into ea_col_def(nimi,kirjeldus,tyyp,peralg,perlop,varaasta,varper,eaobj) values ('Tekkepõhine kulu','Lisatud automaatselt',3,ymd(2020,1,1),ymd(2020,12,31),0,1,0);
        select first "id" into li_id_col2 from ea_col_def where tyyp=3 and objnr is null and varper=1 and eaobj=0;
      end if;
      if li_id_col2 is not null then
        insert into ea_lkd_col(id_lkd,id_col) values (100001,li_id_col2)
      end if;
      -- column 3 Jaak
      set li_id_col=null;
      select first "id" into li_id_col from ea_col_def where tyyp=5 and valemnr=201 and id_col_a=li_id_col1 and id_col_b=li_id_col2;
      if li_id_col is null then
        insert into ea_col_def(nimi,kirjeldus,tyyp,valemnr,id_col_a,id_col_b,x,y,z) values ('Jääk','Lisatud automaatselt',5,201,li_id_col1,li_id_col2,0,0,0);
        select first "id" into li_id_col from ea_col_def where tyyp=5 and valemnr=201 and id_col_a=li_id_col1 and id_col_b=li_id_col2;
      end if;
      if li_id_col is not null then
        insert into ea_lkd_col(id_lkd,id_col) values (100001,li_id_col)
      end if;
      -- column 4 Protsent
      set li_id_col=null;
      select first "id" into li_id_col from ea_col_def where tyyp=5 and valemnr=202 and id_col_a=li_id_col2 and id_col_b=li_id_col1;
      if li_id_col is null then
        insert into ea_col_def(nimi,kirjeldus,tyyp,valemnr,id_col_a,id_col_b,x,y,z) values ('Protsent','Lisatud automaatselt',5,202,li_id_col2,li_id_col1,100,0,0);
        select first "id" into li_id_col from ea_col_def where tyyp=5 and valemnr=202 and id_col_a=li_id_col2 and id_col_b=li_id_col1;
      end if;
      if li_id_col is not null then
        insert into ea_lkd_col(id_lkd,id_col) values (100001,li_id_col)
      end if;
    end if;
    insert into ea_komb("id",id_lkd,nimi,tr_level1,tr_level2,veergarv) values (a_id,100001,'Automaatne kombinatsioon',3,5,4);
    select "id" into li_komb from ea_komb where "id"=a_id;
  end if;
  return li_komb;
END