取会计科目各子科目中文字段

由于设计值集时，子科目值集依赖于主科目值集，
gl_flexfields_pkg.get_description_sql对于依赖值集取描述值存在BUG。

这个function适用于：

会计科目第四个段值，如“会计子目”依赖(从属)第三个段值“会计主目”，那么在这种情况下，用gl_flexfields_pkg.get_description_sql取第四个段是会有问题的。

只要有依赖性(从属)的情况，用以下function解决。

---

 

CREATE OR REPLACE PACKAGE BODY cux_common_pkg IS

  FUNCTION get_dependence_segment_desc_f(x_coa_id         IN NUMBER,
                                         x_seg_num        IN NUMBER,
                                         x_seg_val        IN VARCHAR2,
                                         x_parent_seg_val IN VARCHAR2)
    RETURN VARCHAR2 IS
    seg_desc VARCHAR2(1000);
  BEGIN
    SELECT ffv.description
      INTO seg_desc
      FROM fnd_id_flex_segments s,
           fnd_flex_value_sets  vs,
           fnd_flex_values_vl   ffv
     WHERE s.id_flex_num = x_coa_id
       AND s.application_id = 101
       AND s.id_flex_code = 'GL#'
       AND s.segment_num = x_seg_num
       AND s.enabled_flag = 'Y'
       AND ffv.flex_value = x_seg_val
       AND vs.validation_type = 'D'
       AND ffv.parent_flex_value_low = x_parent_seg_val
       AND vs.flex_value_set_id = s.flex_value_set_id
       AND vs.flex_value_set_id = ffv.flex_value_set_id;
    RETURN(seg_desc);
  EXCEPTION
    WHEN no_data_found THEN
      RETURN(NULL);
    WHEN OTHERS THEN
      RETURN(NULL);
  END get_dependence_segment_desc_f;
END cux_common_pkg;

---

 

调用例子：

cux_common_pkg.get_dependence_segment_desc_f(gcc.chart_of_accounts_id,
                                                    4,
                                                    gcc.segment4,
                                                    gcc.segment3) AS 子科目