|
|
| 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 |
| COURT_APPEARANCE_ID |
number |
0 |
|
|
|
|
|
| COURT_CASE_NUMBER |
varchar2 |
100 |
√ |
|
null |
|
|
| APPEARANCE_DATE |
date |
7 |
√ |
|
null |
|
|
| TYPE_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| TYPE_CODE_TYPE |
varchar2 |
30 |
√ |
|
null |
|
|
| TYPE_DESC |
varchar2 |
4000 |
√ |
|
null |
|
|
| REASON_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| REASON_CODE_TYPE |
varchar2 |
30 |
√ |
|
null |
|
|
| REASON_DESC |
varchar2 |
4000 |
√ |
|
null |
|
|
| STATUS_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| STATUS_CODE_TYPE |
varchar2 |
30 |
√ |
|
null |
|
|
| STATUS_DESC |
varchar2 |
4000 |
√ |
|
null |
|
|
| AGENCY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| AGENCY_DESC |
varchar2 |
40 |
√ |
|
null |
|
|
| COMMENTS |
varchar2 |
255 |
√ |
|
null |
|
|
| INCIDENT_ID |
number |
0 |
√ |
|
null |
|
|
| INC_REPORT_NUM |
varchar2 |
20 |
√ |
|
null |
|
|
| ARREST_ID |
number |
0 |
√ |
|
null |
|
| ARRESTS.ARREST_ID
|
Implied Constraint R |
|
| ARREST_NUM |
varchar2 |
100 |
√ |
|
null |
|
|
| COMPLAINT_ID |
number |
0 |
√ |
|
null |
|
|
| COMPLAINT_NUM |
varchar2 |
100 |
√ |
|
null |
|
|
| PERSON_ID |
number |
0 |
√ |
|
null |
|
|
| PERSON_FNAME |
varchar2 |
80 |
√ |
|
null |
|
|
| PERSON_MNAME |
varchar2 |
80 |
√ |
|
null |
|
|
| PERSON_LNAME |
varchar2 |
100 |
√ |
|
null |
|
|
| PERSON_ROLE_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| INDX_SEC_LEVEL_CODE |
number |
0 |
√ |
|
null |
|
|
| OFFICER_ID |
number |
0 |
√ |
|
null |
|
| OFFICERS.OFFICER_ID
|
Implied Constraint R |
|
| OFFICER_FNAME |
varchar2 |
20 |
√ |
|
null |
|
|
| OFFICER_MNAME |
varchar2 |
20 |
√ |
|
null |
|
|
| OFFICER_LNAME |
varchar2 |
20 |
√ |
|
null |
|
|
| OFFICER_INTERNAL_ID |
varchar2 |
100 |
√ |
|
null |
|
|
| CREATOR_ID |
varchar2 |
100 |
√ |
|
null |
|
|
| CREATOR_DATE |
date |
7 |
√ |
|
null |
|
|
| UPDATOR_ID |
varchar2 |
100 |
√ |
|
null |
|
|
| UPDATOR_DATE |
date |
7 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
SELECT CA.ID AS COURT_APPEARANCE_ID,
CC.CASE_NUMBER AS CASE_NUMBER,
CA.APPEARANCE_DATE AS APPEARANCE_DATE,
APP_TYPE.CODE AS TYPE_CODE,
APP_TYPE.CODE_TYPE AS TYPE_CODE_TYPE,
APP_TYPE.DESCRIPTION AS TYPE_DESC,
APP_REASON.CODE AS REASON_CODE,
APP_REASON.CODE_TYPE AS REASON_CODE_TYPE,
APP_REASON.DESCRIPTION AS REASON_DESC,
APP_STATUS.CODE AS STATUS_CODE,
APP_STATUS.CODE_TYPE AS STATUS_CODE_TYPE,
APP_STATUS.DESCRIPTION AS STATUS_DESC,
AC.AGENCY_CODE AS AGENCY_CODE,
AC.AGENCY_DESC AS AGENCY_DESC,
CA.COMMENTS AS COMMENTS,
INCS.INCIDENT_ID AS INCIDENT_ID,
INCS.INC_REPORT_NUMBER AS INC_REPORT_NUM,
ARR.ARREST_ID AS ARREST_ID,
ARR.ARREST_NUM AS ARREST_NUM,
CMP.ID AS COMPLAINT_ID,
CMP.CRIMINAL_COMPLAINT_NUM AS COMPLAINT_NUM,
NAMES.PER_PERSON_ID AS PERSON_ID,
NAMES.FNAME AS PERSON_FNAME,
NAMES.MNAME AS PERSON_MNAME,
NAMES.LNAME AS PERSON_LNAME,
PR.CODE AS PERSON_ROLE_CODE,
MP.INDX_SEC_LEVEL_CODE AS INDX_SEC_LEVEL_CODE,
OFFS.OFFICER_ID AS OFFICER_ID,
OFFS.FNAME AS OFFICER_FNAME,
OFFS.MNAME AS OFFICER_MNAME,
OFFS.LNAME AS OFFICER_LNAME,
OFFS.INTERNAL_ID AS OFFICER_INTERNAL_ID,
CA.CREATOR_ID AS CREATOR_ID,
CA.CREATOR_DATE AS CREATOR_DATE,
CA.UPDATOR_ID AS UPDATOR_ID,
CA.UPDATOR_DATE AS UPDATOR_DATE
FROM COURT_APPEARANCES CA
LEFT OUTER JOIN COURT_CASE_APPEARANCES CCA ON CA.ID = CCA.COURT_APPEARANCE_ID
LEFT OUTER JOIN COURT_CASES CC ON CCA.COURT_CASE_ID = CC.ID
LEFT OUTER JOIN AGENCY_CODES AC
ON AC.AGENCY_CODE = CA.AGENCY_CODE
LEFT OUTER JOIN EJS_CODES APP_TYPE
ON APP_TYPE.CODE_TYPE = CA.TYPE_CODE_TYPE
AND APP_TYPE.CODE = CA.TYPE_CODE
LEFT OUTER JOIN EJS_CODES APP_REASON
ON APP_REASON.CODE_TYPE = CA.REASON_CODE_TYPE
AND APP_REASON.CODE = CA.REASON_CODE
LEFT OUTER JOIN EJS_CODES APP_STATUS
ON APP_STATUS.CODE_TYPE = CA.DISPOSITION_CODE_TYPE
AND APP_STATUS.CODE = CA.DISPOSITION_CODE
LEFT OUTER JOIN COURT_CASE_INCS CCI ON CCI.COURT_CASE_ID = CC.ID
LEFT OUTER JOIN INCIDENTS INCS
ON INCS.INCIDENT_ID = CCI.INC_INCIDENT_ID
LEFT OUTER JOIN COURT_CASE_ARRESTS CCA
ON CCA.COURT_CASE_ID = CC.ID
LEFT OUTER JOIN ARRESTS ARR ON ARR.ARREST_ID = CCA.ARR_ARREST_ID
LEFT OUTER JOIN COURT_CASE_COMPLAINTS CCC
ON CCC.COURT_CASE_ID = CC.ID
LEFT OUTER JOIN CRIMINAL_COMPLAINTS CMP
ON CMP.ID = CCC.COMPLAINT_ID
LEFT OUTER JOIN COURT_CASE_PEOPLE CCP ON CCP.COURT_CASE_ID = CC.ID
LEFT OUTER JOIN EJS_CODES PR ON PR.CODE_TYPE = CCP.PERSON_ROLE_CODE_TYPE
AND PR.CODE = CCP.PERSON_ROLE_CODE
LEFT OUTER JOIN MASTER_NAMES NAMES
ON NAMES.PER_PERSON_ID = CCP.PER_PERSON_ID
AND NAMES.NAME_TYPE_CODE = 'P'
LEFT OUTER JOIN PEOPLE P ON NAMES.PER_PERSON_ID = P.PERSON_ID
LEFT OUTER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID
LEFT OUTER JOIN COURT_APPEARANCE_OFFS CAO ON CAO.COURT_APPEARANCE_ID = CA.ID
LEFT OUTER JOIN OFFICERS OFFS ON OFFS.OFFICER_ID = CAO.OFFICER_ID
Possibly Referenced Tables/Views:
