Query to get HR Orgnization Grant Parent

Here is the query to get grant parent of organization

 

 

SELECT  OSE.organization_id_child,OU.organization_id,OU.name, OU.type
      FROM   (SELECT  POSE.organization_id_child,
                      POSE.organization_id_parent
             FROM  per_org_structure_elements POSE
             WHERE POSE.org_structure_version_id  = (SELECT OSV.org_structure_version_id
                                                     FROM   per_org_structure_versions OSV,
                                                        per_organization_structures OS
                                                     WHERE SYSDATE BETWEEN OSV.date_from AND NVL (OSV.date_to,SYSDATE + 1)
                                                     AND OS.ORGANIZATION_STRUCTURE_ID = OSV.ORGANIZATION_STRUCTURE_ID
                                                     AND UPPER(OS.name)               = 'XX You Company'  -------------------------------------------------------------
                                                     AND OS.primary_structure_flag    = 'Y' ))OSE,
                   hr_all_organization_units  OU
      WHERE   OSE.organization_id_parent      =  OU.organization_id
    --  AND     UPPER(OU.name)                  = 'GROUP 2'
      AND     OU.type = 'AGP'
      CONNECT BY PRIOR organization_id_parent = organization_id_child
      START WITH organization_id_child =  (select paa.organization_id
      from
                   per_all_people_F pea
                   ,per_all_assignments_f paa
where pea.person_id=paa.person_id
      and pea.person_id=:p_person_id
      and sysdate between pea.effective_start_date and NVL(pea.effective_end_date,sysdate+1)
      and sysdate between paa.effective_start_date and NVL(paa.effective_end_date,sysdate+1)
      and paa.assignment_type='E')