which table stores the details of scheduling frequencies for BIP scheduled reports?

Gurivi Reddy-Oracle · Jan 16, 2025 12:53PM

I have a requirement from customer needs to build a report which shows the details of scheduled BIP jobs, which are not triggered even scheduled time reached, so for that i need add condition for 'next scheduled run',Can someone please help me ?

MandeepGupta · Jan 16, 2025 1:00PM

https://community.oracle.com/products/oracleanalytics/discussion/24312/which-table-stores-the-details-of-scheduling-frequencies-for-bip-scheduled-reports

Please check the query given below link:

Query to find BIP report schedule details — Cloud Customer Connect

Thanks.

Gurivi Reddy-Oracle · Jan 16, 2025 2:24PM

Thanks Mandeepguptha for your responce .
I gone through the link which you shared but the tables which mentioned there ,i dont have access in BI publisher page ,can you please suggest how this table's can be accessed in cloud.

XMLP_SCHED_DATA,XMLP_SCHED_DELIVERY,XMLP_SCHED_JOB,XMLP_SCHED_OUTPUT

Bhaskar Konar · Jan 16, 2025 2:45PM

https://community.oracle.com/products/oracleanalytics/discussion/24312/which-table-stores-the-details-of-scheduling-frequencies-for-bip-scheduled-reports

Can you please try the following query?

select prop.value report_url, hist.* FROM

fusion_ora_ess.request_property prop,

fusion_ora_ess.request_history hist

WHERE hist.requestid= prop.requestid

AND hist.jobtype = 'JobType://oracle/bip/ess/EssBipJobType'

and prop.name = 'report_url'

Hope this help.

Cheers,

Gurivi Reddy-Oracle · Jan 16, 2025 3:45PM

Hi Bhaskar ,
Thanks for your response.
I ran the query but there are no details of Scheduled Frequency in History table.

MandeepGupta · Jan 18, 2025 3:16PM

https://community.oracle.com/products/oracleanalytics/discussion/comment/62812#Comment_62812

Please check below query:

WITH schedule_history
AS
(
SELECT DISTINCT rh.username scheduled_user_name
,rp.value submitted_job_name
,rh.requestedstart schedule_start_date
,rh.requestedend schedule_end_date
,rh.scheduled next_run_date
,rh.parentrequestid
,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) next_requestid
FROM fusion.ess_request_property rp
,fusion.ess_request_history rh
,fusion.ess_request_property rp_Param
WHERE rp.value LIKE '%XX%TEST%%'
AND rh.requestid = rp.requestid
AND rh.processstart >= TRUNC(SYSDATE,'MONTH')
AND rp_Param.requestid = rp.requestid
AND rp.NAME = 'ujobname'
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)
)
SELECT schedule_history.scheduled_user_name
,schedule_history.submitted_job_name
,schedule_history.schedule_start_date
,schedule_history.schedule_end_date
,rh.scheduled next_schedule_date
FROM schedule_history schedule_history
,fusion.ess_request_history rh
WHERE schedule_history.parentrequestid = rh.parentrequestid
AND schedule_history.next_requestid = rh.requestid