How to create Custom ESS Job in Oracle Cloud/Fusion

/ Cloud / By

Oracle Cloud Enterprize Scheduler Service (ESS) Jobs are programs based on PL/SQL, BIP Reports, Host, SQL Loader, and other technologies to execute business logic. There are many standard/seeded ESS jobs available in Oracle Fusion like Aging Report, Trial Balance reports, etc

But, what about custom ess job? Can you create a custom ess job in the Oracle cloud?

You do not have access to create any objects like table, view, PL/SQL in cloud application. so you can create a custom ESS job on the BI Publisher report only.

So let’s learn how to create a custom ESS job in Oracle cloud with example.

1. Create BI Publisher Report

This is a simple BI Publisher Report based on the all_objects table with the owner as an input parameter.

a) Create data model

Login to OCF and navigate to Tools -> Report and Analytics. Click on the Browse Catalog button to open the BI publisher console. Click on the New drop-down list available on the upper right-hand side and select the Data Model option.

This opens a blank Data Model canvas. Click on the plus sign to create a new SQL query-based Data Set along with one input parameter as shown below.

1. SQL Query

SELECT OWNER,
       OBJECT_NAME,
       SUBOBJECT_NAME,
       OBJECT_TYPE,
       STATUS
FROM ALL_OBJECTS
WHERE OWNER = NVL(:P_OWNER, OWNER)

2. Data Model

BI Publisher Data Model

3. Parameter

Create Parameter

Now, go to the data model and click on the Data tab and click on the view tab to generate sample data. This data is required to create the report.

Click on Save As Sample Data button.

Run Data Model and Save Sample Data

 

You should get confirmation from OCF that Saved as sample data. Click Ok

Click on the Save icon available on the upper right hand. This opens the browse and save window. Browse and Select the path( /Shared Folder/Custom/) to save the data model.

The data model is done, now let’s create a report layout.

b) Create a Report Layout

Click on the Create Report button to open the below window. Select the Use Report Editor option and click on the Finish button.

Choose Use Report Editor Option

This opens the save box. Save the report in /Shared Folder/Custom/ path.

Click on the Blank (Portrait) icon to open the report canvas.

select blank portrait

Create a simple data grid based report and drag required columns from the left-hand side column list as shown below.

create report layout

Click on the Save button to save the layout and click on the Done button. You can click on the View Report button to test if the report is working fine or not.

Now, go to the report editor window and make the necessary changes. You can change the Output Formats, Default Format, and other options.

Save when done.

Change report output formats, default format, etc

That’s it and your report is created.

2. Create ESS Job

Go to the main navigation screen and select the Setup and Maintenance task. Select the Search option from the Tasklist menu on the left-hand side.

Search Task

This opens a search window as shown below. Search for Manage%Sch%.

Select Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications

Select the specific link as per your cloud application. For me, it is  Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications.

Create new ESS Job

Click on the + sign to create a new ESS Job.

Create ESS Job Definition as shown below.

Create ESS Job Definition

  • Display Name – Any valid name for your ess job
  • Name – Short Name
  • Path – Path where the report is saved
  • Job Application Name – Select the application name here
  • Job type – BIPJobType
  • Report ID – Enter the report name with .xdo extension

You can enter the required information which is self-explanatory. Make sure to select Enable submission from the Scheduled Process checkbox.

Next, go to the parameter tab and create a parameter.

Create ESS Job Parameter

Enter Parameter Prompt, Data Type. You can select Read-only, Required, Do not display checkbox, and specify Default Value if any.

Click on Save and close to return to the main window. On Main Window again, you need to click Save and Close button. This saves your definition of the ESS Job.

Click on Done to exit the setup task.

3. Submit ESS JOb

Go to Tools –> Scheduled Processes to submit the ess job. Enter the name of the job in the name field as shown below.

Click Ok.

submit ess job

Enter the parameter value and click on the submit button to submit the ess job.

Enter parameter

When the Report completes, select that record.  The output section shows the output of the report. You can click on the Republish button to open the output in a separate window and export report in different format like PDF, Excel, HTML, XML, etc

ess job completed

That’s it.

Summary

You just learned how to create a Custom ESS job with parameters in the Oracle cloud. The only limitation in cloud/fusion apps is you can create ESS Job on the BI Publisher report only.

I hope you found this tutorial very useful. Please share and subscribe.