Tuesday, 20 August 2013

Query to Find The Structure/Defination of Value Set

SELECT ffvs.flex_value_set_name "Value Set Name", ffvs.description,
       flv.meaning "Validation Type", fls.meaning "Security Type",
              --security_enabled_flag,
       fll.meaning "List Type",
                               --longlist_flag,
                               flf.meaning "Format Type",
                                                         --format_type,
                                                         maximum_size,
       number_precision "Precision",
       DECODE (alphanumeric_allowed_flag,
               'Y', 'Unchacked',
               'N', 'Checked'
              ) "Number Only",
       DECODE (uppercase_only_flag,
               'Y', 'Checked',
               'N', 'Unchecked'
              ) "Upper Case Only",
       DECODE (numeric_mode_enabled_flag,
               'Y', 'Checked',
               'N', 'Unchacked'
              ) "Zero Fill number",
       minimum_value, maximum_value,
       fuc.user_name "Created By User",
       ful.user_name "Last Updated By User"
  FROM fnd_flex_value_sets ffvs,
       fnd_lookups flv,
       fnd_lookups fls,
       fnd_lookups fll,
       fnd_lookups flf,
       fnd_user fuc,
       Fnd_user ful
 WHERE flv.lookup_code = ffvs.validation_type
   AND fls.lookup_code = ffvs.security_enabled_flag
   AND fll.lookup_code = ffvs.longlist_flag
   AND flf.lookup_code = ffvs.format_type
   and fuc.user_id(+)=ffvs.created_by
   and ful.user_id(+)=ffvs.last_updated_by
   AND flf.lookup_type = 'FIELD_TYPE'
   AND fll.lookup_type = 'FLEX_VALUESET_LONGLIST_FLAG'
   AND fls.lookup_type = 'FLEX_VST_SECURITY_ENABLED_FLAG'
   AND flv.lookup_type = 'SEG_VAL_TYPES'
   --AND flex_value_set_name LIKE :values_name
   order by ffvs.last_update_date;

No comments:

Post a Comment