SELECT
PAR_REQ_ID "Job Id",
rpt_interval||' '||rpt_uom "Frequency",
STATUS,
ACTL_START_DT,
EXEC_SRVR_NAME
FROM
SIEBEL.S_SRM_REQUEST
WHERE
REQ_TYPE_CD = 'RPT_INSTANCE'
AND STATUS = 'ACTIVE'
AND PAR_REQ_ID in
(
select
par.row_id
from
siebel.S_SRM_REQUEST par,
siebel.S_SRM_ACT_PARAM aparam ,
siebel.S_SRM_REQUEST child ,
siebel.S_SRM_REQ_PARAM param
where
par.row_id = child.par_req_id
and par.req_type_cd = 'RPT_PARENT'
and par.STATUS = 'ACTIVE'
and child.status in ('QUEUED','ACTIVE')
and par.row_id = param.req_id
and child.req_type_cd = 'RPT_INSTANCE'
and param.ACTPARAM_ID = aparam.row_id
and aparam.NAME = 'Workflow Process Name'
group by par.row_id,param.value, par.rpt_interval||' '||par.rpt_uom
)
order by ACTL_START_DT desc;