View Definition:
WITH Root
AS (SELECT
i.incident_id,
i.inc_report_number,
(SELECT ac.ori_number FROM agency_codes ac WHERE ac.agency_code = i.agncy_cd_agency_code ) ori_number,
isupp.supp_agency_code ,
(SELECT ac.agency_desc FROM agency_codes ac WHERE ac.agency_code = i.agncy_cd_agency_code ) supp_agency_desc,
isupp.approving_agency_code,
isupp.agency_only ,
isupp.security_level ,
i.report_date ,
i.start_date ,
i.end_date ,
isupp.approval_date ,
isupp.isc_status_code ,
(SELECT ecisc.description FROM ejs_codes ecisc WHERE ecisc.code_type = isupp.isc_status_code_type AND ecisc.code = isupp.isc_status_code) isc_status_desc,
nof.nibrs_code ,
nc.nibrs_desc ,
nc.offense_group_code nibr_group_code,
o.offns_cd_offense_code offense_code,
(SELECT oc.offense_desc FROM offense_codes oc WHERE oc.offense_code = o.offns_cd_offense_code) offense_desc,
o.offnse_cd_offense_status_code offense_status_code,
(SELECT osc.offense_status_desc FROM offense_status_codes osc WHERE o.offnse_cd_offense_status_code = osc.offense_status_code) offense_status_desc,
ecoffcategory.sort_order offense_category,
CASE
WHEN ecoffcategory.sort_order = 1 THEN 'Violent'
WHEN ecoffcategory.sort_order = 2 THEN 'Property'
END AS offense_cat_desc,
ecoffhierarchy.sort_order offense_hierarchy,
CASE
WHEN ecoffhierarchy.sort_order = 1 AND nof.nibrs_code = '09A' THEN 'Criminal Homicide'
WHEN ecoffhierarchy.sort_order = 2 AND nof.nibrs_code = '11A' THEN 'Rape'
WHEN ecoffhierarchy.sort_order = 3 AND nof.nibrs_code = '120' THEN 'Robbery'
WHEN ecoffhierarchy.sort_order = 4 AND nof.nibrs_code = '13A' THEN 'Aggravated Assault'
WHEN ecoffhierarchy.sort_order = 5 AND nof.nibrs_code = '220' AND o.place_place_code ='20' THEN 'Burglary Residence'
WHEN ecoffhierarchy.sort_order = 5 AND nof.nibrs_code = '220' AND o.place_place_code <>'20' THEN 'Burglary Non Residence'
WHEN ecoffhierarchy.sort_order = 6 AND nof.nibrs_code LIKE '23%' THEN 'Larceny'
WHEN ecoffhierarchy.sort_order = 7 AND nof.nibrs_code = '240' THEN 'Vehicle Theft'
WHEN ecoffhierarchy.sort_order = 8 AND nof.nibrs_code = '200' THEN 'Arson'
ELSE 'N/A'
END AS offense_hier_desc ,
o.ucr_number,
o.offense_number,
o.ac_code,
(SELECT ecattempt.description FROM EJS_CODES ecattempt WHERE ecattempt.code_type = o.ac_code_type AND ecattempt.code = o.ac_code) ac_desc,
o.place_place_code place_code,
(SELECT ecpc.description FROM EJS_CODES ecpc WHERE ecpc.code_type = o.place_code_type AND ecpc.code = o.place_place_code) place_desc,
o.fec_forced_entry_code forced_entry_code
FROM incidents i
JOIN offenses o ON o.inc_incident_id = i.incident_id
JOIN nibrs_offenses nof ON nof.offense_code = o.offns_cd_offense_code
JOIN nibrs_codes nc ON nc.nibrs_code = nof.nibrs_code
JOIN incident_supplements isupp ON isupp.inc_incident_id = i.incident_id
AND isupp.supp_seq = o.supp_seq
AND isupp.isc_status_code = 'A'
LEFT OUTER JOIN ejs_codes ecoffhierarchy
ON ecoffhierarchy.code_type = 'OFFENSE_HIERARCHY'
AND nof.nibrs_code = ecoffhierarchy.code
AND ecoffhierarchy.active <> 'N'
LEFT OUTER JOIN ejs_codes ecoffcategory
ON ecoffcategory.code_type = 'OFFENSE_CATEGORY'
AND nof.nibrs_code = ecoffcategory.code
AND ecoffcategory.active <> 'N'
)
SELECT DISTINCT
Root.incident_id,
Root.inc_report_number,
Root.ori_number,
Root.supp_agency_code ,
Root.supp_agency_desc ,
Root.approving_agency_code,
Root.agency_only,
Root.security_level,
TRUNC (Root.report_date) AS REPORT_DATE,
TRUNC (Root.start_date) AS START_DATE,
TRUNC (Root.end_date) AS END_DATE,
TRUNC (Root.approval_date) AS APPROVAL_DATE,
Root.isc_status_code AS ISC_STATUS_CODE,
Root.isc_status_desc AS ISC_STATUS_DESC,
Root.nibr_group_code AS NIBR_GROUP_CODE,
Root.nibrs_code AS NIBRS_CODE,
Root.nibrs_desc AS NIBRS_DESC,
Root.offense_category AS OFFENSE_CATEGORY,
Root.offense_cat_desc AS OFFENSE_CAT_DESC,
Root.offense_hierarchy AS OFFENSE_HIERARCHY,
Root.offense_hier_desc AS OFFENSE_HIER_DESC,
Root.offense_code AS OFFENSE_CODE,
Root.offense_desc AS OFFENSE_DESC,
Root.offense_status_code AS OFFENSE_STATUS_CODE,
Root.offense_status_desc AS OFFENSE_STATUS_DESC,
Root.ucr_number AS UCR_NUMBER,
Root.offense_number AS OFFENSE_NUMBER,
Root.ac_code AS ATTEMPT_COMPLETE_CODE,
Root.ac_desc AS AC_DESC,
Root.forced_entry_code AS FORCED_ENTRY_CODE,
Root.place_code AS PLACE_CODE,
Root.place_desc AS PLACE_DESC
FROM Root
