Here is the Oracle SQL Query for the Business Unit
Please use this as a reference and modify this as per the requirements.
select distinct
FBU.BU_NAME as "Name",
HBUL.Location_name as "Location Nane",
ppnf.full_NAME as "Manager",
FBU.status as "Active Flag",
FSS.set_name as "Default Set"
from
FUN_ALL_BUSINESS_UNITS_V FBU,
HR_BU_LOCATIONS_X HBUL,
per_person_names_f ppnf,
FND_SETID_SETS FSS
where
FBU.LOCATION_ID = HBUL.LOCATION_ID (+)
and FBU.MANAGER_ID = ppnf.person_id (+)
and ppnf.name_type (+)= 'GLOBAL'
and sysdate between ppnf.EFFECTIVE_START_DATE(+) and ppnf.EFFECTIVE_END_DATE(+)
and FBU.Default_set_id = FSS.set_id
and FSS.language ='US'
ORDER BY 1
