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

Shailender Thallam
4 years ago
/******************************************************************* 
 *PURPOSE: SQL Query to get Org Structure along with LE, BU, Ledger and Flex   *
 *AUTHOR: Shailender Thallam 				           *
 *******************************************************************/
SELECT
       horg.name            enterprise
     , horg.organization_id enterprise_id
     , xep.legal_entity_id
     , xep.name legal_entity
     , glev.flex_segment_value
     , gll.name ledger_name
     , gll.ledger_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.organization_id "BU_ID"
     , hou.name "BU_NAME"
     , hou.short_code "BU_SHORT_CODE"
     , hou.date_from "BU_FROM_DATE"
     , hou.date_to "BU_TO_DATE"
     , hou.set_of_books_id
FROM
       xle_entity_profiles     xep
     , xle_registrations       xr
     , hz_parties              hzp
     , hr_operating_units      hou
     , hr_organization_units   horg
     , gl_legal_entities_bsvs  glev
     , gl_ledger_norm_seg_vals glnsv
     , gl_ledgers              gll
WHERE
       xep.transacting_entity_flag = 'Y'
       AND xep.party_id            = hzp.party_id
       AND xep.legal_entity_id     = xr.source_id
       AND xr.source_table         = 'XLE_ENTITY_PROFILES'
       AND xr.identifying_flag     = 'Y'
       AND xep.legal_entity_id     = hou.default_legal_context_id
       AND horg.organization_id    = hou.business_group_id
       AND glev.legal_entity_id    = xep.legal_entity_id
       AND glnsv.legal_entity_id   = xep.legal_entity_id
       AND gll.ledger_id           = glnsv.ledger_id
;
For Further Reading
