Business Units

SELECT 
     haotl.NAME                 BU_NAME, 
     hao.LOCATION_ID            LOCATION_ID, 
     haotl.BUSINESS_GROUP_ID    BUSINESS_GROUP_ID, 
     haotl.EFFECTIVE_START_DATE DATE_FROM, 
     haotl.EFFECTIVE_END_DATE   DATE_TO, 
     hoi.ORG_INFORMATION1       MANAGER_ID, 
     hoi.ORG_INFORMATION2       LEGAL_ENTITY_ID, 
     hoi.ORG_INFORMATION3       PRIMARY_LEDGER_ID, 
     hoi.ORG_INFORMATION4       DEFAULT_SET_ID, 
     haotl.NAME                 SHORT_CODE, 
     hoi.ORG_INFORMATION6       ENABLED_FOR_HR_FLAG, 
     hoi.ORG_INFORMATION7       FIN_BUSINESS_UNIT_ID, 
     hoi.ORG_INFORMATION8       DEFAULT_CURRENCY_CODE, 
     hoi.ORG_INFORMATION9       PROFIT_CENTER_FLAG, 
     hoi.CREATED_BY             CREATED_BY, 
     hoi.CREATION_DATE          CREATION_DATE, 
     hoi.LAST_UPDATED_BY        LAST_UPDATED_BY, 
     hoi.LAST_UPDATE_DATE       LAST_UPDATE_DATE, 
     hoi.LAST_UPDATE_LOGIN      LAST_UPDATE_LOGIN 
 FROM 
     HR_ALL_ORGANIZATION_UNITS_F hao, 
     HR_ORGANIZATION_UNITS_F_TL haotl, 
     HR_ORG_UNIT_CLASSIFICATIONS_F houc, 
     HR_ORGANIZATION_INFORMATION_F hoi 
 WHERE 
     hao.ORGANIZATION_ID = haotl.ORGANIZATION_ID 
     AND houc.ORGANIZATION_ID = haotl.ORGANIZATION_ID 
     AND houc.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT' 
     AND hoi.ORGANIZATION_ID = haotl.ORGANIZATION_ID 
     AND hoi.ORG_INFORMATION_CONTEXT = houc.CLASSIFICATION_CODE 
     AND TRUNC(SYSDATE) BETWEEN haotl.EFFECTIVE_START_DATE AND haotl.EFFECTIVE_END_DATE 
     AND haotl.LANGUAGE = USERENV('LANG') 
     AND haotl.EFFECTIVE_START_DATE = hao.EFFECTIVE_START_DATE 
     AND haotl.EFFECTIVE_END_DATE = hao.EFFECTIVE_END_DATE 
 ORDER BY BU_NAME;