Oracle gl account Hierarchy Query

Hi friends, we are going to discuss about the Oracle gl account hierarchy query. Wee will share the detail sql query which will extract the gl accounts hierarchy in oracle apps. If we want to extract the gl accounts based on the hierarchy wise then we need to use this sql query. This is one of the important sql query related to gl accounts in oracle apps. We will also share the important tables which helps to develop the Oracle gl account Hierarchy Query. Using this sql query we can develop the custom report in oracle to fetch the gl accounts details including accounts hierarchy. Please find below the complete detail about Oracle gl account Hierarchy Query.

Oracle gl account Hierarchy Query

4 Important Table Uses in the Oracle gl account Hierarchy Query

1.FND_FLEX_VALUES_TL FFVL,

2.FND_FLEX_VALUES FFV,

3.FND_FLEX_VALUE_SETS FFVS,

4.FND_FLEX_VALUE_NORM_HIERARCHY

Oracle GL Account Hierarchy Query

Here below is the complete sql query to get the details of gl accounts hierarchy in Oracle Apps.

SELECT FFV.FLEX_VALUE,

       FFV.ENABLED_FLAG,

       (SELECT TEMPLATE_NAME

          FROM GL.GL_SUMMARY_TEMPLATES

         WHERE ledger_id = 1

           AND TEMPLATE_ID = FFV.STRUCTURED_HIERARCHY_LEVEL) GROUP_NAME,

       FFV.HIERARCHY_LEVEL,

       CHILD_RANGE.CHILD_FLEX_VALUE_LOW CHILD_LOW,

       CHILD_RANGE.CHILD_FLEX_VALUE_HIGH CHILD_HIGH

  FROM APPLSYS.FND_FLEX_VALUES_TL FFVL,

       APPLSYS.FND_FLEX_VALUES FFV,

       APPLSYS.FND_FLEX_VALUE_SETS FFVS,

       (SELECT PARENT_FLEX_VALUE,

               CHILD_FLEX_VALUE_LOW,

               CHILD_FLEX_VALUE_HIGH

          FROM APPLSYS.FND_FLEX_VALUE_NORM_HIERARCHY

         WHERE FLEX_VALUE_SET_ID = 3566789) CHILD_RANGE

 WHERE FFVL.LANGUAGE = 'US'

   AND FFVL.FLEX_VALUE_ID = FFV.FLEX_VALUE_ID

   AND FFV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID

   AND FFV.SUMMARY_FLAG = 'Y'

   AND FFVS.FLEX_VALUE_SET_NAME = 'XXGL_NATURAL_ACCOUNT'

   AND FFV.FLEX_VALUE = CHILD_RANGE.PARENT_FLEX_VALUE(+)

 ORDER BY FFV.FLEX_VALUE;

Oracle gl account Hierarchy Query