Digression: Querying for Organizations, Operating Units, Legal Entities and Ledgers in an R12 Environment

Invariably in an Oracle Applications environment, one needs to be aware of and know the relationship between organizations, operating units, legal entities and ledgers.

This query is just a variation of the apps.org_organization_definitions Oracle Applications view whose results provide this insight.  I take no credit for this:

 select
 hou.organization_id organization_id,
    mp.organization_code organization_code,
    lgr.ledger_id,
    decode(hoi2.org_information_context, 'Accounting Information', to_number(hoi2.org_information3), to_number(null)) operating_unit,
    decode(hoi2.org_information_context, 'Accounting Information', to_number(hoi2.org_information2), null) legal_entity
  from hr.hr_all_organization_units hou,
    hr.hr_organization_information hoi1,
    hr.hr_organization_information hoi2,
    inv.mtl_parameters mp,
    gl.gl_ledgers lgr
  where hou.organization_id = hoi1.organization_id
  and hou.organization_id   = hoi2.organization_id
  and hou.organization_id   = mp.organization_id
  and hoi1.org_information1 = 'INV'
  and hoi1.org_information2 = 'Y'
  and ( hoi1.org_information_context || '') = 'CLASS'
  and ( hoi2.org_information_context || '') ='Accounting Information'
  and to_number(decode(rtrim(translate(hoi2.org_information1,'0123456789',' ')), null, hoi2.org_information1,-99999)) = lgr.ledger_id
  and lgr.object_type_code ='L'
  and nvl(lgr.complete_flag,'Y') ='Y'