ALTER PROCEDURE "dba"."pv_koond_id_balti"( in a_skoop integer default 0, in a_yksuskood char(16) default '' )
BEGIN
  declare li_id integer;
  declare li_asutnr integer;
  declare li_skoop integer;
  declare li_yksusnr integer;
  declare li_max_yksusnr integer;
  declare ll_pv_id integer;
  declare ll_pv_room_id integer;
  declare li_klassif smallint;
  declare li_address smallint;
  declare li_room smallint;
  declare ls_nimi char(200);
  declare ls_enimi char(200);
  declare ls_reg_nr char(200);
  declare ls_info char(200);
  declare ls_konto char(7);
  declare ld_kuup date;
  declare ldc_kogus numeric(15,3);
  declare ldc_alg_m numeric(15,2);
  declare err_notfound exception for sqlstate value '02000';

  declare c_skoobid dynamic scroll cursor for
    select distinct skoobid."skoop",
                    skoobid."nimi"
      from pv_yksus left outer join skoobid on pv_yksus.skoop=skoobid.skoop
                    left outer join gl_4 on pv_yksus.yksusnr=gl_4."id"
      where gl_4."id" is not null;

  declare c_pv_yksus dynamic scroll cursor for
    select pv_yksus."yksusnr",
           pv_yksus."nimi",
           pv_yksus."skoop"
      from pv_yksus left outer join gl_4 on pv_yksus.yksusnr=gl_4."id"
      where gl_4."id" is not null;

  declare c_pv_matvast dynamic scroll cursor for
    select distinct pv_yksus."asutnr",
                    asulist."nimi",asulist.enimi,
                    asulist.reg_nr
      from pv_yksus left outer join asulist on pv_yksus.asutnr=asulist.asutnr
                    left outer join gl_4 on pv_yksus.yksusnr=gl_4."id"
      where pv_yksus.asutnr is not null and
            gl_4."id" is not null;

-- case li_address
  declare c_address dynamic scroll cursor for
    select distinct case li_address when 11 then pv_yksus."yksusnr" when 1 then pv_yksus.meetnr when 2 then pv_yksus.kulnr when 3 then pv_yksus.talnr when 4 then pv_yksus.objectnr when 5 then pv_yksus.osaknr when 6 then pv_yksus.subjectnr when 7 then pv_yksus.suundnr when 8 then pv_yksus.taotnr end case as pv_id,
                    case li_address when 11 then pv_yksus."nimi" when 1 then meetlist.nimi when 2 then kullist.nimi when 3 then tallist.nimi when 4 then objlist.nimi when 5 then osaklist.nimi when 6 then sublist.nimi when 7 then suundlist.nimi when 8 then taotlist.nimi end case as pv_nimi
      from pv_yksus left outer join gl_4 on pv_yksus.yksusnr=gl_4."id"
                    left outer join meetlist on pv_yksus.meetnr=meetlist.meetnr
                    left outer join kullist on pv_yksus.kulnr=kullist.kulnr
                    left outer join tallist on pv_yksus.talnr=tallist.talnr
                    left outer join objlist on pv_yksus.objectnr=objlist.objectnr
                    left outer join osaklist on pv_yksus.osaknr=osaklist.osaknr
                    left outer join sublist on pv_yksus.subjectnr=sublist.subjectnr
                    left outer join suundlist on pv_yksus.suundnr=suundlist.suundnr
                    left outer join taotlist on pv_yksus.taotnr=taotlist.taotnr
      where pv_id is not null and
            gl_4."id" is not null;

-- case li_room
  declare c_room dynamic scroll cursor for
    select distinct case li_room when 11 then pv_yksus."yksusnr" when 1 then pv_yksus.meetnr when 2 then pv_yksus.kulnr when 3 then pv_yksus.talnr when 4 then pv_yksus.objectnr when 5 then pv_yksus.osaknr when 6 then pv_yksus.subjectnr when 7 then pv_yksus.suundnr when 8 then pv_yksus.taotnr end case as pv_room_id,
                    case li_room when 11 then pv_yksus."nimi" when 1 then meetlist.nimi when 2 then kullist.nimi when 3 then tallist.nimi when 4 then objlist.nimi when 5 then osaklist.nimi when 6 then sublist.nimi when 7 then suundlist.nimi when 8 then taotlist.nimi end case as pv_room_nimi
      from pv_yksus left outer join gl_4 on pv_yksus.yksusnr=gl_4."id"
                    left outer join meetlist on pv_yksus.meetnr=meetlist.meetnr
                    left outer join kullist on pv_yksus.kulnr=kullist.kulnr
                    left outer join tallist on pv_yksus.talnr=tallist.talnr
                    left outer join objlist on pv_yksus.objectnr=objlist.objectnr
                    left outer join osaklist on pv_yksus.osaknr=osaklist.osaknr
                    left outer join sublist on pv_yksus.subjectnr=sublist.subjectnr
                    left outer join suundlist on pv_yksus.suundnr=suundlist.suundnr
                    left outer join taotlist on pv_yksus.taotnr=taotlist.taotnr
      where pv_room_id is not null and
              gl_4."id" is not null and (
              li_address=11 and pv_yksus."yksusnr"=li_id or
              li_address=1 and pv_yksus."meetnr"=li_id or
              li_address=2 and pv_yksus."kulnr"=li_id or
              li_address=3 and pv_yksus."talnr"=li_id or
              li_address=4 and pv_yksus."objectnr"=li_id or
              li_address=5 and pv_yksus."osaknr"=li_id or
              li_address=6 and pv_yksus."subjectnr"=li_id or
              li_address=7 and pv_yksus."suundnr"=li_id or
              li_address=8 and pv_yksus."taotnr"=li_id
              );

  declare c_pv_kanne dynamic scroll cursor for
    select pv_kanne."id",
           if pv_kanne."info" is null or Length(Trim(pv_kanne."info"))=0 then '' else convert_amp(Left(pv_kanne."info",192)) endif,
           pv_kanne."toot_nr",
           pv_kanne.kuup,
           convert_amp(pv_kanne."nimi"),
           if pv_kanne.kogus is null or pv_kanne.kogus=0 then 1 else pv_kanne.kogus endif,
           pv_kanne.alg_m,
           pv_yksus.asutnr,
           pv_yksus.skoop,
           pv_kanne.yksusnr,
           if kontlist.konto is null then '551500' else kontlist.konto endif,
           if kontlist.nimi is null then 'Inventar' else kontlist.nimi endif,
           case li_address when 11 then pv_yksus."yksusnr" when 1 then pv_yksus.meetnr when 2 then pv_yksus.kulnr when 3 then pv_yksus.talnr when 4 then pv_yksus.objectnr when 5 then pv_yksus.osaknr when 6 then pv_yksus.subjectnr when 7 then pv_yksus.suundnr when 8 then pv_yksus.taotnr end case as pv_id,
           case li_room when 11 then pv_yksus."yksusnr" when 1 then pv_yksus.meetnr when 2 then pv_yksus.kulnr when 3 then pv_yksus.talnr when 4 then pv_yksus.objectnr when 5 then pv_yksus.osaknr when 6 then pv_yksus.subjectnr when 7 then pv_yksus.suundnr when 8 then pv_yksus.taotnr end case as pv_room_id
    from pv_kanne left outer join pv_yksus on pv_kanne.yksusnr=pv_yksus.yksusnr
                  left outer join kontlist on pv_yksus.konto=kontlist.konto
                  left outer join gl_4 on pv_yksus.yksusnr=gl_4."id"
    where pv_kanne.objnr2 is null and
          gl_4."id" is not null;

  delete from gl_logi;
  delete from gl_4;

  if a_skoop is null then
    set a_skoop=0
  end if;
  if a_yksuskood is null then
    set a_yksuskood=''
  else
    set a_yksuskood=Left(a_yksuskood,16)
  end if;
  select id_balti_klassif,id_balti_address,id_balti_room into li_klassif,li_address,li_room from tm_rmpt where "id"=1;
  select max(yksusnr)+1 into li_max_yksusnr from pv_yksus;

  insert into gl_4("id")
    select pv_yksus.yksusnr
      from pv_yksus
                    left outer join meetlist on pv_yksus.meetnr=meetlist.meetnr
                    left outer join kullist on pv_yksus.kulnr=kullist.kulnr
                    left outer join tallist on pv_yksus.talnr=tallist.talnr
                    left outer join objlist on pv_yksus.objectnr=objlist.objectnr
                    left outer join osaklist on pv_yksus.osaknr=osaklist.osaknr
                    left outer join sublist on pv_yksus.subjectnr=sublist.subjectnr
                    left outer join suundlist on pv_yksus.suundnr=suundlist.suundnr
                    left outer join taotlist on pv_yksus.taotnr=taotlist.taotnr
                    left outer join asulist on pv_yksus.asutnr=asulist.asutnr
      where (pv_yksus.skoop=a_skoop or a_skoop=0) and (
            li_klassif=0 and Left(pv_yksus.kood,length(a_yksuskood))=a_yksuskood or
            li_klassif=1 and Left(meetlist.meet,length(a_yksuskood))=a_yksuskood or
            li_klassif=2 and Left(kullist.ptk,length(a_yksuskood))=a_yksuskood or
            li_klassif=3 and Left(tallist.osa,length(a_yksuskood))=a_yksuskood or
            li_klassif=4 and Left(objlist."object",length(a_yksuskood))=a_yksuskood or
            li_klassif=5 and Left(osaklist.osak,length(a_yksuskood))=a_yksuskood or
            li_klassif=6 and Left(sublist.subject,length(a_yksuskood))=a_yksuskood or
            li_klassif=7 and Left(suundlist.suund,length(a_yksuskood))=a_yksuskood or
            li_klassif=8 and Left(taotlist.taot,length(a_yksuskood))=a_yksuskood or
            li_klassif=9 and Left(asulist.reg_nr,length(a_yksuskood))=a_yksuskood
            );
  
  insert into gl_logi("text") values ('<?xml version="1.0" encoding="UTF-8"?>');
  insert into gl_logi("text") values ('<InventoryRequest xmlns="http://riigitootaja.ee/inventory">');
  insert into gl_logi("text") values ('<type>LIST</type>');
  insert into gl_logi("text") values ('<orgUnits>');

  open c_skoobid;
  skoobid_loop: loop
    fetch next c_skoobid into li_skoop,ls_nimi;
    if sqlstate = err_notfound then -- kui eof
      leave skoobid_loop
    end if;
    insert into gl_logi("text") values ('<orgUnit>');
    insert into gl_logi("text") values ('<orgUnitId>'+cast(li_max_yksusnr+li_skoop as char)+'</orgUnitId>');
    insert into gl_logi("text") values ('<sapId>'+cast(li_max_yksusnr+li_skoop as char)+'</sapId>');
    if li_skoop=0 then
      set ls_nimi=get_u_masut(0)
    end if;
    insert into gl_logi("text") values ('<name>'+ls_nimi+'</name>');
    insert into gl_logi("text") values ('<type>INSTITUTION</type>');
    insert into gl_logi("text") values ('</orgUnit>');
  end loop skoobid_loop;
  close c_skoobid;

  open c_pv_yksus;
  pv_yksus_loop: loop
    fetch next c_pv_yksus into li_yksusnr,ls_nimi,li_skoop;
    if sqlstate = err_notfound then -- kui eof
      leave pv_yksus_loop
    end if;
    insert into gl_logi("text") values ('<orgUnit>');
    insert into gl_logi("text") values ('<orgUnitId>'+cast(li_yksusnr as char)+'</orgUnitId>');
    insert into gl_logi("text") values ('<sapId>'+cast(li_yksusnr as char)+'</sapId>');
    insert into gl_logi("text") values ('<name>'+ls_nimi+'</name>');
    insert into gl_logi("text") values ('<type>DEPARTMENT</type>');
    insert into gl_logi("text") values ('<parentOrgUnitId>'+cast(li_max_yksusnr+li_skoop as char)+'</parentOrgUnitId>');
    insert into gl_logi("text") values ('</orgUnit>');
  end loop pv_yksus_loop;
  close c_pv_yksus;

  insert into gl_logi("text") values ('</orgUnits>');

  insert into gl_logi("text") values ('<addresses>');
  insert into gl_logi("text") values ('<address>');
  insert into gl_logi("text") values ('<addressId>0</addressId>');
  set ls_enimi=get_u_aadr(0);
  insert into gl_logi("text") values ('<name>'+ls_enimi+'</name>');
  insert into gl_logi("text") values ('<rooms>');
  insert into gl_logi("text") values ('<room>');
  insert into gl_logi("text") values ('<roomId>0</roomId>');
  insert into gl_logi("text") values ('<name>Ruum</name>');
  insert into gl_logi("text") values ('</room>');
  insert into gl_logi("text") values ('</rooms>');
  insert into gl_logi("text") values ('</address>');
  if li_address>0 then
    open c_address;
    address_loop: loop
      fetch next c_address into li_id,ls_nimi;
      if sqlstate = err_notfound then -- kui eof
        leave address_loop
      end if;
      insert into gl_logi("text") values ('<address>');
      insert into gl_logi("text") values ('<addressId>'+cast(li_id as char)+'</addressId>');
      insert into gl_logi("text") values ('<name>'+ls_nimi+'</name>');
      insert into gl_logi("text") values ('<rooms>');
      insert into gl_logi("text") values ('<room>');
      insert into gl_logi("text") values ('<roomId>0</roomId>');
      insert into gl_logi("text") values ('<name>Ruum</name>');
      insert into gl_logi("text") values ('</room>');
      if li_room>0 then
        open c_room;
        room_loop: loop
          fetch next c_room into li_asutnr,ls_nimi;
          if sqlstate = err_notfound then -- kui eof
            leave room_loop
          end if;
          insert into gl_logi("text") values ('<room>');
          insert into gl_logi("text") values ('<roomId>'+cast(li_asutnr as char)+'</roomId>');
          insert into gl_logi("text") values ('<name>'+ls_nimi+'</name>');
          insert into gl_logi("text") values ('</room>');
        end loop room_loop;
        close c_room;
      end if;
      insert into gl_logi("text") values ('</rooms>');
      insert into gl_logi("text") values ('</address>');
    end loop address_loop;
    close c_address;
  end if;
  insert into gl_logi("text") values ('</addresses>');

  insert into gl_logi("text") values ('<persons>');
  open c_pv_matvast;
  pv_matvast_loop: loop
    fetch next c_pv_matvast into li_id,ls_nimi,ls_enimi,ls_reg_nr;
    if sqlstate = err_notfound then -- kui eof
      leave pv_matvast_loop
    end if;
    insert into gl_logi("text") values ('<person>');
    insert into gl_logi("text") values ('<personId>'+cast(li_id as char)+'</personId>');
    insert into gl_logi("text") values ('<sapId>'+cast(li_id as char)+'</sapId>');
    insert into gl_logi("text") values ('<firstName>'+ls_enimi+'</firstName>');
    insert into gl_logi("text") values ('<lastName>'+ls_nimi+'</lastName>');
    insert into gl_logi("text") values ('<personalCode>'+ls_reg_nr+'</personalCode>');
    insert into gl_logi("text") values ('<institutionId>1</institutionId>');
    insert into gl_logi("text") values ('<institution>Institutsioon</institution>');
    insert into gl_logi("text") values ('<departmentId>1</departmentId>');
    insert into gl_logi("text") values ('<department>Department</department>');
    insert into gl_logi("text") values ('<positionId>1</positionId>');
    insert into gl_logi("text") values ('<position>Positsioon</position>');
    insert into gl_logi("text") values ('</person>');
  end loop pv_matvast_loop;
  close c_pv_matvast;
  insert into gl_logi("text") values ('</persons>');

  insert into gl_logi("text") values ('<lists>');
  open c_skoobid;
  skoobid_loop: loop
    fetch next c_skoobid into li_skoop,ls_nimi;
    if sqlstate = err_notfound then -- kui eof
      leave skoobid_loop
    end if;
    insert into gl_logi("text") values ('<list>');
    insert into gl_logi("text") values ('<listId>'+cast(li_max_yksusnr+li_skoop as char)+'</listId>');
    insert into gl_logi("text") values ('<name>'+ls_nimi+'</name>');
    insert into gl_logi("text") values ('</list>');
  end loop skoobid_loop;
  close c_skoobid;
  insert into gl_logi("text") values ('</lists>');

  insert into gl_logi("text") values ('<assets>');
  open c_pv_kanne;
  pv_kanne_loop: loop
    fetch next c_pv_kanne into li_id,ls_info,ls_reg_nr,ld_kuup,ls_nimi,ldc_kogus,ldc_alg_m,li_asutnr,li_skoop,li_yksusnr,ls_konto,ls_enimi,ll_pv_id,ll_pv_room_id;
    if sqlstate = err_notfound then -- kui eof
      leave pv_kanne_loop
    end if;
    if li_asutnr>0 then
      if ll_pv_id is null then
        set ll_pv_id=0;
      end if;
      if ll_pv_room_id is null then
        set ll_pv_room_id=0;
      end if;
      insert into gl_logi("text") values ('<asset>');
      insert into gl_logi("text") values ('<assetId>'+cast(li_id as char)+'</assetId>');
      insert into gl_logi("text") values ('<number>'+cast(li_id as char)+'</number>');
      insert into gl_logi("text") values ('<inventoryNumber>'+ls_reg_nr+'</inventoryNumber>');
      insert into gl_logi("text") values ('<assetClass>');
      insert into gl_logi("text") values ('<code>'+ls_konto+'</code>');
      insert into gl_logi("text") values ('<name>'+ls_enimi+'</name>');
      insert into gl_logi("text") values ('</assetClass>');
      insert into gl_logi("text") values ('<assetType>Vara</assetType>');
      insert into gl_logi("text") values ('<name1>'+ls_nimi+'</name1>');
      if Length(ls_info)>0 then
        insert into gl_logi("text") values ('<name2>'+ls_info+'</name2>');
      end if;
      insert into gl_logi("text") values ('<responsibleEmployeeId>'+cast(li_asutnr as char)+'</responsibleEmployeeId>');
      insert into gl_logi("text") values ('<userEmployeeId>'+cast(li_asutnr as char)+'</userEmployeeId>');
      insert into gl_logi("text") values ('<usageType>SHARED</usageType>');
      insert into gl_logi("text") values ('<amount>'+cast(ldc_kogus as char)+'</amount>');
      insert into gl_logi("text") values ('<unit><code>PCE</code><name>TK</name></unit>');
      insert into gl_logi("text") values ('<capitalizationDate>'+cast(ld_kuup as char)+'+03:00</capitalizationDate>');
      insert into gl_logi("text") values ('<initialValue>'+cast(ldc_alg_m as char)+'</initialValue>');
      insert into gl_logi("text") values ('<responsibleOrgUnitId>'+cast(li_yksusnr as char)+'</responsibleOrgUnitId>');
      insert into gl_logi("text") values ('<addressId>'+cast(ll_pv_id as char)+'</addressId>');
      insert into gl_logi("text") values ('<roomId>'+cast(ll_pv_room_id as char)+'</roomId>');
      insert into gl_logi("text") values ('<listId>'+cast(li_max_yksusnr+li_skoop as char)+'</listId>');
      insert into gl_logi("text") values ('</asset>');
    end if;
  end loop pv_kanne_loop;
  close c_pv_kanne;
  insert into gl_logi("text") values ('</assets>');

  insert into gl_logi("text") values ('</InventoryRequest>');
  select CSCONVERT("text",'UTF-8') from gl_logi order by "id";
END