create or replace procedure update_scp_stat_mininfo
is
begin
insert into i_scp_stat_mininfo (caps,scpid,timezone,datetime)
select max(caps),scpid,timezone, datetime from (
select sum(caps)caps,datetime times,scpid scpid,trunc(substr(datetime,15,2)/5) as timezone,
(substr(datetime,0,13)) as datetime from i_scp_stat
where to_char(replace(substr(datetime,0,10),'-','')) >= (select to_char(sysdate-1,'yyyymmdd') from dual)
and to_char(replace(substr(datetime,0,10),'-','')) < (select to_char(sysdate,'yyyymmdd') from dual)
group by datetime,scpid
)
group by scpid,timezone,datetime;
commit;
exception
when others then
dbms_output.put_line(substr(sqlerrm,1,200));
return;
end update_scp_stat_mininfo;