| View slot9.WORLD.TSTA.BI_INCIDENTS_VW |
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Analyzed at Mon Sep 20 21:05 MDT 2021 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
View Definition:
SELECT
INC.INCIDENT_ID AS INCIDENT_ID,
INCS.SUPP_SEQ AS SUPPLEMENT_ID,
INC.INC_REPORT_NUMBER AS INC_REPORT_NUMBER,
INCS.RESPONSIBLE_USER_ID AS RESPONSIBLE_USER_ID,
IJIS_RESPONSIBLE.LNAME || ', ' || IJIS_RESPONSIBLE.FNAME AS RESPONSIBLE_USER_NAME,
INCS.APPROVAL_USER_ID AS APPROVAL_USER_ID,
IJIS_APPROVAL.LNAME || ', ' || IJIS_APPROVAL.FNAME AS APPROVAL_USER_NAME,
INCS.SUPP_AGENCY_CODE AS SUPP_AGENCY_CODE,
AC.AGENCY_DESC AS SUPP_AGENCY_DESC,
INC.CREATOR_ID AS CREATOR_ID,
IJIS_CREATOR.LNAME || ', ' || IJIS_CREATOR.FNAME AS CREATOR_USER_NAME,
INC.LOADED_BY,
INC.CREATOR_DATE AS CREATOR_DATE,
( SELECT MIN(VISIT.CREATED_DATE) FROM INC_PAGE_VISITS VISIT
WHERE VISIT.INCIDENT_ID = INCS.INC_INCIDENT_ID AND VISIT.SUPPLEMENT_ID = INCS.SUPP_SEQ
) AS FIRST_VISIT_DATE,
( SELECT MIN(SUB.CREATOR_DATE) FROM INCIDENT_DELETE SUB
WHERE SUB.INCIDENT_ID = INCS.INC_INCIDENT_ID AND SUB.SUPP_SEQ = INCS.SUPP_SEQ
AND SUB.NEW_STATUS_CODE = 'P'
) AS SUBMIT_DATE,
( SELECT COUNT(*) FROM INCIDENT_DELETE SUB
WHERE SUB.INCIDENT_ID = INCS.INC_INCIDENT_ID AND SUB.SUPP_SEQ = INCS.SUPP_SEQ
AND SUB.NEW_STATUS_CODE = 'P'
) AS SUBMIT_COUNT,
( SELECT MIN(APP.CREATOR_DATE) FROM INCIDENT_DELETE APP
WHERE APP.INCIDENT_ID = INCS.INC_INCIDENT_ID AND APP.SUPP_SEQ = INCS.SUPP_SEQ
AND APP.NEW_STATUS_CODE = 'A'
) AS APPROVED_DATE,
( SELECT COUNT(*) FROM INCIDENT_DELETE APP
WHERE APP.INCIDENT_ID = INCS.INC_INCIDENT_ID AND APP.SUPP_SEQ = INCS.SUPP_SEQ
AND APP.NEW_STATUS_CODE = 'A'
) AS APPROVED_COUNT,
( SELECT MIN(APP.CREATOR_DATE) FROM INCIDENT_DELETE APP
WHERE APP.INCIDENT_ID = INCS.INC_INCIDENT_ID AND APP.SUPP_SEQ = INCS.SUPP_SEQ
AND APP.NEW_STATUS_CODE = 'D'
) AS DISAPPROVED_DATE,
( SELECT COUNT(*) FROM INCIDENT_DELETE APP
WHERE APP.INCIDENT_ID = INCS.INC_INCIDENT_ID AND APP.SUPP_SEQ = INCS.SUPP_SEQ
AND APP.NEW_STATUS_CODE = 'D'
) AS DISAPPROVED_COUNT,
( SELECT MIN(ADDR.CREATOR_DATE) FROM INCIDENT_ADDRESSES ADDR
WHERE ADDR.INCIDENT_ID = INCS.INC_INCIDENT_ID
) AS ADDRESS_DATE,
( SELECT COUNT(*) FROM INCIDENT_ADDRESSES ADDR
WHERE ADDR.INCIDENT_ID = INCS.INC_INCIDENT_ID
) AS ADDRESS_COUNT,
( SELECT MIN(OFF.CREATOR_DATE) FROM OFFENSES OFF
WHERE OFF.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND OFF.SUPP_SEQ = INCS.SUPP_SEQ
) AS OFFENSE_DATE,
( SELECT COUNT(*) FROM OFFENSES OFF
WHERE OFF.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND OFF.SUPP_SEQ = INCS.SUPP_SEQ
) AS OFFENSE_COUNT,
( SELECT MIN(PEOP.CREATOR_DATE) FROM INCIDENT_PEOPLE PEOP
INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPR.INC_PER_ID = PEOP.INC_PER_ID AND IPR.ROLE_TYPE IN (SELECT ROLE_TYPE FROM ROLE_CODES WHERE INC_SECTION_CODE = 'OFFENDERS')
WHERE PEOP.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND PEOP.SUPP_SEQ = INCS.SUPP_SEQ
) AS OFFENDER_DATE,
( SELECT COUNT(DISTINCT(PEOP.INC_PER_ID)) FROM INCIDENT_PEOPLE PEOP
INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPR.INC_PER_ID = PEOP.INC_PER_ID AND IPR.ROLE_TYPE IN (SELECT ROLE_TYPE FROM ROLE_CODES WHERE INC_SECTION_CODE = 'OFFENDERS')
WHERE PEOP.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND PEOP.SUPP_SEQ = INCS.SUPP_SEQ
) AS OFFENDER_COUNT,
( SELECT COUNT(DISTINCT(PEOP.INC_PER_ID)) FROM INCIDENT_PEOPLE PEOP
INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPR.INC_PER_ID = PEOP.INC_PER_ID AND IPR.ROLE_TYPE = 'S'
WHERE PEOP.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND PEOP.SUPP_SEQ = INCS.SUPP_SEQ
) AS SUSPECT_COUNT,
( SELECT COUNT(DISTINCT(PEOP.INC_PER_ID)) FROM INCIDENT_PEOPLE PEOP
INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPR.INC_PER_ID = PEOP.INC_PER_ID AND IPR.ROLE_TYPE = 'A'
WHERE PEOP.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND PEOP.SUPP_SEQ = INCS.SUPP_SEQ
) AS ARRESTEE_COUNT,
( SELECT MIN(PEOP.CREATOR_DATE) FROM INCIDENT_PEOPLE PEOP
INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPR.INC_PER_ID = PEOP.INC_PER_ID AND IPR.ROLE_TYPE IN (SELECT ROLE_TYPE FROM ROLE_CODES WHERE INC_SECTION_CODE = 'VICTIMS')
WHERE PEOP.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND PEOP.SUPP_SEQ = INCS.SUPP_SEQ
) AS VICTIM_DATE,
( SELECT COUNT(DISTINCT(PEOP.INC_PER_ID)) FROM INCIDENT_PEOPLE PEOP
INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPR.INC_PER_ID = PEOP.INC_PER_ID AND IPR.ROLE_TYPE IN (SELECT ROLE_TYPE FROM ROLE_CODES WHERE INC_SECTION_CODE = 'VICTIMS')
WHERE PEOP.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND PEOP.SUPP_SEQ = INCS.SUPP_SEQ
) AS VICTIM_COUNT,
( SELECT MIN(PEOP.CREATOR_DATE) FROM INCIDENT_PEOPLE PEOP
INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPR.INC_PER_ID = PEOP.INC_PER_ID AND IPR.ROLE_TYPE IN (SELECT ROLE_TYPE FROM ROLE_CODES WHERE INC_SECTION_CODE IS NULL)
WHERE PEOP.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND PEOP.SUPP_SEQ = INCS.SUPP_SEQ
) AS OTHER_PERSON_DATE,
( SELECT COUNT(DISTINCT(PEOP.INC_PER_ID)) FROM INCIDENT_PEOPLE PEOP
INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPR.INC_PER_ID = PEOP.INC_PER_ID AND IPR.ROLE_TYPE IN (SELECT ROLE_TYPE FROM ROLE_CODES WHERE INC_SECTION_CODE IS NULL)
WHERE PEOP.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND PEOP.SUPP_SEQ = INCS.SUPP_SEQ
) AS OTHER_PERSON_COUNT,
( SELECT MIN(BUS.CREATOR_DATE) FROM INCIDENT_BUSINESSES BUS
WHERE BUS.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND BUS.SUPP_SEQ = INCS.SUPP_SEQ
) AS BUSINESS_DATE,
( SELECT COUNT(*) FROM INCIDENT_BUSINESSES BUS
WHERE BUS.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND BUS.SUPP_SEQ = INCS.SUPP_SEQ
) AS BUSINESS_COUNT,
( SELECT MIN(PROP.CREATOR_DATE) FROM INCIDENT_PROPERTIES PROP
WHERE PROP.INCSUP_INCIDENT_ID = INCS.INC_INCIDENT_ID AND PROP.INCSUP_SUPP_SEQ = INCS.SUPP_SEQ
) AS PROPERY_DATE,
( SELECT COUNT(*) FROM INCIDENT_PROPERTIES PROP
WHERE PROP.INCSUP_INCIDENT_ID = INCS.INC_INCIDENT_ID AND PROP.INCSUP_SUPP_SEQ = INCS.SUPP_SEQ
) AS PROPERY_COUNT,
( SELECT MIN(VEH.CREATOR_DATE) FROM INCIDENT_VEHICLES VEH
WHERE VEH.INCIDENT_ID = INCS.INC_INCIDENT_ID AND VEH.SUPP_SEQ = INCS.SUPP_SEQ
) AS VEHICLE_DATE,
( SELECT COUNT(*) FROM INCIDENT_VEHICLES VEH
WHERE VEH.INCIDENT_ID = INCS.INC_INCIDENT_ID AND VEH.SUPP_SEQ = INCS.SUPP_SEQ
) AS VEHICLE_COUNT,
( SELECT NARR.CREATOR_DATE FROM INC_SUPP_NARRATIVES NARR
WHERE NARR.INCIDENT_ID = INCS.INC_INCIDENT_ID AND NARR.SUPP_SEQ = INCS.SUPP_SEQ AND NARR.NARRATIVE_NUMBER = 1
) AS NARRATIVE_CREATE_DATE,
( SELECT NARR.UPDATOR_DATE FROM INC_SUPP_NARRATIVES NARR
WHERE NARR.INCIDENT_ID = INCS.INC_INCIDENT_ID AND NARR.SUPP_SEQ = INCS.SUPP_SEQ AND NARR.NARRATIVE_NUMBER = 1
) AS NARRATIVE_UPDATE_DATE,
( SELECT NVL (DBMS_LOB.GETLENGTH(NOTE.NARRATIVE), 0) FROM EJS_NOTES NOTE
INNER JOIN INC_SUPP_NARRATIVES NARR ON NARR.NOTE_ID = NOTE.NOTE_ID
WHERE NARR.INCIDENT_ID = INCS.INC_INCIDENT_ID AND NARR.SUPP_SEQ = INCS.SUPP_SEQ AND NARR.NARRATIVE_NUMBER = 1
) AS NARRATIVE_SIZE,
( SELECT COUNT(*) FROM INC_SUPP_NARRATIVES NARR
WHERE NARR.INCIDENT_ID = INCS.INC_INCIDENT_ID AND NARR.SUPP_SEQ = INCS.SUPP_SEQ
) AS NARRATIVE_COUNT,
( SELECT MIN(ATT.CREATOR_DATE) FROM INCIDENT_ATTACHMENTS ATT
WHERE ATT.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND ATT.SUPP_SEQ = INCS.SUPP_SEQ
) AS ATTACHMENT_DATE,
( SELECT COUNT(*) FROM INCIDENT_ATTACHMENTS ATT
WHERE ATT.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND ATT.SUPP_SEQ = INCS.SUPP_SEQ
) AS ATTACHMENT_COUNT,
( SELECT MIN(IMG.CREATOR_DATE) FROM INCIDENT_IMAGES IMG
WHERE IMG.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND IMG.SUPP_SEQ = INCS.SUPP_SEQ
) AS IMAGE_DATE,
( SELECT COUNT(*) FROM INCIDENT_IMAGES IMG
WHERE IMG.INC_INCIDENT_ID = INCS.INC_INCIDENT_ID AND IMG.SUPP_SEQ = INCS.SUPP_SEQ
) AS IMAGE_COUNT,
( SELECT MIN(SUB.CREATOR_DATE) FROM INCIDENT_DELETE SUB
WHERE SUB.INCIDENT_ID = INCS.INC_INCIDENT_ID AND SUB.SUPP_SEQ = INCS.SUPP_SEQ
AND SUB.NEW_STATUS_CODE = 'P' ) -
( SELECT MIN(VISIT.CREATED_DATE) FROM INC_PAGE_VISITS VISIT
WHERE VISIT.INCIDENT_ID = INCS.INC_INCIDENT_ID AND VISIT.SUPPLEMENT_ID = INCS.SUPP_SEQ )
AS TIME_TO_SUBMIT,
( SELECT MIN(APP.CREATOR_DATE) FROM INCIDENT_DELETE APP
WHERE APP.INCIDENT_ID = INCS.INC_INCIDENT_ID AND APP.SUPP_SEQ = INCS.SUPP_SEQ
AND APP.NEW_STATUS_CODE = 'A' ) -
( SELECT MIN(SUB.CREATOR_DATE) FROM INCIDENT_DELETE SUB
WHERE SUB.INCIDENT_ID = INCS.INC_INCIDENT_ID AND SUB.SUPP_SEQ = INCS.SUPP_SEQ
AND SUB.NEW_STATUS_CODE = 'P' )
AS TIME_TO_APPROVE,
NVL(ROOT.AGENCY_CODE, AC.AGENCY_CODE) AS ROOT_AGENCY_CODE,
NVL(ROOT.AGENCY_DESC, AC.AGENCY_DESC) AS ROOT_AGENCY_DESC
FROM INCIDENT_SUPPLEMENTS INCS
INNER JOIN INCIDENTS INC ON INC.INCIDENT_ID = INCS.INC_INCIDENT_ID
LEFT OUTER JOIN IJIS_USER_PROFILES IJIS_RESPONSIBLE ON IJIS_RESPONSIBLE.LOGIN_ID = INCS.RESPONSIBLE_USER_ID
LEFT OUTER JOIN IJIS_USER_PROFILES IJIS_APPROVAL ON IJIS_APPROVAL.LOGIN_ID = INCS.APPROVAL_USER_ID
LEFT OUTER JOIN IJIS_USER_PROFILES IJIS_CREATOR ON IJIS_CREATOR.LOGIN_ID = INC.CREATOR_ID
LEFT OUTER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = INCS.SUPP_AGENCY_CODE
LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS POD ON POD.AGENCY_CODE = AC.AGENCY_CODE
LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS POD2 ON POD2.STRUCTURE_ID = POD.STRUCTURE_ID AND POD2.IS_ROOT='Y'
LEFT OUTER JOIN AGENCY_CODES ROOT ON ROOT.AGENCY_CODE = POD2.AGENCY_CODE
WHERE
INCS.SUPP_SEQ = 0
AND INCS.ISC_STATUS_CODE = 'A'
Possibly Referenced Tables/Views:
AGENCY_CODES EJS_NOTES IJIS_USER_PROFILES INC_PAGE_VISITS INC_SUPP_NARRATIVES INCIDENT_ADDRESSES INCIDENT_ATTACHMENTS INCIDENT_BUSINESSES INCIDENT_DELETE INCIDENT_IMAGES INCIDENT_PEOPLE INCIDENT_PERSON_ROLES INCIDENT_PROPERTIES INCIDENT_SUPPLEMENTS INCIDENT_VEHICLES INCIDENTS OFFENSES PSEUDO_ORG_DESCRIPTORS ROLE_CODES VICTIMS
|