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.