Value Sets
SELECT
FVVS.value_set_code,
HR_GENERAL.DECODE_LOOKUP('YES_NO',FVVS.protected_flag) Protected,
FVVS.Description,
FATV.User_Module_Name Module,
vl_validation.meaning Validation_Type,
vl_data_type.meaning Value_Data_Type,
HR_GENERAL.DECODE_LOOKUP('YES_NO',FVVS.security_enabled_flag) Security_Enabled,
FVVS.data_security_object_name Data_Security_Resource_name,
FVVS.precision,
FVVS.scale,
vl_subtype.meaning Value_Subtype,
FVVS.maximum_length,
FVVS.uppercase_only_flag,
FVVS.zero_fill_flag,
CASE WHEN vl_data_type.meaning = 'Character'
THEN
FVVS.minimum_value
WHEN vl_data_type.meaning = 'Number'
THEN
to_char(FVVS.minimum_value_number)
WHEN vl_data_type.meaning = 'Date'
THEN
to_char(FVVS.minimum_value_date)
WHEN vl_data_type.meaning = 'Date Time'
THEN
to_char(FVVS.minimum_value_timestamp)
ELSE NULL END Minimum,
CASE WHEN vl_data_type.meaning = 'Character'
THEN
FVVS.maximum_value
WHEN vl_data_type.meaning = 'Number'
THEN
to_char(FVVS.maximum_value_number)
WHEN vl_data_type.meaning = 'Date'
THEN
to_char(FVVS.maximum_value_date)
WHEN vl_data_type.meaning = 'Date Time'
THEN
to_char(FVVS.maximum_value_timestamp)
ELSE NULL END Maximum,
IND_FVVS.value_Set_Code Ind_Value_Code,
IND_FVVS.description Ind_Value_Desc,
FFVL.flex_value Value,
FFVL.description Value_Description,
FFVL.parent_flex_value_low Independent_Value,
HR_GENERAL.DECODE_LOOKUP('YES_NO',FFVL.enabled_flag) Enabled,
to_char(FFVL.start_date_active,'MM/DD/YYYY') Start_Date,
to_char(FFVL.end_date_active,'MM/DD/YYYY') End_Date,
FFVL.attribute_sort_order Sort_Order,
FVVTE.From_Clause,
FVVTE.Value_attributes_tbl_alias Value_Attributes_Table_Alias,
FVVTE.value_Column_name,
FVVTE.value_Column_type,
FVVTE.value_Column_length,
FVVTE.description_column_name,
FVVTE.description_column_type,
FVVTE.description_column_length,
FVVTE.id_column_name,
FVVTE.id_column_type,
FVVTE.id_column_length,
FVVTE.enabled_flag_column_name,
FVVTE.start_date_column_name,
FVVTE.end_date_column_name,
FVVTE.where_Clause,
FVVTE.order_by_clause
FROM FND_VS_VALUE_SETS FVVS,
FND_VS_VALUE_SETS IND_FVVS,
FND_VS_VT_TABLE_EXT FVVTE,
FND_APPL_TAXONOMY_VL FATV,
FND_FLEX_VALUES_VL FFVL,
fnd_lookups vl_validation,
fnd_lookups vl_data_type,
fnd_lookups vl_subtype
WHERE FVVTE.value_set_id(+) = FVVS.value_set_id
AND FATV.module_id = FVVS.module_id
AND vl_validation.lookup_type = 'FND_VS_VALIDATION_TYPES'
AND vl_data_type.lookup_type = 'FND_VS_VALUE_DATA_TYPES'
AND vl_subtype.lookup_type(+) = 'FND_VS_VARCHAR2_SUBTYPES'
AND vl_validation.lookup_code = FVVS.validation_type
AND vl_data_type.lookup_code = FVVS.value_data_type
AND vl_subtype.lookup_code(+) = FVVS.value_subtype
AND ffvl.flex_value_Set_id(+) = FVVS.value_set_id
AND IND_FVVS.value_Set_id(+) = FVVS.independent_value_Set_id
AND (
(:P_CREATION_TYPE= 'Custom' AND FVVS.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded' AND FVVS.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded Modified' AND FVVS.created_by = 'SEED_DATA_FROM_APPLICATION' AND FVVS.last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL')
)
ORDER BY FVVS.VALUE_SET_CODE
