select distinct 'VI' 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 VICTIM_UNIQUE_ID, --Person id mn.lname LAST_NAME, mn.fname FIRST_NAME, mn.mname MIDDLE_NAME, mn.title_code SUFFIX_NAME, 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_NAME, --Not in EJS RMS 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 OCCUPATION, --Not in EJS RMS null JUVENILE_INDICATOR, --Use Age null SUSPECTED_USING_ALCOHOL, null SUSPECTED_USING_DRUGS, ip.victm_cd_victim_type_code VICTIM_TYPE, null AGGREAVATED_ASSAULT_HOMICIDE, null JUSTIFIABLE_HOMICIDE, null INJURY_TYPE, null RELATIONSHIP_VICTIM_TO_SUSPECT, null VICTIM_IS_COMPLAINANT, null INJURY_DESCRIPTION, null ORGANIZATION_NAME, null ORGANIZATION_ADDRESS, null ORGANIZATION_ADDRESS_CITY, null ORGANIZATION_ADDRESS_STATE, null ORGANIZATION_ADDRESS_ZIP_CODE, null ORGANIZATION_PHONE, inc.incident_id INCIDENT_SYSTEM_NUMBER, (select description from smts where smt_id = (select max(smt_id) from SMTS where per_person_id = p.person_id) and rownum = 1) SMT_DESCRIPTION from incident_people ip, incident_person_roles ipr, incidents inc, agency_codes ac, master_names mn, master_people mp, --18230 people p, misc_ids misc, misc_ids misc2, --person_addresses padd, --addresses addr, phones pho, phones pho2, smts smt, incident_supplements isup --where ip.role_role_type = 'V' --18230 where ipr.role_type = 'V' --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 smt.per_person_id (+) = p.person_id