Value Sets

Concurrent Program Name with Parameter, Value set

On request here is the query to list concurrent program name with its parameter, values set and default value/type

SELECT fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , par.column_seq_num      
      , par.end_user_column_name
      , par.form_left_prompt prompt
      , par.enabled_flag
      , par.required_flag
      , par.display_flag
      , par.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , par.DEFAULT_VALUE
 FROM   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl par
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
 WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
 AND    fcpl.user_concurrent_program_name = :conc_prg_name
 AND    fcpl.LANGUAGE = 'US'
 AND    par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
 AND    ffvs.flex_value_set_id = par.flex_value_set_id
 AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
 AND    flv.lookup_code(+) = par.default_type
 AND    flv.LANGUAGE(+) = USERENV ('LANG')
 ORDER BY par.column_seq_num

FNDLOAD to create value sets

Download a Value Set:

FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt VALUE_SET FLEX_VALUE_SET_NAME='XXXX'

Download Value Set Values:

FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME='XXXX'

Upload a Value Set:

FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt 

Date List of Value(LOV) for Concurrent Request Parameter

We are aware that it is not possible to display calendar window for concurrent request parameter. Here I am discussing on an alternative to create Date List of values.
This is addition to my earlier post Calender in Concurrent program. 
Create a table value set as shown in the screenshot below.


click on Edit Information and Enter following in the table name field

(SELECT (TO_DATE (SYSDATE - 1 + LEVEL, 'DD-MON-RRRR')) date_range
           , (TO_CHAR (SYSDATE - 1 + LEVEL, 'Month, DD RRRR')) date_word 
      FROM DUAL CONNECT BY LEVEL <= 1000)

This is how concurrent program looks when a value is attached to it.

Basics of Value Sets in Oracle Applications

Explained below is the basics of Value Set.

What is a value set and where is it used?
Value set is primarily the List of Values(LOV) to restrict and mantain consistencies in entering or selecting the values. It is also the place holders to allow user enter a value. Oracle Application Object Library uses value sets as important components of key flexfields, descriptive flexfields, and Concurrent Request Submission.

What are the Format Types the value set have?
* Character
* Number
* Time
* Standard Date, Standard Date Time
* Date, Date Time
Note that Date and Date Time value set formats are obsolete and are provided for backward compatibility only. For new value sets, use the the format types Standard Date and Standard Date Time.

What are the validation types?

None:

* There is no validation done for this type of value set, hence allows user to enter any value.

Independent:

* It provides a list of pre-defined values. The predefined values are entered seperately.

Dependent:

* Same like Independent Value Set, except the List of Values shown to you will depends on which the Independent value you have selected in the Prior Segment.
* Must define your independent value set before you define the dependent value set that depends on it.
* Must create at least one dependent value for each independent value.

Table:

* The list of value is created based on database tables
* Allows to write simple queries, joins, order by etc
* The value, meaning and ID can be used to display a value, description to the value but return ID to the calling program or screen.
* Additional columns can also be displayed. The syntax is column "column title(size)",.... e.g. order_type "SO Order Type(40)"
* Can also create dependent values to filter LOV data based on parameter value selected earlier. This can be done using :$FLEX:.value_set_name in the where clause.

Special & Pair:

Pair validation value set allows to select a range of concatenated Flex field segments as parameters to the report. The special value set is used to perform special validation. This is used to enter the entire key flexfield segment in the single parameter of the report/calling entity.

Translatable Independent & Translatable Dependent:

* This is similar to Independent and Dependent value set except that translated values can be displayed to the user.

APIs to create value set
The FND_FLEX_VAL_API package can be used to create different types of value sets.
* VALUESET_EXISTS - To check if value set exists
* DELETE_VALUESET - To delete value set. The value set can only be deleted if it is not being referenced by any program or entity.
* CREATE_VALUESET_NONE
* CREATE_VALUESET_INDEPENDENT
* CREATE_VALUESET_DEPENDENT
* CREATE_VALUESET_TABLE
* CREATE_VALUESET_SPECIAL
* CREATE_VALUESET_PAIR

What are the oracle tables that store value set information?
* FND_FLEX_VALUE_SETS
* FND_ID_FLEX_SEGMENTS
* FND_FLEX_VALUE
* FND_FLEX_VALIDATION_EVENTS
* FND_FLEX_VALUE_RULE_LINES
* FND_FLEX_VALUE_RULE
* FND_FLEX_VALUE_RULE_USAGE
* FND_FLEX_VALIDATION_TABLES

Display Distinct Value in Value Set

Now there are 3 ways to create distinct values value set
1) Write distinct statement in the field where table name is entered, but this has a limitation to the number of characters that can be entered. so if query is long then you cannot make use of this feature
2) Create a view based on your query and use that view in the value set
3) Write your query in such a way that it gives you a distinct value. You can make use of ROWID to meet the requirement.

ex. SELECT ooh.order_number
      FROM   oe_order_lines_all ool, oe_order_headers_all ooh
      WHERE  ool.header_id = ooh.header_id
      AND    ool.ROWID = (SELECT MAX(ROWID)
                          FROM   oe_order_lines_all oo11
                          WHERE  oo11.header_id = ooh.header_id)

Note that the above query is just an example and needs to be changed as per your need. In the above query I purposely joined lines and header to display duplicate order_numbers and then supressed it by using MAX(ROWID).

Calender in Concurrent Request Parameter

One of the problem with concurrent request date parameter is that we cannot attach a calendar the way we have for forms. But there is an alternate solution to have a List of values.

Create a view using following query

CREATE OR REPLACE VIEW xx_date
AS
   (SELECT     (TO_DATE (SYSDATE - 1 + LEVEL, 'DD-MON-RRRR')) date_range
             , (TO_CHAR (SYSDATE - 1 + LEVEL, 'Month, DD RRRR')) date_word
    FROM       DUAL x
    CONNECT BY LEVEL <= 1000)

In the above query sysdate can be replaced with the date range as per requirement. Also the above query will result in 1000 records which can be altered by changing the connect by level value.

Now that we have created a view, a table value set can be created based on it and this can be used in the request parameter.