Query to find Legal Entity and its child organizations

 

Query to find Legal Entity and its child organizations

The following Query will return the Legal Entity and its Child Organizations, with Org id, org code, org name, operating unit name, set of books, Ledger, legal entity name:

Select a.organization_id, a.organization_code, a.organization_name name,                 a.operating_unit, b.name OU, a.set_of_books_id,d.name LEDGER,                 a.legal_entity,c.name LE_NAME                 From  apps. ORG_ORGANIZATION_DEFINITIONS a,                 apps. HR_OPERATING_UNITS b,                 apps. xle_entity_profiles c,                 apps. gl_ledgers d                 Where a.operating_unit=b.organization_id                 AND c.legal_entity_id=a.legal_entity                 AND d.ledger_id=a.set_of_books_id                 --and a.organization_id = a.legal_entity                 and b.organization_id in (24157);