Oracle Apps: Organization Hierarchy Query to get the organizations in a specific Hierarchy

The below query can be used to get the Organizations which belongs to a hierarchy which is defined in the Organization Hierarchy.

Thanks to Sajeer Omar.

Replace the below hardcoded values before executing :

Business group ID

Organization Hierarchy Name

Top Organization Name

If you want to get only the organization as a specific level, uncomment the commented whereclause.

 select (lpad(' ', (lvl - 1) * 5, ' ') || haou_c.name) org_name  
        ,org_hier.*  
 from  hr_all_organization_units haou_c  
      ,(select hier.organization_id_parent  
              ,hier.organization_id_child  
              ,level lvl  
              ,sys_connect_by_path(hier.organization_id_child, '/') org_level  
              ,sys_connect_by_path(level, '/')                      hier_level  
        from  per_org_structure_elements hier  
             ,(select vers.org_structure_version_id  
               from  per_organization_structures_v struct  
                    ,per_org_structure_versions_v  vers  
               where struct.business_group_id = 0 -- Business group ID  
               and  struct.name = 'AJ Hierarchy' -- Organization Hierarchy Name  
               and  struct.organization_structure_id = vers.organization_structure_id  
               and  struct.business_group_id = vers.business_group_id  
               and  SYSDATE BETWEEN vers.date_from and nvl(vers.date_to, sysdate)  
               and  rownum = 1) orgstr  
        where hier.org_structure_version_id = orgstr.org_structure_version_id  
        and  hier.business_group_id = 0 -- Business group ID  
        start with hier.organization_id_parent =  
                                                (select a.organization_id  
                                                 from  hr_all_organization_units a  
                                                 where a.name = 'AJ Pty Ltd (Australia)') -- Top Organization Name  
              and  hier.org_structure_version_id = orgstr.org_structure_version_id  
              and  hier.business_group_id = 0 -- Business group ID  
        connect by prior organization_id_child = organization_id_parent) org_hier  
 where haou_c.organization_id = org_hier.organization_id_child  
 --and  org_hier.lvl = 2 -- If you want to get only a specific level values  
 order by org_hier.org_level;  

Feel free to point out if anything is missing/wrong in this blog.