Query or report on ESS request scheduling information

Wim Bos [MLE]

Description

Database views FUSION.ESS_REQUEST_HISTORY as well FUSION.ESS_REQUEST_PROPERTY do not seem to include actual request scheduling information (eg every 2 hours)

Use Case and Business Need

We have so many scheduled ESS processes that we need a way to report a listing of all scheduled ESS processes including scheduling details

More details

Prefer to make it available in a way that scheduling can be added using BI SQL functionality

Original Idea Number: 98f22f7f06

User_HII63

This is highly desirable to identify what is currently scheduled to avoid duplicated runs and efforts as well as identify what is missing that needs to be scheduled.  the report should return all of the parameters selected *in the form they are selected *( not saying argument 1 but the actual field name like BU or Legal Entity etc).  

Sebastien Mariller

Really need that one to check our configs

Lise-Marie

Indeed, report needed

Subhojit31

Hi,

You can get the schedule information from fusion_ora_ess.request_history table using the adhocschedule column.

Suppose, frequency and interval can be fetched using the below query:

Select

substr(substr(replace(erh.adhocschedule,chr(0)),3),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),'FREQ=')+5),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),';')-instr(substr(replace(erh.adhocschedule,chr(0)),3),'FREQ=')-5) ) FREQUENCY,

substr(substr(replace(erh.adhocschedule,chr(0)),3),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),';')+1),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),';</ical-expression>')-instr(substr(replace(erh.adhocschedule,chr(0)),3),';')-1)) INTERVAL

from fusion_ora_ess.request_history erh

Regards,

Subhojit

Subhojit31

Hi,

You can fetch the details from this table - fusion_ora_ess.request_history using Adhocschedule column.

Example query:

Select

substr(substr(replace(erh.adhocschedule,chr(0)),3),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),'FREQ=')+5),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),';')-instr(substr(replace(erh.adhocschedule,chr(0)),3),'FREQ=')-5) ) FREQUENCY,

substr(substr(replace(erh.adhocschedule,chr(0)),3),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),';')+1),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),';</ical-expression>')-instr(substr(replace(erh.adhocschedule,chr(0)),3),';')-1)) INTERVAL

from fusion_ora_ess.request_history erh

Regards,

Subhojit

Subhojit31

Hi,

You can fetch the details from this table - fusion_ora_ess.request_history using Adhocschedule column.

Example query:

Select

substr(substr(replace(erh.adhocschedule,chr(0)),3),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),'FREQ=')+5),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),';')-instr(substr(replace(erh.adhocschedule,chr(0)),3),'FREQ=')-5) ) FREQUENCY,

substr(substr(replace(erh.adhocschedule,chr(0)),3),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),';')+1),
(instr(substr(replace(erh.adhocschedule,chr(0)),3),';</ical-expression>')-instr(substr(replace(erh.adhocschedule,chr(0)),3),';')-1)) INTERVAL

from fusion_ora_ess.request_history erh

Regards,

Subhojit