GL Chart of Accounts - Segment Values Listing

select
        vi.flex_value                                    isval,
        vit.description                                   isdsc,
        vd.flex_value                                    dsval,
        vdt.description                                   dsdsc,
        vd.start_date_active                             sdate,
        vd.end_date_active                               edate,
        l1.meaning                                       eflag,
        l3.meaning                                       pflag,
        l4.meaning                                       bflag,
        l5.description                                atype
from
        fnd_flex_values                 vi,
        fnd_flex_values_tl             vit,
        fnd_flex_values                 vd,
        fnd_flex_values_tl             vdt,
       gl_lookups                      l1,
        gl_lookups                      l3,
        gl_lookups                      l4,
        gl_lookups                      l5
where
        vi.flex_value_set_id = :PVSETID
    and vit.flex_value_id = vi.flex_value_id + 0
    and vit.language = userenv('LANG')
    and vd.flex_value_set_id = :VSETID
    and vd.parent_flex_value_low = vi.flex_value
    and vd.enabled_flag = l1.lookup_code
    and vdt.flex_value_id = vd.flex_value_id + 0
    and vdt.language = userenv('LANG')
   and l1.lookup_type = 'YES/NO'
    and substrb( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline,
                          instrb( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline
                                      ,fnd_global.newline, 1, :POST_POSITION
                                    ) +1, 1 ) = l3.lookup_code
    and l3.lookup_type = 'YES/NO'
    and substrb( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline,
                          instrb ( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline,
                                       fnd_global.newline,1, :BUDGET_POSITION
                                    )+1, 1 ) = l4.lookup_code
    and l4.lookup_type = 'YES/NO'
    and nvl(substrb( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline,
                                 instrb( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline,
                                            fnd_global.newline, 1, :POSITION 
                                           ) +1, 1
                              ),'A') = l5.lookup_code(+)
    and l5.lookup_type(+) = 'ACCOUNT TYPE'
order by
        vi.flex_value,
        vd.flex_value

select vi.flex_value isval, vit.description isdsc, vd.flex_value dsval, vdt.description dsdsc, vd.start_date_active sdate, vd.end_date_active edate, l1.meaning eflag, l3.meaning pflag, l4.meaning bflag, l5.description atype from fnd_flex_values vi, fnd_flex_values_tl vit, fnd_flex_values vd, fnd_flex_values_tl vdt, gl_lookups l1, gl_lookups l3, gl_lookups l4, gl_lookups l5 where vi.flex_value_set_id = :PVSETID and vit.flex_value_id = vi.flex_value_id + 0 and vit.language = userenv('LANG') and vd.flex_value_set_id = :VSETID and vd.parent_flex_value_low = vi.flex_value and vd.enabled_flag = l1.lookup_code and vdt.flex_value_id = vd.flex_value_id + 0 and vdt.language = userenv('LANG') and l1.lookup_type = 'YES/NO' and substrb( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline, instrb( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline ,fnd_global.newline, 1, :POST_POSITION ) +1, 1 ) = l3.lookup_code and l3.lookup_type = 'YES/NO' and substrb( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline, instrb ( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline, fnd_global.newline,1, :BUDGET_POSITION )+1, 1 ) = l4.lookup_code and l4.lookup_type = 'YES/NO' and nvl(substrb( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline, instrb( fnd_global.newline||vd.compiled_value_attributes||fnd_global.newline, fnd_global.newline, 1, :POSITION ) +1, 1 ),'A') = l5.lookup_code(+) and l5.lookup_type(+) = 'ACCOUNT TYPE' order by vi.flex_value, vd.flex_value