We’ll go over the work description for Oracle Fusion ESS Job. We can run our Reports, Packages, and Scripts in Oracle Fusion applications with the help of the ESS job. Concurrent Program in Oracle apps r12 is the same as ESS jobs in Oracle Fusion application.
Definition of Oracle Fusion ESS Job
Reports and scripts are registered as ESS jobs in Oracle Fusion apps to execute reports, procedures, and scripts, BIP reports, and users run these ESS jobs as Scheduled Processes in Oracle Fusion, equivalent to Concurrent Requests in Oracle apps R12. To build ESS jobs, we must first create an Oracle ESS job definition, after which we can use ESS jobs in Fusion Schedules Processes to run BIP reports, Procedures, and Scripts.
For SQL job requests, ESS uses an asynchronous execution model. The name of an application’s PL/SQL store procedure is defined by a request parameter. ESS uses Oracle Database Scheduler rather than calling the PL/SQL stored process explicitly.
Important ESS Job Table in Oracle Fusion to troubleshoot PL/SQL jobs
DBA_SHEDULER_JOBS: The latest Oracle Scheduler jobs are shown in this view. There are many Oracle Scheduler job states, but several of them do not extend to the ESS-created Scheduler job.
DBA_SCHEDULER_RUNNING_JOBS : This gives you information on all the Scheduler jobs that are actually operating in the database. The DBA_SCHEDULER_JOBS view would include any Scheduler jobs that are available in this view.
DBA_SCHEDULER_JOB_RUN_DETAILS: This gives all Scheduler jobs in the database log run details.
ESS_REQUEST_HISTORY: This table stores the Job Definition, Time Stamp, Parent Process Id information.
ESS_REQUEST_PROPERTY: This table stores submitted ess job definition properties
Query to Get ESS Job History Records in Oracle Fusion Application
SELECT r.requestid, username, processstart, processend, executable_status, v.name, v.value FROM fusion. ess_request_history r, fusion.ess_request_property v WHERE 1=1 AND r.requestid = v.requestid AND v.name LIKE 'submit.argument%' ORDER BY r.requestid, v.name;