| View slot9.WORLD.TSTA.MY_CASE_SEARCH_VW |
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Mon Sep 20 21:05 MDT 2021 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
View Definition:
SELECT
IC.NEXT_UPDATE_DATE,
ICO.ASSIGN_DATE,
IC.INC_CASE_ID,
IC.INC_CASE_NUMBER,
IC.RSC_STATUS_CODE,
CASE WHEN EJCSTAT.DESCRIPTION IS NOT NULL THEN EJCSTAT.DESCRIPTION ELSE 'No Status' END,
-- INCIDENT REPORT NUMBERS
( JOIN_LIMITED(CURSOR(
SELECT INC_REPORT_NUMBER FROM
(SELECT
ICI.INCIDENT_ID, ICI.INC_CASE_ID, INCS.INC_REPORT_NUMBER, ICI.CREATOR_DATE, ICI.SUPP_SEQ
FROM INC_CASE_INCIDENTS ICI
INNER JOIN INCIDENT_SUPPLEMENTS INCSUP
ON INCSUP.INC_INCIDENT_ID = ICI.INCIDENT_ID
AND INCSUP.SUPP_SEQ = ICI.SUPP_SEQ
INNER JOIN INCIDENTS INCS
ON INCS.INCIDENT_ID = INCSUP.INC_INCIDENT_ID
ORDER BY ICI.CREATOR_DATE ASC ) RES
WHERE
RES.INC_CASE_ID = IC.INC_CASE_ID
AND RES.SUPP_SEQ = ( SELECT MIN(ICI.SUPP_SEQ) FROM INC_CASE_INCIDENTS ICI WHERE ICI.INC_CASE_ID = IC.INC_CASE_ID AND ICI.INCIDENT_ID = RES.INCIDENT_ID )
),'||')
),
-- INCIDENT IDS
( JOIN_LIMITED(CURSOR(
SELECT INCIDENT_ID FROM
(SELECT
ICI.INC_CASE_ID, INCS.INCIDENT_ID, ICI.CREATOR_DATE, ICI.SUPP_SEQ
FROM INC_CASE_INCIDENTS ICI
INNER JOIN INCIDENT_SUPPLEMENTS INCSUP
ON INCSUP.INC_INCIDENT_ID = ICI.INCIDENT_ID
AND INCSUP.SUPP_SEQ = ICI.SUPP_SEQ
INNER JOIN INCIDENTS INCS
ON INCS.INCIDENT_ID = INCSUP.INC_INCIDENT_ID
ORDER BY ICI.CREATOR_DATE ASC ) RES
WHERE
RES.INC_CASE_ID = IC.INC_CASE_ID
AND RES.SUPP_SEQ = ( SELECT MIN(ICI.SUPP_SEQ) FROM INC_CASE_INCIDENTS ICI WHERE ICI.INC_CASE_ID = IC.INC_CASE_ID AND ICI.INCIDENT_ID = RES.INCIDENT_ID )
),'||')),
( CASE
WHEN ICO.ASSIGN_DATE IS NULL THEN ' '
ELSE TO_CHAR(ICO.ASSIGN_DATE,'MM/DD/YYYY')
END ),
TO_CHAR(NEXT_UPDATE_DATE,'MM/DD/YYYY'),
-- INCIDENT ADDRESS IDS
( JOIN_LIMITED(CURSOR(
SELECT INCIDENT_ADDRESS FROM
( SELECT
(INC.INC_REPORT_NUMBER || ',' || A.ADDRESS_ID) AS INCIDENT_ADDRESS,
ICINC.INC_CASE_ID, ICINC.CREATOR_DATE
FROM INCIDENT_CASES ICAS
LEFT OUTER JOIN INC_CASE_INCIDENTS ICINC ON ICAS.INC_CASE_ID = ICINC.INC_CASE_ID
LEFT OUTER JOIN INCIDENTS INC ON ICINC.INCIDENT_ID = INC.INCIDENT_ID
LEFT OUTER JOIN INCIDENT_ADDRESSES IADR ON INC.INCIDENT_ID = IADR.INCIDENT_ID
LEFT OUTER JOIN ADDRESSES A ON A.ADDRESS_ID = IADR.ADDRESS_ID
ORDER BY ICINC.CREATOR_DATE ASC )
WHERE INC_CASE_ID = IC.INC_CASE_ID
),'||')
),
-- INCIDENT ADDRESS DESCRIPTIONS
( JOIN_LIMITED(CURSOR(
SELECT INCIDENT_ADDRESS FROM
( SELECT
(a.STREET_NUMBER
|| ' ' || DC.DESCRIPTION
|| ' ' || a.STREET_NAME
|| ' ' || SC.DESCRIPTION
|| ', ' || a.INTRSECT1_STREET_NUMBER
|| ' ' || DC2.DESCRIPTION
|| ' ' || a.INTRSECT1_NAME
|| ' ' || SC2.DESCRIPTION
|| ' ' || DSC.DESCRIPTION
|| ' ' || STC.DESCRIPTION
|| ' #' || a.SUB_NUMBER
|| ' ' || a.CITY
|| ' ' || a.STATE_CD_STATE_CODE
|| ' ' || a.ZIP5
|| '-' || TO_CHAR (a.ZIP4, 'FM0000')
|| ' ' || CC.DESCRIPTION
|| ' ' || CNTC.DESCRIPTION) AS INCIDENT_ADDRESS,
ICINC.INC_CASE_ID, ICINC.CREATOR_DATE
FROM INCIDENT_CASES ICAS
LEFT OUTER JOIN INC_CASE_INCIDENTS ICINC ON ICAS.INC_CASE_ID = ICINC.INC_CASE_ID
LEFT OUTER JOIN INCIDENTS INC ON ICINC.INCIDENT_ID = INC.INCIDENT_ID
LEFT OUTER JOIN INCIDENT_ADDRESSES IADR ON INC.INCIDENT_ID = IADR.INCIDENT_ID
LEFT OUTER JOIN ADDRESSES A ON A.ADDRESS_ID = IADR.ADDRESS_ID
LEFT OUTER JOIN EJS_CODES DC ON A.DIRCT_CD_DIRECTION_CODE = DC.CODE
AND A.DIRCT_CD_DIRECTION_CODE_TYPE = DC.CODE_TYPE
LEFT OUTER JOIN EJS_CODES DSC ON A.DIRECT_SUFFIX = DSC.CODE
AND A.DIRECT_SUFFIX_TYPE = DSC.CODE_TYPE
LEFT OUTER JOIN EJS_CODES SC ON A.STREET_CD_STREET_TYPE_CODE = SC.CODE
AND A.STREET_CD_STREET_TYPE_COD_TYPE = SC.CODE_TYPE
LEFT OUTER JOIN EJS_CODES SC2 ON A.INTRSECT1_STREET_TYPE_CODE = SC2.CODE
AND A.INTRSECT1_STREET_TYPE_COD_TYPE = SC2.CODE_TYPE
LEFT OUTER JOIN EJS_CODES STC ON A.ADDR_SC_ADDRESS_SUBTYPE_CODE = STC.CODE
AND A.ADDR_SC_ADDRESS_SUBTYPE_C_TYPE = STC.CODE_TYPE
LEFT OUTER JOIN EJS_CODES CC ON A.CNTY_CD_COUNTY_CODE = CC.CODE
AND A.CNTY_CD_COUNTY_CODE_TYPE = CC.CODE_TYPE
LEFT OUTER JOIN EJS_CODES CNTC ON A.CNTRY_CD_COUNTRY_TYPE_CODE = CNTC.CODE
AND A.CNTRY_CD_COUNTRY_TYPE_COD_TYPE = CNTC.CODE_TYPE
LEFT OUTER JOIN EJS_CODES DC2 ON A.INTRSECT1_DIR = DC2.CODE
AND A.INTRSECT1_DIR_TYPE = DC2.CODE_TYPE
ORDER BY ICINC.CREATOR_DATE ASC )
WHERE INC_CASE_ID = IC.INC_CASE_ID
),'||')
),
-- VICTIMS
( JOIN_LIMITED(CURSOR(
-- PEOPLE
SELECT INC_REPORT_NUMBER || ',' || INITCAP(FNAME)||' '||INITCAP(LNAME) FROM
(SELECT
ICI.INCIDENT_ID, ICI.INC_CASE_ID, INCS.INC_REPORT_NUMBER, ICI.CREATOR_DATE, ICI.SUPP_SEQ, MN.FNAME, MN.LNAME
FROM INC_CASE_INCIDENTS ICI
INNER JOIN INCIDENT_SUPPLEMENTS INCSUP
ON INCSUP.INC_INCIDENT_ID = ICI.INCIDENT_ID
AND INCSUP.SUPP_SEQ = ICI.SUPP_SEQ
INNER JOIN INCIDENTS INCS
ON INCS.INCIDENT_ID = INCSUP.INC_INCIDENT_ID
INNER JOIN INCIDENTS I ON ICI.INCIDENT_ID = I.INCIDENT_ID
INNER JOIN INCIDENT_PEOPLE IP ON I.INCIDENT_ID = IP.INC_INCIDENT_ID
INNER JOIN PEOPLE P ON P.PERSON_ID = IP.PER_PERSON_ID
INNER JOIN MASTER_PEOPLE MP ON MP.MASTER_PERSON_ID = P.MASTER_PERSON_ID
INNER JOIN PEOPLE PP ON PP.MASTER_PERSON_ID = MP.MASTER_PERSON_ID
INNER JOIN MASTER_NAMES MN ON MN.PER_PERSON_ID = PP.PERSON_ID
AND MN.NAME_TYPE_CODE = 'P'
INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPR.INC_PER_ID = IP.INC_PER_ID
INNER JOIN ROLE_CODES RC ON RC.ROLE_TYPE = IPR.ROLE_TYPE
WHERE RC.INC_SECTION_CODE = 'VICTIMS'
ORDER BY ICI.CREATOR_DATE ASC ) RES
WHERE
RES.INC_CASE_ID = IC.INC_CASE_ID
AND RES.SUPP_SEQ = ( SELECT MIN(ICI.SUPP_SEQ) FROM INC_CASE_INCIDENTS ICI WHERE ICI.INC_CASE_ID = IC.INC_CASE_ID AND ICI.INCIDENT_ID = RES.INCIDENT_ID )
UNION
-- ORGS
SELECT INC_REPORT_NUMBER || ',' || INITCAP(BUSINESS_NAME) FROM
(SELECT
ICI.INCIDENT_ID, ICI.INC_CASE_ID, INCS.INC_REPORT_NUMBER, ICI.CREATOR_DATE, ICI.SUPP_SEQ, B.BUSINESS_NAME
FROM INC_CASE_INCIDENTS ICI
INNER JOIN INCIDENT_SUPPLEMENTS INCSUP
ON INCSUP.INC_INCIDENT_ID = ICI.INCIDENT_ID
AND INCSUP.SUPP_SEQ = ICI.SUPP_SEQ
INNER JOIN INCIDENTS INCS
ON INCS.INCIDENT_ID = INCSUP.INC_INCIDENT_ID
INNER JOIN INCIDENTS I ON ICI.INCIDENT_ID = I.INCIDENT_ID
INNER JOIN INCIDENT_BUSINESSES IB ON I.INCIDENT_ID = IB.INC_INCIDENT_ID
INNER JOIN BUSINESSES B ON B.BUSINESS_NUMBER = IB.BUSNS_BUSINESS_NUMBER
INNER JOIN ROLE_CODES RC ON RC.ROLE_TYPE = IB.IB1_ROLE_TYPE
WHERE RC.INC_SECTION_CODE = 'VICTIMS'
ORDER BY ICI.CREATOR_DATE ASC ) RES
WHERE
RES.INC_CASE_ID = IC.INC_CASE_ID
AND RES.SUPP_SEQ = ( SELECT MIN(ICI.SUPP_SEQ) FROM INC_CASE_INCIDENTS ICI WHERE ICI.INC_CASE_ID = IC.INC_CASE_ID AND ICI.INCIDENT_ID = RES.INCIDENT_ID )
),'||')
),
-- OFFENDERS
( JOIN_LIMITED(CURSOR(
-- KNOWN
SELECT INC_REPORT_NUMBER || ',' || INITCAP(FNAME)||' '||INITCAP(LNAME) FROM
(SELECT
ICI.INCIDENT_ID, ICI.INC_CASE_ID, INCS.INC_REPORT_NUMBER, ICI.CREATOR_DATE, ICI.SUPP_SEQ, MN.FNAME, MN.LNAME
FROM INC_CASE_INCIDENTS ICI
INNER JOIN INCIDENT_SUPPLEMENTS INCSUP
ON INCSUP.INC_INCIDENT_ID = ICI.INCIDENT_ID
AND INCSUP.SUPP_SEQ = ICI.SUPP_SEQ
INNER JOIN INCIDENTS INCS
ON INCS.INCIDENT_ID = INCSUP.INC_INCIDENT_ID
INNER JOIN INCIDENTS I ON ICI.INCIDENT_ID = I.INCIDENT_ID
INNER JOIN INCIDENT_PEOPLE IP ON I.INCIDENT_ID = IP.INC_INCIDENT_ID
INNER JOIN PEOPLE P ON P.PERSON_ID = IP.PER_PERSON_ID
INNER JOIN MASTER_PEOPLE MP ON MP.MASTER_PERSON_ID = P.MASTER_PERSON_ID
AND MP.INDX_TYPE_CODE = 'KNOWN'
INNER JOIN PEOPLE PP ON PP.MASTER_PERSON_ID = MP.MASTER_PERSON_ID
INNER JOIN MASTER_NAMES MN ON MN.PER_PERSON_ID = PP.PERSON_ID
AND MN.NAME_TYPE_CODE = 'P'
INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPR.INC_PER_ID = IP.INC_PER_ID
INNER JOIN ROLE_CODES RC ON RC.ROLE_TYPE = IPR.ROLE_TYPE
WHERE RC.INC_SECTION_CODE = 'OFFENDERS'
ORDER BY ICI.CREATOR_DATE ASC ) RES
WHERE
RES.INC_CASE_ID = IC.INC_CASE_ID
AND RES.SUPP_SEQ = ( SELECT MIN(ICI.SUPP_SEQ) FROM INC_CASE_INCIDENTS ICI WHERE ICI.INC_CASE_ID = IC.INC_CASE_ID AND ICI.INCIDENT_ID = RES.INCIDENT_ID )
UNION
-- UNKNOWN
SELECT INC_REPORT_NUMBER || ',' || 'Unknown Offender' FROM
(SELECT
ICI.INCIDENT_ID, ICI.INC_CASE_ID, INCS.INC_REPORT_NUMBER, ICI.CREATOR_DATE, ICI.SUPP_SEQ
FROM INC_CASE_INCIDENTS ICI
INNER JOIN INCIDENT_SUPPLEMENTS INCSUP
ON INCSUP.INC_INCIDENT_ID = ICI.INCIDENT_ID
AND INCSUP.SUPP_SEQ = ICI.SUPP_SEQ
INNER JOIN INCIDENTS INCS
ON INCS.INCIDENT_ID = INCSUP.INC_INCIDENT_ID
INNER JOIN INCIDENTS I ON ICI.INCIDENT_ID = I.INCIDENT_ID
INNER JOIN INCIDENT_PEOPLE IP ON I.INCIDENT_ID = IP.INC_INCIDENT_ID
INNER JOIN PEOPLE P ON P.PERSON_ID = IP.PER_PERSON_ID
INNER JOIN MASTER_PEOPLE MP ON MP.MASTER_PERSON_ID = P.MASTER_PERSON_ID
AND MP.INDX_TYPE_CODE = 'UNKNOWN'
INNER JOIN PEOPLE PP ON PP.MASTER_PERSON_ID = MP.MASTER_PERSON_ID
INNER JOIN MASTER_NAMES MN ON MN.PER_PERSON_ID = PP.PERSON_ID
AND MN.NAME_TYPE_CODE = 'P'
INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPR.INC_PER_ID = IP.INC_PER_ID
INNER JOIN ROLE_CODES RC ON RC.ROLE_TYPE = IPR.ROLE_TYPE
WHERE RC.INC_SECTION_CODE = 'OFFENDERS'
ORDER BY ICI.CREATOR_DATE ASC ) RES
WHERE
RES.INC_CASE_ID = IC.INC_CASE_ID
AND RES.SUPP_SEQ = ( SELECT MIN(ICI.SUPP_SEQ) FROM INC_CASE_INCIDENTS ICI WHERE ICI.INC_CASE_ID = IC.INC_CASE_ID AND ICI.INCIDENT_ID = RES.INCIDENT_ID )
),'||')
),
-- INCIDENT OFFENSES
( JOIN_LIMITED(CURSOR(
SELECT INCIDENT_OFNS FROM
( SELECT
(INCS.INC_REPORT_NUMBER || ',' || REPLACE (OC.OFFENSE_DESC,',',',') ) AS INCIDENT_OFNS,
ICI.INC_CASE_ID, ICI.CREATOR_DATE, ICI.INCIDENT_ID, ICI.SUPP_SEQ
FROM INC_CASE_INCIDENTS ICI
INNER JOIN INCIDENT_SUPPLEMENTS INCSUP
ON INCSUP.INC_INCIDENT_ID = ICI.INCIDENT_ID
AND INCSUP.SUPP_SEQ = ICI.SUPP_SEQ
INNER JOIN INCIDENTS INCS ON INCS.INCIDENT_ID = INCSUP.INC_INCIDENT_ID
INNER JOIN OFFENSES O ON O.INC_INCIDENT_ID = INCS.INCIDENT_ID
INNER JOIN OFFENSE_CODES OC ON OC.OFFENSE_CODE = O.OFFNS_CD_OFFENSE_CODE
ORDER BY ICI.CREATOR_DATE ASC ) RES
WHERE RES.INC_CASE_ID = IC.INC_CASE_ID
AND RES.SUPP_SEQ = ( SELECT MIN(ICI.SUPP_SEQ) FROM INC_CASE_INCIDENTS ICI WHERE ICI.INC_CASE_ID = IC.INC_CASE_ID AND ICI.INCIDENT_ID = RES.INCIDENT_ID )
),'||')
),
ECR.PARENT_CODE ,
NVL(OFFCRS.MASTER_OFFICER_ID, ICO.OFFICER_ID),
(CASE WHEN IC.CASE_STATUS_CODE IS NOT NULL THEN IC.CASE_STATUS_CODE ELSE '_NO_CASE_STATUS_SET_' END)
FROM
INCIDENT_CASES IC
LEFT OUTER JOIN EJS_CODES EJCSTAT ON EJCSTAT.CODE_TYPE = IC.CASE_STATUS_CODE_TYPE AND EJCSTAT.CODE = IC.CASE_STATUS_CODE
INNER JOIN INCIDENT_CASE_OFFICERS ICO ON IC.INC_CASE_ID = ICO.INC_CASE_ID AND ICO.OFFICER_STATUS = 'Y'
INNER JOIN EJS_CODES_RELATIONS ECR
ON ECR.PARENT_CODE_TYPE = 'REVIEW_STATUS_TYPE_CODES'
AND ECR.CHILD_CODE_TYPE = IC.RSC_STATUS_CODE_TYPE
AND ECR.CHILD_CODE = IC.RSC_STATUS_CODE
LEFT OUTER JOIN OFFICERS OFFCRS ON OFFCRS.OFFICER_ID = ICO.OFFICER_ID
WHERE ICO.END_DATE IS NULL
Possibly Referenced Tables/Views:
ADDRESSES BUSINESSES EJS_CODES EJS_CODES_RELATIONS INC_CASE_INCIDENTS INCIDENT_ADDRESSES INCIDENT_BUSINESSES INCIDENT_CASE_OFFICERS INCIDENT_CASES INCIDENT_PEOPLE INCIDENT_PERSON_ROLES INCIDENT_SUPPLEMENTS INCIDENTS MASTER_NAMES MASTER_PEOPLE OFFENSE_CODES OFFENSES OFFICERS PEOPLE ROLE_CODES VICTIMS
|