|
|
| 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 |
| ID |
number |
0 |
|
|
|
|
|
| AGENCY_CODE |
varchar2 |
30 |
|
|
|
|
|
| AGENCY_DESC |
varchar2 |
40 |
√ |
|
null |
|
|
| CODE |
varchar2 |
30 |
|
|
|
|
|
| DESCRIPTION |
varchar2 |
4000 |
√ |
|
null |
|
|
| LICENSE_COUNT |
number |
0 |
|
|
|
|
|
| ACTIVE_COUNT |
number |
0 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
SELECT
AUTC.AGENCY_USER_TYPE_COUNTS_ID,
AC.AGENCY_CODE,
AC.AGENCY_DESC,
EC.CODE,
EC.DESCRIPTION,
AUTC.COUNT AS LICENSE_COUNT,
(SELECT COUNT(*) FROM IJIS_USER_PROFILES IUP
INNER JOIN EJS_USERS@EJSLOOKUPDBC EU ON IUP.LOGIN_ID = EU.LOGIN_ID
WHERE
IUP.TIME_CODE = EC.CODE
AND EU.ACCOUNT_STATUS = 'A'
AND EU.CC_ADMIN = 'N'
AND (IUP.AGNCY_CD_AGENCY_CODE IN (SELECT
AC2.AGENCY_CODE
FROM AGENCY_PARENT_VW APV
INNER JOIN AGENCY_CODES AC2 ON APV.AGENCY_CODE = AC2.AGENCY_CODE
WHERE APV.PARENT_AGENCY_CODE = AC.AGENCY_CODE
)
OR IUP.AGNCY_CD_AGENCY_CODE = AC.AGENCY_CODE)) AS ACTIVE_COUNT
FROM AGENCY_CODES AC
INNER JOIN AGENCY_USER_TYPE_COUNTS AUTC ON AC.AGENCY_CODE = AUTC.AGENCY_CODE
INNER JOIN EJS_CODES EC ON AUTC.SUBSCRIPTION_LEVEL_CODE_TYPE = EC.CODE_TYPE AND AUTC.SUBSCRIPTION_LEVEL_CODE = EC.CODE
WHERE (
EXISTS (SELECT 1 FROM PSEUDO_ORG_DESCRIPTORS POD WHERE POD.AGENCY_CODE = AC.AGENCY_CODE AND POD.IS_ROOT = 'Y')
OR NOT EXISTS (SELECT 1 FROM PSEUDO_ORG_DESCRIPTORS POD WHERE POD.AGENCY_CODE = AC.AGENCY_CODE)
)
Possibly Referenced Tables/Views:
