BI Report, Business Intelligence Report,, Oracle Fusion, Scheduling

Report Job Parameters Query in Oracle Fusion

Once you have gone live in fusion, you are bound to have many reports scheduled and there will be a day when the most often run report will do something funky and you would like to run a query to fetch you all the report job parameters. this query will serve the purpose:

Query to find the Report Job Parameters

select Job_Name,
requestid,
Seq,
substr(param, 1, instr(param, ‘:’)-1) Param_Name,
substr(param, instr(param, ‘:’)+1, length(param)) Param_Value
from
(
select requestid,
Job_Name,
level Seq,
regexp_substr( translate (param,'{}”‘,’ ‘), ‘[^,]+’, 1, level) Param
FROM (SELECT jobset.requestid ,
jobset.parentrequestid ,
SUBSTR(jobset.definition,INSTR(jobset.definition,’/’,-1,1)+1) ESSJob,
jobset.username,
DECODE(jobset.state, 11,’WARNING’, 2,’READY’, 12,’SUCCEEDED’, 3,’RUNNING’, 13,’PAUSED’, 4,’COMPLETED’, 14,’PENDING_VALIDATION’,
5,’BLOCKED’, 15,’VALIDATION_FAILED’,6,’HOLD’, 16,’SCHEDULE_ENDED’, 7,’CANCELLING’, 17,’FINISHED’, 8,’EXPIRED’,
18,’ERROR_AUTO_RETRY’, 9,’CANCELLED’, 19,’ERROR_MANUAL_RECOVERY’, 10,’ERROR’, STATE) ESSstatus,
jobset.errortype ,
jobset.executable_status,
(NVL(jobset.processend, systimestamp)-jobset.processstart) elapsedtime,
to_char(jobset.submission, ‘DD-MON-YYYY HH24:MI:SS’) submission_UtC,
to_char(jobset.processstart, ‘DD-MON-YYYY HH24:MI:SS’) process_start_utc,
to_char(jobset.processend, ‘DD-MON-YYYY HH24:MI:SS’) process_end_utc,
rp1.value param,
rp3.value Job_Name
FROM fusion_ora_ess.request_history jobset,
fusion_ora_ess.request_property rp1,
fusion_ora_ess.request_property rp3
WHERE 1 = 1
and jobset.PARENTREQUESTID = -1
and rp1.requestid = jobset.requestid
AND rp1.name = ‘report_param_displays’
and rp3.requestid = jobset.requestid
and rp3.name = ‘ujobname’
AND jobset.definition = ‘JobDefinition://oracle/bip/ess/EssBipJob’
AND rp3.value = (” ) )Base_data
CONNECT BY
regexp_substr( Base_data.param , ‘[^,]+’, 1, level) IS NOT NULL
)

Related Posts
Report Security Query – Data Access On Specific Entities
Report Security
RTF- Looping Finite Number Of Times
How To Develop an RTF Template

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

How useful was this post?

Click on a star to rate it!

Average rating 1 / 5. Vote count: 1

No votes so far! Be the first to rate this post.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?