Oracle ERP Cloud – SQL Query to get Organization Structure along with Legal Entity, Business Unit and Ledger

Shailender Thallam
4 years ago
/******************************************************************* 
 *PURPOSE: SQL Query to get Organization Structure along with LE, BU and Ledger    *
 *AUTHOR: Shailender Thallam 				           *
 *******************************************************************/
SELECT
       horg.name            enterprise
     , horg.organization_id enterprise_id
     , xr.registered_name
     , xr.alternate_registered_name
     , xr.registration_number
     , xr.place_of_registration
     , xr.effective_from
     , xr.effective_to
     , xep.name "LEGAL_ENTITY_NAME"
     , xep.legal_entity_identifier
     , hou.business_group_id
     , hou.organization_id "BU_ID"
     , hou.name "BU_NAME"
     , hou.date_from "BU_FROM_DATE"
     , hou.date_to "BU_TO_DATE"
     , hou.short_code "BU_SHORT_CODE"
     , hou.set_of_books_id
     , gll.name ledger_name
     , gll.ledger_id
FROM
       xle_entity_profiles     xep
     , xle_registrations       xr
     , hr_operating_units      hou
     , hr_organization_units   horg
     , gl_ledger_norm_seg_vals glnsv
     , gl_ledgers              gll
WHERE
       xep.legal_entity_id       = xr.source_id
       AND xr.source_table       = 'XLE_ENTITY_PROFILES'
       AND xep.legal_entity_id   = hou.default_legal_context_id(+)
       AND horg.organization_id  = hou.business_group_id
       AND glnsv.legal_entity_id = xep.legal_entity_id
       AND gll.ledger_id         = glnsv.ledger_id
;
For Further Reading
