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
3. 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.
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.
This opens the save box. Save the report in /Shared Folder/Custom/ path.
Click on the Blank (Portrait) icon to open the report canvas.
Create a simple data grid based report and drag required columns from the left-hand side column list as shown below.
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.
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.
This opens a search window as shown below. Search for Manage%Sch%.
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.
Click on the + sign to create a new ESS Job.
Create ESS Job Definition as shown below.
- 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.
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.
Enter the parameter value and click on the submit button to submit the ess job.
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
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.