GL账户描述获取方法

1.

SELECT GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(&CHAR_OF_ACCOUNT_ID
                                            ,1
                                            ,GCC.SEGMENT1) SEG1
      ,GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(&CHAR_OF_ACCOUNT_ID
                                            ,2
                                            ,GCC.SEGMENT2) SEG2
      ,GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(&CHAR_OF_ACCOUNT_ID
                                            ,3
                                            ,GCC.SEGMENT3) SEG3
      ,GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(&CHAR_OF_ACCOUNT_ID
                                            ,4
                                            ,GCC.SEGMENT4) SEG4
      ,GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(&CHAR_OF_ACCOUNT_ID
                                            ,5
                                            ,GCC.SEGMENT5) SEG5
      ,GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(&CHAR_OF_ACCOUNT_ID
                                            ,6
                                            ,GCC.SEGMENT6) SEG6
  FROM GL_CODE_COMBINATIONS GCC
 WHERE GCC.CODE_COMBINATION_ID = &CODE_COMBINATION_ID;

2.

SELECT GCC.SEGMENT4
      ,(SELECT DESCRIPTION
          FROM APPS.FND_FLEX_VALUES_VL FFV
         WHERE FFV.FLEX_VALUE_SET_ID IN
               (SELECT FIV.FLEX_VALUE_SET_ID
                  FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
                 WHERE (ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID) --
                   AND (ID_FLEX_CODE = 'GL#')
                   AND (APPLICATION_ID = 101)
                   AND FIV.SEGMENT_NUM = 4)
           AND FFV.FLEX_VALUE = GCC.SEGMENT4 --独立值集
           AND ROWNUM = 1) SEGMENT4_DESC
      ,GCC.SEGMENT5
      ,(SELECT DESCRIPTION
          FROM APPS.FND_FLEX_VALUES_VL FFV
         WHERE FFV.FLEX_VALUE_SET_ID IN
               (SELECT FIV.FLEX_VALUE_SET_ID
                  FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
                 WHERE (ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID)
                   AND (ID_FLEX_CODE = 'GL#')
                   AND (APPLICATION_ID = 101)
                   AND FIV.SEGMENT_NUM = 5)
           AND FFV.FLEX_VALUE = GCC.SEGMENT5 --
           AND FFV.PARENT_FLEX_VALUE_LOW = GCC.SEGMENT4 --值集依赖（第五段的段值依赖第四段的段值）
           AND ROWNUM = 1) SEGMENT5_DESC
      ,GCC.CONCATENATED_SEGMENTS
  FROM GL_CODE_COMBINATIONS_KFV GCC
 WHERE GCC.LAST_UPDATE_DATE > TRUNC(SYSDATE) - 90;

 

CREATE OR REPLACE FUNCTION MEW_GET_ACCOUNT_DESC_FNC(P_CHART_OF_ACCOUNTS_ID IN NUMBER --科目结构ID
                                                   ,P_CODE_COMBINATION_ID  IN VARCHAR2 --科目ID
                                                   ,P_SEGMENT              IN VARCHAR2 --子值集段值
                                                   ,P_SEGMENT_NUM          IN NUMBER) --子段值序号

 RETURN VARCHAR2 IS
  V_DESC                  VARCHAR2(240);
  V_VALUESET_ID           NUMBER;
  V_PARENT_VALUESET_ID    NUMBER;
  V_DEPEND_SEGMENT_NUMBER NUMBER;
  V_PARENT_SEGMENT_VALUE  VARCHAR2(30);
BEGIN

  --获取当前段值对应的值集ID
  SELECT FIV.FLEX_VALUE_SET_ID
    INTO V_VALUESET_ID
    FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
   WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
     AND (ID_FLEX_CODE = 'GL#')
     AND (APPLICATION_ID = 101)
     AND FIV.SEGMENT_NUM = P_SEGMENT_NUM;

  --判断当前值集是否存在依赖关系
  BEGIN
    SELECT FFVS.PARENT_FLEX_VALUE_SET_ID
      INTO V_PARENT_VALUESET_ID
      FROM FND_FLEX_VALUE_SETS FFVS
     WHERE FFVS.FLEX_VALUE_SET_ID = V_VALUESET_ID
       AND FFVS.PARENT_FLEX_VALUE_SET_ID IS NOT NULL;
 
    --存在依赖关系 判断依赖哪一段的值集
    SELECT FIV.SEGMENT_NUM
      INTO V_DEPEND_SEGMENT_NUMBER
      FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
     WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
       AND (ID_FLEX_CODE = 'GL#')
       AND (APPLICATION_ID = 101)
       AND FIV.FLEX_VALUE_SET_ID = V_PARENT_VALUESET_ID;
 
    --获取依赖值集段值
    SELECT DECODE(V_DEPEND_SEGMENT_NUMBER
                 ,1
                 ,SEGMENT1
                 ,2
                 ,SEGMENT2
                 ,3
                 ,SEGMENT3
                 ,4
                 ,SEGMENT4
                 ,5
                 ,SEGMENT5
                 ,6
                 ,SEGMENT6)
      INTO V_PARENT_SEGMENT_VALUE
      FROM GL_CODE_COMBINATIONS GCC
     WHERE GCC.CODE_COMBINATION_ID = P_CODE_COMBINATION_ID;
    --获取当前段值描述
    SELECT DESCRIPTION
      INTO V_DESC
      FROM APPS.FND_FLEX_VALUES_VL FFV
     WHERE FFV.FLEX_VALUE_SET_ID IN
           (SELECT FIV.FLEX_VALUE_SET_ID
              FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
             WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
               AND (ID_FLEX_CODE = 'GL#')
               AND (APPLICATION_ID = 101)
               AND FIV.SEGMENT_NUM = P_SEGMENT_NUM) --子值集段序号
       AND FFV.FLEX_VALUE = P_SEGMENT --子值集段值
       AND FFV.PARENT_FLEX_VALUE_LOW = V_PARENT_SEGMENT_VALUE --值集依赖（父值集段值）
       AND ROWNUM = 1;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      --不存在依赖关系
      BEGIN
        SELECT DESCRIPTION
          INTO V_DESC
          FROM APPS.FND_FLEX_VALUES_VL FFV
         WHERE FFV.FLEX_VALUE_SET_ID IN
               (SELECT FIV.FLEX_VALUE_SET_ID
                  FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
                 WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
                   AND (ID_FLEX_CODE = 'GL#')
                   AND (APPLICATION_ID = 101)
                   AND FIV.SEGMENT_NUM = P_SEGMENT_NUM) --子值集段序号
           AND FFV.FLEX_VALUE = P_SEGMENT --子值集段值           
           AND ROWNUM = 1;
      EXCEPTION
        WHEN OTHERS THEN
          V_DESC := P_SEGMENT;
      END;
  END;

  RETURN V_DESC;

EXCEPTION
  /*  WHEN TOO_MANY_ROWS THEN
    RETURN 'TOO_MANY_ROWS';
  WHEN NO_DATA_FOUND THEN
    RETURN 'NO_DATA_FOUND';*/
  WHEN OTHERS THEN
    RETURN NULL;
END;

 

SELECT A.SEGMENT1
      ,MEW_GET_ACCOUNT5_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
                                ,NULL
                                ,A.SEGMENT1
                                ,1) SEGMENT1_DESC
      ,A.SEGMENT2
      ,MEW_GET_ACCOUNT5_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
                                ,NULL
                                ,A.SEGMENT2
                                ,2) SEGMENT2_DESC
      ,A.SEGMENT3
      ,MEW_GET_ACCOUNT5_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
                                ,NULL
                                ,A.SEGMENT3
                                ,3) SEGMENT3_DESC
      ,A.SEGMENT4
      ,MEW_GET_ACCOUNT5_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
                                ,NULL
                                ,A.SEGMENT4
                                ,4) SEGMENT4_DESC
      ,A.SEGMENT5
      ,MEW_GET_ACCOUNT5_DESC_FNC(A.CHART_OF_ACCOUNTS_ID
                                ,A.SEGMENT4
                                ,A.SEGMENT5
                                ,5) SEGMENT5_DESC
      ,A.SEGMENT6
      ,A.SEGMENT7
      ,A.CONCATENATED_SEGMENTS
  FROM GL_CODE_COMBINATIONS_KFV A
 WHERE A.SEGMENT1 = 80;

 

SELECT GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION(GCC.CHART_OF_ACCOUNTS_ID
                                               ,GCC.CODE_COMBINATION_ID) CONCAT_DESC
      ,GCC.*
  FROM GL_CODE_COMBINATIONS GCC;