|
|
| 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 |
| KEEP_MASTER_PERSON_ID |
number |
38 |
|
|
|
|
|
| LNAME |
varchar2 |
100 |
√ |
|
null |
|
|
| FNAME |
varchar2 |
80 |
√ |
|
null |
|
|
| MNAME |
varchar2 |
80 |
√ |
|
null |
|
|
| DOB |
date |
7 |
√ |
|
null |
|
|
| SSN |
number |
0 |
√ |
|
null |
|
|
| DL |
varchar2 |
4000 |
√ |
|
null |
|
|
| SEX_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| SEX_DESC |
varchar2 |
4000 |
√ |
|
null |
|
|
| RACE_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| RACE_DESC |
varchar2 |
4000 |
√ |
|
null |
|
|
| CREATOR_DATE |
date |
7 |
√ |
|
null |
|
|
| UPDATOR_ID |
varchar2 |
100 |
√ |
|
null |
|
|
| PERSON_ID |
number |
0 |
|
|
|
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
WITH DISP_MP AS (SELECT DISTINCT KEEP_MASTER_PERSON_ID FROM COLLAPSE_LOG_ENTRIES)
SELECT
DMP.KEEP_MASTER_PERSON_ID,
MN.LNAME,
MN.FNAME,
MN.MNAME,
MN.DOB,
MN.SSN,
EJS_GET_LATEST_PERSON_DL (PERSON_NAME.PERSON_ID, 'N') AS DL,
SC.CODE AS SEX_CODE,
SC.DESCRIPTION AS SEX_DESC,
ERACE.CODE AS RACE_CODE,
ERACE.DESCRIPTION AS RACE_DESC,
(SELECT MAX(CLE.CREATOR_DATE) FROM COLLAPSE_LOG_ENTRIES CLE WHERE CLE.KEEP_MASTER_PERSON_ID = DMP.KEEP_MASTER_PERSON_ID) as CREATOR_DATE,
(SELECT MAX(CLE.UPDATOR_ID) FROM COLLAPSE_LOG_ENTRIES CLE WHERE CLE.KEEP_MASTER_PERSON_ID = DMP.KEEP_MASTER_PERSON_ID) as UPDATOR_ID,
PERSON_NAME.PERSON_ID
FROM DISP_MP DMP
INNER JOIN MASTER_PEOPLE MP ON DMP.KEEP_MASTER_PERSON_ID = MP.MASTER_PERSON_ID
INNER JOIN PEOPLE PERSON_NAME ON MP.MASTER_PERSON_ID = PERSON_NAME.MASTER_PERSON_ID
INNER JOIN MASTER_NAMES MN ON PERSON_NAME.PERSON_ID = MN.PER_PERSON_ID AND MN.NAME_TYPE_CODE = 'P'
LEFT OUTER JOIN EJS_CODES SC ON MP.SEX_CODE_TYPE = SC.CODE_TYPE AND MP.SEX_CODE = SC.CODE
LEFT OUTER JOIN EJS_CODES ERACE ON MP.RACE_CODE_TYPE = ERACE.CODE_TYPE AND MP.RACE_CODE = ERACE.CODE
Possibly Referenced Tables/Views:
