SQL Query to Retrieve List of Departments with Department ID and Name

SELECT 
    hauft.organization_id,
    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 TRUNC(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_date

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.