SELECT DISTINCT 'WA', NULL ACTION, NULL LABEL, ewar.updator_date UPDATE_DATE, TO_CHAR (ewar.UPDATOR_DATE, 'HH24MI') UPDATE_TIME, ac.ori_number AGENCY, ewar.warrant_id WARRANT_NUMBER, ewar.warrant_id WARRANT_UNIQUE_ID, --ewar.docket_number AFFIDAVIT_NUMBER, --18230 wr.reference_id AFFIDAVIT_NUMBER, --18230 ewar.warrant_id WARRANT_REPORT_NUMBER, --ewar.warcd_code WARRANT_TYPE, --18230 (SELECT description FROM ejs_codes ec, warrant_references wr2 WHERE EC.CODE_TYPE = WR2.REFERENCE_TYPE_CODE AND EC.CODE = WR2.REFERENCE_TYPE AND ec.code IN ('COUNTY_WARRANT', 'FTA', 'JP_WARRANT', 'DISTRICT_WARRANT', 'MUNICIPAL_WARRANT') AND WR2.WARRANT_ID = EWAR.WARRANT_ID AND ROWNUM = 1) WARRANT_TYPE, NULL WARRANT_LEVEL, --ewar.lea_remark WARRANT_CONDITIONS, --18230 Not in EJS RMS NULL WARRANT_CONDITIONS, --18230 Not in EJS RMS ewar.wsc_code WARRANT_STATUS, --ewar.status_date WARRANT_STATUS_DATE_TIME, --18230 (SELECT MAX (updator_date) FROM warrant_log WHERE ewar_warrant_id = EWAR.WARRANT_ID AND status_code IS NOT NULL) WARRANT_STATUS_DATE_TIME, --18230 --jud.last_name || ', ' || jud.first_name ISSUING_JUDGE_NAME, --18230 EWAR.JUDGE ISSUING_JUDGE_NAME, --18230 NULL ISSUING_COURT_NAME, --Not in EJS RMS ewar.bond_amount BOND_AMOUNT, ewar.bnd_typ_cd_bond_type BOND_TYPE, NULL FINE_AMOUNT, --Not in EJS RMS TO_CHAR (ewar.date_issued, 'MM/DD/YYYY HH24MI') ISSUE_DATE_TIME, NULL /*to_char(warserv.served_date, 'MM/DD/YYYY') || ' ' || to_char(warserv.served_time, 'HH24MI')*/ SERVED_DATE_TIME, NULL EXPIRATION_DATE_TIME, --Not in EJS RMS NULL /*to_char(warrec.recalled_date, 'MM/DD/YYYY') || ' ' || to_char(warrec.recalled_time, 'HH24MI')*/ RECALLED_DATE_TIME, NULL /*ac2.ori_number*/ ENFORCEMENT_AGENCY_ORI, NULL /*ac2.agency_desc*/ ENFORCEMENT_AGENCY_NAME, NULL /*ac2.agency_state*/ ENFORCEMENT_AGENCY_STATE, NULL /*ac3.ori_number*/ INCIDENT_AGENCY_ORI, NULL /*ac3.agency_desc*/ INCIDENT_AGENCY_NAME, NULL /*ac3.agency_state*/ INCIDENT_AGENCY_STATE, NULL /*inc.inc_report_number*/ INCIDENT_NUMBER, NULL /*inc.incident_id*/ INCIDENT_UNIQUE_ID, NULL /*to_char(inc.start_date, 'MM/DD/YYYY') || ' ' || to_char(inc.start_time, 'HH24MI')*/ OFFENSE_DATE_TIME, NULL COURT_CASE_AGENCY_ORI, --Not in EJS RMS NULL COURT_CASE_AGENCY_NAME, --Not in EJS RMS NULL COURT_CASE_AGENCY_STATE, --Not in EJS RMS NULL COURT_CASE_NUMBER, -- Not in EJS RMS NULL ARREST_AGENCY_ORI, NULL /*warr.agency_name*/ ARREST_AGENCY_NAME, NULL ARREST_AGENCY_STATE, NULL /*warr.arrest_id*/ ARREST_NUMBER, NULL /*warr.arrest_id*/ ARREST_UNIQUE_ID, mn.lname LAST_NAME, mn.fname FIRST_NAME, mn.mname MIDDLE_NAME, mn.title_code SUFFIX, mn.dob DATE_OF_BIRTH, NULL JUVENILE_INDICATOR, mn.ssn SOCIAL_SECURITY_NUMBER, --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, NULL US_CITIZEN, NULL COUNTRY_OF_BIRTH, NULL BIRTH_CITY_TEXT, NULL BIRTH_STATE, misc.id_number PASSPORT_VISA_NUMBER, NULL IMMIGRATION_DOCUMENT_NUMBER, misc2.id_number FBI_NUMBER, misc3.id_number STATE_ID_NUMBER, misc4.id_number DRIVER_LICENSE_NUMBER, misc4.state_cd_state_code DRIVER_LICENSE_STATE, misc4.misc_id_type DRIVER_LICENSE_CLASS, SUBSTRB (ejs_addresses.full_street_summary (addr.address_id), 1, 50) HOME_ADDRESS, addr.city HOME_CITY, addr.state_cd_state_code HOME_STATE, SUBSTRB (addr.zip5 || '-' || addr.zip4, 1, 12) HOME_ZIP, 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 --pd.hair_cd_hair_code HAIR_COLOR, --18230 --pd.eye_cd_eye_code EYE_COLOR, --18230 --pd.height HEIGHT, --18230 --pd.weight WEIGHT, --18230 --pd.skn_cd_skin_code SKIN_TONE --18230 (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, --18230 (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, --18230 (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, --18230 (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, --18230 (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 --18230 FROM e_warrants ewar, agency_codes ac, --judges jud, --18230 --warrant_served warserv, --warrant_recalled warrec, --officers off, --agency_codes ac2, --warrant_incidents warinc, --incidents inc, --agency_codes ac3, --warrant_cases warcase, --20238 --warrants warr, -- 20238 master_names mn, people p, master_people mp, --18230 misc_ids misc, misc_ids misc2, misc_ids misc3, misc_ids misc4, person_addresses padd, addresses addr, phones pho, phones pho2, --physical_descriptions pd, --18230 warrant_references wr --18230 --where ac.agency_code = ewar.agncy_cd_agency_code --18230 WHERE ac.agency_code = ewar.agency_code --18230 --and jud.judge_number(+) = ewar.isu_judge_number --18230 --and warserv.ewar_warrant_id (+) = ewar.warrant_id --20238 --and warrec.ewar_warrant_id (+) = ewar.warrant_id --20238 --and off.officer_id (+) = warserv.serv_officer_id --20238 --and ac2.agency_code (+) = off.agncy_cd_agency_code --20238 --and warinc.ewar_warrant_id (+) = ewar.warrant_id --20238 --and inc.incident_id (+) = warinc.incident_number --20238 --and ac3.agency_code (+) = inc.agncy_cd_agency_code --20238 --and warcase.ewar_warrant_id (+) = ewar.warrant_id --20238 --and warr.ewar_warrant_id (+) = ewar.warrant_id --20238 AND mn.per_person_id = ewar.mn_per_person_id AND mn.mn_id = ewar.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(+) = 'X1' AND misc3.per_person_id(+) = p.person_id AND misc3.misc_cd_misc_id_code(+) = 'X2' AND misc4.per_person_id(+) = p.person_id AND misc4.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 wr.warrant_id = ewar.warrant_id --18230 AND wr.reference_type = 'WARRANT_NUMBER' --18230 --and pd.phys_desc_id (+) = ewar.phy_phys_desc_id --18230