Saturday, May 13, 2017

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

2 comments:

Post a Comment

Best Blogger TipsGet Flower Effect