select distinct 'TR' HEADER, null ACTION, null LABEL, cit.updator_date UPDATE_DATE, to_char(cit.updator_date, 'HH24MI') UPDATE_TIME, ac.ori_number AGENCY, null CASE_NUMBER, --Not in EJS RMS cit.ticket_number CITATION_NUMBER, ac.ori_number REPORTING_AGENCY_ORI, ac.agency_desc REPORTING_AGENCY_NAME, null RESERVED, --to_char(cit.citation_date, 'MM/DD/YYYY') || ' ' || to_char(cit.citation_time, 'HH24MI') CITATION_DATE_TIME, to_char(cit.citation_date, 'MM/DD/YYYY HH24MI') CITATION_DATE_TIME, citadd.cit_addr_comment CITATION_LOCATION, veh.vma_vehicle_make_code VEHICLE_MAKE, veh.vmo_vehicle_model_code VEHICLE_MODEL, veh.vst_vehicle_style_code VEHICLE_BODY_STYLE, veh.vin VIN_NUMBER, veh.year VEHICLE_MODEL_YEAR, vehdesc.top_color VEHICLE_PRIMARY_COLOR, vehdesc.bottom_color VEHICLE_SECOND_COLOR, vehdesc.lic_number LICENSE_PLATE_NUMBER, vehdesc.lic_state_code LICENSE_PLATE_STATE, 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, --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 misc.id_number DRIVERS_LICENSE_NUMBER, misc.state_cd_state_code DRIVERS_LICENSE_STATE, misc.misc_id_type DRIVERS_LICENSE_CLASS, null DOCKET_NUMBER, cit.posted_speed SPEED_LIMIT, cit.actual_speed ALLEGED_SPEED, cc.cha_desc VIOLATION_1_DESCRIPTION, null VIOLATION_2_DESCRIPTION, --Not Used null VIOLATION_3_DESCRIPTION, --Not Used --cit.incident_notes OTHER_VIOLATIONS_OR_NOTES, --18230 cit.stop_comment OTHER_VIOLATIONS_OR_NOTES, --18230 decode(cit.citation_type_code, 'PT', 'Y', 'PM', 'Y', 'N') PARKING_VIOLATION, decode(cit.citation_type_code, 'WN', 'Y', 'N') WARNING_ONLY, null DRUGS_FOUND, null WEAPONS_FOUND, null DRUG_PARAPHERNALIA_FOUND, --Not Used null LICENSE_SUSPENDED, --Not Used null FICTIOUS_TAGS, null EXPIRED_TAGS, null CRIMINAL_TRESPASS, null THEFT_OF_PROPERTY, null ARREST_MADE, null DWI, null DUI, null DISORDERLY_CONDUCT, pd.hair_cd_hair_code HAIR_COLOR, pd.eye_cd_eye_code EYE_CODE, pd.height HEIGHT, pd.weight WEIGHT, cit.citation_id CITATION_SYSTEM_NUMBER, substrb(ejs_addresses.full_street_summary(addr.address_id),1,50) CITATION_ADDRESS, addr.city CITATION_ADDRESS_CITY, addr.state_cd_state_code CITATION_ADDRESS_STATE, substrb(addr.zip5 || '-' || addr.zip4,1,12) CITATION_ADDRESS_ZIP_CODE, addr.latitude CITATION_LOCATION_LATITUDE, addr.longitude CITATION_LOCATION_LONGITUDE, substrb(ejs_addresses.full_street_summary(addr2.address_id),1,50) HOME_ADDRESS, addr2.city HOME_ADDRESS_CITY, addr2.state_cd_state_code HOME_ADDRESS_STATE, substrb(addr2.zip5 || '-' || addr2.zip4,1,12) HOME_ADDRESS_ZIP, null HOME_PHONE, null JUVENILE_INDICATOR, --Use DOB 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 --inc.inc_report_number INCIDENT_NUMBER, --19289 (SELECT inc_report_number from incident_citations inccit, incidents inc, incident_supplements incsupp WHERE incsupp.inc_incident_id = inccit.inc_incident_id AND incsupp.supp_seq = inccit.supp_seq AND inc.incident_id = incsupp.inc_incident_id AND INCSUPP.ISC_STATUS_CODE = 'A' AND INCCIT.CITATION_ID = cit.citation_id AND ROWNUM = 1) INCIDENT_NUMBER, ac.ori_number INCIDENT_AGENCY_ORI, ac.agency_desc INCIDENT_AGENCY_NAME, ac.agency_state INCIDENT_AGENCY_STATE, --inc.incident_id INCIDENT_SYSTEM_NUMBER, --19289 (SELECT incident_id from incident_citations inccit, incidents inc, incident_supplements incsupp WHERE incsupp.inc_incident_id = inccit.inc_incident_id AND incsupp.supp_seq = inccit.supp_seq AND inc.incident_id = incsupp.inc_incident_id AND INCSUPP.ISC_STATUS_CODE = 'A' AND INCCIT.CITATION_ID = cit.citation_id AND ROWNUM = 1) INCIDENT_SYSTEM_NUMBER, --acc.accident_number ACCIDENT_NUMBER , --18230 null ACCIDENT_NUMBER, --18230 ac2.ori_number ACCIDENT_AGENCY_ORI, ac2.agency_desc ACCIDENT_AGENCY_NAME, ac2.agency_state ACCIDENT_AGENCY_STATE, --acc.accident_id ACCIDENT_SYSTEM_NUMBER, --18230 null ACCIDENT_SYSTEM_NUMBER, --18230 cit.citation_type_code CITATION_TYPE, null ORGANIZATION_NAME, null ORGANIZATION_ADDRESS, null ORGANIZATION_CITY, null ORGANIZATION_STATE, null ORGANIZATION_ZIP, null ORGANIZATION_PHONE, citv.CITATION_VIOLATION_ID --19389 from citations cit, --incident_citations inccit, --19289 --incident_supplements incsupp, --19289 --incidents inc, --19289 agency_codes ac, citation_addresses citadd, citation_vehicles citveh, vehicles veh, vehicle_descriptions vehdesc, people p, master_names mn, master_people mp, --18230 misc_ids misc, charge_codes cc, physical_descriptions pd, addresses addr, person_addresses paddr, addresses addr2, --accident_people ap, , --18230 citation_people cp, --18230 --accidents acc, --18230 agency_codes ac2, citation_violations citv --where inccit.citation_id = cit.citation_id --19289 --and incsupp.inc_incident_id = inccit.inc_incident_id --19289 --and incsupp.supp_seq = inccit.supp_seq --19289 --and inc.incident_id = incsupp.inc_incident_id --19289 --and ac.agency_code = inc.agncy_cd_agency_code --19289 where ac.agency_code = cit.agency_code --19289 and citadd.citation_id(+) = cit.citation_id and citveh.citation_id(+) = cit.citation_id and veh.vehicle_id (+) = citveh.vehicle_id and vehdesc.vehicle_id (+) = veh.vehicle_id --and p.person_id (+) = cit.person_id --18230 and p.person_id (+) = cp.person_id --18230 and mn.per_person_id = p.person_id and P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID --and mn.nme_typ_name_type_code = 'P' --18230 and mn.name_type_code = 'P' --18230 and misc.per_person_id(+) = p.person_id and misc.misc_cd_misc_id_code(+) = 'DL' --and cc.code(+) = cit.cha_code --18230 and cc.code(+) = citv.charge_code --18230 and citv.citation_id = cit.citation_id and pd.per_person_id(+) = p.person_id and addr.address_id(+) = citadd.address_id and paddr.person_id(+) = p.person_id and paddr.address_type_code(+) = 'RES' and addr2.address_id(+) = paddr.address_id --and ap.accident_person_id(+) = cit.accident_person_id --18230 and cit.citation_id = cp.citation_id(+) --18230 --and acc.accident_id(+) = ap.accident_id --18230 --and ac2.agency_code(+) = acc.agency_code --18230 and ac2.agency_code(+) = cit.agency_code --18230 --and incsupp.isc_status_code='A ' --19289