BIP – Report to extract BIP scheduled jobs

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