Query HRMS organization hierarchies

Query below returns all primary organization hierarchy versions with validity period (date from - date to) of each hierarchy version.
Adapt to your needs as necessary.

select level, hcy.*

from

(

select vsn.org_structure_version_id

,      vsn.date_from

,      nvl(vsn.date_to, hr_general.end_of_time)  date_to

,      ele.organization_id_parent

,      ele.organization_id_child

from   per_organization_structures  ose

,      per_org_structure_versions   vsn

,      per_org_structure_elements   ele

where  ose.primary_structure_flag    = 'Y'

and    ose.organization_structure_id = vsn.organization_structure_id

and    ele.org_structure_version_id  = vsn.org_structure_version_id

) hcy

connect by prior hcy.organization_id_child = hcy.organization_id_parent

       and prior hcy.org_structure_version_id = hcy.org_structure_version_id

start with hcy.organization_id_parent = :p_organization_id