Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

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

4 comments:

Jefferson G. Camargo said...

Really great!

Thangamani K said...

Great effort

Anonymous said...

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

Raju Chinthapatla said...

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 (https://t.me/apps88) in telegram.

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect