RishOraDev's Oracle Blogs

In my previous post, I explained how to “Create an ESS Job in Oracle ERP Cloud using BI Pulisher“. In this post, we will take a step forward and see how we can create a Lookup, create a List Of Values and attach the List of Values to a parameter for an ESS Job.

I will quickly recollect what we did in the last post. We created a Data-Model using a SQL query to fetch records from all_objects table. Then we created the report and added all_objects.owner as the parameter of the report as a free text field. Here we will replace the text field with a List of Value. The additional steps required to create the Lookup and Value Set and attaching that to the parameter of the ESS Job are highlighted in red below.

Note: We will not create an ESS Job from scratch, rather we’ll re-use the one that I explained in my previous post. For details on how to create an ESS Job, please visit the previous post, here.

1. Create the Data Model
2. Create Report Layout
3. Create Lookup and Lookup Values
4. Create List OF Values
5. Attach the List Of Values to the Parameter of the ESS Job
6. Submit the ESS Job

Assuming that you have already created the ESS Job, I will directly jump on to the Step 3, which is to create the Lookup.

3. Create Lookup

Login to your Oracle Cloud ERP instance and navigate to Setup and Maintenance and from the Task List click on Search.

Search with Manage Common Lookups.

Click on the search result. This will take us to the Manage Common Lookups page, where we will create the Lookup and add Lookup Values.

Click on the + icon to create the Lookup.

Add the details for the Lookup as per the table below.

Lookup Type	XXRM_OBJECT_OWNER_LKP
Meaning	XXRM_OBJECT_OWNER_LKP
Description	List of Object Owners
Module	Application Toolkit
Lookup Configuration Level [Default]	User
REST Access Secured	Secure

Click Save. This will enable the section to add the Lookup Values. Click on the + icon to insert the values for the lookup.

Add the Lookup Value and click Save. Repeat this step for each Lookup Value that you want to add. I have added a few Lookup Values as shown in the screenshot below:

Once you are done with adding the Lookup Values, click on Save and Close. You have successfully created the Lookup. Next step is to create the List Of Values.

4. Create List Of Values

To create the List Of Values, navigate to Setup and Maintenance and from the Task List click on Search. Search with Manage%Enterprise%Scheduler%. In my last post I had created the ESS Job under ‘Manage Enterprise Scheduler Job Definitions and Job Sets for Financial, Supply Chain Management, and Related Applications‘. I select the same here.

Now, navigate to the Manage List of Values Sources tab.

Click on the + icon to create the List of Values Source.

Fill up the details as per the table below:

Application Name	Application Toolkit
User List of Values Source Name	XXRM_OBJECT_OWNER_LOV
Description	XXRM_OBJECT_OWNER_LOV
LOV Type	Predefined
List of Values Source Definition Name	oracle.apps.fnd.applcore.lookups.model.publicView.CommonLookupPVO

Once you have filled in the details click on Save and Close.

5. Attach the List of Values to the Parameter of the ESS Job

Navigate to the Manage Job Definitions page and search for the Job XXRM Test Object List Report, which we created earlier. Click the Edit button.

When the ESS Job window pops up, click on the Edit button in the Parameters section.

When we created the parameter earlier, we created it as a Text Box. We’ll now change it to List of Values.

Once you select List of values as the Page Element, select the List of Values Source – XXRM_OBJECT_OWNER_LOV, that we created in Step 4, from the list.

Select LookupCode as the Attribute. In Display Attributes section, I want the LookupCode to be displayed in the LOV, so I will select LookupCode from the list of available attributes and move it to the right, as shown in the screenshot below. If you want to display any other attributes, please follow the same process. Click Save and Close.

Click on the Manage Dependencies icon to open up the Manage List of Values Dependencies page.

Select ByLookupType as the View Criteria. This will enable the Bind Variables section.

Insert the lookup type, we created – [ XXRM_OBJECT_OWNER_LKP ], within single quotes [ ‘ ], as the Default Value. Then click OK. Then Save and Close the ESS Job.

6. Submit the ESS Job

Now we are all set to execute the ESS Job. To submit the job, navigate to Tools > Scheduled Processes. Click on Schedule New Process. You should be able to see all the Lookup Values we configured in the parameter of the ESS Job, in form of a LOV.

Select any value from the LOV and click on Submit. Here, I select FUSION_SEED.

Request Id is displayed. Note down the Request ID. Wait for the job to complete successfully. Once the ESS Job succeeds, view the output to confirm that the records are fetched based on the object owner we selected as the parameter.

That’s it. That is all you need to do to create an ESS Job with Parameters using a Lookup.

I hope you find this post helpful. If you do like this post, please let me know in the comments section below, and share with friends and colleagues. In case you have any further queries, please feel free to reach out and I’ll try my best to get back to you.