In this article, we will fetch the mapping between Business Units and Reference Data Sets.
SELECT HAUFT.NAME AS BU_CODE,
HAUFT.NAME AS BU_DESCRIPTION,
HOUCF.LEGISLATION_CODE AS COUNTRY,
TO_CHAR(HAUFT.EFFECTIVE_START_DATE, 'YYYY/MM/DD') AS EFFECTIVE_START_DATE,
TO_CHAR(HAUFT.EFFECTIVE_END_DATE, 'YYYY/MM/DD') AS EFFECTIVE_END_DATE,
HOUCF.STATUS AS STATUS,
FS.SET_CODE AS SET_CODE,
FS.SET_NAME AS SET_NAME,
HAUFT.LAST_UPDATED_BY AS LAST_UPDATED_BY,
TO_CHAR(HAUFT.LAST_UPDATE_DATE, 'YYYY/MM/DD') AS LAST_UPDATE_DATE
FROM HR_ORG_UNIT_CLASSIFICATIONS_F HOUCF,
HR_ALL_ORGANIZATION_UNITS_F HAOUF,
HR_ORGANIZATION_UNITS_F_TL HAUFT,
HR_ORGANIZATION_INFORMATION_F HOIF,
FND_SETID_SETS_VL FS
WHERE HAOUF.ORGANIZATION_ID = HOUCF.ORGANIZATION_ID
AND HAOUF.ORGANIZATION_ID = HAUFT.ORGANIZATION_ID
AND HAOUF.ORGANIZATION_ID = HOIF.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 FS.SET_ID(+) = HOIF.ORG_INFORMATION4
AND HOUCF.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'
