Query to Get Element Account and Balancing Details

  SELECT petf.element_name,

         pec.classification_name,

         (SELECT pck_cost.segment4

            FROM fnd_flex_value_sets ffvs,

                 fnd_flex_values ffv,

                 fnd_flex_values_tl ffvt,

                 pay_cost_allocation_keyflex pck_cost

           WHERE     flex_value_set_name = 'XXX_GL_ACCOUNT'

                 AND ffv.flex_value_set_id = ffvs.flex_value_set_id

                 AND ffvt.flex_value_id = ffv.flex_value_id

                 AND ffvs.flex_value_set_id = ffv.flex_value_set_id

                 AND ffvt.language = 'US'

                 AND ffv.enabled_flag = 'Y'

                 AND ffv.flex_value = pck_cost.segment4

                 AND pck_cost.cost_allocation_keyflex_id =

                        pelf.cost_allocation_keyflex_id)

            cost,

         (SELECT ffvt.description

            FROM fnd_flex_value_sets ffvs,

                 fnd_flex_values ffv,

                 fnd_flex_values_tl ffvt,

                 pay_cost_allocation_keyflex pck_cost

           WHERE     flex_value_set_name = 'XXX_GL_ACCOUNT'

                 AND ffv.flex_value_set_id = ffvs.flex_value_set_id

                 AND ffvt.flex_value_id = ffv.flex_value_id

                 AND ffvs.flex_value_set_id = ffv.flex_value_set_id

                 AND ffvt.language = 'US'

                 AND ffv.enabled_flag = 'Y'

                 AND ffv.flex_value = pck_cost.segment4

                 AND pck_cost.cost_allocation_keyflex_id =

                        pelf.cost_allocation_keyflex_id)

            cost_account,

         (SELECT pck_balancing.segment4

            FROM fnd_flex_value_sets ffvs,

                 fnd_flex_values ffv,

                 fnd_flex_values_tl ffvt,

                 pay_cost_allocation_keyflex pck_balancing

           WHERE     flex_value_set_name = 'XXX_GL_ACCOUNT'

                 AND ffv.flex_value_set_id = ffvs.flex_value_set_id

                 AND ffvt.flex_value_id = ffv.flex_value_id

                 AND ffvs.flex_value_set_id = ffv.flex_value_set_id

                 AND ffvt.language = 'US'

                 AND ffv.enabled_flag = 'Y'

                 AND ffv.flex_value = pck_balancing.segment4

                 AND pck_balancing.cost_allocation_keyflex_id =

                        pelf.balancing_keyflex_id)

            balancing,

         (SELECT ffvt.description

            FROM fnd_flex_value_sets ffvs,

                 fnd_flex_values ffv,

                 fnd_flex_values_tl ffvt,

                 pay_cost_allocation_keyflex pck_balancing

           WHERE     flex_value_set_name = 'XXX_GL_ACCOUNT'

                 AND ffv.flex_value_set_id = ffvs.flex_value_set_id

                 AND ffvt.flex_value_id = ffv.flex_value_id

                 AND ffvs.flex_value_set_id = ffv.flex_value_set_id

                 AND ffvt.language = 'US'

                 AND ffv.enabled_flag = 'Y'

                 AND ffv.flex_value = pck_balancing.segment4

                 AND pck_balancing.cost_allocation_keyflex_id =

                        pelf.balancing_keyflex_id)

            balacing_account,

         petf.multiple_entries_allowed_flag,

         petf.processing_priority,

         pelf.standard_link_flag,

         ppg.segment1 Nationality_Type,

         ppg.segment2 Leave_Group,

         ppg.segment3 Work_Category,

         ppg.segment4 Pension_Group,

         ppg.segment5 Employee_Group

    FROM pay_element_types_f petf,

         pay_element_links_f pelf,

         pay_element_classifications pec,

         pay_people_groups ppg

   WHERE     petf.effective_start_date = TO_DATE ('01-JAN-1951')

         AND petf.element_type_id = pelf.element_type_id

         AND pec.classification_id = petf.classification_id

         AND ppg.people_group_id(+) = pelf.people_group_id

ORDER BY element_name