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, b.indx_sec_level_code AS indx_sec_level_code, b.indx_sec_level_id AS indx_sec_level_id, b.indx_sec_level_agency AS indx_sec_level_agency, b.active_subscription AS active_subscription, b.btc_type_code_type AS btc_type_code_type, b.indx_type_code AS indx_type_code, ejs.ejs_comment AS ejs_comment, a.address_id AS address_id, a.street_number AS street_number, a.dirct_cd_direction_code AS dirct_cd_direction_code, 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, a.zip5 AS zip5, a.zip4 AS zip4, TO_CHAR (ba.date_of_info, 'MM/DD/YYYY HH:MI AM') AS ba_date_of_info, TO_CHAR (ba.occupied_from,'MM/DD/YYYY HH:MI AM') AS occupied_from, TO_CHAR (ba.occupied_to,'MM/DD/YYYY HH:MI AM') AS occupied_to, ba.bus_addr_comment AS bus_addr_comment, ba.servable_count AS servable_count, ba.address_type_code AS address_type_code, bat.keyword AS keyword, TO_CHAR (bat.date_of_info, 'MM/DD/YYYY HH:MI AM') AS bat_date_of_info, bat.attachment_desc AS attachment_desc, br.business_number_related AS business_number_related, br.bus_relation_code AS bus_relation_code, br.bus_relation_code_type AS bus_relation_code_type, br.emergency_contact_yn AS br_emergency_cont_yn, TO_CHAR (br.date_of_info,'MM/DD/YYYY HH:MI AM') AS br_date_of_info, br.comments AS comments, TO_CHAR (pb.date_of_info,'MM/DD/YYYY HH:MI AM') AS pb_date_of_info, pb.emergency_contact_yn AS pb_emergency_cont_yn, pb.comments AS pb_comments, pb.peop_bus_role_code AS peop_bus_role_code, pb.peop_bus_role_code_type AS peop_bus_role_code_type, e.employment_no AS employment_no, e.per_person_id AS per_person_id, e.emp_type_employment_code AS emp_type_employment_code, e.employment_length AS employment_length, TO_CHAR (e.start_date, 'MM/DD/YYYY HH:MI AM') AS start_date, e.hours_per_week AS hours_per_week, e.income AS income, e.reason_for_leaving AS reason_for_leaving, e.ocupation AS ocupation, TO_CHAR (e.date_of_info,'MM/DD/YYYY HH:MI AM') AS e_date_of_info, e.employer_name AS employer_name, e.employment_code AS employment_code, e.employment_code_type AS employment_code_type, mn.lname AS emplastname, mn.fname AS empfirstname, mn.mname AS empmiddlename, mn.dob AS empdob, (FLOOR (MONTHS_BETWEEN (SYSDATE, mn.dob) / 12)) AS empage, bv.role_code AS role_code, TO_CHAR (bv.date_of_info,'MM/DD/YYYY HH:MI AM') AS bv_date_of_info, bv.role_code_type AS role_code_type, bv.vehicle_id AS vehicle_index_id, v.vin AS vehiclein, v.YEAR AS vehicleyear, v.vst_vehicle_style_code AS vstylecode, v.YEAR AS vyear, mnpb.lname AS bplastname, mnpb.fname AS bpfirstname, mnpb.mname AS bpmiddlename, mnpb.dob AS bpdob, (FLOOR (MONTHS_BETWEEN (SYSDATE, mnpb.dob) / 12) ) AS bpage, v.vma_vehicle_make_code AS vmakecode, v.vmo_vehicle_model_code AS vmodelcode, vt.type_desc AS vtype, vm.vehicle_make_desc AS vmake, ec.description AS bus_relation_desc, atp.address_type_desc AS buss_address_type_desc, ea.description AS state_cd_state_type, vmd.vehicle_model_desc AS vmodel, ejsc.description AS people_role_desc, vs.vehicle_style_desc AS vstyle, ebb.description AS btc_type_desc, eds.description AS direction_code_desc, mn.name_type_code AS name_type_code, p.person_id AS ass_person_id, br.business_number AS ass_buss_id, ph.pho_typ_cd_phone_type_code AS phone_type_code, ph.AREA AS phone_area, ph.prefix AS phone_prefix, ph.suffix AS phone_suffix, ph.ext AS phone_extension, b.creator_id AS createdby, b.creator_date AS creation_date, to_char(from_tz(cast(b.creator_date as timestamp), (select ec.code from ejs_codes ec where ec.code_type = 'DATABASE_TIMEZONE' and rownum = 1)) at time zone 'UTC','YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'), b.updator_date AS modification_date, b.updator_id AS modifiedby FROM install i, businesses b LEFT OUTER JOIN ejs_comments ejs ON b.rec_id = ejs.rec_id 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 business_attachments bat ON b.business_number = bat.business_number LEFT OUTER JOIN business_relations br ON b.business_number = br.business_number LEFT OUTER JOIN business_vehicles bv ON bv.business_number = b.business_number LEFT OUTER JOIN people_businesses pb ON pb.business_number = b.business_number LEFT OUTER JOIN employments e ON e.busns_business_number = b.business_number LEFT OUTER JOIN master_names mn ON mn.per_person_id = e.per_person_id LEFT OUTER JOIN vehicles v ON bv.vehicle_id = v.vehicle_id LEFT OUTER JOIN master_names mnpb ON mnpb.per_person_id = pb.person_id LEFT OUTER JOIN vehicle_type_codes vt ON vt.type_code = v.vtc_type_code LEFT OUTER JOIN vehicle_makes vm ON vm.vehicle_make_code = v.vma_vehicle_make_code AND vm.vtc_type_code = v.vtc_type_code LEFT OUTER JOIN ejs_codes ejsc ON ejsc.code = pb.peop_bus_role_code AND ejsc.code_type = pb.peop_bus_role_code_type LEFT OUTER JOIN ejs_codes ec ON ec.code = br.bus_relation_code AND ec.code_type = br.bus_relation_code_type LEFT OUTER JOIN address_type_codes atp ON ba.address_type_code = atp.address_type_code 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 vehicle_models vmd ON vmd.vehicle_model_code = v.vmo_vehicle_model_code AND vmd.vma_vehicle_make_code = v.vma_vehicle_make_code AND vmd.vtc_type_code = v.vtc_type_code LEFT OUTER JOIN ejs_codes eb ON UPPER (eb.code_type) = 'INDEX_TYPE_CODES' AND eb.code = b.indx_type_code LEFT OUTER JOIN vehicle_styles vs ON vs.vehicle_style_code = v.vst_vehicle_style_code AND vs.vtc_type_code = v.vtc_type_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 LEFT OUTER JOIN people p ON p.person_id = pb.person_id LEFT OUTER JOIN business_addr_phones bap on b.business_number = bap.business_number and ba.address_id = bap.address_id LEFT OUTER JOIN phones ph on bap.phone_id = ph.phone_id WHERE B.INDX_TYPE_CODE != 'SOCIETY' and (select count(*) from business_collapse_log where collapse_id=b.business_number)=0