BIP – Query to extract Business Units

 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 = 'FUN_BUSINESS_UNIT' 
    AND trunc(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_date