select distinct 'OF' HEADER, null ACTION, null LABEL, offns.updator_date UPDATE_DATE, to_char(offns.updator_date, 'HH24MI') UPDATE_TIME, ac.ori_number AGENCY, inc.inc_report_number INCIDENT_NUMBER, offns.offense_number OFFENSE_UNIQUE_ID, cc.statute_new STATUTE_CODE, --offns.ucr_number UCR_CODE, (select distinct(nibrs_code) from nibrs_offenses where offense_code = offns.offns_cd_offense_code) UCR_CODE, --19389 cc.cha_desc STATUTE_DESCRIPTION, decode(cc.charge_category_code, 'CF', 'F', 'CM', 'M', NULL) LEVEL_SEVERITY_CODE, --offns.csau_code OFFENSE_COMPLETED, --18230 offns.ac_code OFFENSE_COMPLETED, offns.place_place_code LOCATION_TYPE, null AGENCY_LOCATION_CODE, --Not in EJS ia.inc_addr_comment LOCATION_DESCRIPTION, substrb(ejs_addresses.full_street_summary(addr.address_id),1,50) OFFENSE_ADDRESS, addr.city OFFENSE_ADDRESS_CITY, addr.state_cd_state_code OFFENSE_ADDRESS_STATE, substrb(addr.zip5 || '-' || addr.zip4,1,12) OFFENSE_ADDRESS_ZIP, offns.bias_code BIAS_MOTIVATION, --18230 inc.alcohol_related SUSPECTED_ALCOHOL, inc.drug_related SUSPECTED_DRUGS, inc.computer_related SUSPECTED_COMPUTER, inc.gang_related GANG_RELATED, offcd.offense_desc CRIMINAL_ACTIVITY_TYPE_1, null CRIMINAL_ACTIVITY_TYPE_2, --Not Used. null CRIMINAL_ACTIVITY_TYPE_3, --Not Used. weap.wc_weapon_code WEAPON_TYPE, offns.premises NUMBER_OF_PREMISES_ENTERED, offns.FEC_FORCED_ENTRY_CODE METHOD_OF_ENTRY, mo.mo_comment MODUS_OPERANDI, mo.point_of_entry_code POINT_OF_ENTRY_TEXT, inc.incident_id INCIDENT_SYSTEM_NUMBER, addr.latitude OFFENSE_GPS_LATITUDE, addr.longitude OFFENSE_GPS_LONGITUDE, null NCIC_CODE, offcd.offense_desc OFFENSE_DESCRIPTION, offns.charge_code LOCAL_CHARGE_CODE from offenses offns, incident_supplements incsup, incidents inc, agency_codes ac, charge_codes cc, incident_addresses ia, addresses addr, offense_codes offcd, weapons weap, modus_operandi mo where incsup.inc_incident_id = offns.inc_incident_id and incsup.supp_seq = offns.supp_seq and inc.incident_id = offns.inc_incident_id and incsup.isc_status_code = 'A' and ac.agency_code = inc.agncy_cd_agency_code and cc.code (+) = offns.charge_code and ia.incident_id (+) = offns.inc_incident_id and addr.address_id (+) = ia.address_id and offcd.offense_code = offns.offns_cd_offense_code and weap.offnse_inc_incident_id (+) = offns.inc_incident_id and weap.offnse_supp_seq (+) = offns.supp_seq and weap.offnse_offense_number (+) = offns.offense_number and mo.incident_id_offense (+) = offns.inc_incident_id and mo.supp_seq_offense (+) = offns.supp_seq and mo.offense_number (+) = offns.offense_number