Job details of a fusion report is something you’ll need to find once you have gone live and there are million reports scheduled and you want to know the various details such as start time and stop time of the job. So in this Fusion Training post, we will have a look at the sample query.
In case you are looking for query to find out the parameters with which the report was submitted, try this post:
Fusion Report Job Parameters
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Job details Query
This query will fetch you following details of an ESS Job you have already submitted:
1. Job Name
2. Report Path
3. Process Start Date
4. Process End date
5. ESS Status
6. State
7. State Change Time
8. Request Id
9. Job Set Name
10. Scheduled
11. Request Start Date
12. Request End Date
Select rp4.value job_name ,
rp1.value report_path,
TO_CHAR(jobset.processstart,’MM/DD/YYYY HH24:MI:SS’) process_start_date ,
TO_CHAR(jobset.processend,’MM/DD/YYYY HH24:MI:SS’) process_end_date,
DECODE(jobset.state, 2,’READY’, 3,’RUNNING’, 4,’COMPLETED’,
5,’BLOCKED’, 6,’HOLD’, 7,’CANCELLING’,
8,’EXPIRED’, 9,’CANCELLED’, 10,’ERROR’,
11,’WARNING’, 12,’SUCCEEDED’, 13,’PAUSED’,
14,’PENDING_VALIDATION’, 15,’VALIDATION_FAILED’, 16,’SCHEDULE_ENDED’,
17,’FINISHED’, 18,’ERROR_AUTO_RETRY’, 19,’ERROR_MANUAL_RECOVERY’, STATE) ess_status,
jobset.state,
TO_CHAR(jobset.STATECHANGETIME,’MM/DD/YYYY HH24:MI:SS’) state_change_time,
jobset.requestid ,
jobset.name,
TO_CHAR(jobset.SCHEDULED, ‘MM/DD/YYYY HH24:MI:SS’) scheduled ,
TO_CHAR(jobset.SUBMISSION,’MM/DD/YYYY HH24:MI:SS’) submission_date,
TO_CHAR(jobset.REQUESTEDSTART,’MM/DD/YYYY HH24:MI:SS’) requested_start_date,
TO_CHAR(jobset.requestedend,’MM/DD/YYYY HH24:MI:SS’) requested_end_date
FROM
fusion_ora_ess.request_property rp1,
fusion_ora_ess.request_property rp4,
fusion_ora_ess.request_history jobset
WHERE 1 = 1
and jobset.DEFINITION= ‘JobDefinition://oracle/bip/ess/EssBipJob’
and rp1.name = ‘report_url’
and rp1.value like ‘%<Your Report Name Here>%’
and rp1.REQUESTID = jobset.REQUESTID
and rp4.REQUESTID = jobset.REQUESTID
and rp4.name = ‘ujobname’
and jobset.PARENTREQUESTID != -1
and jobset.state <> 9
order by jobset.PROCESSend asc
Related Posts
Fusion Report Job Parameters
How to develop a report
OTBI Vs BI
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com