1. Implementing Applications
  2. Manage List of Values Sources

Manage List of Values Sources

A list of values source for job definitions determines where a list of values comes from and what the specific values are. Users can select from the list when they set parameters.

For example, the list of values for a Country parameter gives users a list of countries to choose from. You can create list of values sources to use in job definitions that you or someone else created. Parameters in predefined job definitions can have lists of values too, but not from list of values sources that you can manage. But if you duplicate a predefined job definition, list of values sources might be automatically created for your new job definition. You can manage those sources as you would a source that you created yourself.

Here's how you get started to create, edit, or delete list of values sources:

  1. In the Setup and Maintenance work area, go to the Manage Enterprise Scheduler Job Definitions and Job Sets task in the Application Extensions functional area. Or, depending on your offering, you might use a different functional area.

    Note: If you don't see the task, make sure that the Enterprise Scheduler Job Definitions and Job Sets feature is enabled at the offering level.

  2. Click the Manage List of Values Sources tab. If you don’t see anything here, that just means you haven’t created any sources or duplicated any predefined job definitions that would result in automatically creating list of values sources.

  3. Go on to create or edit list of values sources.

Create List of Values Source

Create a source based on predefined view objects or your own SQL query.

  • For predefined view objects, contact your help desk to find out if there are any that meet your needs. And to get the view object's full path, which you enter when creating the list of values source.
  • For your own SQL query, you can see the Tables and Views guides for more information, if you want to base the list of values source on a specific table or view.

Your list of values source might behave differently from similar sources used in predefined jobs. There are some limitations to creating custom sources. For example, you can’t add new filtering criteria or configure the search.

  1. On the Manage List of Values Sources tab, click the Create icon.

  2. In the Create List of Values Source dialog box, select something from the Application Name list that's related to your list of values. If you don't know what to select or can't find anything that applies, just select Application Toolkit.

  3. In the User List of Values Source Name field, enter a name that helps you identify your list of values source.

  4. From the LOVType list, select Predefined to use a view object or User Defined to use a SQL query.

  5. If you selected Predefined, then in the List of Values Source Definition Name field, enter the full path of the view object that your help desk provided.

    If you selected User Defined, enter your own SQL in the Query field.

    • Your query can't have any aggregate function like DISTINCT, SUM, MAX, or AVG in the direct select clause. It can't have UNION, UNION ALL, INTERSECT, or MINUS either.

    • If you absolutely need to use any of those functions, you need to wrap the query in another select clause. Here are a couple examples:

      Don't Use This

      Use This Instead

      SELECT DISTINCT CHANNEL.MEANING MEANING FROM FND_LOOKUP_VALUES_TL

      SELECT MEANING FROM (SELECT DISTINCT MEANING MEANING FROM FND_LOOKUP_VALUES_TL);

      select lookup_code as LookupCode from fnd_lookup_values_b where lookup_type='EXM_REPORT_STATUS' UNION select 'NULL' as LookupCode from dual

      SELECT LOOKUPCODE FROM (select lookup_code as LookupCode from fnd_lookup_values_b where lookup_type='EXM_REPORT_STATUS' UNION select 'NULL' as LookupCode from dual)

  6. Make sure everything looks fine and then click Save and Close. You can't change most of the things about your source after you create it.

Edit or Delete List of Values Source

Here are some things to know about editing and deleting list of values sources, which you can do on the Manage List of Values Sources tab.

  • You can edit only the application name and description.

  • When you delete a list of values source, you can't find it and manage it any more, but it's not really gone. Any job definitions using that source can continue using it. So after you delete a source, you can't create another one later with the same name, even though you won't be able to find the deleted one. To avoid confusion, it's recommended that you don't ever delete any list of values source. Just create new ones as needed, with unique names.

Related Topics