Queries for Value Sets


Queries for Value Sets

Value Sets based on table:
This Query gives details of value sets that are based on a oracle application tables.
select ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffvs.validation_type,
    ffvt.value_column_name ,
    ffvt.meaning_column_name ,
    ffvt.id_column_name ,
    ffvt.application_table_name ,
    ffvt.additional_where_clause
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_validation_tables ffvt
WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id;
Independent Value set Details:
This query gives details of independent FND Value sets i.e. Values are static and these are not derived from any application table.
SELECT ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffvs.validation_type,
    ffv.flex_value_id ,
    ffv.flex_value ,
    ffvt.flex_value_meaning ,
    ffvt.description value_description
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_values ffv ,
    fnd_flex_values_tl ffvt
WHERE
    ffvs.flex_value_set_id     = ffv.flex_value_set_id
    and ffv.flex_value_id      = ffvt.flex_value_id
    AND ffvt.language          = USERENV('LANG');
Thanks
Dibyajyoti Koch
Have a nice Day!
Advertisements

3 Responses to Queries for Value Sets

  1. Steve says:

    Dude.. this has been immensely helpful! Thanks for these.
    Keep up the good work.

  2. Ishaq says:

    Thanks for the Valuable Information just to add for more info, please find queries to get GL segments information.

    Keep up the good work.

    Regards

    Ishaq

    –List of GL Segments and their value set id
    SELECT id_flex_num AS chart_of_account_id,
    application_column_name,
    segment_name,
    flex_value_set_id
    FROM fnd_id_flex_segments
    WHERE application_id = ‘101’ AND id_flex_code = ‘GL#’;

    –List of GL Segments and their Descriptions
    SELECT DISTINCT fnd_id_flex_segments.id_flex_num AS chart_of_account_id,
    fnd_id_flex_segments.application_column_name,
    fnd_id_flex_segments.segment_name,
    fnd_flex_values.flex_value,
    fnd_flex_values_tl.description AS description,
    fnd_flex_values.parent_flex_value_low parent_segment,
    fnd_flex_values.flex_value_set_id
    FROM Fnd_Flex_Values_tl,
    Fnd_Flex_Values,
    fnd_id_flex_segments
    WHERE fnd_flex_values.flex_value_set_id = fnd_id_flex_segments.flex_value_set_id
    AND fnd_id_flex_segments.application_id = ‘101’
    AND fnd_id_flex_segments.id_flex_code = ‘GL#’
    –AND fnd_id_flex_segments.id_flex_num = ‘101’
    AND fnd_flex_values.flex_value_id = fnd_flex_values_tl.flex_value_id
    –AND fnd_id_flex_segments.application_column_name = ‘SEGMENT3’
    ORDER BY fnd_id_flex_segments.id_flex_num,
    fnd_id_flex_segments.application_column_name,
    fnd_id_flex_segments.segment_name,
    fnd_flex_values.flex_value,
    fnd_flex_values_tl.description

  3. Rajas says:

    Hi Dibyajyoti,

    Trying to add the GL Responisibility->Journal->Values in the KeyFlexfield->Accounting Flexfield->Department. Following error populates.
    ‘APP-FND-01030: Value 1000 is longer than its maximum lenght of 2 characters’
    When i try to change the value set size, it generate an error message ‘You cannot change maximum size of Right-justify Zero-fill value sets. Since the value 001 is not same as 0000001,such a change would make the existing values invalid.’
    Found the Table Name for this updates as FND_FLEX_VALUE_SETS, how shall i find out the Concurrent Program name form the table name ? any query ?

    Referred this Link :
    https://community.oracle.com/thread/943492?tstart=0

    Please help me understand on this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: