Oracle EBS值集

1、从值集中取值

SELECT ffv.flex_value, ffv.description

  FROM fnd_flex_values_vl ffv, fnd_flex_value_sets ffs

 WHERE ffv.flex_value_set_id = ffs.flex_value_set_id

   AND ffs.flex_value_set_name = 'CUX_GL_COA_CO' --值集名

   AND ffv.enabled_flag = 'Y'

   AND SYSDATE BETWEEN nvl(ffv.start_date_active, SYSDATE) AND nvl(ffv.end_date_active, SYSDATE + 1);

2、获取值集值的相关程序编写

/*

            取得值集中值的说明

            p_flex_value_name : 值集名称

            p_segment         : 值集的值的value

           

  */

  FUNCTION get_flex_value_desc(p_flex_value_name IN VARCHAR2 , p_segment IN VARCHAR2 ) RETURN VARCHAR2 IS

    l_description VARCHAR2(100 ) := NULL;

 

  BEGIN

    SELECT ffv.description

      INTO l_description

      FROM fnd_flex_values_vl ffv, fnd_flex_value_sets ffs

     WHERE ffv.flex_value_set_id = ffs.flex_value_set_id

       AND ffs.flex_value_set_name = p_flex_value_name

       AND ffv.enabled_flag = 'Y'

       AND ffv.flex_value = p_segment;

    RETURN l_description;

  EXCEPTION

    WHEN too_many_rows THEN

      write_log( '在函数cux_develop_tools.get_flex_value_desc发生错误，返回多行' );

      RETURN l_description;

    WHEN no_data_found THEN

      RETURN NULL ;

    WHEN OTHERS THEN

      write_log( '在函数cux_develop_tools.get_flex_value_desc发生错误' );

      write_log( SQLERRM);

      RETURN l_description;

  END get_flex_value_desc;