Build Report to follow Integration Jobs activity

with TASK_DEFS
as (
select
JD.NAME JD_NAME,
JD.UUID JD_UUID,
TD.NAME TD_NAME,
TD.UUID TD_UUID,
TD.TASK_GLOBAL_ORDER TD_GLOBAL_ORDER,
TD.TASK_QNAME TD_QNAME,
TD.TASK_GLOBAL_ORDER || ':' || TD.TASK_QNAME TD_ORDERED_NAME,
TD.TASK_LEVEL TD_LEVEL,
TD.CLASSNAME TD_CLASSNAME
from
(
select
TD.NAME,
TD.UUID,
TD.O_JOBDEF,
TD.CLASSNAME CLASSNAME,
REPLACE(RPAD(SYS_CONNECT_BY_PATH(LPAD(TD.ORD_POS, 3, '0'), '//'), 5*7, '//000'), '//', '.') TASK_GLOBAL_ORDER,
LEVEL TASK_LEVEL,
SYS_CONNECT_BY_PATH(TD.NAME, '//') TASK_QNAME
from
MTA_TASK_DEF TD
--inner join MTA_JOB_DEF JD on TD.O_JOBDEF = JD.UUID
connect by TD.R_TASKDEF = PRIOR TD.UUID
start with TD.R_TASKDEF is null
) TD
inner join MTA_JOB_DEF JD on TD.O_JOBDEF = JD.UUID
),
TASK_LOGS
as (
select
IL.LOADID LOADID,
IB.BATCHID BATCHID,
JI.UUID JI_UUID,
JL.UUID JL_UUID,
JL.START_DATE JL_START_DATE,
TL.R_TASKDEF TD_UUID,
TL.UUID TL_UUID,
TL.START_DATE TL_START_DATE,
TL.END_DATE TL_END_DATE,
extract(DAY from (TL.END_DATE - TL.START_DATE)) *24 * 60 * 60 * 1000 +
extract(HOUR from (TL.END_DATE - TL.START_DATE)) * 60 * 60* 1000 +
extract(MINUTE from (TL.END_DATE - TL.START_DATE)) * 60 * 1000 +
extract(SECOND from (TL.END_DATE - TL.START_DATE)) * 1000
TL_DURATION,
TL.ROW_COUNT TL_ROW_COUNT,
TL.SEL_COUNT TL_SEL_COUNT,
TL.INS_COUNT TL_INS_COUNT,
TL.MRG_COUNT TL_MRG_COUNT,
TL.UPD_COUNT TL_UPD_COUNT,
TL.DEL_COUNT TL_DEL_COUNT
from
MTA_INTEG_LOAD IL
inner join MTA_INTEG_BATCH IB on IL.UUID = IB.R_INTEGLOAD
inner join MTA_JOB_INSTANCE JI
on (JI.UUID = IB.R_JOBINSTANCE)
inner join MTA_JOB_LOG JL
on (JL.R_JOBINSTANCE = JI.UUID)
inner join MTA_TASK_LOG TL
on (TL.O_JOBLOG = JL.UUID)
)
select
TL.LOADID,
TL.BATCHID,
TL.JL_START_DATE,
TD.JD_NAME,
TD.TD_UUID,
TD.TD_ORDERED_NAME,
TD.TD_CLASSNAME,
TL.TL_START_DATE,
TL.TL_END_DATE,
TL.TL_DURATION,
TL.TL_ROW_COUNT,
TL.TL_SEL_COUNT,
TL.TL_INS_COUNT,
TL.TL_MRG_COUNT,
TL.TL_UPD_COUNT,
TL.TL_DEL_COUNT,
TD.TD_QNAME,
TD.TD_GLOBAL_ORDER
from
TASK_LOGS TL
inner join TASK_DEFS TD on TL.TD_UUID = TD.TD_UUID
order by TL.BATCHID, TL.JL_START_DATE, TD.TD_ORDERED_NAME