WITH JUV_AGE AS ( SELECT TO_NUMBER(VALUE) as JUVENILE_AGE FROM EJS_SUB_MAINT WHERE KEYWORD = 'JUVENILE_AGE' AND ROWNUM = 1 ), OFFENDER_AGES AS ( SELECT IP.INC_INCIDENT_ID AS INCIDENT_ID, CASE WHEN IP.APPROX_AGE IN ('NB','NN','BB') THEN 0 WHEN IP.APPROX_AGE IN ('00') THEN NULL ELSE TO_NUMBER(IP.APPROX_AGE) END AS AGE, CASE WHEN IP.APPROX_AGE IS NOT NULL AND IP.APPROX_AGE <> '00' AND TO_NUMBER(DECODE(IP.APPROX_AGE, 'NB','0', 'NN','0', 'BB','0', IP.APPROX_AGE)) <= JUV_AGE.JUVENILE_AGE THEN 'Y' ELSE 'N' END AS JUVENILE FROM INCIDENT_PEOPLE IP INNER JOIN INCIDENT_PERSON_ROLES IPR USING(INC_PER_ID) INNER JOIN INCIDENT_SUPPLEMENTS ISUP2 ON IP.INC_INCIDENT_ID = ISUP2.INC_INCIDENT_ID AND IP.SUPP_SEQ = ISUP2.SUPP_SEQ INNER JOIN ROLE_CODES RC ON IPR.ROLE_TYPE = RC.ROLE_TYPE INNER JOIN MASTER_NAMES MN ON IP.MN_MN_ID = MN.MN_ID AND IP.PER_PERSON_ID = MN.PER_PERSON_ID JOIN JUV_AGE ON 1=1 AND ISUP2.ISC_STATUS_CODE = 'A' AND RC.INC_SECTION_CODE = 'OFFENDERS' ) SELECT INST.DB_SCHEMA || '_' || O.REC_ID as OFFENSE_ID, I.INC_REPORT_NUMBER, I.START_DATE AS OCCURRENCE_DATE, AC.AGENCY_DESC AS AGENCY_NAME, CASE WHEN AC2.AGENCY_DESC IS NOT NULL THEN AC2.AGENCY_DESC ELSE AC.AGENCY_DESC END AS ORGANIZATION_NAME, A.LATITUDE, A.LONGITUDE, OC.OFFENSE_CODE, OC.OFFENSE_DESC, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = O.AC_CODE_TYPE AND EC.CODE = O.AC_CODE) AS AC_FLAG, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = OS.SUSPICN_CD_SUSPICION_CODE_TYPE AND EC.CODE = OS.SUSPICN_CD_SUSPICION_CODE) AS SUSPICION, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = W.WC_WEAPON_CODE_TYPE AND EC.CODE = W.WC_WEAPON_CODE) AS WEAPON, NC.NIBRS_CODE, NC.NIBRS_DESC, NC.OFFENSE_GROUP_CODE, NTC.CRIME_AGAINST_CODE, MI.TEXT as MAP_LABEL, OA.JUVENILE AS JUVENILE_OFFENDER, OA.AGE AS OFFENDER_AGE, CASE WHEN EXISTS (SELECT 1 FROM INCIDENT_PEOPLE IP INNER JOIN INCIDENT_PERSON_ROLES IPR USING(INC_PER_ID) INNER JOIN INCIDENT_SUPPLEMENTS ISUP2 ON IP.INC_INCIDENT_ID = ISUP2.INC_INCIDENT_ID AND IP.SUPP_SEQ = ISUP2.SUPP_SEQ WHERE IP.INC_INCIDENT_ID = I.INCIDENT_ID AND ISUP2.ISC_STATUS_CODE = 'A' AND IPR.ROLE_TYPE = 'A') THEN 'Y' ELSE 'N' END AS HAS_ARRESTEE, (SELECT DEFAULT_STATE FROM INSTALL) AS STATE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = O.BIAS_CODE_TYPE AND EC.CODE = O.BIAS_CODE) AS BIAS, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = O.FEC_FORCED_ENTRY_CODE_TYPE AND EC.CODE = O.FEC_FORCED_ENTRY_CODE) AS FORCED_ENTRY, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = O.PLACE_CODE_TYPE AND EC.CODE = O.PLACE_PLACE_CODE) AS PLACE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = O.CARGO_THEFT_CODE_TYPE AND EC.CODE = O.CARGO_THEFT_CODE) AS CARGO_THEFT, O.CHARGE_CODE AS CHARGE_CODE, (SELECT CC.CHA_DESC FROM CHARGE_CODES CC WHERE CC.CODE = O.CHARGE_CODE) AS CHARGE, O.ARREST_CHARGE_CODE AS ARREST_CHARGE_CODE, (SELECT ACC.ARREST_CHA_DESC FROM ARREST_CHARGE_CODES ACC WHERE ACC.ARREST_CHARGE_CODE = O.ARREST_CHARGE_CODE) AS ARREST_CHARGE FROM OFFENSES O INNER JOIN OFFENSE_CODES OC ON O.OFFNS_CD_OFFENSE_CODE = OC.OFFENSE_CODE INNER JOIN NIBRS_OFFENSES NO ON OC.OFFENSE_CODE = NO.OFFENSE_CODE INNER JOIN NIBRS_CODES NC ON NO.NIBRS_CODE = NC.NIBRS_CODE LEFT OUTER JOIN MAP_ICONS MI ON NC.MAP_ICON_ID = MI.ID INNER JOIN INCIDENTS I ON O.INC_INCIDENT_ID = I.INCIDENT_ID INNER JOIN AGENCY_CODES AC ON I.AGNCY_CD_AGENCY_CODE = AC.AGENCY_CODE INNER JOIN INCIDENT_ADDRESSES IA ON I.INCIDENT_ID = IA.INCIDENT_ID INNER JOIN ADDRESSES A ON IA.ADDRESS_ID = A.ADDRESS_ID LEFT OUTER JOIN OFFENDER_SUSPICIONS OS ON O.INC_INCIDENT_ID = OS.OFFNSE_INC_INCIDENT_ID AND O.SUPP_SEQ = OS.OFFNSE_SUPP_SEQ AND O.OFFENSE_NUMBER = OS.OFFNSE_OFFENSE_NUMBER LEFT OUTER JOIN WEAPONS W ON O.INC_INCIDENT_ID = W.OFFNSE_INC_INCIDENT_ID AND O.SUPP_SEQ = W.OFFNSE_SUPP_SEQ AND O.OFFENSE_NUMBER = W.OFFNSE_OFFENSE_NUMBER LEFT OUTER JOIN NIBRS_CRIME_TARGETS NTC ON NC.NIBRS_CODE = NTC.NIBRS_CODE LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS POD ON I.AGNCY_CD_AGENCY_CODE = POD.AGENCY_CODE LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS POD2 ON POD.STRUCTURE_ID = POD2.STRUCTURE_ID AND POD2.IS_ROOT = 'Y' LEFT OUTER JOIN AGENCY_CODES AC2 ON POD2.AGENCY_CODE = AC2.AGENCY_CODE LEFT OUTER JOIN OFFENDER_AGES OA ON O.INC_INCIDENT_ID = OA.INCIDENT_ID JOIN JUV_AGE ON 1=1 JOIN INSTALL INST ON 1=1 WHERE O.SUPP_SEQ = ( SELECT MAX(O2.SUPP_SEQ) FROM OFFENSES O2 INNER JOIN INCIDENT_SUPPLEMENTS ISUPP ON O2.INC_INCIDENT_ID = ISUPP.INC_INCIDENT_ID AND O2.SUPP_SEQ = ISUPP.SUPP_SEQ WHERE O2.INC_INCIDENT_ID = O.INC_INCIDENT_ID AND O2.SUPP_SEQ = O.SUPP_SEQ AND ISUPP.ISC_STATUS_CODE_TYPE = 'INCIDENT_STATUS_CODES' AND ISUPP.ISC_STATUS_CODE = 'A' )