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

 

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