View Definition:
with vic
as (
-- Creates Victim data
SELECT DISTINCT I.INC_REPORT_NUMBER,
i.incident_id,
IPR.INC_PER_ID,
isupp.supp_seq,
MN.PER_PERSON_ID,
O.OFFNS_CD_OFFENSE_CODE,
o.INC_INCIDENT_ID, --ipo.OFFNSE_OFFENSE_NUMBER,
initcap(get_person_name(MP.MASTER_PERSON_ID)) AS vic_name,
MP.SEX_CODE as vic_sex_code,
EJS_GET_EJS_CODE_DESCR(MP.SEX_CODE_TYPE, MP.SEX_CODE) AS Vic_sex,
MN.DOB vic_dob,
I.INJRY_CD_INJURY_CODE as INJURY_CODE,
EJS_GET_EJS_CODE_DESCR(I.INJURY_CODE_TYPE, I.INJRY_CD_INJURY_CODE) AS injury,
VA.RELTN_RELATION_CODE,
EJS_GET_EJS_CODE_DESCR(VA.RELATION_CODE_TYPE, VA.RELTN_RELATION_CODE) as RELATION,
VA.INC_PER_ID as Ofe_inc_per_id,
MP.race_code as vic_race_code,
EJS_GET_EJS_CODE_DESCR(IN_CODE_TYPE => mp.race_code_type, IN_CODE => MP.race_code) as Vic_race
FROM incidents i
JOIN incident_supplements isupp
ON i.incident_id = isupp.inc_incident_id
JOIN incident_people ip
ON isupp.inc_incident_id = ip.inc_incident_id AND ip.supp_seq = isupp.supp_seq
JOIN incident_person_roles ipr
ON ip.inc_per_id = ipr.inc_per_id AND IPR.ROLE_TYPE IN ('VPO', 'M', 'D', 'V')
JOIN PEOPLE P
ON IP.PER_PERSON_ID = P.PERSON_ID
JOIN MASTER_PEOPLE MP
ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID
JOIN People CP
ON MP.MASTER_PERSON_ID = cp.MASTER_PERSON_ID -- bring in the PERSON_ID even if source record is collapsed
JOIN MASTER_NAMES MN
ON CP.PERSON_ID = MN.PER_PERSON_ID AND mn.name_type_code = 'P'
LEFT JOIN INJURIES I
ON I.INC_PER_ID = IP.INC_PER_ID
JOIN offenses O
ON I.INCIDENT_ID = o.INC_INCIDENT_ID and O.DV_RELATED_CODE = 'Y'
join VICTIMs v
on V.INCIDENT_ID = i.incident_id and V.INC_PER_ID = ip.inc_per_id
join VICTIM_ARRESTEES va
on i.incident_id = VA.OFFNSE_INC_INCIDENT_ID and o.OFFENSE_NUMBER = VA.OFFNSE_OFFENSE_NUMBER and v.v_number =
va.v_number
--end Victim Data
),
Offender
as (
-- Offender Data
SELECT DISTINCT
I.INC_REPORT_NUMBER,
IPR.INC_PER_ID,
MN.PER_PERSON_ID,
i.incident_id,
O.OFFNS_CD_OFFENSE_CODE,
INITCAP (get_person_name (MP.MASTER_PERSON_ID)) AS ofe_name,
MP.SEX_CODE as ofe_sex_code,
EJS_GET_EJS_CODE_DESCR (MP.SEX_CODE_TYPE, MP.SEX_CODE) AS ofe_sex,
MN.DOB ofe_dob,
MP.race_code AS ofe_race_code,
EJS_GET_EJS_CODE_DESCR (IN_CODE_TYPE => mp.race_code_type, IN_CODE => MP.race_code)
AS ofe_race
FROM incidents i
Join incident_supplements isupp
on i.incident_id = isupp.inc_incident_id
Join incident_people ip
on isupp.inc_incident_id = ip.inc_incident_id AND ip.supp_seq = isupp.supp_seq
join incident_person_roles ipr
on ip.inc_per_id = ipr.inc_per_id and IPR.ROLE_TYPE in ('S', 'A')
join PEOPLE P
on IP.PER_PERSON_ID = P.PERSON_ID
join MASTER_PEOPLE MP
on P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID
Join People CP
on MP.MASTER_PERSON_ID = cp.MASTER_PERSON_ID -- bring in the PERSON_ID even if source record is collapsed
join MASTER_NAMES MN
on CP.PERSON_ID = MN.PER_PERSON_ID and mn.name_type_code = 'P'
left join offenses O
on I.INCIDENT_ID = o.INC_INCIDENT_ID
--end offender
)
--Body
SELECT distinct -- added to remove Duplicated offences IA-34956
I.REPORT_DATE,
I.REPORT_TIME,
I.INC_REPORT_NUMBER,
i.incident_id,
I.AGNCY_CD_AGENCY_CODE,
AC.AGENCY_DESC,
AC.ORI_NUMBER,
AC.AGENCY_COUNTY,
OC.OFFENSE_DESC,
o.OFFNS_CD_OFFENSE_CODE,
nibrs_code,
NC.LAYER_CODE,
NC.NIBRS_DESC,
wep.WC_WEAPON_CODE,
vic.vic_name,
vic.vic_sex_code,
vic.vic_sex,
vic.vic_dob,
vic.vic_race_code,
vic.vic_race,
vic.INJURY_CODE,
vic.injury,
Vic.RELTN_RELATION_CODE,
vic.RELATION,
ofe.ofe_name,
ofe.ofe_sex_code,
ofe.ofe_sex,
ofe.ofe_dob,
ofe.ofe_race_code,
ofe.ofe_race,
i.drug_related,
i.alcohol_related,
osc.OFFENSE_STATUS_DESC as DISPOSITION ,
EJS_ADDRESSES.full_street_summary(iadd.ADDRESS_ID) AS ADDRESS_LINE1,
iadd.city as city_code,
NVL2(EJS_GET_EJS_CODE_DESCR(IN_CODE_TYPE => ADDR.CITY_CD_CITY_CODE_TYPE, IN_CODE => ADDR.CITY_CD_CITY_CODE)
, ADDR.CITY,EJS_GET_EJS_CODE_DESCR(IN_CODE_TYPE => iadd.city_type_code, IN_CODE => iadd.city) ) as City ,
EJS_GET_EJS_CODE_DESCR(IN_CODE_TYPE => ADDR.STATE_CD_STATE_CODE_TYPE, IN_CODE => ADDR.STATE_CD_STATE_CODE) as State,
EJS_GET_EJS_CODE_DESCR(IN_CODE_TYPE => iadd.county_code_type, IN_CODE => iadd.county_code) as county_cd,
ADDR.ZIP5||
CASE
WHEN ADDR.ZIP4 IS NULL THEN ''
ELSE '-'||ADDR.ZIP4
END as zip,
ADDR.LATITUDE,
ADDR.LONGITUDE
FROM INCIDENTS I
left join AGENCY_CODES AC
ON AC.AGENCY_CODE = I.AGNCY_CD_AGENCY_CODE
Left join INCIDENT_SUPPLEMENTS I_sup
ON I.INCIDENT_ID = I_sup.INC_INCIDENT_ID
left join offenses O
on I_sup.INC_INCIDENT_ID = o.INC_INCIDENT_ID and I_sup.SUPP_SEQ = o.SUPP_SEQ
left join OFFENSE_STATUS_CODES osc on O.OFFNSE_CD_OFFENSE_STATUS_CODE = osc.OFFENSE_STATUS_CODE
left join NIBRS_OFFENSES Noff
on o.OFFNS_CD_OFFENSE_CODE = NOFF.OFFENSE_CODE
LEFT JOIN NIBRS_CODES nc USING (NIBRS_CODE)
left join offense_codes OC
on OC.OFFENSE_CODE = o.OFFNS_CD_OFFENSE_CODE
left join WEAPONS wep
on Wep.OFFNSE_INC_INCIDENT_ID = o.INC_INCIDENT_ID AND Wep.OFFNSE_OFFENSE_NUMBER = o.OFFENSE_NUMBER AND Wep.OFFNSE_SUPP_SEQ
= o.SUPP_SEQ
join vic
on i.INCIDENT_ID = vic.INCIDENT_ID and o.OFFNS_CD_OFFENSE_CODE = vic.OFFNS_CD_OFFENSE_CODE
join Offender ofe
on i.INCIDENT_ID = ofe.INCIDENT_ID and o.OFFNS_CD_OFFENSE_CODE = ofe.OFFNS_CD_OFFENSE_CODE and vic.Ofe_inc_per_id =
ofe.inc_per_id
left join INCIDENT_ADDRESSES iadd on i.INCIDENT_ID = IADD.INCIDENT_ID
left join ADDRESSES addr on iadd.ADDRESS_ID = ADDR.ADDRESS_ID
where o.DV_RELATED_CODE ='Y'
