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

code

Report Page