FUNCTION GetOfficeWorkingTime(p_officeid number) return WORKINGTIME_INFO pipelined is
--показать рабочее время офиса
  l_sql varchar2(1000);
  l_day varchar2(5);
  l_work_from varchar2(5); 
  l_work_till varchar2(5);
begin
  for l_day in 1..7 loop
    l_sql:= 'select '
            ||'wd' || to_char(l_day) ||'_work_from, '
            ||'wd' || to_char(l_day) ||'_work_till '
            ||'from MBL_OFFICES'
            ||'where id = '|| to_char(p_officeid);
                
    dbg(l_sql);
    begin
         execute immediate l_sql into l_work_from, l_work_till;
         dbg('l_day = '||l_day);
         dbg('l_work_from = '||l_work_from);
         dbg('l_work_till = '||l_work_till);
         pipe row(WORKINGTIME_LAYOUT_ITEM(l_day, l_work_from, l_work_till));
    exception
      when others then
       pipe row(WORKINGTIME_LAYOUT_ITEM(l_day, null, null));
    end;
  end loop;  
end;

FUNCTION GetOfficesInfo return OFFICEINFO_LAYOUT pipelined is
--Информация об офисах
cursor c_office is
    SELECT /*+DRIVING_SITE(ZZZ)*/  
       ID, PHONE, LATITUDE, 
      CITY, COUNTRY
    from mbl_offices zzz
    where enabled=1;   
type c_office_type is table of c_office%rowtype;
v_res c_office_type;    
i number;

arr WORKINGTIME_INFO;
begin
 open c_office;
 fetch c_office bulk collect into v_res limit 500;
 if v_res.count > 0  then
 for i in v_res.first..v_res.last loop
     SELECT * BULK COLLECT INTO arr FROM TABLE(GetOfficeWorkingTime(v_res(i).id));

     pipe row(OFFICEINFO_LAYOUT_ITEM(
                                    v_res(i).id ,
                                    v_res(i).phone,
                                    v_res(i).latitude ,
                                    v_res(i).city
                                   ,arr                                  
                                  ));
 end loop;
 end if;
 close c_office;
 SaveActivityLog(5,null,null,null,'OK');
 return;
end;

     SELECT * BULK COLLECT INTO arr FROM TABLE(GetOfficeWorkingTime(v_res(i).id));