SELECT COMPANY.PARTY_ID COMPANY_ID , COMPANY.PARTY_NAME COMPANY_NAME , COMPANY.ADDRESS1 ADDRESS1 , COMPANY.ADDRESS2 ADDRESS2 , COMPANY.ADDRESS3 ADDRESS3 , COMPANY.ADDRESS4 ADDRESS4 , COMPANY.CITY CITY , COMPANY.COUNTY COUNTY , COMPANY.PROVINCE PROVINCE , COMPANY.STATE STATE , COMPANY.POSTAL_CODE POSTAL_CODE , TERRITORY.TERRITORY_SHORT_NAME COUNTRY FROM HZ_PARTIES COMPANY , FND_TERRITORIES_TL TERRITORY , HZ_CODE_ASSIGNMENTS ENT_COM_ASSIGN WHERE COMPANY.PARTY_TYPE = 'ORGANIZATION' AND ENT_COM_ASSIGN.OWNER_TABLE_ID = COMPANY.PARTY_ID AND ENT_COM_ASSIGN.CLASS_CATEGORY= 'POS_PARTICIPANT_TYPE' AND ENT_COM_ASSIGN.CLASS_CODE = 'ENTERPRISE' AND OWNER_TABLE_NAME = 'HZ_PARTIES' AND COMPANY.STATUS = 'A' AND COMPANY.PARTY_NAME <> '####' AND COMPANY.COUNTRY = TERRITORY.TERRITORY_CODE(+) AND TERRITORY.LANGUAGE(+) = USERENV('LANG')