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 , CASE WHEN ( EXISTS (SELECT 1 FROM HZ_CODE_ASSIGNMENTS WHERE OWNER_TABLE_ID = COMPANY.PARTY_ID AND CLASS_CATEGORY = 'POS_PARTICIPANT_TYPE' AND CLASS_CODE = 'ENTERPRISE' ) ) THEN ( SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'COMPANY_TYPE' AND LOOKUP_CODE = 'ENTERPRISE' AND LANGUAGE = USERENV('LANG') ) WHEN ( EXISTS (SELECT 1 FROM HZ_CUST_ACCOUNTS CUSTOMER_ACCOUNT WHERE CUSTOMER_ACCOUNT.PARTY_ID = COMPANY.PARTY_ID AND CUSTOMER_ACCOUNT.STATUS = 'A' ) ) THEN ( SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'COMPANY_TYPE' AND LOOKUP_CODE = 'CUSTOMER' AND LANGUAGE = USERENV('LANG') ) ELSE ( SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'COMPANY_TYPE' AND LOOKUP_CODE = 'VENDOR' AND LANGUAGE = USERENV('LANG') ) END AS COMPANY_TYPE FROM HZ_PARTIES COMPANY WHERE COMPANY.PARTY_TYPE = 'ORGANIZATION' AND COMPANY.STATUS = 'A' AND COMPANY.PARTY_NAME <> '####' AND ( EXISTS (SELECT 1 FROM HZ_CODE_ASSIGNMENTS WHERE OWNER_TABLE_ID = COMPANY.PARTY_ID AND CLASS_CATEGORY = 'POS_PARTICIPANT_TYPE' AND CLASS_CODE = 'ENTERPRISE' ) OR EXISTS (SELECT 1 FROM HZ_CUST_ACCOUNTS WHERE PARTY_ID = COMPANY.PARTY_ID AND STATUS = 'A' ) OR EXISTS (SELECT 1 FROM AP_SUPPLIERS WHERE PARTY_ID = COMPANY.PARTY_ID) )