SELECT DISTINCT UPPER(i.db_schema) ||'_'|| b.business_number AS ID, UPPER(i.db_schema) AS county, b.business_number AS business_number, b.business_name AS business_name, b.business_id AS business_id, b.btc_type_code AS btc_type_code, ebb.description AS btc_type_desc, b.btc_type_code_type AS btc_type_code_type, b.indx_sec_level_code AS indx_sec_level_code, a.street_number AS street_number, a.dirct_cd_direction_code AS dirct_cd_direction_code, eds.description AS direction_code_desc, a.street_name AS street_name, a.street_cd_street_type_code AS street_cd_street_type_code, a.addr_sc_address_subtype_code AS addr_sc_address_subtype_code, a.sub_number AS sub_number, a.city AS city, a.state_cd_state_code AS state_cd_state_code, ea.description AS state_cd_state_type, a.zip5 AS zip5, a.zip4 AS zip4, b.creator_id AS createdby, b.creator_date AS creation_date, CONV_SOLR_CREATOR_DATE(b.creator_date, (SELECT ec.code FROM ejs_codes ec WHERE ec.code_type = 'DATABASE_TIMEZONE' AND ROWNUM = 1)), b.updator_date AS modification_date, b.updator_id AS modifiedby FROM install i, businesses b LEFT OUTER JOIN business_addresses ba ON b.business_number = ba.business_number LEFT OUTER JOIN addresses a ON ba.address_id = a.address_id LEFT OUTER JOIN ejs_codes ea ON ea.code_type = a.state_cd_state_code_type AND ea.code = a.state_cd_state_code LEFT OUTER JOIN ejs_codes ebb ON ebb.code = b.btc_type_code AND ebb.code_type = b.btc_type_code_type LEFT OUTER JOIN ejs_codes eds ON a.dirct_cd_direction_code = eds.code AND a.dirct_cd_direction_code_type = eds.code_type WHERE B.INDX_TYPE_CODE != 'SOCIETY' and (select count(*) from business_collapse_log where collapse_id=b.business_number)=0 and ( ba.ADDRESS_ID IS NULL OR ( ba.ADDRESS_ID IS NOT NULL AND ba.ADDRESS_ID = (select max(ba2.address_id) from business_addresses ba2 where ba2.business_number = ba.business_number) ) )