|
|
| 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 |
| INSTANCE_ID |
number |
38 |
|
|
|
|
|
| SCREEN_ID |
number |
38 |
|
|
|
|
|
| CREATOR_ID |
varchar2 |
100 |
√ |
|
null |
|
|
| CREATOR_NAME |
varchar2 |
42 |
√ |
|
null |
|
|
| CREATOR_DATE |
date |
7 |
√ |
|
null |
|
|
| SCREEN_TYPE_CODE |
varchar2 |
30 |
|
|
|
|
|
| OFFICER_ID |
number |
38 |
√ |
|
null |
|
|
| OFFICER_NAME |
varchar2 |
165 |
√ |
|
null |
|
|
| AGENCY_CODE |
varchar2 |
30 |
|
|
|
|
|
| ORG_AGENCY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| INSTANCE_NUMBER |
varchar2 |
30 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
SELECT
USI.INSTANCE_ID,
US.SCREEN_ID,
IUP.LOGIN_ID,
IUP.LNAME || ', ' || IUP.FNAME,
USI.CREATOR_DATE,
USI.SCREEN_TYPE_CODE,
USI.OFFICER_ID,
CASE WHEN O.OFFICER_ID IS NOT NULL THEN
O.TITLE || ' ' || O.FNAME || ' ' || O.LNAME || '(#'||O.INTERNAL_ID||')'
ELSE '' END,
USI.AGENCY_CODE,
CASE
WHEN ROD.AGENCY_CODE IS NOT NULL THEN ROD.AGENCY_CODE
ELSE AC.AGENCY_CODE
END
AS ORG_AGENCY_CODE,
USI.INSTANCE_NUMBER
FROM USER_SCREEN_INSTANCES USI
INNER JOIN USER_SCREENS US ON USI.SCREEN_ID = US.SCREEN_ID
LEFT OUTER JOIN IJIS_USER_PROFILES IUP ON USI.CREATOR_ID = IUP.LOGIN_ID
LEFT OUTER JOIN OFFICERS O ON USI.OFFICER_ID = O.OFFICER_ID
INNER JOIN AGENCY_CODES AC ON USI.AGENCY_CODE = AC.AGENCY_CODE
LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS POD ON AC.AGENCY_CODE = POD.AGENCY_CODE
LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS ROD ON POD.STRUCTURE_ID = ROD.STRUCTURE_ID AND ROD.IS_ROOT = 'Y'
Possibly Referenced Tables/Views:
