|
|
| Legend: |
| Primary key columns |
| Columns with indexes |
| Implied relationships |
| Excluded column relationships |
| < n > number of related tables |
|
|
| Column |
Type |
Size |
Nulls |
Auto |
Default |
Children |
Parents |
| INC_REPORT_NUMBER |
varchar2 |
20 |
√ |
|
null |
|
|
| INCIDENT_ID |
number |
0 |
√ |
|
null |
|
|
| POSTED |
varchar2 |
1 |
|
|
|
|
|
| DATASETID |
number |
0 |
|
|
|
|
|
| STATE_STATUS |
varchar2 |
30 |
√ |
|
null |
|
|
| HEADERID |
number |
0 |
|
|
|
|
|
| REPORTING_OFFICER |
varchar2 |
4000 |
√ |
|
null |
|
|
| OFFENSES |
varchar2 |
4000 |
√ |
|
null |
|
|
| START_DATE |
date |
7 |
√ |
|
null |
|
|
| MICR_STATUS |
varchar2 |
4000 |
√ |
|
null |
|
|
| SUBMISSION_STATUS |
varchar2 |
30 |
√ |
|
null |
|
|
| FILE_ID |
number |
0 |
√ |
|
null |
|
|
| ERROR_FILE_ID |
number |
0 |
√ |
|
null |
|
|
| RESPONSE_FILE_ID |
number |
0 |
√ |
|
null |
|
|
| RESPONSE_TYPE_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| RESPONSE_TYPE_CODE_TYPE |
varchar2 |
30 |
√ |
|
null |
|
|
| RESPONSE_TYPE_DESC |
varchar2 |
4000 |
√ |
|
null |
|
|
| DATE_GENERATED |
date |
7 |
√ |
|
null |
|
|
| DATE_SUBMITTED |
date |
7 |
√ |
|
null |
|
|
| SUBMITTED_BY |
varchar2 |
100 |
√ |
|
null |
|
|
| SUBMITTER_USER_INFO |
varchar2 |
4000 |
√ |
|
null |
|
|
| ERRORS |
varchar2 |
4000 |
√ |
|
null |
|
|
| ERROR_COUNT |
number |
0 |
√ |
|
null |
|
|
| WARNINGS |
varchar2 |
4000 |
√ |
|
null |
|
|
| WARNING_COUNT |
number |
0 |
√ |
|
null |
|
|
| INFORMATION |
varchar2 |
4000 |
√ |
|
null |
|
|
| INFORMATION_COUNT |
number |
0 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
SELECT
I.INC_REPORT_NUMBER,
I.INCIDENT_ID,
DIC.POSTED,
DIC.DATASETID,
DIC.STATE_STATUS,
DIC.HEADERID,
(SELECT STRAGG(GET_OFF_EMP_NAME(OI.OFF1_OFFICER_ID,'0'))
FROM OFFICER_INCIDENTS OI
WHERE OI.INC_INCIDENT_ID = I.INCIDENT_ID
AND OI.ORC_ROLE_CODE = 'R' AND OI.SUPP_SEQ = 0)
AS REPORTING_OFFICER,
JOIN(CURSOR(SELECT
DISTINCT O.OFFNS_CD_OFFENSE_CODE||' '|| OC.OFFENSE_DESC
from OFFENSES O
LEFT OUTER JOIN OFFENSE_CODES OC ON O.OFFNS_CD_OFFENSE_CODE = OC.OFFENSE_CODE
WHERE O.INC_INCIDENT_ID = I.INCIDENT_ID),'||~||')
AS OFFENSES,
I.START_DATE,
JOIN(CURSOR(SELECT
initcap(ECD.CODE)
FROM EJS_CODES ECD
WHERE ECD.CODE_TYPE = 'DS_STATE_STATUS_CODES'
AND ECD.CODE IN (
SELECT ISUOP.DS_ICR_SUBMISSION_STATUS
FROM INCIDENT_SUPPLEMENTS ISUOP
WHERE ISUOP.INC_INCIDENT_ID = I.INCIDENT_ID) ), '||~||')
AS MICR_STATUS,
(SELECT ISP.Ds_Icr_Submission_Status
FROM INCIDENT_SUPPLEMENTS ISP
WHERE ISP.Inc_Incident_Id = DIC.Repnum
AND rownum = 1)
AS SUBMISSION_STATUS,
DIHF.FILE_ID,
DIHF.ERROR_FILE_ID,
DIHF.RESPONSE_FILE_ID,
DIHF.RESPONSE_TYPE_CODE,
DIHF.RESPONSE_TYPE_CODE_TYPE,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = DIHF.RESPONSE_TYPE_CODE_TYPE AND EC.CODE = DIHF.RESPONSE_TYPE_CODE) AS RESPONSE_TYPE_DESC,
DIHF.DATE_GENERATED,
DIHF.DATE_SUBMITTED,
DIHF.SUBMITTED_BY,
GET_USER_NAME(DIHF.SUBMITTED_BY) AS SUBMITTER_USER_INFO,
JOIN(CURSOR(
SELECT ERROR_TEXT FROM DS_ICR_HEADER_MESSAGES DIHE WHERE DIHE.HEADERID = DIC.HEADERID AND MESSAGE_TYPE_CODE = 'ERROR' AND DIHE.EXT_SYS_CODE = 'MICR'
),'||~||') AS ERRORS,
(SELECT COUNT(*) FROM DS_ICR_HEADER_MESSAGES DIHE WHERE DIHE.HEADERID = DIC.HEADERID AND MESSAGE_TYPE_CODE = 'ERROR' AND DIHE.EXT_SYS_CODE = 'MICR') AS ERROR_COUNT,
JOIN(CURSOR(
SELECT ERROR_TEXT FROM DS_ICR_HEADER_MESSAGES DIHE WHERE DIHE.HEADERID = DIC.HEADERID AND MESSAGE_TYPE_CODE = 'WARNING' AND DIHE.EXT_SYS_CODE = 'MICR'
),'||~||') AS WARNINGS,
(SELECT COUNT(*) FROM DS_ICR_HEADER_MESSAGES DIHE WHERE DIHE.HEADERID = DIC.HEADERID AND MESSAGE_TYPE_CODE = 'WARNING' AND DIHE.EXT_SYS_CODE = 'MICR') AS WARNING_COUNT,
JOIN(CURSOR(
SELECT ERROR_TEXT FROM DS_ICR_HEADER_MESSAGES DIHE WHERE DIHE.HEADERID = DIC.HEADERID AND MESSAGE_TYPE_CODE = 'INFORMATION' AND DIHE.EXT_SYS_CODE = 'MICR'
),'||~||') AS INFORMATION,
(SELECT COUNT(*) FROM DS_ICR_HEADER_MESSAGES DIHE WHERE DIHE.HEADERID = DIC.HEADERID AND MESSAGE_TYPE_CODE = 'INFORMATION' AND DIHE.EXT_SYS_CODE = 'MICR') AS INFORMATION_COUNT
FROM DS_ICR_HEADERS DIC
LEFT OUTER JOIN INCIDENTS I ON I.INCIDENT_ID = DIC.REPNUM
LEFT OUTER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = I.AGNCY_CD_AGENCY_CODE
LEFT OUTER JOIN DS_ICR_HEADER_FILES DIHF ON DIC.HEADERID = DIHF.HEADERID
Possibly Referenced Tables/Views:
