SELECT HAO.ORGANIZATION_ID AS BUSINESS_UNIT_ID,
HAOT.NAME,
HAO.BUSINESS_GROUP_ID,
HAO.EFFECTIVE_START_DATE,
HAO.EFFECTIVE_END_DATE
FROM HR_ALL_ORGANIZATION_UNITS_F HAO, HR_ORGANIZATION_UNITS_F_TL HAOT
WHERE HAO.ORGANIZATION_ID = HAOT.ORGANIZATION_ID
AND HAO.EFFECTIVE_START_DATE = HAOT.EFFECTIVE_START_DATE
AND HAO.EFFECTIVE_END_DATE = HAOT.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN HAO.EFFECTIVE_START_DATE AND
HAO.EFFECTIVE_END_DATE
AND HAOT.LANGUAGE = 'US'
ORDER BY HAOT.NAME