code
with t as (
select /*+ use_concat index(C C_ERR_PROT_DOPER)*/
substr(C.NETID,1,25) NETID,
T.CODE USER_CODE,
substr(decode(C.ERR_ID,35,'операционный день',5,'отработала процедура',36,'ошибка опердня',37,'ошибка процедуры'),1,20) ACT,
to_char(C.CORRECTDT,'DD.MM HH24:MI:SS') CTIME,
to_date (regexp_substr(substr(decode(C.ERR_ID,35,D2.PAR_TXT,36,D2.PAR_TXT,D.PAR_TXT),1,255),'\d\d:\d\d:\d\d'),'hh24:mi:ss') start_time,
to_date (to_char(c.correctdt,'HH24:MI:SS'),'HH24:MI:SS') end_time,
substr(decode(C.ERR_ID,35,D.PAR_TXT,36,D.PAR_TXT,D1.PAR_TXT),1,8) DEP,
to_date(substr(decode(C.ERR_ID,35,D1.PAR_TXT,36,D1.PAR_TXT,D2.PAR_TXT),1,8),'dd.mm.rr') DOPER,
substr(decode(C.ERR_ID,35,'0',5,'0','1'),1,1) ERRFL,
substr(decode(C.ERR_ID,35,D2.PAR_TXT,36,D2.PAR_TXT,D.PAR_TXT),1,255) ACTION1,
case when substr(decode(C.ERR_ID,35,D2.PAR_TXT,36,D2.PAR_TXT,D.PAR_TXT),1,255) in ('регламент 1','регламент 2','регламент 3','регламент 4','закрытие','открытие') then 1 else 0 end sog,
substr(decode(C.ERR_ID,35,D2.PAR_TXT,36,D2.PAR_TXT,D.PAR_TXT),256,255) ACTION2,
substr(D3.PAR_TXT,1,255) ERRTXT
from C_ERR_PROT C, C_ERR_PROT_DTL D, C_ERR_PROT_DTL D1, C_ERR_PROT_DTL D2, C_ERR_PROT_DTL D3, C_USR T
where C.ERR_ID in (5,35,36,37,5608)
and D.ID(+)=C.ID and D.PAR_NUM(+)=0
and D1.ID(+)=C.ID and D1.PAR_NUM(+)=1
and D2.ID(+)=C.ID and D2.PAR_NUM(+)=2
and D3.ID(+)=C.ID and D3.PAR_NUM(+)=3
and T.ID=C.USER_ID+0
and substr(decode(C.ERR_ID,35,D2.PAR_TXT,36,D2.PAR_TXT,D.PAR_TXT),1,255) <>'P_PkgSvd' --ошибочные, не нужны в журнале
and C.CORRECTDT between to_date('&cFromTime','dd.mm.rr hh24:mi') and to_date('&cToTime','dd.mm.rr hh24:mi')
and c.doper between &doper and &doper2
order by DEP, C.ID
),tt as (
SELECT act,ctime,dep,doper,errfl,action1,ACTION2,ERRTXT,
to_char(start_time,'dd.mm.rr hh24:mi') start_time,
case when sog = 1 then null else to_char(end_time,'HH24:MI:SS') end end_time,
case when end_time-start_time<0 then 24*60*60 else 0 end date_inc,
CAST((CAST(end_time+case when end_time-start_time<0 then 1 else 0 end AS TIMESTAMP) - CAST(start_time AS TIMESTAMP)) AS INTERVAL DAY TO SECOND (0)) DATE_DIFF,
extract( hour from CAST(end_time AS TIMESTAMP) - CAST(start_time AS TIMESTAMP) )*60*60+
extract( minute from CAST(end_time AS TIMESTAMP) - CAST(start_time AS TIMESTAMP) )*60+
extract( second from CAST(end_time AS TIMESTAMP) - CAST(start_time AS TIMESTAMP)) elapsed_seconds,
sog,
sum(sog) over (order by dep,ctime) grp_id
FROM t
),ttt as (
SELECT act,ctime,dep,doper,action1,action2,errtxt,errfl,
start_time,
end_time,
case when sog = 1
then cast(numtodsinterval(sum(elapsed_seconds+date_inc) over (partition by grp_id),'SECOND') as interval day(2) to second(0))
else date_diff
end work_time,
sog,
grp_id,
max(case when action1 in ('регламент 1','регламент 2','регламент 3','регламент 4','закрытие','открытие')
then action1
else null
end) over (partition by grp_id) grp_name
from tt
) SELECT ttt.*
FROM ttt
where grp_name = 'регламент 1'
order by doper,grp_name,dep,grp_id,sog desc,ctime,start_time