GL_SEGMENT_WITH_BY ORG

/* Formatted on 14-09-2011 11:25:38 (QP5 v5.115.810.9015) */
  SELECT   DECODE (gcc.segment1,
                   '10',
                   'APIL - HO - 10',
                   '20',
                   'APIL - BPU UNIT - 20',
                   '21',
                   'APIL - BPU UNIT - 21',
                   '30',
                   'APIL - SHREE GOPAL UNIT - 30',
                   '40',
                   'APIL - SEWA UNIT - 40',
                   '50',
                   'APIL - BWN - 50',
                   '51',
                   'APIL - BWN - 51')
              OPERATING_UNIT,
           ffv.FLEX_VALUE NATURAL_ACCT,641449
           ffv.description,
           ffv.HIERARCHY_LEVEL,
           ffv.FLEX_VALUE_SET_ID
    FROM   FND_FLEX_VALUES_VL ffv, gl_code_combinations gcc
   WHERE   ( ('' IS NULL)
            OR (ffv.structured_hierarchy_level IN
                      (SELECT   hierarchy_id
                         FROM   fnd_flex_hierarchies_vl h
                        WHERE   h.flex_value_set_id = 1014870
                                AND h.hierarchy_name LIKE '')))
           AND gcc.segment3 = FLEX_VALUE
           AND ffv.FLEX_VALUE_SET_ID = 1014870
          -- AND ffv.END_DATE_ACTIVE IS NULL
           and ffv.END_DATE_ACTIVE IS not NULL
ORDER BY   gcc.segment1   -- ffv.flex_value

    NEW
--------------
SELECT   DECODE (gcc.segment1,
                   '10',
                   'APIL - HO - 10',
                   '20',
                   'APIL - BPU UNIT - 20',
                   '21',
                   'APIL - BPU UNIT - 21',
                   '30',
                   'APIL - SHREE GOPAL UNIT - 30',
                   '40',
                   'APIL - SEWA UNIT - 40',
                   '50',
                   'APIL - BWN - 50',
                   '51',
                   'APIL - BWN - 51')
              OPERATING_UNIT,
           ffv.FLEX_VALUE NATURAL_ACCT,
           ffv.description,
           ffv.HIERARCHY_LEVEL,
           ffv.FLEX_VALUE_SET_ID,
           ffv.COMPILED_VALUE_ATTRIBUTES
    FROM   FND_FLEX_VALUES_VL ffv, gl_code_combinations gcc
   WHERE   ( ('' IS NULL)
            OR (ffv.structured_hierarchy_level IN
                      (SELECT   hierarchy_id
                         FROM   fnd_flex_hierarchies_vl h
                        WHERE   h.flex_value_set_id = 1014870
                                AND h.hierarchy_name LIKE '')))
           AND gcc.segment3 = FLEX_VALUE
           AND ffv.FLEX_VALUE_SET_ID = 1014870
          -- AND ffv.END_DATE_ACTIVE IS NULL
           and ffv.END_DATE_ACTIVE IS not NULL
and            ffv.COMPILED_VALUE_ATTRIBUTES is not null
ORDER BY   gcc.segment1   -- ffv.flex_value

select * from all_tab_columns where column_name = 'HIERARCHY_ID'    -- like  -- COMPILED_VALUE_ATTRIBUTES  

select * from FND_FLEX_HIERARCHIES
select * from FND_FLEX_HIERARCHIES_TL
select * from FND_FLEX_VALUES where COMPILED_VALUE_ATTRIBUTES is not null

SELECT     DECODE (gcc.segment1,
                   '10',
                   'APIL - HO - 10',
                   '20',
                   'APIL - BPU UNIT - 20',
                   '21',
                   'APIL - BPU UNIT - 21',
                   '30',
                   'APIL - SHREE GOPAL UNIT - 30',
                   '40',
                   'APIL - SEWA UNIT - 40',
                   '50',
                   'APIL - BWN - 50',
                   '51',
                   'APIL - BWN - 51')
              OPERATING_UNIT, ffv.FLEX_VALUE_MEANING NATURAL_ACCT, ffv.DESCRIPTION ACCT_DESC
    FROM       gl_code_combinations gcc, fnd_flex_values_vl ffv
   WHERE       gcc.segment3 = ffv.FLEX_VALUE
           AND ffv.FLEX_VALUE_SET_ID = 1014870
           AND ffv.END_DATE_ACTIVE IS NULL    --or AND ffv.END_DATE_ACTIVE IS  NULL
           AND gcc.segment1 IN ('10', '20', '21', '30', '40', '50', '51')
ORDER BY   TO_NUMBER (gcc.segment1)