ALTER PROCEDURE "dba"."dk_import_earve"(in a_id integer,in a_objnr integer,in a_peralg date,in a_perlop date,in a_id_user integer, in a_euroforeve smallint default 1)
begin
  declare ll_id_dk_costreport integer;
  declare ls_type char(3);
  declare ll_asutnr integer;
  declare ls_crenr char(20);
  declare ld_crekuup date;
  if exists(select 1 from dk_earve where dk_earve."id"=a_id and dk_earve.imporditud=0) then
    select id_dk_costreport into ll_id_dk_costreport from dk_earve where id=a_id;
    insert into gl_dk_kanne( objnr,tulnud,sisse,kuup,summa,alusnr,"info",konto,asutnr,kulnr,artnr,tulnr,objectnr,user_code,arvekuup,arvenr,taskuup,viitnr,sarve,kogus,yhiknr,hind,kbnr,summa2,saajanr,osaknr,subjectnr,suundnr,talnr,alliknr,ravonr,partnr,meetnr,taotnr,id_earve,crenr,id_maks,lisanr) 
      select get_eak_objnr(dk_earve_rida.id_dk_earve,dk_earve_rida."id",a_objnr),
             'E-arve',
             if dk_earve.sisse is null then 0 else dk_earve.sisse endif,
             get_eak_transactiondate(dk_earve_rida.id_dk_earve,dk_earve_rida."id"),
             if dk_earve_rida.summa is null then get_eak_itemsum(dk_earve_rida.id_dk_earve,dk_earve_rida."id") else dk_earve_rida.summa endif as calc_itemsum,
             get_dk_alus(Left(dk_earve_rida."description",50)) as dk_alusnr,
             if dk_alusnr is null then Left(dk_earve_rida."description"+RTrim(' '+dk_earve."contractnumber"),200) else dk_earve."contractnumber" endif,
             dk_earve_rida.konto,
             get_eak_earve_asutnr(dk_earve."id"),
             dk_earve_rida.kulnr,
             dk_earve_rida.artnr,
             dk_earve_rida.tulnr,
             dk_earve_rida.objectnr,
             a_id_user,
             if dk_costreport."id" is null then dk_earve.invoicedate else dk_costreport.invoicedate endif,
             if dk_costreport."id" is null then Left(dk_earve.invoicenumber,20) else if dk_costreport.invoicenumber is null then Left(dk_earve.invoicenumber,20) else Left(dk_costreport.invoicenumber,20) endif endif,
             if dk_earve.duedate is null then if dk_earve.payduedate is null or dk_earve.payduedate<ymd(2011,1,1) then date(now()) else dk_earve.payduedate endif else dk_earve.duedate endif,
             if "dk_earve"."paymentrefid" is null or Length("dk_earve"."paymentrefid")=0 then "dk_earve"."paymentreferencenumber" else "dk_earve"."paymentrefid" endif,
             if dk_earve.sisse is null or dk_earve.sisse=0 then if dk_earve.paytoaccount is null or Length(trim(dk_earve.paytoaccount))=0 then dk_earve.maksjaiban else Left(dk_earve.paytoaccount,30) endif else '' endif,
             dk_earve_rida.itemamount,
             if dk_earve_rida.itemunit is null or Length(Trim(dk_earve_rida.itemunit))=0 then null else existid_yhiklist(left(dk_earve_rida.itemunit,12)) endif,
             dk_earve_rida.itemprice,
             if dk_earve_rida.eakvatcode is null or Length(dk_earve_rida.eakvatcode)=0 then (if dk_earve_rida.vatrate is null or dk_earve_rida.vatrate=0 then null else existid_kblist_protsent(dk_earve_rida.vatrate) endif) else existid_kblist_nimi(Left(dk_earve_rida.eakvatcode,25)) endif,
             if dk_earve_rida.summa2 is null then if dk_earve_rida.vatsum is null then 0 else dk_earve_rida.vatsum endif else dk_earve_rida.summa2 endif as calc_vatsum,
             get_eak_earve_saajanr(dk_earve."id"),
             dk_earve_rida.osaknr,
             dk_earve_rida.subjectnr,
             dk_earve_rida.suundnr,
             dk_earve_rida.talnr,
             dk_earve_rida.alliknr,
             dk_earve_rida.ravonr,
             dk_earve_rida.partnr,
             dk_earve_rida.meetnr,
             dk_earve_rida.taotnr,
             dk_earve."id",
             Left(Trim(dk_earve.sourceinvoice),20),
             dk_earve_rida.id_maks,
             dk_earve_rida.lisanr
        from dk_earve_rida left outer join dk_earve on dk_earve_rida.id_dk_earve = dk_earve.id left outer join dk_costreport on dk_earve.id_dk_costreport=dk_costreport."id"
        where ((ll_id_dk_costreport is null and dk_earve_rida.id_dk_earve=a_id) or (ll_id_dk_costreport is not null and dk_earve.id_dk_costreport=ll_id_dk_costreport)) and dk_earve_rida."id">0 and (calc_itemsum<>0 or calc_vatsum<>0);
    -- poordkaibemaks
    update gl_dk_kanne left outer join kblist on gl_dk_kanne.kbnr=kblist.kbnr set gl_dk_kanne.summa4=Round(gl_dk_kanne.summa*kblist.protsent4/100,2) where not (kblist.protsent4 is null or kblist.protsent4=0);
    -- kreedit arve mitte kuluaruande korral
    if ll_id_dk_costreport is null then
      select Upper(Left(Trim(dk_earve.type),3)),Left(Trim(dk_earve.sourceinvoice),20) into ls_type,ls_crenr from dk_earve where dk_earve.id=a_id;
      if ls_type='CRE' then
        update gl_dk_kanne set summa=(-1)*Abs(summa), summa2=(-1)*Abs(summa2), kogus=(-1)*Abs(kogus), hind=Abs(hind) where gl_dk_kanne.id_earve=a_id;
        if Length(ls_crenr) > 0 then
          select first asutnr into ll_asutnr from gl_dk_kanne where gl_dk_kanne.id_earve=a_id;
        end if;
        if ll_asutnr > 0 then
          select Max(arvekuup) into ld_crekuup from dk_kanne where dk_kanne.asutnr=ll_asutnr and dk_kanne.arvenr=ls_crenr;
          if ld_crekuup is not null then
            update gl_dk_kanne set crekuup=ld_crekuup where gl_dk_kanne.id_earve=a_id
          end if
        end if
      end if
    end if
  end if
end