Create a Shared LOV

In this topic, you customize some of the page items to show a list of values (LOVs). You create an LOV on the MANAGER_ID page item and then convert it to a Shared LOV. In addition, you create a Shared LOV to show a list of jobs and then associate the LOV with the JOB_ID page item.


Click Run Application.




Click the edit icon in front of one of the rows in the Employee Report.




Notice that the EMPLOYEE_ID for the Manager is displayed. You want this to be a select list and show the managers last name. Click Edit Page 2 in the Developer Toolbar.




Scroll down the Page Rendering section and right-click the P2_MANAGER_ID page item and select Edit. Alternatively, you can double-click the page item.




Select the Select List quick pick for the Display As field.




Notice that after you change the Display As, the List of Values subtab appears. Select List of Values to go to that section on this page.




You want a list of employees to be dynamically displayed. Click the Create Dynamic List of Values link.




Select the EMPLOYEES table from the list of values (using the icon) or enter the name in the Table or View field. Then click Next.




Select LAST_NAME for Display column and EMPLOYEE_ID for Return Value and click Next. Note you must have a display and return value for each List of Values you create.




The SQL query that will execute for this item is displayed. Click Finish.




Click Apply Changes.




Your changes were saved. Click Run.




Notice that the Managers Last Name is now listed instead of the Managers Employee ID. Select the list icon.




The list of values is displayed. You might want to use this List of Values on other pages in your application. As a result, you want to make it a Shared LOV. Click the Edit Page 2 button in the Developer Toolbar.




Double-click the P2_MANAGER_ID page item to open its property page.




Because you last selected the List of Values subtab, you should be automatically directed to that section. To convert the LOV to a shared LOV, select Convert LOV from the list of Tasks on the right.




The Create Shared LOV window is displayed with the LOV SQL query. Enter EMPLOYEE_LIST for List of Values Name field and click Create.




The LOV was created successfully. To view all the Shared LOVs, select the Shared Components icon in the upper right corner of the page.




Under User Interface, select List of Values.




You want to create another LOV to show a list of Job Titles. This time, you create the Shared LOV and then reference it on the page item properties page. Click Create.




Accept the default and click click Next.




Enter JOB_LIST for name and click the Dynamic radio button. Then click Next.




Change the SQL query to the following and click Create List of Values.


select job_title d, job_id r
from jobs
order by 1          



The JOB_LIST LOV was created successfully. Now you can reference it in your page item for JOB_ID. Click the Edit Page 2 icon in the upper right of this page. Note that this Edit Page icon references the last page you editted.




Scroll down under Page Rendering and double-click the P2_JOB_ID page item.




Select the Select List quick pick link.




Click the List of Values subtab.




Select JOB_LIST from the list of Named LOVs.




If the JOB_ID is empty, you may want to specify a default value. Select Yes for Display Null Value and enter - Select Job - for Null Display Value and click Apply Changes.




Click the Run Page icon in the upper right section of the page.




Notice that the Job Id now displays the title of the job rather than the id. Click Edit Page 2. In the next topic, you create a validation for this page.