How to create List Of Values (LOV) using SQL Query in BIP ESS Job in Oracle Fusion?

How to create List Of Values (LOV) using SQL Query in BIP ESS Job in Oracle Fusion?

on May 21, 2023

In this post we will look at how to create List Of Values (LOV) using SQL Query in BIP ESS Job

In previous post we had defined an ESS Job from BIP Report with one parameter Invoice Source. Now we will make this parameter as LOV.

In Manage Enterprise Scheduler Job Definitions, go to Manage List of Values Sources tab and click on Add button

Select the Application Name as Application Toolkit, LOVType as User Defined. Enter the LOV Source Name, description and enter below query then click Save and Close button

SELECT rownum AS LOV_ID,
       NAME
FROM   (SELECT DISTINCT source NAME
        FROM   ap_invoices_all)
ORDER  BY 2

LOV will be saved with LOV Definition Name as sessiondef.oracle.apps.atk.essMeta.model.publicView.INV_SRCAtkEssLOVVO

Now we can assign this LOV to our ESS Job Parameter. Click on Edit button to make changes to existing parameter of ESS Job

Existing parameter Page Element is Text box, change it List of values

Select the Attribute and Display Attributes as Name. Click Save and Close once done.

Now when we will try to submit the ESS Job, we will be able to see the LOV

In case we had selected the page Element as Choice List then we would be presented with all values in single drop down

Comments