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 , (SELECT TERRITORY_SHORT_NAME FROM FND_TERRITORIES_TL WHERE COMPANY.COUNTRY = TERRITORY_CODE AND LANGUAGE = USERENV('LANG')) AS COUNTRY FROM HZ_PARTIES COMPANY WHERE COMPANY.PARTY_TYPE = 'ORGANIZATION' AND COMPANY.STATUS = 'A' AND COMPANY.PARTY_NAME <> '####' AND EXISTS (SELECT 1 FROM HZ_CUST_ACCOUNTS CUSTOMER_ACCOUNT WHERE CUSTOMER_ACCOUNT.PARTY_ID = COMPANY.PARTY_ID AND CUSTOMER_ACCOUNT.STATUS = 'A')