Query To Find Immediate Parent Organization

SQL Query to Get Immediate Parent Organization

SELECT HR_GENERAL.DECODE_ORGANIZATION(ose.organization_id_parent)

--  INTO l_org_parent_id

  FROM per_organization_structures os,

       per_org_structure_versions osv,

       per_org_structure_elements ose

 WHERE     os.primary_structure_flag = 'Y'

       AND os.business_group_id = :p_business_group_id

       AND osv.organization_structure_id = os.organization_structure_id

       AND osv.date_from =

              (SELECT MAX (osv1.date_from)

                 FROM per_org_structure_versions osv1

                WHERE osv1.organization_structure_id =

                         osv.organization_structure_id)

       AND ose.org_structure_version_id = osv.org_structure_version_id

       AND ose.organization_id_child = :p_org_id;