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