CREATE PROCEDURE "DBA"."pv_koond_id_balti"( )
BEGIN
  declare li_id integer;
  declare li_asutnr integer;
  declare li_yksusnr integer;
  declare ls_nimi char(200);
  declare ls_enimi char(200);
  declare ls_reg_nr 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_pv_yksus dynamic scroll cursor for select pv_yksus."yksusnr",pv_yksus."nimi" from pv_yksus;
  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
      where pv_yksus.asutnr is not null;
  declare c_pv_kanne dynamic scroll cursor for
    select pv_kanne."id",
           pv_kanne."toot_nr",
           pv_kanne.kuup,
           pv_kanne."nimi",
           pv_kanne.kogus,
           pv_kanne.alg_m,
           pv_kanne.asutnr,
           pv_kanne.yksusnr,
           if kontlist.konto is null then 'konto' else kontlist.konto endif,
           if kontlist.nimi is null then 'nimi' else kontlist.nimi endif
    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;
  delete from gl_logi;
  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>');
  insert into gl_logi("text") values ('<orgUnit>');
  insert into gl_logi("text") values ('<orgUnitId>1</orgUnitId>');
  insert into gl_logi("text") values ('<sapId>1</sapId>');
  set ls_nimi=get_u_masut(0);
  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>');
  open c_pv_yksus;
  pv_yksus_loop: loop
    fetch next c_pv_yksus into li_id,ls_nimi;
    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_id as char)+'</orgUnitId>');
    insert into gl_logi("text") values ('<sapId>'+cast(li_id 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>1</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>1</addressId>');
  set ls_nimi=get_u_aadr(0);
  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>1</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>');
  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>');
  insert into gl_logi("text") values ('<list>');
  insert into gl_logi("text") values ('<listId>1</listId>');
  insert into gl_logi("text") values ('<name>Nimekiri</name>');
  insert into gl_logi("text") values ('</list>');
  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_reg_nr,ld_kuup,ls_nimi,ldc_kogus,ldc_alg_m,li_asutnr,li_yksusnr,ls_konto,ls_enimi;
    if sqlstate = err_notfound then -- kui eof
      leave pv_kanne_loop
    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>'+ls_reg_nr+'</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>');
    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)+'</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>1</addressId>');
    insert into gl_logi("text") values ('<roomId>0</roomId>');
    insert into gl_logi("text") values ('<listId>1</listId>');
    insert into gl_logi("text") values ('</asset>');
  end loop pv_kanne_loop;
  close c_pv_kanne;
  insert into gl_logi("text") values ('</assets>');

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