Value Set Usage Query

SELECT
fcpt.user_concurrent_program_name Concurrent_Program_Name,
fcp.concurrent_program_name Short_Name,
fdfcuv.column_seq_num Column_Seq_Number,
fdfcuv.end_user_column_name Parameter_Name,
fdfcuv.form_left_prompt Prompt,
fdfcuv.enabled_flag Enabled_Flag,
fdfcuv.required_flag Required_Flag,
fdfcuv.display_flag Display_Flag,
fdfcuv.flex_value_set_id Value_Set_Id,
ffvs.flex_value_set_name Value_Set_Name,
flv.meaning Default_Type,
fdfcuv.DEFAULT_VALUE Default_Value
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv
WHERE
fcp.concurrent_program_id = fcpt.concurrent_program_id
--AND fcpt.user_concurrent_program_name = :conc_prg_name
AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpt.LANGUAGE = USERENV ('LANG')
AND flv.LANGUAGE(+) = USERENV ('LANG')
AND ffvs.flex_value_set_name LIKE :P_VALU_SET_NAME
ORDER BY fdfcuv.column_seq_num;

For More Details CLICK HERE