Query to Get the Ledger name, Legal entity details in Oracle Fusion General Ledger

SELECT  

  le.NAME,

  le.LEGAL_ENTITY_ID,

  le.PARTY_ID,

  le.EFFECTIVE_FROM,

  le.EFFECTIVE_TO,

  lg.CHART_OF_ACCOUNTS_ID,

  lg.ledger_id,

  lg.currency_code ledger_currency,

  le.NAME LEGAL_ENTITY_NAME,

  le.LEGAL_ENTITY_IDENTIFIER,

  reg.REGISTRATION_NUMBER,

  g.COUNTRY_CODE  TERRITORY_SHORT_NAME

 

FROM

  GL_LEDGER_CONFIG_DETAILS primDet ,

  GL_LEDGERS lg ,      

  GL_LEDGER_RELATIONSHIPS rs ,

  GL_LEDGER_CONFIGURATIONS cfg ,  

  GL_LEDGER_CONFIG_DETAILS cfgDet ,

  XLE_ENTITY_PROFILES le ,   

  XLE_REGISTRATIONS reg ,

  hz_geographies g 

 

WHERE 1=1

  AND ( (rs.target_ledger_category_code = 'SECONDARY'          

  AND rs.relationship_type_code <> 'NONE' ) OR (rs.target_ledger_category_code = 'PRIMARY'

  AND rs.relationship_type_code = 'NONE' ) OR (rs.target_ledger_category_code = 'ALC'

  AND rs.relationship_type_code IN ('JOURNAL', 'SUBLEDGER') ) )

 

  AND rs.application_id = 101          

  AND lg.ledger_id = rs.target_ledger_id 

  AND lg.ledger_id = rs.primary_ledger_id 

  AND lg.ledger_category_code = rs.target_ledger_category_code 

  AND nvl(lg.complete_flag, 'Y') = 'Y'   

  AND primDet.object_id = rs.primary_ledger_id                 

  AND primDet.object_type_code = 'PRIMARY'

  AND primDet.setup_step_code = 'NONE'   

  AND cfg.configuration_id = primDet.configuration_id          

  AND cfgDet.configuration_id (+) = cfg.configuration_id       

  AND cfgDet.object_type_code (+) = 'LEGAL_ENTITY'             

  AND cfgDet.setup_step_code (+) = 'NONE'

  AND le.legal_entity_id (+) = cfgDet.object_id                

  AND reg.source_id (+) = cfgDet.object_id

  AND reg.source_table (+) = 'XLE_ENTITY_PROFILES'             

  AND reg.identifying_flag (+) = 'Y'     

  and   g.geography_id = le.geography_id   

  AND lg.LEDGER_CATEGORY_CODE = 'PRIMARY' 

  AND cfgDet.OBJECT_ID IS NOT NULL

 

ORDER BY NAME