ALTER PROCEDURE "dba"."kp_pa_maks"(in a_maks char(8),in a_objnr integer,in a_user_code integer,in a_perlop date,in a_t_akuu numeric(2),in a_t_aasta numeric(4),in a_skoop integer)
begin
  declare li_asutnr integer;
  declare ls_konto char(7);
  declare li_sotsartnr integer;
  declare li_tatkartnr integer;
  declare ls_sarve char(30);
  declare ls_viitnr char(35);
  declare ls_info char(200);
  declare ls_sotsart char(16);
  declare ls_tatkart char(16);

  delete from gl_kp_kanne;

  case a_maks
    when 'tulumaks' then
      set ls_info='Tulumaks';
      select k_tulukree into ls_konto from pa_konfm where pa_konfm.id = 1;
    when 'tvtkmaks' then
      set ls_info='Töövõtja töötuskindlustus';
      select k_tvtkkree into ls_konto from pa_konfm where pa_konfm.id = 1;
    when 'pensmaks' then
      set ls_info='Kogumispension';
      select k_penskree into ls_konto from pa_konfm where pa_konfm.id = 1;
    when 'tatkmaks' then
      set ls_info='Tööandja töötuskindlustus';
      select k_tatkkree into ls_konto from pa_konfm where pa_konfm.id = 1;
    when 'sotsmaks' then
      set ls_info='Sotsiaalmaks';
      select k_sotskree into ls_konto from pa_konfm where pa_konfm.id = 1;
  end case
  ;
  select first asulist.asutnr into li_asutnr from asulist where asulist.nimi = 'Maksu- ja Tolliamet';
  if li_asutnr is null or li_asutnr = 0 then
    set li_asutnr=existid_asulist('','Maksu- ja Tolliamet','')
  end if;
  select if Length(Trim(k_ettedeeb))=0 or k_ettedeeb is null then ls_konto else k_ettedeeb endif,pa_konfm.k_sotsart,pa_konfm.k_tatkart,Trim(pa_konfm.k_tulusarve) into ls_konto,ls_sotsart,ls_tatkart,ls_sarve from pa_konfm where pa_konfm.id = 1;
  set ls_viitnr=get_tuluviitnr(a_skoop);
  select first artlist.artnr into li_sotsartnr from artlist where Trim(artlist.art) = ls_sotsart;
  select first artlist.artnr into li_tatkartnr from artlist where Trim(artlist.art) = ls_tatkart;
  if ls_sarve='' then
    set ls_sarve=null
  end if;
  if ls_konto='' then
    set ls_konto=null
  end if;

  -- maksude vahekoond
  delete from gl_pa_tasu;
  insert into gl_pa_tasu(tasunr,summa,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,palgaleht,
                        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,
        (case a_maks when 'tulumaks' then(if pa_taslist_a.sisse = 0 then pa_tasu.tulumaks else 0-pa_tasu.tulumaks endif)+(if pa_taslist_a.algol = '6a' then pa_tasu.par1 else 0 endif)
                     when 'tvtkmaks' then(if pa_taslist_a.sisse = 0 then pa_tasu.tvtkmaks else 0-pa_tasu.tvtkmaks endif)
                     when 'pensmaks' then(if pa_taslist_a.sisse = 0 then pa_tasu.pensmaks else 0-pa_tasu.pensmaks endif)
                     when 'tatkmaks' then(if pa_taslist_a.sisse = 0 then pa_tasu.tatkmaks else 0-pa_tasu.tatkmaks endif)
                     when 'sotsmaks' then(if pa_taslist_a.sisse = 0 then pa_tasu.sotsmaks else 0-pa_tasu.sotsmaks endif)
        end),
           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.palgaleht,
           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_paryld as pa_paryld_b on pa_tasu.vparyldnr=pa_paryld_b.paryldnr
                   left outer join pa_yksus on pa_tasu.yksusnr=pa_yksus.yksusnr
      where pa_tasu.t_aasta = a_t_aasta and pa_tasu.t_akuu = a_t_akuu and pa_tasu.palgaleht is not null and pa_yksus.skoop = a_skoop and (pa_yksus.kp_objnr = a_objnr or pa_yksus.kp_maksud=1);
  call pr_pa_kan_nulli();

  insert into gl_kp_kanne( kuup,summa,sisse,"info",tulnud,user_code,objnr,asutnr,sarve,viitnr,konto,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
    select a_perlop,
           sum(gl_pa_tasu.summa),
           0,
           ls_info,
           'Maks:'+a_maks,
           a_user_code,
           a_objnr,
           li_asutnr,
           ls_sarve,
           ls_viitnr,
           ls_konto,
           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) or a_maks in ('tulumaks','tvtkmaks','pensmaks')) then coalesce(gl_pa_tasu.artnr,gl_pa_tasu.art1nr,gl_pa_tasu.art2nr) else if a_maks='sotsmaks' then li_sotsartnr else li_tatkartnr endif 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_taslist as pa_taslist_b on gl_pa_tasu.vtas_s = pa_taslist_b.tas_s
                      left outer join pa_paryld as pa_paryld_a on gl_pa_tasu.paryldnr = pa_paryld_a.paryldnr
                      left outer join pa_leping on gl_pa_tasu.lepnr = pa_leping.lepnr
      group by pa_meetnr,pa_kulnr,pa_talnr,pa_artnr,pa_tulnr,pa_objectnr,pa_osaknr,pa_subjectnr,pa_suundnr,pa_taotnr;


  delete from gl_kp_kanne where gl_kp_kanne.summa = 0;
end