SELECT /*+ PUSH_PRED(pd) PUSH_PRED(pa)*/ i.incident_Id as ID, i.inc_report_number , 'Last 24 hours' AS schedule_for ,INCSUP.APPROVAL_DATE AS approval_date , CASE WHEN ac.ori_number IS NULL THEN ac.agency_code ELSE ac.ori_number END AS ori , CASE WHEN ac.ori_number IS NULL THEN ac.agency_code ELSE ac.ori_number END || ' ' ||ac.agency_desc AS agency , AC.AGENCY_CODE as agency_code ,( SELECT CASE WHEN MAX(supp_report_date) IS NOT NULL THEN TO_CHAR(MAX(supp_report_date),'RRRR-MM-DD')||'T' ||TO_CHAR(MAX(supp_report_date),'HH24:MI:SS')||'Z' ELSE '' END FROM incident_supplements iss2 WHERE iss2.inc_incident_id = ip.inc_incident_id AND iss2.isc_status_code='A' ) AS edit_date , mnp.per_person_id AS person_id , rc.role_desc AS name_type , mnp.lname AS last_name , mnp.fname AS first_name , mnp.mname AS middle_name , mna.lname || CASE WHEN mna.fname IS NOT NULL THEN ',' || mna.fname ELSE '' END AS monicker , CASE WHEN ad.street_number IS NOT NULL THEN ad.street_number || ' ' ELSE '' END || CASE WHEN ad.dirct_cd_direction_code IS NOT NULL THEN ad.dirct_cd_direction_code || ' ' ELSE '' END || CASE WHEN ad.street_name IS NOT NULL THEN ad.street_name || ' ' ELSE '' END || CASE WHEN ad.street_cd_street_type_code IS NOT NULL THEN ad.street_cd_street_type_code || ' ,' ELSE '' END || CASE WHEN ad.direct_suffix IS NOT NULL THEN ad.direct_suffix || ' ,' ELSE '' END || CASE WHEN ad.city is not NULL THEN ad.city || ' ,' ELSE '' END || CASE WHEN ad.state_cd_state_code IS NOT NULL THEN ad.state_cd_state_code || ' ,' ELSE '' END || CASE WHEN ad.zip5 IS NOT NULL THEN ad.zip5 || ' ' ELSE '' END AS person_address , mnp.dob , rcc.description AS race , sc.description AS sex , CASE WHEN hr.description IS NULL THEN ( SELECT LISTAGG(ej.description,',') WITHIN GROUP (ORDER BY bp.person_id) AS fh FROM bolo_people bp JOIN ejs_codes ej ON ej.code = bp.descriptor_value AND ej.code_type = 'HAIR_CODES' WHERE descriptor_code = 'HRCOLR' AND BP.person_id = p.person_id ) ELSE hr.description END AS hair , CASE WHEN hl.description IS NULL THEN ( SELECT LISTAGG(ej.description,',') WITHIN GROUP (ORDER BY bp.person_id) AS fh FROM bolo_people bp JOIN ejs_codes ej ON ej.code = bp.descriptor_value AND ej.code_type = 'HAIR_LENGTH_CODES' WHERE descriptor_code = 'HRLNGT' AND bp.person_id = p.person_id ) ELSE hl.description END AS hair_length , CASE WHEN ec.description IS NULL THEN ( SELECT LISTAGG(ej.description,',') WITHIN GROUP (ORDER BY bp.person_id) AS fh FROM bolo_people bp JOIN ejs_codes ej ON ej.code = bp.descriptor_value AND ej.code_type = 'EYE_CODES' WHERE descriptor_code = 'EYECOL' AND bp.person_id = p.person_id ) ELSE ec.description END AS eyes , huc.description AS hand_use , '' AS speech , te.descr AS teeth , '' AS physical_condition , CASE WHEN bc.description IS NULL THEN ( SELECT LISTAGG(ej.description,',') WITHIN GROUP (ORDER BY bp.person_id) AS fh FROM bolo_people bp JOIN ejs_codes ej ON ej.code = bp.descriptor_value AND ej.code_type = 'BUILD_CODES' WHERE descriptor_code = 'BUILD' AND bp.person_id = p.person_id ) ELSE bc.description END AS build , CASE WHEN skc.description IS NULL THEN ( SELECT LISTAGG(ej.description,',') WITHIN GROUP (ORDER BY bp.person_id) AS fh FROM bolo_people bp JOIN ejs_codes ej ON ej.code = bp.descriptor_value AND ej.code_type = 'SKIN_CODES' WHERE descriptor_code = 'SKIN' AND bp.person_id = p.person_id ) ELSE skc.description END AS complexion , CASE WHEN fch.description IS NULL THEN ( SELECT LISTAGG(ej.description,',') WITHIN GROUP (ORDER BY bp.person_id) AS fh FROM bolo_people bp JOIN ejs_codes ej ON ej.code = bp.descriptor_value AND ej.code_type = 'FACIAL_HAIR_CODES' WHERE descriptor_code = 'FACLHR' AND bp.person_id = p.person_id ) ELSE fch.description END AS facial_hair , mnp.slname || CASE WHEN mnp.sfname is not null THEN ',' || mnp.sfname ELSE '' END AS soundex , smc.description || ' ' || sm.description AS person_notes , CASE WHEN pd.glasses IS NULL THEN (SELECT descriptor_value FROM bolo_people WHERE descriptor_code = 'GLASS' AND person_id = p.person_id) ELSE pd.glasses END AS glass , apc.description AS appearance , srt.descriptor_value AS shirt , pnt.descriptor_value AS pants , sho.descriptor_value AS shoes , jac.descriptor_value AS jacket , CASE WHEN hat.descriptor_value IS NOT NULL THEN 'YES' ELSE 'NO' END AS hat , msk.descriptor_value AS mask , CASE WHEN pd.weight IS NULL THEN (SELECT descriptor_value FROM bolo_people WHERE descriptor_code = '' AND person_id = P.person_id) ELSE pd.weight END AS weight1 , CASE WHEN pd.weight IS NULL THEN (SELECT descriptor_value FROM bolo_people WHERE descriptor_code = '' AND person_id = P.person_id) ELSE pd.weight END AS weight2 , CASE WHEN pd.height IS NULL THEN (SELECT descriptor_value FROM bolo_people WHERE descriptor_code = '' AND person_id = P.person_id) ELSE pd.height END AS height1 , CASE WHEN pd.height IS NULL THEN (SELECT descriptor_value FROM bolo_people WHERE descriptor_code = '' AND person_id = P.person_id) ELSE pd.height END AS height2 , CASE WHEN pd.age IS NULL THEN (SELECT descriptor_value FROM bolo_people WHERE descriptor_code = '' AND person_id = P.person_id) ELSE TO_CHAR(pd.age) END AS age1 , CASE WHEN pd.age IS NULL THEN (SELECT descriptor_value FROM bolo_people WHERE descriptor_code = '' AND person_id = P.person_id) ELSE TO_CHAR(pd.age) END AS age2 , CASE WHEN mi.id_number IS NOT NULL THEN mi.id_number || '-' || mi.state_cd_state_code ELSE '' END AS sid , '' AS facial_recognition FROM master_people mp INNER JOIN people p ON mp.master_person_id = p.master_person_id INNER JOIN incident_people ip ON p.person_id = ip.per_person_id INNER JOIN incidents i ON ip.inc_incident_id = i.incident_id INNER JOIN incident_supplements incsup on INCSUP.INC_INCIDENT_ID = I.INCIDENT_ID and incsup.isc_status_code='A' INNER JOIN incident_person_roles ipr ON ip.inc_per_id = ipr.inc_per_id INNER JOIN role_codes rc ON ipr.role_type = rc.role_type INNER JOIN agency_codes ac ON i.agncy_cd_agency_code = ac.agency_code LEFT JOIN master_names mnp ON p.person_id = mnp.per_person_id AND mnp.name_type_code = 'P' LEFT JOIN master_names mna ON p.person_id = mna.per_person_id AND mna.name_type_code = 'A' LEFT JOIN misc_ids mi ON p.person_id = mi.per_person_id AND mi.misc_cd_misc_id_code = 'X2' LEFT JOIN ( SELECT phyd.* FROM physical_descriptions phyd JOIN ( SELECT per_person_id, phys_desc_id, ROW_NUMBER () OVER (PARTITION BY per_person_id ORDER BY date_of_info DESC) AS rown FROM physical_descriptions ) phd ON phyd.per_person_id = phd.per_person_id AND phd.phys_desc_id = phyd.phys_desc_id AND phd.rown = 1 ) pd ON mnp.per_person_id = pd.per_person_id LEFT JOIN ( SELECT pera.* FROM person_addresses pera JOIN ( SELECT person_id ,person_address_id, ROW_NUMBER () OVER (PARTITION BY person_id ORDER BY date_of_info DESC) AS rown FROM person_addresses ) perad ON pera.person_id = perad.person_id AND perad.person_address_id = pera.person_address_id AND perad.rown = 1 ) pa ON p.person_id = pa.person_id AND pa.address_type_code IN ('RES', 'LOC', 'PRM', 'POB') LEFT JOIN addresses ad ON pa.address_id = ad.address_id LEFT JOIN smts sm ON p.person_id = sm.per_person_id LEFT JOIN ( SELECT sm.* FROM smts sm JOIN ( SELECT per_person_id , MAX(date_of_info) AS date_of_info FROM smts GROUP BY per_person_id ) smt ON sm.per_person_id = smt.per_person_id AND sm.date_of_info = smt.date_of_info ) hu ON p.person_id = hu.per_person_id AND hu.smt_cd_smt_code = 'PRH' LEFT JOIN ( SELECT sm.per_person_id,LISTAGG(smc.description,'/') WITHIN GROUP (ORDER BY sm.smt_id ) AS descr FROM smts sm JOIN ejs_codes smc ON sm.smt_cd_smt_code_type = smc.code_type AND sm.smt_cd_smt_code = smc.code WHERE sm.smt_cd_smt_code IN ('SLVR','GOLD','DENT') GROUP BY sm.per_person_id ) te ON p.person_id = te.per_person_id LEFT JOIN smts ap ON p.person_id = ap.per_person_id AND ap.smt_cd_smt_code NOT IN ('SLVR','GOLD','DENT','PRH','FACE') AND sm.smt_id = ap.smt_id LEFT JOIN ejs_codes huc ON hu.bdy_prt_body_part_code_type = huc.code_type AND hu.bdy_prt_body_part_code = huc.code LEFT JOIN ejs_codes apc ON ap.bdy_prt_body_part_code_type = apc.code_type AND ap.bdy_prt_body_part_code = apc.code LEFT JOIN ejs_codes smc ON sm.smt_cd_smt_code_type = smc.code_type AND sm.smt_cd_smt_code = smc.code LEFT JOIN ejs_codes rcc ON mp.race_code_type = rcc.code_type AND mp.race_code = rcc.code LEFT JOIN ejs_codes sc ON mp.sex_code_type = sc.code_type AND mp.sex_code = sc.code LEFT JOIN ejs_codes hr ON pd.hair_cd_hair_code_type = hr.code_type AND pd.hair_cd_hair_code = hr.code LEFT JOIN ejs_codes hl ON pd.hair_lngth_hair_length_co_type = hl.code_type AND pd.hair_lngth_hair_length_code = hl.code LEFT JOIN ejs_codes ec ON pd.eye_cd_eye_code_type = ec.code_type AND pd.eye_cd_eye_code = ec.code LEFT JOIN ejs_codes bc ON pd.bld_cd_build_code_type = bc.code_type AND pd.bld_cd_build_code = bc.code LEFT JOIN ejs_codes skc ON pd.skn_cd_skin_code_type = skc.code_type AND pd.skn_cd_skin_code = skc.code LEFT JOIN ejs_codes fch ON pd.face_hr_facial_hair_type = fch.code_type AND pd.face_hr_facial_hair = fch.code LEFT JOIN bolo_people srt ON p.person_id = srt.person_id AND srt.descriptor_code = 'CLTH' AND srt.descriptor_value IN ('BLOUSE', 'TNKTOP', 'HALTR', 'THRMLS', 'TSHRT', 'BRA') LEFT JOIN bolo_people pnt ON p.person_id = pnt.person_id AND pnt.descriptor_code = 'CLTH' AND pnt.descriptor_value IN ('JEANS', 'DSLK', 'SHORTS', 'SWMSUT', 'SKIRT', 'DRESS', 'THRMLU') LEFT JOIN bolo_people sho ON p.person_id = sho.person_id AND sho.descriptor_code = 'CLTH' AND sho.descriptor_value IN ('SHOES','BOOTS','SOCKS') LEFT JOIN bolo_people jac ON p.person_id = jac.person_id AND jac.descriptor_code = 'CLTH' AND jac.descriptor_value IN ('SUIT','VEST','COAT') LEFT JOIN bolo_people hat ON p.person_id = hat.person_id AND hat.descriptor_code = 'CLTH' AND hat.descriptor_value IN ('HAT') LEFT JOIN bolo_people msk ON p.person_id = msk.person_id AND msk.descriptor_code = 'CLTH' AND msk.descriptor_value IN ('MASK') WHERE incsup.approval_date = (SELECT MAX(ISUP2.APPROVAL_DATE) FROM INCIDENT_SUPPLEMENTS ISUP2 WHERE ISUP2.INC_INCIDENT_ID = incsup.inc_incident_Id AND ISUP2.ISC_STATUS_CODE = 'A')