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.
