|
|
| 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 |
| AGENCY_CODE |
varchar2 |
30 |
|
|
|
|
|
| AGENCY_DESCRIPTION |
varchar2 |
40 |
√ |
|
null |
|
|
| ORI_NUMBER |
varchar2 |
9 |
√ |
|
null |
|
|
| LICENSED_USERS |
number |
0 |
√ |
|
null |
|
|
| SUPPORT_LEVEL |
varchar2 |
4000 |
√ |
|
null |
|
|
| IS_ROOT |
varchar2 |
1 |
√ |
|
null |
|
|
| PARENT_AGENCY |
varchar2 |
40 |
√ |
|
null |
|
|
| ROOT_AGENCY |
varchar2 |
40 |
√ |
|
null |
|
|
| SUBSCRIPTION_LEVEL |
varchar2 |
30 |
√ |
|
null |
|
|
| SUBSCRIPTION_COUNT |
number |
0 |
√ |
|
null |
|
|
| AGENCY_FEATURE |
varchar2 |
4000 |
√ |
|
null |
|
|
| AGENCY_EXTERNAL_SYSTEM |
varchar2 |
80 |
√ |
|
null |
|
|
| SEARCH_INTERFACE |
varchar2 |
500 |
√ |
|
null |
|
|
| AGENCY_WEB_SERVICE |
varchar2 |
80 |
√ |
|
null |
|
|
| RICH_MEDIA_STORAGE_GB |
number |
0 |
√ |
|
null |
|
|
| AGENCY_WORKGROUP |
varchar2 |
4000 |
√ |
|
null |
|
|
| DEFAULT_SECURITY_LEVEL |
number |
38 |
√ |
|
null |
|
|
| LEA_CASE_UPDATE_DAYS |
number |
3 |
√ |
|
null |
|
|
| UCR_REQUIRED |
varchar2 |
1 |
|
|
|
|
|
| UCR_CLERK |
varchar2 |
1 |
|
|
|
|
|
| NIBRS_REPORTING_OPTION |
varchar2 |
4000 |
√ |
|
null |
|
|
| INTERDEX_ID |
varchar2 |
100 |
√ |
|
null |
|
|
| INTERNAL_OFFENSES |
varchar2 |
1 |
|
|
|
|
|
| CASE_GOOD_STANDING_DAYS |
number |
3 |
√ |
|
null |
|
|
| CASE_ACT_GOOD_STANDING_DAYS |
number |
0 |
√ |
|
null |
|
|
| CASE_ACTIVITY_NEXT_UPDATE_DAYS |
number |
0 |
√ |
|
null |
|
|
| DISPATCH_ENABLED |
varchar2 |
1 |
|
|
|
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
SELECT AC.AGENCY_CODE AS AGENCY_CODE,
AC.AGENCY_DESC AS AGENCY_DESCRIPTION,
AC.ORI_NUMBER AS ORI_NUMBER,
AC.LICENSED_USERS AS LICENSED_USERS,
EC4.DESCRIPTION AS SUPPORT_LEVEL,
POD.IS_ROOT AS IS_ROOT,
(select agency_desc
from agency_codes
where agency_code = (select agency_code
from pseudo_org_descriptors
where pod_id = POD.AGENCY_PARENT)) AS PARENT_AGENCY,
(select agency_desc
from agency_codes
where agency_code = (select agency_code
from pseudo_org_descriptors
where structure_id = pod.structure_id
and is_root = 'Y')) AS ROOT_AGENCY,
UTC.SUBSCRIPTION_LEVEL_CODE AS SUBSCRIPTION_LEVEL,
UTC.COUNT AS SUBSCRIPTION_COUNT,
EC1.DESCRIPTION AS AGENCY_FEATURE,
ESC.DESCRIPTION AS AGENCY_EXTERNAL_SYSTEM,
SI.DESCRIPTION AS AGENCY_SEARCH_INTERFACE,
WSA.SERVICE_NAME AS AGENCY_WEB_SERVICE,
AC.RICH_MEDIA_STORAGE_GB AS RICH_MEDIA_STORAGE_GB,
EC2.DESCRIPTION AS AGENCY_WORKGROUP,
AC.DEF_LEA_SEC_LEVEL AS DEFAULT_SECURITY_LEVEL,
AC.LEA_CASE_UPDATE AS LEA_CASE_UPDATE_DAYS,
AC.UCR_REQUIRED AS UCR_REQUIRED,
AC.UCR_CLERK AS UCR_CLERK,
EC3.DESCRIPTION AS NIBRS_REPORTING_OPTION,
AC.INTERDEX_ID AS INTERDEX_ID,
AC.INTERNAL_OFFENSES AS INTERNAL_OFFENSES,
AC.CASE_GOOD_STANDING_DAYS AS CASE_GOOD_STANDING_DAYS,
AC.CASE_ACT_GOOD_STANDING_DAYS AS CASE_ACT_GOOD_STANDING_DAYS,
AC.CASE_ACT_NEXT_UPDATE_DATE_DAYS AS CASE_ACTIVITY_NEXT_UPDATE_DAYS,
AC.DISPATCH_ENABLED AS DISPATCH_ENABLED
FROM AGENCY_CODES AC
LEFT OUTER JOIN AGENCY_FEATURES AF
ON AF.AGENCY_CODE = AC.AGENCY_CODE
LEFT OUTER JOIN EJS_CODES EC1
ON AF.FEATURE_CODE = EC1.CODE
AND AF.FEATURE_CODE_TYPE = EC1.CODE_TYPE
AND EC1.CODE_TYPE = AF.FEATURE_CODE_TYPE
LEFT OUTER JOIN EXTERNAL_SYSTEM_AGENCIES ESA
ON AC.AGENCY_CODE = ESA.AGNCY_CD_AGENCY_CODE
LEFT OUTER JOIN EXTERNAL_SYSTEM_CODES ESC
ON ESA.EXSYS_CODE = ESC.CODE
LEFT OUTER JOIN AGENCY_SEARCH_INTERFACES ASI
ON AC.AGENCY_CODE = ASI.AGENCY_CODE
LEFT OUTER JOIN SEARCH_INTERFACES SI
ON SI.INTERFACE_ID = ASI.INTERFACE_ID
LEFT OUTER JOIN EJS_WEB_SERVICE_AGENCIES WSA
ON WSA.AGENCY_CODE = AC.AGENCY_CODE
LEFT OUTER JOIN AGENCY_USER_TYPE_COUNTS UTC
ON UTC.AGENCY_CODE = AC.AGENCY_CODE
AND UTC.COUNT != 0
LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS POD
ON POD.AGENCY_CODE = AC.AGENCY_CODE
LEFT OUTER JOIN AGENCY_WORKGROUPS AW
ON AW.AGENCY_CODE = AC.AGENCY_CODE
LEFT OUTER JOIN EJS_CODES EC2
ON EC2.CODE = AW.WORKGROUP_CODE
AND EC2.CODE_TYPE = AW.WORKGROUP_CODE_TYPE
LEFT OUTER JOIN EJS_CODES EC3
ON EC3.CODE = AC.OPTION_CODE
AND EC3.CODE_TYPE = AC.OPTION_CODE_TYPE
LEFT OUTER JOIN EJS_CODES EC4
ON EC4.CODE = AC.SUPPORT_CODE
AND EC4.CODE_TYPE = AC.SUPPORT_CODE_TYPE
Possibly Referenced Tables/Views:
