SELECT UPPER (USER) || mn.mn_id ID, UPPER (USER) county, inst.default_state AS state, mn.mn_id AS mnid, INITCAP (p.master_person_id) AS master_person_id, INITCAP (mn.fname) AS fname, INITCAP (mn.lname) AS lname, mn.slname AS slname, mn.sfname AS sfname, INITCAP (mn.mname) AS mname, mn.name_type_code AS name_type, ( SUBSTR (TO_CHAR (mn.ssn), 1, 3) || '-' || SUBSTR (TO_CHAR (mn.ssn), 4, 2) || '-' || SUBSTR (TO_CHAR (mn.ssn), 6)) AS ssn, mn.ssn AS ssn_plain, TO_CHAR (mn.dob, 'mm/dd/yyyy') AS dob, FLOOR (MONTHS_BETWEEN (SYSDATE, mn.dob) / 12) AS age, (CASE WHEN PDESC.height IS NOT NULL THEN SUBSTR (pdesc.height, 0, 1) || ' ft ' || SUBSTR (pdesc.height, 2, 4) || ' inches ' ELSE '' END) AS height, (CASE WHEN PDESC.weight IS NOT NULL THEN PDESC.weight ELSE '' END) AS weight, (CASE WHEN PDESC.BLD_CD_BUILD_CODE IS NOT NULL THEN PDESC.BLD_CD_BUILD_CODE ELSE '' END) AS build_code, (CASE WHEN PDESC.SKN_CD_SKIN_CODE IS NOT NULL THEN PDESC.SKN_CD_SKIN_CODE ELSE '' END) AS skin_tone_code, (CASE WHEN PDESC.HAIR_CD_HAIR_CODE IS NOT NULL THEN PDESC.HAIR_CD_HAIR_CODE ELSE '' END) AS hair_color_code, (CASE WHEN PDESC.EYE_CD_EYE_CODE IS NOT NULL THEN PDESC.EYE_CD_EYE_CODE ELSE '' END) AS eye_color_code, mp.indx_sec_level_code AS seccode, mp.indx_sec_level_id AS security, mp.indx_type_code AS INDEXTYPE, INITCAP ( (SELECT ec.description FROM ejs_codes ec WHERE ec.code_type = mp.race_code_type AND ec.code = mp.race_code)) AS race_desc, mp.race_code AS race, MP.ETHNICITY_CODE, INITCAP ( (SELECT ec.description FROM ejs_codes ec WHERE ec.code_type = MP.ETHNICITY_CODE_TYPE AND ec.code = mp.ethnicity_code)) AS ethnicity_desc, INITCAP ( (SELECT ec.description FROM ejs_codes ec WHERE ec.code_type = mp.sex_code_type AND ec.code = mp.sex_code)) AS sex_desc, mp.sex_code AS sex, mid.state_cd_state_code AS driver_license_state, mid.id_number AS driver_license, a.address_id AS address_id, a.street_number AS street_number, a.dirct_cd_direction_code AS directional, a.street_name AS street_name, a.street_cd_street_type_code AS street_type, a.addr_sc_address_subtype_code AS address_sub_type, a.sub_number AS sub_number, a.city AS city, a.state_cd_state_code AS st, a.zip5 AS zip5, a.zip4 AS zip4, (CASE WHEN RPHN.Area IS NOT NULL THEN RPHN.Area ELSE '' END) AS res_area, (CASE WHEN RPHN.prefix IS NOT NULL THEN RPHN.Prefix ELSE '' END) AS res_prefix, (CASE WHEN RPHN.suffix IS NOT NULL THEN RPHN.suffix ELSE '' END) AS res_suffix, (CASE WHEN wPHN.Area IS NOT NULL THEN wPHN.Area ELSE '' END) AS bus_area, (CASE WHEN wPHN.prefix IS NOT NULL THEN wPHN.Prefix ELSE '' END) AS bus_prefix, (CASE WHEN wPHN.suffix IS NOT NULL THEN wPHN.suffix ELSE '' END) AS bus_suffix, (CASE WHEN cPHN.Area IS NOT NULL THEN cPHN.Area ELSE '' END) AS cell_area, (CASE WHEN cPHN.prefix IS NOT NULL THEN cPHN.Prefix ELSE '' END) AS cell_prefix, (CASE WHEN cPHN.suffix IS NOT NULL THEN cPHN.suffix ELSE '' END) AS cell_suffix, (CASE WHEN EXISTS (SELECT 1 FROM E_WARRANTS W WHERE W.MN_PER_PERSON_ID = P.PERSON_ID AND W.WSC_CODE = 'A') THEN 'Y' ELSE 'N' END) AS has_warrant, (CASE WHEN EXISTS (SELECT MAX (IMA_IMAGE_ID) FROM PERSON_IMAGES WHERE PER_PERSON_ID = p.person_id AND MC1_MUGSHOT_CODE = 'FRO') THEN (SELECT NVL (MAX (IMA_IMAGE_ID), 0) FROM PERSON_IMAGES WHERE PER_PERSON_ID = p.person_id AND MC1_MUGSHOT_CODE = 'FRO') ELSE 0 END) AS image_id, p.person_id, (CASE WHEN jppl.citizenship_country IS NOT NULL THEN jppl.citizenship_country ELSE '' END) AS citizenship FROM install inst, master_names mn INNER JOIN people p ON mn.per_person_id = p.person_id INNER JOIN master_people mp ON p.master_person_id = mp.master_person_id LEFT OUTER JOIN misc_ids mid ON mid.per_person_id = MN.per_person_id AND mid.misc_cd_misc_id_Code = 'DL' LEFT OUTER JOIN person_addresses pa ON pa.person_id = p.person_id AND PA.ADDRESS_ID=ejs_get_latest_res_address(p.person_id) LEFT OUTER JOIN addresses a ON a.address_id = pa.address_id LEFT JOIN PEOPLE_PHYS_DESC_VW PDESC ON PDESC.phys_desc_id = EJS_GET_LATEST_PERSON_PHYS (MN.PER_PERSON_ID, NULL) LEFT OUTER JOIN Phones rPhn ON rPhn.Phone_Id = EJS_GET_LATEST_PHONE_BY_TYPE (p.person_id, 'RES') LEFT OUTER JOIN Phones wPhn ON wPhn.Phone_Id = EJS_GET_LATEST_PHONE_BY_TYPE (p.person_id, 'BUS') LEFT OUTER JOIN Phones cPhn ON cPhn.Phone_Id = EJS_GET_LATEST_PHONE_BY_TYPE (p.person_id, 'CEL') LEFT OUTER JOIN Jail_People jppl ON jppl.person_id = p.person_id