|
|
| 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 |
| LOGIN_ID |
varchar2 |
100 |
|
|
|
|
|
| AGENCY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| AGENCY_DESC |
varchar2 |
40 |
√ |
|
null |
|
|
| LNAME |
varchar2 |
20 |
√ |
|
null |
|
|
| FNAME |
varchar2 |
20 |
√ |
|
null |
|
|
| MNAME |
varchar2 |
20 |
√ |
|
null |
|
|
| TELEPHONE_NUMBER |
varchar2 |
10 |
√ |
|
null |
|
|
| TITLE |
varchar2 |
20 |
√ |
|
null |
|
|
| INTERNAL_ID |
varchar2 |
100 |
√ |
|
null |
|
|
| ASSIGNMENT_ORG_ID |
number |
38 |
√ |
|
null |
|
|
| ASSN_START_DATE |
date |
7 |
√ |
|
null |
|
|
| ASSN_END_DATE |
date |
7 |
√ |
|
null |
|
|
| IS_USER_ASSIGNED |
varchar2 |
1 |
√ |
|
null |
|
|
| IS_RESTRICTED_ORG |
varchar2 |
1 |
√ |
|
null |
|
|
| IS_ACTIVE |
varchar2 |
1 |
√ |
|
null |
|
|
| IS_REMOVED |
varchar2 |
1 |
√ |
|
null |
|
|
| POD_ID |
number |
38 |
√ |
|
null |
|
|
| ASSN_AGENCY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| ASSN_AGENCY_DESC |
varchar2 |
40 |
√ |
|
null |
|
|
| POD_AGENCY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| POD_AGENCY_DESC |
varchar2 |
40 |
√ |
|
null |
|
|
| AGENCY_DESCRIPTOR |
varchar2 |
255 |
√ |
|
null |
|
|
| AGENCY_PARENT |
number |
38 |
√ |
|
null |
|
|
| IS_REFERENCE_AGENCY |
varchar2 |
1 |
√ |
|
null |
|
|
| IS_ROOT |
varchar2 |
1 |
√ |
|
null |
|
|
| STRUCTURE_LEVEL |
number |
38 |
√ |
|
null |
|
|
| STRUCTURE_ID |
varchar2 |
30 |
√ |
|
null |
|
|
| AFFILIATE |
varchar2 |
1 |
√ |
|
null |
|
|
| IS_RESTRICTED |
varchar2 |
1 |
√ |
|
null |
|
|
| DISPLAY_FIELD |
varchar2 |
255 |
√ |
|
null |
|
|
| IS_GEOGRAPHIC |
varchar2 |
1 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
SELECT
IUP.LOGIN_ID
,IUP.AGNCY_CD_AGENCY_CODE
,AG.AGENCY_DESC
,IUP.LNAME
,IUP.FNAME
,IUP.MNAME
,IUP.TELEPHONE_NUMBER
,IUP.TITLE
,IUP.INTERNAL_ID
,ASNO.ASSIGNMENT_ORG_ID
,ASNO.START_DATE
,ASNO.END_DATE
,ASNO.IS_USER_ASSIGNED
,ASNO.IS_RESTRICTED_ORG
,ASNO.IS_ACTIVE
,ASNO.IS_REMOVED
,ASNO.POD_ID
,CASE WHEN ASNO.AGENCY_CODE IS NULL THEN POD.AGENCY_CODE ELSE ASNO.AGENCY_CODE END AS ASSN_AGENCY_CODE
,CASE WHEN ASNO.AGENCY_CODE IS NULL THEN
(SELECT A1.AGENCY_DESC FROM AGENCY_CODES A1 WHERE POD.AGENCY_CODE = A1.AGENCY_CODE)
ELSE
(SELECT A.AGENCY_DESC FROM AGENCY_CODES A WHERE ASNO.AGENCY_CODE = A.AGENCY_CODE)
END AS ASSN_AGENCY_DESC
,POD.AGENCY_CODE
,(SELECT A1.AGENCY_DESC FROM AGENCY_CODES A1 WHERE POD.AGENCY_CODE = A1.AGENCY_CODE) AS POD_AGENCY_DESC
,POD.AGENCY_DESCRIPTOR
,POD.AGENCY_PARENT
,POD.IS_REFERENCE_AGENCY
,POD.IS_ROOT
,POD.STRUCTURE_LEVEL
,POD.STRUCTURE_ID
,POD.AFFILIATE
,POD.IS_RESTRICTED
,POD.DISPLAY_FIELD
,POD.IS_GEOGRAPHIC
FROM IJIS_USER_PROFILES IUP
INNER JOIN AGENCY_CODES AG ON IUP.AGNCY_CD_AGENCY_CODE = AG.AGENCY_CODE
LEFT OUTER JOIN ASSIGNMENT_ORGS ASNO ON IUP.LOGIN_ID = ASNO.LOGIN_ID
LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS POD ON ASNO.POD_ID = POD.POD_ID
Possibly Referenced Tables/Views:
