SELECT HR.ORGANIZATION_ID , HR.NAME , HR.BUSINESS_GROUP_ID FROM HR_ORGANIZATION_UNITS HR WHERE HR.ORGANIZATION_ID IN ( SELECT SE.ORGANIZATION_ID_CHILD FROM PER_ORG_STRUCTURE_ELEMENTS SE WHERE SE.ORG_STRUCTURE_VERSION_ID = ( SELECT APR_ORG_STRUCTURE_VERSION_ID FROM IGW_IMPLEMENTATIONS ) CONNECT BY PRIOR SE.ORGANIZATION_ID_CHILD = SE.ORGANIZATION_ID_PARENT AND PRIOR SE.ORG_STRUCTURE_VERSION_ID = SE.ORG_STRUCTURE_VERSION_ID START WITH SE.ORGANIZATION_ID_PARENT = ( SELECT APR_ORG_STRUCTURE_START_ID FROM IGW_IMPLEMENTATIONS ) UNION SELECT APR_ORG_STRUCTURE_START_ID FROM IGW_IMPLEMENTATIONS ) AND TRUNC(SYSDATE) BETWEEN DATE_FROM AND NVL(DATE_TO , TRUNC(SYSDATE))