ALTER PROCEDURE "DBA"."dk_select_dok"( in a_skoop integer,in a_objnr integer,in a_paring integer,in a_peralg date,in a_perlop date, in a_dk_per smallint default 0, in a_panknr integer default null, in a_contractnr integer default null, in a_kombed smallint default 0 )
BEGIN
  declare li_paring integer;
  delete from "gl_dk_kanne";
  if a_paring=0 then
    if a_panknr is null and a_contractnr is null then
      insert into "gl_dk_kanne" ("objnr","arvekuup","arvenr","asutnr","saajanr","servicenr","kombid","summa","summa2")
        select "dk_kanne"."objnr",
               "dk_kanne"."arvekuup",
               "dk_kanne"."arvenr",
               "dk_kanne"."asutnr",
               max("dk_kanne"."saajanr"),
               max("dk_kanne"."servicenr"),
               max("dk_kanne"."kombid"),
               0, 0
          from "dk_kanne" key left outer join "dk_obj"
          where ("dk_kanne"."objnr" = a_objnr or a_objnr = 0) and
                ("dk_obj"."skoop" = a_skoop or a_skoop = 0) and
                dk_kanne.asutnr > 0 and
                (a_kombed=0 or dk_kanne.kombid is not null) and
                ((a_dk_per=0 and dk_kanne.kuup >= a_peralg and dk_kanne.kuup <= a_perlop) or (a_dk_per=1 and dk_kanne.arvekuup >= a_peralg and dk_kanne.arvekuup <= a_perlop) or (a_dk_per=2 and dk_kanne.taskuup >= a_peralg and dk_kanne.taskuup <= a_perlop))
          group by "dk_kanne"."objnr","dk_kanne"."arvekuup","dk_kanne"."arvenr","dk_kanne"."asutnr"
    else
      insert into "gl_dk_kanne" ("objnr","arvekuup","arvenr","asutnr","saajanr","servicenr","kombid","summa","summa2")
        select "dk_kanne"."objnr",
               "dk_kanne"."arvekuup",
               "dk_kanne"."arvenr",
               "dk_kanne"."asutnr",
               max("dk_kanne"."saajanr"),
               max("dk_kanne"."servicenr"),
               max("dk_kanne"."kombid"),
               0, 0
          from "dk_kanne" key left outer join "dk_obj"
                          left outer join "otsek_service" on "dk_kanne"."servicenr"="otsek_service"."servicenr"
                          left outer join "otsek_contract" on "otsek_service"."contractnr"="otsek_contract"."contractnr"
          where ("dk_kanne"."objnr" = a_objnr or a_objnr = 0) and
                ("dk_obj"."skoop" = a_skoop or a_skoop = 0) and
                dk_kanne.asutnr > 0 and
                (a_kombed=0 or dk_kanne.kombid is not null) and
                ((a_dk_per=0 and dk_kanne.kuup >= a_peralg and dk_kanne.kuup <= a_perlop) or (a_dk_per=1 and dk_kanne.arvekuup >= a_peralg and dk_kanne.arvekuup <= a_perlop) or (a_dk_per=2 and dk_kanne.taskuup >= a_peralg and dk_kanne.taskuup <= a_perlop)) and
                (a_panknr is null or "otsek_contract"."panknr"=a_panknr) and
                (a_contractnr is null or a_contractnr=0 or "otsek_service"."contractnr"=a_contractnr) and
                ((a_panknr is null and a_contractnr is null) or ("dk_kanne"."taskuup">="otsek_service"."akuup" and ("otsek_service"."lkuup" is null or "dk_kanne"."taskuup"<="otsek_service"."lkuup")))
          group by "dk_kanne"."objnr","dk_kanne"."arvekuup","dk_kanne"."arvenr","dk_kanne"."asutnr"
    end if
  else
    set li_paring=set_gl_1(a_paring);
    insert into "gl_dk_kanne" ("objnr","arvekuup","arvenr","asutnr","saajanr","servicenr","kombid","summa","summa2")
      select "dk_kanne"."objnr",
             "dk_kanne"."arvekuup",
             "dk_kanne"."arvenr",
             "dk_kanne"."asutnr",
             max("dk_kanne"."saajanr"),
             max("dk_kanne"."servicenr"),
             max("dk_kanne"."kombid"),
             0, 0
        from "dk_kanne" left outer join "gl_1" on("dk_kanne"."id" = "gl_1"."id") and("dk_kanne"."kuup" = "gl_1"."kuup")
                        key left outer join "dk_obj"
                        left outer join "otsek_service" on "dk_kanne"."servicenr"="otsek_service"."servicenr"
                        left outer join "otsek_contract" on "otsek_service"."contractnr"="otsek_contract"."contractnr"
        where (("dk_kanne"."id" = "gl_1"."id") and("dk_kanne"."kuup" = "gl_1"."kuup") or a_paring = 0) and
              ("dk_kanne"."objnr" = a_objnr or a_objnr = 0) and
              ("dk_obj"."skoop" = a_skoop or a_skoop = 0) and
              dk_kanne.asutnr > 0 and
              (a_kombed=0 or dk_kanne.kombid is not null) and
              ((a_dk_per=0 and dk_kanne.kuup >= a_peralg and dk_kanne.kuup <= a_perlop) or (a_dk_per=1 and dk_kanne.arvekuup >= a_peralg and dk_kanne.arvekuup <= a_perlop) or (a_dk_per=2 and dk_kanne.taskuup >= a_peralg and dk_kanne.taskuup <= a_perlop)) and
              (a_panknr is null or "otsek_contract"."panknr"=a_panknr) and
              (a_contractnr is null or a_contractnr=0 or "otsek_service"."contractnr"=a_contractnr) and
              ((a_panknr is null and a_contractnr is null) or ("dk_kanne"."taskuup">="otsek_service"."akuup" and ("otsek_service"."lkuup" is null or "dk_kanne"."taskuup"<="otsek_service"."lkuup")))
        group by "dk_kanne"."objnr","dk_kanne"."arvekuup","dk_kanne"."arvenr","dk_kanne"."asutnr"
  end if;

  update "gl_dk_kanne" left outer join "dk_kanne" on "gl_dk_kanne"."objnr"="dk_kanne"."objnr" and "gl_dk_kanne"."arvekuup"="dk_kanne"."arvekuup" and "gl_dk_kanne"."arvenr"="dk_kanne"."arvenr" and "gl_dk_kanne"."asutnr"="dk_kanne"."asutnr"
    set "gl_dk_kanne"."id"="dk_kanne"."id",
        "gl_dk_kanne"."aasta"="dk_kanne"."aasta",
        "gl_dk_kanne"."kuup"="dk_kanne"."kuup"
    where "dk_kanne"."id" is not null;

END