/******************************************************************* *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 ; |