Level1	Level 2	Level 3
Org Id 81		
	102	
	103	
	104	
		111 (parent 104)
		112(parent 104)
	105	

 

    Query to get hierarchy:  
    SELECT  
    LPAD(' ',10*(LEVEL-1)) || org.name hierarchy,ose.organization_id_parent,  
    org.organization_id  
    FROM  
    hr_all_organization_units org,  
    per_org_structure_elements ose  
    WHERE 1=1  
    AND org.organization_id = ose.organization_id_child  
    AND ose.org_structure_version_id = 61  
    --and org.organization_id = 340  
    START WITH  
    ose.organization_id_parent = 81  
    CONNECT BY PRIOR  
    ose.organization_id_child = ose.organization_id_parent  
    ORDER SIBLINGS BY  
    org.location_id,  
    ose.organization_id_child   
      
    Required  Columns:  
      
    SELECT haou.name, papf.EMPLOYEE_NUMBER, papf.full_name  
    FROM   
    per_org_structure_elements ose  
    ,hr_all_organization_units haou  
    ,per_all_assignments_f paaf  
    ,per_all_people_f papf  
    WHERE ose.ORGANIZATION_ID_PARENT = 81  
    AND ose.ORG_STRUCTURE_VERSION_ID = 61  
    AND haou.organization_id = ose.organization_id_child  
    --and paaf.organization_id = ose.organization_id_child  
    and papf.person_id = paaf.person_id  
    and paaf.organization_id = haou.organization_id  
    and :P_GIVEN_DATE between papf.effective_start_date and papf.effective_end_date  
    and :P_GIVEN_DATE between paaf.effective_start_date and paaf.effective_end_date