标签归档:GL

获取会计科目段子父级关系

––-相关的参数值请根据自己的实际情况填写
SELECT DATA_SOURCE.FLEX_VALUE_SET_NAME 值集名称,
       DATA_SOURCE.FLEX_VALUE_SET_ID   值集ID,
       DATA_SOURCE.FLEX_VALUE          值代码,
       DATA_SOURCE.DESCRIPTION         值描述,
       DATA_SOURCE.SUMMARY_FLAG        是否父级,
       DATA_SOURCE.HIERARCHY_NAME      组级别,
       DATA_SOURCE.RANGE_FROM          范围从,
       DATA_SOURCE.RANGE_TO            范围至
  FROM (SELECT FFVS.FLEX_VALUE_SET_NAME    FLEX_VALUE_SET_NAME,
               FFVS.FLEX_VALUE_SET_ID      FLEX_VALUE_SET_ID,
               FFV.FLEX_VALUE              FLEX_VALUE,
               FFV.SUMMARY_FLAG            SUMMARY_FLAG,
               FFV.DESCRIPTION             DESCRIPTION,
               FFHV.HIERARCHY_NAME         HIERARCHY_NAME,
               FFVNH.CHILD_FLEX_VALUE_LOW  RANGE_FROM,
               FFVNH.CHILD_FLEX_VALUE_HIGH RANGE_TO
          FROM FND_FLEX_VALUES_VL            FFV,
               FND_FLEX_VALUE_NORM_HIERARCHY FFVNH,
               FND_FLEX_VALUE_SETS           FFVS,
               FND_FLEX_HIERARCHIES_VL       FFHV
         WHERE FFV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
           AND FFV.FLEX_VALUE_SET_ID = FFVNH.FLEX_VALUE_SET_ID
           AND FFV.FLEX_VALUE = FFVNH.PARENT_FLEX_VALUE
           AND FFV.STRUCTURED_HIERARCHY_LEVEL = FFHV.HIERARCHY_ID
           AND FFVS.FLEX_VALUE_SET_NAME = 'XXXX_ACCOUNT'
           AND FFV.ENABLED_FLAG = 'Y'
           AND (FFV.END_DATE_ACTIVE IS NULL OR FFV.END_DATE_ACTIVE > SYSDATE)
           AND FFVS.FLEX_VALUE_SET_NAME IN
               (SELECT B.SEGMENT_NAME
                  FROM APPLSYS.FND_ID_FLEX_STRUCTURES A,
                       APPLSYS.FND_ID_FLEX_SEGMENTS B,
                       APPLSYS.FND_FLEX_VALUE_SETS C,
                       (SELECT *
                          FROM APPLSYS.FND_SEGMENT_ATTRIBUTE_VALUES AB
                         WHERE AB.ID_FLEX_CODE = 'GL#'
                           AND AB.ATTRIBUTE_VALUE = 'Y'
                           AND AB.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL') ABB
                 WHERE A.ID_FLEX_CODE = B.ID_FLEX_CODE
                   AND A.ID_FLEX_CODE = 'GL#'
                   AND A.ID_FLEX_NUM = B.ID_FLEX_NUM
                   AND B.FLEX_VALUE_SET_ID = C.FLEX_VALUE_SET_ID
                   AND B.ID_FLEX_NUM = ABB.ID_FLEX_NUM(+)
                   AND B.APPLICATION_COLUMN_NAME =
                       ABB.APPLICATION_COLUMN_NAME(+)
                   AND A.ID_FLEX_STRUCTURE_CODE = 'XXXX_ACCOUNT_FLEXFIELD')
     
        UNION
     
        SELECT FFVS.FLEX_VALUE_SET_NAME FLEX_VALUE_SET_NAME,
               FFVS.FLEX_VALUE_SET_ID FLEX_VALUE_SET_ID,
               FFV.FLEX_VALUE FLEX_VALUE,
               FFV.SUMMARY_FLAG SUMMARY_FLAG,
               FFV.DESCRIPTION DESCRIPTION,
               '明细组' HIERARCHY_NAME,
               NULL RANGE_FROM,
               NULL RANGE_TO
          FROM FND_FLEX_VALUES_VL FFV, FND_FLEX_VALUE_SETS FFVS
         WHERE FFV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
           AND FFVS.FLEX_VALUE_SET_NAME = 'XXXX_ACCOUNT'
           AND FFV.ENABLED_FLAG = 'Y'
           AND (FFV.END_DATE_ACTIVE IS NULL OR FFV.END_DATE_ACTIVE > SYSDATE)
           AND FFVS.FLEX_VALUE_SET_NAME IN
               (SELECT B.SEGMENT_NAME
                  FROM APPLSYS.FND_ID_FLEX_STRUCTURES A,
                       APPLSYS.FND_ID_FLEX_SEGMENTS B,
                       APPLSYS.FND_FLEX_VALUE_SETS C,
                       (SELECT *
                          FROM APPLSYS.FND_SEGMENT_ATTRIBUTE_VALUES AB
                         WHERE AB.ID_FLEX_CODE = 'GL#'
                           AND AB.ATTRIBUTE_VALUE = 'Y'
                           AND AB.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL') ABB
                 WHERE A.ID_FLEX_CODE = B.ID_FLEX_CODE
                   
                   AND A.ID_FLEX_CODE = 'GL#'
                   AND A.ID_FLEX_NUM = B.ID_FLEX_NUM
                   AND B.FLEX_VALUE_SET_ID = C.FLEX_VALUE_SET_ID
                   AND B.ID_FLEX_NUM = ABB.ID_FLEX_NUM(+)
                   AND B.APPLICATION_COLUMN_NAME =
                       ABB.APPLICATION_COLUMN_NAME(+)
                   AND A.ID_FLEX_STRUCTURE_CODE = 'XXXX_ACCOUNT_FLEXFIELD')
           AND FFV.SUMMARY_FLAG = 'N') DATA_SOURCE
 ORDER BY DATA_SOURCE.FLEX_VALUE_SET_ID, DATA_SOURCE.FLEX_VALUE;