Oracle EBS 获取完全的值集信息

SELECT fvs1.flex_value_set_name,
    fvs1.description,
    decode(upper(fvs1.longlist_flag),
       'N',
       '值列表',
       'Y',
       '长值列表',
       'X',
       '弹出式列表') 列表类型,
    decode(upper(fvs1.security_enabled_flag),
       'N',
       '无安全性',
       'H',
       '分层安全性',
       'Y',
       '不分层安全性') 安全性类型,
    decode(upper(fvs1.format_type),
       'C',
       '字符',
       'D',
       '日期',
       'I',
       '时间',
       'N',
       '数字',
       'T',
       '时间',
       'X',
       '标准日期',
       'Y',
       '标准日期时间') 格式类型,
    decode(upper(fvs1.alphanumeric_allowed_flag),
       'N',
       '否',
       'Y',
       '是') 仅限于数字,
    decode(upper(fvs1.uppercase_only_flag),
       'N',
       '否',
       'Y',
       '是') 仅限于大写字母,
    decode(upper(fvs1.numeric_mode_enabled_flag),
       'N',
       '否',
       'Y',
       '是') 右对齐和填零编号,
    fvs1.maximum_size,
    fvs1.number_precision,
    fvs1.minimum_value,
    fvs1.maximum_value,
    decode(upper(fvs1.validation_type),
       'D',
       '从属',
       'F',
       '表',
       'I',
       '独立',
       'N',
       '无',
       'P',
       '对',
       'U',
       '特定',
       'X',
       '可转换独立',
       'Y',
       '可转换从属'),
    fa.application_name,
    fvt.application_table_name,
    decode(upper(fvt.summary_allowed_flag),
       'N',
       '否',
       'Y',
       '是') 允许父值,
    fvt.value_column_name,
    decode(upper(fvt.value_column_type),
       'V',
       'Varchar2',
       'D',
       '日期',
       'C',
       '字符',
       'N',
       '数字',
       NULL) 值类型,
    fvt.value_column_size,
    fvt.meaning_column_name,
    decode(upper(fvt.meaning_column_type),
       'V',
       'Varchar2',
       'D',
       '日期',
       'C',
       '字符',
       'N',
       '数字',
       NULL) 含义类型,
    fvt.meaning_column_size,
    fvt.id_column_name,
    decode(upper(fvt.id_column_type),
       'V',
       'Varchar2',
       'D',
       '日期',
       'C',
       '字符',
       'N',
       '数字',
       NULL) 标识类型,
    fvt.id_column_size,
    fvt.additional_where_clause,
    fvt.additional_quickpick_columns,
    fvs2.flex_value_set_name,
    fvs1.dependant_default_value,
    fvs1.dependant_default_meaning,
    decode(upper(fve.event_code),
       'V',
       '验证',
       'I',
       '插入/更新',
       'Q',
       '列表值',
       'O',
       '编辑/编辑',
       'E',
       '编辑',
       'F',
       '查询',
       'L',
       '装入') 事件,
    fve.user_exit,
    CASE
     WHEN (fvs1.creation_date >= to_date('2016-01-01','YYYY-MM-DD') or
         fvs1.flex_value_set_name LIKE '[TABLE_OWNER]' || '%') THEN
     '是'
     ELSE
     '否'
    END,
    CASE
     WHEN (substr(fvs1.flex_value_set_name,
           1,
           length('[TABLE_OWNER]')) = '[TABLE_OWNER]') THEN
     '是'
     ELSE
     '否'
    END,
    userenv('LANG')
 FROM fnd_flex_value_sets        fvs1,
    fnd_flex_value_sets        fvs2,
    fnd_flex_validation_tables fvt,
    fnd_application_tl         fa,
    fnd_flex_validation_events fve
 WHERE fvs1.flex_value_set_id = fvt.flex_value_set_id(+)
  AND fvs1.flex_value_set_id = fve.flex_value_set_id(+)
  AND fvt.table_application_id = fa.application_id(+)
  AND fa.LANGUAGE(+) = userenv('LANG')
  AND fvs1.parent_flex_value_set_id = fvs2.flex_value_set_id(+)
  AND fvs1.last_update_date >= to_date('2016-01-01','YYYY-MM-DD')

 

转载于:https://www.cnblogs.com/jenrry/p/10006862.html