ALTER PROCEDURE "dba"."kp_pa_kinnip"(in a_tas_s integer,in a_objnr integer,in a_vali_objnr integer,in a_user_code integer,in a_perlop date,in a_v_akuu numeric(2),in a_v_aasta numeric(4),in a_skoop integer)
begin

  delete from gl_kp_kanne;

  -- kinnipidamine 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,
           pa_tasu.summa,
           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_plnr on pa_tasu.v_aasta = pa_plnr.v_aasta and pa_tasu.palgaleht=pa_plnr.palgaleht
                   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_taslist_a.tas_s = a_tas_s and
            pa_tasu.v_aasta = a_v_aasta and
            pa_tasu.v_akuu = a_v_akuu and
            pa_yksus.skoop = a_skoop and
           (pa_yksus.kp_objnr = a_objnr or a_vali_objnr = 0);
  call pr_pa_kan_nulli();

  -- lisa kinnipidamine kannetesse
  insert into gl_kp_kanne( kuup,summa,sisse,"info",tulnud,user_code,objnr,asutnr,saajanr,sarve,panknr,viitnr,konto,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr)
    select a_perlop,
           sum(gl_pa_tasu.summa),
           0,
           if pa_taslist_a.detmk = 1 then if pa_paryld_a.saajanr is null then pa_taslist_a.nimi + ' ' + pa_paryld_a."info" else pa_taslist_a.nimi + ' ' + get_asulist_nimi(pa_leping.asutnr) + ' ' + pa_paryld_a."info" endif else pa_taslist_a.nimi endif as pa_info,
           'Kinnip.'+Trim(convert(char(5),a_tas_s)) as pa_tulnud,
           a_user_code,
           a_objnr,
           if pa_paryld_a.asutnr is null then pa_leping.asutnr else pa_paryld_a.asutnr endif as pa_asutnr,
           pa_paryld_a.saajanr,
           if pa_paryld_a.sarve is null or Length(Trim(pa_paryld_a.sarve))='' then if pa_paryld_a.saajanr is null then get_asulist_sarve(if pa_paryld_a.asutnr is null then pa_leping.asutnr else pa_paryld_a.asutnr endif) else get_asulist_sarve(pa_paryld_a.saajanr) endif else Trim(pa_paryld_a.sarve) endif as pa_sarve,
           if pa_paryld_a.sarve is null or Length(Trim(pa_paryld_a.sarve))='' then if pa_paryld_a.saajanr is null then get_asulist_panknr(if pa_paryld_a.asutnr is null then pa_leping.asutnr else pa_paryld_a.asutnr endif) else get_asulist_panknr(pa_paryld_a.saajanr) endif else Trim(pa_paryld_a.panknr) endif as pa_panknr,
           if pa_paryld_a.viitnr is null or Length(Trim(pa_paryld_a.viitnr))='' then if pa_paryld_a.saajanr is null then get_asulist_viitnr_talle(if pa_paryld_a.asutnr is null then pa_leping.asutnr else pa_paryld_a.asutnr endif) else get_asulist_viitnr_talle(pa_paryld_a.saajanr) endif else Trim(pa_paryld_a.viitnr) endif as pa_viitnr,
           if pa_taslist_a.kree is null then pa_taslist_b.kree else pa_taslist_a.kree endif as pa_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,
           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_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_info,pa_tulnud,pa_asutnr,pa_paryld_a.saajanr,pa_sarve,pa_panknr,pa_viitnr,pa_konto,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