select distinct 'WI' 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 WITNESS_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 misc.id_number PASSPORT_VISA_NUMBER, null IMMIGRATION_DOCUMENT_NUMBER, --Not in EJS RMS null COUNTRY_OF_BIRTH, --Not in EJS RMS misc2.id_number DRIVERS_LICENSE_NUMBER, misc2.state_cd_state_code DRIVERS_LICENSE_STATE, misc2.misc_id_type DRIVERS_LICENSE_CLASS, substrb(ejs_addresses.full_street_summary(addr.address_id),1,50) HOME_ADDRESS, addr.city HOME_ADDRESS_CITY, addr.state_cd_state_code HOME_ADDRESS_STATE, substrb(addr.zip5 || '-' || addr.zip4,1,12) HOME_ADDRESS_ZIP_CODE, pho.area || '-' || pho.prefix || '-' || pho.suffix HOME_PHONE, pho2.area || '-' || pho2.prefix || '-' || pho2.suffix 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 smts.description SCARS_MARKS_TATTOOS, inc.incident_id INCIDENT_SYSTEM_NUMBER from incident_people ip, incident_person_roles ipr, 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, smts, incident_supplements isup --where ip.role_role_type = 'W' --18230 where ipr.role_type = 'W' --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 smts.per_person_id (+) = p.person_id