Oracle Apps: Query to get the details of a Table Type ValueSet

The below query gives the full details of a Table Type Valueset in Oracle eBusiness. The last column gives the actual query which gets executed by the Valueset.

 select ffvs.flex_value_set_id  
       ,ffvs.flex_value_set_name  
       ,ffvs.description  
       ,decode(ffvs.longlist_flag  
              ,'Y', 'Long List of Values'  
              ,'N', 'List of Values'  
              ,'X', 'Poplist') list_type  
       ,decode(ffvs.security_enabled_flag  
              ,'N' ,'No Security'  
              ,'H' ,'Hierarchical Security'  
              ,'Y' ,'Non-Hierarchical Security') security_type  
       ,decode(ffvs.format_type  
              ,'C' ,'Char'  
              ,'D' ,'Date'  
              ,'T' ,'DateTime'  
              ,'N' ,'Number'  
              ,'X' ,'Standard Date'  
              ,'Y' ,'Standard DateTime'  
              ,'I' ,'Time') format_type  
       ,ffvs.maximum_size  
       ,ffvs.number_precision precision
       ,decode(ffvs.alphanumeric_allowed_flag  
              ,'N', 'Y'  
              ,'Y', 'N') numbers_only  
       ,ffvs.uppercase_only_flag uppercase_only  
       ,ffvs.numeric_mode_enabled_flag right_justify_and_zero_fill  
       ,ffvs.minimum_value min_value  
       ,ffvs.maximum_value max_value  
       ,decode(ffvs.validation_type  
              ,'I' ,'Independent'  
              ,'D' ,'Dependent'  
              ,'N' ,'None'  
              ,'P' ,'Pair'  
              ,'U' ,'Special'  
              ,'F' ,'Table'  
              ,'X' ,'Translatable Independent'  
              ,'Y' ,'Translatable Dependent') validation_type  
       ,(select application_name   
         from fnd_application_vl x   
         where x.application_id = ffvt.table_application_id) table_application  
       ,ffvt.application_table_name table_name  
       ,ffvt.value_column_name  
       ,decode(ffvt.value_column_type  
              ,'V', 'Varchar2'  
              ,'N', 'Number'  
              ,'C', 'Char'  
              ,'D', 'Date') value_column_type  
       ,ffvt.value_column_size  
       ,ffvt.meaning_column_name  
       ,decode(ffvt.meaning_column_type  
              ,'V', 'Varchar2'  
              ,'N', 'Number'  
              ,'C', 'Char'  
              ,'D', 'Date') meaning_column_type  
       ,ffvt.meaning_column_size  
       ,ffvt.id_column_name  
       ,decode(ffvt.id_column_type  
              ,'V', 'Varchar2'  
              ,'N', 'Number'  
              ,'C', 'Char'  
              ,'D', 'Date') id_column_type  
       ,ffvt.id_column_size  
       ,ffvt.additional_where_clause  
       ,ffvt.additional_quickpick_columns  
       ,('select ' ||NVL(value_column_name,NULL)   ||', '  
                   ||NVL(meaning_column_name,NULL) ||', '  
                   ||NVL(id_column_name,NULL)      ||' '||  
         'from '  || ffvt.application_table_name   ||' '||  
         'where ' || decode(upper(substr(ltrim(to_char(ffvt.additional_where_clause)),0,5))  
                           ,'WHERE', substr(to_char(ffvt.additional_where_clause),6)  
                           ,to_char(ffvt.additional_where_clause)) )valueset_query  
 from  apps.fnd_flex_value_sets        ffvs  
      ,apps.fnd_flex_validation_tables ffvt  
 where ffvs.flex_value_set_id = ffvt.flex_value_set_id  
 and   ffvs.flex_value_set_name = 'XXAJ_VALUESET'; -- Replace the valuesetname  

This query is helpful when we need to find out all the value sets which is using a particular profile_option or another valueset or a specific table etc...


Add the below where clause to the above query to find out all the valuesets which uses a specific value in the whereclause.

 and  ffvt.additional_where_clause like '%AJ%'; -- Replace the value with the string you want to search for.  

Feel free to point out if anything is missing/wrong in this blog.