SELECT DISTINCT HOU.ORGANIZATION_ID , HOU.NAME ORG_NAME , HLC.MEANING TYPE , HL.DESCRIPTION LOCATION , HL.TOWN_OR_CITY , HL.REGION_2 STATE , HL.COUNTRY , HOU.BUSINESS_GROUP_ID , HOU.DATE_FROM , HOU.DATE_TO FROM HR_ORGANIZATION_UNITS HOU , HR_LOCATIONS HL , HR_ORGANIZATION_INFORMATION HOI , HR_LOOKUPS HLC WHERE HOU.ORGANIZATION_ID=HOI.ORGANIZATION_ID AND HOI.ORG_INFORMATION_CONTEXT||''='CLASS' AND HOI.ORG_INFORMATION1 ='HR_ORG' AND HOI.ORG_INFORMATION2='Y' AND HOU.INTERNAL_EXTERNAL_FLAG = 'INT' AND HOU.LOCATION_ID=HL.LOCATION_ID(+) AND HOU.TYPE =HLC.LOOKUP_CODE (+) AND HLC.LOOKUP_TYPE (+) ='ORG_TYPE' AND HLC.ENABLED_FLAG(+)='Y'