Oracle Fusion HCM: Department Query

SELECT hauft.NAME

FROM   HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf, HR_ORGANIZATION_UNITS_F_TL hauft

WHERE   haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID

AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID

AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE

AND hauft.LANGUAGE = 'US'

AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE

AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE

AND houcf.CLASSIFICATION_CODE = 'DEPARTMENT'

AND     SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date

AND EXISTS

(SELECT 1

FROM    anc_per_acrl_entry_dtls apaed,

            per_all_assignments_m paam

WHERE apaed.assignment_id = paam.assignment_id

AND     paam.primary_flag = 'Y'

AND     paam.assignment_type = 'E'

AND     TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

AND     paam.organization_id = haouf.organization_id)