View Definition:
SELECT distinct i.incident_id,
ip.inc_per_id,
i.inc_report_number,
isupp.supp_agency_code "AGNCY_CD_AGENCY_CODE",
i.report_date,
i.start_date,
i.end_date,
isupp.isc_status_code "STATUS",
isupp.isc_status_code_type,
isupp.supp_seq,
isupp.responsible_user_id,
isupp.agency_only,
isupp.cid,
isupp.security_level,
i.summary,
'' as rtc_code_type,
p.master_person_id AS person_id,
mp.sex_code sex_sex_code,
mp.sex_code_type,
mp.race_code race_race_code,
mp.race_code_type,
m.mn_id,
m.name_type_code nme_typ_name_type_code,
m.lname,
m.fname,
m.dob,
m.ssn,
m.slname,
m.sfname,
case
when exists (select 1 from inc_per_forces ipr where ipr.inc_per_id = ip.inc_per_id) then 'Y' else NULL end as rtc_code,
IPF.RTC_CODE AS USE_OF_FORCE_CODE,
case
when IP.APPROX_AGE = '00' then NULL
when IP.APPROX_AGE in ('BB','NB','NN') then 0
else TO_NUMBER(IP.APPROX_AGE, '999')
end as age,
(select
listagg(role_type, ',')
within group (order by role_type) as role_type_codes
from incident_person_roles ipr
where ip.inc_per_id = ipr.inc_per_id) as role_type_codes,
isupp.approving_agency_Code,
isupp.approval_date,
CASE
WHEN IP.EXPUNGE='Y' OR MP.EXPUNGE='Y' OR I.EXPUNGE='Y' THEN 'Y'
ELSE 'N'
END,
mp.INDX_SEC_LEVEL_CODE,
i.inc_disp_code,
i.inc_disp_code_type
FROM
INCIDENTS I
INNER JOIN INCIDENT_SUPPLEMENTS ISUPP ON I.INCIDENT_ID = ISUPP.INC_INCIDENT_ID
INNER JOIN INCIDENT_PEOPLE IP ON ISUPP.INC_INCIDENT_ID = IP.INC_INCIDENT_ID AND ISUPP.SUPP_SEQ = IP.SUPP_SEQ
INNER JOIN PEOPLE P ON IP.PER_PERSON_ID = P.PERSON_ID
INNER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID
INNER JOIN PEOPLE PP ON MP.MASTER_PERSON_ID = PP.MASTER_PERSON_ID
INNER JOIN MASTER_NAMES M ON PP.PERSON_ID = M.PER_PERSON_ID
LEFT OUTER JOIN INC_PER_FORCES IPF ON IPF.INC_PER_ID = IP.INC_PER_ID
