ALTER PROCEDURE "dba"."dk_pa_kan"(in a_objnr integer,in a_kuup date,in a_user_code integer)
begin
  declare li_spank integer;
  declare li_t_aasta numeric(4);
  declare li_t_akuu numeric(2);
  declare li_t_paev integer;
  declare li_v_aasta numeric(4);
  declare li_v_akuu numeric(2);
  declare ls_tulnud char(100);
  declare li_skoop integer;
  declare ls_djkonto char(7);
  set li_t_aasta=Years(a_kuup);
  set li_t_akuu=Month(a_kuup);
  select dk_obj.skoop,dk_obj.djkonto,dk_obj.spank into li_skoop,ls_djkonto,li_spank from dk_obj where dk_obj.objnr = a_objnr;
  delete from gl_dk_kanne;
  set ls_tulnud='Palk';

--    if ls_sarve is null or Length(trim(ls_sarve)) = 0 then
--      if li_saajanr is null then
--        select asulist.panknr,asulist.sarve into li_panknr,ls_sarve from asulist where asulist.asutnr = li_asutnr
--      else
--        select asulist.panknr,asulist.sarve into li_panknr,ls_sarve from asulist where asulist.asutnr = li_saajanr
--      end if
--    end if;


-- lisa pangad
-- tasumiseks päeva järgi
-- memorder perlop järgi


  insert into gl_dk_kanne( kuup,summa,summa2,sisse,"info",asutnr,taiendus,konto,meetnr,kulnr,talnr,artnr,tulnr,objectnr,osaknr,subjectnr,suundnr,taotnr,objnr,tulnud,user_code)
      select a_kuup,
             sum(pa_tasu.summa),
             0,
             li_spank,
             pa_taslist_a.nimi,
             pa_leping.asutnr,
            (if li_spank=1 then pa_taslist_a.deeb else pa_taslist_a.kree endif) as pa_djkonto,
            (if li_spank=0 then pa_taslist_a.deeb else pa_taslist_a.kree endif) as pa_konto,
            (if pa_tasu.meetnr is null then (if pa_paryld_b.meetnr is null then(if pa_taslist_a.meetnr is null then (if pa_taslist_b.meetnr is null or pa_taslist_b.samkul=0 then pa_yksus.meetnr else pa_taslist_b.meetnr endif) else pa_taslist_a.meetnr endif) else pa_paryld_b.meetnr endif) else pa_tasu.meetnr endif) as kl_meetnr,
            (if pa_tasu.kulnr is null then (if pa_paryld_b.kulnr is null then (if pa_taslist_a.kulnr is null then (if pa_taslist_b.kulnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.kulnr is null then 0 else pa_yksus.kulnr endif) else pa_taslist_b.kulnr endif) else pa_taslist_a.kulnr endif) else pa_paryld_b.kulnr endif) else pa_tasu.kulnr endif) as kl_kulnr,
            (if pa_tasu.talnr is null then (if pa_paryld_b.talnr is null then (if pa_taslist_a.talnr is null then (if pa_taslist_b.talnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.talnr is null then 0 else pa_yksus.talnr endif) else pa_taslist_b.talnr endif) else pa_taslist_a.talnr endif) else pa_paryld_b.talnr endif) else pa_tasu.talnr endif) as kl_talnr,
            (if pa_taslist_a.artnr is null then (if pa_taslist_b.artnr is null then (if pa_yksus.artnr is null then 0 else pa_yksus.artnr endif) else pa_taslist_b.artnr endif) else pa_taslist_a.artnr endif) as kl_artnr,
            (if pa_taslist_a.tulnr is null then(if pa_taslist_b.tulnr is null then (if pa_yksus.tulnr is null then 0 else pa_yksus.tulnr endif) else pa_taslist_b.tulnr endif) else pa_taslist_a.tulnr endif) as kl_tulnr,
            (if pa_tasu.objectnr is null then (if pa_paryld_b.objectnr is null then (if pa_taslist_a.objectnr is null then (if pa_taslist_b.objectnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.objectnr is null then 0 else pa_yksus.objectnr endif) else pa_taslist_b.objectnr endif) else pa_taslist_a.objectnr endif) else pa_paryld_b.objectnr endif) else pa_tasu.objectnr endif) as kl_objectnr,
            (if pa_tasu.osaknr is null then (if pa_paryld_b.osaknr is null then (if pa_taslist_a.osaknr is null then (if pa_taslist_b.osaknr is null or pa_taslist_b.samkul=0 then (if pa_yksus.osaknr is null then 0 else pa_yksus.osaknr endif) else pa_taslist_b.osaknr endif) else pa_taslist_a.osaknr endif) else pa_paryld_b.osaknr endif) else pa_tasu.osaknr endif) as kl_osaknr,
            (if pa_tasu.subjectnr is null then (if pa_paryld_b.subjectnr is null then (if pa_taslist_a.subjectnr is null then (if pa_taslist_b.subjectnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.subjectnr is null then 0 else pa_yksus.subjectnr endif) else pa_taslist_b.subjectnr endif) else pa_taslist_a.subjectnr endif) else pa_paryld_b.subjectnr endif) else pa_tasu.subjectnr endif) as kl_subjectnr,
            (if pa_tasu.suundnr is null then (if pa_paryld_b.suundnr is null then (if pa_taslist_a.suundnr is null then (if pa_taslist_b.suundnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.suundnr is null then 0 else pa_yksus.suundnr endif) else pa_taslist_b.suundnr endif) else pa_taslist_a.suundnr endif) else pa_paryld_b.suundnr endif) else pa_tasu.suundnr endif) as kl_suundnr,
            (if pa_tasu.taotnr is null then (if pa_paryld_b.taotnr is null then (if pa_taslist_a.taotnr is null then (if pa_taslist_b.taotnr is null or pa_taslist_b.samkul=0 then (if pa_yksus.taotnr is null then 0 else pa_yksus.taotnr endif) else pa_taslist_b.taotnr endif) else pa_taslist_a.taotnr endif) else pa_paryld_b.taotnr endif) else pa_tasu.taotnr endif) as kl_taotnr,
             a_objnr,
             ls_tulnud,
             a_user_code
       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_a on pa_tasu.paryldnr = pa_paryld_a.paryldnr
                  left outer join pa_paryld as pa_paryld_b on pa_tasu.vparyldnr = pa_paryld_b.paryldnr
                  left outer join pa_leping on pa_tasu.lepnr = pa_leping.lepnr
                  left outer join pa_yksus on pa_tasu.yksusnr = pa_yksus.yksusnr
       where pa_djkonto = ls_djkonto and
             pa_plnr.v_aasta = li_v_aasta and pa_plnr.v_akuu = li_v_akuu and
             pa_yksus.skoop = li_skoop
       group by pa_taslist_a.nimi,pa_leping.asutnr,pa_djkonto,pa_konto,kl_meetnr,kl_kulnr,kl_talnr,kl_artnr,kl_tulnr,kl_objectnr,kl_osaknr,kl_subjectnr,kl_suundnr,kl_taotnr;

  delete from gl_dk_kanne where gl_dk_kanne.summa = 0;
end