I have designed below query to extract the schedule of a BIP submitted jobs which is not available directly. Replace the job name attribute with your job name or run it for all:
/*******************************************************************************************
Job Status - HRC_LOADER_ESS_STATE
1 - Wait
2 - Ready
3 - Running
4 - Completed
5 - Blocked
6 - Hold
7 - Canceling
8 - Expired
9 - Canceled
10 - Error
11 - Warning
12 - Succeeded
13 - Paused
14 - Pending Validation
15 - Validation Failed
16 - Schedule Ended
18 - Error Auto Retry
19 - Error Manual recovery
*******************************************************************************************/
WITH schedule_history
AS
(
SELECT DISTINCT rh.username scheduled_user_name
,rp.value submitted_job_name
,rh.processstart last_job_start_date
,rh.processend last_job_end_date
-- ,rh.elapsedtime elapsedtime
,flv.meaning last_job_status
,rh.requestedstart schedule_start_date
,rh.requestedend schedule_end_date
,rh.scheduled next_run_date
,rh.parentrequestid
,rh.requestid
,SUBSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
),3
)
,(INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
),3
)
,'FREQ=')+5
)
,(INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
),3
)
,';'
)
- INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
),3
)
,'FREQ=')-5
)
) schedule_frequency
,SUBSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
),3
)
,(INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
),3
),';'
)+1),(INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
),3
),';</ical-expression>'
)-INSTR(SUBSTR(REPLACE(rh.adhocschedule,chr(0)
),3
),';'
)-1
)
) schedule_interval
,(SELECT max(rh2.requestid)
FROM fusion.ess_request_history rh2
WHERE rh2.parentrequestid = rh.parentrequestid
AND rh.username = rh2.username
AND rh2.state = 1) next_requestid
FROM fusion.ess_request_property rp
,fusion.request_history rh
,fusion.ess_request_property rp_Param
,fusion.fnd_lookup_values flv
WHERE rp.value LIKE '%TEST%NAME%'
AND rh.requestid = rp.requestid
AND rh.processstart >= TRUNC(SYSDATE,'MONTH')
AND rp_Param.requestid = rp.requestid
AND rp.NAME = 'ujobname'
AND flv.lookup_type = 'HRC_LOADER_ESS_STATE'
AND flv.language = USERENV('LANG')
AND flv.lookup_code = rh.state
AND rh.requestid = (select max(rh2.requestid) from fusion.ess_request_history rh2
where rh2.parentrequestid = rh.parentrequestid
and rh.USERNAME = rh2.USERNAME
AND rh2.state <> 1) -- 1 is wait
)
SELECT schedule_history.scheduled_user_name
,MAX(DECODE (rp.name, 'report_url', value)) report_url
,MAX(DECODE (rp.name, 'layout_name', value)) layout_name
,schedule_history.submitted_job_name
,schedule_history.schedule_start_date
,schedule_history.schedule_end_date
,schedule_history.schedule_frequency
,schedule_history.schedule_interval
,rh.scheduled next_schedule_date
,flv.meaning next_job_status
,MAX(DECODE (rp.name, 'CONTROL_XML', value)) output_details
,MAX(DECODE (rp.name, 'OUTPUT_FORMAT', value)) output_format
,MAX(DECODE (rp.name, 'save_data', value)) output_save_data
,MAX(DECODE (rp.name, 'email_notification', value)) email_notification
,MAX(DECODE (rp.name, 'is_bursting', value)) bursting_used
,MAX(DECODE (rp.name, 'notify_email_when_failed', value)) notify_email_when_failed
,MAX(DECODE (rp.name, 'notify_email_when_skipped', value)) notify_email_when_skipped
,MAX(DECODE (rp.name, 'notify_email_when_success', value)) notify_email_when_success
,MAX(DECODE (rp.name, 'notify_email_when_warning', value)) notify_email_when_warning
,MAX(DECODE (rp.name, 'notify_to', value)) notify_to
,MAX(DECODE (rp.name, 'report_param_displays', value)) report_param_displays
,MAX(DECODE (rp.name, 'report_params', value)) report_params
,schedule_history.last_job_start_date last_job_start_date
,schedule_history.last_job_end_date last_job_end_date
-- ,schedule_history.elapsedtime last_job_elapsedtime
,schedule_history.last_job_status last_job_status
,schedule_history.parentrequestid parent_request_id
,schedule_history.requestid last_job_request_id
,rh.requestid scheduled_job_request_id
FROM schedule_history schedule_history
,fusion.request_history rh
,fusion.ess_request_property rp
,fusion.fnd_lookup_values flv
WHERE schedule_history.parentrequestid = rh.parentrequestid
AND schedule_history.next_requestid = rh.requestid
AND flv.lookup_type = 'HRC_LOADER_ESS_STATE'
AND flv.language = USERENV('LANG')
AND flv.lookup_code = rh.state
AND rp.requestid = rh.requestid
GROUP BY schedule_history.scheduled_user_name
,schedule_history.submitted_job_name
,schedule_history.schedule_start_date
,schedule_history.schedule_end_date
,schedule_history.schedule_frequency
,schedule_history.schedule_interval
,rh.scheduled
,flv.meaning
,schedule_history.last_job_start_date
,schedule_history.last_job_end_date
-- ,schedule_history.elapsedtime
,schedule_history.last_job_status
,schedule_history.parentrequestid
,schedule_history.requestid
,rh.requestid