SELECT UPPER(inst.db_schema) ||'_'|| mn.mn_id ID, UPPER(inst.db_schema) county, inst.default_state as state, inst.county_name as county_name, mn.mn_id AS mnid, p.master_person_id AS personid, mn.fname AS fname, mn.lname AS lname, mn.slname AS slname, mn.sfname AS sfname, mn.mname AS mname, (SUBSTR(TO_CHAR(LPAD(MN.SSN,9,'0')), 1, 3) || '-' || SUBSTR(TO_CHAR(LPAD(MN.SSN,9,'0')), 4, 2) || '-' || SUBSTR(TO_CHAR(LPAD(MN.SSN,9,'0')), 6) ) AS SSN, TO_CHAR(LPAD(MN.SSN,9,'0')) as ssn_plain, TO_CHAR (mn.dob, 'mm/dd/yyyy') AS dob, TO_CHAR (mn.dob, 'yyyy-mm-dd') AS dob_sort, (SELECT ec.description FROM ejs_codes ec WHERE ec.code_type = mn.title_code_type AND ec.code = mn.title_code) AS title, mn.title_code as title_code, FLOOR (MONTHS_BETWEEN (SYSDATE, mn.dob) / 12) AS age, mp.indx_sec_level_code AS seccode, mp.indx_sec_level_id AS security, mp.indx_type_code AS INDEXTYPE, (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, (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, EJS_MISC_IDS_PK.GET_PLAIN_MISC_IDS_WITH_TYPE(p.person_id,0) as misc_type, EJS_MISC_IDS_PK.GET_MISC_IDS(p.person_id,0) as misc_number, EJS_MISC_IDS_PK.GET_MISC_IDS(p.person_id,1) as misc_number_plain, EJS_MISC_IDS_PK.GET_DRIVER_LICENSE_IDS(p.person_id,0) as drivers_license, EJS_MISC_IDS_PK.GET_DRIVER_LICENSE_IDS(p.person_id,1) as drivers_license_plain, EJS_MISC_IDS_PK.GET_DRIVER_LICENSE_STATES(p.person_id) as drivers_license_state, mp.creator_id AS createdby, mp.creator_date AS creation_date, CONV_SOLR_CREATOR_DATE(mp.creator_date, (SELECT ec.code FROM ejs_codes ec WHERE ec.code_type = 'DATABASE_TIMEZONE' AND ROWNUM = 1)), mp.updator_date AS modification_date, mp.updator_id AS modifiedby, mn.name_type_code AS nametype, (SELECT ec.description FROM ejs_codes ec WHERE ec.code_type = mn.name_type_code_type AND ec.code = mn.name_type_code) AS nametypedesc, get_person_primary_image(P.PERSON_ID, NULL) AS IMA_IMAGE_ID, CASE WHEN EXISTS (SELECT 1 FROM E_WARRANTS W WHERE W.MN_PER_PERSON_ID = P.PERSON_ID AND W.WSC_CODE IN ('A','H')) THEN 'Y' ELSE 'N' END AS has_warrant, EJS_FORMAT_ADDRESS(A.ADDRESS_ID,'Y') AS residence_address, A.STATE_CD_STATE_CODE as residence_address_state, A.ZIP5 as residence_address_Zip, CASE WHEN A.CITY_CD_CITY_CODE IS NOT NULL THEN (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = A.CITY_CD_CITY_CODE_TYPE AND EC.CODE = A.CITY_CD_CITY_CODE) ELSE A.CITY END AS residence_address_city, A.ADDRESS_ID as residence_address_id, to_char(PA.DATE_OF_INFO,'MM/DD/RRRR') as ADDRESS_DATE 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 PERSON_ADDRESSES PA ON PA.PERSON_ADDRESS_ID = EJS_GET_LATEST_RES_ADDRESS_ID(p.person_id) LEFT OUTER JOIN addresses a on a.address_id = pa.address_id