Query to get Multi-Org Setup From Backend

SELECT   d.legal_entity_id le_id,

        d.NAME le_name,

        a.ledger_id ledger_id,

         a.short_name ledger_short_name, a.NAME ledger_name,

         a.currency_code currency,

         (SELECT gcc.segment1

            FROM apps.gl_code_combinations_kfv gcc

           WHERE gcc.code_combination_id = a.ret_earn_code_combination_id)  bsv,

         b.organization_id ou_id, b.short_code ou_short_name, b.NAME ou_name,

         c.organization_id inv_org_id, c.organization_code inv_org_code,

         c.organization_name inv_org_name, e.organization_code mtl_org_name,

         e.calendar_code mtl_calendar_code,

         (SELECT concatenated_segments

            FROM apps.gl_code_combinations_kfv

           WHERE code_combination_id = e.material_account) material_account,

         (SELECT concatenated_segments

            FROM apps.gl_code_combinations_kfv

           WHERE code_combination_id = e.material_overhead_account) material_oh_account,

         (SELECT concatenated_segments

            FROM apps.gl_code_combinations_kfv

           WHERE code_combination_id = e.ap_accrual_account) ap_accrual_account,

         (SELECT concatenated_segments

            FROM apps.gl_code_combinations_kfv

           WHERE code_combination_id = e.interorg_receivables_account) ic_ar_account,

         (SELECT concatenated_segments

            FROM apps.gl_code_combinations_kfv

           WHERE code_combination_id = e.interorg_payables_account) ic_ap_account,

         (SELECT concatenated_segments

            FROM apps.gl_code_combinations_kfv

           WHERE code_combination_id = e.cost_of_sales_account) cogs_account,

         (SELECT concatenated_segments

            FROM apps.gl_code_combinations_kfv

           WHERE code_combination_id = e.invoice_price_var_account) ipv_account,

         (SELECT concatenated_segments

            FROM apps.gl_code_combinations_kfv

           WHERE code_combination_id = e.average_cost_var_account)  avg_cost_var_account,

         (SELECT concatenated_segments

            FROM apps.gl_code_combinations_kfv

           WHERE code_combination_id = e.sales_account) sales_account,

         (SELECT concatenated_segments

            FROM apps.gl_code_combinations_kfv

           WHERE code_combination_id = e.expense_account) exp_account,

         e.interorg_trnsfr_charge_percent ic_xfer_percent,

         e.attribute6 wh_link, e.attribute7 inv_org_type,

         e.attribute8 min_max_planning_day, e.attribute10 top_off_allowed,

         e.attribute11 wh_attributes_mandatory, e.attribute12 facility_code,

         e.attribute13 sph_region

    FROM apps.mtl_parameters e,

         apps.xle_entity_profiles d,

         apps.org_organization_definitions c,

         apps.hr_operating_units b,

         apps.gl_ledgers a

   WHERE e.organization_id = c.organization_id

     AND d.legal_entity_id = c.legal_entity

     AND c.disable_date IS NULL

     AND c.organization_name NOT LIKE 'DO NOT USE%'

     AND c.operating_unit = b.organization_id

     AND c.set_of_books_id = a.ledger_id

     AND b.set_of_books_id = a.ledger_id

     AND a.ledger_category_code = 'PRIMARY'

     --AND a.latest_opened_period_name = 'JUN-16'

ORDER BY bsv, ou_id, inv_org_id;