R12.2 GL Balances Queries

  SELECT    

gb.* ,

  ( SELECT meaning 

FROM fnd_lookup_values 

WHERE lookup_type = 'ACCOUNT_TYPE'

  AND lookup_code = gcc.gl_account_type 

    AND enabled_flag = 'Y' 

  AND view_application_id = 0 ) account_type_desc  ,

gcc.*,

gps.*,

(SELECT ffv.description

  FROM apps.fnd_id_flex_structures_vl fif ,

           apps.fnd_id_flex_segments_vl fifs,

          apps.fnd_flex_values_vl ffv

  WHERE fif.id_flex_num = gcc.chart_of_accounts_id

      AND fif.application_id = 101 

      AND fif.id_flex_code = 'GL#'

      AND fif.id_flex_code         = fifs.id_flex_code

      AND fif.id_flex_num          = fifs.id_flex_num

      AND fifs.application_column_name = 'SEGMENT1'

      AND fifs.flex_value_set_id   = ffv.flex_value_set_id

      AND ffv.flex_value = gcc.segment1 

  )  segment1_description,

  (SELECT ffv.description

  FROM apps.fnd_id_flex_structures_vl fif ,

           apps.fnd_id_flex_segments_vl fifs,

          apps.fnd_flex_values_vl ffv

  WHERE fif.id_flex_num = gcc.chart_of_accounts_id

      AND fif.application_id = 101 

      AND fif.id_flex_code = 'GL#'

      AND fif.id_flex_code         = fifs.id_flex_code

      AND fif.id_flex_num          = fifs.id_flex_num

      AND fifs.application_column_name = 'SEGMENT2'

      AND fifs.flex_value_set_id   = ffv.flex_value_set_id

      AND ffv.flex_value = gcc.segment2 

  )  segment2_description,

  (SELECT ffv.description

  FROM apps.fnd_id_flex_structures_vl fif ,

           apps.fnd_id_flex_segments_vl fifs,

          apps.fnd_flex_values_vl ffv

  WHERE fif.id_flex_num = gcc.chart_of_accounts_id

      AND fif.application_id = 101 

      AND fif.id_flex_code = 'GL#'

      AND fif.id_flex_code         = fifs.id_flex_code

      AND fif.id_flex_num          = fifs.id_flex_num

      AND fifs.application_column_name = 'SEGMENT3'

      AND fifs.flex_value_set_id   = ffv.flex_value_set_id

      AND ffv.flex_value = gcc.segment3 

  )  segment3_description, 

(SELECT ffv.description

  FROM apps.fnd_id_flex_structures_vl fif ,

           apps.fnd_id_flex_segments_vl fifs,

          apps.fnd_flex_values_vl ffv

  WHERE fif.id_flex_num = gcc.chart_of_accounts_id

      AND fif.application_id = 101 

      AND fif.id_flex_code = 'GL#'

      AND fif.id_flex_code         = fifs.id_flex_code

      AND fif.id_flex_num          = fifs.id_flex_num

      AND fifs.application_column_name = 'SEGMENT4'

      AND fifs.flex_value_set_id   = ffv.flex_value_set_id

      AND ffv.flex_value = gcc.segment4 

  )  segment4_description, 

  (SELECT ffv.description

  FROM apps.fnd_id_flex_structures_vl fif ,

           apps.fnd_id_flex_segments_vl fifs,

          apps.fnd_flex_values_vl ffv

  WHERE fif.id_flex_num = gcc.chart_of_accounts_id

      AND fif.application_id = 101 

      AND fif.id_flex_code = 'GL#'

      AND fif.id_flex_code         = fifs.id_flex_code

      AND fif.id_flex_num          = fifs.id_flex_num

      AND fifs.application_column_name = 'SEGMENT5'

      AND fifs.flex_value_set_id   = ffv.flex_value_set_id

      AND ffv.flex_value = gcc.segment5 

  )  segment5_description,  

  (SELECT ffv.description

  FROM apps.fnd_id_flex_structures_vl fif ,

           apps.fnd_id_flex_segments_vl fifs,

          apps.fnd_flex_values_vl ffv

  WHERE fif.id_flex_num = gcc.chart_of_accounts_id

      AND fif.application_id = 101 

      AND fif.id_flex_code = 'GL#'

      AND fif.id_flex_code         = fifs.id_flex_code

      AND fif.id_flex_num          = fifs.id_flex_num

      AND fifs.application_column_name = 'SEGMENT6'

      AND fifs.flex_value_set_id   = ffv.flex_value_set_id

      AND ffv.flex_value = gcc.segment6 

  )  segment6_description, 

  (SELECT ffv.description

  FROM apps.fnd_id_flex_structures_vl fif ,

           apps.fnd_id_flex_segments_vl fifs,

          apps.fnd_flex_values_vl ffv

  WHERE fif.id_flex_num = gcc.chart_of_accounts_id

      AND fif.application_id = 101 

      AND fif.id_flex_code = 'GL#'

      AND fif.id_flex_code         = fifs.id_flex_code

      AND fif.id_flex_num          = fifs.id_flex_num

      AND fifs.application_column_name = 'SEGMENT7'

      AND fifs.flex_value_set_id   = ffv.flex_value_set_id

      AND ffv.flex_value = gcc.segment7 

  )  segment7_description,  

  (SELECT ffv.description

  FROM apps.fnd_id_flex_structures_vl fif ,

           apps.fnd_id_flex_segments_vl fifs,

          apps.fnd_flex_values_vl ffv

  WHERE fif.id_flex_num = gcc.chart_of_accounts_id

      AND fif.application_id = 101 

      AND fif.id_flex_code = 'GL#'

      AND fif.id_flex_code         = fifs.id_flex_code

      AND fif.id_flex_num          = fifs.id_flex_num

      AND fifs.application_column_name = 'SEGMENT8'

      AND fifs.flex_value_set_id   = ffv.flex_value_set_id

      AND ffv.flex_value = gcc.segment8 

  )  segment8_description, 

  (SELECT ffv.description

  FROM apps.fnd_id_flex_structures_vl fif ,

           apps.fnd_id_flex_segments_vl fifs,

          apps.fnd_flex_values_vl ffv

  WHERE fif.id_flex_num = gcc.chart_of_accounts_id

      AND fif.application_id = 101 

      AND fif.id_flex_code = 'GL#'

      AND fif.id_flex_code         = fifs.id_flex_code

      AND fif.id_flex_num          = fifs.id_flex_num

      AND fifs.application_column_name = 'SEGMENT9'

      AND fifs.flex_value_set_id   = ffv.flex_value_set_id

      AND ffv.flex_value = gcc.segment9 

  )  segment9_description

FROM 

     apps.gl_balances gb

 ,   apps.gl_code_combinations_kfv gcc

 ,   apps.gl_period_statuses gps

WHERE gb.ledger_id = 2083  

 AND gb.period_year >= 2016

 AND gb.code_combination_id = gcc.code_combination_id

AND gps.application_id = 101 

AND gb.period_name          = gps.period_name

AND gps.set_of_books_id = gb.ledger_id