select distinct 'SU' HEADER, null ACTION, null LABEL, ip.updator_date UPDATE_DATE, to_char(ip.updator_date, 'HH24MI') UPDATE_TIME, ac.ori_number AGENCY, inc.inc_report_number INCIDENT_NUMBER, ip.per_person_id SUSPECT_UNIQUE_ID, --Person id mn.lname LAST_NAME, mn.fname FIRST_NAME, mn.mname MIDDLE_NAME, mn.title_code SUFFIX_NAME, mn.ssn SOCIAL_SECURITY_NUMBER, mn.dob DATE_OF_BIRTH, bdm_age(mn.dob, ip.updator_date) AGE, --Age as of UPDATE_DATE --p.sex_sex_code GENDER, --18230 --p.race_race_code RACE, --18230 --p.ethnicity_code ETHNICITY, --18230 mp.sex_code GENDER, --18230 mp.race_code RACE, --18230 mp.ethnicity_code ETHNICITY, --18230 null RESIDENT_STATUS, --Not in EJS RMS null US_CITIZEN, --Not in EJS RMS (select id_number from misc_ids where updator_date = (select max(updator_date) from misc_ids mi where per_person_id = p.person_id and misc_cd_misc_id_code = misc.misc_cd_misc_id_code) and rownum = 1) PASSPORT_VISA_NUMBER, null IMMIGRATION_DOCUMENT_NUMBER, --Not in EJS RMS null COUNTRY_OF_BIRTH, --Not in EJS RMS (select id_number from misc_ids where updator_date = (select max(updator_date) from misc_ids mi2 where per_person_id = p.person_id and misc_cd_misc_id_code = misc2.misc_cd_misc_id_code) and rownum = 1) DRIVERS_LICENSE_NUMBER, (select state_cd_state_code from misc_ids where updator_date = (select max(updator_date) from misc_ids mi2 where per_person_id = p.person_id and misc_cd_misc_id_code = misc2.misc_cd_misc_id_code) and rownum = 1) DRIVERS_LICENSE_STATE, (select misc_id_type from misc_ids where updator_date = (select max(updator_date) from misc_ids mi2 where per_person_id = p.person_id and misc_cd_misc_id_code = misc2.misc_cd_misc_id_code) and rownum = 1) DRIVERS_LICENSE_CLASS, (select substrb(ejs_addresses.full_street_summary(a1.address_id),1,50) from addresses a1 where a1.address_id = (select max(address_id) from person_addresses pa where pa.person_id = p.person_id and address_type_code = 'RES')) HOME_ADDRESS, (select city from addresses a1 where a1.address_id = (select max(address_id) from person_addresses pa where pa.person_id = p.person_id and address_type_code = 'RES')) HOME_ADDRESS_CITY, (select state_cd_state_code from addresses a1 where a1.address_id = (select max(address_id) from person_addresses pa where pa.person_id = p.person_id and address_type_code = 'RES')) HOME_ADDRESS_STATE, (select substrb(zip5 || '-' || zip4,1,12) from addresses a1 where a1.address_id = (select max(address_id) from person_addresses pa where pa.person_id = p.person_id and address_type_code = 'RES')) HOME_ADDRESS_ZIP_CODE, (select area || '-' || prefix || '-' || suffix from phones where phone_id = (select max(phone_id) from phones where per_person_id = p.person_id and pho_typ_cd_phone_type_code = pho.pho_typ_cd_phone_type_code)) HOME_PHONE, (select area || '-' || prefix || '-' || suffix from phones where phone_id = (select max(phone_id) from phones where per_person_id = p.person_id and pho_typ_cd_phone_type_code = pho2.pho_typ_cd_phone_type_code)) CELL_PHONE, null EMPLOYER_ADDRESS, --Not in EJS RMS null EMPLOYER_ADDRESS_CITY, --Not in EJS RMS null EMPLOYER_ADDRESS_STATE, --Not in EJS RMS null EMPLOYER_ADDRESS_ZIP_CODE, --Not in EJS RMS null WORK_PHONE, --Not in EJS RMS null EMPLOYER_NAME, --Not in EJS RMS null OCCUPATION, --Not in EJS RMS null JUVENILE_INDICATOR, --Use Age inc.alcohol_related SUSPECTED_ALCOHOL, inc.drug_related SUSPECTED_DRUGS, (select hair_cd_hair_code from physical_descriptions where date_of_info = (select max(date_of_info) from physical_descriptions where per_person_id = p.person_id) and rownum = 1) HAIR_COLOR, (select eye_cd_eye_code from physical_descriptions where date_of_info = (select max(date_of_info) from physical_descriptions where per_person_id = p.person_id) and rownum = 1) EYE_COLOR, (select height from physical_descriptions where date_of_info = (select max(date_of_info) from physical_descriptions where per_person_id = p.person_id) and rownum = 1) HEIGHT, (select weight from physical_descriptions where date_of_info = (select max(date_of_info) from physical_descriptions where per_person_id = p.person_id) and rownum = 1) WEIGHT, (select description from smts where smt_id = (select max(smt_id) from SMTS where per_person_id = p.person_id) and rownum = 1) SCARS_MARKS_TATTOOS, null RELATIONSHIP_SUSPECT_VICTIM, null DNA_INDICATOR, --Not in EJS RMS null REGISTERED_OFFENDER_INDICATOR, null BIRTH_CITY_TEXT, null BIRTH_STATE, misc3.id_number FBI_ID, REPLACE(mn2.lname || ', ' || NVL(mn2.fname, ',NULL'), ',NULL') ALTERNATE_NAME_1, 'ALIAS' ALTERNATE_NAME_1_TYPE, null MINIMUM_HEIGHT, --Not in EJS RMS null MINIMUM_WEIGHT, --Not in EJS RMS null MAXIMUM_HEIGHT, --Not in EJS RMS null MAXIMUM_WEIGHT, --Not in EJS RMS (select bld_cd_build_code from physical_descriptions where date_of_info = (select max(date_of_info) from physical_descriptions where per_person_id = p.person_id) and rownum = 1) BUILD, (select glasses from physical_descriptions where date_of_info = (select max(date_of_info) from physical_descriptions where per_person_id = p.person_id) and rownum = 1) EYEWEAR, null DENTAL_CHARACTERISTIC, --Not in EJS RMS null SPEECH, (select skn_cd_skin_code from physical_descriptions where date_of_info = (select max(date_of_info) from physical_descriptions where per_person_id = p.person_id) and rownum = 1) SKIN_TONE, null MINIMUM_AGE, --Not in EJS RMS null MAXIMUM_AGE, --Not in EJS RMS inc.incident_id INCIDENT_SYSTEM_NUMBER, null ALTERNATE_NAME_2, null ALTERNATE_NAME_2_TYPE, null ALTERNATE_NAME_3, null ALTERNATE_NAME_3_TYPE, null SUSPECT_DESCRIPTION, (select id_number from misc_ids where updator_date = (select max(updator_date) from misc_ids mi2 where per_person_id = p.person_id and misc_cd_misc_id_code = misc4.misc_cd_misc_id_code) and rownum = 1) SID from incident_people ip, incident_person_roles ipr, --18230 incidents inc, agency_codes ac, master_names mn, people p, master_people mp, --18230 misc_ids misc, misc_ids misc2, --person_addresses padd, --addresses addr, phones pho, phones pho2, physical_descriptions pd, smts smt, misc_ids misc3, incident_arrests ia, --warrants warr, e_warrants ewar, agency_codes ac2, master_names mn2, misc_ids misc4, incident_supplements isup --where ip.role_role_type = 'S' --18230 where ipr.role_type = 'S' --18230 and ip.inc_per_id = ipr.inc_per_iD --18230 and isup.inc_incident_id = ip.inc_incident_id and isup.supp_seq = ip.supp_seq and inc.incident_id = isup.inc_incident_id and isup.isc_status_code = 'A' and ac.agency_code = inc.agncy_cd_agency_code and mn.per_person_id = ip.per_person_id and mn.mn_id = ip.mn_mn_id and p.person_id = mn.per_person_id and p.master_person_id = mp.master_person_id --18230 and misc.per_person_id (+) = p.person_id and misc.misc_cd_misc_id_code (+) = 'PP' and misc2.per_person_id (+) = p.person_id and misc2.misc_cd_misc_id_code (+) = 'DL' --and padd.person_id (+) = p.person_id --and padd.address_type_code (+) = 'RES' --and addr.address_id = padd.address_id and pho.per_person_id (+) = p.person_id and pho.pho_typ_cd_phone_type_code (+) = 'RES' and pho2.per_person_id (+) = p.person_id and pho2.pho_typ_cd_phone_type_code (+) = 'CEL' and pd.per_person_id (+) = p.person_id and smt.per_person_id (+) = p.person_id and misc3.per_person_id (+) = p.person_id and misc3.misc_cd_misc_id_code (+) = 'X1' and ia.incsup_inc_incident_id (+) = inc.incident_id --and warr.arrest_id (+) = ia.arr_arrest_id --20238 --and ewar.warrant_id (+) = warr.ewar_warrant_id --20238 --and ac2.agency_code (+) = ewar.agncy_cd_agency_code --18230 and ac2.agency_code (+) = ewar.agency_code --18230 and mn2.per_person_id (+) = p.person_id --and mn2.nme_typ_name_type_code (+) = 'A' --18230 and mn2.name_type_code (+) = 'A' --18230 and misc4.per_person_id (+) = p.person_id and misc4.misc_cd_misc_id_code (+) = 'X2'