SQL for getting Business Unit details and Reference Data Set in Oracle HCM Cloud environment

Sample SQL Query (You can modify as required):

-----------------------------------

SELECT

         'BUSINESS_UNIT' AS TYPE

,HAUFT.NAME AS CODE

,HAUFT.NAME AS 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

,HOIF.ORG_INFORMATION4 AS ORG_INFORMATION4

,FS.SET_ID AS SET_ID

,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

,(SELECT SET_ID, SET_CODE, SET_NAME FROM 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'