ALTER PROCEDURE "dba"."pr_pa_kan"(in a_skoop integer,in a_peralg date,in a_perlop date)
begin
  declare li_v_aasta numeric(4);
  declare li_v_akuu numeric(2);
  declare li_t_aasta numeric(4);
  declare li_t_akuu numeric(2);
  declare ls_order char(3);
  declare ls_erisorder char(3);
  declare ls_ettekree char(7);
  declare li_leping_tun smallint;
  declare ls_tulukree_mata char(7);
  declare ls_tvtkkree_mata char(7);
  declare ls_penskree_mata char(7);
  declare ls_sotskree_mata char(7);
  declare ls_tatkkree_mata char(7);
  declare ls_tulukree char(7);
  declare ls_tvtkkree char(7);
  declare ls_penskree char(7);
  declare ls_sotsdeeb char(7);
  declare ls_sotskree char(7);
  declare ls_sotskonto char(7);
  declare ls_sotsart char(16);
  declare li_sotsartnr integer;
  declare ls_tatkdeeb char(7);
  declare ls_tatkkree char(7);
  declare ls_tatkkonto char(7);
  declare ls_tatkart char(16);
  declare li_tatkartnr integer;
  declare ls_eristuludeeb char(7);
  declare ls_eristulukree char(7);
  declare ls_eristuluart char(16);
  declare li_eristuluartnr integer;
  declare ls_eris5tuludeeb char(7);
  declare ls_eris5tulukree char(7);
  declare ls_eris5tuluart char(16);
  declare li_eris5tuluartnr integer;
  declare ls_erissotsdeeb char(7);
  declare ls_erissotskree char(7);
  declare ls_erissotsart char(16);
  declare li_erissotsartnr integer;
  declare li_skoop integer;
  declare ls_dk_kontod char(7000);
  declare err_notfound exception for sqlstate value '02000';
  -- valdkondade kaupa
  declare c_skoobid dynamic scroll cursor for
    select skoobid.skoop from skoobid where skoobid.skoop=a_skoop or a_skoop=0;

  delete from gl_pr_kanne;
  delete from gl_pr_kanne_import;

  -- algvaartustamine
  set a_perlop=get_perlop(a_perlop);
  set li_v_aasta=Years(a_perlop);
  set li_v_akuu=Month(a_perlop);
  -- tasumiseks kuu varasem
  set li_t_akuu=li_v_akuu-1;
  set li_t_aasta=li_v_aasta;
  if li_t_akuu=0 then
    set li_t_akuu=12;
    set li_t_aasta=li_t_aasta-1
  end if;




  if exists(select 1 from pa_leping_tun where konto is not null) then
    set li_leping_tun=1
  else
    set li_leping_tun=0
  end if;

  select pa_konfm.yld_palk_mem,pa_konfm.yld_eris_mem,
         if (k_ettedeeb is null or k_ettedeeb_dk=1) then '' else Trim(k_ettedeeb) endif,
         if k_tulukree is null or Trim(k_tulukree) = '' then 'xxxxxyz' else k_tulukree endif,
         if k_tulukree_mata is null or Trim(k_tulukree_mata) = '' then 'xxxxxyz' else k_tulukree_mata endif,
         if k_tvtkkree is null or Trim(k_tvtkkree) = '' then 'xxxxxyz' else k_tvtkkree endif,
         if k_tvtkkree_mata is null or Trim(k_tvtkkree_mata) = '' then 'xxxxxyz' else k_tvtkkree_mata endif,
         if k_penskree is null or Trim(k_penskree) = '' then 'xxxxxyz' else k_penskree endif,
         if k_penskree_mata is null or Trim(k_penskree_mata) = '' then 'xxxxxyz' else k_penskree_mata endif,
         if k_sotskree is null or Trim(k_sotskree) = '' then 'xxxxxyz' else k_sotskree endif,
         if k_sotskree_mata is null or Trim(k_sotskree_mata) = '' then 'xxxxxyz' else k_sotskree_mata endif,
         if k_tatkkree is null or Trim(k_tatkkree) = '' then 'xxxxxyz' else k_tatkkree endif,
         if k_tatkkree_mata is null or Trim(k_tatkkree_mata) = '' then 'xxxxxyz' else k_tatkkree_mata endif,
         if k_sotsdeeb is null or Trim(k_sotsdeeb) = '' then 'xxxxxyz' else k_sotsdeeb endif,
         if k_tatkdeeb is null or Trim(k_tatkdeeb) = '' then 'xxxxxyz' else k_tatkdeeb endif,
         if k_sotsart is null or Trim(k_sotsart) = '' then 'xxxxxyz' else k_sotsart endif,
         if k_tatkart is null or Trim(k_tatkart) = '' then 'xxxxxyz' else k_tatkart endif,
         if k_eristuludeeb is null then '' else Trim(k_eristuludeeb) endif,
         if k_eristulukree is null then '' else Trim(k_eristulukree) endif,
         if k_eristuluart is null then '' else Trim(k_eristuluart) endif,
         if k_eris5tuludeeb is null or Trim(k_eris5tuludeeb)='' then if k_eristuludeeb is null then '' else Trim(k_eristuludeeb) endif else Trim(k_eris5tuludeeb) endif,
         if k_eris5tulukree is null or Trim(k_eris5tulukree)='' then if k_eristulukree is null then '' else Trim(k_eristulukree) endif else Trim(k_eris5tulukree) endif,
         if k_eris5tuluart is null or Trim(k_eris5tuluart)='' then if k_eristuluart is null then '' else Trim(k_eristuluart) endif else k_eris5tuluart endif,
         if k_erissotsdeeb is null then '' else Trim(k_erissotsdeeb) endif,
         if k_erissotskree is null then '' else Trim(k_erissotskree) endif,
         if k_erissotsart is null then '' else Trim(k_erissotsart) endif,
         if k_sotskonto is null or Trim(k_sotskonto) = '' then 'xxxxxyz' else k_sotskonto endif,
         if k_tatkkonto is null or Trim(k_tatkkonto) = '' then 'xxxxxyz' else k_tatkkonto endif
    into ls_order,ls_erisorder,
         ls_ettekree,
         ls_tulukree,
         ls_tulukree_mata,
         ls_tvtkkree,
         ls_tvtkkree_mata,
         ls_penskree,
         ls_penskree_mata,
         ls_sotskree,
         ls_sotskree_mata,
         ls_tatkkree,
         ls_tatkkree_mata,
         ls_sotsdeeb,
         ls_tatkdeeb,
         ls_sotsart,
         ls_tatkart,
         ls_eristuludeeb,
         ls_eristulukree,
         ls_eristuluart,
         ls_eris5tuludeeb,
         ls_eris5tulukree,
         ls_eris5tuluart,
         ls_erissotsdeeb,
         ls_erissotskree,
         ls_erissotsart,
         ls_sotskonto,
         ls_tatkkonto
    from pa_konfm where pa_konfm.id = 1;

  select first artlist.artnr into li_sotsartnr from artlist where Trim(artlist.art) = ls_sotsart;
  if li_sotsartnr is null then
    set li_sotsartnr=0
  end if;
  select first artlist.artnr into li_tatkartnr from artlist where Trim(artlist.art) = ls_tatkart;
  if li_tatkartnr is null then
    set li_tatkartnr=0
  end if;
  select first artlist.artnr into li_eristuluartnr from artlist where Trim(artlist.art) = ls_eristuluart;
  if li_eristuluartnr is null then
    set li_eristuluartnr=0
  end if;
  select first artlist.artnr into li_eris5tuluartnr from artlist where Trim(artlist.art) = ls_eris5tuluart;
  if li_eris5tuluartnr is null then
    set li_eris5tuluartnr=0
  end if;
  select first artlist.artnr into li_erissotsartnr from artlist where Trim(artlist.art) = ls_erissotsart;
  if li_erissotsartnr is null then
    set li_erissotsartnr=0
  end if;

  -- SKOOBI tsykkel

  open c_skoobid;
  skoobid_loop: loop
    fetch next c_skoobid into li_skoop;
    if sqlstate = err_notfound then -- kui eof
      leave skoobid_loop
    end if;
    set ls_dk_kontod=get_dk_kontod(li_skoop,0);

    -- jooksev arvestus

    delete from gl_pa_tasu;
    insert into gl_pa_tasu(tasunr,summa,tulumaks,tvtkmaks,pensmaks,sotsmaks,tatkmaks,par1,v_akuu,v_aasta,a_apaev,a_lpaev,a_akuu,a_aasta,kuup2,t_akuu,t_aasta,tas_s,vtas_s,lepnr,paryldnr,vparyldnr,yksusnr,deeb,kree,konto,tun_konto,
                          meetnr,meet1nr,meet2nr,meet3nr,meet4nr,kulnr,kul1nr,kul2nr,kul3nr,kul4nr,talnr,tal1nr,tal2nr,tal3nr,tal4nr,artnr,art1nr,art2nr,tulnr,tul1nr,tul2nr,objectnr,object1nr,object2nr,object3nr,object4nr,osaknr,osak1nr,osak2nr,osak3nr,osak4nr,subjectnr,subject1nr,subject2nr,subject3nr,subject4nr,suundnr,suund1nr,suund2nr,suund3nr,suund4nr,taotnr,taot1nr,taot2nr,taot3nr,taot4nr)
      select pa_tasu.tasunr,
             pa_tasu.summa,
            (if pa_taslist_a.sisse = 0 then pa_tasu.tulumaks else(-1)*pa_tasu.tulumaks endif),
            (if pa_taslist_a.sisse = 0 then pa_tasu.tvtkmaks else(-1)*pa_tasu.tvtkmaks endif),
            (if pa_taslist_a.sisse = 0 then pa_tasu.pensmaks else(-1)*pa_tasu.pensmaks endif),
            (if pa_taslist_a.sisse = 0 then pa_tasu.sotsmaks else(-1)*pa_tasu.sotsmaks endif),
            (if pa_taslist_a.sisse = 0 then pa_tasu.tatkmaks else(-1)*pa_tasu.tatkmaks endif),
             if pa_taslist_a.algol='6a' then pa_tasu.par1 else 0 endif,
             pa_tasu.v_akuu,pa_tasu.v_aasta,pa_tasu.a_apaev,pa_tasu.a_lpaev,pa_tasu.a_akuu,pa_tasu.a_aasta,ymd(pa_tasu.a_aasta,pa_tasu.a_akuu,pa_tasu.a_lpaev),pa_tasu.t_akuu,pa_tasu.t_aasta,
             pa_tasu.tas_s,pa_tasu.vtas_s,
             pa_tasu.lepnr,
             pa_tasu.paryldnr,pa_tasu.vparyldnr,
             pa_tasu.yksusnr,
            (if pa_taslist_a.deeb is null then pa_taslist_b.deeb else pa_taslist_a.deeb endif),
            (if pa_taslist_a.kree is null then pa_taslist_b.kree else pa_taslist_a.kree endif),
            (if pa_taslist_a.konto is null then pa_taslist_b.konto else pa_taslist_a.konto endif) as pa_konto,
             if li_leping_tun=1 then get_pa_leping_tun_konto(pa_tasu.lepnr,ymd(pa_tasu.a_aasta,pa_tasu.a_akuu,pa_tasu.a_lpaev)) else null endif,
             pa_tasu.meetnr,pa_paryld_b.meetnr,pa_taslist_a.meetnr,pa_taslist_b.meetnr,pa_yksus.meetnr,
             pa_tasu.kulnr,pa_paryld_b.kulnr,pa_taslist_a.kulnr,pa_taslist_b.kulnr,pa_yksus.kulnr,
             pa_tasu.talnr,pa_paryld_b.talnr,pa_taslist_a.talnr,pa_taslist_b.talnr,pa_yksus.talnr,
             pa_taslist_a.artnr,pa_taslist_b.artnr,pa_yksus.artnr,
             pa_taslist_a.tulnr,pa_taslist_b.tulnr,pa_yksus.tulnr,
             pa_tasu.objectnr,pa_paryld_b.objectnr,pa_taslist_a.objectnr,pa_taslist_b.objectnr,pa_yksus.objectnr,
             pa_tasu.osaknr,pa_paryld_b.osaknr,pa_taslist_a.osaknr,pa_taslist_b.osaknr,pa_yksus.osaknr,
             pa_tasu.subjectnr,pa_paryld_b.subjectnr,pa_taslist_a.subjectnr,pa_taslist_b.subjectnr,pa_yksus.subjectnr,
             pa_tasu.suundnr,pa_paryld_b.suundnr,pa_taslist_a.suundnr,pa_taslist_b.suundnr,pa_yksus.suundnr,
             pa_tasu.taotnr,pa_paryld_b.taotnr,pa_taslist_a.taotnr,pa_taslist_b.taotnr,pa_yksus.taotnr
        from pa_tasu left outer join pa_taslist as pa_taslist_a on pa_tasu.tas_s = pa_taslist_a.tas_s
                     left outer join pa_taslist as pa_taslist_b on pa_tasu.vtas_s = pa_taslist_b.tas_s
                     left outer join pa_yksus on pa_tasu.yksusnr = pa_yksus.yksusnr
                     left outer join pa_paryld as pa_paryld_b on pa_tasu.vparyldnr = pa_paryld_b.paryldnr
        where
              pa_yksus.skoop=li_skoop and
              pa_tasu.palgaleht is not null and
             (pa_tasu.v_aasta=li_v_aasta and pa_tasu.v_akuu=li_v_akuu);
    call pr_pa_kan_nulli();
    update gl_pa_tasu set deeb=if exists(select 1 from kontlist where konto=Left(tun_konto,5)+substring(deeb,6)) then Left(tun_konto,5)+substring(deeb,6) else deeb endif where tun_konto is not null and Length(tun_konto)>4 and Length(deeb)>5 and left(deeb,2)=left(tun_konto,2);

    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
     select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.summa),
             pa_taslist_a.nimi as pa_info,
             ls_order,
             if pa_taslist_a.sisse=1 or gl_pa_tasu.konto is null or(gl_pa_tasu.v_aasta>gl_pa_tasu.a_aasta or gl_pa_tasu.v_aasta=gl_pa_tasu.a_aasta and gl_pa_tasu.v_akuu>=gl_pa_tasu.a_akuu) then gl_pa_tasu.deeb else gl_pa_tasu.konto endif as pa_deeb,
             gl_pa_tasu.kree as pa_kree,
             if pa_taslist_a.sisse=1 then asulist_a.partnr else coalesce(pa_taslist_a.partnr,asulist_b.partnr,asulist_a.partnr) endif as pa_partnr,
             if pa_taslist_a.sisse=1 then coalesce(pa_taslist_a.partnr,asulist_b.partnr,asulist_a.partnr) else asulist_a.partnr endif as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                      left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_paryld as pa_paryld_a on gl_pa_tasu.paryldnr = pa_paryld_a.paryldnr
                      left outer join asulist as asulist_b on pa_paryld_a.asutnr = asulist_b.asutnr
      where pa_deeb<>pa_kree and locate(ls_dk_kontod,';'+"Left"(pa_kree+Space(7),7)+';')=0
      group by pa_info,pa_deeb,pa_kree,pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;


    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
     select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.tulumaks),
             'Tulumaks',
             ls_order,
             (if pa_taslist_a.sisse = 0 then pa_taslist_a.kree else pa_taslist_b.kree endif) as pa_deeb,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then ls_tulukree else ls_tulukree_mata endif as pa_kree,
             asulist_a.partnr as pa_partnr,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then null else asulist_a.partnr endif as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                      left outer join pa_taslist as pa_taslist_b on gl_pa_tasu.vtas_s = pa_taslist_b.tas_s
                      left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_paryld as pa_paryld_a on gl_pa_tasu.paryldnr = pa_paryld_a.paryldnr
                      left outer join asulist as asulist_b on pa_paryld_a.asutnr = asulist_b.asutnr
      group by pa_deeb,pa_kree,pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;

    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
     select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.tvtkmaks),
             'Töövõtja töötuskindlustus',
             ls_order,
             (if pa_taslist_a.sisse = 0 then pa_taslist_a.kree else pa_taslist_b.kree endif) as pa_deeb,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then ls_tvtkkree else ls_tvtkkree_mata endif as pa_kree,
             asulist_a.partnr as pa_partnr,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then null else asulist_a.partnr endif as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                      left outer join pa_taslist as pa_taslist_b on gl_pa_tasu.vtas_s = pa_taslist_b.tas_s
                      left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_paryld as pa_paryld_a on gl_pa_tasu.paryldnr = pa_paryld_a.paryldnr
                      left outer join asulist as asulist_b on pa_paryld_a.asutnr = asulist_b.asutnr
      group by pa_deeb,pa_kree,pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;

    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
     select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.pensmaks),
             'Kogumispension',
             ls_order,
             (if pa_taslist_a.sisse = 0 then pa_taslist_a.kree else pa_taslist_b.kree endif) as pa_deeb,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then ls_penskree else ls_penskree_mata endif as pa_kree,
             asulist_a.partnr as pa_partnr,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then null else asulist_a.partnr endif as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                      left outer join pa_taslist as pa_taslist_b on gl_pa_tasu.vtas_s = pa_taslist_b.tas_s
                      left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_paryld as pa_paryld_a on gl_pa_tasu.paryldnr = pa_paryld_a.paryldnr
                      left outer join asulist as asulist_b on pa_paryld_a.asutnr = asulist_b.asutnr
      group by pa_deeb,pa_kree,pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;

    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
     select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.par1),
             'Tulumaks alimentidelt',
             ls_order,
             gl_pa_tasu.kree as pa_deeb,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then ls_tulukree else ls_tulukree_mata endif as pa_kree,
             coalesce(pa_taslist_a.partnr,asulist_b.partnr,asulist_a.partnr) as pa_partnr,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then null else coalesce(pa_taslist_a.partnr,asulist_b.partnr,asulist_a.partnr) endif as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                      left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_paryld as pa_paryld_a on gl_pa_tasu.paryldnr = pa_paryld_a.paryldnr
                      left outer join asulist as asulist_b on pa_paryld_a.asutnr = asulist_b.asutnr
      where gl_pa_tasu.par1<>0
      group by pa_deeb,pa_kree,pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;

    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
     select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.sotsmaks),
             'Sotsiaalmaks',
             ls_order,
             if pa_taslist_a.sisse=1 or ls_sotskonto = 'xxxxxyz' or gl_pa_tasu.konto is null or (gl_pa_tasu.v_aasta>gl_pa_tasu.a_aasta or gl_pa_tasu.v_aasta=gl_pa_tasu.a_aasta and gl_pa_tasu.v_akuu>=gl_pa_tasu.a_akuu) then if pa_taslist_a.samkon=1 then gl_pa_tasu.deeb else ls_sotsdeeb endif else ls_sotskonto endif as pa_deeb,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then ls_sotskree else ls_sotskree_mata endif as pa_kree,
             asulist_a.partnr as pa_partnr,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then null else asulist_a.partnr endif as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             if (pa_taslist_a.samart is not null and pa_taslist_a.samart=1) then coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) else li_sotsartnr endif as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                      left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_paryld as pa_paryld_a on gl_pa_tasu.paryldnr = pa_paryld_a.paryldnr
                      left outer join asulist as asulist_b on pa_paryld_a.asutnr = asulist_b.asutnr
      group by pa_deeb,pa_kree,pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;


    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
     select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.tatkmaks),
             'Tööandja töötuskindlustus',
             ls_order,
             if pa_taslist_a.sisse=1 or ls_tatkkonto = 'xxxxxyz' or gl_pa_tasu.konto is null or (gl_pa_tasu.v_aasta>gl_pa_tasu.a_aasta or gl_pa_tasu.v_aasta=gl_pa_tasu.a_aasta and gl_pa_tasu.v_akuu>=gl_pa_tasu.a_akuu) then if pa_taslist_a.samkon=1 then gl_pa_tasu.deeb else ls_tatkdeeb endif else ls_tatkkonto endif as pa_deeb,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then ls_tatkkree else ls_tatkkree_mata endif as pa_kree,
             asulist_a.partnr as pa_partnr,
             if (gl_pa_tasu.v_aasta=gl_pa_tasu.t_aasta and gl_pa_tasu.v_akuu=gl_pa_tasu.t_akuu) then null else asulist_a.partnr endif as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             if (pa_taslist_a.samart is not null and pa_taslist_a.samart=1) then coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) else li_tatkartnr endif as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                      left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_paryld as pa_paryld_a on gl_pa_tasu.paryldnr = pa_paryld_a.paryldnr
                      left outer join asulist as asulist_b on pa_paryld_a.asutnr = asulist_b.asutnr
      group by pa_deeb,pa_kree,pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;


  -- maksud deklareerimata kontolt maha

  delete from gl_pa_tasu;
  insert into gl_pa_tasu(tasunr,summa,tulumaks,tvtkmaks,pensmaks,sotsmaks,tatkmaks,par1,v_akuu,v_aasta,a_apaev,a_lpaev,a_akuu,a_aasta,kuup2,t_akuu,t_aasta,tas_s,vtas_s,lepnr,paryldnr,vparyldnr,yksusnr,
                        meetnr,meet1nr,meet2nr,meet3nr,meet4nr,kulnr,kul1nr,kul2nr,kul3nr,kul4nr,talnr,tal1nr,tal2nr,tal3nr,tal4nr,artnr,art1nr,art2nr,tulnr,tul1nr,tul2nr,objectnr,object1nr,object2nr,object3nr,object4nr,osaknr,osak1nr,osak2nr,osak3nr,osak4nr,subjectnr,subject1nr,subject2nr,subject3nr,subject4nr,suundnr,suund1nr,suund2nr,suund3nr,suund4nr,taotnr,taot1nr,taot2nr,taot3nr,taot4nr)
    select pa_tasu.tasunr,
           pa_tasu.summa,
          (if pa_taslist_a.sisse = 0 then pa_tasu.tulumaks else(-1)*pa_tasu.tulumaks endif),
          (if pa_taslist_a.sisse = 0 then pa_tasu.tvtkmaks else(-1)*pa_tasu.tvtkmaks endif),
          (if pa_taslist_a.sisse = 0 then pa_tasu.pensmaks else(-1)*pa_tasu.pensmaks endif),
          (if pa_taslist_a.sisse = 0 then pa_tasu.sotsmaks else(-1)*pa_tasu.sotsmaks endif),
          (if pa_taslist_a.sisse = 0 then pa_tasu.tatkmaks else(-1)*pa_tasu.tatkmaks endif),
           pa_tasu.par1,
           pa_tasu.v_akuu,pa_tasu.v_aasta,pa_tasu.a_apaev,pa_tasu.a_lpaev,pa_tasu.a_akuu,pa_tasu.a_aasta,ymd(pa_tasu.a_aasta,pa_tasu.a_akuu,pa_tasu.a_lpaev),pa_tasu.t_akuu,pa_tasu.t_aasta,
           pa_tasu.tas_s,pa_tasu.vtas_s,
           pa_tasu.lepnr,
           pa_tasu.paryldnr,pa_tasu.vparyldnr,
           pa_tasu.yksusnr,
           pa_tasu.meetnr,pa_paryld_b.meetnr,pa_taslist_a.meetnr,pa_taslist_b.meetnr,pa_yksus.meetnr,
           pa_tasu.kulnr,pa_paryld_b.kulnr,pa_taslist_a.kulnr,pa_taslist_b.kulnr,pa_yksus.kulnr,
           pa_tasu.talnr,pa_paryld_b.talnr,pa_taslist_a.talnr,pa_taslist_b.talnr,pa_yksus.talnr,
           pa_taslist_a.artnr,pa_taslist_b.artnr,pa_yksus.artnr,
           pa_taslist_a.tulnr,pa_taslist_b.tulnr,pa_yksus.tulnr,
           pa_tasu.objectnr,pa_paryld_b.objectnr,pa_taslist_a.objectnr,pa_taslist_b.objectnr,pa_yksus.objectnr,
           pa_tasu.osaknr,pa_paryld_b.osaknr,pa_taslist_a.osaknr,pa_taslist_b.osaknr,pa_yksus.osaknr,
           pa_tasu.subjectnr,pa_paryld_b.subjectnr,pa_taslist_a.subjectnr,pa_taslist_b.subjectnr,pa_yksus.subjectnr,
           pa_tasu.suundnr,pa_paryld_b.suundnr,pa_taslist_a.suundnr,pa_taslist_b.suundnr,pa_yksus.suundnr,
           pa_tasu.taotnr,pa_paryld_b.taotnr,pa_taslist_a.taotnr,pa_taslist_b.taotnr,pa_yksus.taotnr
      from pa_tasu left outer join pa_taslist as pa_taslist_a on pa_tasu.tas_s = pa_taslist_a.tas_s
                   left outer join pa_taslist as pa_taslist_b on pa_tasu.vtas_s = pa_taslist_b.tas_s
                   left outer join pa_yksus on pa_tasu.yksusnr = pa_yksus.yksusnr
                   left outer join pa_paryld as pa_paryld_b on pa_tasu.vparyldnr = pa_paryld_b.paryldnr
        where pa_yksus.skoop=li_skoop and
              pa_tasu.palgaleht is not null and
             (pa_tasu.t_aasta = li_v_aasta and pa_tasu.t_akuu = li_v_akuu) and
              not(pa_tasu.v_aasta = pa_tasu.t_aasta and pa_tasu.v_akuu = pa_tasu.t_akuu);
  call pr_pa_kan_nulli();

    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
      select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.tulumaks),
            'Tulumaks',
             ls_order,ls_tulukree_mata,ls_tulukree,
             asulist_a.partnr as pa_partnr,
             null,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
        group by pa_partnr,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;

    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
      select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.par1),
            'Tulumaks alimentidelt',
             ls_order,ls_tulukree_mata,ls_tulukree,
             asulist_a.partnr as pa_partnr,
             null,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
      where pa_taslist_a.algol='6a' and gl_pa_tasu.par1 is not null and gl_pa_tasu.par1<>0
      group by pa_partnr,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;

    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
      select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.tvtkmaks),
            'Töövõtja töötuskindlustus',
             ls_order,ls_tvtkkree_mata,ls_tvtkkree,
             if pa_taslist_a.partnr is null then if asulist_b.partnr is null then asulist_a.partnr else asulist_b.partnr endif else pa_taslist_a.partnr endif as pa_partnr,
             asulist_a.partnr as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                      left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_paryld as pa_paryld_a on gl_pa_tasu.paryldnr = pa_paryld_a.paryldnr
                      left outer join asulist as asulist_b on pa_paryld_a.asutnr = asulist_b.asutnr
      group by pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;

    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
      select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.pensmaks),
            'Kogumispension',
             ls_order,ls_penskree_mata,ls_penskree,
             if pa_taslist_a.partnr is null then if asulist_b.partnr is null then asulist_a.partnr else asulist_b.partnr endif else pa_taslist_a.partnr endif as pa_partnr,
             asulist_a.partnr as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                      left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_paryld as pa_paryld_a on gl_pa_tasu.paryldnr = pa_paryld_a.paryldnr
                      left outer join asulist as asulist_b on pa_paryld_a.asutnr = asulist_b.asutnr
      group by pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;


    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
      select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.sotsmaks),
            'Sotsiaalmaks',
             ls_order,ls_sotskree_mata,ls_sotskree,
             asulist_a.partnr as pa_partnr,
             null,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             if (pa_taslist_a.samart is not null and pa_taslist_a.samart=1) then coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) else li_sotsartnr endif as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
      group by pa_partnr,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;


    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
      select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.tatkmaks),
            'Tööandja töötuskindlustus',
             ls_order,ls_tatkkree_mata,ls_tatkkree,
             asulist_a.partnr as pa_partnr,
             null,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             if (pa_taslist_a.samart is not null and pa_taslist_a.samart=1) then coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) else li_tatkartnr endif as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
      group by pa_partnr,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;


-- ettearvestatud summad ettemaksu kontolt maha

  delete from gl_pa_tasu;
  insert into gl_pa_tasu(tasunr,summa,tulumaks,tvtkmaks,pensmaks,sotsmaks,tatkmaks,par1,v_akuu,v_aasta,a_apaev,a_lpaev,a_akuu,a_aasta,kuup2,t_akuu,t_aasta,tas_s,vtas_s,lepnr,paryldnr,vparyldnr,yksusnr,deeb,konto,tun_konto,
                        meetnr,meet1nr,meet2nr,meet3nr,meet4nr,kulnr,kul1nr,kul2nr,kul3nr,kul4nr,talnr,tal1nr,tal2nr,tal3nr,tal4nr,artnr,art1nr,art2nr,tulnr,tul1nr,tul2nr,objectnr,object1nr,object2nr,object3nr,object4nr,osaknr,osak1nr,osak2nr,osak3nr,osak4nr,subjectnr,subject1nr,subject2nr,subject3nr,subject4nr,suundnr,suund1nr,suund2nr,suund3nr,suund4nr,taotnr,taot1nr,taot2nr,taot3nr,taot4nr)
    select pa_tasu.tasunr,
           pa_tasu.summa,
          (if pa_taslist_a.sisse = 0 then pa_tasu.tulumaks else(-1)*pa_tasu.tulumaks endif),
          (if pa_taslist_a.sisse = 0 then pa_tasu.tvtkmaks else(-1)*pa_tasu.tvtkmaks endif),
          (if pa_taslist_a.sisse = 0 then pa_tasu.pensmaks else(-1)*pa_tasu.pensmaks endif),
          (if pa_taslist_a.sisse = 0 then pa_tasu.sotsmaks else(-1)*pa_tasu.sotsmaks endif),
          (if pa_taslist_a.sisse = 0 then pa_tasu.tatkmaks else(-1)*pa_tasu.tatkmaks endif),
           pa_tasu.par1,
           pa_tasu.v_akuu,pa_tasu.v_aasta,pa_tasu.a_apaev,pa_tasu.a_lpaev,pa_tasu.a_akuu,pa_tasu.a_aasta,ymd(pa_tasu.a_aasta,pa_tasu.a_akuu,pa_tasu.a_lpaev),pa_tasu.t_akuu,pa_tasu.t_aasta,
           pa_tasu.tas_s,pa_tasu.vtas_s,
           pa_tasu.lepnr,
           pa_tasu.paryldnr,pa_tasu.vparyldnr,
           pa_tasu.yksusnr,
          (if pa_taslist_a.deeb is null then pa_taslist_b.deeb else pa_taslist_a.deeb endif),
          (if pa_taslist_a.konto is null then pa_taslist_b.konto else pa_taslist_a.konto endif) as pa_konto,
           if li_leping_tun=1 then get_pa_leping_tun_konto(pa_tasu.lepnr,ymd(pa_tasu.a_aasta,pa_tasu.a_akuu,pa_tasu.a_lpaev)) else null endif,
           pa_tasu.meetnr,pa_paryld_b.meetnr,pa_taslist_a.meetnr,pa_taslist_b.meetnr,pa_yksus.meetnr,
           pa_tasu.kulnr,pa_paryld_b.kulnr,pa_taslist_a.kulnr,pa_taslist_b.kulnr,pa_yksus.kulnr,
           pa_tasu.talnr,pa_paryld_b.talnr,pa_taslist_a.talnr,pa_taslist_b.talnr,pa_yksus.talnr,
           pa_taslist_a.artnr,pa_taslist_b.artnr,pa_yksus.artnr,
           pa_taslist_a.tulnr,pa_taslist_b.tulnr,pa_yksus.tulnr,
           pa_tasu.objectnr,pa_paryld_b.objectnr,pa_taslist_a.objectnr,pa_taslist_b.objectnr,pa_yksus.objectnr,
           pa_tasu.osaknr,pa_paryld_b.osaknr,pa_taslist_a.osaknr,pa_taslist_b.osaknr,pa_yksus.osaknr,
           pa_tasu.subjectnr,pa_paryld_b.subjectnr,pa_taslist_a.subjectnr,pa_taslist_b.subjectnr,pa_yksus.subjectnr,
           pa_tasu.suundnr,pa_paryld_b.suundnr,pa_taslist_a.suundnr,pa_taslist_b.suundnr,pa_yksus.suundnr,
           pa_tasu.taotnr,pa_paryld_b.taotnr,pa_taslist_a.taotnr,pa_taslist_b.taotnr,pa_yksus.taotnr
      from pa_tasu left outer join pa_taslist as pa_taslist_a on pa_tasu.tas_s = pa_taslist_a.tas_s
                   left outer join pa_taslist as pa_taslist_b on pa_tasu.vtas_s = pa_taslist_b.tas_s
                   left outer join pa_yksus on pa_tasu.yksusnr = pa_yksus.yksusnr
                   left outer join pa_paryld as pa_paryld_b on pa_tasu.vparyldnr = pa_paryld_b.paryldnr
        where pa_yksus.skoop=li_skoop and
              pa_konto is not null and
              pa_tasu.palgaleht is not null and(pa_tasu.a_aasta=li_v_aasta and pa_tasu.a_akuu=li_v_akuu) and
             ((pa_tasu.a_aasta > pa_tasu.v_aasta) or(pa_tasu.a_aasta = pa_tasu.v_aasta and pa_tasu.a_akuu > pa_tasu.v_akuu)) and
              pa_taslist_a.sisse = 0;
    call pr_pa_kan_nulli();

    update gl_pa_tasu set deeb=if exists(select 1 from kontlist where konto=Left(tun_konto,5)+substring(deeb,6)) then Left(tun_konto,5)+substring(deeb,6) else deeb endif where tun_konto is not null and Length(tun_konto)>4 and Length(deeb)>5 and left(deeb,2)=left(tun_konto,2);

    insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
     select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.summa),
             pa_taslist_a.nimi as pa_info,
             ls_order,
             gl_pa_tasu.deeb as pa_deeb,
             gl_pa_tasu.konto as pa_kree,
             coalesce(pa_taslist_a.partnr,asulist_b.partnr,asulist_a.partnr) as pa_partnr,
             asulist_a.partnr as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
      from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                      left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                      left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
                      left outer join pa_paryld as pa_paryld_a on gl_pa_tasu.paryldnr = pa_paryld_a.paryldnr
                      left outer join asulist as asulist_b on pa_paryld_a.asutnr = asulist_b.asutnr
      group by pa_info,pa_deeb,pa_kree,pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
  

    if exists(select 1 from kontlist where konto=ls_sotskonto) then
      insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
      select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.sotsmaks),
             'Sotsiaalmaks',
             ls_order,
             if pa_taslist_a.samkon=1 then gl_pa_tasu.deeb else ls_sotsdeeb endif as pa_deeb,
             ls_sotskonto,
             asulist_a.partnr as pa_partnr,
             asulist_a.partnr as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             if pa_taslist_a.samart=1 then coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) else li_sotsartnr endif as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
        from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                        left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                        left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
        group by pa_deeb,pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
    end if; 

           
    if exists(select 1 from kontlist where konto=ls_tatkkonto) then
      insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"info","order",deeb,kree,partnr,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
      select 'Palk',
             li_skoop,
             a_perlop,
             sum(gl_pa_tasu.tatkmaks),
             'Tööandja töötuskindlustus',
             ls_order,
             if pa_taslist_a.samkon=1 then gl_pa_tasu.deeb else ls_tatkdeeb endif as pa_deeb,
             ls_tatkkonto,
             asulist_a.partnr as pa_partnr,
             asulist_a.partnr as pa_partnr2,
             coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
             coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
             coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
             if pa_taslist_a.samart=1 then coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) else li_sotsartnr endif as pa_artnr,
             coalesce(gl_pa_tasu.tulnr,gl_pa_tasu.tul1nr,gl_pa_tasu.tul2nr) as pa_tulnr,
             coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
             coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
             coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
             coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
             coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr
        from gl_pa_tasu left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
                        left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
                        left outer join asulist as asulist_a on pa_leping.asutnr = asulist_a.asutnr
        group by pa_deeb,pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
    end if; 

 -- Erisoodustus jooksev arvestus

  -- arvestatud erisoodustus
  insert into gl_pr_kanne_import( tulnud,"info",skoop,kuup,summa,"order",deeb,partnr,kree,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr)
        select 'Palk','Erisoodustus',li_skoop,a_perlop,sum(pa_erisood.summa),ls_erisorder,
               pa_erisood.deeb as pa_deeb,
               pa_erisood.partnr as pa_partnr,
               pa_erisood.kree as pa_kree,
               pa_erisood.partnr2 as pa_partnr2,
               if pa_erisood.meetnr is null then pa_yksus.meetnr else pa_erisood.meetnr endif as pa_meetnr,
               if pa_erisood.kulnr is null then pa_yksus.kulnr else pa_erisood.kulnr endif as pa_kulnr,
               if pa_erisood.talnr is null then pa_yksus.talnr else pa_erisood.talnr endif as pa_talnr,
               if pa_erisood.artnr is null then pa_yksus.artnr else pa_erisood.artnr endif as pa_artnr,
               if pa_erisood.tulnr is null then pa_yksus.tulnr else pa_erisood.tulnr endif as pa_tulnr,
               if pa_erisood.objectnr is null then pa_yksus.objectnr else pa_erisood.objectnr endif as pa_objectnr,
               if pa_erisood.osaknr is null then pa_yksus.osaknr else pa_erisood.osaknr endif as pa_osaknr,
               if pa_erisood.subjectnr is null then pa_yksus.subjectnr else pa_erisood.subjectnr endif as pa_subjectnr,
               if pa_erisood.suundnr is null then pa_yksus.suundnr else pa_erisood.suundnr endif as pa_suundnr,
               if pa_erisood.taotnr is null then pa_yksus.taotnr else pa_erisood.taotnr endif as pa_taotnr,
               pa_erisood.alliknr as pa_alliknr,
               pa_erisood.ravonr as pa_ravonr
        from pa_erisood left outer join pa_yksus on pa_erisood.yksusnr = pa_yksus.yksusnr
        where pa_yksus.skoop=li_skoop and
              pa_erisood.v_aasta = li_v_aasta and pa_erisood.v_akuu=li_v_akuu and
              pa_erisood.deeb is not null and pa_erisood.kree is not null
        group by pa_deeb,pa_partnr,pa_kree,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr,pa_alliknr,pa_ravonr;

  -- erisoodustuse arvestatud tulumaks
  if Length(ls_eristuludeeb)>0 and Length(ls_eristulukree)>0 then
    insert into gl_pr_kanne_import( tulnud,"info",skoop,kuup,summa,"order",deeb,partnr,kree,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr)
        select 'Palk','Tulumaks erisoodustustelt',li_skoop,a_perlop,sum(pa_erisood.tulumaks),ls_erisorder,
               if lisalist.objnr=40 then ls_eristuludeeb else ls_eris5tuludeeb endif as pa_deeb,
               pa_erisood.partnr as pa_partnr,
               if lisalist.objnr=40 then ls_eristulukree else ls_eris5tulukree endif as pa_kree,
               pa_erisood.partnr2 as pa_partnr2,
               if pa_erisood.meetnr is null then pa_yksus.meetnr else pa_erisood.meetnr endif as pa_meetnr,
               if pa_erisood.kul2nr is null then if pa_erisood.kulnr is null then pa_yksus.kulnr else pa_erisood.kulnr endif else pa_erisood.kul2nr endif as pa_kulnr,
               if pa_erisood.talnr is null then pa_yksus.talnr else pa_erisood.talnr endif as pa_talnr,
               if pa_erisood.samart is null or pa_erisood.samart=0 then if lisalist.objnr=40 then li_eristuluartnr else li_eris5tuluartnr endif else if pa_erisood.artnr is null then pa_yksus.artnr else pa_erisood.artnr endif endif as pa_artnr,
               if pa_erisood.tulnr is null then pa_yksus.tulnr else pa_erisood.tulnr endif as pa_tulnr,
               if pa_erisood.objectnr is null then pa_yksus.objectnr else pa_erisood.objectnr endif as pa_objectnr,
               if pa_erisood.osaknr is null then pa_yksus.osaknr else pa_erisood.osaknr endif as pa_osaknr,
               if pa_erisood.subjectnr is null then pa_yksus.subjectnr else pa_erisood.subjectnr endif as pa_subjectnr,
               if pa_erisood.suundnr is null then pa_yksus.suundnr else pa_erisood.suundnr endif as pa_suundnr,
               if pa_erisood.taotnr is null then pa_yksus.taotnr else pa_erisood.taotnr endif as pa_taotnr,
               pa_erisood.alliknr as pa_alliknr,
               pa_erisood.ravonr as pa_ravonr
        from pa_erisood left outer join pa_yksus on pa_erisood.yksusnr = pa_yksus.yksusnr left outer join lisalist on pa_erisood.lisanr=lisalist.lisanr
        where pa_yksus.skoop=li_skoop and
              pa_erisood.v_aasta = li_v_aasta and pa_erisood.v_akuu=li_v_akuu
        group by pa_deeb,pa_partnr,pa_kree,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr,pa_alliknr,pa_ravonr;
  end if;

  -- erisoodustuse arvestatud sotsmaks
  if Length(ls_erissotsdeeb)>0 and Length(ls_erissotskree)>0 then
    insert into gl_pr_kanne_import( tulnud,"info",skoop,kuup,summa,"order",deeb,partnr,kree,partnr2,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr)
        select 'Palk','Sotsiaalmaks erisoodustustelt',li_skoop,a_perlop,sum(pa_erisood.sotsmaks),ls_erisorder,
               ls_erissotsdeeb,
               pa_erisood.partnr as pa_partnr,
               ls_erissotskree,
               pa_erisood.partnr2 as pa_partnr2,
               if pa_erisood.meetnr is null then pa_yksus.meetnr else pa_erisood.meetnr endif as pa_meetnr,
               if pa_erisood.kul2nr is null then if pa_erisood.kulnr is null then pa_yksus.kulnr else pa_erisood.kulnr endif else pa_erisood.kul2nr endif as pa_kulnr,
               if pa_erisood.talnr is null then pa_yksus.talnr else pa_erisood.talnr endif as pa_talnr,
               if pa_erisood.samart is null or pa_erisood.samart=0 then li_erissotsartnr else if pa_erisood.artnr is null then pa_yksus.artnr else pa_erisood.artnr endif endif as pa_artnr,
               if pa_erisood.tulnr is null then pa_yksus.tulnr else pa_erisood.tulnr endif as pa_tulnr,
               if pa_erisood.objectnr is null then pa_yksus.objectnr else pa_erisood.objectnr endif as pa_objectnr,
               if pa_erisood.osaknr is null then pa_yksus.osaknr else pa_erisood.osaknr endif as pa_osaknr,
               if pa_erisood.subjectnr is null then pa_yksus.subjectnr else pa_erisood.subjectnr endif as pa_subjectnr,
               if pa_erisood.suundnr is null then pa_yksus.suundnr else pa_erisood.suundnr endif as pa_suundnr,
               if pa_erisood.taotnr is null then pa_yksus.taotnr else pa_erisood.taotnr endif as pa_taotnr,
               pa_erisood.alliknr as pa_alliknr,
               pa_erisood.ravonr as pa_ravonr
        from pa_erisood left outer join pa_yksus on pa_erisood.yksusnr = pa_yksus.yksusnr left outer join lisalist on pa_erisood.lisanr=lisalist.lisanr
        where pa_yksus.skoop=li_skoop and
              pa_erisood.v_aasta = li_v_aasta and pa_erisood.v_akuu=li_v_akuu
        group by pa_partnr,pa_partnr2,pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr,pa_alliknr,pa_ravonr;
  end if;

-- MAKSUDE ETTEMAKSU KONTO

  if Length(ls_ettekree)>0 then

-- Arvestuse maksud ettemaksude kontolt maha

    delete from gl_pa_tasu;
    insert into gl_pa_tasu(tasunr,summa,tulumaks,tvtkmaks,pensmaks,sotsmaks,tatkmaks,par1,v_akuu,v_aasta,a_apaev,a_lpaev,a_akuu,a_aasta,kuup2,t_akuu,t_aasta,tas_s,vtas_s,lepnr,paryldnr,vparyldnr,yksusnr,
                          meetnr,meet1nr,meet2nr,meet3nr,meet4nr,kulnr,kul1nr,kul2nr,kul3nr,kul4nr,talnr,tal1nr,tal2nr,tal3nr,tal4nr,artnr,art1nr,art2nr,tulnr,tul1nr,tul2nr,objectnr,object1nr,object2nr,object3nr,object4nr,osaknr,osak1nr,osak2nr,osak3nr,osak4nr,subjectnr,subject1nr,subject2nr,subject3nr,subject4nr,suundnr,suund1nr,suund2nr,suund3nr,suund4nr,taotnr,taot1nr,taot2nr,taot3nr,taot4nr)
      select pa_tasu.tasunr,
             pa_tasu.summa,
            (if pa_taslist_a.sisse = 0 then pa_tasu.tulumaks else(-1)*pa_tasu.tulumaks endif),
            (if pa_taslist_a.sisse = 0 then pa_tasu.tvtkmaks else(-1)*pa_tasu.tvtkmaks endif),
            (if pa_taslist_a.sisse = 0 then pa_tasu.pensmaks else(-1)*pa_tasu.pensmaks endif),
            (if pa_taslist_a.sisse = 0 then pa_tasu.sotsmaks else(-1)*pa_tasu.sotsmaks endif),
            (if pa_taslist_a.sisse = 0 then pa_tasu.tatkmaks else(-1)*pa_tasu.tatkmaks endif),
             pa_tasu.par1,
             pa_tasu.v_akuu,pa_tasu.v_aasta,pa_tasu.a_apaev,pa_tasu.a_lpaev,pa_tasu.a_akuu,pa_tasu.a_aasta,ymd(pa_tasu.a_aasta,pa_tasu.a_akuu,pa_tasu.a_lpaev),pa_tasu.t_akuu,pa_tasu.t_aasta,
             pa_tasu.tas_s,pa_tasu.vtas_s,
             pa_tasu.lepnr,
             pa_tasu.paryldnr,pa_tasu.vparyldnr,
             pa_tasu.yksusnr,
             pa_tasu.meetnr,pa_paryld_b.meetnr,pa_taslist_a.meetnr,pa_taslist_b.meetnr,pa_yksus.meetnr,
             pa_tasu.kulnr,pa_paryld_b.kulnr,pa_taslist_a.kulnr,pa_taslist_b.kulnr,pa_yksus.kulnr,
             pa_tasu.talnr,pa_paryld_b.talnr,pa_taslist_a.talnr,pa_taslist_b.talnr,pa_yksus.talnr,
             pa_taslist_a.artnr,pa_taslist_b.artnr,pa_yksus.artnr,
             pa_taslist_a.tulnr,pa_taslist_b.tulnr,pa_yksus.tulnr,
             pa_tasu.objectnr,pa_paryld_b.objectnr,pa_taslist_a.objectnr,pa_taslist_b.objectnr,pa_yksus.objectnr,
             pa_tasu.osaknr,pa_paryld_b.osaknr,pa_taslist_a.osaknr,pa_taslist_b.osaknr,pa_yksus.osaknr,
             pa_tasu.subjectnr,pa_paryld_b.subjectnr,pa_taslist_a.subjectnr,pa_taslist_b.subjectnr,pa_yksus.subjectnr,
             pa_tasu.suundnr,pa_paryld_b.suundnr,pa_taslist_a.suundnr,pa_taslist_b.suundnr,pa_yksus.suundnr,
             pa_tasu.taotnr,pa_paryld_b.taotnr,pa_taslist_a.taotnr,pa_taslist_b.taotnr,pa_yksus.taotnr
        from pa_tasu left outer join pa_taslist as pa_taslist_a on pa_tasu.tas_s = pa_taslist_a.tas_s
                     left outer join pa_taslist as pa_taslist_b on pa_tasu.vtas_s = pa_taslist_b.tas_s
                     left outer join pa_yksus on pa_tasu.yksusnr = pa_yksus.yksusnr
                     left outer join pa_paryld as pa_paryld_b on pa_tasu.vparyldnr = pa_paryld_b.paryldnr
        where pa_yksus.skoop=li_skoop and
              pa_tasu.palgaleht is not null and
             (pa_tasu.t_aasta=li_t_aasta and pa_tasu.t_akuu=li_t_akuu);
    call pr_pa_kan_nulli();

    if Length(ls_tulukree)>0 then
      insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"order",deeb,kree,meetnr,kulnr,talnr,objectnr,osaknr,subjectnr,suundnr,taotnr,"info")
        select 'Palk',li_skoop,a_perlop,sum(gl_pa_tasu.tulumaks),ls_order,ls_tulukree,ls_ettekree,
               coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
               coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
               coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
               coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
               coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
               coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
               coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
               coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr,
              'Tulumaks'
        from gl_pa_tasu left outer join pa_yksus on gl_pa_tasu.yksusnr = pa_yksus.yksusnr
        group by pa_meetnr,pa_kulnr,pa_talnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
      insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"order",deeb,kree,meetnr,kulnr,talnr,objectnr,osaknr,subjectnr,suundnr,taotnr,"info")
        select 'Palk',li_skoop,a_perlop,sum(gl_pa_tasu.par1),ls_order,ls_tulukree,ls_ettekree,
               coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
               coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
               coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
               coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
               coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
               coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
               coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
               coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr,
               'Tulumaks alimentidelt'
        from gl_pa_tasu left outer join pa_yksus on gl_pa_tasu.yksusnr = pa_yksus.yksusnr left outer join pa_taslist as pa_taslist_a on gl_pa_tasu.tas_s = pa_taslist_a.tas_s
        where pa_taslist_a.algol='6a' and gl_pa_tasu.par1 is not null
        group by pa_meetnr,pa_kulnr,pa_talnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
    end if;
    if Length(ls_tvtkkree)>0 then
      insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"order",deeb,kree,meetnr,kulnr,talnr,objectnr,osaknr,subjectnr,suundnr,taotnr,"info")
        select 'Palk',li_skoop,a_perlop,sum(gl_pa_tasu.tvtkmaks),ls_order,ls_tvtkkree,ls_ettekree,
               coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
               coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
               coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
               coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
               coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
               coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
               coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
               coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr,
               'Töövõtja töötuskindlustus'
        from gl_pa_tasu left outer join pa_yksus on gl_pa_tasu.yksusnr = pa_yksus.yksusnr
        group by pa_meetnr,pa_kulnr,pa_talnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
    end if;
    if Length(ls_penskree)>0 then
      insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"order",deeb,kree,meetnr,kulnr,talnr,objectnr,osaknr,subjectnr,suundnr,taotnr,"info")
        select 'Palk',li_skoop,a_perlop,sum(gl_pa_tasu.pensmaks),ls_order,ls_penskree,ls_ettekree,
               coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
               coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
               coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
               coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
               coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
               coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
               coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
               coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr,
               'Pensionikindlustus'
        from gl_pa_tasu left outer join pa_yksus on gl_pa_tasu.yksusnr = pa_yksus.yksusnr
        group by pa_meetnr,pa_kulnr,pa_talnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
    end if;
    if Length(ls_sotskree)>0 then
      insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"order",deeb,kree,meetnr,kulnr,talnr,objectnr,osaknr,subjectnr,suundnr,taotnr,"info")
        select 'Palk',li_skoop,a_perlop,sum(gl_pa_tasu.sotsmaks),ls_order,ls_sotskree,ls_ettekree,
               coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
               coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
               coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
               coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
               coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
               coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
               coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
               coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr,
               'Sotsiaalmaks'
        from gl_pa_tasu left outer join pa_yksus on gl_pa_tasu.yksusnr = pa_yksus.yksusnr
        group by pa_meetnr,pa_kulnr,pa_talnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
    end if;
    if Length(ls_tatkkree)>0 then
      insert into gl_pr_kanne_import( tulnud,skoop,kuup,summa,"order",deeb,kree,meetnr,kulnr,talnr,objectnr,osaknr,subjectnr,suundnr,taotnr,"info")
        select 'Palk',li_skoop,a_perlop,sum(gl_pa_tasu.tatkmaks),ls_order,ls_tatkkree,ls_ettekree,
               coalesce(gl_pa_tasu.meetnr,gl_pa_tasu.meet1nr,gl_pa_tasu.meet2nr,gl_pa_tasu.meet3nr,gl_pa_tasu.meet4nr) as pa_meetnr,
               coalesce(gl_pa_tasu.kulnr,gl_pa_tasu.kul1nr,gl_pa_tasu.kul2nr,gl_pa_tasu.kul3nr,gl_pa_tasu.kul4nr) as pa_kulnr,
               coalesce(gl_pa_tasu.talnr,gl_pa_tasu.tal1nr,gl_pa_tasu.tal2nr,gl_pa_tasu.tal3nr,gl_pa_tasu.tal4nr) as pa_talnr,
               coalesce(gl_pa_tasu.objectnr,gl_pa_tasu.object1nr,gl_pa_tasu.object2nr,gl_pa_tasu.object3nr,gl_pa_tasu.object4nr) as pa_objectnr,
               coalesce(gl_pa_tasu.osaknr,gl_pa_tasu.osak1nr,gl_pa_tasu.osak2nr,gl_pa_tasu.osak3nr,gl_pa_tasu.osak4nr) as pa_osaknr,
               coalesce(gl_pa_tasu.subjectnr,gl_pa_tasu.subject1nr,gl_pa_tasu.subject2nr,gl_pa_tasu.subject3nr,gl_pa_tasu.subject4nr) as pa_subjectnr,
               coalesce(gl_pa_tasu.suundnr,gl_pa_tasu.suund1nr,gl_pa_tasu.suund2nr,gl_pa_tasu.suund3nr,gl_pa_tasu.suund4nr) as pa_suundnr,
               coalesce(gl_pa_tasu.taotnr,gl_pa_tasu.taot1nr,gl_pa_tasu.taot2nr,gl_pa_tasu.taot3nr,gl_pa_tasu.taot4nr) as pa_taotnr,
               'Tööandja töötuskindlustus'
        from gl_pa_tasu left outer join pa_yksus on gl_pa_tasu.yksusnr = pa_yksus.yksusnr
        group by pa_meetnr,pa_kulnr,pa_talnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
    end if;


-- Erisoodustuse maksude ettemaksu kontolt maha

    if Length(ls_eristulukree)>0 then
      insert into gl_pr_kanne_import( tulnud,"info",skoop,kuup,summa,"order",deeb,kree,meetnr,kulnr,talnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
        select 'Palk','Erisoodustuste tulumaks lisa 4',li_skoop,a_perlop,sum(pa_erisood.tulumaks),ls_erisorder,ls_eristulukree,ls_ettekree,
               if pa_erisood.meetnr is null then pa_yksus.meetnr else pa_erisood.meetnr endif as pa_meetnr,
               if pa_erisood.kul2nr is null then if pa_erisood.kulnr is null then pa_yksus.kulnr else pa_erisood.kulnr endif else pa_erisood.kul2nr endif as pa_kulnr,
               if pa_erisood.talnr is null then pa_yksus.talnr else pa_erisood.talnr endif as pa_talnr,
               if pa_erisood.objectnr is null then pa_yksus.objectnr else pa_erisood.objectnr endif as pa_objectnr,
               if pa_erisood.osaknr is null then pa_yksus.osaknr else pa_erisood.osaknr endif as pa_osaknr,
               if pa_erisood.subjectnr is null then pa_yksus.subjectnr else pa_erisood.subjectnr endif as pa_subjectnr,
               if pa_erisood.suundnr is null then pa_yksus.suundnr else pa_erisood.suundnr endif as pa_suundnr,
               if pa_erisood.taotnr is null then pa_yksus.taotnr else pa_erisood.taotnr endif as pa_taotnr
        from pa_erisood left outer join pa_yksus on pa_erisood.yksusnr = pa_yksus.yksusnr left outer join lisalist on pa_erisood.lisanr=lisalist.lisanr
        where pa_yksus.skoop=li_skoop and pa_erisood.t_aasta = li_t_aasta and pa_erisood.t_akuu=li_t_akuu and lisalist.objnr=40
        group by pa_meetnr,pa_kulnr,pa_talnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
    end if;
    if Length(ls_eris5tulukree)>0 then
      insert into gl_pr_kanne_import( tulnud,"info",skoop,kuup,summa,"order",deeb,kree,meetnr,kulnr,talnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
        select 'Palk','Erisoodustuste tulumaks lisa 5',li_skoop,a_perlop,sum(pa_erisood.tulumaks),ls_erisorder,ls_eris5tulukree,ls_ettekree,
               if pa_erisood.meetnr is null then pa_yksus.meetnr else pa_erisood.meetnr endif as pa_meetnr,
               if pa_erisood.kul2nr is null then if pa_erisood.kulnr is null then pa_yksus.kulnr else pa_erisood.kulnr endif else pa_erisood.kul2nr endif as pa_kulnr,
               if pa_erisood.talnr is null then pa_yksus.talnr else pa_erisood.talnr endif as pa_talnr,
               if pa_erisood.objectnr is null then pa_yksus.objectnr else pa_erisood.objectnr endif as pa_objectnr,
               if pa_erisood.osaknr is null then pa_yksus.osaknr else pa_erisood.osaknr endif as pa_osaknr,
               if pa_erisood.subjectnr is null then pa_yksus.subjectnr else pa_erisood.subjectnr endif as pa_subjectnr,
               if pa_erisood.suundnr is null then pa_yksus.suundnr else pa_erisood.suundnr endif as pa_suundnr,
               if pa_erisood.taotnr is null then pa_yksus.taotnr else pa_erisood.taotnr endif as pa_taotnr
        from pa_erisood left outer join pa_yksus on pa_erisood.yksusnr = pa_yksus.yksusnr left outer join lisalist on pa_erisood.lisanr=lisalist.lisanr
        where pa_yksus.skoop=li_skoop and pa_erisood.t_aasta = li_t_aasta and pa_erisood.t_akuu=li_t_akuu and lisalist.objnr=50
        group by pa_meetnr,pa_kulnr,pa_talnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
    end if;
    if Length(ls_erissotskree)>0 then
      insert into gl_pr_kanne_import( tulnud,"info",skoop,kuup,summa,"order",deeb,kree,meetnr,kulnr,talnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
        select 'Palk','Erisoodustuste sotsiaalmaks',li_skoop,a_perlop,sum(pa_erisood.sotsmaks),ls_erisorder,ls_erissotskree,ls_ettekree,
               if pa_erisood.meetnr is null then pa_yksus.meetnr else pa_erisood.meetnr endif as pa_meetnr,
               if pa_erisood.kul2nr is null then if pa_erisood.kulnr is null then pa_yksus.kulnr else pa_erisood.kulnr endif else pa_erisood.kul2nr endif as pa_kulnr,
               if pa_erisood.talnr is null then pa_yksus.talnr else pa_erisood.talnr endif as pa_talnr,
               if pa_erisood.objectnr is null then pa_yksus.objectnr else pa_erisood.objectnr endif as pa_objectnr,
               if pa_erisood.osaknr is null then pa_yksus.osaknr else pa_erisood.osaknr endif as pa_osaknr,
               if pa_erisood.subjectnr is null then pa_yksus.subjectnr else pa_erisood.subjectnr endif as pa_subjectnr,
               if pa_erisood.suundnr is null then pa_yksus.suundnr else pa_erisood.suundnr endif as pa_suundnr,
               if pa_erisood.taotnr is null then pa_yksus.taotnr else pa_erisood.taotnr endif as pa_taotnr
        from pa_erisood left outer join pa_yksus on pa_erisood.yksusnr = pa_yksus.yksusnr
        where pa_yksus.skoop=li_skoop and pa_erisood.t_aasta = li_t_aasta and pa_erisood.t_akuu=li_t_akuu
        group by pa_meetnr,pa_kulnr,pa_talnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;
    end if;

  end if;

  end loop skoobid_loop;
  close c_skoobid;

  delete from gl_pr_kanne_import where summa=0;
  update gl_pr_kanne_import set deeb = null where(deeb = 'xxxxxyz' or Length(trim(deeb)) = 0);
  update gl_pr_kanne_import set kree = null where(kree = 'xxxxxyz' or Length(trim(kree)) = 0);
  update gl_pr_kanne_import set meetnr = null where meetnr = 0;
  update gl_pr_kanne_import set kulnr = null where kulnr = 0;
  update gl_pr_kanne_import set talnr = null where talnr = 0;
  update gl_pr_kanne_import set artnr = null where artnr = 0;
  update gl_pr_kanne_import set tulnr = null where tulnr = 0;
  update gl_pr_kanne_import set objectnr = null where objectnr = 0;
  update gl_pr_kanne_import set osaknr = null where osaknr = 0;
  update gl_pr_kanne_import set subjectnr = null where subjectnr = 0;
  update gl_pr_kanne_import set suundnr = null where suundnr = 0;
  update gl_pr_kanne_import set taotnr = null where taotnr = 0;
  update gl_pr_kanne_import set alliknr = null where alliknr = 0;
  update gl_pr_kanne_import set ravonr = null where ravonr = 0;
  update gl_pr_kanne_import set partnr = null where partnr = 0;
  update gl_pr_kanne_import set partnr2 = null where partnr2 = 0;

  insert into gl_pr_kanne( tulnud,id_prog,kuup,summa,"info","order",deeb,partnr,kree,partnr2,asutnr,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,skoop)
    select 'Palk',5,kuup,sum(summa),"info","order",deeb,partnr,kree,partnr2,asutnr,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,skoop
      from gl_pr_kanne_import
      group by kuup,"info","order",deeb,partnr,kree,partnr2,asutnr,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,alliknr,ravonr,skoop;
  delete from gl_pr_kanne where summa=0;

end