SQL Query to List All Active RCR jobs in Siebel

As with any Siebel implementation, one of the day-to-day monitoring tasks for an Administrator are the Siebel components. And the RCR jobs running on the servers tend to be an integral part of the Workflow Process Manager and Workflow Process Batch Manager component requests. So, tracking them becomes useful in situations where there are component issues or some critical job failure.

This SQL can be a reference to list all the RCR jobs by their Execution Server name which is very helpful if you are not assigning any Server Name to the job upon submitting.

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;

As usual, you can feel free to play around and come up with complex outputs with this reference SQL.

Cheers,
Shyam